Re: Mysterious V$SQL_PLAN ;)

>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