Oracle Explan

最新推荐文章于 2023-06-04 21:26:11 发布

cijih66918

最新推荐文章于 2023-06-04 21:26:11 发布

阅读量342

点赞数

文章标签：数据库 runtime

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(*)

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

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.

DBMS_XPLAN : Display Oracle Execution Plans

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)

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

1 row selected.

Elapsed: 00:00:10.28

Execution Plan

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

Plan hash value: 1550022268

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

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

| 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

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

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

| 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

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

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

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

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

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

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

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

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

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

| 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

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

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

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

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

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

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

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

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

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

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

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

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

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