Re: fetching from V$SQL_PLAN doesn't return

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 28 Nov 2003 06:48:25 -0600
Message-ID: <uznegacgl.fsf@standardandpoors.com>

I have never used the v$sql_plan. I wrote the following as "explain.sql" and put it in my SQLPATH.

set linesize 300
set pagesize 45
set long 20
column query_plan format A110 truncate
column other format A15 truncate
column other_tag format A30 truncate
column object_node format A15 truncate
column PART format A4
column PARR format A4

SELECT level,

	RTRIM (LPAD (' ', 2 * level) ||	RTRIM (operation) || ' ' || RTRIM (options) || ' ' || object_name) query_plan


       ,decode(object_node,NULL,'N','YES') PARR

       ,decode(partition_id,NULL,'N','YES') PART

       ,cost

FROM	plan_table

CONNECT BY PRIOR ID = PARENT_ID
START WITH ID = 0
/

The plan_table's definition can be found in $ORACLE_HOME/rdbms/admin/utlxplan.sql

-- 
Galen Boyer

Received on Fri Nov 28 2003 - 06:48:25 CST