Oracle Explan

OraFige發表於2011-12-22
Oracle

ORACLE EXPLAIN PLAN

在ORACLE資料庫中,需要對SQL語句進行優化的話需要知道其執行計劃,從而針對性的進行調整.ORACLE的執行計劃的獲得有幾種方法,下面就來總結下

1、EXPLAIN的使用

    Oracle RDBMS執行每一條SQL語句，都必須經過Oracle優化器的評估。所以，瞭解優化器是如何選擇(搜尋)路徑以及索引是如何被使用的，對優化SQL語句有很大的幫助。Explain可以用來迅速方便地查出對於給定SQL語句中的查詢資料是如何得到的即搜尋路徑(我們通常稱為Access Path)。從而使我們選擇最優的查詢方式達到最大的優化效果。

1.1 、安 裝                                                                                                                       

 要使用EXPLAIN首先要執行相應的指令碼，建立出Explain_plan表。

具體指令碼執行如下：

   $ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX)  該指令碼後會生成一個表這個程式會建立一個名為plan_table的表。

1.2 、使用

常規使用語法：

explain PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ]

FOR < sql_statement >

其中：

STATEMENT_ID：是一個唯一的字串，把當前執行計劃與儲存在同一PLAN中的其它執行計劃區別開來。

TABLE_NAME：是plan表名，它結構如前所示，你可以任意設定這個名稱。

SQL_STATEMENT：是真正的SQL語句。                                                                    

比如：                                                                                                               

SQL>explain plan set statement_id='T_TEST' for select * from t_test;            

SQL>

Explained

 

執行下面語句可以查詢到執行計劃

SQL>SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID

  2  FROM PLAN_TABLE  a

  3  WHERE STATEMENT_ID='T_TEST'

  4  ORDER BY Id;

也可以用這句話 select * from table(dbms_xplan.display); 可以把所有PLAN_TABLE裡的資料羅列出來。

2 、AUTOTRACE的使用方法

2.1、安裝

用sys使用者執行指令碼ultxplan.sql

建立這個表的指令碼是：（UNIX:$ORACLE_HOME/rdbms/admin, Windows:%ORACLE_HOME%\rdbms\admin）ultxplan.sql。

SQL> connect sys/sys@colm2 as sysdba;

SQL> @C:\oracle\ora92\rdbms\admin\utlxplan.sql;

SQL> create public synonym plan_table for plan_table;--建立同義詞

SQL> grant all on plan_table to public;--授權所有使用者

要在資料庫中建立一個角色plustrace,用sys使用者執行指令碼plustrce.sql來建立這個角色，這個指令碼在目錄（UNIX:$ORACLE_HOME/sqlplus/admin, Windows:%ORACLE_HOME%\sqlplus\admin）中；

SQL> @C:\oracle\ora92\sqlplus\admin\plustrce.sql;

然後將角色plustrace授予需要autotrace的使用者；

SQL>grant plustrace to public;

經過以上步驟的設定，就可以在sql*plus中使用autotrace了

2、2 使用

使用起來非常方便，只要使用一條命令就可以了

SQL>SET AUTOTRACE ON；

*autotrace功能只能在SQL*PLUS裡使用

  其他一些使用方法：

2.2.1、在SQLPLUS中得到語句總的執行時間

SQL> set timing on;

2.2.2、只顯示執行計劃--（會同時執行語句得到結果）

SQL>set autotrace on explain

比如：

sql> select count(*) from test;

count(*)

-------------

4

Execution plan

----------------------------

0            select statement ptimitzer=choose (cost=3 card=1)

1     0        sort(aggregate)

2     1           partition range(all)

3     2             table access (full) of 't_test'  (cost=3 card=900)

2.2.3、只顯示統計資訊---（會同時執行語句得到結果）

SQL>set  autotrace on statistics;

（備註：對於SYS使用者，統計資訊將會是0）

2.2.4、顯示執行計劃，遮蔽執行結果--（但語句實質還執行的 

SQL> set autotrace on traceonly;   

（備註：同SET AUTOTRACE ON; 只不過不顯示結果，顯示計劃和統計）

 

 

 

2.2.5、僅僅顯示執行計劃，遮蔽其他一切結果--(語句還是執行了)

SQL>set autotrace on traceonly explain;

對於僅僅檢視大表的Explain Plan非常管用。

2.2.6、關閉

SQL>set autotrace off;

