[20151021]理解dbms_xplan.display_cursor的format引數all.txt
[20151021]理解dbms_xplan.display_cursor的format引數all.txt
--今天才理解dbms_xplan.display_cursor的format引數all，看來看書與看文件不夠仔細。
--我一般看執行計劃使用我自己的指令碼：
$ cat dpcz.sql
set verify off
--select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost partition'));
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline '));
prompt
prompt argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive
prompt
1.測試：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> alter session set statistics_level=all ;
Session altered.
SCOTT@test> select * from table(dbms_xplan.display_cursor('3u9s9tczfvy7w',NULL,'ALL allstats'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3u9s9tczfvy7w, child number 0
-------------------------------------
select * from emp,dept where dept.deptno=emp.deptno
Plan hash value: 844388907
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | | | 7 (100)| | 140 |00:00:00.01 | 100 | | | |
| 1 | MERGE JOIN | | 10 | 14 | 826 | 7 (15)| 00:00:01 | 140 |00:00:00.01 | 100 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 5 | 100 | 3 (0)| 00:00:01 | 40 |00:00:00.01 | 40 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 10 | 5 | | 1 (0)| 00:00:01 | 40 |00:00:00.01 | 20 | | | |
|* 4 | SORT JOIN | | 40 | 14 | 546 | 4 (25)| 00:00:01 | 140 |00:00:00.01 | 60 | 2048 | 2048 | 10/0/0|
| 5 | TABLE ACCESS FULL | EMP | 10 | 14 | 546 | 3 (0)| 00:00:01 | 140 |00:00:00.01 | 60 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
5 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
SCOTT@test> select count(*) from emp,emp,emp,emp,emp;
COUNT(*)
----------
537824
SCOTT@test> select count(*) from emp,emp,emp,emp,emp;
COUNT(*)
----------
537824
SCOTT@test> @dpcz ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4sa10z6dygzjh, child number 0
-------------------------------------
select count(*) from emp,emp,emp,emp,emp
Plan hash value: 1016554931
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11232 (100)| | 1 |00:00:02.55 | 13 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:02.55 | 13 | | | |
| 2 | MERGE JOIN CARTESIAN | | 1 | 537K| 11232 (1)| 00:00:01 | 537K|00:00:02.03 | 13 | | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 38416 | 808 (1)| 00:00:01 | 38416 |00:00:00.15 | 10 | | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 2744 | 62 (0)| 00:00:01 | 2744 |00:00:00.01 | 7 | | | |
| 5 | MERGE JOIN CARTESIAN | | 1 | 196 | 7 (0)| 00:00:01 | 196 |00:00:00.01 | 4 | | | |
| 6 | INDEX FULL SCAN | PK_EMP | 1 | 14 | 1 (0)| 00:00:01 | 14 |00:00:00.01 | 1 | | | |
| 7 | BUFFER SORT | | 14 | 14 | 6 (0)| 00:00:01 | 196 |00:00:00.01 | 3 | 73728 | 73728 | |
| 8 | INDEX FAST FULL SCAN| PK_EMP | 1 | 14 | 0 (0)| | 14 |00:00:00.01 | 3 | | | |
| 9 | BUFFER SORT | | 196 | 14 | 62 (0)| 00:00:01 | 2744 |00:00:00.01 | 3 | 73728 | 73728 | |
| 10 | INDEX FAST FULL SCAN | PK_EMP | 1 | 14 | 0 (0)| | 14 |00:00:00.01 | 3 | | | |
| 11 | BUFFER SORT | | 2744 | 14 | 808 (1)| 00:00:01 | 38416 |00:00:00.05 | 3 | 73728 | 73728 | |
| 12 | INDEX FAST FULL SCAN | PK_EMP | 1 | 14 | 0 (0)| | 14 |00:00:00.01 | 3 | | | |
| 13 | BUFFER SORT | | 38416 | 14 | 11231 (1)| 00:00:01 | 537K|00:00:00.66 | 3 | 73728 | 73728 | |
| 14 | INDEX FAST FULL SCAN | PK_EMP | 1 | 14 | 0 (0)| | 14 |00:00:00.01 | 3 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / EMP_0005@SEL$1
8 - SEL$1 / EMP_0004@SEL$1
10 - SEL$1 / EMP_0003@SEL$1
12 - SEL$1 / EMP_0002@SEL$1
14 - SEL$1 / EMP_0001@SEL$1
36 rows selected.
SCOTT@test> select * from table(dbms_xplan.display_cursor('4sa10z6dygzjh',NULL,'ALL allstats'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4sa10z6dygzjh, child number 0
-------------------------------------
select count(*) from emp,emp,emp,emp,emp
Plan hash value: 1016554931
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | 11232 (100)| | 2 |00:00:05.10 | 26 | | | |
| 1 | SORT AGGREGATE | | 2 | 1 | | | 2 |00:00:05.10 | 26 | | | |
| 2 | MERGE JOIN CARTESIAN | | 2 | 537K| 11232 (1)| 00:00:01 | 1075K|00:00:04.05 | 26 | | | |
| 3 | MERGE JOIN CARTESIAN | | 2 | 38416 | 808 (1)| 00:00:01 | 76832 |00:00:00.30 | 20 | | | |
| 4 | MERGE JOIN CARTESIAN | | 2 | 2744 | 62 (0)| 00:00:01 | 5488 |00:00:00.02 | 14 | | | |
| 5 | MERGE JOIN CARTESIAN | | 2 | 196 | 7 (0)| 00:00:01 | 392 |00:00:00.01 | 8 | | | |
| 6 | INDEX FULL SCAN | PK_EMP | 2 | 14 | 1 (0)| 00:00:01 | 28 |00:00:00.01 | 2 | | | |
| 7 | BUFFER SORT | | 28 | 14 | 6 (0)| 00:00:01 | 392 |00:00:00.01 | 6 | 73728 | 73728 | |
| 8 | INDEX FAST FULL SCAN| PK_EMP | 2 | 14 | 0 (0)| | 28 |00:00:00.01 | 6 | | | |
| 9 | BUFFER SORT | | 392 | 14 | 62 (0)| 00:00:01 | 5488 |00:00:00.01 | 6 | 73728 | 73728 | |
| 10 | INDEX FAST FULL SCAN | PK_EMP | 2 | 14 | 0 (0)| | 28 |00:00:00.01 | 6 | | | |
| 11 | BUFFER SORT | | 5488 | 14 | 808 (1)| 00:00:01 | 76832 |00:00:00.10 | 6 | 73728 | 73728 | |
| 12 | INDEX FAST FULL SCAN | PK_EMP | 2 | 14 | 0 (0)| | 28 |00:00:00.01 | 6 | | | |
| 13 | BUFFER SORT | | 76832 | 14 | 11231 (1)| 00:00:01 | 1075K|00:00:01.31 | 6 | 73728 | 73728 | |
| 14 | INDEX FAST FULL SCAN | PK_EMP | 2 | 14 | 0 (0)| | 28 |00:00:00.01 | 6 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / EMP_0005@SEL$1
8 - SEL$1 / EMP_0004@SEL$1
10 - SEL$1 / EMP_0003@SEL$1
12 - SEL$1 / EMP_0002@SEL$1
14 - SEL$1 / EMP_0001@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
7 - (#keys=0)
9 - (#keys=0)
11 - (#keys=0)
13 - (#keys=0)
--上下對比就明白 ，僅僅執行引數format=>'ALL allstats'，可以發現starts,A-Rows, Buffers，A－Time都乘以2.
--我之所以爭取，因為我還加入了引數last，這樣僅僅以最後1次計算，實際上當時寫指令碼時自己並沒有認真理解許多引數。
--以後看書看文件要注意細節。
--今天才理解dbms_xplan.display_cursor的format引數all，看來看書與看文件不夠仔細。
--我一般看執行計劃使用我自己的指令碼：
$ cat dpcz.sql
set verify off
--select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost partition'));
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline '));
prompt
prompt argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive
prompt
1.測試：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> alter session set statistics_level=all ;
Session altered.
SCOTT@test> select * from table(dbms_xplan.display_cursor('3u9s9tczfvy7w',NULL,'ALL allstats'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3u9s9tczfvy7w, child number 0
-------------------------------------
select * from emp,dept where dept.deptno=emp.deptno
Plan hash value: 844388907
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | | | 7 (100)| | 140 |00:00:00.01 | 100 | | | |
| 1 | MERGE JOIN | | 10 | 14 | 826 | 7 (15)| 00:00:01 | 140 |00:00:00.01 | 100 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 5 | 100 | 3 (0)| 00:00:01 | 40 |00:00:00.01 | 40 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 10 | 5 | | 1 (0)| 00:00:01 | 40 |00:00:00.01 | 20 | | | |
|* 4 | SORT JOIN | | 40 | 14 | 546 | 4 (25)| 00:00:01 | 140 |00:00:00.01 | 60 | 2048 | 2048 | 10/0/0|
| 5 | TABLE ACCESS FULL | EMP | 10 | 14 | 546 | 3 (0)| 00:00:01 | 140 |00:00:00.01 | 60 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
5 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
SCOTT@test> select count(*) from emp,emp,emp,emp,emp;
COUNT(*)
----------
537824
SCOTT@test> select count(*) from emp,emp,emp,emp,emp;
COUNT(*)
----------
537824
SCOTT@test> @dpcz ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4sa10z6dygzjh, child number 0
-------------------------------------
select count(*) from emp,emp,emp,emp,emp
Plan hash value: 1016554931
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11232 (100)| | 1 |00:00:02.55 | 13 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:02.55 | 13 | | | |
| 2 | MERGE JOIN CARTESIAN | | 1 | 537K| 11232 (1)| 00:00:01 | 537K|00:00:02.03 | 13 | | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 38416 | 808 (1)| 00:00:01 | 38416 |00:00:00.15 | 10 | | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 2744 | 62 (0)| 00:00:01 | 2744 |00:00:00.01 | 7 | | | |
| 5 | MERGE JOIN CARTESIAN | | 1 | 196 | 7 (0)| 00:00:01 | 196 |00:00:00.01 | 4 | | | |
| 6 | INDEX FULL SCAN | PK_EMP | 1 | 14 | 1 (0)| 00:00:01 | 14 |00:00:00.01 | 1 | | | |
| 7 | BUFFER SORT | | 14 | 14 | 6 (0)| 00:00:01 | 196 |00:00:00.01 | 3 | 73728 | 73728 | |
| 8 | INDEX FAST FULL SCAN| PK_EMP | 1 | 14 | 0 (0)| | 14 |00:00:00.01 | 3 | | | |
| 9 | BUFFER SORT | | 196 | 14 | 62 (0)| 00:00:01 | 2744 |00:00:00.01 | 3 | 73728 | 73728 | |
| 10 | INDEX FAST FULL SCAN | PK_EMP | 1 | 14 | 0 (0)| | 14 |00:00:00.01 | 3 | | | |
| 11 | BUFFER SORT | | 2744 | 14 | 808 (1)| 00:00:01 | 38416 |00:00:00.05 | 3 | 73728 | 73728 | |
| 12 | INDEX FAST FULL SCAN | PK_EMP | 1 | 14 | 0 (0)| | 14 |00:00:00.01 | 3 | | | |
| 13 | BUFFER SORT | | 38416 | 14 | 11231 (1)| 00:00:01 | 537K|00:00:00.66 | 3 | 73728 | 73728 | |
| 14 | INDEX FAST FULL SCAN | PK_EMP | 1 | 14 | 0 (0)| | 14 |00:00:00.01 | 3 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / EMP_0005@SEL$1
8 - SEL$1 / EMP_0004@SEL$1
10 - SEL$1 / EMP_0003@SEL$1
12 - SEL$1 / EMP_0002@SEL$1
14 - SEL$1 / EMP_0001@SEL$1
36 rows selected.
SCOTT@test> select * from table(dbms_xplan.display_cursor('4sa10z6dygzjh',NULL,'ALL allstats'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4sa10z6dygzjh, child number 0
-------------------------------------
select count(*) from emp,emp,emp,emp,emp
Plan hash value: 1016554931
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | 11232 (100)| | 2 |00:00:05.10 | 26 | | | |
| 1 | SORT AGGREGATE | | 2 | 1 | | | 2 |00:00:05.10 | 26 | | | |
| 2 | MERGE JOIN CARTESIAN | | 2 | 537K| 11232 (1)| 00:00:01 | 1075K|00:00:04.05 | 26 | | | |
| 3 | MERGE JOIN CARTESIAN | | 2 | 38416 | 808 (1)| 00:00:01 | 76832 |00:00:00.30 | 20 | | | |
| 4 | MERGE JOIN CARTESIAN | | 2 | 2744 | 62 (0)| 00:00:01 | 5488 |00:00:00.02 | 14 | | | |
| 5 | MERGE JOIN CARTESIAN | | 2 | 196 | 7 (0)| 00:00:01 | 392 |00:00:00.01 | 8 | | | |
| 6 | INDEX FULL SCAN | PK_EMP | 2 | 14 | 1 (0)| 00:00:01 | 28 |00:00:00.01 | 2 | | | |
| 7 | BUFFER SORT | | 28 | 14 | 6 (0)| 00:00:01 | 392 |00:00:00.01 | 6 | 73728 | 73728 | |
| 8 | INDEX FAST FULL SCAN| PK_EMP | 2 | 14 | 0 (0)| | 28 |00:00:00.01 | 6 | | | |
| 9 | BUFFER SORT | | 392 | 14 | 62 (0)| 00:00:01 | 5488 |00:00:00.01 | 6 | 73728 | 73728 | |
| 10 | INDEX FAST FULL SCAN | PK_EMP | 2 | 14 | 0 (0)| | 28 |00:00:00.01 | 6 | | | |
| 11 | BUFFER SORT | | 5488 | 14 | 808 (1)| 00:00:01 | 76832 |00:00:00.10 | 6 | 73728 | 73728 | |
| 12 | INDEX FAST FULL SCAN | PK_EMP | 2 | 14 | 0 (0)| | 28 |00:00:00.01 | 6 | | | |
| 13 | BUFFER SORT | | 76832 | 14 | 11231 (1)| 00:00:01 | 1075K|00:00:01.31 | 6 | 73728 | 73728 | |
| 14 | INDEX FAST FULL SCAN | PK_EMP | 2 | 14 | 0 (0)| | 28 |00:00:00.01 | 6 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / EMP_0005@SEL$1
8 - SEL$1 / EMP_0004@SEL$1
10 - SEL$1 / EMP_0003@SEL$1
12 - SEL$1 / EMP_0002@SEL$1
14 - SEL$1 / EMP_0001@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
7 - (#keys=0)
9 - (#keys=0)
11 - (#keys=0)
13 - (#keys=0)
--上下對比就明白 ，僅僅執行引數format=>'ALL allstats'，可以發現starts,A-Rows, Buffers，A－Time都乘以2.
--我之所以爭取，因為我還加入了引數last，這樣僅僅以最後1次計算，實際上當時寫指令碼時自己並沒有認真理解許多引數。
--以後看書看文件要注意細節。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1815419/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 引數LOG_ARCHIVE_FORMAT 的格式OracleHiveORM
- Oracle NLS_TIMESTAMP_FORMAT引數OracleORM
- Oracle 10G的log_archive_format引數Oracle 10gHiveORM
- 修改資料庫的NLS_DATE_FORMAT引數資料庫ORM
- jmeter 引數理解JMeter
- 關於python中format佔位符中的 {!} 引數PythonORM
- linux，mtime引數的理解Linux
- 深入理解mysql引數MySql
- JavaScript引數傳遞的深入理解JavaScript
- 引數FAST_START_MTTR_TARGET的理解AST
- 1.5 - Numpy的方法中，axis引數的理解
- linux find depth引數理解Linux
- find命令-mtime引數理解
- 深入理解JVM（三）——配置引數JVM
- flume 寫往hdfs引數理解分析
- optimizer_dynamic_sampling引數的理解
- 初始化引數OPEN_CURSORS的理解加深
- v$session中LAST_CALL_ET引數的理解SessionAST
- 帶你深入理解傳遞引數
- Request 接收引數亂碼原理解析
- 理解spread運算子與rest引數REST
- 淺談對python pandas中 inplace 引數的理解Python
- 你都理解建立執行緒池的引數嗎？執行緒
- innodb_flush_log_at_trx_commit引數的直白理解MIT
- 深入理解RabbitMQ中的prefetch_count引數MQ
- JavaScript引數傳遞中值和引用的一種理解JavaScript
- 隱含引數_minimum_giga_scn的理解
- RAC中對DB引數檔案的簡單理解
- Oracle一些引數的理解 cursor_sharingOracle
- 【Data Pump】理解expdp中的ESTIMATE和ESTIMATE_ONLY引數
- 2、從引數估計的角度理解邏輯迴歸邏輯迴歸
- F5負載均衡器的重要引數理解負載
- 對 “C語言指標變數作為函式引數” 的個人理解C語言指標變數函式
- 通過原始碼理解 Java 執行緒池的核心引數原始碼Java執行緒
- LevelDB原始碼分析：理解Slice實現 - 高效的LevelDB引數物件原始碼物件
- 深度理解Oracle10g中UNDO_RETENTION引數的使用Oracle
- kettle 引數——變數引數和常量引數變數
- C#中的值引數，引用引數及輸出引數C#