-- cputime-dbtime-usercalls-gets-awr-daytime.sql
-- Dave Abercrombie, david.abercrombie@tapjoy.com, aberdave@gmail.com
-- March 4 2001
-- see http://aberdave.blogspot.com
--
-- Get CPU resource consumption, wait time, and workload metrics "buffer gets" and "user calls"
-- from the AWR version of v$sysstat. These data can be used for response time modeling.
-- This query assumes hourly AWR snapshots, and these metrics are thus hourly averages.
--
-- Written using "subquery factoring" and the analytic function lead() to simplify 
-- maintenance. Many of the subqueries can be run and debugged alone.
--
-- Subquery #1, denorm_awr_sysstat, denormalizes four v$sysstat metrics using the max-decode trick
-- Subquery #2, awr_sysstat_deltas, uses lead() to calculate deltas
-- Subquery #3, daytime_weekday_snaps, identifies AWR snaps for daytime weekday hours
-- Subquery #4, cpu_resources, finds the total CPU seconds per hour available
-- The main query does one join and the (ugly, cumbersome) unit conversion 
--
-- Bugs and limitations:
-- 
-- 1) Assumes hourly AWR snapshots. If this is false, you could calculate snapshot
--    durations and use them for rate calculations.
-- 
-- 2) Uses v$sysstat even though v$sys_time_model has better CPU metrics. But it is
--    very convenient to get buffer gets (etc.) along with CPU data in one query
--
-- 3) The units of CPU and wait time are bizarre: centiseconds per hour. For response
--    time modelling, I prefer everything to be in milliseconds, but centiseconds and
--    hours are closer to the Oracle's native values in dba_hist_sysstat, and thus
--    perhaps easier to maintain. For example, look at the cumbersome calculations
--    used for CPU utilization below - ugly, ugly, ugly!
-- 
column BEGIN_HOUR format a16
--
with denorm_awr_sysstat as (
   select
      snap_id,
      max(decode( stat_name, 'CPU used by this session',  value, NULL)) as DB_CPU,
      max(decode( stat_name, 'DB time',                   value, NULL)) as DB_time,
      max(decode( stat_name, 'user calls',                value, NULL)) as User_Calls,
      max(decode( stat_name, 'consistent gets',           value, NULL)) as Buffer_Gets
   from
      dba_hist_sysstat
   where
      stat_name in ('CPU used by this session','DB time','user calls','consistent gets')
   group by
      snap_id
),
awr_sysstat_deltas as (
  select
      snap_id,
      lead(DB_CPU,1) over(order by snap_id)      - DB_CPU      as delta_DB_CPU,
      lead(DB_time,1) over(order by snap_id)     - DB_time     as delta_DB_time,
      lead(User_Calls,1) over(order by snap_id)  - User_Calls  as delta_User_Calls,
      lead(Buffer_Gets,1) over(order by snap_id) - Buffer_Gets as delta_Buffer_Gets
   from
      denorm_awr_sysstat
),
daytime_weekday_snaps as (
   select
      snap_id,
      begin_interval_time,
      end_interval_time
   from
      dba_hist_snapshot
   where
      -- Limit to weekday business hours
      --
      -- start with simple arithmetic in timestamp to convert to date
      -- then subtract truncated version from float version to calculate fraction of a day
      -- then limit it to hours between 8am and 6pm (will get hour that starts just after 5pm)
      --
      -- "You can use NUMBER constants in arithmetic operations on
      -- date and timestamp values, but not interval values. Oracle
      -- internally converts timestamp values to date values and
      -- interprets NUMBER constants in arithmetic datetime and
      -- interval expressions as numbers of days."
      -- http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#SQLRF00207
      --
      (BEGIN_INTERVAL_TIME +0) - trunc(BEGIN_INTERVAL_TIME +0) between (8/24) and (18/24)
   and
      -- avoid weekends
      -- http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924
      --
      trim(to_char(BEGIN_INTERVAL_TIME,'Day')) not in ('Saturday','Sunday')
),
cpu_resources as (
   -- multiply the number of CPUs by 3600 to get the
   -- total CPU seconds per hour (_SPH) that are avialable.
   select snap_id, to_number(value) * 3600 as avialable_cpu_sph 
   from dba_hist_parameter
   where parameter_name = 'cpu_count'
)
select
   daytime_weekday_snaps.snap_id,
   to_char(daytime_weekday_snaps.begin_interval_time,'YYYY-MM-DD HH24:MI') as begin_hour,
   --
   -- The unit of time used for CPU time and "DB Time" in v$syssyat and its AWR version 
   -- is "centiseconds" (i.e., 1/100 of a second). Since I assume all AWR snap intervals
   -- are one hour long, these deltas are in the bizarre units of centiseconds per hour,
   -- indicated with a suffix of "_CSPH".
   awr_sysstat_deltas.delta_DB_CPU  as db_cpu_csph,
   awr_sysstat_deltas.delta_DB_time as db_time_csph,
   --
   -- To calculate average CPU utilization over the snapshot:
   -- divide "CPU centiseconds per hour" by 100 to get CPU seconds consumed per hour,
   -- divide by the available seconds per hour to get utilization
   -- multiply by 100 to get percent utilization
   -- then finally round to an integer
   round((((awr_sysstat_deltas.delta_DB_CPU)/100)/cpu_resources.avialable_cpu_sph)*100) as pcnt_cpu_utilize,
   -- 
   -- calulate wait time in centiseconds per hour
   -- by subtracting "DB CPU" from "DB time"
   awr_sysstat_deltas.delta_DB_time - awr_sysstat_deltas.delta_DB_CPU as wait_csph,
   --
   -- pass along the buffer gets and user calls per hour,
   -- no fancy unit conversion needed
   awr_sysstat_deltas.delta_User_Calls as User_Calls_ph,
   awr_sysstat_deltas.delta_Buffer_Gets as Buffer_Gets_ph
from
   daytime_weekday_snaps,
   awr_sysstat_deltas,
   cpu_resources
where
   awr_sysstat_deltas.snap_id = daytime_weekday_snaps.snap_id
and
   awr_sysstat_deltas.snap_id = cpu_resources.snap_id
order by
   daytime_weekday_snaps.snap_id
;