總結：SQLPLUS 下的自動顯示功能，在看執行計劃中其語句還是會被執行的。尤其在執行UPDATE/DELETE語句時請千萬注意，ORACLE是先執行指令碼同時顯示執行計劃的，即使使用set autotrace on traceonly explain;

這個時候推薦使用EXPLAIN PLAN FOR來看或者PL/SQL等第三方工具

3、第三工具來看執行計劃

   如果在PL/SQL中使用選擇要查詢語句顯示執行計劃，則只需要SQL WINDOWS 視窗裡面輸入要查詢的SQL語句，然後選擇按鍵F5或者在選單TOOLS�D�D>Explain Plan 選單按鍵就可以在執行計劃視窗檢視該語句的執行計劃。

在TOAD語句中在執行當前的SQL視窗中選擇下方的Explain PlanTAB頁即可以檢視要執行語句的執行計劃資訊。

4、限制

explain真正的唯一的限制是使用者不能去解釋其它使用者的表，檢視，索引或其它型別，使用者必須是所有被解釋事物的所有者，如果不是所有者而只有select許可權，explain會返回一個錯誤。

EXPLAIN PLAN Usage

When an SQL statement is passed to the server the Cost Based Optimizer (CBO) uses database statistics to create an execution plan which it uses to navigate through the data. Once you've highlighted a problem query the first thing you should do is EXPLAIN the statement to check the execution plan that the CBO has created. This will often reveal that the query is not using the relevant indexes, or indexes to support the query are missing. Interpretation of the execution plan is beyond the scope of this article.

Related articles.

Plan Table

The explain plan process stores data in the PLAN_TABLE. This table can be located in the current schema or a shared schema and is created using in SQL*Plus as follows.

-- Creating a shared PLAN_TABLE prior to 11g

SQL> CONN sys/password AS SYSDBA

Connected

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

SQL> GRANT ALL ON sys.plan_table TO public;

SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;

In Oracle 11g a shared PLAN_TABLE is created by default, but you can still create a local version of the table using the "utlxplan.sql" script.

AUTOTRACE - The Easy Option?

Switching on the AUTOTRACE parameter in SQL*Plus causes an explain to be performed on every query.

SQL> SET AUTOTRACE ON

SQL> SELECT *

  2  FROM   emp e, dept d

  3  WHERE  e.deptno = d.deptno

  4  AND    e.ename  = 'SMITH';

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO     DEPTNO DNAME          LOC

---------- ---------- -------------- -------------

      7369 SMITH      CLERK           7902 17-DEC-80        800

        20         20 RESEARCH       DALLAS

 

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE

   1    0   NESTED LOOPS

   2    1     TABLE ACCESS (FULL) OF 'EMP'

   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

   4    3       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

 

 

 

 

Statistics

----------------------------------------------------------

         81  recursive calls

          4  db block gets

         27  consistent gets

          0  physical reads

          0  redo size

        941  bytes sent via SQL*Net to client

        425  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>

This is a relatively easy way to get the execution plan but there is an issue. In order to get the execution plan the statement must be run to completion. If the query is particularly inefficient and/or returns many rows, this may take a considerable time. At first glance, using the TRACEONLY option of AUTOTRACE seems to remove this issue, but this option merely suppresses the output of the query data, it doesn't prevent the statement being run. As such, long running queries will still take a long time to complete, but they will not present their data. The following example show this in practice.

CREATE OR REPLACE FUNCTION pause_for_secs(p_seconds IN NUMBER) RETURN NUMBER A

BEGIN

  DBMS_LOCK.sleep(p_seconds);

  RETURN p_seconds;

END;

/

 

Function created.

 

SQL> SET TIMING ON

SQL> SET AUTOTRACE ON

SQL> SELECT pause_for_secs(10) FROM DUAL;

 

PAUSE_FOR_SECS(10)

------------------

                10

 

1 row selected.

 

Elapsed: 00:00:10.28

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1550022268

 

-------------------------------------------------------------------------

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------

|   0 | SELECT STATEMENT |      |     1 |       |     2   (0)| 00:00:01 |

|   1 |  FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------

 

 

Statistics

----------------------------------------------------------

        189  recursive calls

          0  db block gets

        102  consistent gets

          0  physical reads

          0  redo size

        331  bytes sent via SQL*Net to client

        332  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

         13  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> SET AUTOTRACE TRACEONLY

