Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Mysterious V$SQL_PLAN ;)

Re: Mysterious V$SQL_PLAN ;)

From: <J.Velikanovs_at_alise.lv>
Date: Tue, 4 Jan 2005 18:23:37 +0200
Message-ID: <OF8CFB5258.DCA77E34-ONC2256F7F.0059BCE9-C2256F7F.005A626B@alise.lv>


>Does anyone have a good select statement that prints the plan from
>v$sql_plan nicely?

I like this solution ;)

insert into plan_table
select ADDRESS, sysdate, 'REMARKS', OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, 0, 'OBJECT_TYPE', OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID, POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES from  v$sql_plan
where
 address='673C9E9C'; -- Your SQL address

select * from table(dbms_xplan.display);

PS Original source http://www.quest-pipelines.com/pipelines/dba/tips.htm

On 04.01.2005 18:02:07 oracle-l-bounce wrote:

>On Tue, 4 Jan 2005 15:28:45 +0000, Niall Litchfield
><niall.litchfield_at_gmail.com> wrote:
>> > The only opportunity to see real explain plan is to check V$SQL_PLAN,
by
>> > my opinion. Or I am wrong?
>>
>> A problem in 8i, and in 10 you have DBA_HIST_SQL_PLAN for historical
>> plans. The latter is in principle great for folks like me who have
>> long suggested that a change in execution plan is worth investigating
>> secure in the knowledge that determining whether a plan has changed or
>> not is somewhat difficult in earlier versions...
>>
>> --
>> Niall Litchfield
>
>In some sense I wonder why it took so long for this view to show up.
>It has been needed for a long time.
>
>Does anyone have a good select statement that prints the plan from
>v$sql_plan nicely?
>
>Thanks,
>Steven Patenaude
>--
>http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 04 2005 - 10:22:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US