
select a.LAST_CALL_ET,a.sid,a.status,a.sql_id,a.USERNAME,a.EVENT from v$session a where a.username is not null and a.PROGRAM='sqlplus.exe';
select a.LAST_CALL_ET,a.sid,a.status,a.sql_id,a.USERNAME,a.EVENT from v$session a where a.SQL_ID='3vn4sbyvsd08y';
select * from v$sql_monitor d where d.status not like 'DONE%';

SELECT dbms_sqltune.report_sql_monitor(sql_id  => '99qfh6psd3cpy',type  => 'html',report_level => 'all') FROM dual;

-- 慢SQL
SELECT a.USERNAME,
       (SELECT upper(nb.OSUSER) FROM v$session nb WHERE nb.SID = a.sid) OSUSER, 
       (SELECT nb.sid || ',' || nb.SERIAL# || ',' || pr.SPID
          FROM v$process pr, v$session nb
         WHERE nb.PADDR = pr.ADDR
           and nb.sid = a.SID
           and nb.SERIAL# = a.SERIAL#) session_info,
       a.opname,
       to_char(a.START_TIME, 'YYYY-MM-DD HH24:MI:SS') start_time,
       round(a.SOFAR * 100 / a.TOTALWORK, 2) || '%' AS progress,
       a.TIME_REMAINING  TIME_REMAINING,
       a.elapsed_seconds elapsed_seconds,
       message message,
       (SELECT nb.EVENT FROM V$session_Wait nb WHERE nb.SID = a.SID) wait_event,
       (SELECT nb.STATUS FROM v$session nb WHERE nb.SID = a.SID) STATUS
  FROM v$session_longops a
 WHERE a.time_remaining <> 0
 ORDER BY status, a.TIME_REMAINING DESC, a.SQL_ID, a.sid;

 -- 表空间
 WITH wt1 AS
 (SELECT ts.TABLESPACE_NAME,
         df.all_bytes,
         decode(df.TYPE,
                'D',
                nvl(fs.FREESIZ, 0),
                'T',
                df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,
         df.MAXSIZ,
         ts.BLOCK_SIZE,
         ts.LOGGING,
         ts.FORCE_LOGGING,
         ts.CONTENTS,
         ts.EXTENT_MANAGEMENT,
         ts.SEGMENT_SPACE_MANAGEMENT,
         ts.RETENTION,
         ts.DEF_TAB_COMPRESSION,
         ts.bigfile,
         df.ts_df_count
    FROM dba_tablespaces ts,
         (SELECT 'D' TYPE,
                 TABLESPACE_NAME,
                 COUNT(*) ts_df_count,
                 SUM(BYTES) all_bytes,
                 SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
            FROM dba_data_files d
           GROUP BY TABLESPACE_NAME
          UNION ALL
          SELECT 'T',
                 TABLESPACE_NAME,
                 COUNT(*) ts_df_count,
                 SUM(BYTES) all_bytes,
                 SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))
            FROM dba_temp_files d
           GROUP BY TABLESPACE_NAME) df,
         (SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ
            FROM dba_free_space
           GROUP BY TABLESPACE_NAME
          UNION ALL
          SELECT tablespace_name, SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
            FROM gv$sort_usage a, dba_tablespaces d
           WHERE a.tablespace = d.tablespace_name
           GROUP BY tablespace_name) fs
   WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
     AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))
SELECT (SELECT A.TS#
          FROM V$TABLESPACE A
         WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,
       t.TABLESPACE_NAME TS_Name,
       round(t.all_bytes / 1024 / 1024) ts_size_M,
       round(t.freesiz / 1024 / 1024) Free_Size_M,
       round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
       round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 2) Used_per,
       round(MAXSIZ / 1024 / 1024 / 1024, 2) MAX_Size_g,
       round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 /
             MAXSIZ,
             2) USED_per_MAX,
       round(t.BLOCK_SIZE) BLOCK_SIZE,
       t.LOGGING,
       t.FORCE_LOGGING,
       t.CONTENTS,
       t.EXTENT_MANAGEMENT,
       t.SEGMENT_SPACE_MANAGEMENT,
       t.RETENTION,
       t.DEF_TAB_COMPRESSION,
       t.bigfile,
       t.ts_df_count
  FROM wt1 t
