RE: script to see explain plan

From: fairlie rego <fairlie_r_at_yahoo.com>
Date: Thu, 5 Oct 2006 17:03:42 -0700 (PDT)
Message-ID: <20061006000342.51136.qmail@web31903.mail.mud.yahoo.com>

As Mark said u don't need this script anymore in 10G.    

  You could use    

  set pagesize 500
set linesize 500    

  If the plan is not flushed out use
  select * from table(dbms_xplan.display_cursor('SQL_ID'));    

  or    

  from AWR use
  select * from table(dbms_xplan.display_awr('SQL_ID'));    

  Regards,
  Fairlie

Siva Valiveru <SValiveru_at_looksmart.net> wrote:   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

          Fairlie Rego
Senior Oracle Consultant   

  http://el-caro.blogspot.com/
  M: +61 402 792 405                      

---

Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min.
--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 05 2006 - 19:03:42 CDT