ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > sql的执行计划 详解

sql的执行计划 详解

原创 Oracle 作者：哎呀我的天呐 时间：2015-01-09 14:38:12 0 删除 编辑

一条合法的语句在执行之后，就会在内存中至少产生一条执行计划，可以从视图v$sql_plan查询。每一条执行计划对于一个游标。一条语句生产的第一个游标的CHILD_NUMBER（即v$sql_plan中的CHILD_NUMBER）为0，同一条sql语句可能因为环境或版本等其他因素不同而产生不同的执行计划，也就是说一条sql可能有多个CHILD_NUMBER。

除执行过的sql会在内存中生成执行计划外，还可以通过explain plan让优化器对sql语句进行解析，生成查询计划。执行explain plan命令后，oracle会将解释生成的执行计划插入sys.plan_table$（10g之前，表名为plan_table；10g之后，通过公共同义词plan_table指向sys.plan_table$）中。explain plan产生的执行计划不会在语句执行时重用，而是以类似explain plan for的形式在缓存中。
plan_table是全局临时表，plan_table只是存放explain plan的结果和真正的执行计划无关。 真正的执行计划放在v$sql_plan, dba_hist_sql_plan


全局临时表

点击(此处)折叠或打开

  1. CREATE GLOBAL TEMPORARY TABLE TABLENAME (
  2. COL1 VARCHAR2(10)
  3. COL2 NUMBER
  4. ) ON COMMIT PRESERVE(DELETE) ROWS


这种临时表不占用表空间，而且不同的SESSION之间互相看不到对方的数据，在会话结束后表中的数据自动清空，如果选了DELETE ROWS，则在提交的时候即清空数据，PRESERVE则一直到会话结束。

产生执行计划

  • 需要PLAN TABLE表:

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan

  • 创建执行计划：

SQL>explain plan for select last_name from hr.employee;

  • 从plan_table表中查询执行计划  四种方式显示执行计划


  • 1、直接查询PLAN_TABLE
                          2、Select plan_table_output from table(DBMS_XPLAN.DISKPLAY());
                         3、使用utlxpls.sql脚本
                         4、使用utlxplp.sql脚本   显示并行查询的计划

SYS> @$ORACLE_HOME/rdbms/admin/utlxplan     --这个脚本里面的内容就是下面内容。

点击(此处)折叠或打开

  1. create table PLAN_TABLE (
  2.         statement_id varchar2(30),
  3.         plan_id number,
  4.         timestamp date,
  5.         remarks varchar2(4000),
  6.         operation varchar2(30),
  7.         options varchar2(255),
  8.         object_node varchar2(128),
  9.         object_owner varchar2(30),
  10.         object_name varchar2(30),
  11.         object_alias varchar2(65),
  12.         object_instance numeric,
  13.         object_type varchar2(30),
  14.         optimizer varchar2(255),
  15.         search_columns number,
  16.         id numeric,
  17.         parent_id numeric,
  18.         depth numeric,
  19.         position numeric,
  20.         cost numeric,
  21.         cardinality numeric,
  22.         bytes numeric,
  23.         other_tag varchar2(255),
  24.         partition_start varchar2(255),
  25.         partition_stop varchar2(255),
  26.         partition_id numeric,
  27.         other long,
  28.         distribution varchar2(30),
  29.         cpu_cost numeric,
  30.         io_cost numeric,
  31.         temp_space numeric,
  32.         access_predicates varchar2(4000),
  33.         filter_predicates varchar2(4000),
  34.         projection varchar2(4000),
  35.         time numeric,
  36.         qblock_name varchar2(30),
  37.         other_xml clob
  38. )


 通过DBMS_XPLAN包显示执行计划（推荐）


DBMS_XPLAN含有5个函数用于输出格式化的执行计划，如下：


文档上说

