RAC Overhead

Procedure Call

RAC_Overhead(#DBID#, #ALERTVALUE#, #ALERTSTRING#)

Description

This alert calculates the cumulative amount of RAC wait time overhead from the last hour for each instance. For Oracle 10g the procedure will look for wait events names starting with "gc" and for other versions it will look for wait event names starting with "global cache".

PL/SQL code for RAC_Overhead procedure

Yes, the code is too small to read. It was shrunk to fit into this webpage design. Do a view page source and then copy the code to your favorite text editor.

create or replace procedure RAC_Overhead (
   p_db_id              in number,
   p_alert_value        out number,
   p_alert_string       out varchar2) IS
 
   sSQL                 varchar2(1000);
   sRac_Events          varchar(20);
   sOracle_Version      varchar2(20);
 
BEGIN
   -- get the Oracle version for this database
   sSQL := '';
   sSQL := sSQL || ' SELECT oracleversion';
   sSQL := sSQL || ' FROM cond WHERE id = :1';
   EXECUTE IMMEDIATE sSQL INTO sOracle_Version USING p_db_id;
 
   -- find out which events we should look for based on the Oracle version
   IF SUBSTR(sOracle_Version, 1, 2) = '10' THEN
      sRac_Events := 'gc %';
   ELSE
      sRac_Events := 'global cache %';
   END IF;
   DBMS_OUTPUT.PUT_LINE(sRac_Events);
 
   -- Calculate the percent of wait time attributed to RAC
   sSQL := '';
   sSQL := sSQL || ' SELECT ROUND((rac_wait_time / tot_wait_time)*100,2)';
   sSQL := sSQL || '    FROM (';
   sSQL := sSQL || '       SELECT SUM(DECODE(event_category, ''R'', wait_time)) rac_wait_time,';
   sSQL := sSQL || '              SUM(DECODE(event_category, ''A'', wait_time)) tot_wait_time';
   sSQL := sSQL || '       FROM (';
   sSQL := sSQL || '          SELECT ''R'' event_category, count(1) wait_time';
   sSQL := sSQL || '          FROM   consw_' || p_db_id || ' w, conev_' || p_db_id || ' e';
   sSQL := sSQL || '          WHERE  w.keeq = e.id';
   sSQL := sSQL || '          AND    e.name like ''' || sRac_Events || '''';
   sSQL := sSQL || '          AND    w.iedx > concap.convert_from_date(sysdate - 1/24)';
   sSQL := sSQL || '          UNION';
   sSQL := sSQL || '          SELECT ''A'' event_category, count(1) wait_time';
   sSQL := sSQL || '          FROM   consw_' || p_db_id || ' w, conev_' || p_db_id || ' e';
   sSQL := sSQL || '          WHERE  w.keeq = e.id';
   sSQL := sSQL || '          AND    w.iedx > concap.convert_from_date(sysdate - 1/24)';
   sSQL := sSQL || '       )';
   sSQL := sSQL || '    )';
   EXECUTE IMMEDIATE sSQL INTO p_alert_value;
END RAC_Overhead;
/

For more information about Writing Custom Alerts, see PG110.