SQL> SELECT pause_for_secs(10) FROM DUAL;

 

1 row selected.

 

Elapsed: 00:00:10.26

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1550022268

 

-------------------------------------------------------------------------

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------

|   0 | SELECT STATEMENT |      |     1 |       |     2   (0)| 00:00:01 |

|   1 |  FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

        331  bytes sent via SQL*Net to client

        332  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>

The query takes the same time to return (about 10 seconds) whether the TRACEONLY option is used or not. If the TRACEONLY option prevented the query running, you would expect it to return instantly, like an EXPLAIN PLAN.

The solution to this is to use the TRACEONLY EXPLAIN option, which only performs the EXPLAIN PLAN, rather than running the statement.

EXPLAIN PLAN

The EXPLAIN PLAN method doesn't require the query to be run, greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE. First the query must be explained.

SQL> EXPLAIN PLAN FOR

  2  SELECT *

  3  FROM   emp e, dept d

  4  WHERE  e.deptno = d.deptno

  5  AND    e.ename  = 'SMITH';

 

Explained.

 

SQL>

Then the execution plan displayed.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

 

Plan Table

--------------------------------------------------------------------------------

| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |

--------------------------------------------------------------------------------

| SELECT STATEMENT          |          |       |      |        |       |       |

|  NESTED LOOPS             |          |       |      |        |       |       |

|   TABLE ACCESS FULL       |EMP       |       |      |        |       |       |

|   TABLE ACCESS BY INDEX RO|DEPT      |       |      |        |       |       |

|    INDEX UNIQUE SCAN      |PK_DEPT   |       |      |        |       |       |

--------------------------------------------------------------------------------

 

8 rows selected.

 

SQL>

For parallel queries use the "utlxplp.sql" script. instead of "utlxpls.sql".

From Oracle 9i onward, you should display execution plans using the DBMS_XPLAN package.

Statement ID

If multiple people are accessing the same plan table, or you would like to keep a history of the execution plans you should use the STATEMENT_ID. This associates a user specified ID with each plan which can be used when retrieving the data.

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR

  2  SELECT *

  3  FROM   emp e, dept d

  4  WHERE  e.deptno = d.deptno

  5  AND    e.ename  = 'SMITH';

 

Explained.

 

SQL> @explain.sql TIM

 

PLAN                                   OBJECT_NAME     OBJECT_TYPE     BYTES  COST PARTITION_START PARTITION_STOP

-------------------------------------- --------------- --------------- ----- ----- --------------- ---------------

Select Statement                                                          57     4

  1.1 Nested Loops                                                        57     4

    2.1 Table Access (Full)            EMP             TABLE              37     3

    2.2 Table Access (By Index Rowid)  DEPT            TABLE              20     1

      3.1 Index (Unique Scan)          PK_DEPT         INDEX (UNIQUE)            0

 

5 rows selected.

 

SQL>

By default the Oracle scripts do not accept a statement_id parameter. You can easily modify the scripts or you can use the script. listed under DBA Scripts on this site.

For more information see:

**************************************************************************************************************

DBMS_XPLAN : Display Oracle Execution Plans

The DBMS_XPLAN package is used to format the output of an explain plan. It was introduced in Oracle 9i as a replacement for the "utlxpls.sql" script. or custom queries of the plan table. Subsequent database versions have increased the functionality of the package.

Setup

If it is not already present create the SCOTT schema.

conn sys/password as sysdba

@$ORACLE_HOME/rdbms/admin/utlsampl.sql

Create a PLAN_TABLE if it does not already exist.

conn sys/password as sysdba

@$ORACLE_HOME/rdbms/admin/utlxplan.sql

CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;

GRANT ALL ON sys.plan_table TO public;

DISPLAY Function

The DISPLAY function allows us to display the execution plan stored in the plan table. First we explain an SQL statement.

CONN scott/tiger

 

EXPLAIN PLAN FOR

SELECT *

FROM   emp e, dept d

WHERE  e.deptno = d.deptno

AND    e.ename  = 'SMITH';

Next we use the DBMS_XPLAN.DISPLAY function to display the execution plan.

SET LINESIZE 130

SET PAGESIZE 0

SELECT *

FROM   TABLE(DBMS_XPLAN.DISPLAY);

 

----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |     1 |    58 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |         |       |       |            |          |

