--调整参数，服务器端检查触发器是否触发的时间间隔
SP_SET_PARA_VALUE(1,'TIMER_TRIG_CHECK_INTERVAL',1);

--创建辅助表
CREATE TABLE "SYSDBA"."MEM_SESS_VM"
(
"NOW" TIMESTAMP(6),
"NAME" VARCHAR(128),
"SUM(TOTAL_SIZE)/1024/1024" BIGINT,
"AVG(TOTAL_SIZE)/1024/1024" DEC,
"MAX(TOTAL_SIZE)/1024/1024" BIGINT,
"SUM(TARGET_SIZE)/1024/1024" BIGINT,
"AVG(TARGET_SIZE)/1024/1024" DEC,
"MAX(TARGET_SIZE)/1024/1024" BIGINT);

CREATE TABLE "SYSDBA"."MEM_SUM"
(
"NOW" TIMESTAMP(6),
"SUM(TOTAL_SIZE)/1024/1024" BIGINT,
"NAME" VARCHAR(128));

CREATE TABLE "SYSDBA"."SESS_MEM"
(
"NOW" TIMESTAMP(6),
"100000+COUNT(*)" BIGINT,
"N" VARCHAR(32767),
"X" VARCHAR(39),
"'C'" VARCHAR(267),
"'1'" DEC);

CREATE TABLE "SYSDBA"."ARCH_QUEUE"
(
"NOW" TIMESTAMP(6),
"ARCH_TYPE" VARCHAR(32),
"WAITING" INTEGER,
"READY" BIGINT,
"CUR_WAIT_TIME" INTEGER,
"MAX_WAIT_TIME" INTEGER,
"TOTAL_WAIT_TIME" BIGINT,
"AVERAGE_WAIT_TIME" INTEGER);

--创建监控过程
CREATE OR REPLACE PROCEDURE SYSDBA.JK_MEM
AS
BEGIN
    INSERT INTO SYSDBA.MEM_SESS_VM  --监控SESS和VM内存使用
      SELECT NOW,
             NAME,
             SUM(TOTAL_SIZE)/1024/1024,
             AVG(TOTAL_SIZE)/1024/1024,
             MAX(TOTAL_SIZE)/1024/1024,
             SUM(TARGET_SIZE)/1024/1024,
             AVG(TARGET_SIZE)/1024/1024,
             MAX(TARGET_SIZE)/1024/1024
        FROM V$MEM_POOL
       WHERE NAME IN ('SESSION',
                      'VIRTUAL MACHINE')
    GROUP BY NAME
    ORDER BY 1 DESC;
    
    INSERT INTO SYSDBA.MEM_SUM    --监控MEM_POOL使用
      SELECT NOW,
             SUM(TOTAL_SIZE)/1024/1024,
             NAME
        FROM V$MEM_POOL
    GROUP BY NAME
    ORDER BY 2 DESC;
    
    INSERT INTO SYSDBA.SESS_MEM  --监控SESSIONS和MEM_POOL
      SELECT NOW,*
        FROM ( SELECT 100000+COUNT(*),'ALL' N,'2' X,'C','1' FROM V$SESSIONS
             UNION ALL
             SELECT 100000+COUNT(*),
                    'CALL',
                    '1',
                    '',
                    '2'
               FROM V$SESSIONS
              WHERE STATE IN('WAIT',
                             'ACTIVE')
                AND UPPER(SQL_TEXT) LIKE '%CALL%'
             UNION ALL
             SELECT 100000+COUNT(*),
                    'NO_MERGE',
                    '1',
                    '',
                    '2'
               FROM V$SESSIONS
              WHERE STATE IN('WAIT',
                             'ACTIVE')
                AND UPPER(SQL_TEXT) LIKE '%NO_MERGE%'
             UNION ALL
             SELECT 100000+COUNT(*),
                    'TOTAL',
                    '1',
                    'A',
                    1
               FROM V$SESSIONS
              WHERE STATE IN('WAIT',
                             'ACTIVE')
             UNION ALL
                SELECT DATEDIFF(SS,LAST_RECV_TIME,SYSDATE),
                       DBMS_LOB.SUBSTR(SF_GET_SESSION_SQL(SESS_ID)),
                       'SP_CLOSE_SESSION('||SESS_ID||');',
                       THRD_ID||APPNAME||CLNT_IP,
                       CAST( M.TS * 1.0/1024/1024 AS NUMBER(38,2))
                  FROM V$SESSIONS S
             LEFT JOIN (SELECT SUM(TOTAL_SIZE) TS,CREATOR FROM V$MEM_POOL GROUP BY CREATOR) M
                    ON S.THRD_ID = M.CREATOR
                 WHERE STATE IN('WAIT',
                                'ACTIVE') )
    ORDER BY 1 DESC;
    
    INSERT INTO SYSDBA.ARCH_QUEUE  --监控ARCH_QUEUE
    SELECT NOW,* FROM V$ARCH_QUEUE;
    
    COMMIT;
END;

--创建JOB
BEGIN
DBMS_JOB.ISUBMIT(1,'SYSDBA.JK_MEM;',SYSDATE,'SYSDATE+1/8640');  --每隔10秒执行一次SYSDBA.JK_MEM
COMMIT;
END; 

--删除JOB
BEGIN
 DBMS_JOB.REMOVE(1);
END;