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 information
TYPICAL ... displays most relevant information
SERIAL .... like TYPICAL but without parallel information
ALL ....... displays all information

SQL> 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 25
SQL> set linesize 121
SQL>
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_id
SQL> 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" section
ALLSTATS 	A shortcut for 'IOSTATS MEMSTATS'
BYTES 	If relevant, shows the number of bytes estimated by the optimizer
COST 	If relevant, shows optimizer cost information
IOSTATS 	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 cursor
LAST 	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 execution
MEMSTATS 	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 operators
NOTE 	If relevant, shows the note section of the explain plan
PARALLEL 	If relevant, shows PX information (distribution method and table queue information)
PARTITION 	If relevant, shows partition pruning information
PREDICATE 	If relevant, shows the predicate section
PROJECTION 	If relevant, shows the projection section
REMOTE 	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 optimizer
RUNSTATS_LAST 	Same as IOSTATS LAST: displays the runtime stat for the last execution of the cursor
RUNSTATS_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
--------------------
china

SQL> 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                         0
select sql_id,child_number,sql_text from v$sql where sql_text like '%gather_plan
_statistics%'

fn50a33auutj0                         0
select /* + gather_plan_statistics */ name from t1 where id=9

4udk8ch96vutj                         0
select /*+ gather_plan_statistics */ name from t1 where id=9


SQL> select * from table(dbms_xplan.display_cursor('4udk8ch96vutj',0,'ALLSTATS L
AST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------
SQL_ID  4udk8ch96vutj, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ name from t1 where id=9

Plan hash value: 1141790563

--------------------------------------------------------------------------------
---------------
| Id  | Operation                   | Name  | Starts | E-Rows | A-Rows |   A-Tim
e   | 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 行。