Can you please post the exact detail how to create the view and use.
Thanks.
Andy Hassall <andy_at_andyh.co.uk> wrote in message news:<2jmfsvsj9ss6erjaubgeisv8f087mo0h90_at_4ax.com>...
> On 27 Nov 2003 22:31:29 -0800, maks70_at_comcast.net (MAK) wrote:
>
> >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.
>
> If you're on 9.2, take advantage of DBMS_XPLAN, it produces nicely formatted
> explain plans including the new access and filter predicates.
>
> It works most easily from a plan PLAN_TABLE, but there's an example in Thomas
> Kyte's new book Effective Oracle by Design for getting it to display plans out
> of v$sql_plan - basically create a view against v$sql_plan so it looks like a
> PLAN_TABLE, with the address/hash fields concatenated to form a statement_id
> column, then use DBMS_XPLAN specifying the table_name and statement_id
> parameters to get it to read from the view and select the plan for the
> statement you want.
Received on Fri Dec 05 2003 - 16:20:55 CST
