[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次计算，实际上当时写脚本时自己并没有认真理解许多参数。
--以后看书看文档要注意细节。
