Re: fetching from V$SQL_PLAN doesn't return

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.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)