VIEW: SYS.USER_SQLTUNE_PLANS

Source

View Text - Preformatted

SELECT h.task_id,
         h.exec_name as execution_name,
         h.object_id,
         (case when h.attribute < power(2, 16) then
           decode(h.attribute,
                0, 'Original',
                1, 'Original with adjusted cost',
                2, 'Using SQL profile',
                3, 'Using new indices',
                5, 'Using baseline plan',
                6, 'Using non-accepted plan',
                7, 'Using parallel execution')
                when h.attribute > 3*power(2, 16) and
                     h.attribute < 4*power(2, 16) then
                     'Plan from workload repository'
                when h.attribute > 4*power(2, 16) and
                     h.attribute < 5*power(2, 16) then
                     'Plan from cursor cache'
                when h.attribute > 5*power(2, 16) and
                     h.attribute < 6*power(2, 16) then
                     'Plan from SQL tuning set'
                when h.attribute > 6*power(2, 16) then
                     'Plan from SQL performance analyzer' end)
                AS attribute,
         p.statement_id,
         h.plan_hash as PLAN_HASH_VALUE,
         h.plan_id,
         p.timestamp,
         p.remarks,
         p.operation,
         p.options,
         p.object_node,
         p.object_owner,
         p.object_name,
         p.object_alias,
         p.object_instance,
         p.object_type,
         p.optimizer,
         p.search_columns,
         p.id,
         p.parent_id,
         p.depth,
         p.position,
         p.cost,
         p.cardinality,
         p.bytes,
         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.other_xml
  FROM   wri$_adv_sqlt_plan_hash h, wri$_adv_sqlt_plans p, wri$_adv_tasks t
  WHERE  h.task_id = t.id and h.plan_id = p.plan_id and
         t.owner# = SYS_CONTEXT('USERENV', 'CURRENT_USERID')

View Text - HTML Formatted

SELECT H.TASK_ID
, H.EXEC_NAME AS EXECUTION_NAME
, H.OBJECT_ID
, (CASE WHEN H.ATTRIBUTE < POWER(2
, 16) THEN DECODE(H.ATTRIBUTE
, 0
, 'ORIGINAL'
, 1
, 'ORIGINAL WITH ADJUSTED COST'
, 2
, 'USING SQL PROFILE'
, 3
, 'USING NEW INDICES'
, 5
, 'USING BASELINE PLAN'
, 6
, 'USING NON-ACCEPTED PLAN'
, 7
, 'USING PARALLEL EXECUTION') WHEN H.ATTRIBUTE > 3*POWER(2
, 16) AND H.ATTRIBUTE < 4*POWER(2
, 16) THEN 'PLAN
FROM WORKLOAD REPOSITORY' WHEN H.ATTRIBUTE > 4*POWER(2
, 16) AND H.ATTRIBUTE < 5*POWER(2
, 16) THEN 'PLAN
FROM CURSOR CACHE' WHEN H.ATTRIBUTE > 5*POWER(2
, 16) AND H.ATTRIBUTE < 6*POWER(2
, 16) THEN 'PLAN
FROM SQL TUNING SET' WHEN H.ATTRIBUTE > 6*POWER(2
, 16) THEN 'PLAN
FROM SQL PERFORMANCE ANALYZER' END) AS ATTRIBUTE
, P.STATEMENT_ID
, H.PLAN_HASH AS PLAN_HASH_VALUE
, H.PLAN_ID
, P.TIMESTAMP
, P.REMARKS
, P.OPERATION
, P.OPTIONS
, P.OBJECT_NODE
, P.OBJECT_OWNER
, P.OBJECT_NAME
, P.OBJECT_ALIAS
, P.OBJECT_INSTANCE
, P.OBJECT_TYPE
, P.OPTIMIZER
, P.SEARCH_COLUMNS
, P.ID
, P.PARENT_ID
, P.DEPTH
, P.POSITION
, P.COST
, P.CARDINALITY
, P.BYTES
, 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.OTHER_XML
FROM WRI$_ADV_SQLT_PLAN_HASH H
, WRI$_ADV_SQLT_PLANS P
, WRI$_ADV_TASKS T
WHERE H.TASK_ID = T.ID
AND H.PLAN_ID = P.PLAN_ID AND T.OWNER# = SYS_CONTEXT('USERENV'
, 'CURRENT_USERID')