UNION ALL
SELECT to_number('') TS#,
       'ALL TS:' TS_Name,
       round(SUM(t.all_bytes) / 1024 / 1024, 2) ts_size_M,
       round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,
       round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
       round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 2) Used_per,
       round(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_Size,
       to_number('') "USED,% of MAX Size",
       to_number('') BLOCK_SIZE,
       '' LOGGING,
       '' FORCE_LOGGING,
       '' CONTENTS,
       '' EXTENT_MANAGEMENT,
       '' SEGMENT_SPACE_MANAGEMENT,
       '' RETENTION,
       '' DEF_TAB_COMPRESSION,
       '' bigfile,
       to_number('') ts_df_count
  FROM wt1 t
 order by CONTENTS,TS#
 ;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

select a.LAST_CALL_ET,a.sid,a.status,a.sql_id,a.USERNAME,a.EVENT from v$session a where a.username is not null and a.PROGRAM='sqlplus.exe';

select a.LAST_CALL_ET,a.sid,a.status,a.sql_id,a.USERNAME,a.EVENT from v$session a where a.SQL_ID='3vn4sbyvsd08y';

select * from v$sql_monitor d where d.status not like 'DONE%';

SELECT dbms_sqltune.report_sql_monitor(sql_id => '99qfh6psd3cpy',type => 'html',report_level => 'all') FROM dual;

-- 慢SQL

SELECT a.USERNAME,

(SELECT upper(nb.OSUSER) FROM v$session nb WHERE nb.SID = a.sid) OSUSER,

(SELECT nb.sid || ',' || nb.SERIAL# || ',' || pr.SPID

FROM v$process pr, v$session nb

WHERE nb.PADDR = pr.ADDR

and nb.sid = a.SID

and nb.SERIAL# = a.SERIAL#) session_info,

a.opname,

to_char(a.START_TIME, 'YYYY-MM-DD HH24:MI:SS') start_time,

round(a.SOFAR * 100 / a.TOTALWORK, 2) || '%' AS progress,

a.TIME_REMAINING TIME_REMAINING,

a.elapsed_seconds elapsed_seconds,

message message,

(SELECT nb.EVENT FROM V$session_Wait nb WHERE nb.SID = a.SID) wait_event,

(SELECT nb.STATUS FROM v$session nb WHERE nb.SID = a.SID) STATUS

FROM v$session_longops a

WHERE a.time_remaining <> 0

ORDER BY status, a.TIME_REMAINING DESC, a.SQL_ID, a.sid;

-- 表空间

WITH wt1 AS

(SELECT ts.TABLESPACE_NAME,

df.all_bytes,

decode(df.TYPE,

'D',

nvl(fs.FREESIZ, 0),

'T',

df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,

df.MAXSIZ,

ts.BLOCK_SIZE,

ts.LOGGING,

ts.FORCE_LOGGING,

ts.CONTENTS,

ts.EXTENT_MANAGEMENT,

ts.SEGMENT_SPACE_MANAGEMENT,

ts.RETENTION,

ts.DEF_TAB_COMPRESSION,

ts.bigfile,

df.ts_df_count

FROM dba_tablespaces ts,

(SELECT 'D' TYPE,

TABLESPACE_NAME,

COUNT(*) ts_df_count,

SUM(BYTES) all_bytes,

SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ

FROM dba_data_files d

GROUP BY TABLESPACE_NAME

UNION ALL

SELECT 'T',

TABLESPACE_NAME,

COUNT(*) ts_df_count,

SUM(BYTES) all_bytes,

SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))

FROM dba_temp_files d

GROUP BY TABLESPACE_NAME) df,

(SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ

FROM dba_free_space

GROUP BY TABLESPACE_NAME

UNION ALL

SELECT tablespace_name, SUM(d.BLOCK_SIZE * a.BLOCKS) bytes

FROM gv$sort_usage a, dba_tablespaces d

WHERE a.tablespace = d.tablespace_name

GROUP BY tablespace_name) fs

WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME

AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))

SELECT (SELECT A.TS#

FROM V$TABLESPACE A

WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,

t.TABLESPACE_NAME TS_Name,

round(t.all_bytes / 1024 / 1024) ts_size_M,

round(t.freesiz / 1024 / 1024) Free_Size_M,

round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,

round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 2) Used_per,

round(MAXSIZ / 1024 / 1024 / 1024, 2) MAX_Size_g,

round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 /

MAXSIZ,

2) USED_per_MAX,

round(t.BLOCK_SIZE) BLOCK_SIZE,

t.LOGGING,

t.FORCE_LOGGING,

t.CONTENTS,

t.EXTENT_MANAGEMENT,

t.SEGMENT_SPACE_MANAGEMENT,

t.RETENTION,

t.DEF_TAB_COMPRESSION,

t.bigfile,

t.ts_df_count

FROM wt1 t

UNION ALL

SELECT to_number('') TS#,

'ALL TS:' TS_Name,

round(SUM(t.all_bytes) / 1024 / 1024, 2) ts_size_M,

round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,

round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,

round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 2) Used_per,

round(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_Size,

to_number('') "USED,% of MAX Size",

to_number('') BLOCK_SIZE,

'' LOGGING,

'' FORCE_LOGGING,

'' CONTENTS,

'' EXTENT_MANAGEMENT,

'' SEGMENT_SPACE_MANAGEMENT,

'' RETENTION,

'' DEF_TAB_COMPRESSION,

'' bigfile,

to_number('') ts_df_count

FROM wt1 t

order by CONTENTS,TS#

;

V$SESSION

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.wait_event,
      pgsa.wait_event_type,
      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.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;

-- 锁
-- granted=t是阻塞别人的，f是被阻塞的
SELECT database dbid, pd.datname, locktype, relation, relation::regclass relation_name, mode, pid, granted
FROM pg_locks pl left join pg_database pd
on pl.database=pd.oid
where pl.database > 0;

-- 锁查询
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 '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');

create view vw_lock_lhr as 
with    
t_wait as    
(    
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,    
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted   
),   
t_run as   
(   
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,   
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted   
),   
t_overlap as   
(   
  select r.* from t_wait w join t_run r on   
  (   
    r.locktype is not distinct from w.locktype and   
    r.database is not distinct from w.database and   
    r.relation is not distinct from w.relation and   
    r.page is not distinct from w.page and   
    r.tuple is not distinct from w.tuple and   
    r.virtualxid is not distinct from w.virtualxid and   
    r.transactionid is not distinct from w.transactionid and   
    r.classid is not distinct from w.classid and   
    r.objid is not distinct from w.objid and   
    r.objsubid is not distinct from w.objsubid and   
    r.pid <> w.pid   
  )    
),    
t_unionall as    
(    
  select r.* from t_overlap r    
  union all    
  select w.* from t_wait w    
)    
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,   
string_agg(   
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||   
'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||   
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||    
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||    
'SQL (Current SQL in Transaction): '||chr(10)||  
case when query is null then 'NULL' else query::text end,    
chr(10)||'--------'||chr(10)    
order by    
  (  case mode    
    when 'INVALID' then 0   
    when 'AccessShareLock' then 1   
    when 'RowShareLock' then 2   
    when 'RowExclusiveLock' then 3   
    when 'ShareUpdateExclusiveLock' then 4   
    when 'ShareLock' then 5   
    when 'ShareRowExclusiveLock' then 6   
    when 'ExclusiveLock' then 7   
    when 'AccessExclusiveLock' then 8   
    else 0   
  end  ) desc,   
  (case when granted then 0 else 1 end)  
) as lock_conflict  
from t_unionall   
group by   
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;  

create view vw_lock2_lhr as 
with recursive tmp_lock as (
    select distinct
           --w.mode w_mode,w.page w_page,
           --w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
           --now()-w.query_start w_locktime,w.query w_query 
           w.pid as id,--w_pid,
           r.pid as parentid--r_pid,
           --r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,
           --r.relation::regclass,
           --r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,
           --r.query_start r_query_start,
           --now()-r.query_start r_locktime,r.query r_query,   
    from (
          select a.mode,a.locktype,a.database,
                 a.relation,a.page,a.tuple,a.classid,
                 a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
                 a.transactionid,
                 b.query as query,
                 b.xact_start,b.query_start,b.usename,b.datname 
           from pg_locks a,
                pg_stat_activity b 
          where a.pid=b.pid 
            and not a.granted 
          ) w,
         (
          select a.mode,a.locktype,a.database,
                 a.relation,a.page,a.tuple,a.classid,
                 a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
                 a.transactionid,
                 b.query as query,
                 b.xact_start,b.query_start,b.usename,b.datname 
            from pg_locks a,
                 pg_stat_activity b -- select pg_typeof(pid) from pg_stat_activity
           where a.pid=b.pid 
             and a.granted 
          ) r 
    where 1=1
      and r.locktype is not distinct from w.locktype 
      and r.database is not distinct from w.database 
      and r.relation is not distinct from w.relation 
      and r.page is not distinct from w.page 
      and r.tuple is not distinct from w.tuple 
      and r.classid is not distinct from w.classid 
      and r.objid is not distinct from w.objid 
      and r.objsubid is not distinct from w.objsubid 
      and r.transactionid is not distinct from w.transactionid 
      and r.pid <> w.pid

),tmp0 as (
  select *
    from tmp_lock tl
   union all
  --查找root，同一时刻可能有多个root
  select t1.parentid,0::int4
    from tmp_lock t1
   where 1=1
     and t1.parentid not in (select id from tmp_lock)
),tmp3 (pathid,depth,id,parentid) as (
  --对过滤出的机构向下递归，构成tree
  SELECT array[id]::text[] as pathid,1 as depth,id,parentid
    FROM tmp0
   where 1=1
     and parentid=0
   union
  SELECT t0.pathid||array[t1.id]::text[] as pathid,t0.depth+1 as depth,t1.id,t1.parentid
    FROM tmp0 t1,  
         tmp3 t0
   where 1=1
     and t1.parentid=t0.id
)
select distinct 
       '/'||array_to_string(a0.pathid,'/') as pathid,
       a0.depth,
       a0.id,a0.parentid,lpad(a0.id::text, 2*a0.depth-1+length(a0.id::text),' ') as tree_id,
       --'select pg_cancel_backend('||a0.id|| ');' as cancel_pid,
       --'select pg_terminate_backend('||a0.id|| ');' as term_pid,
       case when a0.depth =1 then 'select pg_terminate_backend('|| a0.id || ');' else null end  as term_pid,
       case when a0.depth =1 then 'select cancel_backend('|| a0.id || ');' else null end  as cancel_pid
       ,a2.datname,a2.client_addr,a2.wait_event_type,a2.wait_event,a2.state
       --,a2.backend_start,a2.xact_start,a2.query_start
  from tmp3 a0
       left outer join (select distinct '/'||id||'/' as prefix_id,id
                        from tmp0
             where 1=1 ) a1
                  on position( a1.prefix_id in '/'||array_to_string(a0.pathid,'/')||'/' ) >0
     left outer join pg_stat_activity a2 -- select * from pg_stat_activity
                  on a0.id = a2.pid
order by '/'||array_to_string(a0.pathid,'/'),a0.depth;

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

-- 慢查询会话

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.wait_event,

pgsa.wait_event_type,

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.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;

-- 锁

-- granted=t是阻塞别人的，f是被阻塞的

SELECT database dbid, pd.datname, locktype, relation, relation::regclass relation_name, mode, pid, granted

FROM pg_locks pl left join pg_database pd

on pl.database=pd.oid

where pl.database > 0;

-- 锁查询

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 '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');

create view vw_lock_lhr as

with

t_wait as

(

select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,

a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,

b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name

from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted

t_run as

(

select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,

a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,

b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name

from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted

t_overlap as

(

select r.* from t_wait w join t_run r on

(

r.locktype is not distinct from w.locktype and

r.database is not distinct from w.database and

r.relation is not distinct from w.relation and

r.page is not distinct from w.page and

r.tuple is not distinct from w.tuple and

r.virtualxid is not distinct from w.virtualxid and

r.transactionid is not distinct from w.transactionid and

r.classid is not distinct from w.classid and

r.objid is not distinct from w.objid and

r.objsubid is not distinct from w.objsubid and

r.pid <> w.pid

)

t_unionall as

(

select r.* from t_overlap r

union all

select w.* from t_wait w

)

select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,

string_agg(

'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||

'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||

'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||

'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||

'SQL (Current SQL in Transaction): '||chr(10)||

case when query is null then 'NULL' else query::text end,

chr(10)||'--------'||chr(10)

order by

( case mode

when 'INVALID' then 0

when 'AccessShareLock' then 1

when 'RowShareLock' then 2

when 'RowExclusiveLock' then 3

when 'ShareUpdateExclusiveLock' then 4

when 'ShareLock' then 5

when 'ShareRowExclusiveLock' then 6

when 'ExclusiveLock' then 7

when 'AccessExclusiveLock' then 8

else 0

end ) desc,

(case when granted then 0 else 1 end)

) as lock_conflict

from t_unionall

group by

locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;

create view vw_lock2_lhr as

with recursive tmp_lock as (

select distinct

--w.mode w_mode,w.page w_page,

--w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,

--now()-w.query_start w_locktime,w.query w_query

w.pid as id,--w_pid,

r.pid as parentid--r_pid,

--r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,

--r.relation::regclass,

--r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,

--r.query_start r_query_start,

--now()-r.query_start r_locktime,r.query r_query,

from (

select a.mode,a.locktype,a.database,

a.relation,a.page,a.tuple,a.classid,

a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,

a.transactionid,

b.query as query,

b.xact_start,b.query_start,b.usename,b.datname

from pg_locks a,

pg_stat_activity b

where a.pid=b.pid

and not a.granted

) w,

(

select a.mode,a.locktype,a.database,

a.relation,a.page,a.tuple,a.classid,

a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,

a.transactionid,

b.query as query,

b.xact_start,b.query_start,b.usename,b.datname

from pg_locks a,

pg_stat_activity b -- select pg_typeof(pid) from pg_stat_activity

where a.pid=b.pid

and a.granted

) r

where 1=1

and r.locktype is not distinct from w.locktype

and r.database is not distinct from w.database

and r.relation is not distinct from w.relation

and r.page is not distinct from w.page

and r.tuple is not distinct from w.tuple

and r.classid is not distinct from w.classid

and r.objid is not distinct from w.objid

and r.objsubid is not distinct from w.objsubid

and r.transactionid is not distinct from w.transactionid

and r.pid <> w.pid

),tmp0 as (

select *

from tmp_lock tl

union all

--查找root，同一时刻可能有多个root

select t1.parentid,0::int4

from tmp_lock t1

where 1=1

and t1.parentid not in (select id from tmp_lock)

),tmp3 (pathid,depth,id,parentid) as (

--对过滤出的机构向下递归，构成tree

SELECT array[id]::text[] as pathid,1 as depth,id,parentid

FROM tmp0

where 1=1

and parentid=0

union

SELECT t0.pathid||array[t1.id]::text[] as pathid,t0.depth+1 as depth,t1.id,t1.parentid

FROM tmp0 t1,

tmp3 t0

where 1=1

and t1.parentid=t0.id

)

select distinct

'/'||array_to_string(a0.pathid,'/') as pathid,

a0.depth,

a0.id,a0.parentid,lpad(a0.id::text, 2*a0.depth-1+length(a0.id::text),' ') as tree_id,

--'select pg_cancel_backend('||a0.id|| ');' as cancel_pid,

--'select pg_terminate_backend('||a0.id|| ');' as term_pid,

case when a0.depth =1 then 'select pg_terminate_backend('|| a0.id || ');' else null end as term_pid,

case when a0.depth =1 then 'select cancel_backend('|| a0.id || ');' else null end as cancel_pid

,a2.datname,a2.client_addr,a2.wait_event_type,a2.wait_event,a2.state

--,a2.backend_start,a2.xact_start,a2.query_start

from tmp3 a0

left outer join (select distinct '/'||id||'/' as prefix_id,id

from tmp0

where 1=1 ) a1

on position( a1.prefix_id in '/'||array_to_string(a0.pathid,'/')||'/' ) >0

left outer join pg_stat_activity a2 -- select * from pg_stat_activity

on a0.id = a2.pid

order by '/'||array_to_string(a0.pathid,'/'),a0.depth;

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;

-- 锁
-- granted=t是阻塞别人的，f是被阻塞的
SELECT database, locktype, relation, relation::regclass, mode, pid FROM pg_locks where granted='f';
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, 200) 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');

-- 锁详情
create view vw_lock_lhr as 
with    
t_wait as    
(    
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,    
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted   
),   
t_run as   
(   
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,   
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted   
),   
t_overlap as   
(   
  select r.* from t_wait w join t_run r on   
  (   
    r.locktype is not distinct from w.locktype and   
    r.database is not distinct from w.database and   
    r.relation is not distinct from w.relation and   
    r.page is not distinct from w.page and   
    r.tuple is not distinct from w.tuple and   
    r.virtualxid is not distinct from w.virtualxid and   
    r.transactionid is not distinct from w.transactionid and   
    r.classid is not distinct from w.classid and   
    r.objid is not distinct from w.objid and   
    r.objsubid is not distinct from w.objsubid and   
    r.pid <> w.pid   
  )    
),    
t_unionall as    
(    
  select r.* from t_overlap r    
  union all    
  select w.* from t_wait w    
)    
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,   
string_agg(   
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||   
'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||   
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||    
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||    
'SQL (Current SQL in Transaction): '||chr(10)||  
case when query is null then 'NULL' else query::text end,    
chr(10)||'--------'||chr(10)    
order by    
  (  case mode    
    when 'INVALID' then 0   
    when 'AccessShareLock' then 1   
    when 'RowShareLock' then 2   
    when 'RowExclusiveLock' then 3   
    when 'ShareUpdateExclusiveLock' then 4   
    when 'ShareLock' then 5   
    when 'ShareRowExclusiveLock' then 6   
    when 'ExclusiveLock' then 7   
    when 'AccessExclusiveLock' then 8   
    else 0   
  end  ) desc,   
  (case when granted then 0 else 1 end)  
) as lock_conflict  
from t_unionall   
group by   
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;

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

-- 慢查询会话

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;

-- 锁

-- granted=t是阻塞别人的，f是被阻塞的

SELECT database, locktype, relation, relation::regclass, mode, pid FROM pg_locks where granted='f';

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, 200) 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');

-- 锁详情

create view vw_lock_lhr as

with

t_wait as

(

select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,

a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,

b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name

from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted

t_run as

(

select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,

a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,

b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name

from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted

t_overlap as

(

select r.* from t_wait w join t_run r on

(

r.locktype is not distinct from w.locktype and

r.database is not distinct from w.database and

r.relation is not distinct from w.relation and

r.page is not distinct from w.page and

r.tuple is not distinct from w.tuple and

r.virtualxid is not distinct from w.virtualxid and

r.transactionid is not distinct from w.transactionid and

r.classid is not distinct from w.classid and

r.objid is not distinct from w.objid and

r.objsubid is not distinct from w.objsubid and

r.pid <> w.pid

)

t_unionall as

(

select r.* from t_overlap r

union all

select w.* from t_wait w

)

select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,

string_agg(

'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||

'SQL (Current SQL in Transaction): '||chr(10)||

case when query is null then 'NULL' else query::text end,

chr(10)||'--------'||chr(10)

order by

( case mode

when 'INVALID' then 0

when 'AccessShareLock' then 1

when 'RowShareLock' then 2

when 'RowExclusiveLock' then 3

when 'ShareUpdateExclusiveLock' then 4

when 'ShareLock' then 5

when 'ShareRowExclusiveLock' then 6

when 'ExclusiveLock' then 7

when 'AccessExclusiveLock' then 8

else 0

end ) desc,

(case when granted then 0 else 1 end)

) as lock_conflict

from t_unionall

group by

locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;

SQL Server

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

-- sp_who2;

-- sys.dm_exec_requests是 master.dbo.sysprocesses的替代视图
-- 当前正在执行的SQL(包括等待的SQL、当前正在堵塞与被堵塞SQL、还原进度、恢复进度等)
-- sp_who2;

SELECT a.percent_complete,
    a.spid,
    (SELECT count(*) FROM master.dbo.sysprocesses sp where sp.spid=a.spid) thread_cnt,
    -- a.dop,
    blocking_session_id blocking_spid,
    a.database_id,
    db_name(a.database_id) dbname,
    (wait_time/1000) wait_time,
    start_time,
    sp.last_batch,
    wait_type,
    last_wait_type,
    wait_resource,
    open_transaction_count,
    a.status,
    a.command,
    a.cpu_time/1000 cpu_time,
    a.total_elapsed_time/1000 total_elapsed_time,
    a.writes,
    a.logical_reads,
    sp.physical_io,
    a.row_count,
    a.sql_handle,
    SUBSTRING (c.text,a.statement_start_offset/2,
       (CASE WHEN a.statement_end_offset = -1  THEN LEN(CONVERT(NVARCHAR(MAX), c.text)) * 2
            ELSE a.statement_end_offset END - a.statement_start_offset)/2) internal_SQL_TEXT,
    c.text Parent_SQL_TEXT,
    -- a.plan_handle,
    b.query_plan,
   'kill '+cast(a.spid as varchar) kill1
FROM  (select nb.session_id spid,nb.* from sys.dm_exec_requests nb WITH(NOLOCK)) a
LEFT JOIN master.dbo.sysprocesses sp WITH(NOLOCK) 
on  sp.spid=a.spid and sp.ecid=0
LEFT JOIN
 (select n1.session_id ,n2.query_plan 
 from sys.dm_exec_requests n1  WITH(NOLOCK)  CROSS APPLY  sys.dm_exec_query_plan(n1.plan_handle) AS n2  ) b
on a.spid=b.session_id
LEFT JOIN
( SELECT n1.session_id ,n2.text
 FROM sys.dm_exec_requests n1 WITH(NOLOCK)  CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2    ) c
on a.spid=c.session_id
WHERE a.spid <>@@spid 
and
(a.status !='sleeping' 
    or (a.status='sleeping' and a.spid IN (select blocking_session_id from sys.dm_exec_requests nb where open_transaction_count > 0 )) 
    or (a.status='sleeping' and a.open_transaction_count > 0 )
    ) 
and a.status!='background'
order by total_elapsed_time desc 
GO

SELECT sp.status,sp.cmd,sp.lastwaittype,sp.waitresource,sp.cpu,sp.physical_io,sp.* 
FROM master.dbo.sysprocesses sp WITH(NOLOCK) 
where sp.spid=85;

-- 或者
SELECT er.percent_complete,
    P.spid,
    P.cnt thread_cnt,
    blocking,
    P.dbid,
    db_name(P.dbid) dbname,
    (waittime/1000) waittime,
    lastwaittype,
    waitresource,
    open_tran,
    P.status,
    cpu,
    physical_io,
    memusage,
    login_time,
    last_batch,
    hostname,
    [program_name],
    hostprocess,
    cmd,
    nt_domain,
    nt_username,
    net_address,
    net_library,
    loginame,
    P.sql_handle,
    SUBSTRING (s.text,er.statement_start_offset/2,
       (CASE WHEN er.statement_end_offset = -1  THEN LEN(CONVERT(NVARCHAR(MAX), s.text)) * 2
            ELSE er.statement_end_offset END - er.statement_start_offset)/2) internal_SQL_TEXT,
    s.text Parent_SQL_TEXT,
    er.plan_handle,
    er.query_plan,
   'kill '+cast(P.spid as varchar) kill1
FROM ( select nb.spid,dbid,nb.hostname,nb.program_name,nb.hostprocess,nb.cmd,nb.nt_domain,nb.nt_username, nb.net_address, nb.net_library,
max(nb.blocked) blocking, (count(distinct nb.blocked)-1)  blocking_cnt,
max(nb.waittime) waittime,
max(nb.waittype) waittype,(count(distinct nb.waittype)-1)  waittype_cnt,
max(nb.lastwaittype) lastwaittype,
max(nb.waitresource) waitresource,
sum(cast(nb.cpu as bigint)) cpu,
sum(nb.physical_io) physical_io,
max(nb.login_time) login_time,
max(nb.last_batch) last_batch,
sum(nb.open_tran) open_tran,
max(nb.loginame) loginame,
max(nb.memusage) memusage,
max(nb.status) status,
max(nb.sql_handle) sql_handle,
count(*) cnt 
from master.dbo.sysprocesses nb  
where  (nb.status !='sleeping' 
    or (nb.status='sleeping' and nb.spid IN(select blocked from master.dbo.sysprocesses nb where blocked > 0 )) 
    or (nb.status='sleeping' and nb.open_tran > 0 )
    ) 
and nb.status!='background'
group by spid,dbid,nb.hostname,nb.program_name,nb.hostprocess,nb.cmd ,nb.nt_domain,nb.nt_username, nb.net_address, nb.net_library ) P 
LEFT JOIN (select n1.percent_complete,n1.session_id ,n1.statement_start_offset,n1.statement_end_offset,n1.plan_handle,n2.query_plan 
 from sys.dm_exec_requests n1  CROSS APPLY  sys.dm_exec_query_plan(n1.plan_handle) AS n2 ) er  
ON er.session_id = P.spid
LEFT JOIN ( SELECT n1.spid,n2.text FROM master.dbo.sysprocesses n1 CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2  ) s
on P.spid=s.spid
WHERE P.spid <>@@spid 
order by waittime desc 
GO

-- 分步查看所有进程
select
    nb.spid,
    count(*) thread_cnt, 
    (case when(max ( nb.blocked )=nb.spid) then 0 else max ( nb.blocked ) end ) blocking,
    dbid,db_name(nb.dbid) dbname,
    max ( nb.waittime/1000 ) waittime,
    max ( nb.waittype ) waittype,
    ( count ( distinct nb.waittype ) - 1 ) waittype_cnt,
    max ( nb.lastwaittype ) lastwaittype,
    max ( nb.waitresource ) waitresource,
    sum ( cast ( nb.cpu as bigint ) ) cpu,
    sum ( nb.physical_io ) physical_io,
    max ( nb.login_time ) login_time,
    max ( nb.last_batch ) last_batch,
    sum ( nb.open_tran ) open_tran,
    max ( nb.loginame ) loginame,
    max ( nb.memusage ) memusage,
    max ( nb.status ) status,
    max ( nb.sql_handle ) sql_handle,
    nb.hostname,
    nb.program_name,
    nb.hostprocess,
    nb.cmd,
    nb.nt_domain,
    nb.nt_username,
    nb.net_address,
    nb.net_library 
from
    master.dbo.sysprocesses nb 
where
    (
        nb.status != 'sleeping' 
        or ( nb.status= 'sleeping' and nb.spid IN ( select blocked from master.dbo.sysprocesses nb where blocked > 0 ) ) 
        or ( nb.status= 'sleeping' and nb.open_tran > 0 ) 
    ) 
    and nb.status!= 'background' 
group by spid,dbid, nb.hostname,    nb.program_name,    nb.hostprocess, nb.cmd ,    nb.nt_domain,   nb.nt_username, nb.net_address, nb.net_library 
order by    waittime desc;

select er.percent_complete,SUBSTRING (s.text,er.statement_start_offset/2,
       (CASE WHEN er.statement_end_offset = -1  THEN LEN(CONVERT(NVARCHAR(MAX), s.text)) * 2
            ELSE er.statement_end_offset END - er.statement_start_offset)/2) internal_SQL_TEXT,
    s.text Parent_SQL_TEXT,
    er.plan_handle,
    er.query_plan,
   'kill '+cast(s.spid as varchar) kill1
from (select n1.percent_complete,n1.session_id ,n1.statement_start_offset,n1.statement_end_offset,n1.plan_handle,n2.query_plan 
 from sys.dm_exec_requests n1  CROSS APPLY  sys.dm_exec_query_plan(n1.plan_handle) AS n2 ) er  
LEFT JOIN ( SELECT n1.spid,n2.text FROM master.dbo.sysprocesses n1 CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2  ) s
on er.session_id=s.spid
WHERE s.spid =385  
GO

-- 历史SQL执行计划查看
SELECT
deqp.dbid,
db_name(deqp.dbid) dbname,
SUBSTRING (desql.text,deqs.statement_start_offset/2,
       (CASE WHEN deqs.statement_end_offset = -1  THEN LEN(CONVERT(NVARCHAR(MAX), desql.text)) * 2
            ELSE deqs.statement_end_offset END - deqs.statement_start_offset)/2)  internal_SQL_TEXT,
desql.text AS Parent_SQL_TEXT,
deqp.query_plan,
 (deqs.total_logical_reads + deqs.total_logical_writes)  Total_IO,
((deqs.total_logical_reads + deqs.total_logical_writes) / deqs.execution_count )   [Average_IO] ,
 deqs.execution_count,
deqs.*
FROM sys.dm_exec_query_stats AS deqs
CROSS apply sys.dm_exec_sql_text ( deqs.sql_handle ) desql
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE 1=1 
    and  db_name(deqp.dbid)='lhrdb'
    and desql.text like '%lhrdb.dbo.BigTable%'
   -- and deqs.last_execution_time < GETDATE() -- 指定查询的时间范围
ORDER BY
    deqs.last_execution_time DESC;

SELECT  
desql.dbid,
db_name(desql.dbid) dbname,
desql.text,
qp.query_plan,
 cp.*
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY
    sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS apply sys.dm_exec_sql_text ( cp.plan_handle ) desql
WHERE 1=1
 and  db_name(desql.dbid)='lhrdb'
 and desql.text like '%lhrdb.dbo.BigTable%'
 ;

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

-- sp_who2;

-- sys.dm_exec_requests是 master.dbo.sysprocesses的替代视图

-- 当前正在执行的SQL(包括等待的SQL、当前正在堵塞与被堵塞SQL、还原进度、恢复进度等)

-- sp_who2;

SELECT a.percent_complete,

a.spid,

(SELECT count(*) FROM master.dbo.sysprocesses sp where sp.spid=a.spid) thread_cnt,

-- a.dop,

blocking_session_id blocking_spid,

a.database_id,

db_name(a.database_id) dbname,

(wait_time/1000) wait_time,

start_time,

sp.last_batch,

wait_type,

last_wait_type,

wait_resource,

open_transaction_count,

a.status,

a.command,

a.cpu_time/1000 cpu_time,

a.total_elapsed_time/1000 total_elapsed_time,

a.writes,

a.logical_reads,

sp.physical_io,

a.row_count,

a.sql_handle,

SUBSTRING (c.text,a.statement_start_offset/2,

(CASE WHEN a.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), c.text)) * 2

ELSE a.statement_end_offset END - a.statement_start_offset)/2) internal_SQL_TEXT,

c.text Parent_SQL_TEXT,

-- a.plan_handle,

b.query_plan,

'kill '+cast(a.spid as varchar) kill1

FROM (select nb.session_id spid,nb.* from sys.dm_exec_requests nb WITH(NOLOCK)) a

LEFT JOIN master.dbo.sysprocesses sp WITH(NOLOCK)

on sp.spid=a.spid and sp.ecid=0

LEFT JOIN

(select n1.session_id ,n2.query_plan

from sys.dm_exec_requests n1 WITH(NOLOCK) CROSS APPLY sys.dm_exec_query_plan(n1.plan_handle) AS n2 ) b

on a.spid=b.session_id

LEFT JOIN

( SELECT n1.session_id ,n2.text

FROM sys.dm_exec_requests n1 WITH(NOLOCK) CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2 ) c

on a.spid=c.session_id

WHERE a.spid <>@@spid

and

(a.status !='sleeping'

or (a.status='sleeping' and a.spid IN (select blocking_session_id from sys.dm_exec_requests nb where open_transaction_count > 0 ))

or (a.status='sleeping' and a.open_transaction_count > 0 )

)

and a.status!='background'

order by total_elapsed_time desc

SELECT sp.status,sp.cmd,sp.lastwaittype,sp.waitresource,sp.cpu,sp.physical_io,sp.*

FROM master.dbo.sysprocesses sp WITH(NOLOCK)

where sp.spid=85;

-- 或者

SELECT er.percent_complete,

P.spid,

P.cnt thread_cnt,

blocking,

P.dbid,

db_name(P.dbid) dbname,

(waittime/1000) waittime,

lastwaittype,

waitresource,

open_tran,

P.status,

cpu,

physical_io,

memusage,

login_time,

last_batch,

hostname,

[program_name],

hostprocess,

cmd,

nt_domain,

nt_username,

net_address,

net_library,

loginame,

P.sql_handle,

SUBSTRING (s.text,er.statement_start_offset/2,

(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), s.text)) * 2

ELSE er.statement_end_offset END - er.statement_start_offset)/2) internal_SQL_TEXT,

s.text Parent_SQL_TEXT,

er.plan_handle,

er.query_plan,

'kill '+cast(P.spid as varchar) kill1

FROM ( select nb.spid,dbid,nb.hostname,nb.program_name,nb.hostprocess,nb.cmd,nb.nt_domain,nb.nt_username, nb.net_address, nb.net_library,

max(nb.blocked) blocking, (count(distinct nb.blocked)-1) blocking_cnt,

max(nb.waittime) waittime,

max(nb.waittype) waittype,(count(distinct nb.waittype)-1) waittype_cnt,

max(nb.lastwaittype) lastwaittype,

max(nb.waitresource) waitresource,

sum(cast(nb.cpu as bigint)) cpu,

sum(nb.physical_io) physical_io,

max(nb.login_time) login_time,

max(nb.last_batch) last_batch,

sum(nb.open_tran) open_tran,

max(nb.loginame) loginame,

max(nb.memusage) memusage,

max(nb.status) status,

max(nb.sql_handle) sql_handle,

count(*) cnt

from master.dbo.sysprocesses nb

where (nb.status !='sleeping'

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

or (nb.status='sleeping' and nb.open_tran > 0 )

)

and nb.status!='background'

group by spid,dbid,nb.hostname,nb.program_name,nb.hostprocess,nb.cmd ,nb.nt_domain,nb.nt_username, nb.net_address, nb.net_library ) P

LEFT JOIN (select n1.percent_complete,n1.session_id ,n1.statement_start_offset,n1.statement_end_offset,n1.plan_handle,n2.query_plan

from sys.dm_exec_requests n1 CROSS APPLY sys.dm_exec_query_plan(n1.plan_handle) AS n2 ) er

ON er.session_id = P.spid

LEFT JOIN ( SELECT n1.spid,n2.text FROM master.dbo.sysprocesses n1 CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2 ) s

on P.spid=s.spid

WHERE P.spid <>@@spid

order by waittime desc

-- 分步查看所有进程

select

nb.spid,

count(*) thread_cnt,

(case when(max ( nb.blocked )=nb.spid) then 0 else max ( nb.blocked ) end ) blocking,

dbid,db_name(nb.dbid) dbname,

max ( nb.waittime/1000 ) waittime,

max ( nb.waittype ) waittype,

( count ( distinct nb.waittype ) - 1 ) waittype_cnt,

max ( nb.lastwaittype ) lastwaittype,

max ( nb.waitresource ) waitresource,

sum ( cast ( nb.cpu as bigint ) ) cpu,

sum ( nb.physical_io ) physical_io,

max ( nb.login_time ) login_time,

max ( nb.last_batch ) last_batch,

sum ( nb.open_tran ) open_tran,