The DBMS_XPLAN package supplies four table functions:

  • DISPLAY - to format and display the contents of a plan table.

  • DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.  格式化并显示执行计划的内容的任何加载游标。

  • DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.

  • DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.

    Displaying a Plan Table Using DBMS_XPLAN.DISPLAY

    Execute an explain plan command on a SELECT statement: 

    EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
   WHERE e.deptno = d.deptno
   AND e.ename='benoit';

    Display the plan using the DBMS_XPLAN.DISPLAY table function 

    SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

    This query produces the following output: 

    Plan hash value: 3693697075
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    57 |     6  (34)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    57 |     6  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    37 |     3  (34)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3  (34)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("E"."ENAME"='benoit')

15 rows selected.


    Displaying a Cursor Execution Plan Using DBMS_XPLAN.DISPLAY_CURSOR

    By default, the table function DISPLAY_CURSOR formats the execution plan for the last SQL statement executed by the session. For example: 

    SELECT ename  FROM  emp e, dept d 
   WHERE   e.deptno = d.deptno  
   AND   e.empno=7369;

ENAME
----------
SMITH

    To display the execution plan of the last executed statement for that session: 

    SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

    This query produces the following output: 

    Plan hash value: 3693697075, SQL hash value: 2096952573, child number: 0
------------------------------------------------------------------
SELECT ename FROM emp e, dept d WHERE e.deptno = d.deptno
AND e.empno=7369

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |            |          |
|*  1 |  HASH JOIN         |      |     1 |    16 |     6  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    13 |     3  (34)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    12 |     3  (34)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("E"."EMPNO"=7369)

21 rows selected.

    You can also use the table function DISPLAY_CURSOR to display the execution plan for any loaded cursor stored in the cursor cache. In that case, you must supply a reference to the child cursor to the table function. This includes the SQL ID of the statement and optionally the child number.

    Run a query with a distinctive comment: 

    SELECT /* TOTO */ ename, dname 
FROM dept d join emp e USING (deptno);

    Get sql_id and child_number for the preceding statement: 

    SELECT sql_id, child_number
FROM v$sql 
WHERE sql_text LIKE '%TOTO%';

SQL_ID         CHILD_NUMBER
----------     -----------------------------
gwp663cqh5qbf   0

    Display the execution plan for the cursor: 

    SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));

Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0
--------------------------------------------------------
SELECT /* TOTO */ ename, dname 
FROM dept d JOIN emp e USING (deptno);

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     7 (100)|          |
|   1 |  SORT GROUP BY      |      |     4 |    64 |     7  (43)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    14 |   224 |     6  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    44 |     3  (34)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    70 |     3  (34)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."DEPTNO"="D"."DEPTNO")

    Instead of issuing two queries, one to the get the sql_id and child_number pair and one to display the plan, you can combine these in a single query:

    Display the execution plan of all cursors matching the string 'TOTO': 

    SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%TOTO%';

    Displaying a Plan Table with Parallel Information

    By default, only relevant information is reported by the display and display_cursor table functions. In Displaying a Plan Table Using DBMS_XPLAN.DISPLAY, the query does not execute in parallel. Hence, information related to the parallelization of the plan is not reported. As shown in the following example, parallel information is reported only if the query executes in parallel. 

    ALTER TABLE emp PARALLEL;
EXPLAIN PLAN for
SELECT * FROM emp e, dept d
   WHERE e.deptno = d.deptno
   AND e.ename    ='hermann'
   ORDER BY e.empno;

    Display the plan using the DBMS_XPLAN.DISPLAY table function 

    SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 3693697345

    -------------------------------------------------------------------------------------------------------------
