Monitor Database Processes and Sessions - Code Example

Log Database Processes - Code Example

A job to log processes in database to a table every 10 minutes



create table ADMIN_HIST_OPEN_CURSORS
(
  run_date             DATE,
  db_schema            VARCHAR2(30),
  machine              VARCHAR2(30),
  open_cursors_current NUMBER,
  open_cursors_max     NUMBER,
  open_cursors_total   NUMBER,
  rank                 NUMBER
)
tablespace SH_DBA_TBS_TB_01;

create table ADMIN_HIST_PROCESSES
(
  run_date  DATE,
  db_schema VARCHAR2(30),
  machine   VARCHAR2(30),
  os_user   VARCHAR2(30),
  processes NUMBER,
  rank      NUMBER
)
tablespace SH_DBA_TBS_TB_01;


--==============================================================
-- Manual Steps
--==============================================================
----------------------------------
-- As sysdba:
----------------------------------
-- CREATE TABLESPACE SH_DBA_TBS_TB_01 DATAFILE '/oracle_db/db1/db_igt/SH_DBA_TBS_TB_01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
-- CREATE TABLESPACE SH_DBA_TBS_IX_01 DATAFILE '/oracle_db/db1/db_igt/SH_DBA_TBS_IX_01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
-- CREATE USER SH_DBA IDENTIFIED BY SH_PASS DEFAULT TABLESPACE SH_DBA_TBS_TB_01;
-- GRANT CONNECT, RESOURCE TO SH_DBA;
-- GRANT SELECT ANY DICTIONARY TO SH_DBA;
-- GRANT UNLIMITED TABLESPACE TO SH_DBA;
-- GRANT CREATE PUBLIC SYNONYM TO SH_DBA;
-- GRANT DROP PUBLIC SYNONYM TO SH_DBA;
-- GRANT DEBUG CONNECT SESSION TO SH_DBA;
-- GRANT CREATE PROCEDURE, EXECUTE ANY PROCEDURE TO SH_DBA;
-- GRANT CREATE SNAPSHOT, CREATE TRIGGER, CREATE ANY INDEX TO SH_DBA;
-- GRANT CREATE SYNONYM TO SH_DBA;
-- GRANT UNLIMITED TABLESPACE TO SH_DBA;
-- GRANT QUERY REWRITE TO SH_DBA;
-- GRANT CREATE ANY DIRECTORY TO SH_DBA;
-- GRANT SELECT_CATALOG_ROLE TO SH_DBA;

-- GRANT DBA TO SH_DBA;
-- as system
-- GRANT SELECT ON SYS.V_$PROCESS TO MY_USER;
-- GRANT SELECT ON SYS.V_$SESSION TO MY_USER;

------------------------
-- Job
------------------------
DECLARE
   v_job_number NUMBER(10);
BEGIN
 DBMS_JOB.SUBMIT (JOB => v_job_number, 
                  WHAT => 'ADMIN_UTIL.monitor_oracle;', 
                  NEXT_DATE => TRUNC(SYSDATE,'mi') +10/1440, 
                  INTERVAL => 'TRUNC(SYSDATE,''mi'') +10/1440'
 );
 COMMIT;
END;
/

------------------------------------------------
CREATE OR REPLACE PACKAGE ADMIN_UTIL AS       
  PROCEDURE monitor_oracle;
END ADMIN_UTIL;
/

------------------------------------------------
CREATE OR REPLACE PACKAGE BODY MONITOR_UTIL AS
--==============================================================
-- Manual Steps
--==============================================================
-- CREATE TABLE ADMIN_HIST_PROCESSES
--      (run_date DATE,
--       db_schema VARCHAR2(30),
--       machine   VARCHAR2(30),
--       os_user   VARCHAR2(30),
--       processes  NUMBER,
--       rank       NUMBER) TABLESPACE IGT_TABLE;
--
-- As sysdba:
-- GRANT SELECT ON SYS.V_$PROCESS TO COLLECTOR;
-- GRANT SELECT ON SYS.V_$SESSION TO COLLECTOR;
--
-- Job
--DECLARE
--   v_job_number NUMBER(10);
--BEGIN
-- DBMS_JOB.SUBMIT (JOB => v_job_number,
--                  WHAT => 'ADMIN_UTIL.log_sessions;',
--                  NEXT_DATE => TRUNC(SYSDATE,'mi') +10/1440,
--                  INTERVAL => 'TRUNC(SYSDATE,''mi'') +10/1440'
-- );
-- COMMIT;
--END;
--/

  PROCEDURE log_open_cursors IS
  BEGIN  
      INSERT INTO ADMIN_HIST_OPEN_CURSORS (run_date, db_schema, machine, open_cursors_current, open_cursors_max, open_cursors_total, rank)
            SELECT TRUNC(SYSDATE,'mi') as run_date,
                   username AS db_schema ,
                   machine,
                   avg_cur AS open_cursors_current,
                   max_cur AS open_cursors_max,
                   total_cur AS open_cursors_total, 
                   rownum                   
            FROM   (
              SELECT SUM(a.value) total_cur, 
                     ROUND(avg(a.value)) avg_cur, 
                     MAX(a.value) max_cur, 
                     s.username   username,  
                     s.machine machine
               FROM V$SESSTAT A, 
                    V$STATNAME B, 
                    V$SESSION S 
              WHERE a.statistic# = b.statistic#  
                AND S.sid=a.sid
                AND b.name = 'opened cursors current' 
                AND S.username IS NOT NULL                
              GROUP BY s.username, s.machine
             HAVING SUM(a.value) > 99
              ORDER BY 1 desc
              );


  EXCEPTION

    WHEN OTHERS THEN
      NULL;
  END log_open_cursors;
