VIEW: SYS.INT$DBA_HIST_SQLSTAT

Source

View Text - Preformatted

select sql.snap_id, sql.dbid, sql.instance_number,
       sql_id, plan_hash_value,
       optimizer_cost, optimizer_mode, optimizer_env_hash_value,
       sharable_mem, loaded_versions, version_count,
       substrb(module,1,(select ksumodlen from x$modact_length)) module,
       substrb(action,1,(select ksuactlen from x$modact_length)) action,
       sql_profile, force_matching_signature,
       parsing_schema_id, parsing_schema_name, parsing_user_id,
       fetches_total, fetches_delta,
       end_of_fetch_count_total, end_of_fetch_count_delta,
       sorts_total, sorts_delta,
       executions_total, executions_delta,
       px_servers_execs_total, px_servers_execs_delta,
       loads_total, loads_delta,
       invalidations_total, invalidations_delta,
       parse_calls_total, parse_calls_delta, disk_reads_total,
       disk_reads_delta, buffer_gets_total, buffer_gets_delta,
       rows_processed_total, rows_processed_delta, cpu_time_total,
       cpu_time_delta, elapsed_time_total, elapsed_time_delta,
       iowait_total, iowait_delta, clwait_total, clwait_delta,
       apwait_total, apwait_delta, ccwait_total, ccwait_delta,
       direct_writes_total, direct_writes_delta, plsexec_time_total,
       plsexec_time_delta, javexec_time_total, javexec_time_delta,
       io_offload_elig_bytes_total, io_offload_elig_bytes_delta,
       io_interconnect_bytes_total, io_interconnect_bytes_delta,
       physical_read_requests_total, physical_read_requests_delta,
       physical_read_bytes_total, physical_read_bytes_delta,
       physical_write_requests_total, physical_write_requests_delta,
       physical_write_bytes_total, physical_write_bytes_delta,
       optimized_physical_reads_total, optimized_physical_reads_delta,
       cell_uncompressed_bytes_total, cell_uncompressed_bytes_delta,
       io_offload_return_bytes_total, io_offload_return_bytes_delta,
       bind_data, sql.flag,
       decode(sql.con_dbid, 0, sql.dbid, sql.con_dbid),
       con_dbid_to_id(decode(sql.con_dbid, 0, sql.dbid, sql.con_dbid)) con_id
from DBA_HIST_SNAPSHOT sn, WRH$_SQLSTAT sql
  where     sn.snap_id         = sql.snap_id
        and sn.dbid            = sql.dbid
        and sn.instance_number = sql.instance_number

View Text - HTML Formatted

SELECT SQL.SNAP_ID
, SQL.DBID
, SQL.INSTANCE_NUMBER
, SQL_ID
, PLAN_HASH_VALUE
, OPTIMIZER_COST
, OPTIMIZER_MODE
, OPTIMIZER_ENV_HASH_VALUE
, SHARABLE_MEM
, LOADED_VERSIONS
, VERSION_COUNT
, SUBSTRB(MODULE
, 1
, (SELECT KSUMODLEN
FROM X$MODACT_LENGTH)) MODULE
, SUBSTRB(ACTION
, 1
, (SELECT KSUACTLEN
FROM X$MODACT_LENGTH)) ACTION
, SQL_PROFILE
, FORCE_MATCHING_SIGNATURE
, PARSING_SCHEMA_ID
, PARSING_SCHEMA_NAME
, PARSING_USER_ID
, FETCHES_TOTAL
, FETCHES_DELTA
, END_OF_FETCH_COUNT_TOTAL
, END_OF_FETCH_COUNT_DELTA
, SORTS_TOTAL
, SORTS_DELTA
, EXECUTIONS_TOTAL
, EXECUTIONS_DELTA
, PX_SERVERS_EXECS_TOTAL
, PX_SERVERS_EXECS_DELTA
, LOADS_TOTAL
, LOADS_DELTA
, INVALIDATIONS_TOTAL
, INVALIDATIONS_DELTA
, PARSE_CALLS_TOTAL
, PARSE_CALLS_DELTA
, DISK_READS_TOTAL
, DISK_READS_DELTA
, BUFFER_GETS_TOTAL
, BUFFER_GETS_DELTA
, ROWS_PROCESSED_TOTAL
, ROWS_PROCESSED_DELTA
, CPU_TIME_TOTAL
, CPU_TIME_DELTA
, ELAPSED_TIME_TOTAL
, ELAPSED_TIME_DELTA
, IOWAIT_TOTAL
, IOWAIT_DELTA
, CLWAIT_TOTAL
, CLWAIT_DELTA
, APWAIT_TOTAL
, APWAIT_DELTA
, CCWAIT_TOTAL
, CCWAIT_DELTA
, DIRECT_WRITES_TOTAL
, DIRECT_WRITES_DELTA
, PLSEXEC_TIME_TOTAL
, PLSEXEC_TIME_DELTA
, JAVEXEC_TIME_TOTAL
, JAVEXEC_TIME_DELTA
, IO_OFFLOAD_ELIG_BYTES_TOTAL
, IO_OFFLOAD_ELIG_BYTES_DELTA
, IO_INTERCONNECT_BYTES_TOTAL
, IO_INTERCONNECT_BYTES_DELTA
, PHYSICAL_READ_REQUESTS_TOTAL
, PHYSICAL_READ_REQUESTS_DELTA
, PHYSICAL_READ_BYTES_TOTAL
, PHYSICAL_READ_BYTES_DELTA
, PHYSICAL_WRITE_REQUESTS_TOTAL
, PHYSICAL_WRITE_REQUESTS_DELTA
, PHYSICAL_WRITE_BYTES_TOTAL
, PHYSICAL_WRITE_BYTES_DELTA
, OPTIMIZED_PHYSICAL_READS_TOTAL
, OPTIMIZED_PHYSICAL_READS_DELTA
, CELL_UNCOMPRESSED_BYTES_TOTAL
, CELL_UNCOMPRESSED_BYTES_DELTA
, IO_OFFLOAD_RETURN_BYTES_TOTAL
, IO_OFFLOAD_RETURN_BYTES_DELTA
, BIND_DATA
, SQL.FLAG
, DECODE(SQL.CON_DBID
, 0
, SQL.DBID
, SQL.CON_DBID)
, CON_DBID_TO_ID(DECODE(SQL.CON_DBID
, 0
, SQL.DBID
, SQL.CON_DBID)) CON_ID FROM DBA_HIST_SNAPSHOT SN
, WRH$_SQLSTAT SQL
WHERE SN.SNAP_ID = SQL.SNAP_ID
AND SN.DBID = SQL.DBID
AND SN.INSTANCE_NUMBER = SQL.INSTANCE_NUMBER