下述3种方法得当oracle认为应该的执行计划：
SQL> set autot off
SQL> explain plan for
2 SELECT prod_category, AVG(amount_sold)
3 FROM sh.sales s, sh.products p
4 WHERE p.prod_id = s.prod_id
5 GROUP BY prod_category;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1197568639
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 255 | 574 (10)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 5 | 255 | 574 (10)| 00:00:07 | | |
|* 2 | HASH JOIN | | 72 | 3672 | 573 (10)| 00:00:07 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 2160 | 570 (10)| 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 648 | 570 (10)| 00:00:07 | | |
| 5 | PARTITION RANGE ALL | | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 7 | VIEW | index$_join$_002 | 72 | 1512 | 3 (34)| 00:00:01 | | |
|* 8 | HASH JOIN | | | | | | | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 1512 | 1 (0)| 00:00:01 | | |
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 1512 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="ITEM_1")
8 - access(ROWID=ROWID)
23 rows selected.
SQL> set autot traceonly
SQL> SELECT prod_category, AVG(amount_sold)
2 FROM sh.sales s, sh.products p
3 WHERE p.prod_id = s.prod_id
4 GROUP BY prod_category;
Execution Plan
----------------------------------------------------------
Plan hash value: 1197568639
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 255 | 574 (10)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 5 | 255 | 574 (10)| 00:00:07 | | |
|* 2 | HASH JOIN | | 72 | 3672 | 573 (10)| 00:00:07 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 2160 | 570 (10)| 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 648 | 570 (10)| 00:00:07 | | |
| 5 | PARTITION RANGE ALL | | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 7 | VIEW | index$_join$_002 | 72 | 1512 | 3 (34)| 00:00:01 | | |
|* 8 | HASH JOIN | | | | | | | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 1512 | 1 (0)| 00:00:01 | | |
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 1512 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="ITEM_1")
8 - access(ROWID=ROWID)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1641 consistent gets
1619 physical reads
0 redo size
851 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
换种方式，看看ALL的格式：
SQL> explain plan for
2 SELECT prod_category, AVG(amount_sold)
3 FROM sh.sales s, sh.products p
4 WHERE p.prod_id = s.prod_id
5 GROUP BY prod_category;
Explained.
SQL> select * from table(dbms_xplan.display('',null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1197568639
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 255 | 574 (10)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 5 | 255 | 574 (10)| 00:00:07 | | |
|* 2 | HASH JOIN | | 72 | 3672 | 573 (10)| 00:00:07 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 2160 | 570 (10)| 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 648 | 570 (10)| 00:00:07 | | |
| 5 | PARTITION RANGE ALL | | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 7 | VIEW | index$_join$_002 | 72 | 1512 | 3 (34)| 00:00:01 | | |
|* 8 | HASH JOIN | | | | | | | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 1512 | 1 (0)| 00:00:01 | | |
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 1512 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$62C25666
3 - SEL$7A21928B / VW_GBC_5@SEL$0737CF93
4 - SEL$7A21928B
6 - SEL$7A21928B / S@SEL$1
7 - SEL$5F03631D / P@SEL$1
8 - SEL$5F03631D
9 - SEL$5F03631D / indexjoin$_alias$_001@SEL$5F03631D
10 - SEL$5F03631D / indexjoin$_alias$_002@SEL$5F03631D
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="ITEM_1")
8 - access(ROWID=ROWID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "PROD_CATEGORY"[VARCHAR2,50], SUM("ITEM_2")[22], SUM("ITEM_3")[22]
2 - (#keys=1) "ITEM_3"[NUMBER,22], "ITEM_2"[NUMBER,22], "PROD_CATEGORY"[VARCHAR2,50]
3 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[NUMBER,22]
4 - (#keys=1) "S"."PROD_ID"[NUMBER,22], COUNT("AMOUNT_SOLD")[22], SUM("AMOUNT_SOLD")[22]
5 - "S"."PROD_ID"[NUMBER,22], "AMOUNT_SOLD"[NUMBER,22]
6 - "S"."PROD_ID"[NUMBER,22], "AMOUNT_SOLD"[NUMBER,22]
7 - "PROD_CATEGORY"[VARCHAR2,50], "P"."PROD_ID"[NUMBER,22]
8 - (#keys=1) "P"."PROD_ID"[NUMBER,22], "PROD_CATEGORY"[VARCHAR2,50]
9 - ROWID[ROWID,10], "P"."PROD_ID"[NUMBER,22]
10 - ROWID[ROWID,10], "PROD_CATEGORY"[VARCHAR2,50]
49 rows selected.
SQL> set autot off;
SQL> alter system flush shared_pool;
System altered.
第3种方式获得执行计划：
SQL> SELECT prod_category, AVG(amount_sold)
2 FROM sh.sales s, sh.products p
3 WHERE p.prod_id = s.prod_id
4 GROUP BY prod_category;
PROD_CATEGORY AVG(AMOUNT_SOLD)
-------------------------------------------------- ----------------
Software/Other 34.1313997
Hardware 1344.50776
Electronics 125.551667
Peripherals and Accessories 108.824588
Photo 188.064642
SQL> select * from table(dbms_xplan.display_cursor(null,null,'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT prod_category, AVG(amount_sold) FROM sh.sales s, sh.products p
WHERE p.prod_id = s.prod_id GROUP BY prod_category
Plan hash value: 1197568639
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
| 2 | HASH JOIN | |
| 3 | VIEW | VW_GBC_5 |
| 4 | HASH GROUP BY | |
| 5 | PARTITION RANGE ALL | |
| 6 | TABLE ACCESS FULL | SALES |
| 7 | VIEW | index$_join$_002 |
| 8 | HASH JOIN | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK |
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |
---------------------------------------------------------
23 rows selected.
只能看到最近一次的查询语句的执行计划，所以再来一次：
SQL> SELECT prod_category, AVG(amount_sold)
2 FROM sh.sales s, sh.products p
3 WHERE p.prod_id = s.prod_id
4 GROUP BY prod_category;
PROD_CATEGORY AVG(AMOUNT_SOLD)
-------------------------------------------------- ----------------
Software/Other 34.1313997
Hardware 1344.50776
Electronics 125.551667
Peripherals and Accessories 108.824588
Photo 188.064642
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cmgy9bj1fmsga, child number 0
-------------------------------------
SELECT prod_category, AVG(amount_sold) FROM sh.sales s, sh.products p
WHERE p.prod_id = s.prod_id GROUP BY prod_category
Plan hash value: 1197568639
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 574 (100)| | | |
| 1 | HASH GROUP BY | | 5 | 255 | 574 (10)| 00:00:07 | | |
|* 2 | HASH JOIN | | 72 | 3672 | 573 (10)| 00:00:07 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 2160 | 570 (10)| 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 648 | 570 (10)| 00:00:07 | | |
| 5 | PARTITION RANGE ALL | | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 7 | VIEW | index$_join$_002 | 72 | 1512 | 3 (34)| 00:00:01 | | |
|* 8 | HASH JOIN | | | | | | | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 1512 | 1 (0)| 00:00:01 | | |
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 1512 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$62C25666
3 - SEL$7A21928B / VW_GBC_5@SEL$0737CF93
4 - SEL$7A21928B
6 - SEL$7A21928B / S@SEL$1
7 - SEL$5F03631D / P@SEL$1
8 - SEL$5F03631D
9 - SEL$5F03631D / indexjoin$_alias$_001@SEL$5F03631D
10 - SEL$5F03631D / indexjoin$_alias$_002@SEL$5F03631D
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="ITEM_1")
8 - access(ROWID=ROWID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "PROD_CATEGORY"[VARCHAR2,50], SUM("ITEM_2")[22], SUM("ITEM_3")[22]
2 - (#keys=1) "ITEM_3"[NUMBER,22], "ITEM_2"[NUMBER,22], "PROD_CATEGORY"[VARCHAR2,50]
3 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[NUMBER,22]
4 - "S"."PROD_ID"[NUMBER,22], COUNT("AMOUNT_SOLD")[22], SUM("AMOUNT_SOLD")[22]
5 - "S"."PROD_ID"[NUMBER,22], "AMOUNT_SOLD"[NUMBER,22]
6 - "S"."PROD_ID"[NUMBER,22], "AMOUNT_SOLD"[NUMBER,22]
7 - "PROD_CATEGORY"[VARCHAR2,50], "P"."PROD_ID"[NUMBER,22]
8 - (#keys=1) "P"."PROD_ID"[NUMBER,22], "PROD_CATEGORY"[VARCHAR2,50]
9 - ROWID[ROWID,10], "P"."PROD_ID"[NUMBER,22]
10 - ROWID[ROWID,10], "PROD_CATEGORY"[VARCHAR2,50]
55 rows selected.
花样很多，请看这次：
SQL> explain plan for
2 SELECT prod_category, AVG(amount_sold)
3 FROM sh.sales s, sh.products p
4 WHERE p.prod_id = s.prod_id
5 GROUP BY prod_category;
Explained.
SQL> select * from table(dbms_xplan.display('',null,'basic +cost +predicate'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1197568639
----------------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 574 (10)|
| 1 | HASH GROUP BY | | 574 (10)|
|* 2 | HASH JOIN | | 573 (10)|
| 3 | VIEW | VW_GBC_5 | 570 (10)|
| 4 | HASH GROUP BY | | 570 (10)|
| 5 | PARTITION RANGE ALL | | 530 (3)|
| 6 | TABLE ACCESS FULL | SALES | 530 (3)|
| 7 | VIEW | index$_join$_002 | 3 (34)|
|* 8 | HASH JOIN | | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK | 1 (0)|
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 1 (0)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="ITEM_1")
8 - access(ROWID=ROWID)
23 rows selected.
还有这一次：
SQL> explain plan for
2 SELECT prod_category, AVG(amount_sold)
3 FROM sh.sales s, sh.products p
4 WHERE p.prod_id = s.prod_id
5 GROUP BY prod_category;
Explained.
SQL> select * from table(dbms_xplan.display('',null,'typical -cost -bytes'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1197568639
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 00:00:07 | | |
| 1 | HASH GROUP BY | | 5 | 00:00:07 | | |
|* 2 | HASH JOIN | | 72 | 00:00:07 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 00:00:07 | | |
| 5 | PARTITION RANGE ALL | | 918K| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 00:00:07 | 1 | 28 |
| 7 | VIEW | index$_join$_002 | 72 | 00:00:01 | | |
|* 8 | HASH JOIN | | | | | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 00:00:01 | | |
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 00:00:01 | | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="ITEM_1")
8 - access(ROWID=ROWID)
23 rows selected.
柱状图相关的执行计划：
默认带着表和索引统计信息及直方图：
SQL> explain plan for
2 SELECT employee_id, last_name, job_id
3 FROM hr.employees
4 WHERE job_id = 'AD_VP';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display('',null,'ALL'))
2 ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1019430118
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 42 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 | 42 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='AD_VP')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEE_ID"[NUMBER,22], "LAST_NAME"[VARCHAR2,25], "JOB_ID"[VARCHAR2,10]
2 - "EMPLOYEES".ROWID[ROWID,10], "JOB_ID"[VARCHAR2,10]
26 rows selected.
SQL> select * from table(dbms_xplan.display('',null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1019430118
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 42 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 | 42 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='AD_VP')
14 rows selected.
表中数据的真实分布情况：
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
SQL> select job_id,count(*) from hr.employees group by job_id order by 2;
JOB_ID COUNT(*)
---------- ----------
AC_ACCOUNT 1
AC_MGR 1
PU_MAN 1
PR_REP 1
MK_REP 1
MK_MAN 1
HR_REP 1
FI_MGR 1
AD_PRES 1
AD_ASST 1
AD_VP 2
IT_PROG 5
PU_CLERK 5
ST_MAN 5
SA_MAN 5
FI_ACCOUNT 5
SH_CLERK 20
ST_CLERK 20
SA_REP 30
19 rows selected.
删除直方图
exec dbms_stats.delete_column_stats(user,'EMPLOYEES','JOB_ID');
执行计划变为：
SQL> select * from table(dbms_xplan.display('',null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1019430118
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 19 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='AD_VP')
继续把表的统计信息删除掉：
SQL> explain plan for
2 SELECT employee_id, last_name, job_id
3 FROM hr.employees
4 WHERE job_id = 'AD_VP';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display('',null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1019430118
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 68 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 | 68 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='AD_VP')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEE_ID"[NUMBER,22], "LAST_NAME"[VARCHAR2,25], "JOB_ID"[VARCHAR2,10]
2 - "EMPLOYEES".ROWID[ROWID,10], "JOB_ID"[VARCHAR2,10]
Note
-----
- dynamic sampling used for this statement (level=2)
30 rows selected.
此处触发了动态采样功能。
重新收集统计信息：
SQL> Exec DBMS_STATS.GATHER_TABLE_STATS('HR','EMPLOYEES',method_opt=>'FOR COLUMNS SIZE 254 JOB_ID');
PL/SQL procedure successfully completed.
确认下直方图确实存在了：
SQL> select * from dba_histograms where table_name='EMPLOYEES' and column_name='JOB_ID';
带着直方图，去访问数据量大的数据，也没有触发全表扫描动作
SQL> explain plan for
2 SELECT employee_id, last_name, job_id
3 FROM hr.employees
4 WHERE job_id = 'ST_CLERK';
Explained.
SQL> select * from table(dbms_xplan.display('',null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1019430118
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 420 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 20 | 420 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 20 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='ST_CLERK')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEE_ID"[NUMBER,22], "LAST_NAME"[VARCHAR2,25], "JOB_ID"[VARCHAR2,10]
2 - "EMPLOYEES".ROWID[ROWID,10], "JOB_ID"[VARCHAR2,10]
26 rows selected.
SQL> set autot off
SQL> explain plan for
2 SELECT prod_category, AVG(amount_sold)
3 FROM sh.sales s, sh.products p
4 WHERE p.prod_id = s.prod_id
5 GROUP BY prod_category;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1197568639
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 255 | 574 (10)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 5 | 255 | 574 (10)| 00:00:07 | | |
|* 2 | HASH JOIN | | 72 | 3672 | 573 (10)| 00:00:07 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 2160 | 570 (10)| 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 648 | 570 (10)| 00:00:07 | | |
| 5 | PARTITION RANGE ALL | | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 7 | VIEW | index$_join$_002 | 72 | 1512 | 3 (34)| 00:00:01 | | |
|* 8 | HASH JOIN | | | | | | | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 1512 | 1 (0)| 00:00:01 | | |
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 1512 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="ITEM_1")
8 - access(ROWID=ROWID)
23 rows selected.
SQL> set autot traceonly
SQL> SELECT prod_category, AVG(amount_sold)
2 FROM sh.sales s, sh.products p
3 WHERE p.prod_id = s.prod_id
4 GROUP BY prod_category;
Execution Plan
----------------------------------------------------------
Plan hash value: 1197568639
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 255 | 574 (10)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 5 | 255 | 574 (10)| 00:00:07 | | |
|* 2 | HASH JOIN | | 72 | 3672 | 573 (10)| 00:00:07 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 2160 | 570 (10)| 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 648 | 570 (10)| 00:00:07 | | |
| 5 | PARTITION RANGE ALL | | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 7 | VIEW | index$_join$_002 | 72 | 1512 | 3 (34)| 00:00:01 | | |
|* 8 | HASH JOIN | | | | | | | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 1512 | 1 (0)| 00:00:01 | | |
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 1512 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="ITEM_1")
8 - access(ROWID=ROWID)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1641 consistent gets
1619 physical reads
0 redo size
851 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
换种方式，看看ALL的格式：
SQL> explain plan for
2 SELECT prod_category, AVG(amount_sold)
3 FROM sh.sales s, sh.products p
4 WHERE p.prod_id = s.prod_id
5 GROUP BY prod_category;
Explained.
SQL> select * from table(dbms_xplan.display('',null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1197568639
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 255 | 574 (10)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 5 | 255 | 574 (10)| 00:00:07 | | |
|* 2 | HASH JOIN | | 72 | 3672 | 573 (10)| 00:00:07 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 2160 | 570 (10)| 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 648 | 570 (10)| 00:00:07 | | |
| 5 | PARTITION RANGE ALL | | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 7 | VIEW | index$_join$_002 | 72 | 1512 | 3 (34)| 00:00:01 | | |
|* 8 | HASH JOIN | | | | | | | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 1512 | 1 (0)| 00:00:01 | | |
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 1512 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$62C25666
3 - SEL$7A21928B / VW_GBC_5@SEL$0737CF93
4 - SEL$7A21928B
6 - SEL$7A21928B / S@SEL$1
7 - SEL$5F03631D / P@SEL$1
8 - SEL$5F03631D
9 - SEL$5F03631D / indexjoin$_alias$_001@SEL$5F03631D
10 - SEL$5F03631D / indexjoin$_alias$_002@SEL$5F03631D
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="ITEM_1")
8 - access(ROWID=ROWID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "PROD_CATEGORY"[VARCHAR2,50], SUM("ITEM_2")[22], SUM("ITEM_3")[22]
2 - (#keys=1) "ITEM_3"[NUMBER,22], "ITEM_2"[NUMBER,22], "PROD_CATEGORY"[VARCHAR2,50]
3 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[NUMBER,22]
4 - (#keys=1) "S"."PROD_ID"[NUMBER,22], COUNT("AMOUNT_SOLD")[22], SUM("AMOUNT_SOLD")[22]
5 - "S"."PROD_ID"[NUMBER,22], "AMOUNT_SOLD"[NUMBER,22]
6 - "S"."PROD_ID"[NUMBER,22], "AMOUNT_SOLD"[NUMBER,22]
7 - "PROD_CATEGORY"[VARCHAR2,50], "P"."PROD_ID"[NUMBER,22]
8 - (#keys=1) "P"."PROD_ID"[NUMBER,22], "PROD_CATEGORY"[VARCHAR2,50]
9 - ROWID[ROWID,10], "P"."PROD_ID"[NUMBER,22]
10 - ROWID[ROWID,10], "PROD_CATEGORY"[VARCHAR2,50]
49 rows selected.
SQL> set autot off;
SQL> alter system flush shared_pool;
System altered.
第3种方式获得执行计划：
SQL> SELECT prod_category, AVG(amount_sold)
2 FROM sh.sales s, sh.products p
3 WHERE p.prod_id = s.prod_id
4 GROUP BY prod_category;
PROD_CATEGORY AVG(AMOUNT_SOLD)
-------------------------------------------------- ----------------
Software/Other 34.1313997
Hardware 1344.50776
Electronics 125.551667
Peripherals and Accessories 108.824588
Photo 188.064642
SQL> select * from table(dbms_xplan.display_cursor(null,null,'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT prod_category, AVG(amount_sold) FROM sh.sales s, sh.products p
WHERE p.prod_id = s.prod_id GROUP BY prod_category
Plan hash value: 1197568639
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
| 2 | HASH JOIN | |
| 3 | VIEW | VW_GBC_5 |
| 4 | HASH GROUP BY | |
| 5 | PARTITION RANGE ALL | |
| 6 | TABLE ACCESS FULL | SALES |
| 7 | VIEW | index$_join$_002 |
| 8 | HASH JOIN | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK |
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |
---------------------------------------------------------
23 rows selected.
只能看到最近一次的查询语句的执行计划，所以再来一次：
SQL> SELECT prod_category, AVG(amount_sold)
2 FROM sh.sales s, sh.products p
3 WHERE p.prod_id = s.prod_id
4 GROUP BY prod_category;
PROD_CATEGORY AVG(AMOUNT_SOLD)
-------------------------------------------------- ----------------
Software/Other 34.1313997
Hardware 1344.50776
Electronics 125.551667
Peripherals and Accessories 108.824588
Photo 188.064642
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cmgy9bj1fmsga, child number 0
-------------------------------------
SELECT prod_category, AVG(amount_sold) FROM sh.sales s, sh.products p
WHERE p.prod_id = s.prod_id GROUP BY prod_category
Plan hash value: 1197568639
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 574 (100)| | | |
| 1 | HASH GROUP BY | | 5 | 255 | 574 (10)| 00:00:07 | | |
|* 2 | HASH JOIN | | 72 | 3672 | 573 (10)| 00:00:07 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 2160 | 570 (10)| 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 648 | 570 (10)| 00:00:07 | | |
| 5 | PARTITION RANGE ALL | | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 8075K| 530 (3)| 00:00:07 | 1 | 28 |
| 7 | VIEW | index$_join$_002 | 72 | 1512 | 3 (34)| 00:00:01 | | |
|* 8 | HASH JOIN | | | | | | | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 1512 | 1 (0)| 00:00:01 | | |
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 1512 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$62C25666
3 - SEL$7A21928B / VW_GBC_5@SEL$0737CF93
4 - SEL$7A21928B
6 - SEL$7A21928B / S@SEL$1
7 - SEL$5F03631D / P@SEL$1
8 - SEL$5F03631D
9 - SEL$5F03631D / indexjoin$_alias$_001@SEL$5F03631D
10 - SEL$5F03631D / indexjoin$_alias$_002@SEL$5F03631D
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="ITEM_1")
8 - access(ROWID=ROWID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "PROD_CATEGORY"[VARCHAR2,50], SUM("ITEM_2")[22], SUM("ITEM_3")[22]
2 - (#keys=1) "ITEM_3"[NUMBER,22], "ITEM_2"[NUMBER,22], "PROD_CATEGORY"[VARCHAR2,50]
3 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[NUMBER,22]
4 - "S"."PROD_ID"[NUMBER,22], COUNT("AMOUNT_SOLD")[22], SUM("AMOUNT_SOLD")[22]
5 - "S"."PROD_ID"[NUMBER,22], "AMOUNT_SOLD"[NUMBER,22]
6 - "S"."PROD_ID"[NUMBER,22], "AMOUNT_SOLD"[NUMBER,22]
7 - "PROD_CATEGORY"[VARCHAR2,50], "P"."PROD_ID"[NUMBER,22]
8 - (#keys=1) "P"."PROD_ID"[NUMBER,22], "PROD_CATEGORY"[VARCHAR2,50]
9 - ROWID[ROWID,10], "P"."PROD_ID"[NUMBER,22]
10 - ROWID[ROWID,10], "PROD_CATEGORY"[VARCHAR2,50]
55 rows selected.
花样很多，请看这次：
SQL> explain plan for
2 SELECT prod_category, AVG(amount_sold)
3 FROM sh.sales s, sh.products p
4 WHERE p.prod_id = s.prod_id
5 GROUP BY prod_category;
Explained.
SQL> select * from table(dbms_xplan.display('',null,'basic +cost +predicate'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1197568639
----------------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 574 (10)|
| 1 | HASH GROUP BY | | 574 (10)|
|* 2 | HASH JOIN | | 573 (10)|
| 3 | VIEW | VW_GBC_5 | 570 (10)|
| 4 | HASH GROUP BY | | 570 (10)|
| 5 | PARTITION RANGE ALL | | 530 (3)|
| 6 | TABLE ACCESS FULL | SALES | 530 (3)|
| 7 | VIEW | index$_join$_002 | 3 (34)|
|* 8 | HASH JOIN | | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK | 1 (0)|
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 1 (0)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="ITEM_1")
8 - access(ROWID=ROWID)
23 rows selected.
还有这一次：
SQL> explain plan for
2 SELECT prod_category, AVG(amount_sold)
3 FROM sh.sales s, sh.products p
4 WHERE p.prod_id = s.prod_id
5 GROUP BY prod_category;
Explained.
SQL> select * from table(dbms_xplan.display('',null,'typical -cost -bytes'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1197568639
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 00:00:07 | | |
| 1 | HASH GROUP BY | | 5 | 00:00:07 | | |
|* 2 | HASH JOIN | | 72 | 00:00:07 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 00:00:07 | | |
| 5 | PARTITION RANGE ALL | | 918K| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 00:00:07 | 1 | 28 |
| 7 | VIEW | index$_join$_002 | 72 | 00:00:01 | | |
|* 8 | HASH JOIN | | | | | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 00:00:01 | | |
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 00:00:01 | | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="ITEM_1")
8 - access(ROWID=ROWID)
23 rows selected.
柱状图相关的执行计划：
默认带着表和索引统计信息及直方图：
SQL> explain plan for
2 SELECT employee_id, last_name, job_id
3 FROM hr.employees
4 WHERE job_id = 'AD_VP';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display('',null,'ALL'))
2 ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1019430118
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 42 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 | 42 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='AD_VP')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEE_ID"[NUMBER,22], "LAST_NAME"[VARCHAR2,25], "JOB_ID"[VARCHAR2,10]
2 - "EMPLOYEES".ROWID[ROWID,10], "JOB_ID"[VARCHAR2,10]
26 rows selected.
SQL> select * from table(dbms_xplan.display('',null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1019430118
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 42 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 | 42 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='AD_VP')
14 rows selected.
表中数据的真实分布情况：
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
SQL> select job_id,count(*) from hr.employees group by job_id order by 2;
JOB_ID COUNT(*)
---------- ----------
AC_ACCOUNT 1
AC_MGR 1
PU_MAN 1
PR_REP 1
MK_REP 1
MK_MAN 1
HR_REP 1
FI_MGR 1
AD_PRES 1
AD_ASST 1
AD_VP 2
IT_PROG 5
PU_CLERK 5
ST_MAN 5
SA_MAN 5
FI_ACCOUNT 5
SH_CLERK 20
ST_CLERK 20
SA_REP 30
19 rows selected.
删除直方图
exec dbms_stats.delete_column_stats(user,'EMPLOYEES','JOB_ID');
执行计划变为：
SQL> select * from table(dbms_xplan.display('',null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1019430118
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 19 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='AD_VP')
继续把表的统计信息删除掉：
SQL> explain plan for
2 SELECT employee_id, last_name, job_id
3 FROM hr.employees
4 WHERE job_id = 'AD_VP';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display('',null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1019430118
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 68 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 | 68 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='AD_VP')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEE_ID"[NUMBER,22], "LAST_NAME"[VARCHAR2,25], "JOB_ID"[VARCHAR2,10]
2 - "EMPLOYEES".ROWID[ROWID,10], "JOB_ID"[VARCHAR2,10]
Note
-----
- dynamic sampling used for this statement (level=2)
30 rows selected.
此处触发了动态采样功能。
重新收集统计信息：
SQL> Exec DBMS_STATS.GATHER_TABLE_STATS('HR','EMPLOYEES',method_opt=>'FOR COLUMNS SIZE 254 JOB_ID');
PL/SQL procedure successfully completed.
确认下直方图确实存在了：
SQL> select * from dba_histograms where table_name='EMPLOYEES' and column_name='JOB_ID';
带着直方图，去访问数据量大的数据，也没有触发全表扫描动作
SQL> explain plan for
2 SELECT employee_id, last_name, job_id
3 FROM hr.employees
4 WHERE job_id = 'ST_CLERK';
Explained.
SQL> select * from table(dbms_xplan.display('',null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1019430118
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 420 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 20 | 420 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 20 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$1 / EMPLOYEES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='ST_CLERK')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEE_ID"[NUMBER,22], "LAST_NAME"[VARCHAR2,25], "JOB_ID"[VARCHAR2,10]
2 - "EMPLOYEES".ROWID[ROWID,10], "JOB_ID"[VARCHAR2,10]
26 rows selected.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/89196/viewspace-1459645/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/89196/viewspace-1459645/
3705
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
