Sqlstat History

Oracle Diagnosics pack purchased? AWR reports reveal many aspects of database usage and behavior. In addition to Enterprise Manager Cloud control pages the dba_hist_* views are available. Here is a script that visualizes most elapsed_time and shows 18 figures of sql clauses used and captured in dba_hist_sqlstat view.

select trim(rpad(' ',ceil(t.elapsed_time/t.mxe*len),'*'))||trim(rpad(' ',ceil((t.mxe-t.elapsed_time)/t.mxe*len),'-')) vis
     , t.*
  from (
select s.*
     , max(s.elapsed_time)over() mxe
     , 80 len
from (
select min(trunc(sn.begin_interval_time,'hh24')) bg
     , max(trunc(sn.end_interval_time,'hh24')) en
     , max(sn.snap_id) snap_id
     , ss.parsing_schema_name
     , ss.module
--     , ss.action
--     , ss.sql_id, (select sql_text from dba_hist_sqltext t where ss.sql_id = t.sql_id and rownum = 1) sql_text
     , sum(ss.elapsed_time_delta) elapsed_time
     , sum(ss.sharable_mem) sharable_mem
     , sum(ss.version_count) version
     , sum(ss.fetches_delta) fetches
     , sum(ss.end_of_fetch_count_delta) end_of_fetch_count
     , sum(ss.sorts_delta) sorts
     , sum(ss.executions_delta) executions
     , sum(ss.loads_delta) loads
     , sum(ss.parse_calls_delta) parse_calls
     , sum(ss.buffer_gets_delta) buffer_gets
     , sum(ss.rows_processed_delta) rows_processed
     , sum(ss.cpu_time_delta) cpu_time
     , sum(ss.iowait_delta) iowait
     , sum(ss.plsexec_time_delta) plsexec_time
     , sum(ss.physical_read_requests_delta) physical_read_requests
     , sum(ss.physical_read_bytes_delta) physical_read_bytes
     , sum(ss.physical_write_requests_delta) physical_write_requests
     , sum(ss.physical_write_bytes_delta) physical_write_bytes
  from dba_hist_sqlstat ss
     , dba_hist_snapshot sn 
 where ss.snap_id = sn.snap_id
   and ss.instance_number = sn.instance_number
   and ss.dbid = ss.dbid
   and sn.end_interval_time > trunc(sysdate)
 group by ss.parsing_schema_name
     , ss.module
--     , ss.action
--     , ss.sql_id
     , sn.snap_id
) s ) t
order by module,vis
;