oracle sql语句消耗,Oracle之查看最近最消耗CPU的SQL语句及会话信息的方法

导读：

1、查找最近一分钟内，最消耗CPU的SQL语句；

2、查找最近一分钟内，最消耗CPU的会话；

3、查找最近一分钟内，最消耗I/O的SQL语句；

4、查找最近一分钟内，最消耗资源的SQL语句；

5、查找最近一分钟内，最消耗资源的会话。

在Oracle中，查找最近一段时间，最消耗CPU的SQL语句及会话信息：

可以根据 V$ACTIVE_SESSION_HISTORY视图来获取。

1、查找最近一分钟内，最消耗CPU的SQL语句：

SELECT ASH.INST_ID,

ASH.SQL_ID,

(SELECT VS.SQL_TEXT

FROM GV$SQLAREA VS

WHERE VS.SQL_ID = ASH.SQL_ID

AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,

ASH.SQL_CHILD_NUMBER,

ASH.SQL_OPNAME,

ASH.SESSION_INFO,

COUNTS,

PCTLOAD * 100 || '%' PCTLOAD

FROM (SELECT ASH.INST_ID,

ASH.SQL_ID,

ASH.SQL_CHILD_NUMBER,

ASH.SQL_OPNAME,

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

ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||

ASH.SESSION_TYPE) SESSION_INFO,

COUNT(*) COUNTS,

ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,

DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER

FROM GV$ACTIVE_SESSION_HISTORY ASH

WHERE ASH.SESSION_TYPE <> 'BACKGROUND'

AND ASH.SESSION_STATE = 'ON CPU'

AND SAMPLE_TIME > SYSDATE - 1 / (24 * 60)

GROUP BY ASH.INST_ID,

ASH.SQL_ID,

ASH.SQL_CHILD_NUMBER,

ASH.SQL_OPNAME,

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

ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||

ASH.SESSION_TYPE)) ASH

WHERE RANK_ORDER <= 10

ORDER BY COUNTS DESC;

2、查找最近一分钟内，最消耗CPU的会话：

SELECT SESSION_ID,

COUNT(*)

FROM V$ACTIVE_SESSION_HISTORY V

WHERE V.SESSION_STATE = 'ON CPU'

AND V.SAMPLE_TIME > SYSDATE - 10/ (24 * 60)

GROUP BY SESSION_ID

ORDER BY COUNT(*) DESC;

3、查找最近一分钟内，最消耗I/O的SQL语句：

SELECT ASH.INST_ID,

ASH.SQL_ID,

(SELECT VS.SQL_TEXT

FROM GV$SQLAREA VS

WHERE VS.SQL_ID = ASH.SQL_ID

AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,

ASH.SQL_CHILD_NUMBER,

ASH.SQL_OPNAME,

ASH.SESSION_INFO,

COUNTS,

PCTLOAD * 100 || '%' PCTLOAD

FROM (SELECT ASH.INST_ID,

ASH.SQL_ID,

ASH.SQL_CHILD_NUMBER,

ASH.SQL_OPNAME,

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

ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||

ASH.SESSION_TYPE) SESSION_INFO,

COUNT(*) COUNTS,

ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,

DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER

FROM GV$ACTIVE_SESSION_HISTORY ASH

WHERE ASH.SESSION_TYPE <> 'BACKGROUND'

AND ASH.SESSION_STATE = 'WAITING'

AND ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)

AND ASH.WAIT_CLASS = 'USER I/O'

GROUP BY ASH.INST_ID,

ASH.SQL_ID,

ASH.SQL_CHILD_NUMBER,

ASH.SQL_OPNAME,

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

ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||

ASH.SESSION_TYPE)) ASH

WHERE RANK_ORDER <= 10

ORDER BY COUNTS DESC;

4、查找最近一分钟内，最消耗资源的SQL语句：

SELECT ASH.INST_ID,

ASH.SQL_ID,

(SELECT VS.SQL_TEXT

FROM GV$SQLAREA VS

WHERE VS.SQL_ID = ASH.SQL_ID

AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,

ASH.SQL_CHILD_NUMBER,

ASH.SQL_OPNAME,

ASH.SESSION_INFO,

COUNTS,

PCTLOAD * 100 || '%' PCTLOAD

FROM (SELECT ASH.INST_ID,

ASH.SQL_ID,

ASH.SQL_CHILD_NUMBER,

ASH.SQL_OPNAME,

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

ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||

ASH.SESSION_TYPE) SESSION_INFO,

COUNT(*) COUNTS,

ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,

DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER

FROM GV$ACTIVE_SESSION_HISTORY ASH

WHERE ASH.SESSION_TYPE <> 'BACKGROUND'

AND ASH.SESSION_STATE = 'WAITING'

AND ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)

AND ASH.WAIT_CLASS = 'USER I/O'

GROUP BY ASH.INST_ID,

ASH.SQL_ID,

ASH.SQL_CHILD_NUMBER,

ASH.SQL_OPNAME,

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

ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||

ASH.SESSION_TYPE)) ASH

WHERE RANK_ORDER <= 10

ORDER BY COUNTS DESC;

5、查找最近一分钟内，最消耗资源的会话：

SELECT ASH.SESSION_ID,

ASH.SESSION_SERIAL#,

ASH.USER_ID,

ASH.PROGRAM,

SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 0)) "CPU",

SUM(DECODE(ASH.SESSION_STATE, 'WAITING', 1, 0)) -

SUM(DECODE(ASH.SESSION_STATE,

'WAITING',

DECODE(ASH.WAIT_CLASS, 'USER I/O', 1, 0),

0)) "WAITING",

SUM(DECODE(ASH.SESSION_STATE,

'WAITING',

DECODE(ASH.WAIT_CLASS, 'USER I/O', 1, 0),

0)) "IO",

SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 1)) "TOTAL"

FROM V$ACTIVE_SESSION_HISTORY ASH

WHERE ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)

GROUP BY ASH.SESSION_ID, ASH.USER_ID, ASH.SESSION_SERIAL#, ASH.PROGRAM

ORDER BY SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 1));