解释计划
语句EXPLAIN PLAN 用来显示优化器为SQL语句选择的执行计划。将估计的信息称为解释计划输出，把实际信息称为执行计划输出。以此区分实际的和预期的执行计划。
使用解释计划
当使用EXPLAIN来为查询生成预期的执行计划时，输出将包括以下几种：
SQL语句中所引用到的每一张表。访问每张表所用的方法。每一对需要联结的数据源所用的联结方法。按次序列出的所有需要完成的运算。计划中各步骤的谓语信息列表。对于每个运算，估计出该步骤所要操作的数据行数和字节数。对于每个运算，计算出成本值。如果适合，所访问的分区信息。如果适用，并行执行的相关信息。
hr@orclpdb1:orclcdb> explain plan for2 select e.last_name || ', '||e.first_name as full_name,3 e.phone_number, e.email, e.department_id,4 d.department_name, c.country_name,l.city,l.state_province,5 r.region_name6 from hr.employees e, hr.departments d, hr.countries c,7 hr.locations l, hr.regions r8 where e.department_id = d.department_id9and d.location_id = l.location_id10and l.country_id = c.country_id11and c.region_id = r.region_id;Explained.hr@orclpdb1:orclcdb> select * from table(dbms_xplan.display(format=>'BASIC+COST+PREDICATE'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------Error: format 'BASIC+COST+PREDICATE' not valid for DBMS_XPLAN.DISPLAY()1 row selected.hr@orclpdb1:orclcdb>
1.2 PLAN_TABLE
解释计划输出中所看到的信息是由EXPLAIN PLAN命令生成并默认存储在表PLAN_TABLE中的。AUTOTRACE命令从所提供的dbms_xplan包中调用display函数来自动生成输出。当使用EXPLAIN PLAN命令时你必须手工执行查询。
PLAN_TABLE表
hr@orclpdb1:orclcdb> desc plan_tableNameNull? Type----------------------------------------------------- -------- ------------------------------------STATEMENT_ID VARCHAR2(30)PLAN_IDNUMBERTIMESTAMP DATEREMARKSVARCHAR2(4000)OPERATION VARCHAR2(30)OPTIONSVARCHAR2(255)OBJECT_NODE VARCHAR2(128)OBJECT_OWNER VARCHAR2(128)OBJECT_NAME VARCHAR2(128)OBJECT_ALIAS VARCHAR2(261)OBJECT_INSTANCE NUMBER(38)OBJECT_TYPE VARCHAR2(30)OPTIMIZER VARCHAR2(255)SEARCH_COLUMNS NUMBERID NUMBER(38)PARENT_ID NUMBER(38)DEPTH NUMBER(38)POSITIONNUMBER(38)COST NUMBER(38)CARDINALITY NUMBER(38)BYTES NUMBER(38)OTHER_TAG VARCHAR2(255)PARTITION_START VARCHAR2(255)PARTITION_STOP VARCHAR2(255)PARTITION_ID NUMBER(38)OTHER LONGOTHER_XML CLOBDISTRIBUTION VARCHAR2(30)CPU_COSTNUMBER(38)IO_COSTNUMBER(38)TEMP_SPACE NUMBER(38)ACCESS_PREDICATESVARCHAR2(4000)FILTER_PREDICATESVARCHAR2(4000)PROJECTION VARCHAR2(4000)TIME NUMBER(38)QBLOCK_NAME VARCHAR2(128)hr@orclpdb1:orclcdb>
1.3 分解计划
在学习如何阅读计划输出时，理解计划是如何组织的非常重要。
只有一个子运算的运算可以分为以下3类：
加工运算 从子运算接收一个数据行集并经过加工以后传递给其父运算。传递运算 只是起传递的作用而不对来自子运算的数据做任何修改或加工。它们基本上是用来确定某个运算的特性。迭代运算 表示子运算要多次执行。
1.4 导致解释计划未达目的的原因
使用解释计划时有以下3点可能导致计划输出与实际执行计划不一致，请谨记：
解释计划是基于你使用它的时候的环境来生成的。解释计划不考虑绑定变量的数据类型。解释计划不“窥视”绑定变量的值。EXPLAIN PLAN只显示原始计划而并不显示最终的计划。
1.5 阅读计划
有3种途径有助于你阅读和理解所有计划：
学会识别和分割父子组。掌握计划中运算执行的顺序。学会以叙述的形式阅读计划。
2 执行计划
2.1 查看最近生成的SQL语句
scott@orclpdb1:orclcdb> select /*recentsql*/ sql_id,child_number,2 hash_value,address,executions,sql_text3 from v$sql4 where parsing_user_id = (select user_id5 from all_users6where username = 'SCOTT')7 and command_type in (2,3,6,7,189)8 and UPPER(sql_text) not like UPPER('%recentsql%');SQL_ID CHILD_NUMBER HASH_VALUE ADDRESSEXECUTIONS------------- ------------ ---------- ---------------- ----------SQL_TEXT----------------------------------------------------------------------------------------------------711b9thj3s4ug 2 574362447 0000000072F829B83SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O2") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("O2"."TYPE#"=88) THEN 1 ELSE 0 END)AS C3 FROM "SYS"."OBJ$" SAMPLE BLOCK(32.76, 8) SEED(2) "O2" WHERE ("O2"."TYPE#"=88)) innerQuery8h0w3cbh1sj17 0 3759948839 0000000065ED5AC84SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "DO") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("DO"."TYPE#"=92) THEN 1 ELSE 0 END)AS C3 FROM "SYS"."OBJ$" SAMPLE BLOCK(32.76, 8) SEED(2) "DO" WHERE ("DO"."TYPE#"=92)) innerQuerycvxurqu0r4p9s 0 2171753784 0000000063190D202SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("O"."LINKNAME" IS NULL) AND ("O"."NAME"<>'_NEXT_OBJECT') AND ("O"."NAME"<>'_default_auditing_options_') THEN 1 ELSE 0 END) AS C3 FROM "SYS"."OBJ$" SAMPLE BLOCK(32.76, 8) SEED(1) "O" WHERE ("O"."LINKNAME" IS NULL)) innerQuery2fnu4swc94x56 0 412251302 0000000065D7BF082SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("O"."TYPE#"=9) THEN 1 ELSE 0 END) ASC3 FROM "SYS"."OBJ$" SAMPLE BLOCK(34.0704, 8) SEED(3) "O" WHERE ("O"."TYPE#"=9)) innerQuery1vcptyw64539d 0 205688109 000000006D901D104SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "IO") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("IO"."TYPE#"=2) THEN 1 ELSE 0 END)AS C3 FROM "SYS"."OBJ$" SAMPLE BLOCK(34.0704, 8) SEED(3) "IO" WHERE ("IO"."TYPE#"=2)) innerQuerya85aszwrrx49p 0 796823861 000000006BEE04983SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "OA") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("OA"."PRIVILEGE#"=26) THEN 1 ELSE 0END) AS C3 FROM "SYS"."OBJAUTH$" "OA" WHERE ("OA"."PRIVILEGE#"=26)) innerQuerydfrr3t49vdg23 0 330742851 000000006BA7A5E82SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("O"."TYPE#"=13) THEN 1 ELSE 0 END) AS C3 FROM "SYS"."OBJ$" SAMPLE BLOCK(32.76, 8) SEED(1) "O" WHERE ("O"."TYPE#"=13)) innerQuery4brf4vrw4tman 0 4165782868 0000000065ABB8804SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "DO") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("DO"."TYPE#"=92) THEN 1 ELSE 0 END)AS C3 FROM "SYS"."OBJ$" SAMPLE BLOCK(34.0704, 8) SEED(3) "DO" WHERE ("DO"."TYPE#"=92)) innerQuerygtcj3hs72xnb0 0 237949280 000000006DBCF5A02SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("O"."TYPE#"=13) THEN 1 ELSE 0 END) AS C3 FROM "SYS"."OBJ$" SAMPLE BLOCK(32.76, 8) SEED(2) "O" WHERE ("O"."TYPE#"=13)) innerQuery0j5t5v01p1u1f 0 55633966 0000000065599EB82SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("O"."LINKNAME" IS NULL) AND ("O"."NAME"<>'_NEXT_OBJECT') AND ("O"."NAME"<>'_default_auditing_options_') THEN 1 ELSE 0 END) AS C3 FROM "SYS"."OBJ$" SAMPLE BLOCK(32.76, 8) SEED(2) "O" WHERE ("O"."LINKNAME" IS NULL)) innerQuerygrqh1qs9ajypn 2 313064116 000000006F23C3F83SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O2") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("O2"."TYPE#"=88) THEN 1 ELSE 0 END)AS C3 FROM "SYS"."OBJ$" SAMPLE BLOCK(32.76, 8) SEED(1) "O2" WHERE ("O2"."TYPE#"=88)) innerQuery64mytapbgu243 0 1459423363 00000000659558D04SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "IO") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("IO"."TYPE#"=2) THEN 1 ELSE 0 END)AS C3 FROM "SYS"."OBJ$" SAMPLE BLOCK(32.76, 8) SEED(1) "IO" WHERE ("IO"."TYPE#"=2)) innerQuery2hbqfbtu1a9qj 0 1947543249 00000000727933803SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "OBJAUTH$")*/ 1 AS C1 FROM "SYS"."OBJAUTH$" "OBJAUTH$" WHERE ("OBJAUTH$"."PRIVILEGE#"=9 OR "OBJAUTH$"."PRIVILEGE#"=17)) innerQuery2hkj0am2paatk 0 3310693170 0000000068338768 13SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") */ 1 AS C1 FROM "SYS"."X$KZSRO" "X$KZSRO" WHERE ("X$KZSRO"."CON_ID"=0 OR "X$KZSRO"."CON_ID"=3)) innerQuery599a02kw4qd73 0 3091936483 000000006B6BA2182SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("O"."TYPE#"=9) THEN 1 ELSE 0 END) ASC3 FROM "SYS"."OBJ$" SAMPLE BLOCK(32.76, 8) SEED(2) "O" WHERE ("O"."TYPE#"=9)) innerQuery2ab8q85k5uqkd 2 1683839565 0000000068F436A83SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O2") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("O2"."TYPE#"=88) THEN 1 ELSE 0 END)AS C3 FROM "SYS"."OBJ$" SAMPLE BLOCK(34.0704, 8) SEED(3) "O2" WHERE ("O2"."TYPE#"=88)) innerQuery5vmt9nkkpkr7f 0 2774097134 0000000071BCA6704SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "DO") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("DO"."TYPE#"=92) THEN 1 ELSE 0 END)AS C3 FROM "SYS"."OBJ$" SAMPLE BLOCK(32.76, 8) SEED(1) "DO" WHERE ("DO"."TYPE#"=92)) innerQuery8tjsfvqd872sr 1 2592312087 00000000659411D03SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "OA") */ 1AS C1 FROM "SYS"."OBJAUTH$" "OA" WHERE ("OA"."PRIVILEGE#"=26)) innerQuery7nqh3a78933m5 0 3499200101 000000006DAADAB02SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("O"."TYPE#"=9) THEN 1 ELSE 0 END) ASC3 FROM "SYS"."OBJ$" SAMPLE BLOCK(32.76, 8) SEED(1) "O" WHERE ("O"."TYPE#"=9)) innerQueryat7grrzkbr4rh 1 3837498096 000000006B5C2C883SELECT LOWER(USER || '@' || SYS_CONTEXT('userenv', 'con_name') || ':' || SYS_CONTEXT('userenv', 'instance_name')) X FROM dual6u5zqzz2nm55c 0 3309933740 000000006B4273403SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL2k4q6d465bm0n 0 206949396 000000006BBA5CC02SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel OPT_ESTIMATE(@"innerQuery", TABLE, "X$KZSRO#1", SCALE_ROWS=3) */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "OA#0") */ 1 AS C1 FROM "SYS"."X$KZSRO" "X$KZSRO#1", "SYS"."OBJAUTH$" "OA#0" WHERE ("OA#0"."PRIVILEGE#"=12 OR "OA#0"."PRIVILEGE#"=26) AND ("OA#0"."GRANTEE#"="X$KZSRO#1"."KZSROROL") AND ("X$KZSRO#1"."CON_ID"=0 OR "X$KZSRO#1"."CON_ID"=3)) innerQuery8pnc9umudrrdw 0 4108049852 0000000072792B202SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "OA") */ 1AS C1 FROM "SYS"."OBJAUTH$" "OA" WHERE ("OA"."PRIVILEGE#"=12 OR "OA"."PRIVILEGE#"=26)) innerQuery4k6u4npmqms0m 0 1734991891 000000007163B3C84SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(C1,0), NVL(C2,0), NVL(C3,0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "IO") */ 4294967295 AS C1, COUNT(*) AS C2, SUM(CASE WHEN ("IO"."TYPE#"=2) THEN 1 ELSE 0 END)AS C3 FROM "SYS"."OBJ$" SAMPLE BLOCK(32.76, 8) SEED(2) "IO" WHERE ("IO"."TYPE#"=2)) innerQuery24 rows selected.scott@orclpdb1:orclcdb>
2.2 查看相关执行计划
使用dbms_xplan.display_cursor函数
scott@orclpdb1:orclcdb> select /*+gather_plan_statistics*/ empno,ename from SCOTT.emp where ename = 'KING';EMPNO ENAME---------- ----------7839 KING1 row selected.scott@orclpdb1:orclcdb> set serveroutput offscott@orclpdb1:orclcdb> scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID 5qgz1p0cut7mx, child number 0BEGIN DBMS_OUTPUT.DISABLE; END;NOTE: cannot fetch plan for SQL_ID: 5qgz1p0cut7mx, CHILD_NUMBER: 0Please verify value of SQL_ID and CHILD_NUMBER;It could also be that the plan is no longer in cursor cache (check v$sql_plan)8 rows selected.scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID 91kkkbx7bnw9j, child number 1-------------------------------------select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATSLAST'))Plan hash value: 3713220770---------------------------------------------------------------------| Id | Operation| Name | E-Rows |---------------------------------------------------------------------| 0 | SELECT STATEMENT || || 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |2 |---------------------------------------------------------------------Note------ statistics feedback used for this statement- Warning: basic plan statistics not available. These are only collected when:* hint 'gather_plan_statistics' is used for the statement or* parameter 'statistics_level' is set to 'ALL', at session or system level21 rows selected.scott@orclpdb1:orclcdb>
使用没有gather_plan_statistics提示的dbms_xplan.display_cursor函数
scott@orclpdb1:orclcdb> select ename from SCOTT.emp where ename = 'KING';ENAME----------KING1 row selected.scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID 9ygfkgffb7nhh, child number 0-------------------------------------select ename from SCOTT.emp where ename = 'KING'Plan hash value: 3956160932-------------------------------------------| Id | Operation | Name | E-Rows |-------------------------------------------| 0 | SELECT STATEMENT || ||* 1 | TABLE ACCESS FULL| EMP |1 |-------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("ENAME"='KING')Note------ Warning: basic plan statistics not available. These are only collected when:* hint 'gather_plan_statistics' is used for the statement or* parameter 'statistics_level' is set to 'ALL', at session or system level24 rows selected.scott@orclpdb1:orclcdb>
可以看到，出现了一个警告信息表明无法获取计划统计信息，并高速你如何来收集这些信息。
2.3 收集执行计划统计信息
v$SQL_PLAN_STATISTICS_ALL视图描述
scott@orclpdb1:orclcdb> desc v$sql_plan_statistics_allNameNull? Type----------------------------------------------------- -------- ------------------------------------ADDRESSRAW(8)HASH_VALUE NUMBERSQL_ID VARCHAR2(13)PLAN_HASH_VALUE NUMBERFULL_PLAN_HASH_VALUE NUMBERCHILD_ADDRESSRAW(8)CHILD_NUMBER NUMBERTIMESTAMP DATEOPERATION VARCHAR2(30)OPTIONSVARCHAR2(30)OBJECT_NODE VARCHAR2(40)OBJECT#NUMBEROBJECT_OWNER VARCHAR2(128)OBJECT_NAME VARCHAR2(128)OBJECT_ALIAS VARCHAR2(261)OBJECT_TYPE VARCHAR2(20)OPTIMIZER VARCHAR2(20)ID NUMBERPARENT_ID NUMBERDEPTH NUMBERPOSITIONNUMBERSEARCH_COLUMNS NUMBERCOST NUMBERCARDINALITY NUMBERBYTES NUMBEROTHER_TAG VARCHAR2(35)PARTITION_START VARCHAR2(64)PARTITION_STOP VARCHAR2(64)PARTITION_ID NUMBEROTHER VARCHAR2(4000)DISTRIBUTION VARCHAR2(20)CPU_COSTNUMBERIO_COSTNUMBERTEMP_SPACE NUMBERACCESS_PREDICATESVARCHAR2(4000)FILTER_PREDICATESVARCHAR2(4000)PROJECTION VARCHAR2(4000)TIME NUMBERQBLOCK_NAME VARCHAR2(128)REMARKSVARCHAR2(4000)OTHER_XML CLOBEXECUTIONS NUMBERLAST_STARTS NUMBERSTARTS NUMBERLAST_OUTPUT_ROWS NUMBEROUTPUT_ROWS NUMBERLAST_CR_BUFFER_GETS NUMBERCR_BUFFER_GETS NUMBERLAST_CU_BUFFER_GETS NUMBERCU_BUFFER_GETS NUMBERLAST_DISK_READS NUMBERDISK_READS NUMBERLAST_DISK_WRITES NUMBERDISK_WRITES NUMBERLAST_ELAPSED_TIMENUMBERELAPSED_TIME NUMBERPOLICY VARCHAR2(10)ESTIMATED_OPTIMAL_SIZE NUMBERESTIMATED_ONEPASS_SIZE NUMBERLAST_MEMORY_USED NUMBERLAST_EXECUTION VARCHAR2(10)LAST_DEGREE NUMBERTOTAL_EXECUTIONS NUMBEROPTIMAL_EXECUTIONSNUMBERONEPASS_EXECUTIONSNUMBERMULTIPASSES_EXECUTIONS NUMBERACTIVE_TIME NUMBERMAX_TEMPSEG_SIZE NUMBERLAST_TEMPSEG_SIZENUMBERCON_ID NUMBERCON_DBIDNUMBERscott@orclpdb1:orclcdb>
2.4 标识SQL语句以便以后取回计划
scott@orclpdb1:orclcdb> scott@orclpdb1:orclcdb> select /*KM-EMPTEST1*/ empno,ename2 from emp3 where job = 'MANAGER';EMPNO ENAME---------- ----------7566 JONES7698 BLAKE7782 CLARK3 rows selected.scott@orclpdb1:orclcdb> select sql_id,child_number,sql_text2 from v$sql3 where sql_text like '%KM-EMPTEST1%';SQL_ID CHILD_NUMBER------------- ------------SQL_TEXT----------------------------------------------------------------------------------------------------dcwkjwrc1ssak 0select sql_id,child_number,sql_text from v$sqlwhere sql_text like '%KM-EMPTEST1%'7m34m0utxpy4a 0select /*KM-EMPTEST1*/ empno,ename from empwhere job = 'MANAGER'2 rows selected.scott@orclpdb1:orclcdb> scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display_cursor('7m34m0utxpy4a',0,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID 7m34m0utxpy4a, child number 0-------------------------------------select /*KM-EMPTEST1*/ empno,ename from empwhere job = 'MANAGER'Plan hash value: 3956160932-------------------------------------------| Id | Operation | Name | E-Rows |-------------------------------------------| 0 | SELECT STATEMENT || ||* 1 | TABLE ACCESS FULL| EMP |3 |-------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("JOB"='MANAGER')Note------ Warning: basic plan statistics not available. These are only collected when:* hint 'gather_plan_statistics' is used for the statement or* parameter 'statistics_level' is set to 'ALL', at session or system level25 rows selected.scott@orclpdb1:orclcdb>
