视图 vw_sql_rubbish_monitor_lhr 的内容
Tags： Oraclevw_sql_rubbish_monitor_lhr监控
👉 本文共约80个字，系统预计阅读时间或需1分钟。
不带包
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 | CREATE OR REPLACE VIEW VW_SQL_RUBBISH_MONITOR_LHR AS SELECT A.SID, C.SERIAL#, (SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID, C.OSUSER, C.USERNAME, (SELECT B.SQL_TEXT FROM V$SQLAREA B WHERE B.SQL_ID = A.SQL_ID) SQL_TEXT, A.PLAN_OPERATION || ' ' || A.PLAN_OPTIONS PLAN_OPERATION, A.PLAN_PARTITION_START, A.PLAN_PARTITION_STOP, A.SQL_ID, A.SQL_EXEC_START, C.LOGON_TIME, (D.ELAPSED_TIME / 1000000) ELAPSED_TIME_S, 'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || A.SQL_ID || ''',' || 0 || ',''advanced''));' SQL_PLAN, '笛卡尔积监控' MONITOR_TYPES FROM V$SQL_PLAN_MONITOR A, V$SESSION C, V$SQL_MONITOR D WHERE A.PLAN_OPERATION = 'MERGE JOIN' AND A.PLAN_OPTIONS = 'CARTESIAN' AND A.SID = C.SID AND C.STATUS = 'ACTIVE' AND A.STATUS = 'EXECUTING' AND D.STATUS = 'EXECUTING' AND A.SID = D.SID AND A.KEY = D.KEY AND A.SQL_EXEC_ID = D.SQL_EXEC_ID AND C.USERNAME NOT IN ('SYS') UNION ------------------------------------------ SQL 执行时间超过5小时 SELECT A.SID, C.SERIAL#, (SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID, C.OSUSER, C.USERNAME, A.SQL_TEXT SQL_TEXT, '', '', '', A.SQL_ID, A.SQL_EXEC_START, C.LOGON_TIME, CASE WHEN A.STATUS = 'DONE(ERROR)' AND A.ERROR_MESSAGE IS NOT NULL THEN ((SELECT NB.ELAPSED_TIME FROM V$SQLAREA NB WHERE NB.SQL_ID = A.SQL_ID) / 1000000) ELSE (A.ELAPSED_TIME / 1000000) END AS ELAPSED_TIME, 'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || A.SQL_ID || ''',' || 0 || ',''advanced''));' SQL_PLAN, '执行时间超过5小时' MONITOR_TYPES FROM V$SQL_MONITOR A, V$SESSION C WHERE A.SQL_EXEC_ID = C.SQL_EXEC_ID AND A.SID = C.SID AND C.STATUS = 'ACTIVE' AND A.STATUS IN ('EXECUTING', 'DONE(ERROR)') AND A.ELAPSED_TIME > 18000000000 UNION ------------------------------------------ 分区表全分区扫描 SELECT A.SID, C.SERIAL#, (SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID, C.OSUSER, C.USERNAME, B.SQL_TEXT SQL_TEXT, A.PLAN_OPERATION || ' ' || A.PLAN_OPTIONS PLAN_OPERATION, A.PLAN_PARTITION_START, A.PLAN_PARTITION_STOP, A.SQL_ID, A.SQL_EXEC_START, C.LOGON_TIME, (B.ELAPSED_TIME / 1000000) ELAPSED_TIME, 'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || A.SQL_ID || ''',' || 0 || ',''advanced''));' SQL_PLAN, '分区表全分区扫描' MONITOR_TYPES FROM V$SQL_PLAN_MONITOR A, V$SQL_MONITOR B, V$SESSION C WHERE A.PLAN_OPERATION LIKE 'PARTITION%' AND A.PLAN_OPTIONS = 'ALL' AND A.SID = B.SID AND A.KEY = B.KEY AND A.SQL_EXEC_ID = B.SQL_EXEC_ID AND A.SQL_ID = B.SQL_ID AND A.SID = C.SID(+) AND A.STATUS = 'EXECUTING' AND B.ELAPSED_TIME >= 1000000 * 60 UNION ------------------------------------------ 执行计划中cost花费或者预估行数超大 SELECT A.SID, C.SERIAL#, (SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID, C.OSUSER, C.USERNAME, B.SQL_TEXT SQL_TEXT, A.PLAN_OPERATION || ' ' || A.PLAN_OPTIONS PLAN_OPERATION, A.PLAN_PARTITION_START, A.PLAN_PARTITION_STOP, A.SQL_ID, A.SQL_EXEC_START, C.LOGON_TIME, (B.ELAPSED_TIME / 1000000) ELAPSED_TIME, 'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || A.SQL_ID || ''',' || 0 || ',''advanced''));' SQL_PLAN, '执行计划中cost花费或者预估行数超大' MONITOR_TYPES FROM V$SQL_PLAN_MONITOR A, V$SQL_MONITOR B, V$SESSION C WHERE A.SID = B.SID AND A.KEY = B.KEY AND A.SQL_EXEC_ID = B.SQL_EXEC_ID AND A.SQL_ID = B.SQL_ID AND A.SID = C.SID(+) AND A.STATUS = 'EXECUTING' AND (A.PLAN_COST >= 3107523095 OR A.PLAN_CARDINALITY > 30748908521460) UNION ------------------------------------------ tmp 表空间占用过大 SELECT C.SID, C.SERIAL#, (SELECT PR.SPID FROM V$PROCESS PR WHERE C.PADDR = PR.ADDR) SPID, C.OSUSER, C.USERNAME, (SELECT NB.SQL_TEXT FROM V$SQLAREA NB WHERE NB.SQL_ID = C.SQL_ID) SQL_TEXT, '' PLAN_OPERATION, '', '', C.SQL_ID, C.SQL_EXEC_START, C.LOGON_TIME, ((SELECT NB.ELAPSED_TIME FROM V$SQLAREA NB WHERE NB.SQL_ID = C.SQL_ID) / 1000000) ELAPSED_TIME, 'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || C.SQL_ID || ''',' || 0 || ',''advanced''));' SQL_PLAN, ' TMP 表空间占用过大 【' || C.BYTES || 'BYTES】' MONITOR_TYPES FROM (SELECT A.SESSION_ADDR, B.SID, B.SERIAL#, B.PADDR, B.OSUSER, B.USERNAME, B.SQL_ID, B.SQL_EXEC_START, B.LOGON_TIME, SUM(A.BLOCKS) * 8 * 1024 BYTES FROM V$TEMPSEG_USAGE A, V$SESSION B WHERE A.SESSION_ADDR = B.SADDR AND B.STATUS = 'ACTIVE' GROUP BY A.SESSION_ADDR, B.SID, B.SERIAL#, B.PADDR, B.OSUSER, B.USERNAME, B.SQL_ID, B.SQL_EXEC_START, B.LOGON_TIME) C WHERE C.BYTES > 50 * 1024 * 1024 * 1024 UNION ----------------------------------------- SQL占用undo过大 SELECT S.SID, S.SERIAL#, (SELECT PR.SPID FROM V$PROCESS PR WHERE S.PADDR = PR.ADDR) SPID, S.OSUSER, S.USERNAME, (SELECT NB.SQL_TEXT FROM V$SQLAREA NB WHERE NB.SQL_ID = S.SQL_ID AND ROWNUM = 1) SQL_TEXT, '' PLAN_OPERATION, '', '', S.SQL_ID, TO_DATE(''), S.LOGON_TIME, ((SELECT NB.ELAPSED_TIME FROM V$SQLAREA NB WHERE NB.SQL_ID = S.SQL_ID) / 1000000) ELAPSED_TIME, 'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || S.SQL_ID || ''',' || 0 || ',''advanced''));' SQL_PLAN, 'SQL占用undo过大【' || (T.USED_UBLK * TO_NUMBER((SELECT A.VALUE FROM V$PARAMETER A WHERE A.NAME = 'db_block_size'))) || 'BYTES】' MONITOR_TYPES FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R, V$ROLLSTAT G WHERE T.ADDR = S.TADDR AND T.XIDUSN = R.USN AND R.USN = G.USN AND (T.USED_UBLK * TO_NUMBER((SELECT A.VALUE FROM V$PARAMETER A WHERE A.NAME = 'db_block_size'))) > 50 * 1024 * 1024 * 1024 ORDER BY SQL_EXEC_START DESC ; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