max ( nb.loginame ) loginame,

max ( nb.memusage ) memusage,

max ( nb.status ) status,

max ( nb.sql_handle ) sql_handle,

nb.hostname,

nb.program_name,

nb.hostprocess,

nb.cmd,

nb.nt_domain,

nb.nt_username,

nb.net_address,

nb.net_library

from

master.dbo.sysprocesses nb

where

(

nb.status != 'sleeping'

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

or ( nb.status= 'sleeping' and nb.open_tran > 0 )

)

and nb.status!= 'background'

group by spid,dbid, nb.hostname, nb.program_name, nb.hostprocess, nb.cmd , nb.nt_domain, nb.nt_username, nb.net_address, nb.net_library

order by waittime desc;

select er.percent_complete,SUBSTRING (s.text,er.statement_start_offset/2,

(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), s.text)) * 2

ELSE er.statement_end_offset END - er.statement_start_offset)/2) internal_SQL_TEXT,

s.text Parent_SQL_TEXT,

er.plan_handle,

er.query_plan,

'kill '+cast(s.spid as varchar) kill1

from (select n1.percent_complete,n1.session_id ,n1.statement_start_offset,n1.statement_end_offset,n1.plan_handle,n2.query_plan

from sys.dm_exec_requests n1 CROSS APPLY sys.dm_exec_query_plan(n1.plan_handle) AS n2 ) er

LEFT JOIN ( SELECT n1.spid,n2.text FROM master.dbo.sysprocesses n1 CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2 ) s

on er.session_id=s.spid

WHERE s.spid =385

-- 历史SQL执行计划查看

SELECT

deqp.dbid,

db_name(deqp.dbid) dbname,

SUBSTRING (desql.text,deqs.statement_start_offset/2,

(CASE WHEN deqs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), desql.text)) * 2

