Gvcpu (gvcpu.sql)

What This Script Does

This script provides real-time CPU utilization metrics at both session and system levels across all RAC instances. Essential for identifying CPU bottlenecks and monitoring database CPU consumption.

The Script

set linesize 200
set pagesize 200
col inst_id format 99 heading 'RAC|INST'
col sample_time format 999 heading 'Sample|Time|(S)'
col metric_name format a25
col value format 999.99

clear break

select inst_id
      ,round(sum(100 * cpu / intsize_csec), 2)  as "TOTAL %CPU"
  from gv$sessmetric
  group by inst_id
;

break on report on inst_id skip 1

select
      INST_ID
      ,to_char(BEGIN_TIME,'YYYY-MM-DD HH24:MI:SS') BEGIN_DT
      ,to_char(END_TIME,'YYYY-MM-DD HH24:MI:SS') END_DT
      ,INTSIZE_CSEC/100 Sample_Time
--      ,GROUP_ID
--      ,METRIC_ID
      ,decode(metric_name,'Database CPU Time Ratio',100-round(VALUE,2),round(VALUE,2)) VALUE
      ,METRIC_NAME
      ,METRIC_UNIT
 from gv$SYSMETRIC s
where upper(metric_name) like '%CPU%'
order by inst_id
       , metric_name
       , end_dt desc
;

--  and nvl(s.inst_id, 0) like nvl('&inst_id', '%')

--select * from gv$sessmetric m
--select * from gv$sysmetric_history sh where metric_name like '%CPU%';
--select * from v$sysmetric_summary  where metric_name like '%CPU%';
--select * from dba_hist_sysmetric_summary where metric_name like 'Host CPU Utilization (%)%' or metric_name like 'Database CPU Time Ratio%';

Parameters

The script prompts for:

None - this script requires no parameters## Usage

-- Basic usage
@gvcpu.sql

Sample Output

RAC
INST TOTAL %CPU
---- ----------
   1      45.67
   2      62.34

RAC
INST BEGIN_DT            END_DT              Sample    VALUE METRIC_NAME               METRIC_UNIT
---- ------------------- ------------------- ------- ------- ------------------------- -----------
   1 2025-01-05 14:30:00 2025-01-05 14:31:00      60   15.25 CPU Usage Per Sec         CentiSeconds Per Second
     2025-01-05 14:30:00 2025-01-05 14:31:00      60   12.50 CPU Usage Per Txn         CentiSeconds Per Txn
     2025-01-05 14:29:00 2025-01-05 14:30:00      60   18.75 CPU Usage Per Sec         CentiSeconds Per Second
     2025-01-05 14:30:00 2025-01-05 14:31:00      60   25.00 Database CPU Time Ratio   % Cpu/DB_Time
     2025-01-05 14:30:00 2025-01-05 14:31:00      60   42.30 Host CPU Utilization (%)  % Busy/(Idle+Busy)

   2 2025-01-05 14:30:00 2025-01-05 14:31:00      60   22.50 CPU Usage Per Sec         CentiSeconds Per Second
     2025-01-05 14:30:00 2025-01-05 14:31:00      60   18.25 CPU Usage Per Txn         CentiSeconds Per Txn
     2025-01-05 14:29:00 2025-01-05 14:30:00      60   24.00 CPU Usage Per Sec         CentiSeconds Per Second
     2025-01-05 14:30:00 2025-01-05 14:31:00      60   35.00 Database CPU Time Ratio   % Cpu/DB_Time
     2025-01-05 14:30:00 2025-01-05 14:31:00      60   58.75 Host CPU Utilization (%)  % Busy/(Idle+Busy)