CREATE OR REPLACE VIEW VW_SESSION_LHR AS
SELECT a.username,
       a.SID,
       a.serial#,
       (SELECT p.SPID FROM v$process p WHERE p.ADDR = a.PADDR) SPID,
       a.STATUS,
       (SELECT v.name FROM SYS.AUDIT_ACTIONS v WHERE v.action = a.COMMAND) COMMAND,
       (SELECT d.OBJECT_NAME || ':' || d.OBJECT_TYPE
        FROM   dba_objects d
        WHERE  d.OBJECT_ID = a.ROW_WAIT_OBJ#) 等待对象,
       CASE
           WHEN (SELECT b.session_id
                 FROM   dba_scheduler_running_jobs b
                 WHERE  b.session_id = a.SID
                 AND    rownum = 1) > 0 THEN
            'DBMS_SCHEDULER_JOB'
        WHEN (SELECT b.sid
            FROM   dba_jobs_running b
            WHERE  b.SID = a.SID
            AND    rownum = 1) > 0 THEN
       'DBMS_JOB'
           WHEN b.sid > 0 THEN
            'DBMS_JOB'
           WHEN a.MODULE != 'JDBC Thin Client' THEN
            a.MACHINE || '---' || a.TERMINAL || '---' || a.MODULE ||
            a.ACTION
           ELSE
            a.MODULE
       END 会话类型,
       CASE
           WHEN (SELECT COUNT(1)
                 FROM   v$px_session cn
                 WHERE  cn.SID = cn.QCSID
                 AND    cn.SID = a.SID) > 0 THEN
            'Y'
           WHEN (SELECT COUNT(1) FROM v$px_session cn WHERE cn.SID = a.SID) > 0 THEN
            'N'
       END AS 是否并行主进程,
       a.logon_time,
       (SYSDATE - a.LOGON_TIME)*24*60*60 会话持续时间,
       a.osuser "客户端用户名",
       a.PROCESS,
       a.machine,
       a.terminal,
       a.program,
       a.module,
       a.action,
       a.client_info,
       a.AUDSID,
       a.SADDR,
       a.WAIT_CLASS,
       a.EVENT,
       a.STATE,
       a.WAIT_TIME,
       a.SECONDS_IN_WAIT,
       a.P1,
       a.P1TEXT,
       a.p2,
       a.P2TEXT,
       a.p3,
       a.P3TEXT,
       A.BLOCKING_SESSION_STATUS,
       A.BLOCKING_SESSION,
       A.BLOCKING_INSTANCE,
       (SELECT NB.SQL_TEXT
        FROM   V$SQL NB
        WHERE  NB.SQL_ID = A.SQL_ID
        AND    rownum = 1) SQL_TEXT,
       (SELECT NB.SQL_FULLTEXT
        FROM   V$SQL NB
        WHERE  NB.SQL_ID = A.SQL_ID
        AND    rownum = 1) SQL_FULLTEXT,
       (SELECT NB.executions
        FROM   V$SQL NB
        WHERE  NB.SQL_ID = A.SQL_ID
        AND    NB.CHILD_NUMBER = A.SQL_CHILD_NUMBER
        AND    rownum = 1) 语句执行次数,
       a.SQL_ID SQL_ID,
       a.SQL_ADDRESS,
       a.SQL_HASH_VALUE,
       a.PREV_HASH_VALUE,
       a.PREV_SQL_ADDR,
       a.PREV_SQL_ID,
       a.ACTION_HASH session_action_hash,
       (SELECT NB.ACTION_HASH
        FROM   V$SQL NB
        WHERE  NB.SQL_ID = A.SQL_ID
        AND    NB.CHILD_NUMBER = A.SQL_CHILD_NUMBER
        AND    rownum = 1) sql_ACTION_HASH,
       (SELECT se.PHYSICAL_READS FROM v$sess_io se WHERE se.SID = a.SID) PHYSICAL_READS,
       (SELECT se.block_changes FROM v$sess_io se WHERE se.SID = a.SID) block_changes,
       'select * from v$transaction v where v.ADDR=''' || a.TADDR || ''';' "事务信息",
       a.MODULE_HASH session_MODULE_HASH,
       (SELECT B.MODULE_HASH
        FROM   V$SQL B
        WHERE  b.ADDRESS = a.SQL_ADDRESS
        AND    b.HASH_VALUE = a.SQL_HASH_VALUE
        AND    rownum = 1) sql_MODULE_HASH,
       'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || a.SQL_ID ||''',' || a.SQL_CHILD_NUMBER || ',''advanced''));' sql_plan,
       'ALTER SYSTEM KILL SESSION ''' || a.SID || ',' || a.serial# || ''' IMMEDIATE;' kill_session,
       (SELECT (NB.VALUE)
        FROM   v$sesstat  Nb,
               v$statname Nc
        WHERE  NB.STATISTIC# = NC.STATISTIC#
        AND    NB.SID = A.SID
        AND    NC.NAME = 'session uga memory') "Current UGA MEMORY",
       (SELECT (NB.VALUE)
        FROM   v$sesstat  Nb,
               v$statname Nc
        WHERE  NB.STATISTIC# = NC.STATISTIC#
        AND    NB.SID = A.SID
        AND    NC.NAME = 'session pga memory') "Current PGA MEMORY",
       a.PDML_ENABLED,
       a.PDML_STATUS,
       a.PDDL_STATUS,
       a.PQ_STATUS