| Id | Operation          | Name    | Rows | Bytes | Cost (%CPU)| Time     | TQ    |INOUT |PQ Distrib   |
-------------------------------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT   |         | 1    | 117   | 6 (50)     | 00:00:01 |       |      |             |
| 1  | PX COORDINATOR     |         |      |       |            |          |       |      |             |
| 2  | PX SEND QC (ORDER) |:TQ10003 | 1    | 117   | 6 (50)     | 00:00:01 | Q1,03 | P->S | QC (ORDER)  |
| 3  | SORT ORDER BY      |         | 1    | 117   | 6 (50)     | 00:00:01 | Q1,03 | PCWP |             |
| 4  | PX RECEIVE         |         | 1    | 117   | 5 (40)     | 00:00:01 | Q1,03 | PCWP |             |
| 5  | PX SEND RANGE      |:TQ10002 | 1    | 117   | 5 (40)     | 00:00:01 | Q1,02 | P->P | RANGE       |
|* 6 | HASH JOIN          |         | 1    | 117   | 5 (40)     | 00:00:01 | Q1,02 | PCWP |             |
| 7  | PX RECEIVE         |         | 1    | 87    | 2 (50)     | 00:00:01 | Q1,02 | PCWP |             |
| 8  | PX SEND HASH       |:TQ10001 | 1    | 87    | 2 (50)     | 00:00:01 | Q1,01 | P->P | HASH        |
| 9  | PX BLOCK ITERATOR  |         | 1    | 87    | 2 (50)     | 00:00:01 | Q1,01 | PCWC |             |
|* 10| TABLE ACCESS FULL  | EMP     | 1    | 87    | 2 (50)     | 00:00:01 | Q1,01 | PCWP |             |
| 11 | BUFFER SORT        |         |      |       |            |          | Q1,02 | PCWC |             |
| 12 | PX RECEIVE         |         | 4    | 120   | 3 (34)     | 00:00:01 | Q1,02 | PCWP |             |
| 13 | PX SEND HASH       |:TQ10000 | 4    | 120   | 3 (34)     | 00:00:01 |       | S->P | HASH        |
| 14 | TABLE ACCESS FULL  | DEPT    | 4    | 120   | 3 (34)     | 00:00:01 |       |      |             |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("E"."DEPTNO"="D"."DEPTNO")
10 - filter("E"."ENAME"='hermann')
---------------------------------------------------

    When the query is parallel, information related to parallelism is reported: table queue number (TQ column), table queue type (INOUT) and table queue distribution method (PQ Distrib).

    By default, if several plans in the plan table match the statement_id parameter passed to the display table function (default value is NULL), only the plan corresponding to the last EXPLAIN PLAN command is displayed. Hence, there is no need to purge the plan table after each EXPLAIN PLAN. However, you should purge the plan table regularly to ensure good performance in the execution of the DISPLAY table function. If no plan table is created, Oracle will use a global temporary table to store any plan information for individual users and will preserve its content throughout the lifespan of a session. Note that you cannot truncate the content of a global temporary table.

    For ease of use, you can define a view on top of the display table function and then use that view to display the output of the EXPLAIN PLANcommand:

    Using a View to Display Last Explain Plan 

    # define plan view
CREATE VIEW PLAN AS SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

