Re: fetching from V$SQL_PLAN doesn't return

From: MAK <maks70_at_comcast.net>
Date: 30 Nov 2003 12:33:47 -0800
Message-ID: <b7178504.0311301233.66a418f9@posting.google.com>

Thank you all for exellent ideas... I found the script on asktom.oracle.com site. Tom had modified utlxpls.sql to go against V$sql_plan and that's great.
Idea of using dbms_xplan makes much more sense..

I have took all that info and put together a simpler version. here is the one.
It does not produce the nice output as dbms_xplan but works for me.

variable addr varchar2(20)
variable hash number
variable child number

exec :addr := '&sqladdr'; :hash :=  &hashvalue; :child := &childno;
select lpad(' ', 2*(level-1))||operation||' '||
decode(id, 0, 'Cost = '||position) "OPERATION",

options, object_name
from v$sql_plan
start with (address = :addr

              and hash_value = :hash
              and child_number = :child
              and id=0 )
  connect by prior id = parent_id
          and prior address = address
          and prior hash_value = hash_value
          and prior child_number = child_number

  order by id, position
; Received on Sun Nov 30 2003 - 14:33:47 CST