FROM   v$session a
LEFT   OUTER JOIN DBA_JOBS_RUNNING b
ON     a.SID = b.sid
WHERE  a.username IS NOT NULL
ORDER  BY a.logon_time DESC,
          a.SID        DESC
;

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

CREATE OR REPLACE VIEW VW_SESSION_LHR AS

SELECT a.username,

a.SID,

a.serial#,

(SELECT p.SPID FROM v$process p WHERE p.ADDR = a.PADDR) SPID,

a.STATUS,

(SELECT v.name FROM SYS.AUDIT_ACTIONS v WHERE v.action = a.COMMAND) COMMAND,

(SELECT d.OBJECT_NAME || ':' || d.OBJECT_TYPE

FROM dba_objects d

WHERE d.OBJECT_ID = a.ROW_WAIT_OBJ#) 等待对象,

CASE

WHEN (SELECT b.session_id

FROM dba_scheduler_running_jobs b

WHERE b.session_id = a.SID

AND rownum = 1) > 0 THEN

'DBMS_SCHEDULER_JOB'

WHEN (SELECT b.sid

FROM dba_jobs_running b

WHERE b.SID = a.SID

AND rownum = 1) > 0 THEN

'DBMS_JOB'

WHEN b.sid > 0 THEN

'DBMS_JOB'

WHEN a.MODULE != 'JDBC Thin Client' THEN

a.MACHINE || '---' || a.TERMINAL || '---' || a.MODULE ||

a.ACTION

ELSE

a.MODULE

END 会话类型,

CASE

WHEN (SELECT COUNT(1)

FROM v$px_session cn

WHERE cn.SID = cn.QCSID

AND cn.SID = a.SID) > 0 THEN

'Y'

WHEN (SELECT COUNT(1) FROM v$px_session cn WHERE cn.SID = a.SID) > 0 THEN

'N'

END AS 是否并行主进程,

a.logon_time,

(SYSDATE - a.LOGON_TIME)*24*60*60 会话持续时间,

a.osuser "客户端用户名",

a.PROCESS,

a.machine,

a.terminal,

a.program,

a.module,

a.action,

a.client_info,

a.AUDSID,

a.SADDR,

a.WAIT_CLASS,

a.EVENT,

a.STATE,

a.WAIT_TIME,

a.SECONDS_IN_WAIT,

a.P1,

a.P1TEXT,

a.p2,

a.P2TEXT,

a.p3,

a.P3TEXT,

A.BLOCKING_SESSION_STATUS,

A.BLOCKING_SESSION,

A.BLOCKING_INSTANCE,

(SELECT NB.SQL_TEXT

FROM V$SQL NB

WHERE NB.SQL_ID = A.SQL_ID

AND rownum = 1) SQL_TEXT,

(SELECT NB.SQL_FULLTEXT

FROM V$SQL NB

WHERE NB.SQL_ID = A.SQL_ID

AND rownum = 1) SQL_FULLTEXT,

(SELECT NB.executions

FROM V$SQL NB

WHERE NB.SQL_ID = A.SQL_ID

AND NB.CHILD_NUMBER = A.SQL_CHILD_NUMBER

AND rownum = 1) 语句执行次数,

a.SQL_ID SQL_ID,

a.SQL_ADDRESS,

a.SQL_HASH_VALUE,

a.PREV_HASH_VALUE,

a.PREV_SQL_ADDR,

a.PREV_SQL_ID,

a.ACTION_HASH session_action_hash,

(SELECT NB.ACTION_HASH

FROM V$SQL NB

WHERE NB.SQL_ID = A.SQL_ID

AND NB.CHILD_NUMBER = A.SQL_CHILD_NUMBER

AND rownum = 1) sql_ACTION_HASH,

(SELECT se.PHYSICAL_READS FROM v$sess_io se WHERE se.SID = a.SID) PHYSICAL_READS,

(SELECT se.block_changes FROM v$sess_io se WHERE se.SID = a.SID) block_changes,

'select * from v$transaction v where v.ADDR=''' || a.TADDR || ''';' "事务信息",

a.MODULE_HASH session_MODULE_HASH,

(SELECT B.MODULE_HASH

FROM V$SQL B

WHERE b.ADDRESS = a.SQL_ADDRESS

AND b.HASH_VALUE = a.SQL_HASH_VALUE

AND rownum = 1) sql_MODULE_HASH,

'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || a.SQL_ID ||''',' || a.SQL_CHILD_NUMBER || ',''advanced''));' sql_plan,

