Wednesday, 23 March 2011

Explain Plan

Explain Plan : It is a statement that allows you to have oracle generate execution plan for any sql statement with out actually executing it.You will be able to examine the execution plan by querying the plan table.

Plan Table : A Plan table holds execution plans generated by the explain plan statements.Create Plan table by  running utlxplan.sql located in


Explain Plan Syntax :

Explain plan [set statement_id=<string in single quotes>]

[into <plan table name>]


<sql statements>;

Example to Explain Plan:

Sql> Explain plan set statement_id=’demo’ for

select a.customer_name,a.customer_number,b.invoice_number,



from customers a, invoices b, invoice_items c

where c.invoice_id=:b1

and c.line_number=:b2

and b.invoice_id=c.invoice_id

and a.customer_id=b.customer_id


Sql> @ explain.sql

Enter statement_id : Demo

No comments :

Post a Comment