VIEW: SYS.USER_SQLTUNE_RATIONALE_PLAN

Source

View Text - Preformatted

SELECT rp.task_id, rp.exec_name as execution_name,
         rp.rtn_id AS rationale_id, rp.object_id, rp.operation_id,
         (case when rp.plan_attr < power(2, 16) then
           decode(rp.plan_attr,
                  0, 'Original',
                  1, 'Original with adjusted cost',
                  2, 'Using SQL profile',
                  3, 'Using new indices',
                  7, 'Using parallel execution')
           when rp.plan_attr > 3*power(2, 16) and
                rp.plan_attr < 4*power(2, 16) then
              'Plan from workload repository'
           when rp.plan_attr > 4*power(2, 16) and
                rp.plan_attr < 5*power(2, 16) then
              'Plan from cursor cache'
           when rp.plan_attr > 5*power(2, 16) and
                rp.plan_attr < 6*power(2, 16) then
              'Plan from SQL tuning set'
           when rp.plan_attr > 6*power(2, 16) then
              'Plan from SQL performance analyzer' end)
                AS plan_attribute
  FROM   wri$_adv_sqlt_rtn_plan rp, wri$_adv_objects o, wri$_adv_tasks t
  WHERE  rp.object_id = o.id and rp.task_id = o.task_id and
         o.task_id = t.id and
         o.exec_name = rp.exec_name and
         t.owner# = SYS_CONTEXT('USERENV', 'CURRENT_USERID')

View Text - HTML Formatted

SELECT RP.TASK_ID
, RP.EXEC_NAME AS EXECUTION_NAME
, RP.RTN_ID AS RATIONALE_ID
, RP.OBJECT_ID
, RP.OPERATION_ID
, (CASE WHEN RP.PLAN_ATTR < POWER(2
, 16) THEN DECODE(RP.PLAN_ATTR
, 0
, 'ORIGINAL'
, 1
, 'ORIGINAL WITH ADJUSTED COST'
, 2
, 'USING SQL PROFILE'
, 3
, 'USING NEW INDICES'
, 7
, 'USING PARALLEL EXECUTION') WHEN RP.PLAN_ATTR > 3*POWER(2
, 16) AND RP.PLAN_ATTR < 4*POWER(2
, 16) THEN 'PLAN
FROM WORKLOAD REPOSITORY' WHEN RP.PLAN_ATTR > 4*POWER(2
, 16) AND RP.PLAN_ATTR < 5*POWER(2
, 16) THEN 'PLAN
FROM CURSOR CACHE' WHEN RP.PLAN_ATTR > 5*POWER(2
, 16) AND RP.PLAN_ATTR < 6*POWER(2
, 16) THEN 'PLAN
FROM SQL TUNING SET' WHEN RP.PLAN_ATTR > 6*POWER(2
, 16) THEN 'PLAN
FROM SQL PERFORMANCE ANALYZER' END) AS PLAN_ATTRIBUTE
FROM WRI$_ADV_SQLT_RTN_PLAN RP
, WRI$_ADV_OBJECTS O
, WRI$_ADV_TASKS T
WHERE RP.OBJECT_ID = O.ID
AND RP.TASK_ID = O.TASK_ID AND O.TASK_ID = T.ID AND O.EXEC_NAME = RP.EXEC_NAME AND T.OWNER# = SYS_CONTEXT('USERENV'
, 'CURRENT_USERID')