CPU Usage by Schema by Day

Script: cpu_by_day.sql

DELETE FROM CPU_BY_SCHEMA_BY_DATE
WHERE AWR_DATE = ( SELECT MAX(AWR_DATE) FROM CPU_BY_SCHEMA_BY_DATE );
COMMIT;


insert into oradba.cpu_by_schema_by_date
SELECT  PARSING_SCHEMA_NAME,
  trunc(snap.begin_interval_time) t_date,
  sum(cpu_time_delta) tcpu,
  xx.icpu dcpu,
  (sum(cpu_time_delta) / xx.icpu) xpct
from dba_hist_sqlstat@ngp11 sql, dba_hist_snapshot@ngp11 snap,
  (
  select /*+ parallel(sql2, 2) parallel(snap2,2) */ suM(sql2.cpu_time_delta) icpu, trunc(snap2.begin_interval_time) bit
  from dba_hist_sqlstat@ngp11 sql2, dba_hist_snapshot@ngp11 snap2
--  where sql2.snap_id > 34548
  where sql2.snap_id > 40922
  and sql2.snap_id = snap2.snap_id
  and sql2.instance_number = snap2.instance_number
  AND trunc(snap2.begin_interval_time) > (SELECT MAX(AWR_DATE) FROM CPU_BY_SCHEMA_BY_DATE)
  group by
  trunc(snap2.begin_interval_time)
  ) xx
--where sql.snap_id >  0
where sql.snap_id > 40921
and sql.snap_id = snap.snap_id
--and (parsing_schema_name = 'NGPROD')
and sql.instance_number = snap.instance_number
AND trunc(snap.begin_interval_time) > (SELECT MAX(AWR_DATE) FROM CPU_BY_SCHEMA_BY_DATE)
and xx.bit = trunc(snap.begin_interval_time)
group by
PARSING_SCHEMA_NAME,
trunc(snap.begin_interval_time), xx.icpu
order by trunc(snap.begin_interval_time) , PARSING_SCHEMA_NAME
;
commit;
commit;
commit;
commit;

Purpose

This script calculates and stores daily CPU consumption by schema, tracking what percentage of total database CPU each schema consumes. It’s designed for trend analysis and capacity planning.

Key Features

Daily Aggregation: Summarizes CPU usage by day and schema
Percentage Calculation: Shows each schema’s share of total CPU
Incremental Loading: Only processes new data since last run
Historical Tracking: Maintains history for trend analysis

Prerequisites

Custom Table: CPU_BY_SCHEMA_BY_DATE must exist
Database Link: @ngp11 link must be configured
AWR Access: DBA_HIST views accessible via link
Table Structure:

CREATE TABLE CPU_BY_SCHEMA_BY_DATE (
    PARSING_SCHEMA_NAME VARCHAR2(30),
    AWR_DATE DATE,
    TCPU NUMBER,
    DCPU NUMBER,
    XPCT NUMBER
);

Process Flow

Cleanup: Deletes the most recent date (for reprocessing)
Calculate Total: Computes total daily CPU across all schemas
Schema Breakdown: Calculates CPU per schema
Percentage: Computes each schema’s percentage of total
Storage: Inserts results into tracking table

Output Columns

PARSING_SCHEMA_NAME: Schema that parsed the SQL
T_DATE: Date of measurement
TCPU: Total CPU time for schema (microseconds)
DCPU: Daily total CPU for database
XPCT: Percentage of total CPU used by schema

Configuration Notes

Snapshot Range

Hardcoded snap_id > 40921 (commented alternatives available)
Adjust based on your retention and requirements

Database Link

Currently uses @ngp11
Modify to match your environment

Parallel Hints

Uses parallel(2) for performance
Adjust based on system capacity

Usage

SQL> @cpu_by_day.sql

The script runs automatically without prompts.

Query Results

To view the collected data:

-- Top CPU consumers by day
SELECT awr_date, parsing_schema_name,
       ROUND(xpct * 100, 2) as cpu_percent
FROM cpu_by_schema_by_date
WHERE awr_date >= SYSDATE - 30
ORDER BY awr_date DESC, xpct DESC;

-- Schema CPU trend
SELECT parsing_schema_name,
       MIN(xpct * 100) min_pct,
       AVG(xpct * 100) avg_pct,
       MAX(xpct * 100) max_pct
FROM cpu_by_schema_by_date
WHERE awr_date >= SYSDATE - 30
GROUP BY parsing_schema_name
ORDER BY avg_pct DESC;

Maintenance Considerations

Table Growth: Implement retention policy
Statistics: Keep table statistics current
Indexes: Consider indexing AWR_DATE and PARSING_SCHEMA_NAME
Monitoring: Check for failed runs or gaps

The script can be modified to track:

CPU by module or action
CPU by SQL_ID
CPU by time of day
CPU by wait event correlation

io_by_day.SQL: Similar tracking for I/O metrics
SYSMETRIC_CPU.sql: Real-time CPU metrics
gvcpu.sql: Current CPU usage across RAC