|   2 |   NESTED LOOPS               |         |     1 |    58 |     4   (0)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    38 |     3   (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - filter("E"."ENAME"='SMITH')

   4 - access("E"."DEPTNO"="D"."DEPTNO")

 

18 rows selected.

 

SQL>

The DBMS_XPLAN.DISPLAY function can accept 3 optional parameters:

  • table_name - Name of the PLAN_TABLE, default value 'PLAN_TABLE'.
  • statement_id - Statement id of the plan to be displayed. The default value is NULL, which displays the most recent execution plan in the PLAN_TABLE.
  • format - Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL'. There is also an undocumented 'ADVANCED' setting.

Note. From Oracle 10g Release 2 onwards the format of the output can be tailored by using the standard list of formats along with keywords that represent columns to including or excluding (prefixed with '-'). As a result, the format column can now be a space or comma delimited list. The list of available columns varies depending on the database version and function being called. Check the documentation for your version.

EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR

SELECT *

FROM   emp e, dept d

WHERE  e.deptno = d.deptno

AND    e.ename  = 'SMITH';

 

SET LINESIZE 130

SELECT *

FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC'));

 

Plan hash value: 3625962092

 

------------------------------------------------

| Id  | Operation                    | Name    |

------------------------------------------------

|   0 | SELECT STATEMENT             |         |

|   1 |  NESTED LOOPS                |         |

|   2 |   NESTED LOOPS               |         |

|   3 |    TABLE ACCESS FULL         | EMP     |

|   4 |    INDEX UNIQUE SCAN         | PK_DEPT |

|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |

------------------------------------------------

 

12 rows selected.

 

SQL>

DISPLAY_CURSOR Function

In Oracle 10g Release 1 Oracle introduced the DISPLAY_CURSOR function. Rather than displaying an execution plan from the PLAN_TABLE, it displays the actual execution plan used to run a query stored in the cursor cache. This information is gathered from the V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views, so the user must have access to these. It accepts three optional parameters:

  • sql_id - The SQL_ID of the statement in the cursor cache. The SQL_ID as available from the V$SQL and V$SQLAREA views, or from the V$SESSION view using the PREV_SQL_ID column. If omitted, the last cursor executed by the session is displayed.
  • child_number - The child number of the cursor specified by the SQL_ID parameter. If not specified, all cursors for the specified SQL_ID are diaplyed.
  • format - In addition to the setting available for the DISPLAY function, this function also has 'RUNSTATS_LAST' and 'RUNSTATS_TOT' to display the last and total runtime statistics respectively. These additional format options require "STATISTICS_LEVEL=ALL".

The following example show the advanced output from a query on the SCOTT schema.

CONN / AS SYSDBA

GRANT SELECT ON v_$session TO scott;

GRANT SELECT ON v_$sql TO scott;

GRANT SELECT ON v_$sql_plan TO scott;

GRANT SELECT ON v_$sql_plan_statistics_all TO scott;

 

CONN scott/tiger

 

SELECT *

FROM   emp e, dept d

WHERE  e.deptno = d.deptno

AND    e.ename  = 'SMITH';

 

SET LINESIZE 130

SELECT *

FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------

SQL_ID  gu62pbk51ubc3, child number 0

-------------------------------------

SELECT * FROM   emp e, dept d WHERE  e.deptno = d.deptno AND    e.ename

 = 'SMITH'

 

Plan hash value: 3625962092

 

----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |       |       |     4 (100)|          |

|   1 |  NESTED LOOPS                |         |       |       |            |          |

|   2 |   NESTED LOOPS               |         |     1 |    58 |     4   (0)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    38 |     3   (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |

|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$1

   3 - SEL$1 / E@SEL$1

   4 - SEL$1 / D@SEL$1

   5 - SEL$1 / D@SEL$1

 

Outline Data

-------------

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')

      DB_VERSION('11.2.0.2')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      FULL(@"SEL$1" "E"@"SEL$1")

      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))

      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")

      USE_NL(@"SEL$1" "D"@"SEL$1")

      NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")

      END_OUTLINE_DATA

  */

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - filter("E"."ENAME"='SMITH')

   4 - access("E"."DEPTNO"="D"."DEPTNO")

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],

       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],

       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."DEPTNO"[NUMBER,22],

       "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]

   2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],

       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],

       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D".ROWID[ROWID,10],

       "D"."DEPTNO"[NUMBER,22]

   3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],

       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],

       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]

   4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]

   5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]

 

