Session browser details tabs customize

[admin]

Hi,
I'm trying to create a query to put into the details tab of the session browser that will present an explain plan for the sql for a selected active session.

Looking for ideas as to how to approach this.
Thanks in advance for any ideas.

 

[Theod]

Hi,

I'm using this one

SELECT  child_number cn
       ,level
       ,lpad(' '
	   ,4 * LEVEL) || simple_plan "simple plan"
       ,pl.object_owner "Object Owner"
       ,object_name "object name"
       ,pl.object_type "Object type"
       ,trunc(bytes / 1024 / 1024
	    ,2) AS "size_mb"
       ,cost "cost"
       ,cardinality "cardinality"
       --
       ,pl.access_predicates
       ,pl.filter_predicates
       ,pl.projection
       ,pl.cpu_cost
       ,pl.io_cost
       ,pl.object_node
       ,pl.object#
       ,pl.object_alias
       ,pl.search_columns
       ,pl.other_tag
       ,pl.temp_space
       ,pl.TIME
       ,pl.hash_value
FROM   (SELECT p.id
	      ,p.parent_id
	      ,p.child_number
	      ,object_name
	      , operation || ' (' || options || ') ' || CASE
			WHEN p.optimizer IS NOT NULL THEN
			 ', GOAL = ' || p.optimizer
		END simple_plan
	      ,p.bytes
	      ,p.cost
	      ,p.cardinality
	       --
	      ,p.inst_id
	      ,p.address
	      ,p.hash_value
	      ,p.sql_id
	      ,p.plan_hash_value
	      ,p.timestamp
	      ,p.operation
	      ,p.options
	      ,p.object_node
	      ,p.object#
	      ,p.object_owner
	      ,p.object_alias
	      ,p.object_type
	      ,p.depth
	      ,p.position
	      ,p.search_columns
	      ,p.other_tag
	      ,p.partition_start
	      ,p.partition_stop
	      ,p.partition_id
	      ,p.other
	      ,p.distribution
	      ,p.cpu_cost
	      ,p.io_cost
	      ,p.temp_space
	      ,p.access_predicates
	      ,p.filter_predicates
	      ,p.projection
	      ,p.TIME
	      ,p.qblock_name
	      ,p.remarks
	      ,p.other_xml
	FROM   gv$sql_plan p, gv$session s
	WHERE  p.address = hextoraw(s.sql_address)
	AND    p.hash_value = s.sql_hash_value
	AND    s.sid = :SID
	) pl
START  WITH id = 0
CONNECT BY parent_id = PRIOR id
    AND    child_number = PRIOR child_number
ORDER  BY child_number, id

Since you may have more than one plan for your session/query (possibly by enable/disable parallel query) you can also add :sql_child_number from gv$session with x the query above.

SELECT * FROM (x) WHERE CN = :SQL_CHILD_NUMBER

 

[admin]

Thanks - works very nicely. Sorry for the delay in the reply.

 

[William Robertson]

OK this thread is 2 years old, but in case anyone is interested, a better approach is to use dbms_xplan, for example:

select plan_table_output
from   table
       ( dbms_xplan.display_cursor
         ( :sql_id
         , :sql_child_number
         , 'ADVANCED +peeked_binds -projection' ) )

(then copy and paste into a new SQL window for readability.)

I also have a version set up in Browser Extender that puts the current plan in a new SQL window.