VIEW: SYS.ALL_SQLSET_PLANS

Source

View Text - Preformatted

select d.name as sqlset_name, d.owner as sqlset_owner, sqlset_id, s.con_dbid,
       sql_id, force_matching_signature, p.plan_hash_value, s.id as sql_seq,
       statement_id,
       plan_id,
       timestamp,
       remarks,
       operation,
       options,
       object_node,
       object_owner,
       object_name,
       object_alias,
       object_instance,
       object_type,
       optimizer,
       search_columns,
       l.id,
       parent_id,
       depth,
       position,
       cost,
       cardinality,
       bytes,
       other_tag,
       partition_start,
       partition_stop,
       partition_id,
       other,
       distribution,
       cpu_cost,
       io_cost,
       temp_space,
       access_predicates,
       filter_predicates,
       projection,
       time,
       qblock_name,
       other_xml,
       executions,
       starts,
       output_rows,
       cr_buffer_gets,
       cu_buffer_gets,
       disk_reads,
       disk_writes,
       elapsed_time,
       last_starts,
       last_output_rows,
       last_cr_buffer_gets,
       last_cu_buffer_gets,
       last_disk_reads,
       last_disk_writes,
       last_elapsed_time,
       policy,
       estimated_optimal_size,
       estimated_onepass_size,
       last_memory_used,
       last_execution,
       last_degree,
       total_executions,
       optimal_executions,
       onepass_executions,
       multipasses_executions,
       active_time,
       max_tempseg_size,
       last_tempseg_size
from   WRI$_SQLSET_DEFINITIONS d, WRI$_SQLSET_STATEMENTS s,
       WRI$_SQLSET_PLANS p, WRI$_SQLSET_PLAN_LINES l
where  d.id = s.sqlset_id  and s.id = p.stmt_id and
       s.con_dbid = p.con_dbid and
       p.stmt_id=l.stmt_id and p.plan_hash_value = l.plan_hash_value and
       p.con_dbid = l.con_dbid and
       (d.owner = SYS_CONTEXT('USERENV', 'CURRENT_USER') OR
       EXISTS (select 1
               from   V$ENABLEDPRIVS
               where  priv_number in (-273 /*ADMINISTER ANY SQL TUNING SET*/)))

View Text - HTML Formatted

SELECT D.NAME AS SQLSET_NAME
, D.OWNER AS SQLSET_OWNER
, SQLSET_ID
, S.CON_DBID
, SQL_ID
, FORCE_MATCHING_SIGNATURE
, P.PLAN_HASH_VALUE
, S.ID AS SQL_SEQ
, STATEMENT_ID
, PLAN_ID
, TIMESTAMP
, REMARKS
, OPERATION
, OPTIONS
, OBJECT_NODE
, OBJECT_OWNER
, OBJECT_NAME
, OBJECT_ALIAS
, OBJECT_INSTANCE
, OBJECT_TYPE
, OPTIMIZER
, SEARCH_COLUMNS
, L.ID
, PARENT_ID
, DEPTH
, POSITION
, COST
, CARDINALITY
, BYTES
, OTHER_TAG
, PARTITION_START
, PARTITION_STOP
, PARTITION_ID
, OTHER
, DISTRIBUTION
, CPU_COST
, IO_COST
, TEMP_SPACE
, ACCESS_PREDICATES
, FILTER_PREDICATES
, PROJECTION
, TIME
, QBLOCK_NAME
, OTHER_XML
, EXECUTIONS
, STARTS
, OUTPUT_ROWS
, CR_BUFFER_GETS
, CU_BUFFER_GETS
, DISK_READS
, DISK_WRITES
, ELAPSED_TIME
, LAST_STARTS
, LAST_OUTPUT_ROWS
, LAST_CR_BUFFER_GETS
, LAST_CU_BUFFER_GETS
, LAST_DISK_READS
, LAST_DISK_WRITES
, LAST_ELAPSED_TIME
, POLICY
, ESTIMATED_OPTIMAL_SIZE
, ESTIMATED_ONEPASS_SIZE
, LAST_MEMORY_USED
, LAST_EXECUTION
, LAST_DEGREE
, TOTAL_EXECUTIONS
, OPTIMAL_EXECUTIONS
, ONEPASS_EXECUTIONS
, MULTIPASSES_EXECUTIONS
, ACTIVE_TIME
, MAX_TEMPSEG_SIZE
, LAST_TEMPSEG_SIZE FROM WRI$_SQLSET_DEFINITIONS D
, WRI$_SQLSET_STATEMENTS S
, WRI$_SQLSET_PLANS P
, WRI$_SQLSET_PLAN_LINES L WHERE D.ID = S.SQLSET_ID
AND S.ID = P.STMT_ID AND S.CON_DBID = P.CON_DBID AND P.STMT_ID=L.STMT_ID
AND P.PLAN_HASH_VALUE = L.PLAN_HASH_VALUE AND P.CON_DBID = L.CON_DBID AND (D.OWNER = SYS_CONTEXT('USERENV'
, 'CURRENT_USER') OR EXISTS (SELECT 1
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-273 /*ADMINISTER ANY SQL TUNING SET*/)))