67 rows selected.

 

SQL>

Other Functions

There are some other useful functions in the package, but I don't find myself using them very often, so they are summarized below. If you need more information, follow the links at the bottom of the article for the appropriate database version.

  • DISPLAY_AWR - Introduced in Oracle 10g Release 1, this function displays an execution plan stored in the Advanced Workload Repository (AWR).
  • DISPLAY_SQLSET - Introduced in Oracle 10g Release 2, this function displays the execution plan of a given statement stored in a SQL tuning set.
  • DISPLAY_SQL_PLAN_BASELINE - Introduced in Oracle 11g Release 1, this function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.
  • DISPLAY_PLAN - Introduced in Oracle 11g Release 2, this function displays the contents of the plan table in a variety of formats.

QB_NAME Hint

Sometimes the same table is included in a query multiple times, so it is hard to know which operation in the execution plan refers to which reference to the table. The QB_NAME solves this problem by allowing you to name, or alias, individual query blocks. The alias information is displayed when the FORMAT parameter of the DISPLAY% functions is set to "ALL", or the " +ALIAS" value is added to the FORMAT parameter in 10gR2 onwards.

The following query references the same table twice, so we cannot easily tell from the execution plan which reference is which.

SELECT (SELECT COUNT(*) FROM emp WHERE job = 'SALESMAN') AS salesman_count,

       (SELECT COUNT(*) FROM emp WHERE job = 'MANAGER') AS manager_count

FROM   dual;

 

SET LINESIZE 100

SET PAGESIZE 50

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |

|*  2 |   TABLE ACCESS FULL| EMP  |     3 |    24 |     3   (0)| 00:00:01 |

|   3 |  SORT AGGREGATE    |      |     1 |     8 |            |          |

|*  4 |   TABLE ACCESS FULL| EMP  |     3 |    24 |     3   (0)| 00:00:01 |

|   5 |  FAST DUAL         |      |     1 |       |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------

Next, we add the QB_NAME hint to the subqueries, giving each a recognizable alias. When the correct format is selected, the output from the DISPLAY% functions now includes a table giving us the relevant alias for each operation.

SELECT (SELECT /*+ QB_NAME(salesman) */ COUNT(*) FROM emp WHERE job = 'SALESMAN') AS salesman_count,

       (SELECT /*+ QB_NAME(manager) */  COUNT(*) FROM emp WHERE job = 'MANAGER') AS manager_count

FROM   dual;

 

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALL));

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |

|*  2 |   TABLE ACCESS FULL| EMP  |     3 |    24 |     3   (0)| 00:00:01 |

|   3 |  SORT AGGREGATE    |      |     1 |     8 |            |          |

|*  4 |   TABLE ACCESS FULL| EMP  |     3 |    24 |     3   (0)| 00:00:01 |

|   5 |  FAST DUAL         |      |     1 |       |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SALESMAN

   2 - SALESMAN / EMP@SALESMAN

   3 - MANAGER

   4 - MANAGER  / EMP@MANAGER

   5 - SEL$1    / DUAL@SEL$1

GATHER_PLAN_STATISTICS Hint

Using the GATHER_PLAN_STATISTICS hint makes the optimizer gather the actual cardinalities in addition to the expected cardinalities in the execution plan. This can then be reported by the DISPLAY_CURSOR function if the format is set to 'ALLSTATS'.

CONN scott/tiger

 

SELECT /*+ GATHER_PLAN_STATISTICS */ *

FROM   emp e, dept d

WHERE  e.deptno = d.deptno

AND    e.ename  = 'SMITH';

 

SET LINESIZE 130

SELECT *

FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

 

--------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       9 |

|   1 |  NESTED LOOPS                |         |      1 |        |      1 |00:00:00.01 |       9 |

|   2 |   NESTED LOOPS               |         |      1 |      1 |      1 |00:00:00.01 |       8 |

|*  3 |    TABLE ACCESS FULL         | EMP     |      1 |      1 |      1 |00:00:00.01 |       7 |

|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |

|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       1 |

--------------------------------------------------------------------------------------------------

Now the "Row" column has been replaced by the original cardinality estimate (E-Rows) and the actual cardinality (A-Rows).

http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm

http://docs.oracle.com/cd/B10501_01/server.920/a96533/ex_plan.htm

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26464953/viewspace-713770/，如需轉載，請註明出處，否則將追究法律責任。

相關文章