RE: script to see explain plan
- From: fairlie rego <fairlie_r@xxxxxxxxx>
- To: SValiveru@xxxxxxxxxxxxx
- Date: Thu, 5 Oct 2006 17:03:42 -0700 (PDT)
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@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Cary Millsap
Sent: Thursday, October 05, 2006 2:30 PM
To: wojciech.skrzynecki@xxxxxxxxx; 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//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.
Other related posts:
