Don't do a "connect by" on v$sql_plan. There
is a 'depth' column which exists to emulate the
"connect by" level. You can then do an "order by id"
to get (most) plans reported in the right order.
You will need to include a child-number to get
a single plan corresponding to a single child
cursor in v$sql, by the way.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"MAK" <maks70_at_comcast.net> wrote in message
news:b7178504.0311272231.3b9b064e_at_posting.google.com...
> Hello,
>
> I am trying to get sql plan from v$sql_plan based on address & hash
> values but the query never returns. I used following query. I saw my
> session was alway waiting for ' SQL*Net message to client' event. I am
> runnning 9.2.0.3 on AIX 5.1.
>
> select lpad(' ', 2*(level-1))||operation||' '||
> decode(id, 0, 'Cost = '||position) "OPERATION",
> options, object_name
> from v$sql_plan
> where address = '&addr' and
> hash_value = 'hvalue'
> start with id = 0
> connect by prior id = parent_id
>
>
> If I remove, "start with id = 0 connect by prior id = parent_id " ,
> the query comes back rightaway.
>
> Any idea how to get around this. I'm trying to get explain plan in
> nicely formated fashion. Any other alternatives are welcome.
>
> Thanks
Received on Fri Nov 28 2003 - 15:48:21 CST
