DBMS_XPLAN.DISPLAY_CURSOR&DISPLAY

DBMS_XPLAN是Oracle提供的一个用于查看SQL计划，包括执行计划和解释计划的包,很不错的一个功能不仅如此，它还可以用来输出存储在AWR，SQL调试集，缓存的SQL游标，以及SQL基线中的语句计划比如：DISPLAY，DISPLAY_CURSOR,DISPLAY_AWR,DISPLAY_PLAN,DISPLAY_SQL_PLAN_BASELINE,DISPLAY_SQLSET这里简单介绍执行计划，DISPLAY显示plan_table中的内容dbms_xplan.display(table_name   IN VARCHAR2 DEFAULT 'PLAN_TABLE',statement_id IN VARCHAR2 DEFAULT NULL,format       IN VARCHAR2 DEFAULT 'TYPICAL',filter_preds IN VARCHAR2 DEFAULT NULL)RETURN dbms_xplan_type_table PIPELINED;Format choices are:BASIC ..... displays minimum informationTYPICAL ... displays most relevant informationSERIAL .... like TYPICAL but without parallel informationALL ....... displays all informationSQL> create table t1(id int,name varchar2(10));表已创建。SQL> alter table t1 add constraints pk_t1 primary key (id);表已更改。SQL> begin  2  for i in 1..100 loop  3     insert into t1 values(i,'china');  4  end loop;  5  commit;  6  end;  7  /PL/SQL 过程已成功完成。SQL> explain plan for  2  select name from t1 where id=9;已解释。SQL> set pagesize 25SQL> set linesize 121SQL>SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------Plan hash value: 1141790563------------------------------------------------------------------------------------| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |       |     1 |    20 |     0   (0)| 00:0:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    20 |     0   (0)| 00:0:01 ||*  2 |   INDEX UNIQUE SCAN         | PK_T1 |     1 |       |     0   (0)| 00:0:01 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID"=9)已选择 14 行。-- 也可以使用statement_idSQL> explain plan set statement_id ='loge' for  2  select name from t1 where id=9;已解释。SQL> select * from table(dbms_xplan.display(statement_id=>'loge'));PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------------Plan hash value: 1141790563-------------------------------------------------------------------------------------| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |       |     1 |    20 |     0   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    20 |     0   (0)| 00:00:01 ||*  2 |   INDEX UNIQUE SCAN         | PK_T1 |     1 |       |     0   (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID"=9)已选择 14 行。-- dbms_xplan.display_cursor,这个函数从 GV$SQL_PLAN 或者 GV$SQL_PLAN_STATISTICS_ALL获取信息，-- 实际上，这里的视图是library cache中保存的SQL执行计划，如果信息被换出，将无法查看dbms_xplan.display_cursor(sql_id          IN VARCHAR2 DEFAULT NULL,cursor_child_no IN INTEGER DEFAULT 0,format          IN VARCHAR2 DEFAULT 'TYPICAL')RETURN dbms_xplan_type_table PIPELINED; Format Constants，这里的format除了上边的BASIC,TYPICAL,SERIAL,ALL,还支持下面的ALIAS If relevant, shows the "Query Block Name / Object Alias" sectionALLSTATS A shortcut for 'IOSTATS MEMSTATS'BYTES If relevant, shows the number of bytes estimated by the optimizerCOST If relevant, shows optimizer cost informationIOSTATS Assuming that basic plan statistics are collected when SQL statements are executed (either by using the gather_plan_statistics hint or by setting the parameter statistics_level to ALL), this format will show IO statistics for ALL (or only for the LAST as shown below) executions of the cursorLAST By default, plan statistics are shown for all executions of the cursor. The keyword LAST can be specified to see only the statistics for the last executionMEMSTATS Assuming that PGA memory management is enabled (that is, pga_aggregate_target parameter is set to a non 0 value), this format allows to display memory management statistics (for example, execution mode of the operator, how much memory was used, number of bytes spilled to disk, and so on). These statistics only apply to memory intensive operations like hash-joins, sort or some bitmap operatorsNOTE If relevant, shows the note section of the explain planPARALLEL If relevant, shows PX information (distribution method and table queue information)PARTITION If relevant, shows partition pruning informationPREDICATE If relevant, shows the predicate sectionPROJECTION If relevant, shows the projection sectionREMOTE If relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)ROWS If relevant, shows the number of rows estimated by the optimizerRUNSTATS_LAST Same as IOSTATS LAST: displays the runtime stat for the last execution of the cursorRUNSTATS_TOT Same as IOSTATS: displays IO statistics for all executions of the specified cursor--举例：SQL> select /*+ gather_plan_statistics */ name from t1 where id=9;NAME--------------------chinaSQL> select sql_id,child_number,sql_text from v$sql  2  where sql_text like '%gather_plan_statistics%';SQL_ID                     CHILD_NUMBER-------------------------- ------------SQL_TEXT-------------------------------------------------------------------------------------------------------------------------9fv4fgg2cp1d6                         0select sql_id,child_number,sql_text from v$sql where sql_text like '%gather_plan_statistics%'fn50a33auutj0                         0select /* + gather_plan_statistics */ name from t1 where id=94udk8ch96vutj                         0select /*+ gather_plan_statistics */ name from t1 where id=9SQL> select * from table(dbms_xplan.display_cursor('4udk8ch96vutj',0,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------------SQL_ID  4udk8ch96vutj, child number 0-------------------------------------select /*+ gather_plan_statistics */ name from t1 where id=9Plan hash value: 1141790563-----------------------------------------------------------------------------------------------| Id  | Operation                   | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |       |      1 |        |      1 |00:00:00.01 |       2 ||   1 |  TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |      1 |00:00:00.01 |       2 ||*  2 |   INDEX UNIQUE SCAN         | PK_T1 |      1 |      1 |      1 |00:00:00.01 |       1 |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID"=9)已选择 19 行。