Database trend analysis

Active Session

select to_char(end_interval_time,'mm-dd hh24') snap_time
, instance_number
, avg(v_ps) pSec
from (
select end_interval_time
, instance_number
, v/ela v_ps
from (
select round(s.end_interval_time,'hh24') end_interval_time
, s.instance_number
, (case when s.begin_interval_time = s.startup_time
then value
else value - lag(value,1) over (partition by sy.stat_id
, sy.dbid
, s.instance_number
, s.startup_time
order by sy.snap_id)
end)/1000000 v
, (cast(s.end_interval_time as date) - cast(s.begin_interval_time as date))*24*3600 ela
from dba_hist_snapshot s
, dba_hist_sys_time_model sy
where s.dbid = sy.dbid
and s.instance_number = sy.instance_number
and s.snap_id = sy.snap_id
and sy.stat_name = 'DB time'
and s.end_interval_time > to_date(&start_time,'MMDDYYYY')
and s.end_interval_time < to_date(&end_time,'MMDDYYYY') ))
group by to_char(end_interval_time,'mm-dd hh24'), instance_number
/

DB Growth

-- Get the datbase block size from dba_hist_datafile
WITH ts_info as (
select dbid, ts#, tsname, max(block_size) block_size
from dba_hist_datafile
group by dbid, ts#, tsname),
-- Get the maximum snaphsot id for each day from dba_hist_snapshot
snap_info as (
select dbid,to_char(trunc(end_interval_time,'DD'),'MM/DD/YY') dd, max(s.snap_id) snap_id
from dba_hist_snapshot s
where s.end_interval_time > to_date(&start_time,'MMDDYYYY')
and s.end_interval_time < to_date(&end_time,'MMDDYYYY')
group by dbid,trunc(end_interval_time,'DD'))
-- Sum up the sizes of all the tablespaces for the last snapshot of each day
select s.dd, s.dbid, sum(tablespace_size*f.block_size)
from dba_hist_tbspc_space_usage sp,
ts_info f,
snap_info s
where s.dbid = sp.dbid
and s.snap_id = sp.snap_id
and sp.dbid = f.dbid
and sp.tablespace_id = f.ts#
group by s.dd, s.dbid
order by s.dd
/

CPU Usage

select to_char(round(s.end_interval_time,'hh24'),'mm-dd hh24') snap_time
, os.instance_number
, os.value
from dba_hist_snapshot s
, dba_hist_osstat os
where s.dbid = os.dbid
and s.instance_number = os.instance_number
and s.snap_id = os.snap_id
and os.stat_name = 'LOAD'
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
and os.instance_number=1
order by to_char(trunc(s.end_interval_time,'hh24'),'mm-dd hh24'), os.instance_number
/

Active Session per wait class

select to_char(end_time,'mm-dd hh24') snap_time
, wait_class
, sum(pSec) avg_sess
from
(select end_time
, wait_class
, p_tmfg/1000000/ela pSec
from (
select round(s.end_interval_time,'hh24') end_time
, (cast(s.end_interval_time as date) - cast(s.begin_interval_time as date))*24*3600 ela
, s.snap_id
, wait_class
, e.event_name
, case when s.begin_interval_time = s.startup_time
then e.time_waited_micro_fg
else e.time_waited_micro_fg
- lag(time_waited_micro_fg) over (partition by event_id
, e.dbid
, e.instance_number
, s.startup_time
order by e.snap_id)
end p_tmfg
from dba_hist_snapshot s
, dba_hist_system_event e
where s.dbid = e.dbid
and s.instance_number = e.instance_number
and s.snap_id = e.snap_id
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
and e.wait_class != 'Idle'
union all
select trunc(s.end_interval_time,'hh24') end_time
, (cast(s.end_interval_time as date) - cast(s.begin_interval_time as date))*24*3600 ela
, s.snap_id
, t.stat_name wait_class
, t.stat_name event_name
, case when s.begin_interval_time = s.startup_time
then t.value
else t.value
- lag(value) over (partition by stat_id
, t.dbid
, t.instance_number
, s.startup_time
order by t.snap_id)
end p_tmfg
from dba_hist_snapshot s
, dba_hist_sys_time_model t
where s.dbid = t.dbid
and s.instance_number = t.instance_number
and s.snap_id = t.snap_id
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
and t.stat_name = 'DB CPU'))
group by to_char(end_time,'mm-dd hh24'), wait_class
order by to_char(end_time,'mm-dd hh24'), wait_class

/

Buffer Cache

select to_char(round(s.end_interval_time,'hh24'),'mm-dd hh24') snap_time
, g.instance_number
, g.bytes/1044/1024 mbytes
from dba_hist_snapshot s
, dba_hist_sgastat g
where s.snap_id = g.snap_id
and s.instance_number = g.instance_number
and s.dbid = g.dbid
and g.name = 'buffer_cache'
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
order by to_char(s.end_interval_time,'mm-dd hh24:mi')
, g.instance_number

/

IO stat file type 

select to_char(round(end_interval_time,'hh24'),'mm-dd hh24') snap_time
, instance_number
, sum(megabytes) / 1024 Gigabytes
from
(
select end_interval_time
, instance_number
, megabytes
from
(
select s.snap_id
, s.instance_number
, s.dbid
, s.end_interval_time
, case when s.begin_interval_time = s.startup_time
then nvl(ft.small_read_megabytes+large_read_megabytes,0)
else nvl(ft.small_read_megabytes+large_read_megabytes,0) -
lag(nvl(ft.small_read_megabytes+large_read_megabytes,0),1)
over (partition by ft.filetype_id
, ft.instance_number
, ft.dbid
, s.startup_time
order by ft.snap_id)
end megabytes
from dba_hist_snapshot s
, dba_hist_iostat_filetype ft
, dba_hist_iostat_filetype_name fn
where s.dbid = ft.dbid
and s.instance_number = ft.instance_number
and s.snap_id = ft.snap_id
and s.dbid = fn.dbid
and ft.filetype_id = fn.filetype_id
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
and fn.filetype_name = 'Data File'
)
)
group by to_char(round(end_interval_time,'hh24'),'mm-dd hh24'), instance_number
order by to_char(round(end_interval_time,'hh24'),'mm-dd hh24'), instance_number
/

Logical reads sysstat

select to_char(round(end_interval_time,'hh24'),'mm-dd hh24') snap_time
, instance_number
, avg(pSec) perSec
from ( select end_interval_time
, instance_number
, greatest(v/ela,0) pSec
from (
select /*+ leading(s,sn,sy) */ s.snap_id
, s.instance_number
, s.dbid
, s.end_interval_time
, case when s.begin_interval_time = s.startup_time
then sy.value
else sy.value - lag(sy.value,1) over (partition by sy.stat_id
, sy.instance_number
, sy.dbid
, s.startup_time
order by sy.snap_id)
end v
, (cast(end_interval_time as date) - cast(begin_interval_time as date))*24*3600 ela
from dba_hist_snapshot s
, dba_hist_sysstat sy
, dba_hist_stat_name sn
where s.dbid = sy.dbid
and s.instance_number = sy.instance_number
and s.snap_id = sy.snap_id
and s.dbid = sn.dbid
and sy.stat_id = sn.stat_id
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
and sn.stat_name = 'session logical reads'
)
)
group by to_char(round(end_interval_time,'hh24'),'mm-dd hh24'), instance_number
order by to_char(round(end_interval_time,'hh24'),'mm-dd hh24'), instance_number
/

PGA usage

select to_char(round(s.end_interval_time,'hh24'),'mm-dd hh24') snap_time
, g.instance_number
, g.value/1048576 mbytes
from dba_hist_snapshot s
, dba_hist_pgastat g
where s.snap_id = g.snap_id
and s.instance_number = g.instance_number
and s.dbid = g.dbid
and g.name = 'total PGA allocated'
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
order by to_char(s.end_interval_time,'mm-dd hh24:mi')
, g.instance_number
/


SQL Elapsed

select to_char(round(end_interval_time,'hh24'),'mm-dd hh24') snap_time
, sql_id
, sum(elapsed_time_delta/1000000)/decode(sum(executions_delta),0,null,sum(executions_delta))
avg_elapsed
from dba_hist_sqlstat sq
, dba_hist_snapshot s
where sq.sql_id (+) = &sql_id
and sq.dbid (+) = s.dbid
and sq.instance_number (+) = s.instance_number
and sq.snap_id (+) = s.snap_id
and s.end_interval_time > to_date(&start_date,'MMDDYYYY')
and s.end_interval_time < to_date(&end_date,'MMDDYYYY')
group by to_char(round(end_interval_time,'hh24'),'mm-dd hh24'), sql_id
order by to_char(round(end_interval_time,'hh24'),'mm-dd hh24')
/