Yuan Yao, An Oracle ACE's Blog

set linesize 200
set pages 200
set veri off
col 'Times (s)' format 999,999,999,999.99
col '% DB Time' format 999.99
col 'Statistic Name' format a44
DEFINE beg_snap = 10;
DEFINE end_snap = 11;

WITH cputime_and_dbtime AS (
  SELECT
    (SELECT SUM(e.VALUE - b.value) AS diff_value
       FROM dba_hist_sys_time_model b,
            dba_hist_sys_time_model e
      WHERE e.dbid = b.dbid
        AND e.instance_number = b.instance_number
        AND e.STAT_ID = b.STAT_ID
        AND b.dbid = (SELECT dbid FROM v$database)
        AND b.instance_number = (SELECT instance_number FROM v$instance)
        AND b.snap_id = &beg_snap
        AND e.snap_id = &end_snap
        AND e.stat_name = 'DB CPU') AS cputime,
    (SELECT SUM(e.VALUE - b.value) AS diff_value
       FROM dba_hist_sys_time_model b,
            dba_hist_sys_time_model e
      WHERE e.dbid = b.dbid
        AND e.instance_number = b.instance_number
        AND e.STAT_ID = b.STAT_ID
        AND b.dbid = (SELECT dbid FROM v$database)
        AND b.instance_number = (SELECT instance_number FROM v$instance)
        AND b.snap_id = &beg_snap
        AND e.snap_id = &end_snap
        AND e.stat_name = 'DB time') AS dbtime,
    (SELECT
        EXTRACT(DAY FROM e.end_interval_time - b.end_interval_time) * 86400
        + EXTRACT(HOUR FROM e.end_interval_time - b.end_interval_time) * 3600
        + EXTRACT(MINUTE FROM e.end_interval_time - b.end_interval_time) * 60
        + EXTRACT(SECOND FROM e.end_interval_time - b.end_interval_time) AS d_elp_time
    FROM dba_hist_snapshot b, dba_hist_snapshot e
    WHERE e.snap_id = &end_snap
      AND b.snap_id = &beg_snap
      AND b.dbid = (SELECT dbid FROM v$database)
      AND b.instance_number = (SELECT instance_number FROM v$instance)
      AND e.dbid = b.dbid
      AND e.instance_number = b.instance_number) AS elp_time
  FROM dual
),
model_value AS (
    SELECT e.stat_name stat_name,
           b.value prev_value,
           e.value total_value
      FROM dba_hist_sys_time_model b,
           dba_hist_sys_time_model e
     WHERE b.snap_id = &beg_snap
       AND e.dbid = (SELECT dbid FROM v$database)
       AND e.snap_id = &end_snap
       AND e.instance_number = (SELECT instance_number FROM v$instance)
       AND e.dbid = b.dbid
       AND e.instance_number = b.instance_number(+)
       AND (e.value - 1000) > NVL(b.value, 0)
       AND e.stat_id = b.stat_id
),
model_total AS (
    SELECT * FROM model_value
    UNION ALL
    SELECT 'total CPU time' AS stat_name,
           (SELECT SUM(prev_value) FROM model_value WHERE stat_name='DB CPU' OR stat_name ='background cpu time') AS prev_value,
           (SELECT SUM(total_value) FROM model_value WHERE stat_name='DB CPU' OR stat_name ='background cpu time') AS total_value
    FROM dual
),
value_diff AS (
    SELECT stat_name,
           total_value - NVL(prev_value, 0) diff_value
    FROM model_total
),
value_pct AS (
    SELECT stat_name,
           diff_value / 1000000 AS diff_second,
           DECODE((SELECT dbtime FROM cputime_and_dbtime), 0, NULL, diff_value / (SELECT dbtime FROM cputime_and_dbtime)) * 100 AS pct_db_time,
           CASE
              WHEN t2.stat_name LIKE 'DB time' THEN 2
              WHEN t2.stat_name LIKE 'background %' THEN 3
              WHEN t2.stat_name LIKE 'total CPU time' THEN 4
              ELSE 1
           END AS stat_rank
      FROM value_diff t2
)
SELECT stat_name AS "Statistic Name",
       ROUND(diff_second, 2) AS "Times (s)",
       DECODE(stat_rank, 1, ROUND(pct_db_time, 2), NULL) AS "% DB Time"
  FROM value_pct
 ORDER BY stat_rank, pct_db_time DESC;