【SQL监控】SQL完全监控的脚本

  • lhrbest

    2014-09-03 09:41:20
  • Oracle
  • 原创

【SQL监控】SQL完全监控的脚本


  
    有网友询问我的这篇blog  http://blog.itpub.net/26736162/viewspace-1218671/  中的视图 vw_sql_rubbish_monitor_lhr 的内容，我在这里列举一下，大家共同学习吧。




   由于要做论文，所以，把这篇文章里的内容又修改了一下，原有视图vw_sql_rubbish_monitor_lhr 修改为VW_SQL_PP_LHR，并且提供所有的SQL监控脚本。


   

点击(此处)折叠或打开

  1. SET SQLBLANKLINES ON

  3. --------------------------------- 历史SQL记录
  4. DROP TABLE XB_SQL_MONITOR_LHR PURGE;
  5. CREATE TABLE XB_SQL_MONITOR_LHR
  6. (
  7.   ID INTEGER PRIMARY KEY,
  8.   INST_ID NUMBER,
  9.   SID NUMBER,
  10.   SERIAL# NUMBER,
  11.   SPID NUMBER,
  12.   SQL_ID VARCHAR2(13),
  13.   SQL_TEXT VARCHAR2(4000),
  14.   SQL_FULLTEXT CLOB,
  15.   SQL_EXEC_START DATE,
  16.   SQL_EXEC_ID NUMBER,
  17.   COMMAND_TYPE VARCHAR2(20),
  18.   ELAPSED_TIME NUMBER,
  19.   ELAPSED_TIME2 VARCHAR2(30),
  20.   STATUS VARCHAR2(19),
  21.   USERNAME VARCHAR2(30),
  22.   OS_USER     VARCHAR2(30),
  23.   SESSION_TYPES VARCHAR2(4000),
  24.   LAST_LOAD_TIME DATE,
  25.   LAST_ACTIVE_TIME DATE,
  26.   EXECUTIONS NUMBER ,
  27.   PX_QCSID NUMBER,
  28.   CPU_TIME NUMBER,
  29.   FETCHES NUMBER,
  30.   BUFFER_GETS NUMBER,
  31.   DISK_READS NUMBER,
  32.   DIRECT_WRITES NUMBER,
  33.   BINDS_XML CLOB,
  34.   USER_IO_WAIT_TIME NUMBER,
  35.   CONCURRENCY_WAIT_TIME NUMBER,
  36.   PHYSICAL_READ_BYTES NUMBER,
  37.   PHYSICAL_WRITE_BYTES NUMBER,
  38.   KEY NUMBER,
  39.   PLAN_OBJECT_OWNER VARCHAR2(50),
  40.   PLAN_OBJECT_NAME VARCHAR2(50),
  41.   IN_DATE DATE
  42. ) NOLOGGING
  43. PARTITION BY RANGE(IN_DATE)
  44.    INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
  45.    (PARTITION P201704 VALUES LESS THAN(TO_DATE('201705','YYYYMM')));
  46.                                                               
  47. DROP SEQUENCE S_XB_SQL_MONITOR_LHR;
  48. CREATE SEQUENCE S_XB_SQL_MONITOR_LHR START WITH 1 INCREMENT BY 1 CACHE 1000;
  49. SELECT S_XB_SQL_MONITOR_LHR.NEXTVAL FROM DUAL;
  50.  
  51. CREATE INDEX IND_SQL_MONITOR_SQLID ON XB_SQL_MONITOR_LHR(SQL_ID) LOCAL NOLOGGING;
  52. CREATE INDEX IND_SQL_MONITOR_SID ON XB_SQL_MONITOR_LHR(SID,SERIAL#,SPID) LOCAL NOLOGGING;
  53. CREATE INDEX IND_SQL_MONITOR_IN_DATE ON XB_SQL_MONITOR_LHR(IN_DATE,COMMAND_TYPE,PLAN_OBJECT_NAME) LOCAL NOLOGGING;

  55.                           

  57. COMMENT ON TABLE XB_SQL_MONITOR_LHR IS '历史SQL监控';
  58. COMMENT ON COLUMN XB_SQL_MONITOR_LHR.SQL_EXEC_START IS 'SQL语句开始执行时间';
  59. COMMENT ON COLUMN XB_SQL_MONITOR_LHR.ELAPSED_TIME IS 'SQL语句执行时间（微秒）';
  60. COMMENT ON COLUMN XB_SQL_MONITOR_LHR.EXECUTIONS IS 'SQL语句执行次数';
  61.                                                      
  62. GRANT SELECT ON XB_SQL_MONITOR_LHR TO PUBLIC;


  65. --------------------------------- 历史SQL执行计划记录
  66. DROP TABLE XB_SQL_PLAN_MONITOR_LHR PURGE;
  67. CREATE TABLE XB_SQL_PLAN_MONITOR_LHR (
  68.         ID NUMBER PRIMARY KEY,
  69.     INST_ID NUMBER,
  70.         SQL_MONITOR_ID NUMBER,
  71.         KEY NUMBER,
  72.         STATUS VARCHAR2(25),
  73.         SID NUMBER,
  74.     SERIAL# NUMBER,
  75.     SPID NUMBER,
  76.         SQL_ID VARCHAR2(25),
  77.         SQL_EXEC_START DATE,
  78.     SQL_EXEC_ID NUMBER,
  79.     SQL_PLAN_HASH_VALUE NUMBER ,
  80.     SQL_CHILD_ADDRESS RAW(8),
  81.     CHILD_NUMBER NUMBER,
  82.     PLAN_PARENT_ID NUMBER,
  83.     PLAN_LINE_ID NUMBER,
  84.     PLAN_OPERATION VARCHAR2(30),
  85.     PLAN_OPTIONS VARCHAR2(30),
  86.     OPTIMIZER VARCHAR2(80),
  87.     OBJECT# NUMBER,
  88.     PLAN_OBJECT_OWNER VARCHAR2(30),
  89.     PLAN_OBJECT_NAME VARCHAR2(30),
  90.     PLAN_OBJECT_TYPE VARCHAR2(40),
  91.     OBJECT_ALIAS VARCHAR2(80),
  92.     PLAN_DEPTH NUMBER,
  93.     PLAN_POSITION NUMBER,
  94.     PLAN_COST NUMBER,
  95.     PLAN_CARDINALITY NUMBER,
  96.     PLAN_BYTES NUMBER,
  97.     PLAN_TIME NUMBER,
  98.     PLAN_PARTITION_START VARCHAR2(255) ,
  99.     PLAN_PARTITION_STOP VARCHAR2(255),
  100.     PLAN_CPU_COST NUMBER,
  101.     PLAN_IO_COST NUMBER,
  102.     PLAN_TEMP_SPACE NUMBER,
  103.     STARTS NUMBER,
  104.     OUTPUT_ROWS NUMBER,
  105.     IO_INTERCONNECT_BYTES NUMBER,
  106.     PHYSICAL_READ_REQUESTS NUMBER,
  107.     PHYSICAL_READ_BYTES NUMBER,
  108.     PHYSICAL_WRITE_REQUESTS NUMBER,
  109.     PHYSICAL_WRITE_BYTES NUMBER,
  110.     SEARCH_COLUMNS NUMBER,
  111.         FILTER_PREDICATES VARCHAR2(4000) ,
  112.         ACCESS_PREDICATES VARCHAR2(4000) ,
  113.         PROJECTION VARCHAR2(4000) ,
  114.         OTHER_XML CLOB,
  115.     IN_DATE DATE
  116. ) NOLOGGING
  117. PARTITION BY RANGE(IN_DATE)
  118.     INTERVAL(NUMTOYMINTERVAL(1,'MONTH') )
  119.   (PARTITION P201704 VALUES LESS THAN (TO_DATE('201705', 'YYYYMM'))
  120. );

  122. DROP SEQUENCE S_XB_SQL_PLAN_MONITOR_LHR;
  123. CREATE SEQUENCE S_XB_SQL_PLAN_MONITOR_LHR START WITH 1 INCREMENT BY 1 CACHE 1000;
  124. SELECT S_XB_SQL_PLAN_MONITOR_LHR.NEXTVAL FROM DUAL;
  125.  

  127. CREATE INDEX IND_SQL_MONITOR_ID ON XB_SQL_PLAN_MONITOR_LHR(SQL_MONITOR_ID) NOLOGGING;
  128. CREATE INDEX IND_SQL_PLAN_MONITOR_IN_DATE ON XB_SQL_PLAN_MONITOR_LHR(IN_DATE) LOCAL NOLOGGING;
  129. CREATE INDEX IND_SMONITOR_SQLIDSIDKEY ON XB_SQL_PLAN_MONITOR_LHR(SQL_ID,SID,SERIAL#,SPID,KEY) LOCAL NOLOGGING;





  135. --------监控正在运行的SQL语句
  136. DROP TABLE XB_SQL_MONITOR_PP_LHR;
  137. -- Create table
  138. CREATE TABLE XB_SQL_MONITOR_PP_LHR
  139. (
  140.   ID NUMBER NOT NULL,
  141.   INST_ID NUMBER,
  142.   SID NUMBER,
  143.   SERIAL# NUMBER,
  144.   SPID VARCHAR2(24),
  145.   OSUSER VARCHAR2(30),
  146.   USERNAME VARCHAR2(30),
  147.   SQL_TEXT VARCHAR2(4000),
  148.   SQL_FULLTEXT CLOB,
  149.   PLAN_OPERATION VARCHAR2(61),
  150.   STARTS NUMBER,
  151.   PLAN_PARTITION_START VARCHAR2(128),
  152.   PLAN_PARTITION_STOP VARCHAR2(128),
  153.   EXECUTIONS NUMBER,
  154.   SQL_ID VARCHAR2(13),
  155.   SQL_EXEC_START DATE,
  156.   LOGON_TIME DATE,
  157.   LAST_LOAD_TIME DATE,
  158.   LAST_ACTIVE_TIME DATE,
  159.   ELAPSED_TIME VARCHAR2(500),
  160.   ELAPSED_TIME1 NUMBER,
  161.   MONITOR_TYPES VARCHAR2(500),
  162.   MONITOR_TYPES1 NUMBER,
  163.   MONITOR_VALUE NUMBER,
  164.   TUNING_RESULT CLOB,
  165.   TUNING_TIME DATE,
  166.   SESSION_INFO VARCHAR2(4000),
  167.   SESSION_STATE VARCHAR2(30),
  168.   EVENT VARCHAR2(4000),
  169.   CPU_TIME NUMBER,
  170.   BUFFER_GETS NUMBER,
  171.   PHYSICAL_READ_BYTES NUMBER,
  172.   PHYSICAL_WRITE_BYTES NUMBER,
  173.   USER_IO_WAIT_TIME NUMBER,
  174.   BLOCKING_INSTANCE NUMBER,
  175.   BLOCKING_SESSION NUMBER,
  176.   LAST_CALL_ET NUMBER,
  177.   ASH_COUNTS NUMBER,
  178.   IN_DATE DATE
  179. ) NOLOGGING
  180. PARTITION BY RANGE (IN_DATE) INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
  181. (
  182.   PARTITION P201704 VALUES LESS THAN (TO_DATE('201705', 'YYYYMM'))
  183. );

  185. CREATE INDEX IND_SQL_MONITOR_PPID ON XB_SQL_MONITOR_PP_LHR(SQL_ID) LOCAL NOLOGGING;
  186. CREATE INDEX IND_SQL_MONITOR_PP_DATE ON XB_SQL_MONITOR_PP_LHR(IN_DATE) LOCAL NOLOGGING;

  188. DROP SEQUENCE S_XB_SQL_MONITOR_PP_LHR;
  189. CREATE SEQUENCE S_XB_SQL_MONITOR_PP_LHR START WITH 1 INCREMENT BY 1 CACHE 1000;



  193. DROP TABLE XB_SQL_PARAMETERS_LHR;
  194. CREATE TABLE XB_SQL_PARAMETERS_LHR
  195. ( ID NUMBER PRIMARY KEY,
  196.   CN_NAME VARCHAR2(100) NOT NULL,
  197.   PARAM_NAME VARCHAR2(50) NOT NULL,
  198.   PARAM_TYPE VARCHAR2(50) ,
  199.   PARAM_VALUE VARCHAR2(50) ,
  200.   PARAM_UNIT VARCHAR2(50) ,
  201.   COMMENTS VARCHAR2(500)
  202. ) NOLOGGING CACHE ;


  205. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  206. VALUES (1, 'SQL占用UNDO表空间过大', 'V_UNDOSIZE', 'NUMBER', '52428800', 'BYTES', '单条SQL占用的UNDO表空间大小');
  207. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  208. VALUES (2, 'SQL占用TMP表空间过大', 'V_TMPSIZE', 'NUMBER', '15204352', 'BYTES', '单条SQL占用的临时表空间大小');
  209. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  210. VALUES (3, '执行计划COST花费过大', 'V_PLAN_COST', 'NUMBER', '114', NULL, 'SQL执行计划中的COST花费，参照值，无单位');
  211. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  212. VALUES (4, '执行计划预估行数过大', 'V_PLAN_CARDINALITY', 'NUMBER', '1426', '行', 'SQL执行计划中的预估行数');
  213. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  214. VALUES (5, 'SQL执行时间过大', 'V_ELAPSED_TIME', 'NUMBER', '29', '秒', 'SQL执行时间，单位为秒，1秒等于1000000微秒');
  215. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  216. VALUES (6, 'SQL执行次数过大', 'V_EXECUTIONS', 'NUMBER', '7616', '次', 'SQL执行次数');
  217. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  218. VALUES (7, 'SQL监控时间间隔', 'V_INTERVALTIME', 'NUMBER', '30', '秒', 'SQL监控时间间隔，最小值20秒，最大值120秒，默认30秒，推荐30秒');
  219. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  220. VALUES (8, '笛卡尔积SQL监控', 'V_MERGEJOIN', NULL, NULL, NULL, 'SQL形成笛卡尔积');
  221. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  222. VALUES (9, '分区表全分区扫描', 'V_PARTTABLESCAN', NULL, NULL, NULL, '分区表全分区扫描');
  223. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  224. VALUES (13, 'SQL并行个数过大', 'V_PARALLEL', 'NUMBER', 8, '', 'SQL开并行的最大并行个数');
  225. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  226. VALUES (14, '系统预估的剩余执行时间过长', 'V_ESTIMATE_TIME', 'NUMBER', 900, '秒', '系统预估的剩余执行时间过长');
  227. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  228. VALUES (15, '逻辑读过大', 'V_LOGICAL_READS', 'NUMBER', 1510407, '', '逻辑读过大');
  229. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  230. VALUES (16, '物理读过大', 'V_DISK_READS', 'NUMBER', 1510407, '', '物理读过大');
  231. INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
  232. VALUES (17, '等待事件异常', 'V_WAIT_EVENT', '', '', '', '等待事件异常的SQL语句');
  233. COMMIT;
  234.  


  237. --@@PKG_SQL_MONITOR_LHR.PCK
  238. --------------------------------------------------------------
  239. CREATE OR REPLACE PACKAGE PKG_SQL_MONITOR_LHR AUTHID CURRENT_USER AS

  241.   -----------------------------------------------------------------------------------
  242.   -- CREATED ON 2014-07-15 12:19:12 BY LHR
  243.   --CHANGED ON 2013-07-1512:19:12 BY LHR
  244.   -- FUNCTION： 该包记录所有历史SQL语句
  245.   ---主要采用了 V$SQL_MONITOR 和 V$SQL_PLAN_MONITOR 2个视图
  246.   -----------------------------------------------------------------------------------

  248.   -----------------------------存过--------------------------------------
  249.   --已经执行完毕的SQL
  250.   PROCEDURE P_SQL_DONE_LHR;

  252.   -- EXECUTING POOR PERFORMANCE 正在执行的性能差的SQL
  253.   PROCEDURE P_SQL_EPP_LHR;
  254.   PROCEDURE P_SQL_EPP2_LHR;
  255.   PROCEDURE P_TUNING_SQL;
  256.   PROCEDURE P_GET_PPSQL_PARAMETER;

  258.   ------------------------------函数-------------------------------------

  260.   FUNCTION F_GET_RIGHT_SIZE_LHR(P_SIZE IN VARCHAR2) RETURN VARCHAR2;
  261.   FUNCTION F_GET_TOTAL_TIME_LHR(P_TOTAL_SECONDS NUMBER,
  262.                                 P_FLAG VARCHAR2 DEFAULT 'S')
  263.     RETURN VARCHAR2 DETERMINISTIC;

  265.   ---得到当前SQL语句操作的对象
  266.   FUNCTION F_GET_SQL_OBJECT_LHR(P_SQL VARCHAR2,
  267.                                 P_COMMAND VARCHAR2 DEFAULT 'INTO',
  268.                                 PFLAG NUMBER DEFAULT 1) RETURN VARCHAR2;

  270.   -----得到当前SQL语句的命令类型
  271.   FUNCTION F_GET_SQL_COMMAND_LHR(P_SQL VARCHAR2) RETURN VARCHAR2;

  273. END PKG_SQL_MONITOR_LHR;
  274. /
  275. CREATE OR REPLACE PACKAGE BODY PKG_SQL_MONITOR_LHR AS

  277.   PROCEDURE P_SQL_DONE_LHR AS

  279.     V_DATE DATE := SYSDATE;
  280.     V_COMMAND VARCHAR2(50);
  281.     V_SQL VARCHAR2(32767);
  282.     V_OSUSER VARCHAR2(255);
  283.     V_SPID NUMBER;

  285.   BEGIN

  287.     FOR CUR IN (SELECT S_XB_SQL_MONITOR_LHR.NEXTVAL ID,
  288.                        A.SID,
  289.                        A.SESSION_SERIAL# SERIAL#,
  290.                        A.SQL_ID,
  291.                        NVL(A.SQL_TEXT, NS.SQL_TEXT) SQL_TEXT,
  292.                        DECODE(A.IS_FULL_SQLTEXT,
  293.                               'N',
  294.                               NS.SQL_FULLTEXT,
  295.                               A.SQL_TEXT) SQL_FULLTEXT,
  296.                        A.SQL_EXEC_START,
  297.                        A.ELAPSED_TIME,
  298.                        A.STATUS,
  299.                        A.USERNAME,
  300.                        CASE
  301.                          WHEN A.ERROR_MESSAGE IS NOT NULL THEN
  302.                           (A.MODULE || '--' || A.ACTION || '--' || A.PROGRAM || '--' ||
  303.                           A.PROCESS_NAME || '--' || A.CLIENT_IDENTIFIER || '--' ||
  304.                           A.CLIENT_INFO || '--' || A.SERVICE_NAME) || '----【' ||
  305.                           A.ERROR_MESSAGE || '】'
  306.                          ELSE
  307.                           (A.MODULE || '--' || A.ACTION || '--' || A.PROGRAM || '--' ||
  308.                           A.PROCESS_NAME || '--' || A.CLIENT_IDENTIFIER || '--' ||
  309.                           A.CLIENT_INFO || '--' || A.SERVICE_NAME)
  310.                        END AS SESSION_TYPES,
  311.                        CASE
  312.                          WHEN A.PX_QCSID IS NOT NULL THEN
  313.                           A.PX_QCSID
  314.                          WHEN A.PX_QCSID IS NULL AND
  315.                               A.PX_SERVERS_ALLOCATED IS NOT NULL THEN
  316.                           A.SID
  317.                        END AS PX_QCSID,
  318.                        (SELECT NC.COMMAND_NAME
  319.                           FROM V$SQLCOMMAND NC
  320.                          WHERE NS.COMMAND_TYPE = NC.COMMAND_TYPE) COMMAND_TYPE,
  321.                        A.SQL_EXEC_ID,
  322.                        F_GET_TOTAL_TIME_LHR(A.ELAPSED_TIME / 1000000) ELAPSED_TIME2,
  323.                        A.PROCESS_NAME,
  324.                        A.MODULE,
  325.                        A.CPU_TIME,
  326.                        A.FETCHES,
  327.                        A.BUFFER_GETS,
  328.                        A.DISK_READS,
  329.                        A.DIRECT_WRITES,
  330.                        A.BINDS_XML,
  331.                        A.USER_IO_WAIT_TIME,
  332.                        A.CONCURRENCY_WAIT_TIME,
  333.                        A.PHYSICAL_READ_BYTES,
  334.                        A.PHYSICAL_WRITE_BYTES,
  335.                        A.KEY,
  336.                        C.PLAN_OBJECT_OWNER,
  337.                        C.PLAN_OBJECT_NAME,
  338.                        A.INST_ID,
  339.                        NS.EXECUTIONS EXECUTIONS,
  340.                        NS.LAST_LOAD_TIME,
  341.                        NS.LAST_ACTIVE_TIME
  342.                   FROM (SELECT *
  343.                           FROM GV$SQL_MONITOR NA
  344.                          WHERE NA.STATUS LIKE 'DONE%'
  345.                            AND NA.LAST_REFRESH_TIME >= SYSDATE - 1
  346.                            AND NOT EXISTS
  347.                          (SELECT 1
  348.                                   FROM XB_SQL_MONITOR_LHR B
  349.                                  WHERE NA.SID = B.SID
  350.                                    AND NA.SESSION_SERIAL# = B.SERIAL#
  351.                                    AND NA.SQL_ID = B.SQL_ID
  352.                                    AND NA.SQL_EXEC_ID = B.SQL_EXEC_ID
  353.                                    AND B.IN_DATE BETWEEN SYSDATE - 1 AND
  354.                                        SYSDATE
  355.                                    AND B.INST_ID = NA.INST_ID)) A
  356.                   LEFT OUTER JOIN GV$SQL_PLAN_MONITOR C
  357.                     ON (A.KEY = C.KEY AND A.SQL_ID = C.SQL_ID AND
  358.                        C.PLAN_LINE_ID = 1 AND A.SQL_EXEC_ID = C.SQL_EXEC_ID AND
  359.                        C.PLAN_OBJECT_OWNER IS NOT NULL AND
  360.                        C.PLAN_OBJECT_NAME IS NOT NULL AND
  361.                        C.PLAN_OPERATION IN ('UPDATE', 'DELETE', 'MERGE') AND
  362.                        A.INST_ID = C.INST_ID)
  363.                   LEFT OUTER JOIN GV$SQLAREA NS
  364.                     ON (NS.SQL_ID = A.SQL_ID AND NS.INST_ID = A.INST_ID)) LOOP

  366.       V_SQL := SUBSTR(NVL(DBMS_LOB.SUBSTR(CUR.SQL_FULLTEXT, 3900, 1),
  367.                           CUR.SQL_TEXT),
  368.                       1,
  369.                       3900);

  371.       ------ SQL语句的命令类型
  372.       IF CUR.COMMAND_TYPE IS NULL AND V_SQL IS NOT NULL THEN
  373.         BEGIN
  374.           CUR.COMMAND_TYPE := F_GET_SQL_COMMAND_LHR(V_SQL);
  375.         EXCEPTION
  376.           WHEN OTHERS THEN
  377.             NULL;
  378.         END;

  380.       END IF;

  382.       ---- 找出正在操作的对象
  383.       IF (CUR.COMMAND_TYPE IN ('INSERT',
  384.                                'MERGE INTO',
  385.                                'CREATE TABLE',
  386.                                'CREATE INDEX',
  387.                                'ALTER TABLE',
  388.                                'ALTER INDEX')) AND
  389.          (CUR.PLAN_OBJECT_OWNER IS NULL AND CUR.PLAN_OBJECT_NAME IS NULL) AND
  390.          V_SQL IS NOT NULL THEN

  392.         V_COMMAND := CASE
  393.                        WHEN CUR.COMMAND_TYPE IN ('INSERT', 'MERGE INTO') THEN
  394.                         'INTO'
  395.                        WHEN CUR.COMMAND_TYPE IN ('CREATE TABLE', 'ALTER TABLE') THEN
  396.                         'TABLE'
  397.                        WHEN CUR.COMMAND_TYPE IN ('CREATE INDEX', 'ALTER INDEX') THEN
  398.                         'INDEX'
  399.                      END;

  401.         BEGIN

  403.           CUR.PLAN_OBJECT_OWNER := NVL(F_GET_SQL_OBJECT_LHR(V_SQL,
  404.                                                             V_COMMAND,
  405.                                                             2),
  406.                                        CUR.USERNAME);
  407.           CUR.PLAN_OBJECT_NAME := F_GET_SQL_OBJECT_LHR(V_SQL, V_COMMAND);
  408.         EXCEPTION
  409.           WHEN OTHERS THEN
  410.             NULL;
  411.         END;

  413.       END IF;

  415.       INSERT INTO XB_SQL_MONITOR_LHR
  416.         (ID,
  417.          INST_ID,
  418.          SID,
  419.          SERIAL#,
  420.          SQL_ID,
  421.          SQL_TEXT,
  422.          SQL_FULLTEXT,
  423.          SQL_EXEC_START,
  424.          ELAPSED_TIME,
  425.          STATUS,
  426.          USERNAME,
  427.          SESSION_TYPES,
  428.          EXECUTIONS,
  429.          IN_DATE,
  430.          PX_QCSID,
  431.          COMMAND_TYPE,
  432.          SQL_EXEC_ID,
  433.          ELAPSED_TIME2,
  434.          OS_USER,
  435.          CPU_TIME,
  436.          FETCHES,
  437.          BUFFER_GETS,
  438.          DISK_READS,
  439.          DIRECT_WRITES,
  440.          BINDS_XML,
  441.          USER_IO_WAIT_TIME,
  442.          CONCURRENCY_WAIT_TIME,
  443.          PHYSICAL_READ_BYTES,
  444.          PHYSICAL_WRITE_BYTES,
  445.          KEY,
  446.          PLAN_OBJECT_OWNER,
  447.          PLAN_OBJECT_NAME,
  448.          SPID,
  449.          LAST_LOAD_TIME,
  450.          LAST_ACTIVE_TIME)
  451.       VALUES
  452.         (CUR.ID,
  453.          CUR.INST_ID,
  454.          CUR.SID,
  455.          CUR.SERIAL#,
  456.          CUR.SQL_ID,
  457.          CUR.SQL_TEXT,
  458.          CUR.SQL_FULLTEXT,
  459.          CUR.SQL_EXEC_START,
  460.          CUR.ELAPSED_TIME,
  461.          CUR.STATUS,
  462.          CUR.USERNAME,
  463.          CUR.SESSION_TYPES,
  464.          CUR.EXECUTIONS,
  465.          V_DATE,
  466.          CUR.PX_QCSID,
  467.          CUR.COMMAND_TYPE,
  468.          CUR.SQL_EXEC_ID,
  469.          CUR.ELAPSED_TIME2,
  470.          V_OSUSER,
  471.          CUR.CPU_TIME,
  472.          CUR.FETCHES,
  473.          CUR.BUFFER_GETS,
  474.          CUR.DISK_READS,
  475.          CUR.DIRECT_WRITES,
  476.          CUR.BINDS_XML,
  477.          CUR.USER_IO_WAIT_TIME,
  478.          CUR.CONCURRENCY_WAIT_TIME,
  479.          CUR.PHYSICAL_READ_BYTES,
  480.          CUR.PHYSICAL_WRITE_BYTES,
  481.          CUR.KEY,
  482.          CUR.PLAN_OBJECT_OWNER,
  483.          CUR.PLAN_OBJECT_NAME,
  484.          V_SPID,
  485.          CUR.LAST_LOAD_TIME,
  486.          CUR.LAST_ACTIVE_TIME);

  488.       INSERT INTO XB_SQL_PLAN_MONITOR_LHR
  489.         (ID,
  490.          INST_ID,
  491.          SQL_MONITOR_ID,
  492.          KEY,
  493.          STATUS,
  494.          SID,
  495.          SQL_ID,
  496.          SQL_EXEC_START,
  497.          SQL_EXEC_ID,
  498.          SQL_PLAN_HASH_VALUE,
  499.          SQL_CHILD_ADDRESS,
  500.          PLAN_PARENT_ID,
  501.          PLAN_LINE_ID,
  502.          PLAN_OPERATION,
  503.          PLAN_OPTIONS,
  504.          PLAN_OBJECT_OWNER,
  505.          PLAN_OBJECT_NAME,
  506.          PLAN_OBJECT_TYPE,
  507.          PLAN_DEPTH,
  508.          PLAN_POSITION,
  509.          PLAN_COST,
  510.          PLAN_CARDINALITY,
  511.          PLAN_BYTES,
  512.          PLAN_TIME,
  513.          PLAN_PARTITION_START,
  514.          PLAN_PARTITION_STOP,
  515.          PLAN_CPU_COST,
  516.          PLAN_IO_COST,
  517.          PLAN_TEMP_SPACE,
  518.          STARTS,
  519.          OUTPUT_ROWS,
  520.          IO_INTERCONNECT_BYTES,
  521.          PHYSICAL_READ_REQUESTS,
  522.          PHYSICAL_READ_BYTES,
  523.          PHYSICAL_WRITE_REQUESTS,
  524.          PHYSICAL_WRITE_BYTES,
  525.          IN_DATE,
  526.          CHILD_NUMBER,
  527.          OBJECT#,
  528.          OBJECT_ALIAS,
  529.          OPTIMIZER,
  530.          SEARCH_COLUMNS,
  531.          FILTER_PREDICATES,
  532.          ACCESS_PREDICATES,
  533.          PROJECTION,
  534.          OTHER_XML,
  535.          SERIAL#,
  536.          SPID)

  538.         SELECT S_XB_SQL_PLAN_MONITOR_LHR.NEXTVAL ID,
  539.                CUR.INST_ID,
  540.                (SELECT NB.ID
  541.                   FROM XB_SQL_MONITOR_LHR NB
  542.                  WHERE NB.KEY = A.KEY
  543.                    AND NB.SID = CUR.SID
  544.                    AND NB.SQL_EXEC_ID = NB.SQL_EXEC_ID
  545.                    AND NB.IN_DATE = V_DATE
  546.                    AND NB.SQL_ID = CUR.SQL_ID
  547.                    AND NB.INST_ID = CUR.INST_ID) SQL_MONITOR_ID,
  548.                KEY,
  549.                STATUS,
  550.                SID,
  551.                A.SQL_ID,
  552.                SQL_EXEC_START,
  553.                SQL_EXEC_ID,
  554.                SQL_PLAN_HASH_VALUE,
  555.                SQL_CHILD_ADDRESS,
  556.                PLAN_PARENT_ID,
  557.                PLAN_LINE_ID,
  558.                PLAN_OPERATION,
  559.                PLAN_OPTIONS,
  560.                PLAN_OBJECT_OWNER,
  561.                PLAN_OBJECT_NAME,
  562.                PLAN_OBJECT_TYPE,
  563.                PLAN_DEPTH,
  564.                PLAN_POSITION,
  565.                NVL(A.PLAN_COST, B.COST) COST,
  566.                PLAN_CARDINALITY,
  567.                PLAN_BYTES,
  568.                PLAN_TIME,
  569.                PLAN_PARTITION_START,
  570.                PLAN_PARTITION_STOP,
  571.                PLAN_CPU_COST,
  572.                PLAN_IO_COST,
  573.                PLAN_TEMP_SPACE,
  574.                STARTS,
  575.                OUTPUT_ROWS,
  576.                IO_INTERCONNECT_BYTES,
  577.                PHYSICAL_READ_REQUESTS,
  578.                PHYSICAL_READ_BYTES,
  579.                PHYSICAL_WRITE_REQUESTS,
  580.                PHYSICAL_WRITE_BYTES,
  581.                V_DATE IN_DATE,
  582.                B.CHILD_NUMBER,
  583.                B.OBJECT#,
  584.                B.OBJECT_ALIAS,
  585.                B.OPTIMIZER,
  586.                B.SEARCH_COLUMNS,
  587.                B.FILTER_PREDICATES,
  588.                B.ACCESS_PREDICATES,
  589.                B.PROJECTION,
  590.                B.OTHER_XML,
  591.                CUR.SERIAL#,
  592.                V_SPID
  593.           FROM GV$SQL_PLAN_MONITOR A
  594.           LEFT OUTER JOIN GV$SQL_PLAN B
  595.             ON (A.SQL_ID = B.SQL_ID AND
  596.                A.SQL_PLAN_HASH_VALUE = B.PLAN_HASH_VALUE AND
  597.                A.SQL_CHILD_ADDRESS = B.CHILD_ADDRESS AND
  598.                A.PLAN_LINE_ID = B.ID AND B.SQL_ID = CUR.SQL_ID AND
  599.                A.INST_ID = B.INST_ID)
  600.          WHERE A.STATUS LIKE 'DONE%'
  601.            AND A.SQL_ID NOT IN
  602.                (SELECT BB.SQL_ID
  603.                   FROM XB_SQL_PLAN_MONITOR_LHR BB
  604.                  WHERE BB.SID = CUR.SID
  605.                    AND BB.KEY = CUR.KEY
  606.                    AND BB.SQL_ID = CUR.SQL_ID
  607.                    AND BB.SQL_EXEC_ID = CUR.SQL_EXEC_ID
  608.                    AND BB.IN_DATE BETWEEN SYSDATE - 1 AND SYSDATE
  609.                    AND BB.INST_ID = A.INST_ID)
  610.            AND A.SQL_ID = CUR.SQL_ID
  611.            AND A.KEY = CUR.KEY
  612.            AND A.SID = CUR.SID
  613.            AND A.SQL_EXEC_ID = CUR.SQL_EXEC_ID
  614.            AND A.INST_ID = CUR.INST_ID;

  616.     END LOOP;

  618.     COMMIT;

  620.   EXCEPTION
  621.     WHEN OTHERS THEN
  622.       NULL;
  623.   END P_SQL_DONE_LHR;

  625.   ----------------------------------------------------------------------------------------------

  627.   PROCEDURE P_SQL_EPP_LHR AS

  629.     -----------------------------------------------------------------------------------
  630.     -- Created on 2015/11/21 16:49:28 by lhr
  631.     --Changed on 2017/04/03 16:49:28 by lhr
  632.     -- function： 监控正在执行的SQL
  633.     -----------------------------------------------------------------------------------

  635.     V_TMPSIZE NUMBER := 5368709120; --BYTES
  636.     V_UNDOSIZE NUMBER := 5368709120; --BYTES
  637.     V_PLAN_COST NUMBER := 3107523095; --COST 花费
  638.     V_PLAN_CARDINALITY NUMBER := 30748908521460; --预估行数
  639.     V_ELAPSED_TIME NUMBER := 10000000; ---执行时间，单位：微秒，例执行时间设置5小时，则： 5H*60*60*1000000 ，1秒等于1000000微秒
  640.     V_EXECUTIONS NUMBER := 1000; --执行次数
  641.     V_PARALLEL NUMBER := 4; --SQL开并行的个数
  642.     V_ESTIMATE_TIME NUMBER := 900; --系统预估的剩余执行时间过长

  644.   BEGIN

  646.     SELECT SUM(CASE T.PARAM_NAME
  647.                  WHEN 'V_UNDOSIZE' THEN
  648.                   T.PARAM_VALUE
  649.                END) V_UNDOSIZE,
  650.            SUM(CASE T.PARAM_NAME
  651.                  WHEN 'V_TMPSIZE' THEN
  652.                   T.PARAM_VALUE
  653.                END) V_TMPSIZE,
  654.            SUM(CASE T.PARAM_NAME
  655.                  WHEN 'V_PLAN_COST' THEN
  656.                   T.PARAM_VALUE
  657.                END) V_PLAN_COST,
  658.            SUM(CASE T.PARAM_NAME
  659.                  WHEN 'V_PLAN_CARDINALITY' THEN
  660.                   T.PARAM_VALUE
  661.                END) V_PLAN_CARDINALITY,
  662.            SUM(CASE T.PARAM_NAME
  663.                  WHEN 'V_ELAPSED_TIME' THEN
  664.                   T.PARAM_VALUE
  665.                END) V_ELAPSED_TIME,
  666.            SUM(CASE T.PARAM_NAME
  667.                  WHEN 'V_EXECUTIONS' THEN
  668.                   T.PARAM_VALUE
  669.                END) V_EXECUTIONS,
  670.            SUM(CASE T.PARAM_NAME
  671.                  WHEN 'V_PARALLEL' THEN
  672.                   T.PARAM_VALUE
  673.                END) V_PARALLEL,
  674.            SUM(CASE T.PARAM_NAME
  675.                  WHEN 'V_ESTIMATE_TIME' THEN
  676.                   T.PARAM_VALUE
  677.                END) V_ESTIMATE_TIME
  678.       INTO V_UNDOSIZE,
  679.            V_TMPSIZE,
  680.            V_PLAN_COST,
  681.            V_PLAN_CARDINALITY,
  682.            V_ELAPSED_TIME,
  683.            V_EXECUTIONS,
  684.            V_PARALLEL,
  685.            V_ESTIMATE_TIME
  686.       FROM XB_SQL_PARAMETERS_LHR T
  687.      WHERE T.PARAM_NAME IN ('V_UNDOSIZE',
  688.                             'V_TMPSIZE',
  689.                             'V_PLAN_COST',
  690.                             'V_PLAN_CARDINALITY',
  691.                             'V_ELAPSED_TIME',
  692.                             'V_EXECUTIONS',
  693.                             'V_PARALLEL',
  694.                             'V_ESTIMATE_TIME');

  696.     --------------------------------------------- 垃圾SQL监控
  697.     INSERT INTO XB_SQL_MONITOR_PP_LHR
  698.       (ID,
  699.        INST_ID,
  700.        SID,
  701.        SERIAL#,
  702.        SPID,
  703.        OSUSER,
  704.        USERNAME,
  705.        SQL_TEXT,
  706.        SQL_FULLTEXT,
  707.        PLAN_OPERATION,
  708.        STARTS,
  709.        PLAN_PARTITION_START,
  710.        PLAN_PARTITION_STOP,
  711.        EXECUTIONS,
  712.        SQL_ID,
  713.        SQL_EXEC_START,
  714.        LOGON_TIME,
  715.        LAST_LOAD_TIME,
  716.        LAST_ACTIVE_TIME,
  717.        ELAPSED_TIME,
  718.        ELAPSED_TIME1,
  719.        MONITOR_TYPES,
  720.        MONITOR_TYPES1,
  721.        MONITOR_VALUE,
  722.        SESSION_INFO,
  723.        SESSION_STATE,
  724.        EVENT,
  725.        CPU_TIME,
  726.        BUFFER_GETS,
  727.        PHYSICAL_READ_BYTES,
  728.        PHYSICAL_WRITE_BYTES,
  729.        USER_IO_WAIT_TIME,
  730.        BLOCKING_INSTANCE,
  731.        BLOCKING_SESSION,
  732.        LAST_CALL_ET,
  733.        ASH_COUNTS,
  734.        IN_DATE)
  735.       SELECT S_XB_SQL_MONITOR_PP_LHR.NEXTVAL,
  736.              INST_ID,
  737.              SID,
  738.              SERIAL#,
  739.              SPID,
  740.              OSUSER,
  741.              USERNAME,
  742.              SQL_TEXT,
  743.              (SELECT NB.SQL_FULLTEXT
  744.                 FROM GV$SQLAREA NB
  745.                WHERE NB.INST_ID = T.INST_ID
  746.                  AND T.SQL_ID = NB.SQL_ID) SQL_FULLTEXT,
  747.              PLAN_OPERATION,
  748.              STARTS,
  749.              PLAN_PARTITION_START,
  750.              PLAN_PARTITION_STOP,
  751.              EXECUTIONS,
  752.              SQL_ID,
  753.              SQL_EXEC_START,
  754.              LOGON_TIME,
  755.              '' LAST_LOAD_TIME,
  756.              '' LAST_ACTIVE_TIME,
  757.              F_GET_TOTAL_TIME_LHR(ELAPSED_TIME_S),
  758.              ELAPSED_TIME_S,
  759.              MONITOR_TYPES,
  760.              '' MONITOR_TYPES1,
  761.              '' MONITOR_VALUE,
  762.              SESSION_INFO,
  763.              SESSION_STATE,
  764.              EVENT,
  765.              CPU_TIME,
  766.              BUFFER_GETS,
  767.              PHYSICAL_READ_BYTES,
  768.              PHYSICAL_WRITE_BYTES,
  769.              USER_IO_WAIT_TIME,
  770.              BLOCKING_INSTANCE,
  771.              BLOCKING_SESSION,
  772.              LAST_CALL_ET,
  773.              ASH_COUNTS,
  774.              SYSDATE IN_DATE
  775.         FROM (
  776.              --记录所有正在运行中的性能差的SQL语句
  777.              WITH TMPS AS (SELECT WB.INST_ID INST_ID,
  778.                                   WB.SID SID,
  779.                                   WB.SERIAL#,
  780.                                   WB.SPID,
  781.                                   WB.OSUSER,
  782.                                   WB.USERNAME,
  783.                                   WA.PLAN_DEPTH,
  784.                                   WA.PLAN_OPERATION PLAN_OPERATION,
  785.                                   WA.PLAN_OPTIONS,
  786.                                   WA.PLAN_PARTITION_START,
  787.                                   WA.PLAN_PARTITION_STOP,
  788.                                   WA.STARTS,
  789.                                   WA.PLAN_COST,
  790.                                   WA.PLAN_CARDINALITY,
  791.                                   NVL(WB.SQL_ID, WA.SQL_ID) SQL_ID,
  792.                                   WB.SQL_EXEC_START,
  793.                                   WA.PX_SERVERS_REQUESTED,
  794.                                   WA.PX_SERVERS_ALLOCATED,
  795.                                   WA.PX_MAXDOP,
  796.                                   WA.ELAPSED_TIME_S ELAPSED_TIME_S,
  797.                                   WA.CPU_TIME CPU_TIME,
  798.                                   WA.BUFFER_GETS,
  799.                                   WA.PHYSICAL_READ_BYTES,
  800.                                   WA.PHYSICAL_WRITE_BYTES,
  801.                                   WA.USER_IO_WAIT_TIME USER_IO_WAIT_TIME,
  802.                                   NVL((SELECT NS.SQL_TEXT
  803.                                         FROM GV$SQLAREA NS
  804.                                        WHERE NS.SQL_ID = WB.SQL_ID
  805.                                          AND NS.INST_ID = WB.INST_ID),
  806.                                       WA.SQL_TEXT) SQL_TEXT,
  807.                                   WB.LOGON_TIME,
  808.                                   WB.SQL_EXEC_ID,
  809.                                   WB.EVENT,
  810.                                   WB.BLOCKING_INSTANCE BLOCKING_INSTANCE,
  811.                                   WB.BLOCKING_SESSION BLOCKING_SESSION,
  812.                                   WB.BLOCKING_SESSION_SERIAL# BLOCKING_SESSION_SERIAL#,
  813.                                   WB.TADDR,
  814.                                   WB.SADDR,
  815.                                   WB.LAST_CALL_ET,
  816.                                   (WB.SESSION_INFO || '--' || WB.SESSION_TYPE || '--' ||
  817.                                   WB.MACHINE) SESSION_INFO,
  818.                                   (SELECT NS.EXECUTIONS
  819.                                      FROM GV$SQLAREA NS
  820.                                     WHERE NS.SQL_ID = WB.SQL_ID
  821.                                       AND NS.INST_ID = WB.INST_ID) EXECUTIONS,
  822.                                   'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' ||
  823.                                   WB.SQL_ID || ''',' || WB.SQL_CHILD_NUMBER ||
  824.                                   ',''advanced''));' SQL_PLAN,
  825.                                   WB.ASH_COUNTS,
  826.                                   WB.SESSION_STATE
  827.                              FROM (SELECT A.INST_ID,
  828.                                           A.SID,
  829.                                           A.PLAN_DEPTH,
  830.                                           A.PLAN_OPERATION PLAN_OPERATION,
  831.                                           A.PLAN_OPTIONS,
  832.                                           A.PLAN_PARTITION_START,
  833.                                           A.PLAN_PARTITION_STOP,
  834.                                           A.STARTS,
  835.                                           MAX(A.PLAN_COST) OVER(PARTITION BY A.INST_ID, A.SID, A.KEY, A.SQL_EXEC_ID, A.SQL_ID) AS PLAN_COST,
  836.                                           MAX(A.PLAN_CARDINALITY) OVER(PARTITION BY A.INST_ID, A.SID, A.KEY, A.SQL_EXEC_ID, A.SQL_ID) AS PLAN_CARDINALITY,
  837.                                           A.SQL_ID,
  838.                                           A.SQL_EXEC_START,
  839.                                           B.PX_SERVERS_REQUESTED,
  840.                                           B.PX_SERVERS_ALLOCATED,
  841.                                           B.PX_MAXDOP,
  842.                                           (B.ELAPSED_TIME / 1000000) ELAPSED_TIME_S,
  843.                                           (B.CPU_TIME / 1000000) CPU_TIME,
  844.                                           B.BUFFER_GETS,
  845.                                           B.PHYSICAL_READ_BYTES,
  846.                                           B.PHYSICAL_WRITE_BYTES,
  847.                                           (B.USER_IO_WAIT_TIME / 1000000) USER_IO_WAIT_TIME,
  848.                                           B.SQL_TEXT SQL_TEXT,
  849.                                           (B.MODULE || '--' || B.ACTION || '--' ||
  850.                                           B.PROGRAM || '--' || B.PROCESS_NAME || '--' ||
  851.                                           B.CLIENT_IDENTIFIER || '--' ||
  852.                                           B.CLIENT_INFO || '--' ||
  853.                                           B.SERVICE_NAME) SESSION_INFO,
  854.                                           A.SQL_EXEC_ID
  855.                                      FROM GV$SQL_PLAN_MONITOR A,
  856.                                           GV$SQL_MONITOR B
  857.                                     WHERE A.SID = B.SID
  858.                                       AND A.KEY = B.KEY
  859.                                       AND A.INST_ID = B.INST_ID
  860.                                       AND A.SQL_EXEC_ID = B.SQL_EXEC_ID
  861.                                       AND A.STATUS IN
  862.                                           ('EXECUTING', 'DONE(ERROR)')
  863.                                       AND B.STATUS IN
  864.                                           ('EXECUTING', 'DONE(ERROR)')
  865.                                       AND B.PROCESS_NAME NOT LIKE 'p%') WA
  866.                             RIGHT OUTER JOIN (SELECT ASH.INST_ID,
  867.                                                     ASH.SESSION_ID SID,
  868.                                                     ASH.SESSION_SERIAL# SERIAL#,
  869.                                                     (SELECT PR.SPID
  870.                                                        FROM GV$PROCESS PR
  871.                                                       WHERE GVS.PADDR =
  872.                                                             PR.ADDR
  873.                                                         AND PR.INST_ID =
  874.                                                             ASH.INST_ID) SPID,
  875.                                                     ASH.SESSION_TYPE,
  876.                                                     ASH.USER_ID,
  877.                                                     ASH.SQL_ID,
  878.                                                     ASH.SQL_CHILD_NUMBER,
  879.                                                     ASH.SQL_OPNAME,
  880.                                                     ASH.SQL_EXEC_ID,
  881.                                                     NVL(ASH.EVENT, GVS.EVENT) EVENT,
  882.                                                     ASH.SESSION_STATE,
  883.                                                     ASH.BLOCKING_SESSION,
  884.                                                     ASH.BLOCKING_SESSION_SERIAL#,
  885.                                                     ASH.BLOCKING_INST_ID BLOCKING_INSTANCE,
  886.                                                     ASH.CLIENT_ID,
  887.                                                     ASH.MACHINE,
  888.                                                     GVS.LAST_CALL_ET,
  889.                                                     GVS.TADDR,
  890.                                                     GVS.SADDR,
  891.                                                     GVS.LOGON_TIME,
  892.                                                     GVS.USERNAME,
  893.                                                     GVS.OSUSER,
  894.                                                     GVS.SQL_EXEC_START,
  895.                                                     (GVS.MODULE || '--' ||
  896.                                                     GVS.ACTION || '--' ||
  897.                                                     GVS.PROGRAM || '--' ||
  898.                                                     GVS.PROCESS || '--' ||
  899.                                                     GVS.CLIENT_IDENTIFIER || '--' ||
  900.                                                     GVS.CLIENT_INFO || '--' ||
  901.                                                     GVS.SERVICE_NAME) SESSION_INFO,
  902.                                                     COUNT(*) ASH_COUNTS
  903.                                                FROM GV$ACTIVE_SESSION_HISTORY ASH,
  904.                                                     GV$SESSION GVS
  905.                                               WHERE ASH.INST_ID = GVS.INST_ID
  906.                                                 AND GVS.SQL_ID = ASH.SQL_ID
  907.                                                 AND GVS.SQL_EXEC_ID =
  908.                                                     ASH.SQL_EXEC_ID
  909.                                                 AND ASH.SESSION_ID = GVS.SID
  910.                                                 AND ASH.SESSION_SERIAL# =
  911.                                                     GVS.SERIAL#
  912.                                                 AND GVS.STATUS = 'ACTIVE'
  913.                                                 AND ASH.SQL_ID IS NOT NULL
  914.                                               GROUP BY ASH.INST_ID,
  915.                                                        ASH.SESSION_ID,
  916.                                                        ASH.SESSION_SERIAL#,
  917.                                                        ASH.SESSION_TYPE,
  918.                                                        ASH.USER_ID,
  919.                                                        ASH.SQL_ID,
  920.                                                        ASH.SQL_CHILD_NUMBER,
  921.                                                        ASH.SQL_OPNAME,
  922.                                                        ASH.SQL_EXEC_ID,
  923.                                                        NVL(ASH.EVENT,
  924.                                                            GVS.EVENT),
  925.                                                        ASH.SESSION_STATE,
  926.                                                        ASH.BLOCKING_SESSION,
  927.                                                        ASH.BLOCKING_SESSION_SERIAL#,
  928.                                                        ASH.BLOCKING_INST_ID,
  929.                                                        ASH.CLIENT_ID,
  930.                                                        ASH.MACHINE,
  931.                                                        GVS.LAST_CALL_ET,
  932.                                                        GVS.TADDR,
  933.                                                        GVS.SADDR,
  934.                                                        GVS.LOGON_TIME,
  935.                                                        GVS.USERNAME,
  936.                                                        GVS.OSUSER,
  937.                                                        GVS.PADDR,
  938.                                                        (GVS.MODULE || '--' ||
  939.                                                        GVS.ACTION || '--' ||
  940.                                                        GVS.PROGRAM || '--' ||
  941.                                                        GVS.PROCESS || '--' ||
  942.                                                        GVS.CLIENT_IDENTIFIER || '--' ||
  943.                                                        GVS.CLIENT_INFO || '--' ||
  944.                                                        GVS.SERVICE_NAME),
  945.                                                        GVS.SQL_EXEC_START
  946.                                              HAVING COUNT(*) > 6) WB
  947.                                ON (WB.SID = WA.SID AND
  948.                                   WB.INST_ID = WA.INST_ID AND
  949.                                   WB.SQL_ID = WA.SQL_ID AND
  950.                                   WB.SQL_EXEC_ID = WA.SQL_EXEC_ID))
  951.              ------------------------------------------ 笛卡尔积
  952.                SELECT DISTINCT T.INST_ID,
  953.                                T.SID,
  954.                                T.SERIAL#,
  955.                                T.SPID,
  956.                                T.OSUSER,
  957.                                T.USERNAME,
  958.                                T.EVENT,
  959.                                T.SESSION_STATE,
  960.                                T.SQL_TEXT,
  961.                                T.EXECUTIONS,
  962.                                T.ELAPSED_TIME_S,
  963.                                T.CPU_TIME,
  964.                                T.USER_IO_WAIT_TIME,
  965.                                T.BUFFER_GETS,
  966.                                T.PLAN_OPERATION,
  967.                                T.STARTS,
  968.                                T.PLAN_PARTITION_START,
  969.                                T.PLAN_PARTITION_STOP,
  970.                                T.PHYSICAL_READ_BYTES,
  971.                                T.PHYSICAL_WRITE_BYTES,
  972.                                T.BLOCKING_INSTANCE,
  973.                                T.BLOCKING_SESSION,
  974.                                T.BLOCKING_SESSION_SERIAL#,
  975.                                T.LAST_CALL_ET,
  976.                                T.SQL_ID,
  977.                                T.SQL_EXEC_START,
  978.                                T.SQL_PLAN,
  979.                                T.LOGON_TIME,
  980.                                T.ASH_COUNTS,
  981.                                T.SESSION_INFO,
  982.                                '笛卡尔积【' || COUNT(*) OVER(PARTITION BY T.INST_ID, T.SID, T.SERIAL#, T.SQL_ID) || '】个' MONITOR_TYPES
  983.                  FROM TMPS T
  984.                 WHERE T.PLAN_OPERATION = 'MERGE JOIN'
  985.                   AND T.PLAN_OPTIONS = 'CARTESIAN'
  986.                   AND T.USERNAME NOT IN ('SYS')

  988.                UNION ALL

  990.                ------------------------------------------ SQL执行时间过大
  991.                SELECT T.INST_ID,
  992.                       T.SID,
  993.                       T.SERIAL#,
  994.                       T.SPID,
  995.                       T.OSUSER,
  996.                       T.USERNAME,
  997.                       T.EVENT,
  998.                       T.SESSION_STATE,
  999.                       T.SQL_TEXT,
  1000.                       T.EXECUTIONS,
  1001.                       T.ELAPSED_TIME_S,
  1002.                       T.CPU_TIME,
  1003.                       T.USER_IO_WAIT_TIME,
  1004.                       T.BUFFER_GETS,
  1005.                       T.PLAN_OPERATION,
  1006.                       T.STARTS,
  1007.                       T.PLAN_PARTITION_START,
  1008.                       T.PLAN_PARTITION_STOP,
  1009.                       T.PHYSICAL_READ_BYTES,
  1010.                       T.PHYSICAL_WRITE_BYTES,
  1011.                       T.BLOCKING_INSTANCE,
  1012.                       T.BLOCKING_SESSION,
  1013.                       T.BLOCKING_SESSION_SERIAL#,
  1014.                       T.LAST_CALL_ET,
  1015.                       T.SQL_ID,
  1016.                       T.SQL_EXEC_START,
  1017.                       T.SQL_PLAN,
  1018.                       T.LOGON_TIME,
  1019.                       T.ASH_COUNTS,
  1020.                       T.SESSION_INFO,
  1021.                       '执行时间过大' MONITOR_TYPES
  1022.                  FROM TMPS T
  1023.                 WHERE T.ELAPSED_TIME_S > V_ELAPSED_TIME --5 * 60 * 60
  1024.                   AND (NVL(PLAN_DEPTH, 1) = 1)

  1026.                UNION ALL

  1028.                ------------------------------------------ 分区表全分区扫描

  1030.                SELECT T.INST_ID,
  1031.                       T.SID,
  1032.                       T.SERIAL#,
  1033.                       T.SPID,
  1034.                       T.OSUSER,
  1035.                       T.USERNAME,
  1036.                       T.EVENT,
  1037.                       T.SESSION_STATE,
  1038.                       T.SQL_TEXT,
  1039.                       T.EXECUTIONS,
  1040.                       T.ELAPSED_TIME_S,
  1041.                       T.CPU_TIME,
  1042.                       T.USER_IO_WAIT_TIME,
  1043.                       T.BUFFER_GETS,
  1044.                       T.PLAN_OPERATION,
  1045.                       T.STARTS,
  1046.                       T.PLAN_PARTITION_START,
  1047.                       T.PLAN_PARTITION_STOP,
  1048.                       T.PHYSICAL_READ_BYTES,
  1049.                       T.PHYSICAL_WRITE_BYTES,
  1050.                       T.BLOCKING_INSTANCE,
  1051.                       T.BLOCKING_SESSION,
  1052.                       T.BLOCKING_SESSION_SERIAL#,
  1053.                       T.LAST_CALL_ET,
  1054.                       T.SQL_ID,
  1055.                       T.SQL_EXEC_START,
  1056.                       T.SQL_PLAN,
  1057.                       T.LOGON_TIME,
  1058.                       T.ASH_COUNTS,
  1059.                       T.SESSION_INFO,
  1060.                       '分区表全分区扫描' MONITOR_TYPES
  1061.                  FROM TMPS T
  1062.                 WHERE T.PLAN_OPERATION LIKE 'PARTITION%'
  1063.                   AND T.PLAN_OPTIONS = 'ALL'
  1064.                -- AND T.ELAPSED_TIME_S >= 0.5 * 60 * 60

  1066.                UNION ALL

  1068.                ------------------------------------------ 执行计划中COST花费超大

  1070.                SELECT T.INST_ID,
  1071.                       T.SID,
  1072.                       T.SERIAL#,
  1073.                       T.SPID,
  1074.                       T.OSUSER,
  1075.                       T.USERNAME,
  1076.                       T.EVENT,
  1077.                       T.SESSION_STATE,
  1078.                       T.SQL_TEXT,
  1079.                       T.EXECUTIONS,
  1080.                       T.ELAPSED_TIME_S,
  1081.                       T.CPU_TIME,
  1082.                       T.USER_IO_WAIT_TIME,
  1083.                       T.BUFFER_GETS,
  1084.                       T.PLAN_OPERATION,
  1085.                       T.STARTS,
  1086.                       T.PLAN_PARTITION_START,
  1087.                       T.PLAN_PARTITION_STOP,
  1088.                       T.PHYSICAL_READ_BYTES,
  1089.                       T.PHYSICAL_WRITE_BYTES,
  1090.                       T.BLOCKING_INSTANCE,
  1091.                       T.BLOCKING_SESSION,
  1092.                       T.BLOCKING_SESSION_SERIAL#,
  1093.                       T.LAST_CALL_ET,
  1094.                       T.SQL_ID,
  1095.                       T.SQL_EXEC_START,
  1096.                       T.SQL_PLAN,
  1097.                       T.LOGON_TIME,
  1098.                       T.ASH_COUNTS,
  1099.                       T.SESSION_INFO,
  1100.                       '执行计划中COST花费超大【' || T.PLAN_COST || '】' MONITOR_TYPES
  1101.                  FROM TMPS T
  1102.                 WHERE T.PLAN_COST >= V_PLAN_COST
  1103.                   AND (NVL(PLAN_DEPTH, 1) = 1)
  1104.                            
  1105.                UNION ALL
  1106.                ------------------------------------------ 执行计划中预估行数超大

  1108.                SELECT T.INST_ID,
  1109.                       T.SID,
  1110.                       T.SERIAL#,
  1111.                       T.SPID,
  1112.                       T.OSUSER,
  1113.                       T.USERNAME,
  1114.                       T.EVENT,
  1115.                       T.SESSION_STATE,
  1116.                       T.SQL_TEXT,
  1117.                       T.EXECUTIONS,
  1118.                       T.ELAPSED_TIME_S,
  1119.                       T.CPU_TIME,
  1120.                       T.USER_IO_WAIT_TIME,
  1121.                       T.BUFFER_GETS,
  1122.                       T.PLAN_OPERATION,
  1123.                       T.STARTS,
  1124.                       T.PLAN_PARTITION_START,
  1125.                       T.PLAN_PARTITION_STOP,
  1126.                       T.PHYSICAL_READ_BYTES,
  1127.                       T.PHYSICAL_WRITE_BYTES,
  1128.                       T.BLOCKING_INSTANCE,
  1129.                       T.BLOCKING_SESSION,
  1130.                       T.BLOCKING_SESSION_SERIAL#,
  1131.                       T.LAST_CALL_ET,
  1132.                       T.SQL_ID,
  1133.                       T.SQL_EXEC_START,
  1134.                       T.SQL_PLAN,
  1135.                       T.LOGON_TIME,
  1136.                       T.ASH_COUNTS,
  1137.                       T.SESSION_INFO,
  1138.                       '执行计划中预估行数超大【' || T.PLAN_CARDINALITY || '】' MONITOR_TYPES
  1139.                  FROM TMPS T
  1140.                 WHERE T.PLAN_CARDINALITY > V_PLAN_CARDINALITY
  1141.                   AND (NVL(PLAN_DEPTH, 1) = 1)
  1142.                                       
  1143.                UNION ALL
  1144.                ------------------------------------------ SQL请求并行数过多

  1146.                SELECT T.INST_ID,
  1147.                       T.SID,
  1148.                       T.SERIAL#,
  1149.                       T.SPID,
  1150.                       T.OSUSER,
  1151.                       T.USERNAME,
  1152.                       T.EVENT,
  1153.                       T.SESSION_STATE,
  1154.                       T.SQL_TEXT,
  1155.                       T.EXECUTIONS,
  1156.                       T.ELAPSED_TIME_S,
  1157.                       T.CPU_TIME,
  1158.                       T.USER_IO_WAIT_TIME,
  1159.                       T.BUFFER_GETS,
  1160.                       T.PLAN_OPERATION,
  1161.                       T.STARTS,
  1162.                       T.PLAN_PARTITION_START,
  1163.                       T.PLAN_PARTITION_STOP,
  1164.                       T.PHYSICAL_READ_BYTES,
  1165.                       T.PHYSICAL_WRITE_BYTES,
  1166.                       T.BLOCKING_INSTANCE,
  1167.                       T.BLOCKING_SESSION,
  1168.                       T.BLOCKING_SESSION_SERIAL#,
  1169.                       T.LAST_CALL_ET,
  1170.                       T.SQL_ID,
  1171.                       T.SQL_EXEC_START,
  1172.                       T.SQL_PLAN,
  1173.                       T.LOGON_TIME,
  1174.                       T.ASH_COUNTS,
  1175.                       T.SESSION_INFO,
  1176.                       'SQL请求并行数过多【' || PX_MAXDOP || '】' MONITOR_TYPES
  1177.                  FROM TMPS T
  1178.                 WHERE T.PX_MAXDOP >= V_PARALLEL
  1179.                   AND (NVL(PLAN_DEPTH, 1) = 1)
  1180.                       
  1181.                UNION ALL
  1182.                ------------------------------------------ 系统预估的剩余执行时间过长

  1184.                SELECT T.INST_ID,
  1185.                       T.SID,
  1186.                       T.SERIAL#,
  1187.                       T.SPID,
  1188.                       T.OSUSER,
  1189.                       T.USERNAME,
  1190.                       T.EVENT,
  1191.                       T.SESSION_STATE,
  1192.                       T.SQL_TEXT,
  1193.                       T.EXECUTIONS,
  1194.                       T.ELAPSED_TIME_S,
  1195.                       T.CPU_TIME,
  1196.                       T.USER_IO_WAIT_TIME,
  1197.                       T.BUFFER_GETS,
  1198.                       T.PLAN_OPERATION,
  1199.                       T.STARTS,
  1200.                       T.PLAN_PARTITION_START,
  1201.                       T.PLAN_PARTITION_STOP,
  1202.                       T.PHYSICAL_READ_BYTES,
  1203.                       T.PHYSICAL_WRITE_BYTES,
  1204.                       T.BLOCKING_INSTANCE,
  1205.                       T.BLOCKING_SESSION,
  1206.                       T.BLOCKING_SESSION_SERIAL#,
  1207.                       T.LAST_CALL_ET,
  1208.                       T.SQL_ID,
  1209.                       T.SQL_EXEC_START,
  1210.                       T.SQL_PLAN,
  1211.                       T.LOGON_TIME,
  1212.                       T.ASH_COUNTS,
  1213.                       T.SESSION_INFO,
  1214.                       '系统预估的剩余执行时间过长【' || ROUND(D.TIME_REMAINING) || '】' MONITOR_TYPES
  1215.                  FROM TMPS T, GV$SESSION_LONGOPS D
  1216.                 WHERE T.SQL_EXEC_ID = D.SQL_EXEC_ID
  1217.                   AND T.SID = D.SID
  1218.                   AND T.SERIAL# = D.SERIAL#
  1219.                   AND D.TIME_REMAINING > V_ESTIMATE_TIME
  1220.                   AND T.INST_ID = D.INST_ID
  1221.                   AND D.TIME_REMAINING > 0
  1222.                   AND (NVL(PLAN_DEPTH, 1) = 1)
  1223.                                     
  1224.                UNION ALL
  1225.                ------------------------------------------ 等待事件异常

  1227.                SELECT T.INST_ID,
  1228.                       T.SID,
  1229.                       T.SERIAL#,
  1230.                       T.SPID,
  1231.                       T.OSUSER,
  1232.                       T.USERNAME,
  1233.                       T.EVENT,
  1234.                       T.SESSION_STATE,
  1235.                       T.SQL_TEXT,
  1236.                       T.EXECUTIONS,
  1237.                       T.ELAPSED_TIME_S,
  1238.                       T.CPU_TIME,
  1239.                       T.USER_IO_WAIT_TIME,
  1240.                       T.BUFFER_GETS,
  1241.                       T.PLAN_OPERATION,
  1242.                       T.STARTS,
  1243.                       T.PLAN_PARTITION_START,
  1244.                       T.PLAN_PARTITION_STOP,
  1245.                       T.PHYSICAL_READ_BYTES,
  1246.                       T.PHYSICAL_WRITE_BYTES,
  1247.                       T.BLOCKING_INSTANCE,
  1248.                       T.BLOCKING_SESSION,
  1249.                       T.BLOCKING_SESSION_SERIAL#,
  1250.                       T.LAST_CALL_ET,
  1251.                       T.SQL_ID,
  1252.                       T.SQL_EXEC_START,
  1253.                       T.SQL_PLAN,
  1254.                       T.LOGON_TIME,
  1255.                       T.ASH_COUNTS,
  1256.                       T.SESSION_INFO,
  1257.                       '等待事件异常【' || T.EVENT || '】' MONITOR_TYPES
  1258.                  FROM TMPS T
  1259.                 WHERE T.EVENT NOT IN
  1260.                       ('db file sequential read',
  1261.                        'db file scattered read',
  1262.                        'db file parallel write',
  1263.                        'db file parallel read')
  1264.                   AND (NVL(PLAN_DEPTH, 1) = 1)

  1266.                UNION ALL
  1267.                ------------------------------------------ TMP表空间占用过大

  1269.                SELECT T.INST_ID,
  1270.                       T.SID,
  1271.                       T.SERIAL#,
  1272.                       T.SPID,
  1273.                       T.OSUSER,
  1274.                       T.USERNAME,
  1275.                       T.EVENT,
  1276.                       T.SESSION_STATE,
  1277.                       T.SQL_TEXT,
  1278.                       T.EXECUTIONS,
  1279.                       T.ELAPSED_TIME_S,
  1280.                       T.CPU_TIME,
  1281.                       T.USER_IO_WAIT_TIME,
  1282.                       T.BUFFER_GETS,
  1283.                       T.PLAN_OPERATION,
  1284.                       T.STARTS,
  1285.                       T.PLAN_PARTITION_START,
  1286.                       T.PLAN_PARTITION_STOP,
  1287.                       T.PHYSICAL_READ_BYTES,
  1288.                       T.PHYSICAL_WRITE_BYTES,
  1289.                       T.BLOCKING_INSTANCE,
  1290.                       T.BLOCKING_SESSION,
  1291.                       T.BLOCKING_SESSION_SERIAL#,
  1292.                       T.LAST_CALL_ET,
  1293.                       T.SQL_ID,
  1294.                       T.SQL_EXEC_START,
  1295.                       T.SQL_PLAN,
  1296.                       T.LOGON_TIME,
  1297.                       T.ASH_COUNTS,
  1298.                       T.SESSION_INFO,
  1299.                       'SQL占用TMP表空间过大【' || C.BYTES || '】Bytes' MONITOR_TYPES
  1300.                  FROM TMPS T,
  1301.                       (SELECT A.INST_ID,
  1302.                               A.SESSION_ADDR,
  1303.                               SUM(A.BLOCKS) * 8 * 1024 BYTES
  1304.                          FROM GV$TEMPSEG_USAGE A
  1305.                         GROUP BY A.INST_ID, A.SESSION_ADDR) C
  1306.                 WHERE C.SESSION_ADDR = T.SADDR
  1307.                   AND C.INST_ID = T.INST_ID
  1308.                   AND C.BYTES > V_TMPSIZE --50 * 1024 * 1024 * 1024
  1309.                   AND (NVL(PLAN_DEPTH, 1) = 1)
  1310.                            
  1311.         
  1312.                UNION ALL
  1313.                ----------------------------------------- SQL占用UNDO过大，INACTIVE的会话也可能占用UNDO，但是这里只记录正在运行的SQL语句

  1315.                SELECT T.INST_ID,
  1316.                       T.SID,
  1317.                       T.SERIAL#,
  1318.                       T.SPID,
  1319.                       T.OSUSER,
  1320.                       T.USERNAME,
  1321.                       T.EVENT,
  1322.                       T.SESSION_STATE,
  1323.                       T.SQL_TEXT,
  1324.                       T.EXECUTIONS,
  1325.                       T.ELAPSED_TIME_S,
  1326.                       T.CPU_TIME,
  1327.                       T.USER_IO_WAIT_TIME,
  1328.                       T.BUFFER_GETS,
  1329.                       T.PLAN_OPERATION,
  1330.                       T.STARTS,
  1331.                       T.PLAN_PARTITION_START,
  1332.                       T.PLAN_PARTITION_STOP,
  1333.                       T.PHYSICAL_READ_BYTES,
  1334.                       T.PHYSICAL_WRITE_BYTES,
  1335.                       T.BLOCKING_INSTANCE,
  1336.                       T.BLOCKING_SESSION,
  1337.                       T.BLOCKING_SESSION_SERIAL#,
  1338.                       T.LAST_CALL_ET,
  1339.                       T.SQL_ID,
  1340.                       T.SQL_EXEC_START,
  1341.                       T.SQL_PLAN,
  1342.                       T.LOGON_TIME,
  1343.                       T.ASH_COUNTS,
  1344.                       T.SESSION_INFO,
  1345.                       'SQL占用UNDO过大【' || USED_SIZE_BYTES || '】Bytes' MONITOR_TYPES
  1346.                  FROM TMPS T,
  1347.                       (SELECT ST.ADDR,
  1348.                               ST.INST_ID,
  1349.                               (ST.USED_UBLK * 8 * 1024) USED_SIZE_BYTES
  1350.                          FROM GV$TRANSACTION ST, V$ROLLNAME R, GV$ROLLSTAT G
  1351.                         WHERE ST.XIDUSN = R.USN
  1352.                           AND R.USN = G.USN
  1353.                           AND G.INST_ID = ST.INST_ID) V1
  1354.                 WHERE V1.ADDR = T.TADDR
  1355.                   AND T.INST_ID = V1.INST_ID
  1356.                   AND USED_SIZE_BYTES > V_UNDOSIZE -- 50 * 1024 * 1024 * 1024
  1357.                  
  1358.                UNION ALL
  1359.                ----------------------------------------- 耗费性能SQL

  1361.                SELECT T.INST_ID,
  1362.                       T.SID,
  1363.                       T.SERIAL#,
  1364.                       T.SPID,
  1365.                       T.OSUSER,
  1366.                       T.USERNAME,
  1367.                       T.EVENT,
  1368.                       T.SESSION_STATE,
  1369.                       T.SQL_TEXT,
  1370.                       T.EXECUTIONS,
  1371.                       T.ELAPSED_TIME_S,
  1372.                       T.CPU_TIME,
  1373.                       T.USER_IO_WAIT_TIME,
  1374.                       T.BUFFER_GETS,
  1375.                       T.PLAN_OPERATION,
  1376.                       T.STARTS,
  1377.                       T.PLAN_PARTITION_START,
  1378.                       T.PLAN_PARTITION_STOP,
  1379.                       T.PHYSICAL_READ_BYTES,
  1380.                       T.PHYSICAL_WRITE_BYTES,
  1381.                       T.BLOCKING_INSTANCE,
  1382.                       T.BLOCKING_SESSION,
  1383.                       T.BLOCKING_SESSION_SERIAL#,
  1384.                       T.LAST_CALL_ET,
  1385.                       T.SQL_ID,
  1386.                       T.SQL_EXEC_START,
  1387.                       T.SQL_PLAN,
  1388.                       T.LOGON_TIME,
  1389.                       T.ASH_COUNTS,
  1390.                       T.SESSION_INFO,
  1391.                       'ASH捕获的次数【' || T.ASH_COUNTS || '】【' || SESSION_STATE || '】' MONITOR_TYPES
  1392.                  FROM TMPS T
  1393.                 WHERE T.ASH_COUNTS >= 4
  1394.                   AND (NVL(PLAN_DEPTH, 1) = 1)

  1396.                 ) T;


  1399.     COMMIT;
  1400.     /*
  1401.     EXCEPTION
  1402.       WHEN OTHERS THEN
  1403.         COMMIT;*/

  1405.   END P_SQL_EPP_LHR;

  1407.   PROCEDURE P_SQL_EPP2_LHR AS
  1408.     -----------------------------------------------------------------------------------
  1409.     -- Created on 2015/11/21 16:49:28 by lhr
  1410.     --Changed on 2017/04/03 16:49:28 by lhr
  1411.     -- function： 监控已经执行完毕的SQL
  1412.     -----------------------------------------------------------------------------------

  1414.     V_TMPSIZE NUMBER := 5368709120; --bytes
  1415.     V_UNDOSIZE NUMBER := 5368709120; --bytes
  1416.     V_PLAN_COST NUMBER := 3107523095; --cost 花费
  1417.     V_PLAN_CARDINALITY NUMBER := 30748908521460; --预估行数
  1418.     V_ELAPSED_TIME NUMBER := 10000000; ---执行时间，单位：微秒，例执行时间设置5小时，则： 5h*60*60*1000000 ，1秒等于1000000微秒
  1419.     V_EXECUTIONS NUMBER := 1000; --执行次数
  1420.     V_PARALLEL NUMBER := 4; --SQL开并行的个数
  1421.     V_ESTIMATE_TIME NUMBER := 900; --系统预估的执行时间
  1422.     V_LOGICAL_READS NUMBER := 5368709120; --bytes
  1423.     V_DISK_READS NUMBER := 5368709120; --bytes

  1425.   BEGIN

  1427.     SELECT SUM(CASE T.PARAM_NAME
  1428.                  WHEN 'V_UNDOSIZE' THEN
  1429.                   T.PARAM_VALUE
  1430.                END) V_UNDOSIZE,
  1431.            SUM(CASE T.PARAM_NAME
  1432.                  WHEN 'V_TMPSIZE' THEN
  1433.                   T.PARAM_VALUE
  1434.                END) V_TMPSIZE,
  1435.            SUM(CASE T.PARAM_NAME
  1436.                  WHEN 'V_PLAN_COST' THEN
  1437.                   T.PARAM_VALUE
  1438.                END) V_PLAN_COST,
  1439.            SUM(CASE T.PARAM_NAME
  1440.                  WHEN 'V_PLAN_CARDINALITY' THEN
  1441.                   T.PARAM_VALUE
  1442.                END) V_PLAN_CARDINALITY,
  1443.            SUM(CASE T.PARAM_NAME
  1444.                  WHEN 'V_ELAPSED_TIME' THEN
  1445.                   T.PARAM_VALUE
  1446.                END) V_ELAPSED_TIME,
  1447.            SUM(CASE T.PARAM_NAME
  1448.                  WHEN 'V_EXECUTIONS' THEN
  1449.                   T.PARAM_VALUE
  1450.                END) V_EXECUTIONS,
  1451.            SUM(CASE T.PARAM_NAME
  1452.                  WHEN 'V_PARALLEL' THEN
  1453.                   T.PARAM_VALUE
  1454.                END) V_PARALLEL,
  1455.            SUM(CASE T.PARAM_NAME
  1456.                  WHEN 'V_ESTIMATE_TIME' THEN
  1457.                   T.PARAM_VALUE
  1458.                END) V_ESTIMATE_TIME,
  1459.            SUM(CASE T.PARAM_NAME
  1460.                  WHEN 'V_LOGICAL_READS' THEN
  1461.                   T.PARAM_VALUE
  1462.                END) V_UNDOSIZE,
  1463.            SUM(CASE T.PARAM_NAME
  1464.                  WHEN 'V_DISK_READS' THEN
  1465.                   T.PARAM_VALUE
  1466.                END) V_DISK_READS
  1467.       INTO V_UNDOSIZE,
  1468.            V_TMPSIZE,
  1469.            V_PLAN_COST,
  1470.            V_PLAN_CARDINALITY,
  1471.            V_ELAPSED_TIME,
  1472.            V_EXECUTIONS,
  1473.            V_PARALLEL,
  1474.            V_ESTIMATE_TIME,
  1475.            V_LOGICAL_READS,
  1476.            V_DISK_READS
  1477.       FROM XB_SQL_PARAMETERS_LHR T
  1478.      WHERE T.PARAM_NAME IN ('V_LOGICAL_READS',
  1479.                             'V_DISK_READS',
  1480.                             'V_UNDOSIZE',
  1481.                             'V_TMPSIZE',
  1482.                             'V_PLAN_COST',
  1483.                             'V_PLAN_CARDINALITY',
  1484.                             'V_ELAPSED_TIME',
  1485.                             'V_EXECUTIONS',
  1486.                             'V_PARALLEL',
  1487.                             'V_ESTIMATE_TIME');

  1489.     INSERT INTO XB_SQL_MONITOR_PP_LHR
  1490.       (ID,
  1491.        INST_ID,
  1492.        SID,
  1493.        SERIAL#,
  1494.        USERNAME,
  1495.        SQL_TEXT,
  1496.        EXECUTIONS,
  1497.        SQL_ID,
  1498.        SQL_EXEC_START,
  1499.        LAST_LOAD_TIME,
  1500.        LAST_ACTIVE_TIME,
  1501.        ELAPSED_TIME,
  1502.        ELAPSED_TIME1,
  1503.        MONITOR_TYPES,
  1504.        MONITOR_TYPES1,
  1505.        MONITOR_VALUE,
  1506.        SESSION_INFO,
  1507.        SESSION_STATE,
  1508.        EVENT,
  1509.        CPU_TIME,
  1510.        BUFFER_GETS,
  1511.        PHYSICAL_READ_BYTES,
  1512.        PHYSICAL_WRITE_BYTES,
  1513.        BLOCKING_INSTANCE,
  1514.        BLOCKING_SESSION,
  1515.        ASH_COUNTS,
  1516.        IN_DATE)
  1517.       SELECT S_XB_SQL_MONITOR_PP_LHR.NEXTVAL,
  1518.              INST_ID,
  1519.              SID,
  1520.              SERIAL#,
  1521.              USERNAME,
  1522.              SQL_TEXT,
  1523.              EXECUTIONS,
  1524.              SQL_ID,
  1525.              SQL_EXEC_START,
  1526.              '' LAST_LOAD_TIME,
  1527.              '' LAST_ACTIVE_TIME,
  1528.              F_GET_TOTAL_TIME_LHR(ELAPSED_TIME_S),
  1529.              ELAPSED_TIME_S,
  1530.              MONITOR_TYPES,
  1531.              '' MONITOR_TYPES1,
  1532.              '' MONITOR_VALUE,
  1533.              SESSION_INFO,
  1534.              SESSION_STATE,
  1535.              EVENT,
  1536.              CPU_TIME,
  1537.              BUFFER_GETS,
  1538.              PHYSICAL_READ_BYTES,
  1539.              PHYSICAL_WRITE_BYTES,
  1540.              BLOCKING_INSTANCE,
  1541.              BLOCKING_SESSION,
  1542.              ASH_COUNTS,
  1543.              SYSDATE IN_DATE
  1544.         FROM (

  1546.              WITH TMPA AS (SELECT ASH.INST_ID,
  1547.                                   ASH.SESSION_ID SID,
  1548.                                   ASH.SESSION_SERIAL# SERIAL#,
  1549.                                   ASH.SESSION_TYPE,
  1550.                                   ASH.USER_ID,
  1551.                                   ASH.SQL_ID,
  1552.                                   ASH.SQL_CHILD_NUMBER,
  1553.                                   ASH.SQL_OPNAME,
  1554.                                   ASH.SQL_EXEC_ID,
  1555.                                   ASH.EVENT,
  1556.                                   ASH.SESSION_STATE,
  1557.                                   ASH.BLOCKING_SESSION,
  1558.                                   ASH.BLOCKING_SESSION_SERIAL#,
  1559.                                   ASH.BLOCKING_INST_ID BLOCKING_INSTANCE,
  1560.                                   ASH.CLIENT_ID,
  1561.                                   ASH.MACHINE,
  1562.                                   GVS.PARSING_SCHEMA_NAME USERNAME,
  1563.                                   ASH.SQL_EXEC_START,
  1564.                                   (ASH.MODULE || '--' || ASH.ACTION || '--' ||
  1565.                                   ASH.PROGRAM || '--' || ASH.MACHINE || '--' ||
  1566.                                   ASH.CLIENT_ID || '--' || ASH.SESSION_TYPE) SESSION_INFO,
  1567.                                   COUNT(*) ASH_COUNTS,
  1568.                                   (GVS.ELAPSED_TIME / 1000000) ELAPSED_TIME_S,
  1569.                                   (GVS.CPU_TIME / 1000000) CPU_TIME,
  1570.                                   GVS.EXECUTIONS,
  1571.                                   GVS.DISK_READS,
  1572.                                   GVS.BUFFER_GETS,
  1573.                                   GVS.LAST_ACTIVE_TIME,
  1574.                                   GVS.LAST_LOAD_TIME,
  1575.                                   GVS.PHYSICAL_READ_BYTES,
  1576.                                   GVS.PHYSICAL_WRITE_BYTES,
  1577.                                   GVS.SQL_TEXT
  1578.                              FROM GV$ACTIVE_SESSION_HISTORY ASH, GV$SQL GVS
  1579.                             WHERE ASH.INST_ID = GVS.INST_ID
  1580.                               AND GVS.SQL_ID = ASH.SQL_ID
  1581.                               AND ASH.SQL_ID IS NOT NULL
  1582.                               AND ASH.SAMPLE_TIME > SYSDATE - 30 / 1440
  1583.                             GROUP BY ASH.INST_ID,
  1584.                                      ASH.SESSION_ID,
  1585.                                      ASH.SESSION_SERIAL#,
  1586.                                      ASH.SESSION_TYPE,
  1587.                                      ASH.USER_ID,
  1588.                                      ASH.SQL_ID,
  1589.                                      ASH.SQL_CHILD_NUMBER,
  1590.                                      ASH.SQL_OPNAME,
  1591.                                      ASH.SQL_EXEC_ID,
  1592.                                      ASH.EVENT,
  1593.                                      ASH.SESSION_STATE,
  1594.                                      ASH.BLOCKING_SESSION,
  1595.                                      ASH.BLOCKING_SESSION_SERIAL#,
  1596.                                      ASH.BLOCKING_INST_ID,
  1597.                                      ASH.CLIENT_ID,
  1598.                                      ASH.MACHINE,
  1599.                                      GVS.PARSING_SCHEMA_NAME,
  1600.                                      ASH.SQL_EXEC_START,
  1601.                                      (ASH.MODULE || '--' || ASH.ACTION || '--' ||
  1602.                                      ASH.PROGRAM || '--' || ASH.MACHINE || '--' ||
  1603.                                      ASH.CLIENT_ID || '--' ||
  1604.                                      ASH.SESSION_TYPE),
  1605.                                      (GVS.ELAPSED_TIME / 1000000),
  1606.                                      (GVS.CPU_TIME / 1000000),
  1607.                                      GVS.EXECUTIONS,
  1608.                                      GVS.DISK_READS,
  1609.                                      GVS.BUFFER_GETS,
  1610.                                      GVS.LAST_ACTIVE_TIME,
  1611.                                      GVS.LAST_LOAD_TIME,
  1612.                                      GVS.PHYSICAL_READ_BYTES,
  1613.                                      GVS.PHYSICAL_WRITE_BYTES,
  1614.                                      GVS.SQL_TEXT
  1615.                            HAVING COUNT(*) > 6)

  1617.              ------------------------------------------ 物理读过大
  1618.                SELECT INST_ID,
  1619.                       SID,
  1620.                       SERIAL#,
  1621.                       USERNAME,
  1622.                       SQL_TEXT,
  1623.                       EXECUTIONS,
  1624.                       SQL_ID,
  1625.                       SQL_EXEC_START,
  1626.                       LAST_LOAD_TIME,
  1627.                       LAST_ACTIVE_TIME,
  1628.                       ELAPSED_TIME_S,
  1629.                       SESSION_INFO,
  1630.                       SESSION_STATE,
  1631.                       EVENT,
  1632.                       CPU_TIME,
  1633.                       DISK_READS,
  1634.                       BUFFER_GETS,
  1635.                       PHYSICAL_READ_BYTES,
  1636.                       PHYSICAL_WRITE_BYTES,
  1637.                       BLOCKING_INSTANCE,
  1638.                       BLOCKING_SESSION,
  1639.                       ASH_COUNTS,
  1640.                       '物理读过大' MONITOR_TYPES
  1641.                  FROM TMPA T
  1642.                 WHERE T.DISK_READS > V_DISK_READS
  1643.                      
  1644.                UNION ALL
  1645.                ------------------------------------------ 逻辑读过大
  1646.                SELECT INST_ID,
  1647.                       SID,
  1648.                       SERIAL#,
  1649.                       USERNAME,
  1650.                       SQL_TEXT,
  1651.                       EXECUTIONS,
  1652.                       SQL_ID,
  1653.                       SQL_EXEC_START,
  1654.                       LAST_LOAD_TIME,
  1655.                       LAST_ACTIVE_TIME,
  1656.                       ELAPSED_TIME_S,
  1657.                       SESSION_INFO,
  1658.                       SESSION_STATE,
  1659.                       EVENT,
  1660.                       CPU_TIME,
  1661.                       DISK_READS,
  1662.                       BUFFER_GETS,
  1663.                       PHYSICAL_READ_BYTES,
  1664.                       PHYSICAL_WRITE_BYTES,
  1665.                       BLOCKING_INSTANCE,
  1666.                       BLOCKING_SESSION,
  1667.                       ASH_COUNTS,
  1668.                       '逻辑读过大' MONITOR_TYPES
  1669.                  FROM TMPA T
  1670.                 WHERE T.BUFFER_GETS > V_LOGICAL_READS
  1671.                 
  1672.                UNION ALL
  1673.                ------------------------------------------ 执行次数过大
  1674.                SELECT INST_ID,
  1675.                       SID,
  1676.                       SERIAL#,
  1677.                       USERNAME,
  1678.                       SQL_TEXT,
  1679.                       EXECUTIONS,
  1680.                       SQL_ID,
  1681.                       SQL_EXEC_START,
  1682.                       LAST_LOAD_TIME,
  1683.                       LAST_ACTIVE_TIME,
  1684.                       ELAPSED_TIME_S,
  1685.                       SESSION_INFO,
  1686.                       SESSION_STATE,
  1687.                       EVENT,
  1688.                       CPU_TIME,
  1689.                       DISK_READS,
  1690.                       BUFFER_GETS,
  1691.                       PHYSICAL_READ_BYTES,
  1692.                       PHYSICAL_WRITE_BYTES,
  1693.                       BLOCKING_INSTANCE,
  1694.                       BLOCKING_SESSION,
  1695.                       ASH_COUNTS,
  1696.                       '执行次数过大' MONITOR_TYPES
  1697.                  FROM TMPA T
  1698.                 WHERE T.EXECUTIONS > V_EXECUTIONS
  1699.                      
  1700.                UNION ALL
  1701.                ------------------------------------------ SQL总执行时间过大
  1702.                SELECT INST_ID,
  1703.                       SID,
  1704.                       SERIAL#,
  1705.                       USERNAME,
  1706.                       SQL_TEXT,
  1707.                       EXECUTIONS,
  1708.                       SQL_ID,
  1709.                       SQL_EXEC_START,
  1710.                       LAST_LOAD_TIME,
  1711.                       LAST_ACTIVE_TIME,
  1712.                       ELAPSED_TIME_S,
  1713.                       SESSION_INFO,
  1714.                       SESSION_STATE,
  1715.                       EVENT,
  1716.                       CPU_TIME,
  1717.                       DISK_READS,
  1718.                       BUFFER_GETS,
  1719.                       PHYSICAL_READ_BYTES,
  1720.                       PHYSICAL_WRITE_BYTES,
  1721.                       BLOCKING_INSTANCE,
  1722.                       BLOCKING_SESSION,
  1723.                       ASH_COUNTS,
  1724.                       'SQL总执行时间过大' MONITOR_TYPES
  1725.                  FROM TMPA T
  1726.                 WHERE T.ELAPSED_TIME_S > V_ELAPSED_TIME);

  1728.     COMMIT;

  1730.   END P_SQL_EPP2_LHR;

  1732.   PROCEDURE P_TUNING_SQL AS
  1733.     MY_TASK_NAME VARCHAR2(255);

  1735.   BEGIN
  1736.     ---- 调优捕捉到的垃圾SQL
  1737.     FOR CUR IN (SELECT DISTINCT A.USERNAME, A.SQL_ID, A.SQL_TEXT
  1738.                   FROM XB_SQL_MONITOR_PP_LHR A
  1739.                  WHERE A.TUNING_RESULT IS NULL
  1740.                    AND A.IN_DATE >= SYSDATE - 1) LOOP

  1742.       MY_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => CUR.SQL_TEXT,
  1743.                                                       USER_NAME => CUR.USERNAME);
  1744.       DBMS_SQLTUNE.EXECUTE_TUNING_TASK(TASK_NAME => MY_TASK_NAME);

  1746.       UPDATE XB_SQL_MONITOR_PP_LHR T
  1747.          SET T.TUNING_RESULT = DBMS_SQLTUNE.REPORT_TUNING_TASK(MY_TASK_NAME),
  1748.              T.TUNING_TIME = SYSDATE
  1749.        WHERE T.SQL_ID = CUR.SQL_ID;
  1750.       COMMIT;
  1751.     END LOOP;
  1752.     /*
  1753.     EXCEPTION
  1754.       WHEN OTHERS THEN
  1755.         dbms_output.put_line('error:' || SQLCODE || ',' || SQLERRM);*/
  1756.   END P_TUNING_SQL;

  1758.   PROCEDURE P_GET_PPSQL_PARAMETER AS
  1759.     V_TMPSIZE NUMBER := 5368709120; --bytes
  1760.     V_UNDOSIZE NUMBER := 5368709120; --bytes
  1761.     V_PLAN_COST NUMBER := 3107523095; --cost 花费
  1762.     V_PLAN_CARDINALITY NUMBER := 30748908521460; --预估行数
  1763.     V_ELAPSED_TIME NUMBER := 10000000; ---执行时间，例执行时间设置5小时，则： 5h*60*60*1000000
  1764.     V_EXECUTIONS NUMBER := 1000; --执行次数
  1765.     V_LOGICAL_READS NUMBER := 1000; --逻辑读
  1766.     V_DISK_READS NUMBER := 1000; --物理读
  1767.   BEGIN

  1769.     SELECT ROUND(SUM(BYTES) / 2)
  1770.       INTO V_TMPSIZE
  1771.       FROM V$TEMPFILE D
  1772.      WHERE D.STATUS = 'ONLINE';

  1774.     SELECT ROUND(SUM(BYTES) / 2)
  1775.       INTO V_UNDOSIZE
  1776.       FROM DBA_DATA_FILES D, DBA_TABLESPACES DT
  1777.      WHERE DT.TABLESPACE_NAME = D.TABLESPACE_NAME
  1778.        AND DT.CONTENTS = 'UNDO'
  1779.        AND DT.STATUS = 'ONLINE'
  1780.      GROUP BY D.TABLESPACE_NAME;

  1782.     SELECT ROUND(MAX(A.COST) * 0.8), ROUND(MAX(A.CARDINALITY) * 0.8)
  1783.       INTO V_PLAN_COST, V_PLAN_CARDINALITY
  1784.       FROM GV$SQL_PLAN A
  1785.      WHERE A.OPERATION <> 'MERGE JOIN'
  1786.        AND A.OPTIONS <> 'CARTESIAN'
  1787.        AND NOT EXISTS
  1788.      (SELECT 1
  1789.               FROM GV$SQLAREA B
  1790.              WHERE B.SQL_ID = A.SQL_ID
  1791.                AND B.INST_ID = A.INST_ID
  1792.                AND B.PARSING_SCHEMA_NAME LIKE '%SYS%');

  1794.     SELECT ROUND(MAX(A.ELAPSED_TIME /
  1795.                      (DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS)) / 1000000) * 0.8),
  1796.            ROUND(MAX(A.EXECUTIONS) * 0.8),
  1797.            ROUND(MAX(A.BUFFER_GETS /
  1798.                      (DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS))) * 0.9),
  1799.            ROUND(MAX(A.DISK_READS /
  1800.                      (DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS))) * 0.9)
  1801.       INTO V_ELAPSED_TIME, V_EXECUTIONS, V_LOGICAL_READS, V_DISK_READS
  1802.       FROM GV$SQLAREA A
  1803.      WHERE NOT EXISTS (SELECT 1
  1804.               FROM GV$SQL_PLAN AA
  1805.              WHERE A.SQL_ID = AA.SQL_ID
  1806.                AND AA.OPERATION = 'MERGE JOIN'
  1807.                AND AA.OPTIONS = 'CARTESIAN'
  1808.                AND AA.INST_ID = A.INST_ID)
  1809.        AND A.PARSING_SCHEMA_NAME NOT LIKE '%SYS%';

  1811.     UPDATE XB_SQL_PARAMETERS_LHR T
  1812.        SET T.PARAM_VALUE = V_TMPSIZE
  1813.      WHERE UPPER(T.PARAM_NAME) = UPPER('v_tmpsize');
  1814.     UPDATE XB_SQL_PARAMETERS_LHR T
  1815.        SET T.PARAM_VALUE = V_UNDOSIZE
  1816.      WHERE UPPER(T.PARAM_NAME) = UPPER('v_undosize');
  1817.     UPDATE XB_SQL_PARAMETERS_LHR T
  1818.        SET T.PARAM_VALUE = V_PLAN_COST
  1819.      WHERE UPPER(T.PARAM_NAME) = UPPER('v_plan_cost');
  1820.     UPDATE XB_SQL_PARAMETERS_LHR T
  1821.        SET T.PARAM_VALUE = V_PLAN_CARDINALITY
  1822.      WHERE UPPER(T.PARAM_NAME) = UPPER('v_PLAN_CARDINALITY');
  1823.     UPDATE XB_SQL_PARAMETERS_LHR T
  1824.        SET T.PARAM_VALUE = V_ELAPSED_TIME
  1825.      WHERE UPPER(T.PARAM_NAME) IN
  1826.            (UPPER('v_ELAPSED_TIME'), UPPER('V_ESTIMATE_TIME'));
  1827.     UPDATE XB_SQL_PARAMETERS_LHR T
  1828.        SET T.PARAM_VALUE = V_EXECUTIONS
  1829.      WHERE UPPER(T.PARAM_NAME) = UPPER('v_EXECUTIONS');
  1830.     UPDATE XB_SQL_PARAMETERS_LHR T
  1831.        SET T.PARAM_VALUE = V_EXECUTIONS
  1832.      WHERE UPPER(T.PARAM_NAME) = UPPER('V_LOGICAL_READS');
  1833.     UPDATE XB_SQL_PARAMETERS_LHR T
  1834.        SET T.PARAM_VALUE = V_EXECUTIONS
  1835.      WHERE UPPER(T.PARAM_NAME) = UPPER('V_DISK_READS');

  1837.     COMMIT;

  1839.   EXCEPTION
  1840.     WHEN OTHERS THEN
  1841.       DBMS_OUTPUT.PUT_LINE('error:' || SQLCODE || ',' || SQLERRM);
  1842.   END P_GET_PPSQL_PARAMETER;

  1844.   FUNCTION F_GET_RIGHT_SIZE_LHR(P_SIZE IN VARCHAR2) RETURN VARCHAR2 AS

  1846.     V_RESULTS VARCHAR2(255);

  1848.   BEGIN

  1850.     SELECT CASE
  1851.              WHEN P_SIZE = 0 THEN
  1852.               0 || ''
  1853.              WHEN P_SIZE < 1024 AND P_SIZE > 0 THEN
  1854.               P_SIZE || 'BYTES'
  1855.              WHEN P_SIZE >= 1024 AND P_SIZE < 1048576 THEN
  1856.               ROUND(P_SIZE / 1024, 3) || 'KB'
  1857.              WHEN P_SIZE >= 1048576 AND P_SIZE < 1073741824 THEN
  1858.               ROUND(P_SIZE / 1048576, 3) || 'M'
  1859.              WHEN P_SIZE >= 1073741824 AND P_SIZE < 1099511627776 THEN
  1860.               ROUND(P_SIZE / 1073741824, 3) || 'G'
  1861.              WHEN P_SIZE >= 1099511627776 AND P_SIZE < 1125899906842624 THEN
  1862.               ROUND(P_SIZE / 1099511627776, 3) || 'T'
  1863.            END AS SIZES
  1864.       INTO V_RESULTS
  1865.       FROM DUAL;

  1867.     RETURN V_RESULTS;
  1868.   EXCEPTION
  1869.     WHEN OTHERS THEN
  1870.       NULL;
  1871.       RETURN P_SIZE;
  1872.   END;

  1874.   FUNCTION F_GET_TOTAL_TIME_LHR(P_TOTAL_SECONDS NUMBER,
  1875.                                 P_FLAG VARCHAR2 DEFAULT 'S')
  1876.     RETURN VARCHAR2 DETERMINISTIC IS
  1877.     -----------------------------------------------------------------------------------
  1878.     -- Created on 2012/10/18 16:49:28 by lhr
  1879.     --Changed on 2015/9/3 16:49:28 by lhr
  1880.     -- function： 该函数 返回一个秒转换为标准时间
  1881.     -----------------------------------------------------------------------------------
  1882.     V_TMP FLOAT(18);
  1883.     V_RETURNS VARCHAR2(200);
  1884.     V_TOTAL_SECONDS NUMBER := P_TOTAL_SECONDS;
  1885.   BEGIN

  1887.     IF P_TOTAL_SECONDS IS NULL OR P_TOTAL_SECONDS = 0 THEN

  1889.       RETURN 0 || '秒';

  1891.     END IF;

  1893.     ---传入的为天转换为秒
  1894.     IF UPPER(P_FLAG) = 'D' THEN
  1895.       V_TOTAL_SECONDS := P_TOTAL_SECONDS * 24 * 60 * 60;
  1896.     END IF;

  1898.     -- 首先处理日期

  1900.     --年
  1901.     V_TMP := TRUNC(V_TOTAL_SECONDS / (12 * 30 * 24 * 3600));

  1903.     IF V_TMP > 0 THEN
  1904.       V_RETURNS := V_TMP || '年';

  1906.     END IF;

  1908.     ---月
  1909.     V_TMP := TRUNC(MOD(V_TOTAL_SECONDS, (12 * 30 * 24 * 3600)) /
  1910.                    (3600 * 24 * 30));

  1912.     IF V_TMP > 0 THEN
  1913.       V_RETURNS := V_RETURNS || V_TMP || '月';

  1915.     END IF;

  1917.     --天
  1918.     V_TMP := TRUNC(MOD(V_TOTAL_SECONDS, (3600 * 24 * 30)) / (3600 * 24));

  1920.     IF V_TMP > 0 THEN
  1921.       V_RETURNS := V_RETURNS || V_TMP || '天';

  1923.     END IF;

  1925.     --处理小时

  1927.     V_TMP := TRUNC(MOD(V_TOTAL_SECONDS, 3600 * 24) / 3600);

  1929.     IF V_TMP > 0 THEN
  1930.       V_RETURNS := V_RETURNS || V_TMP || '时';
  1931.     END IF;

  1933.     --处理分钟

  1935.     V_TMP := TRUNC(MOD(V_TOTAL_SECONDS, 3600) / 60);

  1937.     IF V_TMP > 0 THEN
  1938.       V_RETURNS := V_RETURNS || V_TMP || '分';
  1939.     END IF;

  1941.     --处理秒钟
  1942.     V_TMP := TRUNC(MOD(V_TOTAL_SECONDS, 60), 3);

  1944.     IF V_TMP > 0 AND V_TMP < 1 THEN
  1945.       V_RETURNS := V_RETURNS || '0' || V_TMP || '秒';

  1947.     ELSIF V_TMP >= 1 THEN
  1948.       V_RETURNS := V_RETURNS || V_TMP || '秒';
  1949.     ELSIF V_TMP = 0 AND V_RETURNS IS NULL THEN
  1950.       V_RETURNS := '0' || TRUNC(V_TOTAL_SECONDS, 6) || '秒';
  1951.     END IF;

  1953.     RETURN V_RETURNS;

  1955.   END F_GET_TOTAL_TIME_LHR;
  1956.   ---------------------------------------------------------------------------------------------------------------------------------
  1957.   ---------------------------------------------------------------------------------------------------------------------------------

  1959.   FUNCTION F_GET_SQL_OBJECT_LHR(P_SQL VARCHAR2,
  1960.                                 P_COMMAND VARCHAR2 DEFAULT 'INTO',
  1961.                                 PFLAG NUMBER DEFAULT 1) RETURN VARCHAR2 IS
  1962.     -----------------------------------------------------------------------------------
  1963.     -- CREATED ON 2012/8/20 11:33:07 BY LHR
  1964.     --CHANGED ON 2012/8/20 11:33:07 BY LHR
  1965.     -- FUNCTION： 该函数返回
  1966.     -----------------------------------------------------------------------------------

  1968.     --- 1、 回车--》 空格 2、 取前1500个字母 3、多空格--》单空格
  1969.     V_SQL VARCHAR2(32767) := TRIM(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(P_SQL),
  1970.                                                                                                                                       '/\*.*?\*/'),
  1971.                                                                                                                        '--.*+'),
  1972.                                                                                                         CHR(10),
  1973.                                                                                                         ' '),
  1974.                                                                                                 CHR(13),
  1975.                                                                                                 ' '),
  1976.                                                                                         '[ ]+',
  1977.                                                                                         ' '),
  1978.                                                                          '/\*.*?\*/'),
  1979.                                                           '[ ]+',
  1980.                                                           ' '));
  1981.     V_OBJECT_NAME VARCHAR2(4000);
  1982.     V_OBJECT_OWNER VARCHAR2(4000);
  1983.     V_L_INTO NUMBER;
  1984.     V_L_TABR NUMBER;
  1985.   BEGIN
  1986.     ---从第一个INSERT位置开始截取50个字符
  1987.     V_SQL := REPLACE(SUBSTR(V_SQL, INSTR(V_SQL, P_COMMAND), 1000), '(', ' ');
  1988.     --INTO的位置 表名前的空格
  1989.     V_L_INTO := INSTR(V_SQL, P_COMMAND) + LENGTH(P_COMMAND) + 1;

  1991.     --I表名后的空格位置
  1992.     V_L_TABR := INSTR(V_SQL,
  1993.                       ' ',
  1994.                       INSTR(V_SQL, P_COMMAND) + LENGTH(P_COMMAND) + 1,
  1995.                       1);

  1997.     ----- 表名
  1998.     V_OBJECT_NAME := SUBSTR(V_SQL, V_L_INTO, V_L_TABR - V_L_INTO);

  2000.     IF V_OBJECT_NAME LIKE '%.%' THEN

  2002.       V_OBJECT_OWNER := SUBSTR(V_OBJECT_NAME,
  2003.                                1,
  2004.                                INSTR(V_OBJECT_NAME, '.') - 1); --表名前的OWNER

  2006.       V_OBJECT_NAME := SUBSTR(V_OBJECT_NAME, INSTR(V_OBJECT_NAME, '.') + 1); -- 去掉表名前的OWNER
  2007.     END IF;

  2009.     IF PFLAG = 1 THEN
  2010.       RETURN V_OBJECT_NAME;
  2011.     ELSE
  2012.       RETURN V_OBJECT_OWNER;
  2013.     END IF;
  2014.   EXCEPTION
  2015.     WHEN OTHERS THEN
  2016.       NULL;
  2017.   END F_GET_SQL_OBJECT_LHR;

  2019.   FUNCTION F_GET_SQL_COMMAND_LHR(P_SQL VARCHAR2) RETURN VARCHAR2 IS
  2020.     -----------------------------------------------------------------------------------
  2021.     -- CREATED ON 2012/8/20 11:33:07 BY LHR
  2022.     --CHANGED ON 2012/8/20 11:33:07 BY LHR
  2023.     -- FUNCTION： 该函数返回SQL语句的类型
  2024.     -----------------------------------------------------------------------------------

  2026.     --- 1、 回车--》 空格 2、 取前1500个字母 3、多空格--》单空格
  2027.     V_SQL VARCHAR2(32767) := TRIM(REPLACE(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(P_SQL),
  2028.                                                                                                                                                  '/\*.*?\*/'),
  2029.                                                                                                                                   '--.*+'),
  2030.                                                                                                                    CHR(10),
  2031.                                                                                                                    ' '),
  2032.                                                                                                            CHR(13),
  2033.                                                                                                            ' '),
  2034.                                                                                                    '[ ]+',
  2035.                                                                                                    ' '),
  2036.                                                                                     '/\*.*?\*/'),
  2037.                                                                      '[ ]+',
  2038.                                                                      ' '),
  2039.                                                       'SELECT',
  2040.                                                       'SELECT '),
  2041.                                               '('));
  2042.     V_COMMAND VARCHAR2(255);
  2043.   BEGIN
  2044.     IF P_SQL IS NULL THEN
  2045.       RETURN '';
  2046.     ELSIF V_SQL IS NULL THEN

  2048.       SELECT CASE
  2049.                WHEN UPPER(P_SQL) LIKE '%BEGIN%' THEN
  2050.                 'PL/SQL EXECUTE'
  2051.                WHEN UPPER(P_SQL) LIKE '%MERGE%' THEN
  2052.                 'MERGE'
  2053.                WHEN (REPLACE(UPPER(P_SQL), ' ', '') LIKE '%CREATEINDEX%' OR
  2054.                     REPLACE(UPPER(P_SQL), ' ', '') LIKE
  2055.                     '%CREATEUNIQUEINDEX%' OR REPLACE(UPPER(P_SQL), ' ', '') LIKE
  2056.                     '%CREATEBITMAPINDEX%' OR
  2057.                     UPPER(P_SQL) LIKE '%CREATE%INDEX%') THEN
  2058.                 'CREATE INDEX'
  2059.                WHEN REPLACE(UPPER(P_SQL), ' ', '') LIKE '%CREATETABLE%' THEN
  2060.                 'CREATE TABLE'
  2061.                WHEN UPPER(P_SQL) LIKE '%INSERT%' THEN
  2062.                 'INSERT'
  2063.                WHEN UPPER(P_SQL) LIKE '%UPDATE%' THEN
  2064.                 'UPDATE'
  2065.                WHEN UPPER(P_SQL) LIKE '%DELETE%' THEN
  2066.                 'DELETE'
  2067.                WHEN UPPER(P_SQL) LIKE '%WITH%' THEN
  2068.                 'WITH'
  2069.                WHEN UPPER(P_SQL) LIKE '%SELECT%' THEN
  2070.                 'SELECT'
  2071.                WHEN REPLACE(UPPER(P_SQL), ' ', '') LIKE '%ALTERTABLE%' THEN
  2072.                 'ALTER TABLE'
  2073.                WHEN REPLACE(UPPER(P_SQL), ' ', '') LIKE '%ALTERINDEX%' THEN
  2074.                 'ALTER INDEX'
  2075.              END
  2076.         INTO V_COMMAND
  2077.         FROM DUAL;

  2079.     ELSE

  2081.       V_COMMAND := SUBSTR(V_SQL, 1, INSTR(V_SQL, ' ', 4) - 1);

  2083.       IF TRIM(V_COMMAND) IN ('ALTER', 'CREATE') THEN
  2084.         V_SQL := REGEXP_REPLACE(REPLACE(REPLACE(V_SQL, 'UNIQUE'), 'BITMAP'),
  2085.                                 '[ ]+',
  2086.                                 ' ');

  2088.         V_COMMAND := SUBSTR(V_SQL, 1, INSTR(V_SQL, ' ', 2, 2) - 1);

  2090.       ELSIF TRIM(V_COMMAND) IN ('DECLARE', 'BEGIN') THEN
  2091.         V_COMMAND := 'PL/SQL EXECUTE';

  2093.       ELSIF TRIM(V_COMMAND) = 'MERGE' THEN
  2094.         V_COMMAND := 'MERGE INTO';
  2095.       ELSIF TRIM(V_COMMAND) IS NULL THEN

  2097.         SELECT CASE
  2098.                  WHEN UPPER(P_SQL) LIKE '%BEGIN%' THEN
  2099.                   'PL/SQL EXECUTE'
  2100.                  WHEN UPPER(P_SQL) LIKE '%MERGE%' THEN
  2101.                   'MERGE'
  2102.                  WHEN (REPLACE(UPPER(P_SQL), ' ', '') LIKE '%CREATEINDEX%' OR
  2103.                       REPLACE(UPPER(P_SQL), ' ', '') LIKE
  2104.                       '%CREATEUNIQUEINDEX%' OR
  2105.                       REPLACE(UPPER(P_SQL), ' ', '') LIKE
  2106.                       '%CREATEBITMAPINDEX%' OR
  2107.                       UPPER(P_SQL) LIKE '%CREATE%INDEX%') THEN
  2108.                   'CREATE INDEX'
  2109.                  WHEN REPLACE(UPPER(P_SQL), ' ', '') LIKE '%CREATETABLE%' THEN
  2110.                   'CREATE TABLE'
  2111.                  WHEN UPPER(P_SQL) LIKE '%INSERT%' THEN
  2112.                   'INSERT'
  2113.                  WHEN UPPER(P_SQL) LIKE '%UPDATE%' THEN
  2114.                   'UPDATE'
  2115.                  WHEN UPPER(P_SQL) LIKE '%DELETE%' THEN
  2116.                   'DELETE'
  2117.                  WHEN UPPER(P_SQL) LIKE '%WITH%' THEN
  2118.                   'WITH'
  2119.                  WHEN UPPER(P_SQL) LIKE '%SELECT%' THEN
  2120.                   'SELECT'
  2121.                  WHEN REPLACE(UPPER(P_SQL), ' ', '') LIKE '%ALTERTABLE%' THEN
  2122.                   'ALTER TABLE'
  2123.                  WHEN REPLACE(UPPER(P_SQL), ' ', '') LIKE '%ALTERINDEX%' THEN
  2124.                   'ALTER INDEX'
  2125.                END
  2126.           INTO V_COMMAND
  2127.           FROM DUAL;

  2129.       END IF;

  2131.     END IF;

  2133.     V_COMMAND := REPLACE(REPLACE(REPLACE(TRIM(V_COMMAND), '(('), '('), '*');

  2135.     IF V_COMMAND NOT IN ('ALTER INDEX',
  2136.                          'ALTER TABLE',
  2137.                          'CREATE INDEX',
  2138.                          'CREATE TABLE',
  2139.                          'DELETE',
  2140.                          'INSERT',
  2141.                          'MERGE INTO',
  2142.                          'PL/SQL EXECUTE',
  2143.                          'SELECT',
  2144.                          'WITH',
  2145.                          'UPDATE') THEN
  2146.       RETURN '';
  2147.     END IF;

  2149.     RETURN V_COMMAND;

  2151.   EXCEPTION
  2152.     WHEN OTHERS THEN
  2153.       NULL;
  2154.   END;

  2156. END PKG_SQL_MONITOR_LHR;
  2157. /



  2161. --------------------------------------------------------------

  2163. EXEC PKG_SQL_MONITOR_LHR.P_GET_PPSQL_PARAMETER;



  2167. -------------------------
  2168. EXEC DBMS_SCHEDULER.DROP_PROGRAM('PROG_SQL_DONE_LHR',TRUE);
  2169. EXEC DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_SQL_DONE_LHR' );

  2171. BEGIN
  2172.     DBMS_SCHEDULER.CREATE_PROGRAM(PROGRAM_NAME => 'PROG_SQL_DONE_LHR',
  2173.                                   PROGRAM_ACTION => 'PKG_SQL_MONITOR_LHR.P_SQL_DONE_LHR',
  2174.                                   PROGRAM_TYPE => 'STORED_PROCEDURE',
  2175.                                   ENABLED => TRUE);
  2176. END;
  2177. /
  2178.  


  2181. BEGIN

  2183.     DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'JOB_SQL_DONE_LHR',
  2184.                               PROGRAM_NAME => 'PROG_SQL_DONE_LHR',
  2185.                               REPEAT_INTERVAL=> 'FREQ=SECONDLY;INTERVAL=30',
  2186.                               JOB_STYLE => 'LIGHTWEIGHT',
  2187.                               ENABLED=>TRUE,
  2188.                               COMMENTS =>'监控已经执行完毕的SQL语句（V$SQL_MONITOR）');

  2190. END;
  2191. /


  2194. ------------------------
  2195. EXEC DBMS_SCHEDULER.DROP_PROGRAM('PROG_SQL_EPP_LHR',TRUE);
  2196. EXEC DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_SQL_EPP_LHR' );
  2197. BEGIN
  2198.     DBMS_SCHEDULER.create_program(program_name => 'PROG_SQL_EPP_LHR',
  2199.                                   program_action => 'PKG_SQL_MONITOR_LHR.P_SQL_EPP_LHR',
  2200.                                   program_type => 'STORED_PROCEDURE',
  2201.                                   enabled => TRUE);
  2202. END;
  2203. /
  2204.             
  2205. BEGIN

  2207.     DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'JOB_SQL_EPP_LHR',
  2208.                               PROGRAM_NAME => 'PROG_SQL_EPP_LHR',
  2209.                               REPEAT_INTERVAL=> 'FREQ=SECONDLY;INTERVAL=30',
  2210.                               JOB_STYLE => 'LIGHTWEIGHT',
  2211.                               ENABLED=>TRUE,
  2212.                               COMMENTS =>'记录性能低下的SQL语句');
  2213. END;
  2214. /

  2216. EXEC DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_AUTO_TUNING_SQL_LHR' );
  2217. BEGIN
  2218.   --DBMS_SCHEDULER.drop_job('JOB_AUTO_TUNING_SQL_LHR');
  2219.     DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'JOB_AUTO_TUNING_SQL_LHR',
  2220.                               JOB_TYPE => 'STORED_PROCEDURE',
  2221.                               JOB_ACTION => 'PKG_SQL_MONITOR_LHR.P_TUNING_SQL',
  2222.                               repeat_interval => 'FREQ=MINUTELY;INTERVAL=50',
  2223.                               ENABLED => TRUE,
  2224.                               START_DATE => SYSDATE,
  2225.                               COMMENTS => '每隔50分钟优化一次SQL');
  2226. END;
  2227. /
  2228.  
  2229. EXEC DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_SQL_EPP2_LHR' );
  2230. BEGIN
  2231.   --DBMS_SCHEDULER.DROP_JOB('JOB_SQL_EPP2_LHR');
  2232.     DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'JOB_SQL_EPP2_LHR',
  2233.                               JOB_TYPE => 'STORED_PROCEDURE',
  2234.                               JOB_ACTION => 'PKG_SQL_MONITOR_LHR.P_SQL_EPP2_LHR',
  2235.                               REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=28',
  2236.                               ENABLED => TRUE,
  2237.                               START_DATE => SYSDATE,
  2238.                               COMMENTS => 'GV$SQL检查性能低下的SQL');
  2239. END;
  2240. /
  2241.  

  2243. ------------------------- 视图 ------------------------------
  2244. CREATE OR REPLACE VIEW VW_SQL_PP_LHR AS
  2245. --记录所有正在运行中的性能差的SQL语句
  2246. WITH TMPS AS
  2247.  (SELECT WB.INST_ID INST_ID,
  2248.        WB.SID SID,
  2249.        WB.SERIAL#,
  2250.        WB.SPID,
  2251.        WB.OSUSER,
  2252.        WB.USERNAME,
  2253.        WA.PLAN_DEPTH,
  2254.        WA.PLAN_OPERATION PLAN_OPERATION,
  2255.        WA.PLAN_OPTIONS,
  2256.        WA.PLAN_PARTITION_START,
  2257.        WA.PLAN_PARTITION_STOP,
  2258.        WA.STARTS,
  2259.        WA.PLAN_COST,
  2260.        WA.PLAN_CARDINALITY,
  2261.        NVL(WB.SQL_ID, WA.SQL_ID) SQL_ID,
  2262.        WB.SQL_EXEC_START,
  2263.        WA.PX_SERVERS_REQUESTED,
  2264.        WA.PX_SERVERS_ALLOCATED,
  2265.        WA.PX_MAXDOP,
  2266.        WA.ELAPSED_TIME_S ELAPSED_TIME_S,
  2267.        WA.CPU_TIME CPU_TIME,
  2268.        WA.BUFFER_GETS,
  2269.        WA.PHYSICAL_READ_BYTES,
  2270.        WA.PHYSICAL_WRITE_BYTES,
  2271.        WA.USER_IO_WAIT_TIME USER_IO_WAIT_TIME,
  2272.        NVL((SELECT NS.SQL_TEXT
  2273.           FROM GV$SQLAREA NS
  2274.          WHERE NS.SQL_ID = WB.SQL_ID
  2275.            AND NS.INST_ID = WB.INST_ID),WA.SQL_TEXT) SQL_TEXT,
  2276.        WB.LOGON_TIME,
  2277.        WB.SQL_EXEC_ID,
  2278.        WB.EVENT,
  2279.        WB.BLOCKING_INSTANCE BLOCKING_INSTANCE,
  2280.        WB.BLOCKING_SESSION BLOCKING_SESSION,
  2281.        WB.BLOCKING_SESSION_SERIAL# BLOCKING_SESSION_SERIAL#,
  2282.        WB.TADDR,
  2283.        WB.SADDR,
  2284.        WB.LAST_CALL_ET,
  2285.        (WB.SESSION_INFO || '--' || WB.SESSION_TYPE || '--' || WB.MACHINE) SESSION_INFO,
  2286.        (SELECT NS.EXECUTIONS
  2287.           FROM GV$SQLAREA NS
  2288.          WHERE NS.SQL_ID = WB.SQL_ID
  2289.            AND NS.INST_ID = WB.INST_ID) EXECUTIONS,
  2290.        'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || WB.SQL_ID ||
  2291.        ''',' || WB.SQL_CHILD_NUMBER || ',''advanced''));' SQL_PLAN,
  2292.        WB.ASH_COUNTS,
  2293.        WB.SESSION_STATE
  2294.   FROM (SELECT A.INST_ID,
  2295.                A.SID,
  2296.                A.PLAN_DEPTH,
  2297.                A.PLAN_OPERATION PLAN_OPERATION,
  2298.                A.PLAN_OPTIONS,
  2299.                A.PLAN_PARTITION_START,
  2300.                A.PLAN_PARTITION_STOP,
  2301.                A.STARTS,
  2302.                MAX(A.PLAN_COST) OVER(PARTITION BY A.INST_ID, A.SID, A.KEY, A.SQL_EXEC_ID, A.SQL_ID) AS PLAN_COST,
  2303.                MAX(A.PLAN_CARDINALITY) OVER(PARTITION BY A.INST_ID, A.SID, A.KEY, A.SQL_EXEC_ID, A.SQL_ID) AS PLAN_CARDINALITY,
  2304.                A.SQL_ID,
  2305.                A.SQL_EXEC_START,
  2306.                B.PX_SERVERS_REQUESTED,
  2307.                B.PX_SERVERS_ALLOCATED,
  2308.                B.PX_MAXDOP,
  2309.                (B.ELAPSED_TIME / 1000000) ELAPSED_TIME_S,
  2310.                (B.CPU_TIME / 1000000) CPU_TIME,
  2311.                B.BUFFER_GETS,
  2312.                B.PHYSICAL_READ_BYTES,
  2313.                B.PHYSICAL_WRITE_BYTES,
  2314.                (B.USER_IO_WAIT_TIME / 1000000) USER_IO_WAIT_TIME,
  2315.                B.SQL_TEXT SQL_TEXT,
  2316.                (B.MODULE || '--' || B.ACTION || '--' || B.PROGRAM || '--' ||
  2317.                B.PROCESS_NAME || '--' || B.CLIENT_IDENTIFIER || '--' ||
  2318.                B.CLIENT_INFO || '--' || B.SERVICE_NAME) SESSION_INFO,
  2319.                A.SQL_EXEC_ID
  2320.           FROM GV$SQL_PLAN_MONITOR A, GV$SQL_MONITOR B
  2321.          WHERE A.SID = B.SID
  2322.            AND A.KEY = B.KEY
  2323.            AND A.INST_ID = B.INST_ID
  2324.            AND A.SQL_EXEC_ID = B.SQL_EXEC_ID
  2325.            AND A.STATUS IN ('EXECUTING', 'DONE(ERROR)')
  2326.            AND B.STATUS IN ('EXECUTING', 'DONE(ERROR)')
  2327.            AND B.PROCESS_NAME NOT LIKE 'p%') WA
  2328.  RIGHT OUTER JOIN (SELECT ASH.INST_ID,
  2329.                           ASH.SESSION_ID SID,
  2330.                           ASH.SESSION_SERIAL# SERIAL#,
  2331.                           (SELECT PR.SPID
  2332.                              FROM GV$PROCESS PR
  2333.                             WHERE GVS.PADDR = PR.ADDR
  2334.                               AND PR.INST_ID = ASH.INST_ID) SPID,
  2335.                           ASH.SESSION_TYPE,
  2336.                           ASH.USER_ID,
  2337.                           ASH.SQL_ID,
  2338.                           ASH.SQL_CHILD_NUMBER,
  2339.                           ASH.SQL_OPNAME,
  2340.                           ASH.SQL_EXEC_ID,
  2341.                           NVL(ASH.EVENT, GVS.EVENT) EVENT,
  2342.                           ASH.SESSION_STATE,
  2343.                           ASH.BLOCKING_SESSION,
  2344.                           ASH.BLOCKING_SESSION_SERIAL#,
  2345.                           ASH.BLOCKING_INST_ID BLOCKING_INSTANCE,
  2346.                           ASH.CLIENT_ID,
  2347.                           ASH.MACHINE,
  2348.                           GVS.LAST_CALL_ET,
  2349.                           GVS.TADDR,
  2350.                           GVS.SADDR,
  2351.                           GVS.LOGON_TIME,
  2352.                           GVS.USERNAME,
  2353.                           GVS.OSUSER,
  2354.                           GVS.SQL_EXEC_START,
  2355.                                                     (GVS.MODULE || '--' || GVS.ACTION || '--' || GVS.PROGRAM || '--' ||
  2356.                GVS.PROCESS || '--' || GVS.CLIENT_IDENTIFIER || '--' ||
  2357.                GVS.CLIENT_INFO || '--' || GVS.SERVICE_NAME) SESSION_INFO,
  2358.                           COUNT(*) ASH_COUNTS
  2359.                      FROM GV$ACTIVE_SESSION_HISTORY ASH, GV$SESSION GVS
  2360.                     WHERE ASH.INST_ID = GVS.INST_ID
  2361.                       AND GVS.SQL_ID = ASH.SQL_ID
  2362.                       AND GVS.SQL_EXEC_ID = ASH.SQL_EXEC_ID
  2363.                       AND ASH.SESSION_ID = GVS.SID
  2364.                       AND ASH.SESSION_SERIAL# = GVS.SERIAL#
  2365.                       AND GVS.STATUS = 'ACTIVE'
  2366.                       AND ASH.SQL_ID IS NOT NULL
  2367.                     GROUP BY ASH.INST_ID,
  2368.                              ASH.SESSION_ID,
  2369.                              ASH.SESSION_SERIAL#,
  2370.                              ASH.SESSION_TYPE,
  2371.                              ASH.USER_ID,
  2372.                              ASH.SQL_ID,
  2373.                              ASH.SQL_CHILD_NUMBER,
  2374.                              ASH.SQL_OPNAME,
  2375.                              ASH.SQL_EXEC_ID,
  2376.                              NVL(ASH.EVENT, GVS.EVENT),
  2377.                              ASH.SESSION_STATE,
  2378.                              ASH.BLOCKING_SESSION,
  2379.                              ASH.BLOCKING_SESSION_SERIAL#,
  2380.                              ASH.BLOCKING_INST_ID,
  2381.                              ASH.CLIENT_ID,
  2382.                              ASH.MACHINE,
  2383.                              GVS.LAST_CALL_ET,
  2384.                              GVS.TADDR,
  2385.                              GVS.SADDR,
  2386.                              GVS.LOGON_TIME,
  2387.                              GVS.USERNAME,
  2388.                              GVS.OSUSER,
  2389.                              GVS.PADDR,
  2390.                           (GVS.MODULE || '--' || GVS.ACTION || '--' || GVS.PROGRAM || '--' ||
  2391.                GVS.PROCESS || '--' || GVS.CLIENT_IDENTIFIER || '--' ||
  2392.                GVS.CLIENT_INFO || '--' || GVS.SERVICE_NAME),
  2393.                              GVS.SQL_EXEC_START
  2394.                    HAVING COUNT(*) > 6) WB
  2395.     ON (WB.SID = WA.SID AND WB.INST_ID = WA.INST_ID AND
  2396.        WB.SQL_ID = WA.SQL_ID AND WB.SQL_EXEC_ID = WA.SQL_EXEC_ID)
  2397. )
  2398. ------------------------------------------ 笛卡尔积
  2399. SELECT DISTINCT T.INST_ID,
  2400.                 T.SID,
  2401.                 T.SERIAL#,
  2402.                 T.SPID,
  2403.                 T.OSUSER,
  2404.                 T.USERNAME,
  2405.                 T.EVENT,
  2406.                 T.SESSION_STATE,
  2407.                 T.SQL_TEXT,
  2408.                 T.EXECUTIONS,
  2409.                 T.ELAPSED_TIME_S,
  2410.                 T.CPU_TIME,
  2411.                 T.USER_IO_WAIT_TIME,
  2412.                 T.BUFFER_GETS,
  2413.                 T.PLAN_OPERATION,
  2414.                                 T.STARTS,
  2415.                 T.PLAN_PARTITION_START,
  2416.                 T.PLAN_PARTITION_STOP,
  2417.                 T.PHYSICAL_READ_BYTES,
  2418.                 T.PHYSICAL_WRITE_BYTES,
  2419.                 T.BLOCKING_INSTANCE,
  2420.                 T.BLOCKING_SESSION,
  2421.                                 T.BLOCKING_SESSION_SERIAL#,
  2422.                 T.LAST_CALL_ET,
  2423.                 T.SQL_ID,
  2424.                 T.SQL_EXEC_START,
  2425.                 T.SQL_PLAN,
  2426.                 T.LOGON_TIME,
  2427.                                 T.ASH_COUNTS,
  2428.                 T.SESSION_INFO,
  2429.                 '笛卡尔积【' || COUNT(*) OVER(PARTITION BY T.INST_ID, T.SID, T.SERIAL#, T.SQL_ID) || '】个' MONITOR_TYPES
  2430.   FROM TMPS T
  2431.  WHERE T.PLAN_OPERATION = 'MERGE JOIN'
  2432.    AND T.PLAN_OPTIONS = 'CARTESIAN'
  2433.    AND T.USERNAME NOT IN ('SYS')

  2435. UNION ALL

  2437. ------------------------------------------ SQL执行时间过大
  2438. SELECT T.INST_ID,
  2439.        T.SID,
  2440.        T.SERIAL#,
  2441.        T.SPID,
  2442.        T.OSUSER,
  2443.        T.USERNAME,
  2444.        T.EVENT,
  2445.        T.SESSION_STATE,
  2446.        T.SQL_TEXT,
  2447.        T.EXECUTIONS,
  2448.        T.ELAPSED_TIME_S,
  2449.        T.CPU_TIME,
  2450.        T.USER_IO_WAIT_TIME,
  2451.        T.BUFFER_GETS,
  2452.        T.PLAN_OPERATION,
  2453.        T.STARTS,
  2454.        T.PLAN_PARTITION_START,
  2455.        T.PLAN_PARTITION_STOP,
  2456.        T.PHYSICAL_READ_BYTES,
  2457.        T.PHYSICAL_WRITE_BYTES,
  2458.        T.BLOCKING_INSTANCE,
  2459.        T.BLOCKING_SESSION,
  2460.        T.BLOCKING_SESSION_SERIAL#,
  2461.        T.LAST_CALL_ET,
  2462.        T.SQL_ID,
  2463.        T.SQL_EXEC_START,
  2464.        T.SQL_PLAN,
  2465.        T.LOGON_TIME,
  2466.              T.ASH_COUNTS,
  2467.        T.SESSION_INFO,
  2468.        '执行时间过大' MONITOR_TYPES
  2469.   FROM TMPS T
  2470.  WHERE T.ELAPSED_TIME_S > 10 --5 * 60 * 60
  2471.    AND (nvl(PLAN_DEPTH,1)=1)

  2473. UNION ALL

  2475. ------------------------------------------ 分区表全分区扫描

  2477. SELECT T.INST_ID,
  2478.        T.SID,
  2479.        T.SERIAL#,
  2480.        T.SPID,
  2481.        T.OSUSER,
  2482.        T.USERNAME,
  2483.        T.EVENT,
  2484.        T.SESSION_STATE,
  2485.        T.SQL_TEXT,
  2486.        T.EXECUTIONS,
  2487.        T.ELAPSED_TIME_S,
  2488.        T.CPU_TIME,
  2489.        T.USER_IO_WAIT_TIME,
  2490.        T.BUFFER_GETS,
  2491.        T.PLAN_OPERATION,
  2492.        T.STARTS,
  2493.        T.PLAN_PARTITION_START,
  2494.        T.PLAN_PARTITION_STOP,
  2495.        T.PHYSICAL_READ_BYTES,
  2496.        T.PHYSICAL_WRITE_BYTES,
  2497.        T.BLOCKING_INSTANCE,
  2498.        T.BLOCKING_SESSION,
  2499.        T.BLOCKING_SESSION_SERIAL#,
  2500.        T.LAST_CALL_ET,
  2501.        T.SQL_ID,
  2502.        T.SQL_EXEC_START,
  2503.        T.SQL_PLAN,
  2504.        T.LOGON_TIME,
  2505.              T.ASH_COUNTS,
  2506.        T.SESSION_INFO,
  2507.        '分区表全分区扫描' MONITOR_TYPES
  2508.   FROM TMPS T
  2509.  WHERE T.PLAN_OPERATION LIKE 'PARTITION%'
  2510.    AND T.PLAN_OPTIONS = 'ALL'
  2511.   -- AND T.ELAPSED_TIME_S >= 0.5 * 60 * 60

  2513. UNION ALL

  2515. ------------------------------------------ 执行计划中COST花费超大

  2517. SELECT T.INST_ID,
  2518.        T.SID,
  2519.        T.SERIAL#,
  2520.        T.SPID,
  2521.        T.OSUSER,
  2522.        T.USERNAME,
  2523.        T.EVENT,
  2524.        T.SESSION_STATE,
  2525.        T.SQL_TEXT,
  2526.        T.EXECUTIONS,
  2527.        T.ELAPSED_TIME_S,
  2528.        T.CPU_TIME,
  2529.        T.USER_IO_WAIT_TIME,
  2530.        T.BUFFER_GETS,
  2531.        T.PLAN_OPERATION,
  2532.        T.STARTS,
  2533.        T.PLAN_PARTITION_START,
  2534.        T.PLAN_PARTITION_STOP,
  2535.        T.PHYSICAL_READ_BYTES,
  2536.        T.PHYSICAL_WRITE_BYTES,
  2537.        T.BLOCKING_INSTANCE,
  2538.        T.BLOCKING_SESSION,
  2539.        T.BLOCKING_SESSION_SERIAL#,
  2540.        T.LAST_CALL_ET,
  2541.        T.SQL_ID,
  2542.        T.SQL_EXEC_START,
  2543.        T.SQL_PLAN,
  2544.        T.LOGON_TIME,
  2545.              T.ASH_COUNTS,
  2546.        T.SESSION_INFO,
  2547.        '执行计划中COST花费超大【' || T.PLAN_COST || '】' MONITOR_TYPES
  2548.   FROM TMPS T
  2549.  WHERE T.PLAN_COST >= 3107523095
  2550.    AND (nvl(PLAN_DEPTH,1)=1)

  2552. UNION ALL
  2553. ------------------------------------------ 执行计划中预估行数超大

  2555. SELECT T.INST_ID,
  2556.        T.SID,
  2557.        T.SERIAL#,
  2558.        T.SPID,
  2559.        T.OSUSER,
  2560.        T.USERNAME,
  2561.        T.EVENT,
  2562.        T.SESSION_STATE,
  2563.        T.SQL_TEXT,
  2564.        T.EXECUTIONS,
  2565.        T.ELAPSED_TIME_S,
  2566.        T.CPU_TIME,
  2567.        T.USER_IO_WAIT_TIME,
  2568.        T.BUFFER_GETS,
  2569.        T.PLAN_OPERATION,
  2570.        T.STARTS,
  2571.        T.PLAN_PARTITION_START,
  2572.        T.PLAN_PARTITION_STOP,
  2573.        T.PHYSICAL_READ_BYTES,
  2574.        T.PHYSICAL_WRITE_BYTES,
  2575.        T.BLOCKING_INSTANCE,
  2576.        T.BLOCKING_SESSION,
  2577.        T.BLOCKING_SESSION_SERIAL#,
  2578.        T.LAST_CALL_ET,
  2579.        T.SQL_ID,
  2580.        T.SQL_EXEC_START,
  2581.        T.SQL_PLAN,
  2582.        T.LOGON_TIME,
  2583.              T.ASH_COUNTS,
  2584.        T.SESSION_INFO,
  2585.        '执行计划中预估行数超大【' || T.PLAN_CARDINALITY || '】' MONITOR_TYPES
  2586.   FROM TMPS T
  2587.  WHERE T.PLAN_CARDINALITY > 30748908521460
  2588.    AND (nvl(PLAN_DEPTH,1)=1)

  2590.  UNION ALL
  2591. ------------------------------------------ SQL请求并行数过多


  2594. SELECT T.INST_ID,
  2595.        T.SID,
  2596.        T.SERIAL#,
  2597.        T.SPID,
  2598.        T.OSUSER,
  2599.        T.USERNAME,
  2600.        T.EVENT,
  2601.        T.SESSION_STATE,
  2602.        T.SQL_TEXT,
  2603.        T.EXECUTIONS,
  2604.        T.ELAPSED_TIME_S,
  2605.        T.CPU_TIME,
  2606.        T.USER_IO_WAIT_TIME,
  2607.        T.BUFFER_GETS,
  2608.        T.PLAN_OPERATION,
  2609.        T.STARTS,
  2610.        T.PLAN_PARTITION_START,
  2611.        T.PLAN_PARTITION_STOP,
  2612.        T.PHYSICAL_READ_BYTES,
  2613.        T.PHYSICAL_WRITE_BYTES,
  2614.        T.BLOCKING_INSTANCE,
  2615.        T.BLOCKING_SESSION,
  2616.        T.BLOCKING_SESSION_SERIAL#,
  2617.        T.LAST_CALL_ET,
  2618.        T.SQL_ID,
  2619.        T.SQL_EXEC_START,
  2620.        T.SQL_PLAN,
  2621.        T.LOGON_TIME,
  2622.              T.ASH_COUNTS,
  2623.        T.SESSION_INFO,
  2624.        'SQL请求并行数过多【' || PX_MAXDOP || '】' MONITOR_TYPES
  2625.   FROM TMPS T
  2626.  WHERE T.PX_MAXDOP>=8
  2627.     AND (nvl(PLAN_DEPTH,1)=1)


  2630. UNION ALL
  2631. ------------------------------------------ 系统预估的剩余执行时间过长

  2633. SELECT T.INST_ID,
  2634.        T.SID,
  2635.        T.SERIAL#,
  2636.        T.SPID,
  2637.        T.OSUSER,
  2638.        T.USERNAME,
  2639.        T.EVENT,
  2640.        T.SESSION_STATE,
  2641.        T.SQL_TEXT,
  2642.        T.EXECUTIONS,
  2643.        T.ELAPSED_TIME_S,
  2644.        T.CPU_TIME,
  2645.        T.USER_IO_WAIT_TIME,
  2646.        T.BUFFER_GETS,
  2647.        T.PLAN_OPERATION,
  2648.        T.STARTS,
  2649.        T.PLAN_PARTITION_START,
  2650.        T.PLAN_PARTITION_STOP,
  2651.        T.PHYSICAL_READ_BYTES,
  2652.        T.PHYSICAL_WRITE_BYTES,
  2653.        T.BLOCKING_INSTANCE,
  2654.        T.BLOCKING_SESSION,
  2655.        T.BLOCKING_SESSION_SERIAL#,
  2656.        T.LAST_CALL_ET,
  2657.        T.SQL_ID,
  2658.        T.SQL_EXEC_START,
  2659.        T.SQL_PLAN,
  2660.        T.LOGON_TIME,
  2661.              T.ASH_COUNTS,
  2662.        T.SESSION_INFO,
  2663.        '系统预估的剩余执行时间过长【' || ROUND(D.TIME_REMAINING) || '】' MONITOR_TYPES
  2664.   FROM TMPS T, GV$SESSION_LONGOPS D
  2665.  WHERE T.SQL_EXEC_ID = D.SQL_EXEC_ID
  2666.    AND T.SID = D.SID
  2667.    AND T.SERIAL# = D.SERIAL#
  2668.    AND D.TIME_REMAINING > 10
  2669.    AND T.INST_ID = D.INST_ID
  2670.    AND D.TIME_REMAINING >0
  2671.       AND (nvl(PLAN_DEPTH,1)=1)

  2673.  UNION ALL
  2674.  ------------------------------------------ 等待事件异常

  2676. SELECT T.INST_ID,
  2677.        T.SID,
  2678.        T.SERIAL#,
  2679.        T.SPID,
  2680.        T.OSUSER,
  2681.        T.USERNAME,
  2682.        T.EVENT,
  2683.        T.SESSION_STATE,
  2684.        T.SQL_TEXT,
  2685.        T.EXECUTIONS,
  2686.        T.ELAPSED_TIME_S,
  2687.        T.CPU_TIME,
  2688.        T.USER_IO_WAIT_TIME,
  2689.        T.BUFFER_GETS,
  2690.        T.PLAN_OPERATION,
  2691.        T.STARTS,
  2692.        T.PLAN_PARTITION_START,
  2693.        T.PLAN_PARTITION_STOP,
  2694.        T.PHYSICAL_READ_BYTES,
  2695.        T.PHYSICAL_WRITE_BYTES,
  2696.        T.BLOCKING_INSTANCE,
  2697.        T.BLOCKING_SESSION,
  2698.        T.BLOCKING_SESSION_SERIAL#,
  2699.        T.LAST_CALL_ET,
  2700.        T.SQL_ID,
  2701.        T.SQL_EXEC_START,
  2702.        T.SQL_PLAN,
  2703.        T.LOGON_TIME,
  2704.              T.ASH_COUNTS,
  2705.        T.SESSION_INFO,
  2706.        '等待事件异常【' || T.EVENT || '】' MONITOR_TYPES
  2707.   FROM TMPS T
  2708.  WHERE T.EVENT NOT IN ('db file sequential read', 'db file scattered read','db file parallel write','db file parallel read')
  2709.    AND (nvl(PLAN_DEPTH,1)=1)


  2712.  UNION ALL
  2713. ------------------------------------------ TMP表空间占用过大

  2715. SELECT T.INST_ID,
  2716.        T.SID,
  2717.        T.SERIAL#,
  2718.        T.SPID,
  2719.        T.OSUSER,
  2720.        T.USERNAME,
  2721.        T.EVENT,
  2722.        T.SESSION_STATE,
  2723.        T.SQL_TEXT,
  2724.        T.EXECUTIONS,
  2725.        T.ELAPSED_TIME_S,
  2726.        T.CPU_TIME,
  2727.        T.USER_IO_WAIT_TIME,
  2728.        T.BUFFER_GETS,
  2729.        T.PLAN_OPERATION,
  2730.        T.STARTS,
  2731.        T.PLAN_PARTITION_START,
  2732.        T.PLAN_PARTITION_STOP,
  2733.        T.PHYSICAL_READ_BYTES,
  2734.        T.PHYSICAL_WRITE_BYTES,
  2735.        T.BLOCKING_INSTANCE,
  2736.        T.BLOCKING_SESSION,
  2737.        T.BLOCKING_SESSION_SERIAL#,
  2738.        T.LAST_CALL_ET,
  2739.        T.SQL_ID,
  2740.        T.SQL_EXEC_START,
  2741.        T.SQL_PLAN,
  2742.        T.LOGON_TIME,
  2743.              T.ASH_COUNTS,
  2744.        T.SESSION_INFO,
  2745.        'SQL占用TMP表空间过大【' || C.BYTES || '】Bytes' MONITOR_TYPES
  2746.   FROM TMPS T,
  2747.        (SELECT A.INST_ID, A.SESSION_ADDR, SUM(A.BLOCKS) * 8 * 1024 BYTES
  2748.           FROM GV$TEMPSEG_USAGE A
  2749.          GROUP BY A.INST_ID, A.SESSION_ADDR) C
  2750.  WHERE C.SESSION_ADDR = T.SADDR
  2751.    AND C.INST_ID = T.INST_ID
  2752.    AND C.BYTES > 10 --50 * 1024 * 1024 * 1024
  2753.    AND (nvl(PLAN_DEPTH,1)=1)

  2755. UNION ALL
  2756. ----------------------------------------- SQL占用UNDO过大，INACTIVE的会话也可能占用UNDO，但是这里只记录正在运行的SQL语句

  2758. SELECT T.INST_ID,
  2759.        T.SID,
  2760.        T.SERIAL#,
  2761.        T.SPID,
  2762.        T.OSUSER,
  2763.        T.USERNAME,
  2764.        T.EVENT,
  2765.        T.SESSION_STATE,
  2766.        T.SQL_TEXT,
  2767.        T.EXECUTIONS,
  2768.        T.ELAPSED_TIME_S,
  2769.        T.CPU_TIME,
  2770.        T.USER_IO_WAIT_TIME,
  2771.        T.BUFFER_GETS,
  2772.        T.PLAN_OPERATION,
  2773.        T.STARTS,
  2774.        T.PLAN_PARTITION_START,
  2775.        T.PLAN_PARTITION_STOP,
  2776.        T.PHYSICAL_READ_BYTES,
  2777.        T.PHYSICAL_WRITE_BYTES,
  2778.        T.BLOCKING_INSTANCE,
  2779.        T.BLOCKING_SESSION,
  2780.        T.BLOCKING_SESSION_SERIAL#,
  2781.        T.LAST_CALL_ET,
  2782.        T.SQL_ID,
  2783.        T.SQL_EXEC_START,
  2784.        T.SQL_PLAN,
  2785.        T.LOGON_TIME,
  2786.              T.ASH_COUNTS,
  2787.        T.SESSION_INFO,
  2788.        'SQL占用UNDO过大【' || USED_SIZE_BYTES || '】Bytes' MONITOR_TYPES
  2789.   FROM TMPS T,
  2790.        (SELECT ST.ADDR,
  2791.                ST.INST_ID,
  2792.                (ST.USED_UBLK * 8 * 1024) USED_SIZE_BYTES
  2793.           FROM GV$TRANSACTION ST, V$ROLLNAME R, GV$ROLLSTAT G
  2794.          WHERE ST.XIDUSN = R.USN
  2795.            AND R.USN = G.USN
  2796.            AND G.INST_ID = ST.INST_ID) V1
  2797.  WHERE V1.ADDR = T.TADDR
  2798.    AND T.INST_ID = V1.INST_ID
  2799.    AND USED_SIZE_BYTES > 1024 -- 50 * 1024 * 1024 * 1024

  2801. UNION ALL
  2802. ----------------------------------------- 耗费性能SQL


  2805. SELECT T.INST_ID,
  2806.        T.SID,
  2807.        T.SERIAL#,
  2808.        T.SPID,
  2809.        T.OSUSER,
  2810.        T.USERNAME,
  2811.        T.EVENT,
  2812.        T.SESSION_STATE,
  2813.        T.SQL_TEXT,
  2814.        T.EXECUTIONS,
  2815.        T.ELAPSED_TIME_S,
  2816.        T.CPU_TIME,
  2817.        T.USER_IO_WAIT_TIME,
  2818.        T.BUFFER_GETS,
  2819.        T.PLAN_OPERATION,
  2820.        T.STARTS,
  2821.        T.PLAN_PARTITION_START,
  2822.        T.PLAN_PARTITION_STOP,
  2823.        T.PHYSICAL_READ_BYTES,
  2824.        T.PHYSICAL_WRITE_BYTES,
  2825.        T.BLOCKING_INSTANCE,
  2826.        T.BLOCKING_SESSION,
  2827.        T.BLOCKING_SESSION_SERIAL#,
  2828.        T.LAST_CALL_ET,
  2829.        T.SQL_ID,
  2830.        T.SQL_EXEC_START,
  2831.        T.SQL_PLAN,
  2832.        T.LOGON_TIME,
  2833.              T.ASH_COUNTS,
  2834.        T.SESSION_INFO,
  2835.        'ASH捕获的次数【' || T.ASH_COUNTS || '】【'||SESSION_STATE||'】' MONITOR_TYPES
  2836.   FROM TMPS T
  2837. WHERE T.ASH_COUNTS>=4
  2838.    AND (nvl(PLAN_DEPTH,1)=1)
  2839.  ORDER BY SQL_EXEC_START DESC
  2840. ;




看看之前的一些结果：




修改后的结果：

该脚本中有视图也有表。若想直接查询数据库耗费性能的SQL语句，可以直接使用视图VW_SQL_PP_LHR进行查询。若想查询历史记录，则可以通过表XB_SQL_MONITOR_PP_LHR来查询。另外，对于监控中使用的参数表为XB_SQL_PARAMETERS_LHR。JOB每次都会从该表中读取到配置参数的值，该表的查询结果如下图所示：

该脚本中有视图也有表。若想直接查询数据库耗费性能的SQL语句，可以直接使用视图VW_SQL_PP_LHR进行查询。若想查询历史记录，则可以通过表XB_SQL_MONITOR_PP_LHR来查询。另外，对于监控中使用的参数表为XB_SQL_PARAMETERS_LHR。JOB每次都会从该表中读取到配置参数的值，该表的查询结果如下图所示：


下面简单测试一下上边的监控脚本的效果。首先构造一个笛卡尔积连接的SQL，并开启并行。再构造一个锁等待的SQL。如下所示的3条SQL语句
① SELECT /*+ monitor parallel(20)*/
 COUNT(*)
  FROM DBA_OBJECTS A,
       DBA_OBJECTS B,
       DBA_OBJECTS C,
       DBA_OBJECTS D;
② UPDATE  /*+ MONITOR */ XB_SQL_MONITOR_LHR T SET T.SQL_TEXT='XXXXXXXXXXXX';
③ UPDATE  /*+ MONITOR */ XB_SQL_MONITOR_LHR T SET T.SQL_TEXT='XXXXXXXXXXXX';
让以上3条SQL在不同的会话运行，等待大约5分钟后然后查看监控效果。
首先查询视图VW_SQL_PP_LHR：



从结果可以很明显的看出，会话（28，583）在等待锁，而会话（133,437）阻塞了会话（28，583）。对于会话（29，1207）可以看出，由于开了20个并行，所以导致系统CPU不足，所有的会话均在等待CPU资源，而且该会话的SQL语句产生了笛卡尔积、并行数过多、COST花费过大等问题。
对于该JOB的性能，由于作者从多个方面做了优化，所以基本不影响数据库的运行。下面是该JOB的运行日志：
SELECT JRD.LOG_ID,
       JRD.JOB_NAME,
       N.JOB_CLASS,
      TO_CHAR(JRD.ACTUAL_START_DATE, 'YYYY-MM-DD HH24:MI:SS') ACTUAL_START_DATE,
       TO_CHAR(JRD.LOG_DATE, 'YYYY-MM-DD HH24:MI:SS') LOG_DATE,
       JRD.STATUS,
       JRD.ERROR#,
       JRD.RUN_DURATION 运行时长,
       JRD.ADDITIONAL_INFO
  FROM DBA_SCHEDULER_JOB_LOG N, DBA_SCHEDULER_JOB_RUN_DETAILS JRD
 WHERE N.LOG_ID = JRD.LOG_ID 
   AND N.JOB_NAME LIKE 'JOB_SQL_%' 
 ORDER BY JRD.LOG_ID DESC;
JOB运行日志如下图所示：


查询监控表XB_SQL_MONITOR_PP_LHR也可获取相应的监控信息，这里不再演示。



About Me

...............................................................................................................................

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-1262559/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599     微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-04-01 09:00 ~ 2017-04-03 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。


DBA笔试面试讲解
 欢迎与我联系

请使用浏览器的分享功能分享到微信等