'ALTER SYSTEM KILL SESSION ''' || a.SID || ',' || a.serial# || ''' IMMEDIATE;' kill_session,

(SELECT (NB.VALUE)

FROM v$sesstat Nb,

v$statname Nc

WHERE NB.STATISTIC# = NC.STATISTIC#

AND NB.SID = A.SID

AND NC.NAME = 'session uga memory') "Current UGA MEMORY",

(SELECT (NB.VALUE)

FROM v$sesstat Nb,

v$statname Nc

WHERE NB.STATISTIC# = NC.STATISTIC#

AND NB.SID = A.SID

AND NC.NAME = 'session pga memory') "Current PGA MEMORY",

a.PDML_ENABLED,

a.PDML_STATUS,

a.PDDL_STATUS,

a.PQ_STATUS

FROM v$session a

LEFT OUTER JOIN DBA_JOBS_RUNNING b

ON a.SID = b.sid

WHERE a.username IS NOT NULL

ORDER BY a.logon_time DESC,

a.SID DESC

;

V$SQL_MONITOR

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
;

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

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

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

;

V$ACTIVE_SESSION_HISTORY

视图：VW_ASH_SESSION_LHR

CREATE OR REPLACE VIEW VW_ASH_SESSION_LHR AS
SELECT B.INST_ID,
       B.SESSION_ID,
       B.SESSION_SERIAL#,
       A.PARSING_SCHEMA_NAME USERNAME,
       A.SQL_TEXT SQL_TEXT,
       A.SQL_FULLTEXT SQL_FULLTEXT,
       B.SQL_PLAN_OPERATION PLAN_OPERATION,
       SQL_PLAN_OPTIONS,
       B.SQL_ID,
       B.SQL_EXEC_START,
       (A.ELAPSED_TIME / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS) / 1000000) ELAPSED_TIME,
       SYSDATE INDATE,
       ROUND(A.DISK_READS / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS)) MONITOR_VALUE,
       (B.MODULE || '--' || B.ACTION || '--' || B.PROGRAM || '--' ||
       B.MACHINE || '--' || B.CLIENT_ID || '--' || B.SESSION_TYPE) SESSION_INFO,
       EXECUTIONS,
       LAST_LOAD_TIME,
       LAST_ACTIVE_TIME,
       SESSION_TYPE,
       USER_ID,
       SQL_CHILD_NUMBER,
       SQL_OPNAME,
       SQL_EXEC_ID,
       B.SESSION_STATE,
       B.BLOCKING_SESSION,
       B.BLOCKING_SESSION_SERIAL#,
       B.BLOCKING_INST_ID,
       B.EVENT,
       B.COUNTS
  FROM GV$SQL A,
       (SELECT INST_ID,
               SESSION_ID,
               SESSION_SERIAL#,
               SESSION_TYPE,
               USER_ID,
               SQL_ID,
               SQL_CHILD_NUMBER,
               SQL_OPNAME,
               SQL_PLAN_OPERATION,
               SQL_PLAN_OPTIONS,
               SQL_EXEC_ID,
               SQL_EXEC_START,
               EVENT,
               WAIT_CLASS,
               SESSION_STATE,
               BLOCKING_SESSION,
               BLOCKING_SESSION_SERIAL#,
               BLOCKING_INST_ID,
               PROGRAM,
               MODULE,
               ACTION,
               CLIENT_ID,
               MACHINE,
               COUNT(*) COUNTS
          FROM GV$ACTIVE_SESSION_HISTORY
         WHERE SAMPLE_TIME >= SYSDATE - 30 / 1440
           AND SQL_ID IS NOT NULL
         GROUP BY INST_ID,
                  SESSION_ID,
                  SESSION_SERIAL#,
                  SESSION_TYPE,
                  USER_ID,
                  SQL_ID,
                  SQL_CHILD_NUMBER,
                  SQL_OPNAME,
                  SQL_PLAN_OPERATION,
                  SQL_PLAN_OPTIONS,
                  SQL_EXEC_ID,
                  SQL_EXEC_START,
                  EVENT,
                  WAIT_CLASS,
                  SESSION_STATE,
                  BLOCKING_SESSION,
                  BLOCKING_SESSION_SERIAL#,
                  BLOCKING_INST_ID,
                  PROGRAM,
                  MODULE,
                  ACTION,
                  CLIENT_ID,
                  MACHINE
        HAVING COUNT(*) > 6) B
 WHERE A.SQL_ID = B.SQL_ID
   AND A.INST_ID = B.INST_ID
   AND A.CHILD_NUMBER = B.SQL_CHILD_NUMBER
   AND A.LAST_ACTIVE_TIME > SYSDATE - 30 / 1440
   AND A.SQL_TEXT NOT LIKE '/* SQL Analyze(%';

