VIEW: SYS.USER_ADVISOR_SQLSTATS

Source

View Text - Preformatted

SELECT t.name task_name, p.TASK_ID,
         exec_name EXECUTION_NAME, exec_type EXECUTION_TYPE,
         OBJECT_ID, p.plan_id, p.sql_id,
         p.PLAN_HASH as plan_hash_value,
         p.spare_n1 as attr1,
         -- the time stats in the old version are in milliseconds. In the
         -- new code, we used a bit in "flags" to indicate that if the
         -- time stats are in microseconds in the new code. So if
         -- the flags are not set or NULL, we will need to convert them
         -- into microseconds.
         decode(bitand(nvl(s.flags, 0),2), 2, parse_time, parse_time*1000)
                as PARSE_TIME,
         decode(bitand(nvl(s.flags, 0),2), 2, exec_time, exec_time*1000)
                as ELAPSED_TIME,
         decode(bitand(nvl(s.flags, 0),2), 2, cpu_time, cpu_time*1000)
                as CPU_TIME,
         decode(bitand(nvl(s.flags, 0),2), 2, user_io_time, user_io_time*1000)
                as USER_IO_TIME,
         BUFFER_GETS, DISK_READS, DIRECT_WRITES,
         s.spare_n1 PHYSICAL_READ_REQUESTS, s.spare_n2 PHYSICAL_WRITE_REQUESTS,
         s.spare_n3 PHYSICAL_READ_BYTES, s.spare_n4 PHYSICAL_WRITE_BYTES,
         ROWS_PROCESSED, FETCHES, EXECUTIONS,
         END_OF_FETCH_COUNT, OPTIMIZER_COST, OTHER, TESTEXEC_TOTAL_EXECS,
         io_interconnect_bytes,
         decode(bitand(s.flags,1), 1, 'Y', 'N') as TESTEXEC_FIRST_EXEC_IGNORED,
         p.con_dbid
  FROM   wri$_adv_sqlt_plan_hash p,
         wri$_adv_sqlt_plan_stats s,
         wri$_adv_executions e,
         wri$_adv_tasks t
  WHERE  p.plan_id = s.plan_id AND
         p.exec_name = e.name AND
         p.task_id = e.task_id AND
         p.task_id = t.id AND
         t.owner# = SYS_CONTEXT('USERENV', 'CURRENT_USERID') AND
         (p.attribute < power (2,16) OR
          p.attribute >= 3*power (2, 16)) /* hide special plans */

View Text - HTML Formatted

SELECT T.NAME TASK_NAME
, P.TASK_ID
, EXEC_NAME EXECUTION_NAME
, EXEC_TYPE EXECUTION_TYPE
, OBJECT_ID
, P.PLAN_ID
, P.SQL_ID
, P.PLAN_HASH AS PLAN_HASH_VALUE
, P.SPARE_N1 AS ATTR1
, -- THE TIME STATS IN THE OLD VERSION ARE IN MILLISECONDS. IN THE -- NEW CODE
, WE USED A BIT IN "FLAGS" TO INDICATE THAT IF THE -- TIME STATS ARE IN MICROSECONDS IN THE NEW CODE. SO IF -- THE FLAGS ARE NOT SET OR NULL
, WE WILL NEED TO CONVERT THEM -- INTO MICROSECONDS. DECODE(BITAND(NVL(S.FLAGS
, 0)
, 2)
, 2
, PARSE_TIME
, PARSE_TIME*1000) AS PARSE_TIME
, DECODE(BITAND(NVL(S.FLAGS
, 0)
, 2)
, 2
, EXEC_TIME
, EXEC_TIME*1000) AS ELAPSED_TIME
, DECODE(BITAND(NVL(S.FLAGS
, 0)
, 2)
, 2
, CPU_TIME
, CPU_TIME*1000) AS CPU_TIME
, DECODE(BITAND(NVL(S.FLAGS
, 0)
, 2)
, 2
, USER_IO_TIME
, USER_IO_TIME*1000) AS USER_IO_TIME
, BUFFER_GETS
, DISK_READS
, DIRECT_WRITES
, S.SPARE_N1 PHYSICAL_READ_REQUESTS
, S.SPARE_N2 PHYSICAL_WRITE_REQUESTS
, S.SPARE_N3 PHYSICAL_READ_BYTES
, S.SPARE_N4 PHYSICAL_WRITE_BYTES
, ROWS_PROCESSED
, FETCHES
, EXECUTIONS
, END_OF_FETCH_COUNT
, OPTIMIZER_COST
, OTHER
, TESTEXEC_TOTAL_EXECS
, IO_INTERCONNECT_BYTES
, DECODE(BITAND(S.FLAGS
, 1)
, 1
, 'Y'
, 'N') AS TESTEXEC_FIRST_EXEC_IGNORED
, P.CON_DBID
FROM WRI$_ADV_SQLT_PLAN_HASH P
, WRI$_ADV_SQLT_PLAN_STATS S
, WRI$_ADV_EXECUTIONS E
, WRI$_ADV_TASKS T
WHERE P.PLAN_ID = S.PLAN_ID AND P.EXEC_NAME = E.NAME AND P.TASK_ID = E.TASK_ID AND P.TASK_ID = T.ID AND T.OWNER# = SYS_CONTEXT('USERENV'
, 'CURRENT_USERID') AND (P.ATTRIBUTE < POWER (2
, 16) OR P.ATTRIBUTE >= 3*POWER (2
, 16)) /* HIDE SPECIAL PLANS */