【SQL監控】SQL完全監控的指令碼

路途中的人2012發表於2017-05-12
SQL指令碼

【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群，學習最實用的資料庫技術。

【SQL監控】SQL完全監控的指令碼
 DBA筆試面試講解
 歡迎與我聯絡

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1262559/，如需轉載，請註明出處，否則將追究法律責任。

相關文章