CREATE OR REPLACE VIEW VW_ASH_SESSION_LHR AS

SELECT B.INST_ID,

B.SESSION_ID,

B.SESSION_SERIAL#,

A.PARSING_SCHEMA_NAME USERNAME,

A.SQL_TEXT SQL_TEXT,

A.SQL_FULLTEXT SQL_FULLTEXT,

B.SQL_PLAN_OPERATION PLAN_OPERATION,

SQL_PLAN_OPTIONS,

B.SQL_ID,

B.SQL_EXEC_START,

(A.ELAPSED_TIME / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS) / 1000000) ELAPSED_TIME,

SYSDATE INDATE,

ROUND(A.DISK_READS / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS)) MONITOR_VALUE,

(B.MODULE || '--' || B.ACTION || '--' || B.PROGRAM || '--' ||

B.MACHINE || '--' || B.CLIENT_ID || '--' || B.SESSION_TYPE) SESSION_INFO,

EXECUTIONS,

LAST_LOAD_TIME,

LAST_ACTIVE_TIME,

SESSION_TYPE,

USER_ID,

SQL_CHILD_NUMBER,

SQL_OPNAME,

SQL_EXEC_ID,

B.SESSION_STATE,

B.BLOCKING_SESSION,

B.BLOCKING_SESSION_SERIAL#,

B.BLOCKING_INST_ID,

B.EVENT,

B.COUNTS

FROM GV$SQL A,

(SELECT INST_ID,

SESSION_ID,

SESSION_SERIAL#,

SESSION_TYPE,

USER_ID,

SQL_ID,

SQL_CHILD_NUMBER,

SQL_OPNAME,

SQL_PLAN_OPERATION,

SQL_PLAN_OPTIONS,

SQL_EXEC_ID,

SQL_EXEC_START,

EVENT,

WAIT_CLASS,

SESSION_STATE,

BLOCKING_SESSION,

BLOCKING_SESSION_SERIAL#,

BLOCKING_INST_ID,

PROGRAM,

MODULE,

ACTION,

CLIENT_ID,

MACHINE,

COUNT(*) COUNTS

FROM GV$ACTIVE_SESSION_HISTORY

WHERE SAMPLE_TIME >= SYSDATE - 30 / 1440

AND SQL_ID IS NOT NULL

GROUP BY INST_ID,

SESSION_ID,

SESSION_SERIAL#,

SESSION_TYPE,

USER_ID,

SQL_ID,

SQL_CHILD_NUMBER,

SQL_OPNAME,

SQL_PLAN_OPERATION,

SQL_PLAN_OPTIONS,

SQL_EXEC_ID,

SQL_EXEC_START,

EVENT,

WAIT_CLASS,

SESSION_STATE,

BLOCKING_SESSION,

BLOCKING_SESSION_SERIAL#,

BLOCKING_INST_ID,

PROGRAM,

MODULE,

ACTION,

CLIENT_ID,

MACHINE

HAVING COUNT(*) > 6) B

WHERE A.SQL_ID = B.SQL_ID

AND A.INST_ID = B.INST_ID

AND A.CHILD_NUMBER = B.SQL_CHILD_NUMBER

AND A.LAST_ACTIVE_TIME > SYSDATE - 30 / 1440

AND A.SQL_TEXT NOT LIKE '/* SQL Analyze(%';

Oracle查询长时间运行的SQL语句的剩余时间：https://www.xmmup.com/oraclechaxunzhangshijianyunxingdesqlyujudeshengyushijian.html

另外，结合ASH,AWR,ADDM进行分析。

MySQL

show processlist:
show full processlist;

SELECT * FROM information_schema.`PROCESSLIST`;

show processlist:

show full processlist;

SELECT * FROM information_schema.`PROCESSLIST`;

另外，集合如下进行分析：

1、开启慢查询日志，分析慢查询日志，参考：MySQL之慢日志（慢查询日志，Slow Query Log）

2、AWR报告--diagnostics()存储过程

PostgreSQL

-- 慢查询会话
SELECT pgsa.pid,
      pgsa.client_port,
      pgsa.datname AS datname, 
      pgsa.usename AS usename, 
      pgsa.client_addr client_addr, 
      pgsa.application_name AS application_name, 
      pgsa.state AS state, 
      pgsa.backend_start AS backend_start, 
      pgsa.xact_start AS xact_start, 
      state_change,
      pgsa.query_start AS query_start, 
      TRUNC(EXTRACT( epoch  FROM  (NOW() - pgsa.xact_start)  )) AS xact_stay, 
      trunc(EXTRACT( epoch  FROM  (NOW() - pgsa.query_start) )) AS query_stay, 
      REPLACE(pgsa.QUERY, chr(10),  ' ') AS QUERY,
      pgsa.wait_event,pgsa.wait_event_type,pgsa.backend_type,
      'select pg_terminate_backend('||pgsa.pid||');' kill1,
      'select pg_cancel_backend('||pgsa.pid||');' kill2
    FROM   pg_stat_activity AS pgsa
    WHERE   pgsa.state not in ( 'idle' ,'idle in transaction (aborted)' )
