- 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 * || '%' 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(), ) 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 -
- 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 <=
- ORDER BY COUNTS DESC;
- SELECT SESSION_ID, COUNT(*)
- FROM V$ACTIVE_SESSION_HISTORY V
- WHERE V.SESSION_STATE = 'ON CPU'
- AND V.SAMPLE_TIME > SYSDATE -
- GROUP BY SESSION_ID
- ORDER BY COUNT(*) DESC;
- 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 * || '%' 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(), ) 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 -
- 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 <=
- ORDER BY COUNTS DESC;
- 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 * || '%' 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(), ) 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 -
- 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 <=
- ORDER BY COUNTS DESC;
- SELECT ASH.SESSION_ID,
- ASH.SESSION_SERIAL#,
- ASH.USER_ID,
- ASH.PROGRAM,
- SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', , )) "CPU",
- SUM(DECODE(ASH.SESSION_STATE, 'WAITING', , )) -
- SUM(DECODE(ASH.SESSION_STATE,
- 'WAITING',
- DECODE(ASH.WAIT_CLASS, 'USER I/O', , ),
- )) "WAITING",
- SUM(DECODE(ASH.SESSION_STATE,
- 'WAITING',
- DECODE(ASH.WAIT_CLASS, 'USER I/O', , ),
- )) "IO",
- SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', , )) "TOTAL"
- FROM V$ACTIVE_SESSION_HISTORY ASH
- WHERE ASH.SAMPLE_TIME > SYSDATE -
- GROUP BY ASH.SESSION_ID, ASH.USER_ID, ASH.SESSION_SERIAL#, ASH.PROGRAM
- ORDER BY SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', , ));
以上就是oracle查询消耗服务器资源SQL语句的详细内容,更多关于oracle查询消耗服务器资源SQL语句的资料请关注九品源码其它相关文章!