Explain Plan

Explain Plan

Utilizado quando você precisa saber o CBO (Cost Based Optimizer) de uma instrução SQL. O primeiro passo é criar a tabela PLAN_TABLE.

[oracle@oel510gfs ~]
$cd $ORACLE_HOME/rdbms/admin

[oracle@oel510gfs admin]
$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Feb 19 17:31:44 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @utlxplan
Table created.

SQL> GRANT ALL ON sys.plan_table TO public;
Grant succeeded.

SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
Synonym created. 

Com a estrutura criada, que tal agora dar uma olhada nos caminhos percorridos pelo seu SELECT.

SQL> explain plan for
2  select * from dual;
Explained.

SQL> @utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.