ORDER BY  query_stay DESC,xact_stay DESC
LIMIT  100;

-- 杀会话
select 'select pg_terminate_backend('||a.pid||');' kill1,
'select pg_cancel_backend('||a.pid||');' kill2 
from pg_stat_activity   a
where pid<>pg_backend_pid()
and state  in ( 'idle');

-- 锁查询
SELECT pg_locks.pid as pid,  --进程ID
      transactionid as transaction_id, --事务ID
             pd.datname db_name,
      nspname as schemaname,  --schema名
      relname as object_name,  --对象名
      locktype as lock_type,  --锁类型
      mode lock_mode,  --锁模式
      CASE
        WHEN granted = 'f' THEN
         'get_lock'
        WHEN granted = 't' THEN
         'wait_lock'
      END lock_satus,   --锁状态：持有锁|等待锁
      least(query_start, xact_start) AS query_start,  --query请求开始时间
      substr(query, 1, 25) AS query_text   --当前SQL语句
 FROM pg_locks
    left join pg_database pd
    on (pg_locks.database=pd.oid)
 LEFT OUTER JOIN pg_class
   ON (pg_locks.relation = pg_class.oid)
 LEFT OUTER JOIN pg_namespace
   ON (pg_namespace.oid = pg_class.relnamespace),  pg_stat_activity
WHERE NOT pg_locks.pid = pg_backend_pid()
  AND pg_locks.pid = pg_stat_activity.pid
ORDER BY query_start;

-- 慢查询会话

SELECT pgsa.pid,

pgsa.client_port,

pgsa.datname AS datname,

pgsa.usename AS usename,

pgsa.client_addr client_addr,

pgsa.application_name AS application_name,

pgsa.state AS state,

pgsa.backend_start AS backend_start,

pgsa.xact_start AS xact_start,

state_change,

pgsa.query_start AS query_start,

TRUNC(EXTRACT( epoch FROM (NOW() - pgsa.xact_start) )) AS xact_stay,

trunc(EXTRACT( epoch FROM (NOW() - pgsa.query_start) )) AS query_stay,

REPLACE(pgsa.QUERY, chr(10), ' ') AS QUERY,

pgsa.wait_event,pgsa.wait_event_type,pgsa.backend_type,

'select pg_terminate_backend('||pgsa.pid||');' kill1,

'select pg_cancel_backend('||pgsa.pid||');' kill2

FROM pg_stat_activity AS pgsa

WHERE pgsa.state not in ( 'idle' ,'idle in transaction (aborted)' )

ORDER BY query_stay DESC,xact_stay DESC

LIMIT 100;

-- 杀会话

select 'select pg_terminate_backend('||a.pid||');' kill1,

'select pg_cancel_backend('||a.pid||');' kill2

from pg_stat_activity a

where pid<>pg_backend_pid()

and state in ( 'idle');

-- 锁查询

SELECT pg_locks.pid as pid, --进程ID

transactionid as transaction_id, --事务ID

pd.datname db_name,

nspname as schemaname, --schema名

relname as object_name, --对象名

locktype as lock_type, --锁类型

mode lock_mode, --锁模式

CASE

WHEN granted = 'f' THEN

'get_lock'

WHEN granted = 't' THEN

'wait_lock'

END lock_satus, --锁状态：持有锁|等待锁

least(query_start, xact_start) AS query_start, --query请求开始时间

substr(query, 1, 25) AS query_text --当前SQL语句

FROM pg_locks

left join pg_database pd

on (pg_locks.database=pd.oid)

LEFT OUTER JOIN pg_class

ON (pg_locks.relation = pg_class.oid)

LEFT OUTER JOIN pg_namespace

ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity

WHERE NOT pg_locks.pid = pg_backend_pid()

AND pg_locks.pid = pg_stat_activity.pid

ORDER BY query_start;

GreenPlum

