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

时间： 2024-10-23 08:45:17

[20151021]理解dbms_xplan.display_cursor的format参数all.txt的相关文章

通过dbms_xplan.display_cursor识别低效的执行计划

dbms_xplan.display_cursor定义: function display_cursor(sql_id           varchar2 default  null,                                      cursor_child_no  integer  default  0,                                      format          varchar2 default  'TYPICAL')

[20150810]10g下dbms_xplan.display_cursor

[20150810]10g下dbms_xplan.display_cursor.txt --执行过的sql语句要查看执行计划要使用dbms_xplan包,我一般写成脚本反复使用,这样快捷方便一些. --我的定义如下: $  cat dpc.sql set verify off select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partitio

[20120918]理解v$sql的exact_matching_signature与force_matching_signature.txt

[20120918]理解v$sql的exact_matching_signature与force_matching_signature.txt     理解v$sql的exact_matching_signature与force_matching_signature,对于使用sql profile以及SPM有重要意义,自己对于这个一直没有很好的理解, 今天做一个测试看看,可能不全面^_^. SQL> select * from v$version ; BANNER ---------------

[20171109]查看隐含参数脚本.txt

[20171109]查看隐含参数脚本.txt --//查看隐含参数的脚本,今天没事修改一下增加查询description字段的内容.也有网友要求提供这个脚本,实际上这些脚本都是自己工作 --//中不断收集整理.网上许多地方都能找到. $ cat hide.sql col name format a40 col description format a66 col session_value format a22 col default_value format a22 col system_va

[20160501]查看包参数脚本.txt

[20160501]查看包参数脚本.txt --我以前写的脚本 SCOTT@book> @ &r/desc_proc sys dbms_stats get_table% INPUT OWNER PACKAGE_NAME OBJECT_NAME sample : @desc_proc sys dbms_stats gather_%_stats OWNER      PACKAGE_NAME         OBJECT_NAME                      SEQUENCE AR

[20131116]12c的EXTENDED VARCHAR2与隐含参数_scalar_type_lob_storage_threshold.txt

[20131116]12c的EXTENDED VARCHAR2与隐含参数_scalar_type_lob_storage_threshold.txt 参考链接:http://space.itpub.net/267265/viewspace-776806/ google查询了一些资料: 发现:SYS@test01p> @hide _scalar_type_lob_storage_threshold;NAME                                     DESCRIPTI

[20171117]参数filesystemio_options.txt

[20171117]参数filesystemio_options.txt --//前几天看别人的awr报表发现设置参数filesystemio_options=setall,问为什么?对方给出一个链接,某某人都是这样设置的, --//自己很无语,我希望对方能提出自己的见解. --//首先给出oracle官方的解析: https://docs.oracle.com/cd/E11882_01/server.112/e41573/os.htm#PFGRF94410 9.1.1.2 FILESYSTEM

[20171105]exp imp buffer参数解析.txt

[20171105]exp imp buffer参数解析.txt oracle官方所给的关于buffer的解释如下: https://docs.oracle.com/cd/A84870_01/doc/server.816/a76955/ch01.htm BUFFER Default: operating system-dependent. See your Oracle operating system-specific documentation to determine the defaul

[20170516]11G use_large_pages参数2.txt

[20170516]11G use_large_pages参数2.txt //前面我提到如果设置use_large_pages=auto.设置页面大小不足时,oracle会oradism经常修改内核参数vm.nr_hugepages. //忘记测试是否在退出后可以收回.链接如下: http://blog.itpub.net/267265/viewspace-2135210/ --//不知道什么回事,以前写的,忘记发了,补上. 1.环境 SYS@book> @ &r/ver1 PORT_STR