DBMS_XPLAN

Oracle9i New Feature Series: Explain Plan Output Using DBMS Package

Remember the SQL you use to get the explain plan from the PLAN_TABLE and you perform and EXPLAIN on a statement:

  SELECT LPAD(' ', 2*level) || operation ||' ' ||
options ||' ' || object_name || ' COST=' || cost "Execution Plan"
FROM plan_table
  CONNECT BY PRIOR id = parent_id
START WITH ID = 1

Well, you may never use the above SQL, Oracle9i gives you a package to look at the explain plans DBMS_XPLAN.

Here is an example:

SQL> EXPLAIN PLAN FOR
  2  select object_type, object_name
3 from test
4* where object_name = 'DWA_JOB_LOG'
SQL> /
 
Explained.
 
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------
 
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 62 | 46 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2 | 62 | 46 |
|* 2 | INDEX SKIP SCAN | TEST_I1 | 2 | | 45 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - access("TEST"."OBJECT_NAME"='DWA_JOB_LOG')
filter("TEST"."OBJECT_NAME"='DWA_JOB_LOG')
 
Note: cpu costing is off
 
16 rows selected.
 
SQL>
SQL> alter table test parallel;
 
Table altered.
 
SQL> delete from plan_table;
 
3 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> EXPLAIN PLAN FOR
2 select object_type, object_name
3 from test
4 where object_name = 'DWA_JOB_LOG'
5 /
 
Explained.
 
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
 
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 62 | 5 | | | |
|* 1 | TABLE ACCESS FULL | TEST | 2 | 62 | 5 | 80,00 | P->S | QC (RAND) |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
1 - filter("TEST"."OBJECT_NAME"='DWA_JOB_LOG')
 
Note: cpu costing is off
 
14 rows selected.
 
SQL>
 
The following are the parameters of DBMS_XPLAN.DISPLAY procedure:
 
DBMS_XPLAN.DISPLAY(
   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',
   statement_id  IN  VARCHAR2  DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL');

table_name

Specifies the table name where the plan is stored. This parameter defaults to PLAN_TABLE, which is the default plan table for the EXPLAIN PLAN command.

statement_id

Specifies the statement_id of the plan to be displayed. This parameter defaults to NULL, which is the default when the EXPLAIN PLAN command is executed without a set statement_id clause.

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 object name, and the operation option.
TYPICAL: This is the default. Displays the most relevant information in the plan. Partition pruning, parallelism, and predicates are displayed only when available.
ALL: Maximum level. Includes information displayed with the TYPICAL level and adds the SQL statements generated for parallel execution servers (only if parallel).
SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.

 

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.