-- 慢查询会话
SELECT pgsa.pid,
       pgsa.sess_id,
      pgsa.client_port,
      pgsa.datname AS datname, 
      pgsa.usename AS usename, 
      (SELECT nb.rrrsqname from gp_toolkit.gp_resq_role nb where nb.rrrolname=pgsa.usename) rsqname,
      pgsa.client_addr client_addr, 
      pgsa.application_name AS application_name, 
      pgsa.state AS state, 
      pgsa.waiting,
      pgsa.waiting_reason,
      pgsa.backend_start AS backend_start, 
      pgsa.xact_start AS xact_start, 
      state_change,
      pgsa.query_start AS query_start, 
      TRUNC(EXTRACT( epoch  FROM  (NOW() - pgsa.xact_start)  )) AS xact_stay, 
      trunc(EXTRACT( epoch  FROM  (NOW() - pgsa.query_start) )) AS query_stay, 
      REPLACE(pgsa.QUERY, chr(10),  ' ') AS QUERY,
      yc_filesize_GB,
      yc_numfiles, 
      'select pg_terminate_backend('||pgsa.pid||');' kill1,
      'select pg_cancel_backend('||pgsa.pid||');' kill2 
    FROM   pg_stat_activity AS pgsa
        -- 溢出文件大小查询
    LEFT JOIN (select pid,trunc(sum(size)/1024/1024/1024) as  yc_filesize_GB,sum(numfiles) as yc_numfiles from gp_toolkit.gp_workfile_usage_per_query nb group by pid) as d
    ON pgsa.pid=d.pid
    WHERE   pgsa.state not in ( 'idle','idle in transaction (aborted)' )
ORDER BY  query_stay DESC,xact_stay DESC
LIMIT  100;

-- 会话占用内存
SELECT  * 
FROM    session_state.session_level_memory_consumption d 
WHERE   d.datname IS NOT NULL 
ORDER BY
    d.vmem_mb DESC,
    d.sess_id,
    d.segid 
    LIMIT 20;

-- 锁
SELECT * FROM pg_locks;
select * from gp_toolkit.gp_locks_on_relation ;

SELECT pg_locks.pid as pid,  --进程ID
      transactionid as transaction_id, --事务ID
             pd.datname db_name,
      nspname as schemaname,  --schema名
      relname as object_name,  --对象名
      locktype as lock_type,  --锁类型
      mode lock_mode,  --锁模式
      CASE
        WHEN granted = 'f' THEN
         'get_lock'
        WHEN granted = 't' THEN
         'wait_lock'
      END lock_satus,   --锁状态：持有锁|等待锁
      CASE
        WHEN waiting = 'false' THEN
         'already get lock,sql executing'
        WHEN waiting = 'true' THEN
         'waiting get lock,sql waiting execute'
      END waitting_satus,  --当前会话状态：执行中|等待中
      least(query_start, xact_start) AS query_start,  --query请求开始时间
      substr(query, 1, 25) AS query_text   --当前SQL语句
 FROM pg_locks
    left join pg_database pd
    on (pg_locks.database=pd.oid)
 LEFT OUTER JOIN pg_class
   ON (pg_locks.relation = pg_class.oid)
 LEFT OUTER JOIN pg_namespace
   ON (pg_namespace.oid = pg_class.relnamespace),  pg_stat_activity
WHERE NOT pg_locks.pid = pg_backend_pid()
  AND pg_locks.pid = pg_stat_activity.pid
ORDER BY query_start;

-- 杀会话
select 'select pg_terminate_backend('||a.pid||');' kill1,
'select pg_cancel_backend('||a.pid||');' kill2 
from pg_stat_activity   a
where pid<>pg_backend_pid()
and state  in ( 'idle');

-- 慢查询会话

SELECT pgsa.pid,

pgsa.sess_id,

pgsa.client_port,

pgsa.datname AS datname,

pgsa.usename AS usename,

(SELECT nb.rrrsqname from gp_toolkit.gp_resq_role nb where nb.rrrolname=pgsa.usename) rsqname,

pgsa.client_addr client_addr,

pgsa.application_name AS application_name,

pgsa.state AS state,

pgsa.waiting,

pgsa.waiting_reason,

pgsa.backend_start AS backend_start,

pgsa.xact_start AS xact_start,

state_change,

pgsa.query_start AS query_start,

TRUNC(EXTRACT( epoch FROM (NOW() - pgsa.xact_start) )) AS xact_stay,

trunc(EXTRACT( epoch FROM (NOW() - pgsa.query_start) )) AS query_stay,

REPLACE(pgsa.QUERY, chr(10), ' ') AS QUERY,

yc_filesize_GB,

yc_numfiles,

'select pg_terminate_backend('||pgsa.pid||');' kill1,

'select pg_cancel_backend('||pgsa.pid||');' kill2

FROM pg_stat_activity AS pgsa

-- 溢出文件大小查询

LEFT JOIN (select pid,trunc(sum(size)/1024/1024/1024) as yc_filesize_GB,sum(numfiles) as yc_numfiles from gp_toolkit.gp_workfile_usage_per_query nb group by pid) as d

ON pgsa.pid=d.pid

WHERE pgsa.state not in ( 'idle','idle in transaction (aborted)' )

