Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: script to see explain plan

Re: script to see explain plan

From: Jonathan Lewis <>
Date: Fri, 6 Oct 2006 07:31:32 +0100
Message-ID: <028b01c6e911$10cf6160$0200a8c0@Primary>

Watch out for plans with multiple children - you need to specify a child number in your insert or you could get a huge dump on your query. The same is true of dbms_xplan.display_cursor in 10g - it needs a child number as well as an sql_id.

Since the child_number is irrelevant to the AWR tables, the second parameter has to be the plan_hash_value when you use dbms_xplan.display_awr.

I don't recall offering the 'how to get the right plan without running it'; but if I did, it doesn't always work as some statements will be optimised on the execute, not on the parse, and some environments may delaying sending the execute to the database until the first fetch. So your pl/sql block may never send the statement to kernel despite the "open"


Jonathan Lewis

The Co-operative Oracle Users' FAQ

Cost Based Oracle: Fundamentals

Right for the real actual plan already got executed and if the plan is not flushed out from buffer here is script I use.

set long 100000 lines 200 pages 1000

delete from plan_table where statement_id = 'MYQUERY'; insert into plan_table

            'xx' statement_id, 0 plan_id,  timestamp, NULL remarks,
            a.operation, a.options, a.object_node, a.object_owner,
            object_alias,  null object_instance, object_type,
            a.search_columns,, a.parent_id, a.depth, a.position,
            a.cardinality, a.bytes, a.other_tag, a.partition_start,
            a.partition_stop, a.partition_id, a.other, a.distribution,
            a.cpu_cost, a.io_cost, a.temp_space, a.access_predicates,
            a.filter_predicates, projection, time
           ,qblock_name ,OTHER_XML

    from v$sql_plan a
    where sql_id = '&&SQL_ID';

--SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table',
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'MYQUERY')); rollback;

BTW, Jonathan lewis(? I think) presented a method to get real execution plan, without actually fethching data. Anyone got that url?

Something like

Cursor c1 for select * from tab;
Open c1;
Close c1;

And then get the sql_id and query v$sql_plan..

-----Original Message-----

[] On Behalf Of Cary Millsap Sent: Thursday, October 05, 2006 2:30 PM To:; oracle-l Subject: RE: script to see explain plan

One detail that may seem subtle, but it's important. Notice that EXPLAIN PLAN doesn't show an execution plan, it shows a *predicted* execution plan. Contrast this to the notion that there's a real execution plan in your trace data (if you've activated it) or in your V$ fixed views, but only after having executed the query in question.

Cary Millsap
Hotsos Enterprises, Ltd.
Nullius in verba

Hotsos Symposium 2007 / March 4-8 / Dallas Visit for curriculum and schedule details...

-----Original Message-----

[] On Behalf Of Wojciech Skrzynecki Sent: Thursday, October 05, 2006 4:04 PM To: oracle-l
Subject: script to see explain plan

Hello Everybody

I would like ask you to about explain plan. I am looking for the best script to see explain plan. I know that it is possible to see explain plan for active session of other users. I use script from metalink Note:260942.1. Maybe you use better scripts to see explain plan.


Wojciech Skrzynecki
Database Administrator

-- Received on Fri Oct 06 2006 - 01:31:32 CDT

Original text of this message