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;
/