ORDER BY query_stay DESC,xact_stay DESC

LIMIT 100;

-- 会话占用内存

SELECT *

FROM session_state.session_level_memory_consumption d

WHERE d.datname IS NOT NULL

ORDER BY

d.vmem_mb DESC,

d.sess_id,

d.segid

LIMIT 20;

-- 锁

SELECT * FROM pg_locks;

select * from gp_toolkit.gp_locks_on_relation ;

SELECT pg_locks.pid as pid, --进程ID

transactionid as transaction_id, --事务ID

pd.datname db_name,

nspname as schemaname, --schema名

relname as object_name, --对象名

locktype as lock_type, --锁类型

mode lock_mode, --锁模式

CASE

WHEN granted = 'f' THEN

'get_lock'

WHEN granted = 't' THEN

'wait_lock'

END lock_satus, --锁状态：持有锁|等待锁

CASE

WHEN waiting = 'false' THEN

'already get lock,sql executing'

WHEN waiting = 'true' THEN

'waiting get lock,sql waiting execute'

END waitting_satus, --当前会话状态：执行中|等待中

least(query_start, xact_start) AS query_start, --query请求开始时间

substr(query, 1, 25) AS query_text --当前SQL语句

FROM pg_locks

left join pg_database pd

on (pg_locks.database=pd.oid)

LEFT OUTER JOIN pg_class

ON (pg_locks.relation = pg_class.oid)

LEFT OUTER JOIN pg_namespace

ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity

WHERE NOT pg_locks.pid = pg_backend_pid()

AND pg_locks.pid = pg_stat_activity.pid

ORDER BY query_start;

-- 杀会话

select 'select pg_terminate_backend('||a.pid||');' kill1,

'select pg_cancel_backend('||a.pid||');' kill2

from pg_stat_activity a

where pid<>pg_backend_pid()

and state in ( 'idle');

SQL server

主要是第1个SQL，包括了后续的SQL了


-- 当前正在执行的SQL(包括等待的SQL、当前正在堵塞与被堵塞SQL)
SELECT
    spid,
    blocked  blocking,
    P.dbid,
    db_name(P.dbid) dbname,
    (waittime/1000) waittime,
    lastwaittype,
    waitresource,
    open_tran,
    status,
    cpu,
    physical_io,
    memusage,
    login_time,
    last_batch,
    hostname,
    [program_name],
    hostprocess,
    cmd,
    nt_domain,
    nt_username,
    net_address,
    net_library,
    loginame,
    sql_handle,
    TEXT ,
    'kill '+cast(spid as varchar) kill1
FROM master.dbo.sysprocesses P CROSS apply sys.dm_exec_sql_text ( P.sql_handle ) s 
WHERE P.spid <>@@spid 
and (P.status !='sleeping' or (P.status='sleeping' and P.spid IN(select blocked from master.dbo.sysprocesses nb where blocked > 0 )))
order by waittime desc  
GO

-- 当前正在执行的SQL
SELECT  session_id spid,
    db_name( dbid ) AS dbname,
    transaction_id,
    wait_type,
    last_wait_type,
    wait_resource,
    start_time,
    status,
    estimated_completion_time,
    cpu_time,
    logical_reads,
    command,
    TEXT,
    open_transaction_count,
    open_resultset_count,
    percent_complete,
    'kill '+cast(session_id as varchar) kill1
FROM    sys.dm_exec_requests r CROSS apply sys.dm_exec_sql_text ( r.sql_handle ) s 
WHERE session_id <>@@spid
order by start_time 
GO

SELECT 
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
      1, 512)  AS statement_text  
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

-- 当前正在堵塞与被堵塞SQL
SELECT
    spid,
    blocked  blocking,
    P.dbid,
    db_name(P.dbid) dbname,
    waittime,
    lastwaittype,
    waitresource,
    open_tran,
    status,
    cpu,
    physical_io,
    memusage,
    login_time,
    last_batch,
    hostname,
    [program_name],
    hostprocess,
    cmd,
    nt_domain,
    nt_username,
    net_address,
    net_library,
    loginame,
    sql_handle,
    TEXT  ,
    'kill '+cast(spid as varchar) kill1
FROM master.dbo.sysprocesses P CROSS apply sys.dm_exec_sql_text ( P.sql_handle ) s 
WHERE blocked > 0 
OR spid IN ( SELECT sp.blocked FROM master.dbo.sysprocesses sp WHERE sp.blocked > 0 ) 
GO 

-- 是否有未提交事务
SELECT
    spid,
    blocked,
    waittime,
    waittype,
    waitresource,
    db_name ( P.dbid ) dbname,
    cpu,
    physical_io,
    memusage,
    open_tran,
    status,
    login_time,
    last_batch,
    hostname,
    program_name,
    hostprocess,
    loginame,
    cmd,
    TEXT  ,
    'kill '+cast(spid as varchar) kill1
