Oracle中使用DBMS_XPLAN處理執行計劃詳解
Oracle中使用DBMS_XPLAN處理執行計劃詳解
DBMS_XPLAN是Oracle提供的一個用於檢視SQL計劃,包括執行計劃和解釋計劃的包;在以前檢視SQL執行計劃的時候,我都是使用set autotrace命令,不過現在看來,DBMS_XPLAN包給出了更加簡化的獲取和顯示計劃的方式。
這5個函式分別對應不同的顯示計劃的方式,DBMS_XPLAN包不僅可以獲取解釋計劃,它還可以用來輸出儲存在AWR,SQL除錯集,快取的SQL遊標,以及SQL基線中的語句計劃,實現如上的功能,通常會用到一下5個方法:
1.DISPLAY
2.DISPLAY_AWR
3.DISPLAY_CURSOR
4.DISPLAY_PLAN
5.DISPLAY_SQL_PLAN_BASELINE
6.DISPLAY_SQLSET
下面將重點討論關於DBMS_XPLAN包在解釋計劃和執行計劃上的應用。
來看一個經常使用的檢視某條語句的解釋計劃示例:
複製程式碼 程式碼如下:
SQL> explain plan for select * from scott.emp;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
-- 對應autotrace實現
SQL> set autotrace traceonly explain
上例中使用了dbms_xplan.display方法來顯示PLAN_TABLE中儲存的解釋計劃,如果想要顯示執行計劃,就需要使用到DMBS_XPLAN.DISPLAY_CURSOR方法了,DMBS_XPLAN.DISPLAY_CURSOR呼叫簽名:
複製程式碼 程式碼如下:
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
sql_id表示儲存在cursor cache中的SQL語句的id,child_number用於指示快取sql語句計劃的子id,format引數用於控制包含在輸出中的資訊型別,官檔的引數如下:
1.BASIC: 顯示最少的資訊,只包括操作型別,ID名稱和選項。
2.TYPICAL: 預設值,顯示相關資訊以及某些附加的顯示選項,如分割槽和併發使用等。
3.SERIAL: 與TYPICAL型別相似,區別是它不包括併發的資訊,即使是並行執行的計劃。
4.ALL: 顯示最多的資訊,包含了TYPICAL的全部以及更多的附加資訊,如別名和遠端呼叫等。
除了以上的基本的四種輸出格式外,format還有一些附加的選項可用於定製化輸出行為,使用中可以透過逗號和空格分隔來宣告多個關鍵字,同時可以使用”+”和”-”符號來包含或排除相應的顯示元素,這些附加的選項在官檔中也有記載:
1.ROWS – 顯示被最佳化器估算的記錄的行號
2.BYTES – 顯示最佳化器估算的位元組數
3.COST – 顯示最佳化器計算的成本資訊
4.PARTITION – 顯示分割槽的分割資訊
5.PARALLEL – 顯示並行執行資訊
6.PREDICATE – 顯示謂語
7.PROJECTION – 顯示列投影部分(每一行的那些列被傳遞給其父列已經這些列的大小)
8.ALIAS – 顯示查詢塊名稱已經物件別名
9.REMOTE – 顯示分散式查詢資訊
10.NOTE – 顯示註釋
11.IOSTATS – 顯示遊標執行的IO統計資訊
12.MEMSTATS – 為記憶體密集運算如雜湊聯結,排序,或一些型別的點陣圖運算顯示記憶體管理統計資訊
13.ALLSTATS – 與'IOSTATS MEMSTATS'等價
14.LAST – 顯示最後執行的執行計劃統計資訊,預設顯示為ALL型別,並且可以累積。
以上的引數同樣適用於解釋計劃的display方法。
示例部分:
一、使用display_cursor方法檢視最近一條語句的執行計劃
複製程式碼 程式碼如下:
SQL> select /*+ gather_plan_statistics */ count(*) from scott.emp;
COUNT(*)
----------
14
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID f9qyz8s3c2c02, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from scott.emp
Plan hash value: 2937609675
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| PK_EMP | 1 | 14 | 14 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------
14 rows selected.
使用dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')時,將sql_id和child_number設定成null,表示獲取上一條執行語句的執行計劃;注意上面的例子中一定要指定gather_plan_statistics提示或者手動設定資料庫STATISTICS_LEVEL引數為ALL來使得其抓取行資料來源的執行統計資訊,這些資訊包括行數,一直讀取次數,物理讀次數,物理寫次數以及運算在一行資料上耗費的執行時間,如果沒有指定該提示,就不會有A-Rows,A-Time,Buffers這三列資訊。
二、獲取某條指定語句的執行計劃
複製程式碼 程式碼如下:
SQL> select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100);
COUNT(*)
----------
9
-- 透過v$sql檢視查詢到sql語句的SQL_ID和CHILD_NUMBER
SQL> select sql_id,child_number,sql_text from v$sql
2 where sql_text like '%select /*+ gather_plan_statistics */ count(*)%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
5qxmkvh40yw0p 0 select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100)
bqjrnskvpv51n 0 select sql_id,child_number,sql_text from v$sql where sql_text like '%select /*+ gather_plan_statisti
cs */ count(*)%'
-- 獲取對應的執行計劃
SQL> select * from table(dbms_xplan.display_cursor('5qxmkvh40yw0p',0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 5qxmkvh40yw0p, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from hr.employees where
department_id in (90, 100)
Plan hash value: 4167091351
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
| 2 | INLIST ITERATOR | | 1 | | 9 |00:00:00.01 | 2 |
|* 3 | INDEX RANGE SCAN| EMP_DEPARTMENT_IX | 2 | 9 | 9 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("DEPARTMENT_ID"=90 OR "DEPARTMENT_ID"=100))
21 rows selected.
三、透過format引數定製執行計劃輸出資訊
複製程式碼 程式碼如下:
-- 使用ALL來顯示解釋計劃的全部資訊
SQL> explain plan for
2 select * from emp e, dept d
3 where e.deptno = d.deptno
4 and e.ename = 'JONES' ;
Explained.
SQL> select * from table(dbms_xplan.display(format=>'ALL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / E@SEL$1
4 - SEL$1 / D@SEL$1
5 - SEL$1 / D@SEL$1
Predicate Information (identified by operation id):
3 - filter("E"."ENAME"='JONES')
4 - access("E"."DEPTNO"="D"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
"E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
"E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
"D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
2 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
"E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
"E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
"D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
43 rows selected.
-- 去除執行計劃上的位元組數和成本統計資訊
SQL> select empno, ename from emp e, dept d
2 where e.deptno = d.deptno
3 and e.ename = 'JONES' ;
EMPNO ENAME
---------- ----------
7566 JONES
SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'ALLSTATS LAST -COST -BYTES'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3mypf7d6npa97, child number 1
-------------------------------------
select empno, ename from emp e, dept d where e.deptno = d.deptno and
e.ename = 'JONES'
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("E"."ENAME"='JONES' AND "E"."DEPTNO" IS NOT NULL))
19 rows selected.
-- 另一種選項,窺視繫結變數的值,非常方便!!
SQL> variable v_empno number
SQL> exec :v_empno := 7566 ;
PL/SQL procedure successfully completed.
SQL> select * from emp where empno = :v_empno ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'+PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 9q17w9umt58m7, child number 0
-------------------------------------
select * from emp where empno = :v_empno
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :V_EMPNO (NUMBER): 7566
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
2 - access("EMPNO"=:V_EMPNO)
24 rows selected.
-- 並行查詢資訊篩選
SQL> select /*+ parallel(d, 4) parallel (e, 4) */
2 d.dname, avg(e.sal), max(e.sal)
3 from dept d, emp e
4 where d.deptno = e.deptno
5 group by d.dname
6 order by max(e.sal), avg(e.sal) desc;
DNAME AVG(E.SAL) MAX(E.SAL)
-------------- ---------- ----------
SALES 1566.66667 2850
RESEARCH 2175 3000
ACCOUNTING 2916.66667 5000
SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL -BYTES -COST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID gahr597f78j0d, child number 0
-------------------------------------
select /*+ parallel(d, 4) parallel (e, 4) */ d.dname, avg(e.sal),
max(e.sal) from dept d, emp e where d.deptno = e.deptno group by
d.dname order by max(e.sal), avg(e.sal) desc
Plan hash value: 3078011448
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 4 | 00:00:01 | Q1,04 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 4 | 00:00:01 | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 4 | 00:00:01 | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 4 | 00:00:01 | Q1,03 | P->P | RANGE |
| 6 | HASH GROUP BY | | 4 | 00:00:01 | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 14 | 00:00:01 | Q1,03 | PCWP | |
| 8 | PX SEND HASH | :TQ10002 | 14 | 00:00:01 | Q1,02 | P->P | HASH |
|* 9 | HASH JOIN BUFFERED | | 14 | 00:00:01 | Q1,02 | PCWP | |
| 10 | PX RECEIVE | | 4 | 00:00:01 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10000 | 4 | 00:00:01 | Q1,00 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 4 | 00:00:01 | Q1,00 | PCWC | |
|* 13 | TABLE ACCESS FULL| DEPT | 4 | 00:00:01 | Q1,00 | PCWP | |
| 14 | PX RECEIVE | | 14 | 00:00:01 | Q1,02 | PCWP | |
| 15 | PX SEND HASH | :TQ10001 | 14 | 00:00:01 | Q1,01 | P->P | HASH |
| 16 | PX BLOCK ITERATOR | | 14 | 00:00:01 | Q1,01 | PCWC | |
|* 17 | TABLE ACCESS FULL| EMP | 14 | 00:00:01 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("D"."DEPTNO"="E"."DEPTNO")
13 - access(:Z>=:Z AND :Z<=:Z) 17 - access(:Z>=:Z AND :Z<=:Z)
38 rows selected.
DBMS_XPLAN是Oracle提供的一個用於檢視SQL計劃,包括執行計劃和解釋計劃的包;在以前檢視SQL執行計劃的時候,我都是使用set autotrace命令,不過現在看來,DBMS_XPLAN包給出了更加簡化的獲取和顯示計劃的方式。
這5個函式分別對應不同的顯示計劃的方式,DBMS_XPLAN包不僅可以獲取解釋計劃,它還可以用來輸出儲存在AWR,SQL除錯集,快取的SQL遊標,以及SQL基線中的語句計劃,實現如上的功能,通常會用到一下5個方法:
1.DISPLAY
2.DISPLAY_AWR
3.DISPLAY_CURSOR
4.DISPLAY_PLAN
5.DISPLAY_SQL_PLAN_BASELINE
6.DISPLAY_SQLSET
下面將重點討論關於DBMS_XPLAN包在解釋計劃和執行計劃上的應用。
來看一個經常使用的檢視某條語句的解釋計劃示例:
複製程式碼 程式碼如下:
SQL> explain plan for select * from scott.emp;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
-- 對應autotrace實現
SQL> set autotrace traceonly explain
上例中使用了dbms_xplan.display方法來顯示PLAN_TABLE中儲存的解釋計劃,如果想要顯示執行計劃,就需要使用到DMBS_XPLAN.DISPLAY_CURSOR方法了,DMBS_XPLAN.DISPLAY_CURSOR呼叫簽名:
複製程式碼 程式碼如下:
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
sql_id表示儲存在cursor cache中的SQL語句的id,child_number用於指示快取sql語句計劃的子id,format引數用於控制包含在輸出中的資訊型別,官檔的引數如下:
1.BASIC: 顯示最少的資訊,只包括操作型別,ID名稱和選項。
2.TYPICAL: 預設值,顯示相關資訊以及某些附加的顯示選項,如分割槽和併發使用等。
3.SERIAL: 與TYPICAL型別相似,區別是它不包括併發的資訊,即使是並行執行的計劃。
4.ALL: 顯示最多的資訊,包含了TYPICAL的全部以及更多的附加資訊,如別名和遠端呼叫等。
除了以上的基本的四種輸出格式外,format還有一些附加的選項可用於定製化輸出行為,使用中可以透過逗號和空格分隔來宣告多個關鍵字,同時可以使用”+”和”-”符號來包含或排除相應的顯示元素,這些附加的選項在官檔中也有記載:
1.ROWS – 顯示被最佳化器估算的記錄的行號
2.BYTES – 顯示最佳化器估算的位元組數
3.COST – 顯示最佳化器計算的成本資訊
4.PARTITION – 顯示分割槽的分割資訊
5.PARALLEL – 顯示並行執行資訊
6.PREDICATE – 顯示謂語
7.PROJECTION – 顯示列投影部分(每一行的那些列被傳遞給其父列已經這些列的大小)
8.ALIAS – 顯示查詢塊名稱已經物件別名
9.REMOTE – 顯示分散式查詢資訊
10.NOTE – 顯示註釋
11.IOSTATS – 顯示遊標執行的IO統計資訊
12.MEMSTATS – 為記憶體密集運算如雜湊聯結,排序,或一些型別的點陣圖運算顯示記憶體管理統計資訊
13.ALLSTATS – 與'IOSTATS MEMSTATS'等價
14.LAST – 顯示最後執行的執行計劃統計資訊,預設顯示為ALL型別,並且可以累積。
以上的引數同樣適用於解釋計劃的display方法。
示例部分:
一、使用display_cursor方法檢視最近一條語句的執行計劃
複製程式碼 程式碼如下:
SQL> select /*+ gather_plan_statistics */ count(*) from scott.emp;
COUNT(*)
----------
14
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID f9qyz8s3c2c02, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from scott.emp
Plan hash value: 2937609675
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| PK_EMP | 1 | 14 | 14 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------
14 rows selected.
使用dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')時,將sql_id和child_number設定成null,表示獲取上一條執行語句的執行計劃;注意上面的例子中一定要指定gather_plan_statistics提示或者手動設定資料庫STATISTICS_LEVEL引數為ALL來使得其抓取行資料來源的執行統計資訊,這些資訊包括行數,一直讀取次數,物理讀次數,物理寫次數以及運算在一行資料上耗費的執行時間,如果沒有指定該提示,就不會有A-Rows,A-Time,Buffers這三列資訊。
二、獲取某條指定語句的執行計劃
複製程式碼 程式碼如下:
SQL> select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100);
COUNT(*)
----------
9
-- 透過v$sql檢視查詢到sql語句的SQL_ID和CHILD_NUMBER
SQL> select sql_id,child_number,sql_text from v$sql
2 where sql_text like '%select /*+ gather_plan_statistics */ count(*)%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
5qxmkvh40yw0p 0 select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100)
bqjrnskvpv51n 0 select sql_id,child_number,sql_text from v$sql where sql_text like '%select /*+ gather_plan_statisti
cs */ count(*)%'
-- 獲取對應的執行計劃
SQL> select * from table(dbms_xplan.display_cursor('5qxmkvh40yw0p',0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 5qxmkvh40yw0p, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from hr.employees where
department_id in (90, 100)
Plan hash value: 4167091351
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
| 2 | INLIST ITERATOR | | 1 | | 9 |00:00:00.01 | 2 |
|* 3 | INDEX RANGE SCAN| EMP_DEPARTMENT_IX | 2 | 9 | 9 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("DEPARTMENT_ID"=90 OR "DEPARTMENT_ID"=100))
21 rows selected.
三、透過format引數定製執行計劃輸出資訊
複製程式碼 程式碼如下:
-- 使用ALL來顯示解釋計劃的全部資訊
SQL> explain plan for
2 select * from emp e, dept d
3 where e.deptno = d.deptno
4 and e.ename = 'JONES' ;
Explained.
SQL> select * from table(dbms_xplan.display(format=>'ALL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / E@SEL$1
4 - SEL$1 / D@SEL$1
5 - SEL$1 / D@SEL$1
Predicate Information (identified by operation id):
3 - filter("E"."ENAME"='JONES')
4 - access("E"."DEPTNO"="D"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
"E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
"E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
"D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
2 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
"E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
"E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
"D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
43 rows selected.
-- 去除執行計劃上的位元組數和成本統計資訊
SQL> select empno, ename from emp e, dept d
2 where e.deptno = d.deptno
3 and e.ename = 'JONES' ;
EMPNO ENAME
---------- ----------
7566 JONES
SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'ALLSTATS LAST -COST -BYTES'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3mypf7d6npa97, child number 1
-------------------------------------
select empno, ename from emp e, dept d where e.deptno = d.deptno and
e.ename = 'JONES'
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("E"."ENAME"='JONES' AND "E"."DEPTNO" IS NOT NULL))
19 rows selected.
-- 另一種選項,窺視繫結變數的值,非常方便!!
SQL> variable v_empno number
SQL> exec :v_empno := 7566 ;
PL/SQL procedure successfully completed.
SQL> select * from emp where empno = :v_empno ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'+PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 9q17w9umt58m7, child number 0
-------------------------------------
select * from emp where empno = :v_empno
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :V_EMPNO (NUMBER): 7566
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
2 - access("EMPNO"=:V_EMPNO)
24 rows selected.
-- 並行查詢資訊篩選
SQL> select /*+ parallel(d, 4) parallel (e, 4) */
2 d.dname, avg(e.sal), max(e.sal)
3 from dept d, emp e
4 where d.deptno = e.deptno
5 group by d.dname
6 order by max(e.sal), avg(e.sal) desc;
DNAME AVG(E.SAL) MAX(E.SAL)
-------------- ---------- ----------
SALES 1566.66667 2850
RESEARCH 2175 3000
ACCOUNTING 2916.66667 5000
SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL -BYTES -COST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID gahr597f78j0d, child number 0
-------------------------------------
select /*+ parallel(d, 4) parallel (e, 4) */ d.dname, avg(e.sal),
max(e.sal) from dept d, emp e where d.deptno = e.deptno group by
d.dname order by max(e.sal), avg(e.sal) desc
Plan hash value: 3078011448
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 4 | 00:00:01 | Q1,04 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 4 | 00:00:01 | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 4 | 00:00:01 | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 4 | 00:00:01 | Q1,03 | P->P | RANGE |
| 6 | HASH GROUP BY | | 4 | 00:00:01 | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 14 | 00:00:01 | Q1,03 | PCWP | |
| 8 | PX SEND HASH | :TQ10002 | 14 | 00:00:01 | Q1,02 | P->P | HASH |
|* 9 | HASH JOIN BUFFERED | | 14 | 00:00:01 | Q1,02 | PCWP | |
| 10 | PX RECEIVE | | 4 | 00:00:01 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10000 | 4 | 00:00:01 | Q1,00 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 4 | 00:00:01 | Q1,00 | PCWC | |
|* 13 | TABLE ACCESS FULL| DEPT | 4 | 00:00:01 | Q1,00 | PCWP | |
| 14 | PX RECEIVE | | 14 | 00:00:01 | Q1,02 | PCWP | |
| 15 | PX SEND HASH | :TQ10001 | 14 | 00:00:01 | Q1,01 | P->P | HASH |
| 16 | PX BLOCK ITERATOR | | 14 | 00:00:01 | Q1,01 | PCWC | |
|* 17 | TABLE ACCESS FULL| EMP | 14 | 00:00:01 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("D"."DEPTNO"="E"."DEPTNO")
13 - access(:Z>=:Z AND :Z<=:Z) 17 - access(:Z>=:Z AND :Z<=:Z)
38 rows selected.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31397003/viewspace-2137060/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle dbms_xplan獲取執行計劃Oracle
- Oracle執行計劃詳解Oracle
- DBMS_XPLAN檢視執行計劃
- 用DBMS_XPLAN來展示執行計劃
- 執行計劃詳解
- Explain執行計劃詳解AI
- MSSQLSERVER執行計劃詳解SQLServer
- 執行計劃變化的處理
- 使用dbms_xplan包來獲得sql語句的執行計劃SQL
- MySQL Explain執行計劃 - 詳解MySqlAI
- mysql explain 執行計劃詳解MySqlAI
- MySQL執行計劃EXPLAIN詳解MySqlAI
- sql的執行計劃 詳解SQL
- 看懂Oracle中的執行計劃Oracle
- oracle中開啟執行計劃Oracle
- oracle執行計劃的使用(EXPLAIN)OracleAI
- oracle中執行計劃中的cardinalityOracle
- ORACLE執行計劃Oracle
- MySQL explain執行計劃詳細解釋MySqlAI
- 萬字長文詳解HiveSQL執行計劃HiveSQL
- Oracle執行計劃Explain Plan 如何使用OracleAI
- oracle 固定執行計劃Oracle
- Oracle sql執行計劃OracleSQL
- 配置oracle 解釋執行計劃--explain planOracleAI
- oracle sqlprofile 固定執行計劃,並遷移執行計劃OracleSQL
- Hive底層原理:explain執行計劃詳解HiveAI
- 使用Oracle Hint提示來更改執行計劃Oracle
- SQL效能的度量 - 透過explain和dbms_xplan包分析執行計劃SQLAI
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 【Oracle】-【索引-HINT,執行計劃】-帶HINT的索引執行計劃Oracle索引
- 執行計劃__獲取方法、檢視執行順序、統計資訊詳解
- oracle固定執行計劃--sqlprofileOracleSQL
- Oracle 索引和執行計劃Oracle索引
- Oracle閱讀執行計劃Oracle
- oracle執行計劃相關Oracle
- oracle 執行計劃變更Oracle
- 【優化】Oracle 執行計劃優化Oracle
- oracle 執行計劃設定Oracle