VIEW: SYS.DBA_ADVISOR_SQLPLANS

Source

View Text - Preformatted

SELECT t.name task_name, h.task_id,
         h.exec_name as execution_name,
         h.sql_id,
         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,
         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.plan_id = p.plan_id and h.task_id = t.id

View Text - HTML Formatted

SELECT T.NAME TASK_NAME
, H.TASK_ID
, H.EXEC_NAME AS EXECUTION_NAME
, H.SQL_ID
, 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
, 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.PLAN_ID = P.PLAN_ID
AND H.TASK_ID = T.ID