有网友询问我的这篇blog http://blog.itpub.net/26736162/viewspace-1218671/ 中的视图 vw_sql_rubbish_monitor_lhr 的内容，我在这里列举一下，大家共同学习吧。
SET SQLBLANKLINES ON
--------------------------------- 历史SQL记录
DROP TABLE XB_SQL_MONITOR_LHR PURGE;
CREATE TABLE XB_SQL_MONITOR_LHR
(
ID INTEGER PRIMARY KEY,
INST_ID NUMBER,
SID NUMBER,
SERIAL# NUMBER,
SPID NUMBER,
SQL_ID VARCHAR2(13),
SQL_TEXT VARCHAR2(4000),
SQL_FULLTEXT CLOB,
SQL_EXEC_START DATE,
SQL_EXEC_ID NUMBER,
COMMAND_TYPE VARCHAR2(20),
ELAPSED_TIME NUMBER,
ELAPSED_TIME2 VARCHAR2(30),
STATUS VARCHAR2(19),
USERNAME VARCHAR2(30),
OS_USER VARCHAR2(30),
SESSION_TYPES VARCHAR2(4000),
LAST_LOAD_TIME DATE,
LAST_ACTIVE_TIME DATE,
EXECUTIONS NUMBER ,
PX_QCSID NUMBER,
CPU_TIME NUMBER,
FETCHES NUMBER,
BUFFER_GETS NUMBER,
DISK_READS NUMBER,
DIRECT_WRITES NUMBER,
BINDS_XML CLOB,
USER_IO_WAIT_TIME NUMBER,
CONCURRENCY_WAIT_TIME NUMBER,
PHYSICAL_READ_BYTES NUMBER,
PHYSICAL_WRITE_BYTES NUMBER,
KEY NUMBER,
PLAN_OBJECT_OWNER VARCHAR2(50),
PLAN_OBJECT_NAME VARCHAR2(50),
IN_DATE DATE
) NOLOGGING
PARTITION BY RANGE(IN_DATE)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION P201704 VALUES LESS THAN(TO_DATE('201705','YYYYMM')));
DROP SEQUENCE S_XB_SQL_MONITOR_LHR;
CREATE SEQUENCE S_XB_SQL_MONITOR_LHR START WITH 1 INCREMENT BY 1 CACHE 1000;
SELECT S_XB_SQL_MONITOR_LHR.NEXTVAL FROM DUAL;
CREATE INDEX IND_SQL_MONITOR_SQLID ON XB_SQL_MONITOR_LHR(SQL_ID) LOCAL NOLOGGING;
CREATE INDEX IND_SQL_MONITOR_SID ON XB_SQL_MONITOR_LHR(SID,SERIAL#,SPID) LOCAL NOLOGGING;
CREATE INDEX IND_SQL_MONITOR_IN_DATE ON XB_SQL_MONITOR_LHR(IN_DATE,COMMAND_TYPE,PLAN_OBJECT_NAME) LOCAL NOLOGGING;
COMMENT ON TABLE XB_SQL_MONITOR_LHR IS '历史SQL监控';
COMMENT ON COLUMN XB_SQL_MONITOR_LHR.SQL_EXEC_START IS 'SQL语句开始执行时间';
COMMENT ON COLUMN XB_SQL_MONITOR_LHR.ELAPSED_TIME IS 'SQL语句执行时间（微秒）';
COMMENT ON COLUMN XB_SQL_MONITOR_LHR.EXECUTIONS IS 'SQL语句执行次数';
GRANT SELECT ON XB_SQL_MONITOR_LHR TO PUBLIC;
--------------------------------- 历史SQL执行计划记录
DROP TABLE XB_SQL_PLAN_MONITOR_LHR PURGE;
CREATE TABLE XB_SQL_PLAN_MONITOR_LHR (
ID NUMBER PRIMARY KEY,
INST_ID NUMBER,
SQL_MONITOR_ID NUMBER,
KEY NUMBER,
STATUS VARCHAR2(25),
SID NUMBER,
SERIAL# NUMBER,
SPID NUMBER,
SQL_ID VARCHAR2(25),
SQL_EXEC_START DATE,
SQL_EXEC_ID NUMBER,
SQL_PLAN_HASH_VALUE NUMBER ,
SQL_CHILD_ADDRESS RAW(8),
CHILD_NUMBER NUMBER,
PLAN_PARENT_ID NUMBER,
PLAN_LINE_ID NUMBER,
PLAN_OPERATION VARCHAR2(30),
PLAN_OPTIONS VARCHAR2(30),
OPTIMIZER VARCHAR2(80),
OBJECT# NUMBER,
PLAN_OBJECT_OWNER VARCHAR2(30),
PLAN_OBJECT_NAME VARCHAR2(30),
PLAN_OBJECT_TYPE VARCHAR2(40),
OBJECT_ALIAS VARCHAR2(80),
PLAN_DEPTH NUMBER,
PLAN_POSITION NUMBER,
PLAN_COST NUMBER,
PLAN_CARDINALITY NUMBER,
PLAN_BYTES NUMBER,
PLAN_TIME NUMBER,
PLAN_PARTITION_START VARCHAR2(255) ,
PLAN_PARTITION_STOP VARCHAR2(255),
PLAN_CPU_COST NUMBER,
PLAN_IO_COST NUMBER,
PLAN_TEMP_SPACE NUMBER,
STARTS NUMBER,
OUTPUT_ROWS NUMBER,
IO_INTERCONNECT_BYTES NUMBER,
PHYSICAL_READ_REQUESTS NUMBER,
PHYSICAL_READ_BYTES NUMBER,
PHYSICAL_WRITE_REQUESTS NUMBER,
PHYSICAL_WRITE_BYTES NUMBER,
SEARCH_COLUMNS NUMBER,
FILTER_PREDICATES VARCHAR2(4000) ,
ACCESS_PREDICATES VARCHAR2(4000) ,
PROJECTION VARCHAR2(4000) ,
OTHER_XML CLOB,
IN_DATE DATE
) NOLOGGING
PARTITION BY RANGE(IN_DATE)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH') )
(PARTITION P201704 VALUES LESS THAN (TO_DATE('201705', 'YYYYMM'))
);
DROP SEQUENCE S_XB_SQL_PLAN_MONITOR_LHR;
CREATE SEQUENCE S_XB_SQL_PLAN_MONITOR_LHR START WITH 1 INCREMENT BY 1 CACHE 1000;
SELECT S_XB_SQL_PLAN_MONITOR_LHR.NEXTVAL FROM DUAL;
CREATE INDEX IND_SQL_MONITOR_ID ON XB_SQL_PLAN_MONITOR_LHR(SQL_MONITOR_ID) NOLOGGING;
CREATE INDEX IND_SQL_PLAN_MONITOR_IN_DATE ON XB_SQL_PLAN_MONITOR_LHR(IN_DATE) LOCAL NOLOGGING;
CREATE INDEX IND_SMONITOR_SQLIDSIDKEY ON XB_SQL_PLAN_MONITOR_LHR(SQL_ID,SID,SERIAL#,SPID,KEY) LOCAL NOLOGGING;
--------监控正在运行的SQL语句
DROP TABLE XB_SQL_MONITOR_PP_LHR;
-- Create table
CREATE TABLE XB_SQL_MONITOR_PP_LHR
(
ID NUMBER NOT NULL,
INST_ID NUMBER,
SID NUMBER,
SERIAL# NUMBER,
SPID VARCHAR2(24),
OSUSER VARCHAR2(30),
USERNAME VARCHAR2(30),
SQL_TEXT VARCHAR2(4000),
SQL_FULLTEXT CLOB,
PLAN_OPERATION VARCHAR2(61),
STARTS NUMBER,
PLAN_PARTITION_START VARCHAR2(128),
PLAN_PARTITION_STOP VARCHAR2(128),
EXECUTIONS NUMBER,
SQL_ID VARCHAR2(13),
SQL_EXEC_START DATE,
LOGON_TIME DATE,
LAST_LOAD_TIME DATE,
LAST_ACTIVE_TIME DATE,
ELAPSED_TIME VARCHAR2(500),
ELAPSED_TIME1 NUMBER,
MONITOR_TYPES VARCHAR2(500),
MONITOR_TYPES1 NUMBER,
MONITOR_VALUE NUMBER,
TUNING_RESULT CLOB,
TUNING_TIME DATE,
SESSION_INFO VARCHAR2(4000),
SESSION_STATE VARCHAR2(30),
EVENT VARCHAR2(4000),
CPU_TIME NUMBER,
BUFFER_GETS NUMBER,
PHYSICAL_READ_BYTES NUMBER,
PHYSICAL_WRITE_BYTES NUMBER,
USER_IO_WAIT_TIME NUMBER,
BLOCKING_INSTANCE NUMBER,
BLOCKING_SESSION NUMBER,
LAST_CALL_ET NUMBER,
ASH_COUNTS NUMBER,
IN_DATE DATE
) NOLOGGING
PARTITION BY RANGE (IN_DATE) INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION P201704 VALUES LESS THAN (TO_DATE('201705', 'YYYYMM'))
);
CREATE INDEX IND_SQL_MONITOR_PPID ON XB_SQL_MONITOR_PP_LHR(SQL_ID) LOCAL NOLOGGING;
CREATE INDEX IND_SQL_MONITOR_PP_DATE ON XB_SQL_MONITOR_PP_LHR(IN_DATE) LOCAL NOLOGGING;
DROP SEQUENCE S_XB_SQL_MONITOR_PP_LHR;
CREATE SEQUENCE S_XB_SQL_MONITOR_PP_LHR START WITH 1 INCREMENT BY 1 CACHE 1000;
DROP TABLE XB_SQL_PARAMETERS_LHR;
CREATE TABLE XB_SQL_PARAMETERS_LHR
( ID NUMBER PRIMARY KEY,
CN_NAME VARCHAR2(100) NOT NULL,
PARAM_NAME VARCHAR2(50) NOT NULL,
PARAM_TYPE VARCHAR2(50) ,
PARAM_VALUE VARCHAR2(50) ,
PARAM_UNIT VARCHAR2(50) ,
COMMENTS VARCHAR2(500)
) NOLOGGING CACHE ;
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (1, 'SQL占用UNDO表空间过大', 'V_UNDOSIZE', 'NUMBER', '52428800', 'BYTES', '单条SQL占用的UNDO表空间大小');
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (2, 'SQL占用TMP表空间过大', 'V_TMPSIZE', 'NUMBER', '15204352', 'BYTES', '单条SQL占用的临时表空间大小');
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (3, '执行计划COST花费过大', 'V_PLAN_COST', 'NUMBER', '114', NULL, 'SQL执行计划中的COST花费，参照值，无单位');
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (4, '执行计划预估行数过大', 'V_PLAN_CARDINALITY', 'NUMBER', '1426', '行', 'SQL执行计划中的预估行数');
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (5, 'SQL执行时间过大', 'V_ELAPSED_TIME', 'NUMBER', '29', '秒', 'SQL执行时间，单位为秒，1秒等于1000000微秒');
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (6, 'SQL执行次数过大', 'V_EXECUTIONS', 'NUMBER', '7616', '次', 'SQL执行次数');
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (7, 'SQL监控时间间隔', 'V_INTERVALTIME', 'NUMBER', '30', '秒', 'SQL监控时间间隔，最小值20秒，最大值120秒，默认30秒，推荐30秒');
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (8, '笛卡尔积SQL监控', 'V_MERGEJOIN', NULL, NULL, NULL, 'SQL形成笛卡尔积');
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (9, '分区表全分区扫描', 'V_PARTTABLESCAN', NULL, NULL, NULL, '分区表全分区扫描');
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (13, 'SQL并行个数过大', 'V_PARALLEL', 'NUMBER', 8, '', 'SQL开并行的最大并行个数');
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (14, '系统预估的剩余执行时间过长', 'V_ESTIMATE_TIME', 'NUMBER', 900, '秒', '系统预估的剩余执行时间过长');
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (15, '逻辑读过大', 'V_LOGICAL_READS', 'NUMBER', 1510407, '', '逻辑读过大');
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (16, '物理读过大', 'V_DISK_READS', 'NUMBER', 1510407, '', '物理读过大');
INSERT INTO XB_SQL_PARAMETERS_LHR (ID, CN_NAME, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, PARAM_UNIT, COMMENTS)
VALUES (17, '等待事件异常', 'V_WAIT_EVENT', '', '', '', '等待事件异常的SQL语句');
COMMIT;
--@@PKG_SQL_MONITOR_LHR.PCK
--------------------------------------------------------------
CREATE OR REPLACE PACKAGE PKG_SQL_MONITOR_LHR AUTHID CURRENT_USER AS
-----------------------------------------------------------------------------------
-- CREATED ON 2014-07-15 12:19:12 BY LHR
--CHANGED ON 2013-07-1512:19:12 BY LHR
-- FUNCTION： 该包记录所有历史SQL语句
---主要采用了 V$SQL_MONITOR 和 V$SQL_PLAN_MONITOR 2个视图
-----------------------------------------------------------------------------------
-----------------------------存过--------------------------------------
--已经执行完毕的SQL
PROCEDURE P_SQL_DONE_LHR;
-- EXECUTING POOR PERFORMANCE 正在执行的性能差的SQL
PROCEDURE P_SQL_EPP_LHR;
PROCEDURE P_SQL_EPP2_LHR;
PROCEDURE P_TUNING_SQL;
PROCEDURE P_GET_PPSQL_PARAMETER;
------------------------------函数-------------------------------------
FUNCTION F_GET_RIGHT_SIZE_LHR(P_SIZE IN VARCHAR2) RETURN VARCHAR2;
FUNCTION F_GET_TOTAL_TIME_LHR(P_TOTAL_SECONDS NUMBER,
P_FLAG VARCHAR2 DEFAULT 'S')
RETURN VARCHAR2 DETERMINISTIC;
---得到当前SQL语句操作的对象
FUNCTION F_GET_SQL_OBJECT_LHR(P_SQL VARCHAR2,
P_COMMAND VARCHAR2 DEFAULT 'INTO',
PFLAG NUMBER DEFAULT 1) RETURN VARCHAR2;
-----得到当前SQL语句的命令类型
FUNCTION F_GET_SQL_COMMAND_LHR(P_SQL VARCHAR2) RETURN VARCHAR2;
END PKG_SQL_MONITOR_LHR;
/
CREATE OR REPLACE PACKAGE BODY PKG_SQL_MONITOR_LHR AS
PROCEDURE P_SQL_DONE_LHR AS
V_DATE DATE := SYSDATE;
V_COMMAND VARCHAR2(50);
V_SQL VARCHAR2(32767);
V_OSUSER VARCHAR2(255);
V_SPID NUMBER;
BEGIN
FOR CUR IN (SELECT S_XB_SQL_MONITOR_LHR.NEXTVAL ID,
A.SID,
A.SESSION_SERIAL# SERIAL#,
A.SQL_ID,
NVL(A.SQL_TEXT, NS.SQL_TEXT) SQL_TEXT,
DECODE(A.IS_FULL_SQLTEXT,
'N',
NS.SQL_FULLTEXT,
A.SQL_TEXT) SQL_FULLTEXT,
A.SQL_EXEC_START,
A.ELAPSED_TIME,
A.STATUS,
A.USERNAME,
CASE
WHEN A.ERROR_MESSAGE IS NOT NULL THEN
(A.MODULE || '--' || A.ACTION || '--' || A.PROGRAM || '--' ||
A.PROCESS_NAME || '--' || A.CLIENT_IDENTIFIER || '--' ||
A.CLIENT_INFO || '--' || A.SERVICE_NAME) || '----【' ||
A.ERROR_MESSAGE || '】'
ELSE
(A.MODULE || '--' || A.ACTION || '--' || A.PROGRAM || '--' ||
A.PROCESS_NAME || '--' || A.CLIENT_IDENTIFIER || '--' ||
A.CLIENT_INFO || '--' || A.SERVICE_NAME)
END AS SESSION_TYPES,
CASE
WHEN A.PX_QCSID IS NOT NULL THEN
A.PX_QCSID
WHEN A.PX_QCSID IS NULL AND
A.PX_SERVERS_ALLOCATED IS NOT NULL THEN
A.SID
END AS PX_QCSID,
(SELECT NC.COMMAND_NAME
FROM V$SQLCOMMAND NC
WHERE NS.COMMAND_TYPE = NC.COMMAND_TYPE) COMMAND_TYPE,
A.SQL_EXEC_ID,
F_GET_TOTAL_TIME_LHR(A.ELAPSED_TIME / 1000000) ELAPSED_TIME2,
A.PROCESS_NAME,
A.MODULE,
A.CPU_TIME,
A.FETCHES,
A.BUFFER_GETS,
A.DISK_READS,
A.DIRECT_WRITES,
A.BINDS_XML,
A.USER_IO_WAIT_TIME,
A.CONCURRENCY_WAIT_TIME,
A.PHYSICAL_READ_BYTES,
A.PHYSICAL_WRITE_BYTES,
A.KEY,
C.PLAN_OBJECT_OWNER,
C.PLAN_OBJECT_NAME,
A.INST_ID,
NS.EXECUTIONS EXECUTIONS,
NS.LAST_LOAD_TIME,
NS.LAST_ACTIVE_TIME
FROM (SELECT *
FROM GV$SQL_MONITOR NA
WHERE NA.STATUS LIKE 'DONE%'
AND NA.LAST_REFRESH_TIME >= SYSDATE - 1
AND NOT EXISTS
(SELECT 1
FROM XB_SQL_MONITOR_LHR B
WHERE NA.SID = B.SID
AND NA.SESSION_SERIAL# = B.SERIAL#
AND NA.SQL_ID = B.SQL_ID
AND NA.SQL_EXEC_ID = B.SQL_EXEC_ID
AND B.IN_DATE BETWEEN SYSDATE - 1 AND
SYSDATE
AND B.INST_ID = NA.INST_ID)) A
LEFT OUTER JOIN GV$SQL_PLAN_MONITOR C
ON (A.KEY = C.KEY AND A.SQL_ID = C.SQL_ID AND
C.PLAN_LINE_ID = 1 AND A.SQL_EXEC_ID = C.SQL_EXEC_ID AND
C.PLAN_OBJECT_OWNER IS NOT NULL AND
C.PLAN_OBJECT_NAME IS NOT NULL AND
C.PLAN_OPERATION IN ('UPDATE', 'DELETE', 'MERGE') AND
A.INST_ID = C.INST_ID)
LEFT OUTER JOIN GV$SQLAREA NS
ON (NS.SQL_ID = A.SQL_ID AND NS.INST_ID = A.INST_ID)) LOOP
V_SQL := SUBSTR(NVL(DBMS_LOB.SUBSTR(CUR.SQL_FULLTEXT, 3900, 1),
CUR.SQL_TEXT),
1,
3900);
------ SQL语句的命令类型
IF CUR.COMMAND_TYPE IS NULL AND V_SQL IS NOT NULL THEN
BEGIN
CUR.COMMAND_TYPE := F_GET_SQL_COMMAND_LHR(V_SQL);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
---- 找出正在操作的对象
IF (CUR.COMMAND_TYPE IN ('INSERT',
'MERGE INTO',
'CREATE TABLE',
'CREATE INDEX',
'ALTER TABLE',
'ALTER INDEX')) AND
(CUR.PLAN_OBJECT_OWNER IS NULL AND CUR.PLAN_OBJECT_NAME IS NULL) AND
V_SQL IS NOT NULL THEN
V_COMMAND := CASE
WHEN CUR.COMMAND_TYPE IN ('INSERT', 'MERGE INTO') THEN
'INTO'
WHEN CUR.COMMAND_TYPE IN ('CREATE TABLE', 'ALTER TABLE') THEN
'TABLE'
WHEN CUR.COMMAND_TYPE IN ('CREATE INDEX', 'ALTER INDEX') THEN
'INDEX'
END;
BEGIN
CUR.PLAN_OBJECT_OWNER := NVL(F_GET_SQL_OBJECT_LHR(V_SQL,
V_COMMAND,
2),
CUR.USERNAME);
CUR.PLAN_OBJECT_NAME := F_GET_SQL_OBJECT_LHR(V_SQL, V_COMMAND);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
INSERT INTO XB_SQL_MONITOR_LHR
(ID,
INST_ID,
SID,
SERIAL#,
SQL_ID,
SQL_TEXT,
SQL_FULLTEXT,
SQL_EXEC_START,
ELAPSED_TIME,
STATUS,
USERNAME,
SESSION_TYPES,
EXECUTIONS,
IN_DATE,
PX_QCSID,
COMMAND_TYPE,
SQL_EXEC_ID,
ELAPSED_TIME2,
OS_USER,
CPU_TIME,
FETCHES,
BUFFER_GETS,
DISK_READS,
DIRECT_WRITES,
BINDS_XML,
USER_IO_WAIT_TIME,
CONCURRENCY_WAIT_TIME,
PHYSICAL_READ_BYTES,
PHYSICAL_WRITE_BYTES,
KEY,
PLAN_OBJECT_OWNER,
PLAN_OBJECT_NAME,
SPID,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME)
VALUES
(CUR.ID,
CUR.INST_ID,
CUR.SID,
CUR.SERIAL#,
CUR.SQL_ID,
CUR.SQL_TEXT,
CUR.SQL_FULLTEXT,
CUR.SQL_EXEC_START,
CUR.ELAPSED_TIME,
CUR.STATUS,
CUR.USERNAME,
CUR.SESSION_TYPES,
CUR.EXECUTIONS,
V_DATE,
CUR.PX_QCSID,
CUR.COMMAND_TYPE,
CUR.SQL_EXEC_ID,
CUR.ELAPSED_TIME2,
V_OSUSER,
CUR.CPU_TIME,
CUR.FETCHES,
CUR.BUFFER_GETS,
CUR.DISK_READS,
CUR.DIRECT_WRITES,
CUR.BINDS_XML,
CUR.USER_IO_WAIT_TIME,
CUR.CONCURRENCY_WAIT_TIME,
CUR.PHYSICAL_READ_BYTES,
CUR.PHYSICAL_WRITE_BYTES,
CUR.KEY,
CUR.PLAN_OBJECT_OWNER,
CUR.PLAN_OBJECT_NAME,
V_SPID,
CUR.LAST_LOAD_TIME,
CUR.LAST_ACTIVE_TIME);
INSERT INTO XB_SQL_PLAN_MONITOR_LHR
(ID,
INST_ID,
SQL_MONITOR_ID,
KEY,
STATUS,
SID,
SQL_ID,
SQL_EXEC_START,
SQL_EXEC_ID,
SQL_PLAN_HASH_VALUE,
SQL_CHILD_ADDRESS,
PLAN_PARENT_ID,
PLAN_LINE_ID,
PLAN_OPERATION,
PLAN_OPTIONS,
PLAN_OBJECT_OWNER,
PLAN_OBJECT_NAME,
PLAN_OBJECT_TYPE,
PLAN_DEPTH,
PLAN_POSITION,
PLAN_COST,
PLAN_CARDINALITY,
PLAN_BYTES,
PLAN_TIME,
PLAN_PARTITION_START,
PLAN_PARTITION_STOP,
PLAN_CPU_COST,
PLAN_IO_COST,
PLAN_TEMP_SPACE,
STARTS,
OUTPUT_ROWS,
IO_INTERCONNECT_BYTES,
PHYSICAL_READ_REQUESTS,
PHYSICAL_READ_BYTES,
PHYSICAL_WRITE_REQUESTS,
PHYSICAL_WRITE_BYTES,
IN_DATE,
CHILD_NUMBER,
OBJECT#,
OBJECT_ALIAS,
OPTIMIZER,
SEARCH_COLUMNS,
FILTER_PREDICATES,
ACCESS_PREDICATES,
PROJECTION,
OTHER_XML,
SERIAL#,
SPID)
SELECT S_XB_SQL_PLAN_MONITOR_LHR.NEXTVAL ID,
CUR.INST_ID,
(SELECT NB.ID
FROM XB_SQL_MONITOR_LHR NB
WHERE NB.KEY = A.KEY
AND NB.SID = CUR.SID
AND NB.SQL_EXEC_ID = NB.SQL_EXEC_ID
AND NB.IN_DATE = V_DATE
AND NB.SQL_ID = CUR.SQL_ID
AND NB.INST_ID = CUR.INST_ID) SQL_MONITOR_ID,
KEY,
STATUS,
SID,
A.SQL_ID,
SQL_EXEC_START,
SQL_EXEC_ID,
SQL_PLAN_HASH_VALUE,
SQL_CHILD_ADDRESS,
PLAN_PARENT_ID,
PLAN_LINE_ID,
PLAN_OPERATION,
PLAN_OPTIONS,
PLAN_OBJECT_OWNER,
PLAN_OBJECT_NAME,
PLAN_OBJECT_TYPE,
PLAN_DEPTH,
PLAN_POSITION,
NVL(A.PLAN_COST, B.COST) COST,
PLAN_CARDINALITY,
PLAN_BYTES,
PLAN_TIME,
PLAN_PARTITION_START,
PLAN_PARTITION_STOP,
PLAN_CPU_COST,
PLAN_IO_COST,
PLAN_TEMP_SPACE,
STARTS,
OUTPUT_ROWS,
IO_INTERCONNECT_BYTES,
PHYSICAL_READ_REQUESTS,
PHYSICAL_READ_BYTES,
PHYSICAL_WRITE_REQUESTS,
PHYSICAL_WRITE_BYTES,
V_DATE IN_DATE,
B.CHILD_NUMBER,
B.OBJECT#,
B.OBJECT_ALIAS,
B.OPTIMIZER,
B.SEARCH_COLUMNS,
B.FILTER_PREDICATES,
B.ACCESS_PREDICATES,
B.PROJECTION,
B.OTHER_XML,
CUR.SERIAL#,
V_SPID
FROM GV$SQL_PLAN_MONITOR A
LEFT OUTER JOIN GV$SQL_PLAN B
ON (A.SQL_ID = B.SQL_ID AND
A.SQL_PLAN_HASH_VALUE = B.PLAN_HASH_VALUE AND
A.SQL_CHILD_ADDRESS = B.CHILD_ADDRESS AND
A.PLAN_LINE_ID = B.ID AND B.SQL_ID = CUR.SQL_ID AND
A.INST_ID = B.INST_ID)
WHERE A.STATUS LIKE 'DONE%'
AND A.SQL_ID NOT IN
(SELECT BB.SQL_ID
FROM XB_SQL_PLAN_MONITOR_LHR BB
WHERE BB.SID = CUR.SID
AND BB.KEY = CUR.KEY
AND BB.SQL_ID = CUR.SQL_ID
AND BB.SQL_EXEC_ID = CUR.SQL_EXEC_ID
AND BB.IN_DATE BETWEEN SYSDATE - 1 AND SYSDATE
AND BB.INST_ID = A.INST_ID)
AND A.SQL_ID = CUR.SQL_ID
AND A.KEY = CUR.KEY
AND A.SID = CUR.SID
AND A.SQL_EXEC_ID = CUR.SQL_EXEC_ID
AND A.INST_ID = CUR.INST_ID;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END P_SQL_DONE_LHR;
----------------------------------------------------------------------------------------------
PROCEDURE P_SQL_EPP_LHR AS
-----------------------------------------------------------------------------------
-- Created on 2015/11/21 16:49:28 by lhr
--Changed on 2017/04/03 16:49:28 by lhr
-- function： 监控正在执行的SQL
-----------------------------------------------------------------------------------
V_TMPSIZE NUMBER := 5368709120; --BYTES
V_UNDOSIZE NUMBER := 5368709120; --BYTES
V_PLAN_COST NUMBER := 3107523095; --COST 花费
V_PLAN_CARDINALITY NUMBER := 30748908521460; --预估行数
V_ELAPSED_TIME NUMBER := 10000000; ---执行时间，单位：微秒，例执行时间设置5小时，则： 5H*60*60*1000000 ，1秒等于1000000微秒
V_EXECUTIONS NUMBER := 1000; --执行次数
V_PARALLEL NUMBER := 4; --SQL开并行的个数
V_ESTIMATE_TIME NUMBER := 900; --系统预估的剩余执行时间过长
BEGIN
SELECT SUM(CASE T.PARAM_NAME
WHEN 'V_UNDOSIZE' THEN
T.PARAM_VALUE
END) V_UNDOSIZE,
SUM(CASE T.PARAM_NAME
WHEN 'V_TMPSIZE' THEN
T.PARAM_VALUE
END) V_TMPSIZE,
SUM(CASE T.PARAM_NAME
WHEN 'V_PLAN_COST' THEN
T.PARAM_VALUE
END) V_PLAN_COST,
SUM(CASE T.PARAM_NAME
WHEN 'V_PLAN_CARDINALITY' THEN
T.PARAM_VALUE
END) V_PLAN_CARDINALITY,
SUM(CASE T.PARAM_NAME
WHEN 'V_ELAPSED_TIME' THEN
T.PARAM_VALUE
END) V_ELAPSED_TIME,
SUM(CASE T.PARAM_NAME
WHEN 'V_EXECUTIONS' THEN
T.PARAM_VALUE
END) V_EXECUTIONS,
SUM(CASE T.PARAM_NAME
WHEN 'V_PARALLEL' THEN
T.PARAM_VALUE
END) V_PARALLEL,
SUM(CASE T.PARAM_NAME
WHEN 'V_ESTIMATE_TIME' THEN
T.PARAM_VALUE
END) V_ESTIMATE_TIME
INTO V_UNDOSIZE,
V_TMPSIZE,
V_PLAN_COST,
V_PLAN_CARDINALITY,
V_ELAPSED_TIME,
V_EXECUTIONS,
V_PARALLEL,
V_ESTIMATE_TIME
FROM XB_SQL_PARAMETERS_LHR T
WHERE T.PARAM_NAME IN ('V_UNDOSIZE',
'V_TMPSIZE',
'V_PLAN_COST',
'V_PLAN_CARDINALITY',
'V_ELAPSED_TIME',
'V_EXECUTIONS',
'V_PARALLEL',
'V_ESTIMATE_TIME');
--------------------------------------------- 垃圾SQL监控
INSERT INTO XB_SQL_MONITOR_PP_LHR
(ID,
INST_ID,
SID,
SERIAL#,
SPID,
OSUSER,
USERNAME,
SQL_TEXT,
SQL_FULLTEXT,
PLAN_OPERATION,
STARTS,
PLAN_PARTITION_START,
PLAN_PARTITION_STOP,
EXECUTIONS,
SQL_ID,
SQL_EXEC_START,
LOGON_TIME,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME,
ELAPSED_TIME,
ELAPSED_TIME1,
MONITOR_TYPES,
MONITOR_TYPES1,
MONITOR_VALUE,
SESSION_INFO,
SESSION_STATE,
EVENT,
CPU_TIME,
BUFFER_GETS,
PHYSICAL_READ_BYTES,
PHYSICAL_WRITE_BYTES,
USER_IO_WAIT_TIME,
BLOCKING_INSTANCE,
BLOCKING_SESSION,
LAST_CALL_ET,
ASH_COUNTS,
IN_DATE)
SELECT S_XB_SQL_MONITOR_PP_LHR.NEXTVAL,
INST_ID,
SID,
SERIAL#,
SPID,
OSUSER,
USERNAME,
SQL_TEXT,
(SELECT NB.SQL_FULLTEXT
FROM GV$SQLAREA NB
WHERE NB.INST_ID = T.INST_ID
AND T.SQL_ID = NB.SQL_ID) SQL_FULLTEXT,
PLAN_OPERATION,
STARTS,
PLAN_PARTITION_START,
PLAN_PARTITION_STOP,
EXECUTIONS,
SQL_ID,
SQL_EXEC_START,
LOGON_TIME,
'' LAST_LOAD_TIME,
'' LAST_ACTIVE_TIME,
F_GET_TOTAL_TIME_LHR(ELAPSED_TIME_S),
ELAPSED_TIME_S,
MONITOR_TYPES,
'' MONITOR_TYPES1,
'' MONITOR_VALUE,
SESSION_INFO,
SESSION_STATE,
EVENT,
CPU_TIME,
BUFFER_GETS,
PHYSICAL_READ_BYTES,
PHYSICAL_WRITE_BYTES,
USER_IO_WAIT_TIME,
BLOCKING_INSTANCE,
BLOCKING_SESSION,
LAST_CALL_ET,
ASH_COUNTS,
SYSDATE IN_DATE
FROM (
--记录所有正在运行中的性能差的SQL语句
WITH TMPS AS (SELECT WB.INST_ID INST_ID,
WB.SID SID,
WB.SERIAL#,
WB.SPID,
WB.OSUSER,
WB.USERNAME,
WA.PLAN_DEPTH,
WA.PLAN_OPERATION PLAN_OPERATION,
WA.PLAN_OPTIONS,
WA.PLAN_PARTITION_START,
WA.PLAN_PARTITION_STOP,
WA.STARTS,
WA.PLAN_COST,
WA.PLAN_CARDINALITY,
NVL(WB.SQL_ID, WA.SQL_ID) SQL_ID,
WB.SQL_EXEC_START,
WA.PX_SERVERS_REQUESTED,
WA.PX_SERVERS_ALLOCATED,
WA.PX_MAXDOP,
WA.ELAPSED_TIME_S ELAPSED_TIME_S,
WA.CPU_TIME CPU_TIME,
WA.BUFFER_GETS,
WA.PHYSICAL_READ_BYTES,
WA.PHYSICAL_WRITE_BYTES,
WA.USER_IO_WAIT_TIME USER_IO_WAIT_TIME,
NVL((SELECT NS.SQL_TEXT
FROM GV$SQLAREA NS
WHERE NS.SQL_ID = WB.SQL_ID
AND NS.INST_ID = WB.INST_ID),
WA.SQL_TEXT) SQL_TEXT,
WB.LOGON_TIME,
WB.SQL_EXEC_ID,
WB.EVENT,
WB.BLOCKING_INSTANCE BLOCKING_INSTANCE,
WB.BLOCKING_SESSION BLOCKING_SESSION,
WB.BLOCKING_SESSION_SERIAL# BLOCKING_SESSION_SERIAL#,
WB.TADDR,
WB.SADDR,
WB.LAST_CALL_ET,
(WB.SESSION_INFO || '--' || WB.SESSION_TYPE || '--' ||
WB.MACHINE) SESSION_INFO,
(SELECT NS.EXECUTIONS
FROM GV$SQLAREA NS
WHERE NS.SQL_ID = WB.SQL_ID
AND NS.INST_ID = WB.INST_ID) EXECUTIONS,
'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' ||
WB.SQL_ID || ''',' || WB.SQL_CHILD_NUMBER ||
',''advanced''));' SQL_PLAN,
WB.ASH_COUNTS,
WB.SESSION_STATE
FROM (SELECT A.INST_ID,
A.SID,
A.PLAN_DEPTH,
A.PLAN_OPERATION PLAN_OPERATION,
A.PLAN_OPTIONS,
A.PLAN_PARTITION_START,
A.PLAN_PARTITION_STOP,
A.STARTS,
MAX(A.PLAN_COST) OVER(PARTITION BY A.INST_ID, A.SID, A.KEY, A.SQL_EXEC_ID, A.SQL_ID) AS PLAN_COST,
MAX(A.PLAN_CARDINALITY) OVER(PARTITION BY A.INST_ID, A.SID, A.KEY, A.SQL_EXEC_ID, A.SQL_ID) AS PLAN_CARDINALITY,
A.SQL_ID,
A.SQL_EXEC_START,
B.PX_SERVERS_REQUESTED,
B.PX_SERVERS_ALLOCATED,
B.PX_MAXDOP,
(B.ELAPSED_TIME / 1000000) ELAPSED_TIME_S,
(B.CPU_TIME / 1000000) CPU_TIME,
B.BUFFER_GETS,
B.PHYSICAL_READ_BYTES,
B.PHYSICAL_WRITE_BYTES,
(B.USER_IO_WAIT_TIME / 1000000) USER_IO_WAIT_TIME,
B.SQL_TEXT SQL_TEXT,
(B.MODULE || '--' || B.ACTION || '--' ||
B.PROGRAM || '--' || B.PROCESS_NAME || '--' ||
B.CLIENT_IDENTIFIER || '--' ||
B.CLIENT_INFO || '--' ||
B.SERVICE_NAME) SESSION_INFO,
A.SQL_EXEC_ID
FROM GV$SQL_PLAN_MONITOR A,
GV$SQL_MONITOR B
WHERE A.SID = B.SID
AND A.KEY = B.KEY
AND A.INST_ID = B.INST_ID
AND A.SQL_EXEC_ID = B.SQL_EXEC_ID
AND A.STATUS IN
('EXECUTING', 'DONE(ERROR)')
AND B.STATUS IN
('EXECUTING', 'DONE(ERROR)')
AND B.PROCESS_NAME NOT LIKE 'p%') WA
RIGHT OUTER JOIN (SELECT ASH.INST_ID,
ASH.SESSION_ID SID,
ASH.SESSION_SERIAL# SERIAL#,
(SELECT PR.SPID
FROM GV$PROCESS PR
WHERE GVS.PADDR =
PR.ADDR
AND PR.INST_ID =
ASH.INST_ID) SPID,
ASH.SESSION_TYPE,
ASH.USER_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
ASH.SQL_EXEC_ID,
NVL(ASH.EVENT, GVS.EVENT) EVENT,
ASH.SESSION_STATE,
ASH.BLOCKING_SESSION,
ASH.BLOCKING_SESSION_SERIAL#,
ASH.BLOCKING_INST_ID BLOCKING_INSTANCE,
ASH.CLIENT_ID,
ASH.MACHINE,
GVS.LAST_CALL_ET,
GVS.TADDR,
GVS.SADDR,
GVS.LOGON_TIME,
GVS.USERNAME,
GVS.OSUSER,
GVS.SQL_EXEC_START,
(GVS.MODULE || '--' ||
GVS.ACTION || '--' ||
GVS.PROGRAM || '--' ||
GVS.PROCESS || '--' ||
GVS.CLIENT_IDENTIFIER || '--' ||
GVS.CLIENT_INFO || '--' ||
GVS.SERVICE_NAME) SESSION_INFO,
COUNT(*) ASH_COUNTS
FROM GV$ACTIVE_SESSION_HISTORY ASH,
GV$SESSION GVS
WHERE ASH.INST_ID = GVS.INST_ID
AND GVS.SQL_ID = ASH.SQL_ID
AND GVS.SQL_EXEC_ID =
ASH.SQL_EXEC_ID
AND ASH.SESSION_ID = GVS.SID
AND ASH.SESSION_SERIAL# =
GVS.SERIAL#
AND GVS.STATUS = 'ACTIVE'
AND ASH.SQL_ID IS NOT NULL
GROUP BY ASH.INST_ID,
ASH.SESSION_ID,
ASH.SESSION_SERIAL#,
ASH.SESSION_TYPE,
ASH.USER_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
ASH.SQL_EXEC_ID,
NVL(ASH.EVENT,
GVS.EVENT),
ASH.SESSION_STATE,
ASH.BLOCKING_SESSION,
ASH.BLOCKING_SESSION_SERIAL#,
ASH.BLOCKING_INST_ID,
ASH.CLIENT_ID,
ASH.MACHINE,
GVS.LAST_CALL_ET,
GVS.TADDR,
GVS.SADDR,
GVS.LOGON_TIME,
GVS.USERNAME,
GVS.OSUSER,
GVS.PADDR,
(GVS.MODULE || '--' ||
GVS.ACTION || '--' ||
GVS.PROGRAM || '--' ||
GVS.PROCESS || '--' ||
GVS.CLIENT_IDENTIFIER || '--' ||
GVS.CLIENT_INFO || '--' ||
GVS.SERVICE_NAME),
GVS.SQL_EXEC_START
HAVING COUNT(*) > 6) WB
ON (WB.SID = WA.SID AND
WB.INST_ID = WA.INST_ID AND
WB.SQL_ID = WA.SQL_ID AND
WB.SQL_EXEC_ID = WA.SQL_EXEC_ID))
------------------------------------------ 笛卡尔积
SELECT DISTINCT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'笛卡尔积【' || COUNT(*) OVER(PARTITION BY T.INST_ID, T.SID, T.SERIAL#, T.SQL_ID) || '】个' MONITOR_TYPES
FROM TMPS T
WHERE T.PLAN_OPERATION = 'MERGE JOIN'
AND T.PLAN_OPTIONS = 'CARTESIAN'
AND T.USERNAME NOT IN ('SYS')
UNION ALL
------------------------------------------ SQL执行时间过大
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'执行时间过大' MONITOR_TYPES
FROM TMPS T
WHERE T.ELAPSED_TIME_S > V_ELAPSED_TIME --5 * 60 * 60
AND (NVL(PLAN_DEPTH, 1) = 1)
UNION ALL
------------------------------------------ 分区表全分区扫描
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'分区表全分区扫描' MONITOR_TYPES
FROM TMPS T
WHERE T.PLAN_OPERATION LIKE 'PARTITION%'
AND T.PLAN_OPTIONS = 'ALL'
-- AND T.ELAPSED_TIME_S >= 0.5 * 60 * 60
UNION ALL
------------------------------------------ 执行计划中COST花费超大
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'执行计划中COST花费超大【' || T.PLAN_COST || '】' MONITOR_TYPES
FROM TMPS T
WHERE T.PLAN_COST >= V_PLAN_COST
AND (NVL(PLAN_DEPTH, 1) = 1)
UNION ALL
------------------------------------------ 执行计划中预估行数超大
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'执行计划中预估行数超大【' || T.PLAN_CARDINALITY || '】' MONITOR_TYPES
FROM TMPS T
WHERE T.PLAN_CARDINALITY > V_PLAN_CARDINALITY
AND (NVL(PLAN_DEPTH, 1) = 1)
UNION ALL
------------------------------------------ SQL请求并行数过多
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'SQL请求并行数过多【' || PX_MAXDOP || '】' MONITOR_TYPES
FROM TMPS T
WHERE T.PX_MAXDOP >= V_PARALLEL
AND (NVL(PLAN_DEPTH, 1) = 1)
UNION ALL
------------------------------------------ 系统预估的剩余执行时间过长
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'系统预估的剩余执行时间过长【' || ROUND(D.TIME_REMAINING) || '】' MONITOR_TYPES
FROM TMPS T, GV$SESSION_LONGOPS D
WHERE T.SQL_EXEC_ID = D.SQL_EXEC_ID
AND T.SID = D.SID
AND T.SERIAL# = D.SERIAL#
AND D.TIME_REMAINING > V_ESTIMATE_TIME
AND T.INST_ID = D.INST_ID
AND D.TIME_REMAINING > 0
AND (NVL(PLAN_DEPTH, 1) = 1)
UNION ALL
------------------------------------------ 等待事件异常
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'等待事件异常【' || T.EVENT || '】' MONITOR_TYPES
FROM TMPS T
WHERE T.EVENT NOT IN
('db file sequential read',
'db file scattered read',
'db file parallel write',
'db file parallel read')
AND (NVL(PLAN_DEPTH, 1) = 1)
UNION ALL
------------------------------------------ TMP表空间占用过大
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'SQL占用TMP表空间过大【' || C.BYTES || '】Bytes' MONITOR_TYPES
FROM TMPS T,
(SELECT A.INST_ID,
A.SESSION_ADDR,
SUM(A.BLOCKS) * 8 * 1024 BYTES
FROM GV$TEMPSEG_USAGE A
GROUP BY A.INST_ID, A.SESSION_ADDR) C
WHERE C.SESSION_ADDR = T.SADDR
AND C.INST_ID = T.INST_ID
AND C.BYTES > V_TMPSIZE --50 * 1024 * 1024 * 1024
AND (NVL(PLAN_DEPTH, 1) = 1)
UNION ALL
----------------------------------------- SQL占用UNDO过大，INACTIVE的会话也可能占用UNDO，但是这里只记录正在运行的SQL语句
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'SQL占用UNDO过大【' || USED_SIZE_BYTES || '】Bytes' MONITOR_TYPES
FROM TMPS T,
(SELECT ST.ADDR,
ST.INST_ID,
(ST.USED_UBLK * 8 * 1024) USED_SIZE_BYTES
FROM GV$TRANSACTION ST, V$ROLLNAME R, GV$ROLLSTAT G
WHERE ST.XIDUSN = R.USN
AND R.USN = G.USN
AND G.INST_ID = ST.INST_ID) V1
WHERE V1.ADDR = T.TADDR
AND T.INST_ID = V1.INST_ID
AND USED_SIZE_BYTES > V_UNDOSIZE -- 50 * 1024 * 1024 * 1024
UNION ALL
----------------------------------------- 耗费性能SQL
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'ASH捕获的次数【' || T.ASH_COUNTS || '】【' || SESSION_STATE || '】' MONITOR_TYPES
FROM TMPS T
WHERE T.ASH_COUNTS >= 4
AND (NVL(PLAN_DEPTH, 1) = 1)
) T;
COMMIT;
/*
EXCEPTION
WHEN OTHERS THEN
COMMIT;*/
END P_SQL_EPP_LHR;
PROCEDURE P_SQL_EPP2_LHR AS
-----------------------------------------------------------------------------------
-- Created on 2015/11/21 16:49:28 by lhr
--Changed on 2017/04/03 16:49:28 by lhr
-- function： 监控已经执行完毕的SQL
-----------------------------------------------------------------------------------
V_TMPSIZE NUMBER := 5368709120; --bytes
V_UNDOSIZE NUMBER := 5368709120; --bytes
V_PLAN_COST NUMBER := 3107523095; --cost 花费
V_PLAN_CARDINALITY NUMBER := 30748908521460; --预估行数
V_ELAPSED_TIME NUMBER := 10000000; ---执行时间，单位：微秒，例执行时间设置5小时，则： 5h*60*60*1000000 ，1秒等于1000000微秒
V_EXECUTIONS NUMBER := 1000; --执行次数
V_PARALLEL NUMBER := 4; --SQL开并行的个数
V_ESTIMATE_TIME NUMBER := 900; --系统预估的执行时间
V_LOGICAL_READS NUMBER := 5368709120; --bytes
V_DISK_READS NUMBER := 5368709120; --bytes
BEGIN
SELECT SUM(CASE T.PARAM_NAME
WHEN 'V_UNDOSIZE' THEN
T.PARAM_VALUE
END) V_UNDOSIZE,
SUM(CASE T.PARAM_NAME
WHEN 'V_TMPSIZE' THEN
T.PARAM_VALUE
END) V_TMPSIZE,
SUM(CASE T.PARAM_NAME
WHEN 'V_PLAN_COST' THEN
T.PARAM_VALUE
END) V_PLAN_COST,
SUM(CASE T.PARAM_NAME
WHEN 'V_PLAN_CARDINALITY' THEN
T.PARAM_VALUE
END) V_PLAN_CARDINALITY,
SUM(CASE T.PARAM_NAME
WHEN 'V_ELAPSED_TIME' THEN
T.PARAM_VALUE
END) V_ELAPSED_TIME,
SUM(CASE T.PARAM_NAME
WHEN 'V_EXECUTIONS' THEN
T.PARAM_VALUE
END) V_EXECUTIONS,
SUM(CASE T.PARAM_NAME
WHEN 'V_PARALLEL' THEN
T.PARAM_VALUE
END) V_PARALLEL,
SUM(CASE T.PARAM_NAME
WHEN 'V_ESTIMATE_TIME' THEN
T.PARAM_VALUE
END) V_ESTIMATE_TIME,
SUM(CASE T.PARAM_NAME
WHEN 'V_LOGICAL_READS' THEN
T.PARAM_VALUE
END) V_UNDOSIZE,
SUM(CASE T.PARAM_NAME
WHEN 'V_DISK_READS' THEN
T.PARAM_VALUE
END) V_DISK_READS
INTO V_UNDOSIZE,
V_TMPSIZE,
V_PLAN_COST,
V_PLAN_CARDINALITY,
V_ELAPSED_TIME,
V_EXECUTIONS,
V_PARALLEL,
V_ESTIMATE_TIME,
V_LOGICAL_READS,
V_DISK_READS
FROM XB_SQL_PARAMETERS_LHR T
WHERE T.PARAM_NAME IN ('V_LOGICAL_READS',
'V_DISK_READS',
'V_UNDOSIZE',
'V_TMPSIZE',
'V_PLAN_COST',
'V_PLAN_CARDINALITY',
'V_ELAPSED_TIME',
'V_EXECUTIONS',
'V_PARALLEL',
'V_ESTIMATE_TIME');
INSERT INTO XB_SQL_MONITOR_PP_LHR
(ID,
INST_ID,
SID,
SERIAL#,
USERNAME,
SQL_TEXT,
EXECUTIONS,
SQL_ID,
SQL_EXEC_START,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME,
ELAPSED_TIME,
ELAPSED_TIME1,
MONITOR_TYPES,
MONITOR_TYPES1,
MONITOR_VALUE,
SESSION_INFO,
SESSION_STATE,
EVENT,
CPU_TIME,
BUFFER_GETS,
PHYSICAL_READ_BYTES,
PHYSICAL_WRITE_BYTES,
BLOCKING_INSTANCE,
BLOCKING_SESSION,
ASH_COUNTS,
IN_DATE)
SELECT S_XB_SQL_MONITOR_PP_LHR.NEXTVAL,
INST_ID,
SID,
SERIAL#,
USERNAME,
SQL_TEXT,
EXECUTIONS,
SQL_ID,
SQL_EXEC_START,
'' LAST_LOAD_TIME,
'' LAST_ACTIVE_TIME,
F_GET_TOTAL_TIME_LHR(ELAPSED_TIME_S),
ELAPSED_TIME_S,
MONITOR_TYPES,
'' MONITOR_TYPES1,
'' MONITOR_VALUE,
SESSION_INFO,
SESSION_STATE,
EVENT,
CPU_TIME,
BUFFER_GETS,
PHYSICAL_READ_BYTES,
PHYSICAL_WRITE_BYTES,
BLOCKING_INSTANCE,
BLOCKING_SESSION,
ASH_COUNTS,
SYSDATE IN_DATE
FROM (
WITH TMPA AS (SELECT ASH.INST_ID,
ASH.SESSION_ID SID,
ASH.SESSION_SERIAL# SERIAL#,
ASH.SESSION_TYPE,
ASH.USER_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
ASH.SQL_EXEC_ID,
ASH.EVENT,
ASH.SESSION_STATE,
ASH.BLOCKING_SESSION,
ASH.BLOCKING_SESSION_SERIAL#,
ASH.BLOCKING_INST_ID BLOCKING_INSTANCE,
ASH.CLIENT_ID,
ASH.MACHINE,
GVS.PARSING_SCHEMA_NAME USERNAME,
ASH.SQL_EXEC_START,
(ASH.MODULE || '--' || ASH.ACTION || '--' ||
ASH.PROGRAM || '--' || ASH.MACHINE || '--' ||
ASH.CLIENT_ID || '--' || ASH.SESSION_TYPE) SESSION_INFO,
COUNT(*) ASH_COUNTS,
(GVS.ELAPSED_TIME / 1000000) ELAPSED_TIME_S,
(GVS.CPU_TIME / 1000000) CPU_TIME,
GVS.EXECUTIONS,
GVS.DISK_READS,
GVS.BUFFER_GETS,
GVS.LAST_ACTIVE_TIME,
GVS.LAST_LOAD_TIME,
GVS.PHYSICAL_READ_BYTES,
GVS.PHYSICAL_WRITE_BYTES,
GVS.SQL_TEXT
FROM GV$ACTIVE_SESSION_HISTORY ASH, GV$SQL GVS
WHERE ASH.INST_ID = GVS.INST_ID
AND GVS.SQL_ID = ASH.SQL_ID
AND ASH.SQL_ID IS NOT NULL
AND ASH.SAMPLE_TIME > SYSDATE - 30 / 1440
GROUP BY ASH.INST_ID,
ASH.SESSION_ID,
ASH.SESSION_SERIAL#,
ASH.SESSION_TYPE,
ASH.USER_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
ASH.SQL_EXEC_ID,
ASH.EVENT,
ASH.SESSION_STATE,
ASH.BLOCKING_SESSION,
ASH.BLOCKING_SESSION_SERIAL#,
ASH.BLOCKING_INST_ID,
ASH.CLIENT_ID,
ASH.MACHINE,
GVS.PARSING_SCHEMA_NAME,
ASH.SQL_EXEC_START,
(ASH.MODULE || '--' || ASH.ACTION || '--' ||
ASH.PROGRAM || '--' || ASH.MACHINE || '--' ||
ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE),
(GVS.ELAPSED_TIME / 1000000),
(GVS.CPU_TIME / 1000000),
GVS.EXECUTIONS,
GVS.DISK_READS,
GVS.BUFFER_GETS,
GVS.LAST_ACTIVE_TIME,
GVS.LAST_LOAD_TIME,
GVS.PHYSICAL_READ_BYTES,
GVS.PHYSICAL_WRITE_BYTES,
GVS.SQL_TEXT
HAVING COUNT(*) > 6)
------------------------------------------ 物理读过大
SELECT INST_ID,
SID,
SERIAL#,
USERNAME,
SQL_TEXT,
EXECUTIONS,
SQL_ID,
SQL_EXEC_START,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME,
ELAPSED_TIME_S,
SESSION_INFO,
SESSION_STATE,
EVENT,
CPU_TIME,
DISK_READS,
BUFFER_GETS,
PHYSICAL_READ_BYTES,
PHYSICAL_WRITE_BYTES,
BLOCKING_INSTANCE,
BLOCKING_SESSION,
ASH_COUNTS,
'物理读过大' MONITOR_TYPES
FROM TMPA T
WHERE T.DISK_READS > V_DISK_READS
UNION ALL
------------------------------------------ 逻辑读过大
SELECT INST_ID,
SID,
SERIAL#,
USERNAME,
SQL_TEXT,
EXECUTIONS,
SQL_ID,
SQL_EXEC_START,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME,
ELAPSED_TIME_S,
SESSION_INFO,
SESSION_STATE,
EVENT,
CPU_TIME,
DISK_READS,
BUFFER_GETS,
PHYSICAL_READ_BYTES,
PHYSICAL_WRITE_BYTES,
BLOCKING_INSTANCE,
BLOCKING_SESSION,
ASH_COUNTS,
'逻辑读过大' MONITOR_TYPES
FROM TMPA T
WHERE T.BUFFER_GETS > V_LOGICAL_READS
UNION ALL
------------------------------------------ 执行次数过大
SELECT INST_ID,
SID,
SERIAL#,
USERNAME,
SQL_TEXT,
EXECUTIONS,
SQL_ID,
SQL_EXEC_START,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME,
ELAPSED_TIME_S,
SESSION_INFO,
SESSION_STATE,
EVENT,
CPU_TIME,
DISK_READS,
BUFFER_GETS,
PHYSICAL_READ_BYTES,
PHYSICAL_WRITE_BYTES,
BLOCKING_INSTANCE,
BLOCKING_SESSION,
ASH_COUNTS,
'执行次数过大' MONITOR_TYPES
FROM TMPA T
WHERE T.EXECUTIONS > V_EXECUTIONS
UNION ALL
------------------------------------------ SQL总执行时间过大
SELECT INST_ID,
SID,
SERIAL#,
USERNAME,
SQL_TEXT,
EXECUTIONS,
SQL_ID,
SQL_EXEC_START,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME,
ELAPSED_TIME_S,
SESSION_INFO,
SESSION_STATE,
EVENT,
CPU_TIME,
DISK_READS,
BUFFER_GETS,
PHYSICAL_READ_BYTES,
PHYSICAL_WRITE_BYTES,
BLOCKING_INSTANCE,
BLOCKING_SESSION,
ASH_COUNTS,
'SQL总执行时间过大' MONITOR_TYPES
FROM TMPA T
WHERE T.ELAPSED_TIME_S > V_ELAPSED_TIME);
COMMIT;
END P_SQL_EPP2_LHR;
PROCEDURE P_TUNING_SQL AS
MY_TASK_NAME VARCHAR2(255);
BEGIN
---- 调优捕捉到的垃圾SQL
FOR CUR IN (SELECT DISTINCT A.USERNAME, A.SQL_ID, A.SQL_TEXT
FROM XB_SQL_MONITOR_PP_LHR A
WHERE A.TUNING_RESULT IS NULL
AND A.IN_DATE >= SYSDATE - 1) LOOP
MY_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => CUR.SQL_TEXT,
USER_NAME => CUR.USERNAME);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(TASK_NAME => MY_TASK_NAME);
UPDATE XB_SQL_MONITOR_PP_LHR T
SET T.TUNING_RESULT = DBMS_SQLTUNE.REPORT_TUNING_TASK(MY_TASK_NAME),
T.TUNING_TIME = SYSDATE
WHERE T.SQL_ID = CUR.SQL_ID;
COMMIT;
END LOOP;
/*
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('error:' || SQLCODE || ',' || SQLERRM);*/
END P_TUNING_SQL;
PROCEDURE P_GET_PPSQL_PARAMETER AS
V_TMPSIZE NUMBER := 5368709120; --bytes
V_UNDOSIZE NUMBER := 5368709120; --bytes
V_PLAN_COST NUMBER := 3107523095; --cost 花费
V_PLAN_CARDINALITY NUMBER := 30748908521460; --预估行数
V_ELAPSED_TIME NUMBER := 10000000; ---执行时间，例执行时间设置5小时，则： 5h*60*60*1000000
V_EXECUTIONS NUMBER := 1000; --执行次数
V_LOGICAL_READS NUMBER := 1000; --逻辑读
V_DISK_READS NUMBER := 1000; --物理读
BEGIN
SELECT ROUND(SUM(BYTES) / 2)
INTO V_TMPSIZE
FROM V$TEMPFILE D
WHERE D.STATUS = 'ONLINE';
SELECT ROUND(SUM(BYTES) / 2)
INTO V_UNDOSIZE
FROM DBA_DATA_FILES D, DBA_TABLESPACES DT
WHERE DT.TABLESPACE_NAME = D.TABLESPACE_NAME
AND DT.CONTENTS = 'UNDO'
AND DT.STATUS = 'ONLINE'
GROUP BY D.TABLESPACE_NAME;
SELECT ROUND(MAX(A.COST) * 0.8), ROUND(MAX(A.CARDINALITY) * 0.8)
INTO V_PLAN_COST, V_PLAN_CARDINALITY
FROM GV$SQL_PLAN A
WHERE A.OPERATION <> 'MERGE JOIN'
AND A.OPTIONS <> 'CARTESIAN'
AND NOT EXISTS
(SELECT 1
FROM GV$SQLAREA B
WHERE B.SQL_ID = A.SQL_ID
AND B.INST_ID = A.INST_ID
AND B.PARSING_SCHEMA_NAME LIKE '%SYS%');
SELECT ROUND(MAX(A.ELAPSED_TIME /
(DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS)) / 1000000) * 0.8),
ROUND(MAX(A.EXECUTIONS) * 0.8),
ROUND(MAX(A.BUFFER_GETS /
(DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS))) * 0.9),
ROUND(MAX(A.DISK_READS /
(DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS))) * 0.9)
INTO V_ELAPSED_TIME, V_EXECUTIONS, V_LOGICAL_READS, V_DISK_READS
FROM GV$SQLAREA A
WHERE NOT EXISTS (SELECT 1
FROM GV$SQL_PLAN AA
WHERE A.SQL_ID = AA.SQL_ID
AND AA.OPERATION = 'MERGE JOIN'
AND AA.OPTIONS = 'CARTESIAN'
AND AA.INST_ID = A.INST_ID)
AND A.PARSING_SCHEMA_NAME NOT LIKE '%SYS%';
UPDATE XB_SQL_PARAMETERS_LHR T
SET T.PARAM_VALUE = V_TMPSIZE
WHERE UPPER(T.PARAM_NAME) = UPPER('v_tmpsize');
UPDATE XB_SQL_PARAMETERS_LHR T
SET T.PARAM_VALUE = V_UNDOSIZE
WHERE UPPER(T.PARAM_NAME) = UPPER('v_undosize');
UPDATE XB_SQL_PARAMETERS_LHR T
SET T.PARAM_VALUE = V_PLAN_COST
WHERE UPPER(T.PARAM_NAME) = UPPER('v_plan_cost');
UPDATE XB_SQL_PARAMETERS_LHR T
SET T.PARAM_VALUE = V_PLAN_CARDINALITY
WHERE UPPER(T.PARAM_NAME) = UPPER('v_PLAN_CARDINALITY');
UPDATE XB_SQL_PARAMETERS_LHR T
SET T.PARAM_VALUE = V_ELAPSED_TIME
WHERE UPPER(T.PARAM_NAME) IN
(UPPER('v_ELAPSED_TIME'), UPPER('V_ESTIMATE_TIME'));
UPDATE XB_SQL_PARAMETERS_LHR T
SET T.PARAM_VALUE = V_EXECUTIONS
WHERE UPPER(T.PARAM_NAME) = UPPER('v_EXECUTIONS');
UPDATE XB_SQL_PARAMETERS_LHR T
SET T.PARAM_VALUE = V_EXECUTIONS
WHERE UPPER(T.PARAM_NAME) = UPPER('V_LOGICAL_READS');
UPDATE XB_SQL_PARAMETERS_LHR T
SET T.PARAM_VALUE = V_EXECUTIONS
WHERE UPPER(T.PARAM_NAME) = UPPER('V_DISK_READS');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('error:' || SQLCODE || ',' || SQLERRM);
END P_GET_PPSQL_PARAMETER;
FUNCTION F_GET_RIGHT_SIZE_LHR(P_SIZE IN VARCHAR2) RETURN VARCHAR2 AS
V_RESULTS VARCHAR2(255);
BEGIN
SELECT CASE
WHEN P_SIZE = 0 THEN
0 || ''
WHEN P_SIZE < 1024 AND P_SIZE > 0 THEN
P_SIZE || 'BYTES'
WHEN P_SIZE >= 1024 AND P_SIZE < 1048576 THEN
ROUND(P_SIZE / 1024, 3) || 'KB'
WHEN P_SIZE >= 1048576 AND P_SIZE < 1073741824 THEN
ROUND(P_SIZE / 1048576, 3) || 'M'
WHEN P_SIZE >= 1073741824 AND P_SIZE < 1099511627776 THEN
ROUND(P_SIZE / 1073741824, 3) || 'G'
WHEN P_SIZE >= 1099511627776 AND P_SIZE < 1125899906842624 THEN
ROUND(P_SIZE / 1099511627776, 3) || 'T'
END AS SIZES
INTO V_RESULTS
FROM DUAL;
RETURN V_RESULTS;
EXCEPTION
WHEN OTHERS THEN
NULL;
RETURN P_SIZE;
END;
FUNCTION F_GET_TOTAL_TIME_LHR(P_TOTAL_SECONDS NUMBER,
P_FLAG VARCHAR2 DEFAULT 'S')
RETURN VARCHAR2 DETERMINISTIC IS
-----------------------------------------------------------------------------------
-- Created on 2012/10/18 16:49:28 by lhr
--Changed on 2015/9/3 16:49:28 by lhr
-- function： 该函数 返回一个秒转换为标准时间
-----------------------------------------------------------------------------------
V_TMP FLOAT(18);
V_RETURNS VARCHAR2(200);
V_TOTAL_SECONDS NUMBER := P_TOTAL_SECONDS;
BEGIN
IF P_TOTAL_SECONDS IS NULL OR P_TOTAL_SECONDS = 0 THEN
RETURN 0 || '秒';
END IF;
---传入的为天转换为秒
IF UPPER(P_FLAG) = 'D' THEN
V_TOTAL_SECONDS := P_TOTAL_SECONDS * 24 * 60 * 60;
END IF;
-- 首先处理日期
--年
V_TMP := TRUNC(V_TOTAL_SECONDS / (12 * 30 * 24 * 3600));
IF V_TMP > 0 THEN
V_RETURNS := V_TMP || '年';
END IF;
---月
V_TMP := TRUNC(MOD(V_TOTAL_SECONDS, (12 * 30 * 24 * 3600)) /
(3600 * 24 * 30));
IF V_TMP > 0 THEN
V_RETURNS := V_RETURNS || V_TMP || '月';
END IF;
--天
V_TMP := TRUNC(MOD(V_TOTAL_SECONDS, (3600 * 24 * 30)) / (3600 * 24));
IF V_TMP > 0 THEN
V_RETURNS := V_RETURNS || V_TMP || '天';
END IF;
--处理小时
V_TMP := TRUNC(MOD(V_TOTAL_SECONDS, 3600 * 24) / 3600);
IF V_TMP > 0 THEN
V_RETURNS := V_RETURNS || V_TMP || '时';
END IF;
--处理分钟
V_TMP := TRUNC(MOD(V_TOTAL_SECONDS, 3600) / 60);
IF V_TMP > 0 THEN
V_RETURNS := V_RETURNS || V_TMP || '分';
END IF;
--处理秒钟
V_TMP := TRUNC(MOD(V_TOTAL_SECONDS, 60), 3);
IF V_TMP > 0 AND V_TMP < 1 THEN
V_RETURNS := V_RETURNS || '0' || V_TMP || '秒';
ELSIF V_TMP >= 1 THEN
V_RETURNS := V_RETURNS || V_TMP || '秒';
ELSIF V_TMP = 0 AND V_RETURNS IS NULL THEN
V_RETURNS := '0' || TRUNC(V_TOTAL_SECONDS, 6) || '秒';
END IF;
RETURN V_RETURNS;
END F_GET_TOTAL_TIME_LHR;
---------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
FUNCTION F_GET_SQL_OBJECT_LHR(P_SQL VARCHAR2,
P_COMMAND VARCHAR2 DEFAULT 'INTO',
PFLAG NUMBER DEFAULT 1) RETURN VARCHAR2 IS
-----------------------------------------------------------------------------------
-- CREATED ON 2012/8/20 11:33:07 BY LHR
--CHANGED ON 2012/8/20 11:33:07 BY LHR
-- FUNCTION： 该函数返回
-----------------------------------------------------------------------------------
--- 1、 回车--》 空格 2、 取前1500个字母 3、多空格--》单空格
V_SQL VARCHAR2(32767) := TRIM(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(P_SQL),
'/\*.*?\*/'),
'--.*+'),
CHR(10),
' '),
CHR(13),
' '),
'[ ]+',
' '),
'/\*.*?\*/'),
'[ ]+',
' '));
V_OBJECT_NAME VARCHAR2(4000);
V_OBJECT_OWNER VARCHAR2(4000);
V_L_INTO NUMBER;
V_L_TABR NUMBER;
BEGIN
---从第一个INSERT位置开始截取50个字符
V_SQL := REPLACE(SUBSTR(V_SQL, INSTR(V_SQL, P_COMMAND), 1000), '(', ' ');
--INTO的位置 表名前的空格
V_L_INTO := INSTR(V_SQL, P_COMMAND) + LENGTH(P_COMMAND) + 1;
--I表名后的空格位置
V_L_TABR := INSTR(V_SQL,
' ',
INSTR(V_SQL, P_COMMAND) + LENGTH(P_COMMAND) + 1,
1);
----- 表名
V_OBJECT_NAME := SUBSTR(V_SQL, V_L_INTO, V_L_TABR - V_L_INTO);
IF V_OBJECT_NAME LIKE '%.%' THEN
V_OBJECT_OWNER := SUBSTR(V_OBJECT_NAME,
1,
INSTR(V_OBJECT_NAME, '.') - 1); --表名前的OWNER
V_OBJECT_NAME := SUBSTR(V_OBJECT_NAME, INSTR(V_OBJECT_NAME, '.') + 1); -- 去掉表名前的OWNER
END IF;
IF PFLAG = 1 THEN
RETURN V_OBJECT_NAME;
ELSE
RETURN V_OBJECT_OWNER;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END F_GET_SQL_OBJECT_LHR;
FUNCTION F_GET_SQL_COMMAND_LHR(P_SQL VARCHAR2) RETURN VARCHAR2 IS
-----------------------------------------------------------------------------------
-- CREATED ON 2012/8/20 11:33:07 BY LHR
--CHANGED ON 2012/8/20 11:33:07 BY LHR
-- FUNCTION： 该函数返回SQL语句的类型
-----------------------------------------------------------------------------------
--- 1、 回车--》 空格 2、 取前1500个字母 3、多空格--》单空格
V_SQL VARCHAR2(32767) := TRIM(REPLACE(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(P_SQL),
'/\*.*?\*/'),
'--.*+'),
CHR(10),
' '),
CHR(13),
' '),
'[ ]+',
' '),
'/\*.*?\*/'),
'[ ]+',
' '),
'SELECT',
'SELECT '),
'('));
V_COMMAND VARCHAR2(255);
BEGIN
IF P_SQL IS NULL THEN
RETURN '';
ELSIF V_SQL IS NULL THEN
SELECT CASE
WHEN UPPER(P_SQL) LIKE '%BEGIN%' THEN
'PL/SQL EXECUTE'
WHEN UPPER(P_SQL) LIKE '%MERGE%' THEN
'MERGE'
WHEN (REPLACE(UPPER(P_SQL), ' ', '') LIKE '%CREATEINDEX%' OR
REPLACE(UPPER(P_SQL), ' ', '') LIKE
'%CREATEUNIQUEINDEX%' OR REPLACE(UPPER(P_SQL), ' ', '') LIKE
'%CREATEBITMAPINDEX%' OR
UPPER(P_SQL) LIKE '%CREATE%INDEX%') THEN
'CREATE INDEX'
WHEN REPLACE(UPPER(P_SQL), ' ', '') LIKE '%CREATETABLE%' THEN
'CREATE TABLE'
WHEN UPPER(P_SQL) LIKE '%INSERT%' THEN
'INSERT'
WHEN UPPER(P_SQL) LIKE '%UPDATE%' THEN
'UPDATE'
WHEN UPPER(P_SQL) LIKE '%DELETE%' THEN
'DELETE'
WHEN UPPER(P_SQL) LIKE '%WITH%' THEN
'WITH'
WHEN UPPER(P_SQL) LIKE '%SELECT%' THEN
'SELECT'
WHEN REPLACE(UPPER(P_SQL), ' ', '') LIKE '%ALTERTABLE%' THEN
'ALTER TABLE'
WHEN REPLACE(UPPER(P_SQL), ' ', '') LIKE '%ALTERINDEX%' THEN
'ALTER INDEX'
END
INTO V_COMMAND
FROM DUAL;
ELSE
V_COMMAND := SUBSTR(V_SQL, 1, INSTR(V_SQL, ' ', 4) - 1);
IF TRIM(V_COMMAND) IN ('ALTER', 'CREATE') THEN
V_SQL := REGEXP_REPLACE(REPLACE(REPLACE(V_SQL, 'UNIQUE'), 'BITMAP'),
'[ ]+',
' ');
V_COMMAND := SUBSTR(V_SQL, 1, INSTR(V_SQL, ' ', 2, 2) - 1);
ELSIF TRIM(V_COMMAND) IN ('DECLARE', 'BEGIN') THEN
V_COMMAND := 'PL/SQL EXECUTE';
ELSIF TRIM(V_COMMAND) = 'MERGE' THEN
V_COMMAND := 'MERGE INTO';
ELSIF TRIM(V_COMMAND) IS NULL THEN
SELECT CASE
WHEN UPPER(P_SQL) LIKE '%BEGIN%' THEN
'PL/SQL EXECUTE'
WHEN UPPER(P_SQL) LIKE '%MERGE%' THEN
'MERGE'
WHEN (REPLACE(UPPER(P_SQL), ' ', '') LIKE '%CREATEINDEX%' OR
REPLACE(UPPER(P_SQL), ' ', '') LIKE
'%CREATEUNIQUEINDEX%' OR
REPLACE(UPPER(P_SQL), ' ', '') LIKE
'%CREATEBITMAPINDEX%' OR
UPPER(P_SQL) LIKE '%CREATE%INDEX%') THEN
'CREATE INDEX'
WHEN REPLACE(UPPER(P_SQL), ' ', '') LIKE '%CREATETABLE%' THEN
'CREATE TABLE'
WHEN UPPER(P_SQL) LIKE '%INSERT%' THEN
'INSERT'
WHEN UPPER(P_SQL) LIKE '%UPDATE%' THEN
'UPDATE'
WHEN UPPER(P_SQL) LIKE '%DELETE%' THEN
'DELETE'
WHEN UPPER(P_SQL) LIKE '%WITH%' THEN
'WITH'
WHEN UPPER(P_SQL) LIKE '%SELECT%' THEN
'SELECT'
WHEN REPLACE(UPPER(P_SQL), ' ', '') LIKE '%ALTERTABLE%' THEN
'ALTER TABLE'
WHEN REPLACE(UPPER(P_SQL), ' ', '') LIKE '%ALTERINDEX%' THEN
'ALTER INDEX'
END
INTO V_COMMAND
FROM DUAL;
END IF;
END IF;
V_COMMAND := REPLACE(REPLACE(REPLACE(TRIM(V_COMMAND), '(('), '('), '*');
IF V_COMMAND NOT IN ('ALTER INDEX',
'ALTER TABLE',
'CREATE INDEX',
'CREATE TABLE',
'DELETE',
'INSERT',
'MERGE INTO',
'PL/SQL EXECUTE',
'SELECT',
'WITH',
'UPDATE') THEN
RETURN '';
END IF;
RETURN V_COMMAND;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END PKG_SQL_MONITOR_LHR;
/
--------------------------------------------------------------
EXEC PKG_SQL_MONITOR_LHR.P_GET_PPSQL_PARAMETER;
-------------------------
EXEC DBMS_SCHEDULER.DROP_PROGRAM('PROG_SQL_DONE_LHR',TRUE);
EXEC DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_SQL_DONE_LHR' );
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(PROGRAM_NAME => 'PROG_SQL_DONE_LHR',
PROGRAM_ACTION => 'PKG_SQL_MONITOR_LHR.P_SQL_DONE_LHR',
PROGRAM_TYPE => 'STORED_PROCEDURE',
ENABLED => TRUE);
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'JOB_SQL_DONE_LHR',
PROGRAM_NAME => 'PROG_SQL_DONE_LHR',
REPEAT_INTERVAL=> 'FREQ=SECONDLY;INTERVAL=30',
JOB_STYLE => 'LIGHTWEIGHT',
ENABLED=>TRUE,
COMMENTS =>'监控已经执行完毕的SQL语句（V$SQL_MONITOR）');
END;
/
------------------------
EXEC DBMS_SCHEDULER.DROP_PROGRAM('PROG_SQL_EPP_LHR',TRUE);
EXEC DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_SQL_EPP_LHR' );
BEGIN
DBMS_SCHEDULER.create_program(program_name => 'PROG_SQL_EPP_LHR',
program_action => 'PKG_SQL_MONITOR_LHR.P_SQL_EPP_LHR',
program_type => 'STORED_PROCEDURE',
enabled => TRUE);
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'JOB_SQL_EPP_LHR',
PROGRAM_NAME => 'PROG_SQL_EPP_LHR',
REPEAT_INTERVAL=> 'FREQ=SECONDLY;INTERVAL=30',
JOB_STYLE => 'LIGHTWEIGHT',
ENABLED=>TRUE,
COMMENTS =>'记录性能低下的SQL语句');
END;
/
EXEC DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_AUTO_TUNING_SQL_LHR' );
BEGIN
--DBMS_SCHEDULER.drop_job('JOB_AUTO_TUNING_SQL_LHR');
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'JOB_AUTO_TUNING_SQL_LHR',
JOB_TYPE => 'STORED_PROCEDURE',
JOB_ACTION => 'PKG_SQL_MONITOR_LHR.P_TUNING_SQL',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=50',
ENABLED => TRUE,
START_DATE => SYSDATE,
COMMENTS => '每隔50分钟优化一次SQL');
END;
/
EXEC DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_SQL_EPP2_LHR' );
BEGIN
--DBMS_SCHEDULER.DROP_JOB('JOB_SQL_EPP2_LHR');
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'JOB_SQL_EPP2_LHR',
JOB_TYPE => 'STORED_PROCEDURE',
JOB_ACTION => 'PKG_SQL_MONITOR_LHR.P_SQL_EPP2_LHR',
REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=28',
ENABLED => TRUE,
START_DATE => SYSDATE,
COMMENTS => 'GV$SQL检查性能低下的SQL');
END;
/
------------------------- 视图 ------------------------------
CREATE OR REPLACE VIEW VW_SQL_PP_LHR AS
--记录所有正在运行中的性能差的SQL语句
WITH TMPS AS
(SELECT WB.INST_ID INST_ID,
WB.SID SID,
WB.SERIAL#,
WB.SPID,
WB.OSUSER,
WB.USERNAME,
WA.PLAN_DEPTH,
WA.PLAN_OPERATION PLAN_OPERATION,
WA.PLAN_OPTIONS,
WA.PLAN_PARTITION_START,
WA.PLAN_PARTITION_STOP,
WA.STARTS,
WA.PLAN_COST,
WA.PLAN_CARDINALITY,
NVL(WB.SQL_ID, WA.SQL_ID) SQL_ID,
WB.SQL_EXEC_START,
WA.PX_SERVERS_REQUESTED,
WA.PX_SERVERS_ALLOCATED,
WA.PX_MAXDOP,
WA.ELAPSED_TIME_S ELAPSED_TIME_S,
WA.CPU_TIME CPU_TIME,
WA.BUFFER_GETS,
WA.PHYSICAL_READ_BYTES,
WA.PHYSICAL_WRITE_BYTES,
WA.USER_IO_WAIT_TIME USER_IO_WAIT_TIME,
NVL((SELECT NS.SQL_TEXT
FROM GV$SQLAREA NS
WHERE NS.SQL_ID = WB.SQL_ID
AND NS.INST_ID = WB.INST_ID),WA.SQL_TEXT) SQL_TEXT,
WB.LOGON_TIME,
WB.SQL_EXEC_ID,
WB.EVENT,
WB.BLOCKING_INSTANCE BLOCKING_INSTANCE,
WB.BLOCKING_SESSION BLOCKING_SESSION,
WB.BLOCKING_SESSION_SERIAL# BLOCKING_SESSION_SERIAL#,
WB.TADDR,
WB.SADDR,
WB.LAST_CALL_ET,
(WB.SESSION_INFO || '--' || WB.SESSION_TYPE || '--' || WB.MACHINE) SESSION_INFO,
(SELECT NS.EXECUTIONS
FROM GV$SQLAREA NS
WHERE NS.SQL_ID = WB.SQL_ID
AND NS.INST_ID = WB.INST_ID) EXECUTIONS,
'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || WB.SQL_ID ||
''',' || WB.SQL_CHILD_NUMBER || ',''advanced''));' SQL_PLAN,
WB.ASH_COUNTS,
WB.SESSION_STATE
FROM (SELECT A.INST_ID,
A.SID,
A.PLAN_DEPTH,
A.PLAN_OPERATION PLAN_OPERATION,
A.PLAN_OPTIONS,
A.PLAN_PARTITION_START,
A.PLAN_PARTITION_STOP,
A.STARTS,
MAX(A.PLAN_COST) OVER(PARTITION BY A.INST_ID, A.SID, A.KEY, A.SQL_EXEC_ID, A.SQL_ID) AS PLAN_COST,
MAX(A.PLAN_CARDINALITY) OVER(PARTITION BY A.INST_ID, A.SID, A.KEY, A.SQL_EXEC_ID, A.SQL_ID) AS PLAN_CARDINALITY,
A.SQL_ID,
A.SQL_EXEC_START,
B.PX_SERVERS_REQUESTED,
B.PX_SERVERS_ALLOCATED,
B.PX_MAXDOP,
(B.ELAPSED_TIME / 1000000) ELAPSED_TIME_S,
(B.CPU_TIME / 1000000) CPU_TIME,
B.BUFFER_GETS,
B.PHYSICAL_READ_BYTES,
B.PHYSICAL_WRITE_BYTES,
(B.USER_IO_WAIT_TIME / 1000000) USER_IO_WAIT_TIME,
B.SQL_TEXT SQL_TEXT,
(B.MODULE || '--' || B.ACTION || '--' || B.PROGRAM || '--' ||
B.PROCESS_NAME || '--' || B.CLIENT_IDENTIFIER || '--' ||
B.CLIENT_INFO || '--' || B.SERVICE_NAME) SESSION_INFO,
A.SQL_EXEC_ID
FROM GV$SQL_PLAN_MONITOR A, GV$SQL_MONITOR B
WHERE A.SID = B.SID
AND A.KEY = B.KEY
AND A.INST_ID = B.INST_ID
AND A.SQL_EXEC_ID = B.SQL_EXEC_ID
AND A.STATUS IN ('EXECUTING', 'DONE(ERROR)')
AND B.STATUS IN ('EXECUTING', 'DONE(ERROR)')
AND B.PROCESS_NAME NOT LIKE 'p%') WA
RIGHT OUTER JOIN (SELECT ASH.INST_ID,
ASH.SESSION_ID SID,
ASH.SESSION_SERIAL# SERIAL#,
(SELECT PR.SPID
FROM GV$PROCESS PR
WHERE GVS.PADDR = PR.ADDR
AND PR.INST_ID = ASH.INST_ID) SPID,
ASH.SESSION_TYPE,
ASH.USER_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
ASH.SQL_EXEC_ID,
NVL(ASH.EVENT, GVS.EVENT) EVENT,
ASH.SESSION_STATE,
ASH.BLOCKING_SESSION,
ASH.BLOCKING_SESSION_SERIAL#,
ASH.BLOCKING_INST_ID BLOCKING_INSTANCE,
ASH.CLIENT_ID,
ASH.MACHINE,
GVS.LAST_CALL_ET,
GVS.TADDR,
GVS.SADDR,
GVS.LOGON_TIME,
GVS.USERNAME,
GVS.OSUSER,
GVS.SQL_EXEC_START,
(GVS.MODULE || '--' || GVS.ACTION || '--' || GVS.PROGRAM || '--' ||
GVS.PROCESS || '--' || GVS.CLIENT_IDENTIFIER || '--' ||
GVS.CLIENT_INFO || '--' || GVS.SERVICE_NAME) SESSION_INFO,
COUNT(*) ASH_COUNTS
FROM GV$ACTIVE_SESSION_HISTORY ASH, GV$SESSION GVS
WHERE ASH.INST_ID = GVS.INST_ID
AND GVS.SQL_ID = ASH.SQL_ID
AND GVS.SQL_EXEC_ID = ASH.SQL_EXEC_ID
AND ASH.SESSION_ID = GVS.SID
AND ASH.SESSION_SERIAL# = GVS.SERIAL#
AND GVS.STATUS = 'ACTIVE'
AND ASH.SQL_ID IS NOT NULL
GROUP BY ASH.INST_ID,
ASH.SESSION_ID,
ASH.SESSION_SERIAL#,
ASH.SESSION_TYPE,
ASH.USER_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
ASH.SQL_EXEC_ID,
NVL(ASH.EVENT, GVS.EVENT),
ASH.SESSION_STATE,
ASH.BLOCKING_SESSION,
ASH.BLOCKING_SESSION_SERIAL#,
ASH.BLOCKING_INST_ID,
ASH.CLIENT_ID,
ASH.MACHINE,
GVS.LAST_CALL_ET,
GVS.TADDR,
GVS.SADDR,
GVS.LOGON_TIME,
GVS.USERNAME,
GVS.OSUSER,
GVS.PADDR,
(GVS.MODULE || '--' || GVS.ACTION || '--' || GVS.PROGRAM || '--' ||
GVS.PROCESS || '--' || GVS.CLIENT_IDENTIFIER || '--' ||
GVS.CLIENT_INFO || '--' || GVS.SERVICE_NAME),
GVS.SQL_EXEC_START
HAVING COUNT(*) > 6) WB
ON (WB.SID = WA.SID AND WB.INST_ID = WA.INST_ID AND
WB.SQL_ID = WA.SQL_ID AND WB.SQL_EXEC_ID = WA.SQL_EXEC_ID)
)
------------------------------------------ 笛卡尔积
SELECT DISTINCT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'笛卡尔积【' || COUNT(*) OVER(PARTITION BY T.INST_ID, T.SID, T.SERIAL#, T.SQL_ID) || '】个' MONITOR_TYPES
FROM TMPS T
WHERE T.PLAN_OPERATION = 'MERGE JOIN'
AND T.PLAN_OPTIONS = 'CARTESIAN'
AND T.USERNAME NOT IN ('SYS')
UNION ALL
------------------------------------------ SQL执行时间过大
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'执行时间过大' MONITOR_TYPES
FROM TMPS T
WHERE T.ELAPSED_TIME_S > 10 --5 * 60 * 60
AND (nvl(PLAN_DEPTH,1)=1)
UNION ALL
------------------------------------------ 分区表全分区扫描
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'分区表全分区扫描' MONITOR_TYPES
FROM TMPS T
WHERE T.PLAN_OPERATION LIKE 'PARTITION%'
AND T.PLAN_OPTIONS = 'ALL'
-- AND T.ELAPSED_TIME_S >= 0.5 * 60 * 60
UNION ALL
------------------------------------------ 执行计划中COST花费超大
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'执行计划中COST花费超大【' || T.PLAN_COST || '】' MONITOR_TYPES
FROM TMPS T
WHERE T.PLAN_COST >= 3107523095
AND (nvl(PLAN_DEPTH,1)=1)
UNION ALL
------------------------------------------ 执行计划中预估行数超大
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'执行计划中预估行数超大【' || T.PLAN_CARDINALITY || '】' MONITOR_TYPES
FROM TMPS T
WHERE T.PLAN_CARDINALITY > 30748908521460
AND (nvl(PLAN_DEPTH,1)=1)
UNION ALL
------------------------------------------ SQL请求并行数过多
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'SQL请求并行数过多【' || PX_MAXDOP || '】' MONITOR_TYPES
FROM TMPS T
WHERE T.PX_MAXDOP>=8
AND (nvl(PLAN_DEPTH,1)=1)
UNION ALL
------------------------------------------ 系统预估的剩余执行时间过长
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'系统预估的剩余执行时间过长【' || ROUND(D.TIME_REMAINING) || '】' MONITOR_TYPES
FROM TMPS T, GV$SESSION_LONGOPS D
WHERE T.SQL_EXEC_ID = D.SQL_EXEC_ID
AND T.SID = D.SID
AND T.SERIAL# = D.SERIAL#
AND D.TIME_REMAINING > 10
AND T.INST_ID = D.INST_ID
AND D.TIME_REMAINING >0
AND (nvl(PLAN_DEPTH,1)=1)
UNION ALL
------------------------------------------ 等待事件异常
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'等待事件异常【' || T.EVENT || '】' MONITOR_TYPES
FROM TMPS T
WHERE T.EVENT NOT IN ('db file sequential read', 'db file scattered read','db file parallel write','db file parallel read')
AND (nvl(PLAN_DEPTH,1)=1)
UNION ALL
------------------------------------------ TMP表空间占用过大
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'SQL占用TMP表空间过大【' || C.BYTES || '】Bytes' MONITOR_TYPES
FROM TMPS T,
(SELECT A.INST_ID, A.SESSION_ADDR, SUM(A.BLOCKS) * 8 * 1024 BYTES
FROM GV$TEMPSEG_USAGE A
GROUP BY A.INST_ID, A.SESSION_ADDR) C
WHERE C.SESSION_ADDR = T.SADDR
AND C.INST_ID = T.INST_ID
AND C.BYTES > 10 --50 * 1024 * 1024 * 1024
AND (nvl(PLAN_DEPTH,1)=1)
UNION ALL
----------------------------------------- SQL占用UNDO过大，INACTIVE的会话也可能占用UNDO，但是这里只记录正在运行的SQL语句
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'SQL占用UNDO过大【' || USED_SIZE_BYTES || '】Bytes' MONITOR_TYPES
FROM TMPS T,
(SELECT ST.ADDR,
ST.INST_ID,
(ST.USED_UBLK * 8 * 1024) USED_SIZE_BYTES
FROM GV$TRANSACTION ST, V$ROLLNAME R, GV$ROLLSTAT G
WHERE ST.XIDUSN = R.USN
AND R.USN = G.USN
AND G.INST_ID = ST.INST_ID) V1
WHERE V1.ADDR = T.TADDR
AND T.INST_ID = V1.INST_ID
AND USED_SIZE_BYTES > 1024 -- 50 * 1024 * 1024 * 1024
UNION ALL
----------------------------------------- 耗费性能SQL
SELECT T.INST_ID,
T.SID,
T.SERIAL#,
T.SPID,
T.OSUSER,
T.USERNAME,
T.EVENT,
T.SESSION_STATE,
T.SQL_TEXT,
T.EXECUTIONS,
T.ELAPSED_TIME_S,
T.CPU_TIME,
T.USER_IO_WAIT_TIME,
T.BUFFER_GETS,
T.PLAN_OPERATION,
T.STARTS,
T.PLAN_PARTITION_START,
T.PLAN_PARTITION_STOP,
T.PHYSICAL_READ_BYTES,
T.PHYSICAL_WRITE_BYTES,
T.BLOCKING_INSTANCE,
T.BLOCKING_SESSION,
T.BLOCKING_SESSION_SERIAL#,
T.LAST_CALL_ET,
T.SQL_ID,
T.SQL_EXEC_START,
T.SQL_PLAN,
T.LOGON_TIME,
T.ASH_COUNTS,
T.SESSION_INFO,
'ASH捕获的次数【' || T.ASH_COUNTS || '】【'||SESSION_STATE||'】' MONITOR_TYPES
FROM TMPS T
WHERE T.ASH_COUNTS>=4
AND (nvl(PLAN_DEPTH,1)=1)
ORDER BY SQL_EXEC_START DESC
看看之前的一些结果：
修改后的结果：
该脚本中有视图也有表。若想直接查询数据库耗费性能的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群，学习最实用的数据库技术。