ELSE deqs.statement_end_offset END - deqs.statement_start_offset)/2) internal_SQL_TEXT,

desql.text AS Parent_SQL_TEXT,

deqp.query_plan,

(deqs.total_logical_reads + deqs.total_logical_writes) Total_IO,

((deqs.total_logical_reads + deqs.total_logical_writes) / deqs.execution_count ) [Average_IO] ,

deqs.execution_count,

deqs.*

FROM sys.dm_exec_query_stats AS deqs

CROSS apply sys.dm_exec_sql_text ( deqs.sql_handle ) desql

CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp

WHERE 1=1

and db_name(deqp.dbid)='lhrdb'

and desql.text like '%lhrdb.dbo.BigTable%'

-- and deqs.last_execution_time < GETDATE() -- 指定查询的时间范围

ORDER BY

deqs.last_execution_time DESC;

SELECT

desql.dbid,

db_name(desql.dbid) dbname,

desql.text,

qp.query_plan,

cp.*

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY

sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS apply sys.dm_exec_sql_text ( cp.plan_handle ) desql

WHERE 1=1

and db_name(desql.dbid)='lhrdb'

and desql.text like '%lhrdb.dbo.BigTable%'

;

历史SQL执行计划请参考：https://www.xmmup.com/mssqlchaxunlishisqlyujudezhixingjihua.html

其它SQL：


-- 简化版（无执行计划）
-- 当前正在执行的SQL(包括等待的SQL、当前正在堵塞与被堵塞SQL、还原进度、恢复进度等)
SELECT er.percent_complete,
    P.spid,
    blocked  blocking,
    P.dbid,
    db_name(P.dbid) dbname,
    (waittime/1000) waittime,
    lastwaittype,
    waitresource,
    open_tran,
    P.status,
    cpu,
    physical_io,
    memusage,
    login_time,
    last_batch,
    hostname,
    [program_name],
    hostprocess,
    cmd,
    nt_domain,
    nt_username,
    net_address,
    net_library,
    loginame,
    P.sql_handle,
    SUBSTRING (s.text,er.statement_start_offset/2,
       (CASE WHEN er.statement_end_offset = -1  THEN LEN(CONVERT(NVARCHAR(MAX), s.text)) * 2
            ELSE er.statement_end_offset END - er.statement_start_offset)/2) internal_SQL_TEXT,
    s.text Parent_SQL_TEXT,
   'kill '+cast(P.spid as varchar) kill1
FROM master.dbo.sysprocesses P 
 LEFT JOIN sys.dm_exec_requests er  
ON er.session_id = P.spid
LEFT JOIN ( SELECT n1.spid,n2.text FROM master.dbo.sysprocesses n1 CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2 ) s
on P.spid=s.spid
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 )) 
    or (P.status='sleeping' and P.open_tran >0 )
    )
and P.status!='background'        
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

137

138

139

140

141

142

143

144

145

146

147

148

-- 简化版（无执行计划）

-- 当前正在执行的SQL(包括等待的SQL、当前正在堵塞与被堵塞SQL、还原进度、恢复进度等)

SELECT er.percent_complete,

P.spid,

blocked blocking,

P.dbid,

db_name(P.dbid) dbname,

(waittime/1000) waittime,

lastwaittype,

waitresource,

open_tran,

P.status,

cpu,

physical_io,

memusage,

login_time,

last_batch,

hostname,

[program_name],

hostprocess,

cmd,

nt_domain,

nt_username,

net_address,

net_library,

loginame,

P.sql_handle,

SUBSTRING (s.text,er.statement_start_offset/2,

(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), s.text)) * 2

ELSE er.statement_end_offset END - er.statement_start_offset)/2) internal_SQL_TEXT,

s.text Parent_SQL_TEXT,

'kill '+cast(P.spid as varchar) kill1

FROM master.dbo.sysprocesses P

LEFT JOIN sys.dm_exec_requests er

ON er.session_id = P.spid

LEFT JOIN ( SELECT n1.spid,n2.text FROM master.dbo.sysprocesses n1 CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2 ) s

on P.spid=s.spid

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 ))

or (P.status='sleeping' and P.open_tran >0 )

)

and P.status!='background'

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())

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

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

其它主要优化：

1、最大内存配置

2、并行数最大量配置

存储过程定位慢SQL

参考：https://www.xmmup.com/tag/zhengzaizhixing

打赏赞(8)

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

小麦苗

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

发表回复取消回复

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

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

AI (2)
ClickHouse (6)
DM达梦 (9)
Docker (47)
GBase (37)
Greenplum (109)
HANA (2)
K8S (14)
Kafka (3)
Memcached (2)
MongoDB (5)
MySQL (211)
NoSQL (3)
OceanBase (14)
OGG (43)
openGauss (11)
Oracle (635)
OS (385)
PostgreSQL (260)
Python (26)
Redis (21)
SQL Server (161)
SQLite (1)
StarRocks (21)
TDSQL (5)
TiDB (19)
YMatrix (5)
中间件 (10)
云 (10)
人大金仓KingBase (9)
其它 (83)
培训考证 (45)
大数据 (6)
存储 (8)
巡检监控 (47)
工具 (101)
数据迁移 (16)
架构篇 (12)
生活 (11)
电脑 (18)
网络 (95)
育儿 (5)
视频 (17)
面试笔试 (38)

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

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

小麦苗

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

Oracle

常用

V$SESSION

V$SQL_MONITOR

V$ACTIVE_SESSION_HISTORY

MySQL

PostgreSQL

GreenPlum

SQL Server

存储过程定位慢SQL

相关文章

您可能还喜欢...

发表回复取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

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

Oracle

常用

V$SESSION

V$SQL_MONITOR

V$ACTIVE_SESSION_HISTORY

MySQL

PostgreSQL

GreenPlum

SQL Server

存储过程定位慢SQL

相关文章

您可能还喜欢...

GreenPlum、ClickHouse和StarRocks的比较

数据库范式和反范式介绍

高可用异地多活架构设计

发表回复 取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

发表回复取消回复