------------------------------------------------------------------------
  PROCEDURE log_sessions IS
  BEGIN

    BEGIN
      INSERT INTO ADMIN_HIST_PROCESSES (run_date, db_schema, machine, os_user, processes, rank)
      SELECT run_date,
             db_schema ,
             machine,
             osuser,
             processes,
             rownum
      FROM(
            SELECT TRUNC(SYSDATE,'mi') as run_date,
                   schemaname AS db_schema ,
                   machine,
                   osuser,
                   COUNT(*) AS processes
            FROM   (
              SELECT  PROCESSES.*,
                      SESSIONS.*
                 FROM V$PROCESS PROCESSES,
                      V$SESSION SESSIONS
                WHERE PROCESSES.background IS NULL
                  AND PROCESSES.addr = SESSIONS.paddr
               )
            GROUP BY schemaname,machine, osuser
            HAVING COUNT(*) > 1
            ORDER BY COUNT(*) DESC
      );

      INSERT INTO ADMIN_HIST_PROCESSES (run_date, db_schema, machine, os_user, processes, rank)
      SELECT run_date,
             db_schema ,
             machine,
             osuser,
             processes,
             0
      FROM(
            SELECT TRUNC(SYSDATE,'mi') as run_date,
                   'Total' AS db_schema ,
                   NULL as machine,
                   NULL as osuser,
                   COUNT(*) AS processes
            FROM   (
              SELECT  PROCESSES.*,
                      SESSIONS.*
                 FROM V$PROCESS PROCESSES,
                      V$SESSION SESSIONS
                WHERE PROCESSES.background IS NULL
                  AND PROCESSES.addr = SESSIONS.paddr
               )
      );

      COMMIT;


     EXCEPTION
      WHEN OTHERS THEN
        NULL;
     END;

     BEGIN
       IF  TO_NUMBER(to_char(SYSDATE,'hh24')) = 14 THEN
         DELETE FROM ADMIN_HIST_PROCESSES WHERE run_date < SYSDATE - 200;
         COMMIT;
       END IF;
     EXCEPTION
      WHEN OTHERS THEN
        NULL;
     END;


  EXCEPTION

    WHEN OTHERS THEN
      NULL;
  END log_sessions;
------------------------------------------------------------------------
  PROCEDURE monitor_oracle IS  
  BEGIN  
    log_sessions;
    log_open_cursors;
  EXCEPTION  
    WHEN OTHERS THEN
      NULL;
  END monitor_oracle;  
------------------------------------------------------------------------

END MONITOR_UTIL;------------------------------------------------

--==============================================================  

CREATE OR REPLACE VIEW ADMIN_HIST_PROCESSES_SUM_VW AS 
SELECT * FROM 
(
SELECT 'SPARX' as service_name, run_date, SUM(processes) as processes FROM ADMIN_HIST_PROCESSES WHERE 1=1 and DB_SCHEMA like '%SPARX%' GROUP BY run_date 
UNION ALL
SELECT 'IPN' as service_name, run_date, SUM(processes) as processes FROM ADMIN_HIST_PROCESSES WHERE 1=1 and DB_SCHEMA like '%IP%' GROUP BY run_date 
UNION ALL
SELECT 'ORACLE and OGG' as service_name, run_date, SUM(processes)as processes  FROM ADMIN_HIST_PROCESSES WHERE 1=1 and DB_SCHEMA IN ('SYS','OGG') GROUP BY run_date
)
order by run_date desc , service_name asc;


------------------------------
-- Log Sessions and Processes
------------------------------
-- Create tables
CREATE TABLE ADMIN_HIST_SESSIONS_NUMBER
(
  run_date             DATE,
  all_sessions         NUMBER,
  app_sessions         NUMBER,
  all_processes        NUMBER,
  app_processes        NUMBER
)
TABLESPACE IGT_TABLE;

GRANT SELECT ON SYS.V_$PROCESS TO SH_DBA;
GRANT SELECT ON SYS.V_$SESSION TO SH_DBA;

CREATE OR REPLACE PACKAGE MONITOR_UTIL AS
  PROCEDURE log_open_sessions;
END MONITOR_UTIL;
/

CREATE OR REPLACE PACKAGE BODY MONITOR_UTIL AS

PROCEDURE log_open_sessions IS
BEGIN
    INSERT INTO ADMIN_HIST_SESSIONS_NUMBER (run_date, all_sessions, app_sessions, all_processes, app_processes)
    SELECT SYSDATE,
         (SELECT COUNT(*) FROM V$SESSION) as all_sessions,
         (SELECT COUNT(*) FROM V$SESSION WHERE user# <> 0) as app_sessions,
         (SELECT COUNT(*) FROM V$PROCESS) as all_processes,
         (SELECT COUNT(*) FROM V$PROCESS WHERE addr IN (SELECT paddr FROM V$SESSION WHERE user# <> 0)) as app_processes
    FROM DUAL;
    commit;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END log_open_sessions;

END MONITOR_UTIL;
/

BEGIN
  MONITOR_UTIL.log_open_sessions;
END;
/




DECLARE
  v_job_number NUMBER(10);
  v_job_exists NUMBER(10);
BEGIN
 SELECT COUNT(*) INTO v_job_exists FROM USER_JOBS WHERE UPPER(what) LIKE 'MONITOR_UTIL.LOG_OPEN_SESSIONS%';
 IF v_job_exists = 0 THEN
   DBMS_JOB.SUBMIT (JOB => v_job_number, 
                    WHAT => 'MONITOR_UTIL.log_open_sessions;', 
                    NEXT_DATE => TRUNC(SYSDATE,'HH24')+ 1/24,
                    INTERVAL => 'TRUNC(SYSDATE,''HH24'')+ 1/24'
                    );
   COMMIT;
 END IF;
END;
/