DBA_HIST_SQLSTAT


http://docs.oracle.com/cd/E14072_01/server.112/e10820/statviews_4049.htm

 SNAP_ID

 DBID
 INSTANCE_NUMBER
 SQL_ID
 PLAN_HASH_VALUE
 OPTIMIZER_COST
 OPTIMIZER_MODE
 OPTIMIZER_ENV_HASH_VALUE
 SHARABLE_MEM
 LOADED_VERSIONS
 VERSION_COUNT
 MODULE
 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                                   SORTS
 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                     BLOCK READS (?)
 BUFFER_GETS_TOTAL              BUFFER_GETS_DELTA                   LIO
 ROWS_PROCESSED_TOTAL           ROWS_PROCESSED_DELTA
 CPU_TIME_TOTAL                 CPU_TIME_DELTA                          - micro seconds
 ELAPSED_TIME_TOTAL             ELAPSED_TIME_DELTA                - micro seconds
 IOWAIT_TOTAL                   IOWAIT_DELTA                             - us ?
 CLWAIT_TOTAL                   CLWAIT_DELTA                             - us ?
 APWAIT_TOTAL                   APWAIT_DELTA                             - us ?
 CCWAIT_TOTAL                   CCWAIT_DELTA                              - us ?
 DIRECT_WRITES_TOTAL            DIRECT_WRITES_DELTA                DIRECT WRITES
 PLSEXEC_TIME_TOTAL             PLSEXEC_TIME_DELTA                  - us ?
 JAVEXEC_TIME_TOTAL             JAVEXEC_TIME_DELTA                  - us ?
 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      IOPS READ
 PHYSICAL_READ_BYTES_TOTAL      PHYSICAL_READ_BYTES_DELTA
 PHYSICAL_WRITE_REQUESTS_TOTAL  PHYSICAL_WRITE_REQUESTS_DELTA    IOPS  WRITE
 PHYSICAL_WRITE_BYTES_TOTAL     PHYSICAL_WRITE_BYTES_DELTA
 OPTIMIZED_PHYSICAL_READS_TOTAL OPTIMIZED_PHYSICAL_READS_DELTA


Stats for 1 SQL_ID for one DBID

col avg_elapsed for 9,999,999.999
select
 to_char(snap.begin_interval_time,'DD-MON-YYYY HH24:MI') ,
 (ELAPSED_TIME_DELTA/EXECUTIONS_DELTA)/1000  avg_elapsed,
 EXECUTIONS_DELTA  execs,
 DISK_READS_DELTA  disk,
 BUFFER_GETS_DELTA lio,
 ROWS_PROCESSED_DELTA rws,
 CPU_TIME_DELTA       cpu,
 ELAPSED_TIME_DELTA   elapse,
 IOWAIT_DELTA         io_time,
 APWAIT_DELTA         ap_time,
 CCWAIT_DELTA         cc_time,
 DIRECT_WRITES_DELTA  dio,
 PHYSICAL_READ_REQUESTS_DELTA reads,
 PHYSICAL_WRITE_REQUESTS_DELTA writes
from dba_hist_sqlstat  sql,
     dba_hist_snapshot snap
where sql.sql_id='&sql_id'
and sql.dbid=&dbid
and sql.dbid=snap.dbid
and sql.snap_id=snap.snap_id
order by sql.snap_id
/


group by hour

select
 to_char(sn.begin_interval_time,'DD HH24'),
 sum(sql.EXECUTIONS_DELTA)  execs,
 sum(sql.DISK_READS_DELTA)  disk,
 sum(sql.BUFFER_GETS_DELTA) lio,
 sum(sql.ROWS_PROCESSED_DELTA) rws,
 sum(sql.CPU_TIME_DELTA)       cpu,
 sum(sql.ELAPSED_TIME_DELTA)   elapse,
 sum(sql.IOWAIT_DELTA)         io_time,
 sum(sql.APWAIT_DELTA)         ap_time,
 sum(sql.CCWAIT_DELTA)         cc_time,
 sum(sql.DIRECT_WRITES_DELTA)  dio,
 sum(sql.PHYSICAL_READ_REQUESTS_DELTA) reads,
 sum(sql.PHYSICAL_WRITE_REQUESTS_DELTA) writes
from dba_hist_sqlstat  sql,
     dba_hist_snapshot sn
where sql.sql_id='&sql_id'
  and sql.dbid=&dbid
  and sql.dbid=sn.dbid
  and sql.snap_id=sn.snap_id
group by to_char(sn.begin_interval_time,'DD HH24')
order by to_char(sn.begin_interval_time,'DD HH24')
/


average execution time per hour

col avg_elapsed for 9,999,999.999
select
 to_char(snap.begin_interval_time,'DD-MON-YYYY HH24') ,
 --sum(ELAPSED_TIME_DELTA) elapse,
 --sum(EXECUTIONS_DELTA)  execs,
 --count(*),
 round(avg(ELAPSED_TIME_DELTA/EXECUTIONS_DELTA)/1000000,3)  avg_elapsed
from dba_hist_sqlstat sql,
     dba_hist_snapshot snap
where sql.sql_id='&sql_id'
and sql.dbid=&dbid
and sql.dbid=snap.dbid
and snap.snap_id=sql.snap_id
group by to_char(snap.begin_interval_time,'DD-MON-YYYY HH24')
order by 1
/


R2

column r2 format 999
column delta_executions_seconds format 999,999,999
select sum(R2*delta_elapsed)/sum(delta_elapsed) from
(
SELECT
  dbid,
  parsing_schema_name,
  snap_id,
  SUM(executions_delta) AS delta_executions,
  SUM(elapsed_time_delta) / 1000000 AS delta_execution_seconds,
  100 * REGR_R2(optimizer_cost * executions_delta, elapsed_time_delta) AS R2,
  SUM(elapsed_time_delta) AS delta_elapsed
FROM dba_hist_sqlstat
WHERE parsing_schema_name != 'SYS' and parsing_schema_name != 'SYSADM'
AND optimizer_cost  > 0
AND executions_delta > 0
AND elapsed_time_delta > 0
GROUP BY dbid, snap_id, parsing_schema_name
HAVING SUM(elapsed_time_delta) / 1000000 > 3600
ORDER BY dbid, snap_id
)
where r2 > 3
;




set linesize 100

column query_count format 999,999,990
column total_executions format 999,999,990
column total_execution_seconds format 999,999,990
column R2 format 99.90

SELECT
  parsing_schema_name AS schema,
  COUNT(*) AS query_count,
  SUM(executions) AS total_executions,
  SUM(elapsed_time) / 1000000 AS total_execution_seconds,
  100 * REGR_R2(optimizer_cost, elapsed_time/executions) AS R2 -- use the average elapsed time as the response variable
FROM v$sql
WHERE parsing_schema_name != 'SYS'
AND command_type = 3 -- SELECT statements only
AND optimizer_cost > 0
AND executions > 0
AND elapsed_time > 0
GROUP BY parsing_schema_name
HAVING COUNT(*) > 100 AND SUM(elapsed_time) / 1000000 > 3600 -- ensure a sufficient sample size
ORDER BY 5;

Comments