| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: script to see explain plan
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"
Regards
Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
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
--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
Received on Fri Oct 06 2006 - 01:31:32 CDT
 |
 |