RE: script to see explain plan

From: Siva Valiveru <SValiveru_at_looksmart.net>
Date: Thu, 5 Oct 2006 14:40:45 -0700
Message-ID: <8F8AA525BF5CCB40BF4FA4A6156D6F270B9E27AE@sfex3k2.looksmart_sf.ad.looksmart.com>

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
  select

            'xx' statement_id, 0 plan_id,  timestamp, NULL remarks,
            a.operation, a.options, a.object_node, a.object_owner,
a.object_name,
            object_alias,  null object_instance, object_type,
a.optimizer,
            a.search_columns, a.id, a.parent_id, a.depth, a.position,
a.cost,
            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',
'MYQUERY','ALL'));
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

Declare
Cursor c1 for select * from tab;
Begin
Open c1;
Close c1;
End;

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

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Cary Millsap Sent: Thursday, October 05, 2006 2:30 PM To: wojciech.skrzynecki_at_gmail.com; 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.
http://www.hotsos.com
Nullius in verba  

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

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 05 2006 - 16:40:45 CDT