1、创建配置定时任务
–创建表统计数据库会话数
CREATE TABLE “SYSDBA”.“SESSION_STATS_HISTORY”
(
“INSTANCE_NAME” VARCHAR2(128),
“STATE” VARCHAR2(30),
“COUNT_VALUE” NUMBER,
“COLLECT_TIME” TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP) STORAGE(ON “MAIN”, CLUSTERBTR) ;

–创建存储过程统计数据库会话数
CREATE OR REPLACE PROCEDURE INSERT_SESSION_STATS AS
BEGIN
INSERT INTO SESSION_STATS_HISTORY (INSTANCE_NAME, STATE, COUNT_VALUE)
SELECT
INSTANCE_NAME,
STATE,
COUNT(*)
FROM SYS.GV$SESSIONS
GROUP BY INSTANCE_NAME, STATE;

COMMIT;

DBMS_OUTPUT.PUT_LINE('Session stats inserted at: ’ || TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’));
END;

—配置作业定时统计数据库主备库会话数
call SP_CREATE_JOB(‘SESSIONSTATIC’,1,0,’’,0,0,’’,0,‘统计sql会话’);

call SP_JOB_CONFIG_START(‘SESSIONSTATIC’);

call SP_JOB_SET_EP_SEQNO(‘SESSIONSTATIC’, 0);

call SP_ADD_JOB_STEP(‘SESSIONSTATIC’, ‘S1’, 0, ‘EXEC INSERT_SESSION_STATS;’, 0, 0, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE(‘SESSIONSTATIC’, ‘S2’, 1, 1, 1, 0, 10, ‘00:00:00’, ‘23:59:59’, ‘2025-11-13 14:21:28’, NULL, ‘’);

call SP_JOB_CONFIG_COMMIT(‘SESSIONSTATIC’);

call SP_JOB_SET_SCHEMA(‘SESSIONSTATIC’, ‘SYSDBA’);

2、配置完成后，可以调整定时作业调度调整监控会话频率

3、查看会话历史记录
查询命令select * from SYSDBA.SESSION_STATS_HISTORY;