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
;

Access Path Suggestor and Generator -Mariadb

Earlier I have published accesspath suggestors for Oracle and Postgresql. Those are interpreting accesspath bidirectionally. Here is one for Mariadb. I quess it suits also for Mysql. I have not tried. This traverses the foreign key tree only in one direction. So the dependencies from a :roottable to :goaltablename in a :tableschema are reported. Also a query for the whole accesspath is generated.

with recursive fks as (
SELECT constraint_name,
       table_name,
       column_name,
       referenced_table_name,
       referenced_column_name
  FROM information_schema.key_column_usage
 WHERE table_schema = :tableschema 
   AND referenced_column_name IS NOT NULL
), pths as (
 select 1 lvl,
        cast(concat('/',table_name) as varchar(4000)) pth,
        constraint_name,
        table_name,
        column_name,
        referenced_table_name,
        referenced_column_name,
        cast(concat(referenced_table_name,', ',table_name,' ',constraint_name) as varchar(4000)) fro,
        cast(concat(referenced_table_name,'.',referenced_column_name,' = ',constraint_name,'.',column_name) as varchar(4000)) joi
   from fks
  where referenced_table_name = :roottablename
  union all
 select r.lvl+1,
        concat(r.pth,'/',c.table_name),
        c.constraint_name,
        c.table_name,
        c.column_name,
        c.referenced_table_name,
        c.referenced_column_name,
        concat(r.fro,', ',c.table_name,' ',c.constraint_name),
        concat(r.joi,' and ',r.constraint_name,'.',c.referenced_column_name,' = ',c.constraint_name,'.',c.column_name)
   from pths r, fks c
  where r.table_name  = c.referenced_table_name
) cycle table_name, referenced_table_name restrict
select concat('select * from ',fro,' where ',joi,';') sq
     , pths.pth
  from pths
 where table_name = :goaltablename
;