# display the output of the last explain plan command
SELECT * FROM PLAN;

    Summary of DBMS_XPLAN Subprograms

    Table 132-1 DBMS_XPLAN Package Subprograms

    Subprogram Description

    DISPLAY Function

    Displays the contents of the plan table

    DISPLAY_AWR Function

    Displays the contents of an execution plan stored in the AWR

    DISPLAY_CURSOR Function

    Displays the execution plan of any cursor in the cursor cache

    DISPLAY_SQLSET Function

    Displays the execution plan of a given statement stored in a SQL tuning set


    DISPLAY Function

    This table function displays the contents of the plan table.

    In addition, you can use this table function to display any plan (with or without statistics) stored in a table as long as the columns of this table are named the same as columns of the plan table (or V$SQL_PLAN_STATISTICS_ALL if statistics are included). You can apply a predicate on the specified table to select rows of the plan to display.

    Syntax 

    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);

    Parameters

    Table 132-2 DISPLAY Function Parameters

    Parameter Description

    table_name

    Specifies the table name where the plan is stored. This parameter defaults toPLAN_TABLE, which is the default plan table for the EXPLAIN PLAN command. If NULL is specified it also defaults to PLAN_TABLE.

    statement_id

    Specifies the statement_id of the plan to be displayed. This parameter defaults to NULL,which is the default when the EXPLAIN PLANcommand is executed without a setstatement_id clause.If no statement_idis specified, the function will show you the plan of the most recent explained statement.

    format

    Controls the level of details for the plan. It accepts four values:

    • BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.

    • TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes onlyPROJECTION, ALIAS and REMOTE SQLinformation (see below).

    • SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.

    • ALL: Maximum user level. Includes information displayed with the TYPICALlevel with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).

    For finer control on the display output, the following keywords can be added to the above three standard format options to customize their default behavior. Each keyword either represents a logical group of plan table columns (such as PARTITION) or logical additions to the base plan table output (such as PREDICATE). Format keywords must be separated by either a comma or a space:

    • ROWS - if relevant, shows the number of rows estimated by the optimizer

    • BYTES - if relevant, shows the number of bytes estimated by the optimizer

    • COST - if relevant, shows optimizer cost information

    • PARTITION - if relevant, shows partition pruning information

    • PARALLEL - if relevant, shows PX information (distribution method and table queue information)

    • PREDICATE - if relevant, shows the predicate section

    • PROJECTION -if relevant, shows the projection section

    • ALIAS - if relevant, shows the "Query Block Name / Object Alias" section

    • REMOTE - if relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)

    • NOTE - if relevant, shows the note section of the explain plan

    Format keywords can be prefixed by the sign '-' to exclude the specified information. For example, '-PROJECTION' excludes projection information.

    If the target plan table (see table_nameparameter) also stores plan statistics columns (for example, it is a table used to capture the content of the fixed viewV$SQL_PLAN_STATISTICS_ALL), additional format keywords can be used to specify which class of statistics to display when using theDISPLAY Function. These additional format keywords are IOSTATS, MEMSTATS,ALLSTATS and LAST (see theDISPLAY_CURSOR Function or theDISPLAY_SQLSET Function for a full description of these four keywords).

    filter_preds

    SQL filter predicate(s) to restrict the set of rows selected from the table where the plan is stored. When value is NULL (the default), the plan displayed corresponds to the last executed explain plan. For example:filter_preds=>'plan_id = 10'

    Can reference any column of the table where the plan is stored and can contain any SQL construct (for example, sub-query, function calls (see WARNING under Usage Notes)


    Usage Notes

    Here are some ways you might use variations on the format parameter:

    • Use 'ALL -PROJECTION -NOTE' to display everything except the projection and note sections.

    • Use 'TYPICAL PROJECTION' to display using the typical format with the additional projection section (which is normally excluded under the typical format). Since typical is default, using simply 'PROJECTION' is equivalent.

    • Use '-BYTES -COST -PREDICATE' to display using the typical format but excluding optimizer cost and byte estimates as well as the predicate section.

    • Use 'BASIC ROWS' to display basic information with the additional number of rows estimated by the optimizer.

      WARNING:

      Application developers should expose the filter_preds parameter to end-users only after careful consideration because this could expose the application to SQL injection. Indeed, filter_preds can potentially reference any table or execute any server function for which the database user invoking the table function has privileges.

    Examples

    To display the result of the last EXPLAIN PLAN command stored in the plan table: 

    SELECT * FROM table (DBMS_XPLAN.DISPLAY);

    To display from other than the default plan table, "my_plan_table": 

    SELECT * FROM table (DBMS_XPLAN.DISPLAY('my_plan_table'));

    To display the minimum plan information: 

    SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));

    To display the plan for a statement identified by 'foo', such as statement_id='foo': 

    SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', 'foo'));

    DISPLAY_AWR Function

    This table function displays the contents of an execution plan stored in the AWR.

    Syntax 

    DBMS_XPLAN.DISPLAY_AWR( 
   sql_id            IN      VARCHAR2,
   plan_hash_value   IN      NUMBER DEFAULT NULL,
   db_id             IN      NUMBER DEFAULT NULL,
   format            IN      VARCHAR2 DEFAULT TYPICAL);

    Parameters

    Table 132-3 DISPLAY_AWR Table Function Parameters

    Parameter Description

    sql_id

    Specifies the SQL_ID of the SQL statement. You can retrieve the appropriate value for the SQL statement of interest by querying the column SQL_ID in DBA_HIST_SQLTEXT.

    plan_hash_value

    Specifies the PLAN_HASH_VALUE of a SQL statement. This parameter is optional. If omitted, the table function will return all stored execution plans for a given SQL_ID.

    db_id

    Specifies the database_id for which the plan of the SQL statement, identified by SQL_IDshould be displayed. If not supplied, thedatabase_id of the local database will be used, as shown in V$DATABASE.

    format

    Controls the level of details for the plan. It accepts four values:

    • BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.

    • TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes onlyPROJECTION, ALIAS and REMOTE SQLinformation (see below).

    • SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.

    • ALL: Maximum user level. Includes information displayed with the TYPICALlevel with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).

     

    For finer control on the display output, the following keywords can be added to the above three standard format options to customize their default behavior. Each keyword either represents a logical group of plan table columns (such as PARTITION) or logical additions to the base plan table output (such as PREDICATE). Format keywords must be separated by either a comma or a space:

    • ROWS - if relevant, shows the number of rows estimated by the optimizer

    • BYTES - if relevant, shows the number of bytes estimated by the optimizer

    • COST - if relevant, shows optimizer cost information

    • PARTITION - if relevant, shows partition pruning information

    • PARALLEL - if relevant, shows PX information (distribution method and table queue information)

    • PREDICATE - if relevant, shows the predicate section

    • PROJECTION -if relevant, shows the projection section

    • ALIAS - if relevant, shows the "Query Block Name / Object Alias" section

    • REMOTE - if relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)

    • NOTE - if relevant, shows the note section of the explain plan

    Format keywords can be prefixed by the sign '-' to exclude the specified information. For example, '-PROJECTION' excludes projection information.


    Usage Notes

    • To use the DISPLAY_AWR functionality, the calling user must have SELECT privilege on DBA_HIST_SQL_PLAN. DBA_HIST_SQLTEXT, andV$DATABASE, otherwise it will show an appropriate error message.

    • Here are some ways you might use variations on the format parameter:

      • Use 'ALL -PROJECTION -NOTE' to display everything except the projection and note sections.

      • Use 'TYPICAL PROJECTION' to display using the typical format with the additional projection section (which is normally excluded under the typical format). Since typical is default, using simply 'PROJECTION' is equivalent.

      • Use '-BYTES -COST -PREDICATE' to display using the typical format but excluding optimizer cost and byte estimates as well as the predicate section.

      • Use 'BASIC ROWS' to display basic information with the additional number of rows estimated by the optimizer.

    Examples

    To display the different execution plans associated with the SQL ID 'atfwcg8anrykp': 

    SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));

    To display all execution plans of all stored SQL statements containing the string 'TOTO': 

    SELECT tf.* FROM DBA_HIST_SQLTEXT ht, table
    (DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null,  'ALL' )) tf 
 WHERE ht.sql_text like '%TOTO%';

    DISPLAY_CURSOR Function

    This table function displays the explain plan of any cursor loaded in the cursor cache. In addition to the explain plan, various plan statistics (such as. I/O, memory and timing) can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS).

    Syntax 

    DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id        IN  VARCHAR2  DEFAULT  NULL,
   child_number  IN  NUMBER    DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL');

    Parameters

    Table 132-4 DISPLAY_CURSOR Function Parameters

    Parameter Description

    sql_id

    Specifies the SQL_ID of the SQL statement in the cursor cache. You can retrieve the appropriate value by querying the columnSQL_ID in V$SQL or V$SQLAREA. Alternatively, you could choose the columnPREV_SQL_ID for a specific session out ofV$SESSION. This parameter defaults to NULLin which case the plan of the last cursor executed by the session will be displayed.

    child_number

    Child number of the cursor to display. If not supplied, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The child_number can be specified only if sql_id is specified.

    format

    Controls the level of details for the plan. It accepts four values:

    • BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.

    • TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes onlyPROJECTION, ALIAS and REMOTE SQLinformation (see below).

    • SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.

    • ALL: Maximum user level. Includes information displayed with the TYPICALlevel with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).

    For finer control on the display output, the following keywords can be added to the above three standard format options to customize their default behavior. Each keyword either represents a logical group of plan table columns (such as PARTITION) or logical additions to the base plan table output (such as PREDICATE).

     

    Format keywords must be separated by either a comma or a space:

    • ROWS - if relevant, shows the number of rows estimated by the optimizer

    • BYTES - if relevant, shows the number of bytes estimated by the optimizer

    • COST - if relevant, shows optimizer cost information

    • PARTITION - if relevant, shows partition pruning information

    • PARALLEL - if relevant, shows PX information (distribution method and table queue information)

    • PREDICATE - if relevant, shows the predicate section

    • PROJECTION -if relevant, shows the projection section

    • ALIAS - if relevant, shows the "Query Block Name / Object Alias" section

    • REMOTE - if relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)

    • NOTE - if relevant, shows the note section of the explain plan

    • 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 forALL (or only for the LAST as shown below) executions of the cursor.

    • 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.

    • ALLSTATS - A shortcut for'IOSTATS MEMSTATS'

    • 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.

    The following two formats are deprecated but supported for backward compatibility:

    • RUNSTATS_TOT - Same as IOSTATS, that is, displays IO statistics for all executions of the specified cursor.

    • RUNSTATS_LAST - Same as IOSTATSLAST, that is, displays the runtime statistics for the last execution of the cursor

    Format keywords can be prefixed by the sign '-' to exclude the specified information. For example, '-PROJECTION' excludes projection information.


    Usage Notes

    • To use the DISPLAY_CURSOR functionality, the calling user must have SELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQLand V$SQL_PLAN, otherwise it will show an appropriate error message.

    • Here are some ways you might use variations on the format parameter:

      • Use 'ALL -PROJECTION -NOTE' to display everything except the projection and note sections.

      • Use 'TYPICAL PROJECTION' to display using the typical format with the additional projection section (which is normally excluded under the typical format). Since typical is default, using simply 'PROJECTION' is equivalent.

      • Use '-BYTES -COST -PREDICATE' to display using the typical format but excluding optimizer cost and byte estimates as well as the predicate section.

      • Use 'BASIC ROWS' to display basic information with the additional number of rows estimated by the optimizer.

    Examples

    To display the execution plan of the last SQL statement executed by the current session: 

    SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_CURSOR);

    To display the execution plan of all children associated with the SQL ID 'atfwcg8anrykp': 

    SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));

    To display runtime statistics for the cursor included in the preceding statement: 

    SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ALLSTATS LAST');

    DISPLAY_SQLSET Function

    This table function displays the execution plan of a given statement stored in a SQL tuning set.

    Syntax 

    DBMS_XPLAN.DISPLAY_SQLSET(
   sqlset_name      IN  VARCHAR2, 
   sql_id           IN  VARCHAR2,
   plan_hash_value  IN NUMBER := NULL,
   format           IN  VARCHAR2  := 'TYPICAL', 
   sqlset_owner     IN  VARCHAR2  := NULL)
  RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

    Parameters

    Table 132-5 DISPLAY_SQLSET Function Parameters

    Parameter Description

    sqlset_name

    Name of the SQL Tuning Set

    sql_id

    Specifies the sql_id value for a SQL statement having its plan stored in the SQL tuning set. You can find all stored SQL statements by querying table functionDBMS_SQLTUNE.SELECT_SQLSET

    plan_hash_value

    Optional parameter. Identifies a specific stored execution plan for a SQL statement. If suppressed, all stored execution plans are shown.

    format

    Controls the level of details for the plan. It accepts four values:

    • BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.

    • TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes onlyPROJECTION, ALIAS and REMOTE SQLinformation (see below).

    • SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.

    • ALL: Maximum user level. Includes information displayed with the TYPICALlevel with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).

     

    For finer control on the display output, the following keywords can be added to the above three standard format options to customize their default behavior. Each keyword either represents a logical group of plan table columns (such as PARTITION) or logical additions to the base plan table output (such as PREDICATE). Format keywords must be separated by either a comma or a space:

    • ROWS - if relevant, shows the number of rows estimated by the optimizer

    • BYTES - if relevant, shows the number of bytes estimated by the optimizer

    • COST - if relevant, shows optimizer cost information

    • PARTITION - if relevant, shows partition pruning information

    • PARALLEL - if relevant, shows PX information (distribution method and table queue information)

    • PREDICATE - if relevant, shows the predicate section

    • PROJECTION -if relevant, shows the projection section

    • ALIAS - if relevant, shows the "Query Block Name / Object Alias" section

    • REMOTE - if relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)

    • NOTE - if relevant, shows the note section of the explain plan

    • 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 forALL (or only for the LAST as shown below) executions of the cursor.

    • 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.

    • ALLSTATS - A shortcut for'IOSTATS MEMSTATS'

    • 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.

    The following two formats are deprecated but supported for backward compatibility:

    • RUNSTATS_TOT - Same as IOSTATS, that is, displays IO statistics for all executions of the specified cursor.

    • RUNSTATS_LAST - Same as IOSTATSLAST, that is, displays the runtime statistics for the last execution of the cursor

    Format keywords can be prefixed by the sign '-' to exclude the specified information. For example, '-PROJECTION' excludes projection information.

    sqlset_owner

    The owner of the SQL tuning set. The default is the current user.


    Usage Notes

    Here are some ways you might use variations on the format parameter:

    • Use 'ALL -PROJECTION -NOTE' to display everything except the projection and note sections.

    • Use 'TYPICAL PROJECTION' to display using the typical format with the additional projection section (which is normally excluded under the typical format). Since typical is default, using simply 'PROJECTION' is equivalent.

    • Use '-BYTES -COST -PREDICATE' to display using the typical format but excluding optimizer cost and byte estimates as well as the predicate section.

    • Use 'BASIC ROWS' to display basic information with the additional number of rows estimated by the optimizer.

    Examples

    To display the execution plan for the SQL statement associated with SQL ID 'gwp663cqh5qbf' and PLAN HASH 3693697075 in the SQL Tuning Set called 'OLTP_optimization_0405": 

    SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
       'OLTP_optimization_0405','gwp663cqh5qbf', 3693697075));

    To display all execution plans of the SQL ID 'atfwcg8anrykp' stored in the SQL tuning set: 

    SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
      'OLTP_optimization_0405','gwp663cqh5qbf'));

    To display runtime statistics for the SQL statement included in the preceding statement: 

    SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
      'OLTP_optimization_0405', 'gwp663cqh5qbf', NULL, 'ALLSTATS LAST');

    最后再看看这篇文章：http://www.cnblogs.com/jianggc/articles/2033551.html


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29990276/viewspace-1395971/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 约束 comment 创建语句
下一篇： sql_trace
请登录后发表评论 登录
全部评论
哎呀我的天呐
专家姓名：姚崇
https://cloud.tencent.com/developer/inventory/14362

注册时间：2014-10-30

  • 博文量
    403
  • 访问量
    2528743

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593