FROM
    master.dbo.sysprocesses P CROSS apply sys.dm_exec_sql_text ( P.sql_handle ) s 
WHERE
    open_tran <> 0 --and last_batch < dateadd(minute,-15,getdate())
GO

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

-- 当前正在执行的SQL(包括等待的SQL、当前正在堵塞与被堵塞SQL)

SELECT

spid,

blocked blocking,

P.dbid,

db_name(P.dbid) dbname,

(waittime/1000) waittime,

lastwaittype,

waitresource,

open_tran,

status,

cpu,

physical_io,

memusage,

login_time,

last_batch,

hostname,

[program_name],

hostprocess,

cmd,

nt_domain,

nt_username,

net_address,

net_library,

loginame,

sql_handle,

TEXT ,

'kill '+cast(spid as varchar) kill1

FROM master.dbo.sysprocesses P CROSS apply sys.dm_exec_sql_text ( P.sql_handle ) s

WHERE P.spid <>@@spid

and (P.status !='sleeping' or (P.status='sleeping' and P.spid IN(select blocked from master.dbo.sysprocesses nb where blocked > 0 )))

order by waittime desc

-- 当前正在执行的SQL

SELECT session_id spid,

db_name( dbid ) AS dbname,

transaction_id,

wait_type,

last_wait_type,

wait_resource,

start_time,

status,

estimated_completion_time,

cpu_time,

logical_reads,

command,

TEXT,

open_transaction_count,

open_resultset_count,

percent_complete,

'kill '+cast(session_id as varchar) kill1

FROM sys.dm_exec_requests r CROSS apply sys.dm_exec_sql_text ( r.sql_handle ) s

WHERE session_id <>@@spid

order by start_time

SELECT

req.session_id

, req.total_elapsed_time AS duration_ms

, req.cpu_time AS cpu_time_ms

, req.total_elapsed_time - req.cpu_time AS wait_time

, req.logical_reads

, SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1

THEN DATALENGTH(ST.text)

ELSE req.statement_end_offset

END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '),

1, 512) AS statement_text

FROM sys.dm_exec_requests AS req

CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST

ORDER BY total_elapsed_time DESC;

-- 当前正在堵塞与被堵塞SQL

SELECT

spid,

blocked blocking,

P.dbid,

db_name(P.dbid) dbname,

waittime,

lastwaittype,

waitresource,

open_tran,

status,

cpu,

physical_io,

memusage,

login_time,

last_batch,

hostname,

[program_name],

hostprocess,

cmd,

nt_domain,

nt_username,

net_address,

net_library,

loginame,

sql_handle,

TEXT ,

'kill '+cast(spid as varchar) kill1

FROM master.dbo.sysprocesses P CROSS apply sys.dm_exec_sql_text ( P.sql_handle ) s

WHERE blocked > 0

OR spid IN ( SELECT sp.blocked FROM master.dbo.sysprocesses sp WHERE sp.blocked > 0 )

-- 是否有未提交事务

SELECT

spid,

blocked,

waittime,

waittype,

waitresource,

db_name ( P.dbid ) dbname,

cpu,

physical_io,

memusage,

open_tran,

status,

login_time,

last_batch,

hostname,

program_name,

hostprocess,

loginame,

cmd,

TEXT ,

'kill '+cast(spid as varchar) kill1

FROM

master.dbo.sysprocesses P CROSS apply sys.dm_exec_sql_text ( P.sql_handle ) s

WHERE

open_tran <> 0 --and last_batch < dateadd(minute,-15,getdate())

参考 SQL Server 排查异常SQL：https://www.xmmup.com/sql-server-paichayichangsql.html

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

1、最大内存配置

2、并行数最大量配置

打赏赞(1)

标签： DBA脚本 MSSQL MySQL Oracle PostgreSQL SQL Server 会话信息会话查询当前会话快速分析性能性能分析性能排查数据库杀会话汇总

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

2023年 3月
一	二	三	四	五	六	日
	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

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

小麦苗

数据库当前会话查询汇总（当前性能问题快速分析）

Oracle

V$SESSION

V$SQL_MONITOR

V$ACTIVE_SESSION_HISTORY

MySQL

PostgreSQL

GreenPlum

SQL server

相关文章

您可能还喜欢...

发表回复取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

数据库当前会话查询汇总（当前性能问题快速分析）

Oracle

V$SESSION

V$SQL_MONITOR

V$ACTIVE_SESSION_HISTORY

MySQL

PostgreSQL

GreenPlum

SQL server

相关文章

您可能还喜欢...

OLAP MPP分布式关系型数据库的双活容灾系统的设计

高可用异地多活架构设计

OLTP、OLAP和HTAP的区别

发表回复 取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

发表回复取消回复