DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
  b=wIJ/hjw7QkgN1DLwqN8FsyLto33bmUt/W6fLHkD5r3vWxjRxVUsSvqVDEhx+GUqAcT/dJIsG3F8kfAiXg759q+LNx17OPCyF3oAISDJbOa7eaobeB5TQE9lOITJZa2yGe973gQT06ac6qXFhmQlmr5PYCwWZfyhpewXTYMmSZR4=  ;
Message-ID: <20061006000342.51136.qmail@web31903.mail.mud.yahoo.com>
Date: Thu, 5 Oct 2006 17:03:42 -0700 (PDT)
From: fairlie rego <fairlie_r@yahoo.com>
Subject: RE: script to see explain plan
To: SValiveru@looksmart.net
Cc: oracle-l <oracle-l@freelists.org>
In-Reply-To: <8F8AA525BF5CCB40BF4FA4A6156D6F270B9E27AE@sfex3k2.looksmart_sf.ad.looksmart.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-856004424-1160093022=:50639"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: fairlie_r@yahoo.com
--0-856004424-1160093022=:50639
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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@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@freelists.org
[mailto:oracle-l-bounce@freelists.org] On Behalf Of Cary Millsap
Sent: Thursday, October 05, 2006 2:30 PM
To: wojciech.skrzynecki@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@freelists.org
[mailto:oracle-l-bounce@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.
--0-856004424-1160093022=:50639
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

<div>As Mark said u don't need this script anymore in 10G.</div>  <div>&nbsp;</div>  <div>You could use </div>  <div>&nbsp;</div>  <div>set pagesize 500<BR>set linesize 500</div>  <div>&nbsp;</div>  <div>If the plan is not flushed out use</div>  <div>select * from table(dbms_xplan.display_cursor('SQL_ID'));</div>  <div>&nbsp;</div>  <div>or</div>  <div>&nbsp;</div>  <div>from AWR use</div>  <div>select * from table(dbms_xplan.display_awr('SQL_ID'));</div>  <div>&nbsp;</div>  <div>Regards,</div>  <div>Fairlie<BR><BR><B><I>Siva Valiveru &lt;SValiveru@looksmart.net&gt;</I></B> wrote:</div>  <BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">Right for the real actual plan already got executed and if the plan is<BR>not flushed out from buffer here is script I use.<BR><BR>set long 100000 lines 200 pages 1000<BR><BR>delete from plan_table where statement_id = 'MYQUERY';<BR>insert into plan_table<BR>select<BR>'xx' statement_id, 0
 plan_id, timestamp, NULL remarks,<BR>a.operation, a.options, a.object_node, a.object_owner,<BR>a.object_name,<BR>object_alias, null object_instance, object_type,<BR>a.optimizer,<BR>a.search_columns, a.id, a.parent_id, a.depth, a.position,<BR>a.cost,<BR>a.cardinality, a.bytes, a.other_tag, a.partition_start,<BR>a.partition_stop, a.partition_id, a.other, a.distribution,<BR>a.cpu_cost, a.io_cost, a.temp_space, a.access_predicates,<BR>a.filter_predicates, projection, time<BR>,qblock_name ,OTHER_XML<BR>from v$sql_plan a<BR>where sql_id = '&amp;&amp;SQL_ID';<BR><BR>--SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table',<BR>'MYQUERY','ALL'));<BR>SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'MYQUERY'));<BR>rollback;<BR><BR><BR><BR>BTW, Jonathan lewis(? I think) presented a method to get real execution<BR>plan, without actually fethching data. Anyone got that url?<BR><BR>Something like <BR><BR>Declare<BR>Cursor c1 for select * from tab;<BR>Begin<BR>Open c1;<BR>Close
 c1;<BR>End;<BR><BR>And then get the sql_id and query v$sql_plan..<BR><BR><BR><BR>-----Original Message-----<BR>From: oracle-l-bounce@freelists.org<BR>[mailto:oracle-l-bounce@freelists.org] On Behalf Of Cary Millsap<BR>Sent: Thursday, October 05, 2006 2:30 PM<BR>To: wojciech.skrzynecki@gmail.com; oracle-l<BR>Subject: RE: script to see explain plan<BR><BR>One detail that may seem subtle, but it's important. Notice that EXPLAIN<BR>PLAN doesn't show an execution plan, it shows a *predicted* execution<BR>plan. Contrast this to the notion that there's a real execution plan in<BR>your trace data (if you've activated it) or in your V$ fixed views, but<BR>only after having executed the query in question.<BR><BR><BR>Cary Millsap<BR>Hotsos Enterprises, Ltd.<BR>http://www.hotsos.com<BR>Nullius in verba<BR><BR>Hotsos Symposium 2007 / March 4-8 / Dallas Visit www.hotsos.com for<BR>curriculum and schedule details...<BR><BR>-----Original Message-----<BR>From:
 oracle-l-bounce@freelists.org<BR>[mailto:oracle-l-bounce@freelists.org] On Behalf Of Wojciech Skrzynecki<BR>Sent: Thursday, October 05, 2006 4:04 PM<BR>To: oracle-l<BR>Subject: script to see explain plan<BR><BR>Hello Everybody<BR><BR><BR>I would like ask you to about explain plan. I am looking for the best<BR>script to see explain plan. I know that it is possible to see explain<BR>plan for active session of other users. I use script from metalink<BR>Note:260942.1. Maybe you use better scripts to see explain plan.<BR><BR><BR>--<BR>Wojciech Skrzynecki<BR>Database Administrator<BR>--<BR>http://www.freelists.org/webpage/oracle-l<BR><BR><BR>--<BR>http://www.freelists.org/webpage/oracle-l<BR><BR><BR>--<BR>http://www.freelists.org/webpage/oracle-l<BR><BR><BR></BLOCKQUOTE><BR><BR><BR><DIV>  <DIV>  <DIV>  <DIV>  <DIV>  <DIV><STRONG>Fairlie Rego<BR></STRONG>Senior Oracle Consultant</DIV>  <DIV><A href="http://www.optus.com.au/"></A></DIV>  <DIV><FONT face="comic sans ms"><A
 href="http://el-caro.blogspot.com/">http://el-caro.blogspot.com/</A></FONT></DIV>  <DIV><FONT face="Comic Sans MS">M: +61 402 792 405</FONT></DIV>  <DIV>&nbsp;</DIV></DIV></DIV></DIV></DIV></DIV><p>&#32;
		<hr size=1>Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. <a href="http://us.rd.yahoo.com/mail_us/taglines/postman7/*http://us.rd.yahoo.com/evt=39666/*http://messenger.yahoo.com"> Great rates starting at 1¢/min.
--0-856004424-1160093022=:50639--
--
http://www.freelists.org/webpage/oracle-l