oracle查询消耗服务器资源SQL语句

数据库   发布日期:2025年06月19日   浏览次数:136
  1. SELECT ASH.INST_ID,
  2. ASH.SQL_ID,
  3. (SELECT VS.SQL_TEXT
  4. FROM GV$SQLAREA VS
  5. WHERE VS.SQL_ID = ASH.SQL_ID
  6. AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
  7. ASH.SQL_CHILD_NUMBER,
  8. ASH.SQL_OPNAME,
  9. ASH.SESSION_INFO,
  10. COUNTS,
  11. PCTLOAD * || '%' PCTLOAD
  12. FROM (SELECT ASH.INST_ID,
  13. ASH.SQL_ID,
  14. ASH.SQL_CHILD_NUMBER,
  15. ASH.SQL_OPNAME,
  16. (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
  17. ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
  18. ASH.SESSION_TYPE) SESSION_INFO,
  19. COUNT(*) COUNTS,
  20. ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), ) PCTLOAD,
  21. DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
  22. FROM GV$ACTIVE_SESSION_HISTORY ASH
  23. WHERE ASH.SESSION_TYPE <> 'BACKGROUND'
  24. AND ASH.SESSION_STATE = 'ON CPU'
  25. AND SAMPLE_TIME > SYSDATE -
  26. GROUP BY ASH.INST_ID,
  27. ASH.SQL_ID,
  28. ASH.SQL_CHILD_NUMBER,
  29. ASH.SQL_OPNAME,
  30. (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
  31. ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
  32. ASH.SESSION_TYPE)) ASH
  33. WHERE RANK_ORDER <=
  34. ORDER BY COUNTS DESC;

2、查找最近一天内,最消耗CPU的会话

  1. SELECT SESSION_ID, COUNT(*)
  2. FROM V$ACTIVE_SESSION_HISTORY V
  3. WHERE V.SESSION_STATE = 'ON CPU'
  4. AND V.SAMPLE_TIME > SYSDATE -
  5. GROUP BY SESSION_ID
  6. ORDER BY COUNT(*) DESC;

3、查找最近一天内,最消耗I/O的SQL语句

  1. SELECT ASH.INST_ID,
  2. ASH.SQL_ID,
  3. (SELECT VS.SQL_TEXT
  4. FROM GV$SQLAREA VS
  5. WHERE VS.SQL_ID = ASH.SQL_ID
  6. AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
  7. ASH.SQL_CHILD_NUMBER,
  8. ASH.SQL_OPNAME,
  9. ASH.SESSION_INFO,
  10. COUNTS,
  11. PCTLOAD * || '%' PCTLOAD
  12. FROM (SELECT ASH.INST_ID,
  13. ASH.SQL_ID,
  14. ASH.SQL_CHILD_NUMBER,
  15. ASH.SQL_OPNAME,
  16. (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
  17. ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
  18. ASH.SESSION_TYPE) SESSION_INFO,
  19. COUNT(*) COUNTS,
  20. ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), ) PCTLOAD,
  21. DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
  22. FROM GV$ACTIVE_SESSION_HISTORY ASH
  23. WHERE ASH.SESSION_TYPE <> 'BACKGROUND'
  24. AND ASH.SESSION_STATE = 'WAITING'
  25. AND ASH.SAMPLE_TIME > SYSDATE -
  26. AND ASH.WAIT_CLASS = 'USER I/O'
  27. GROUP BY ASH.INST_ID,
  28. ASH.SQL_ID,
  29. ASH.SQL_CHILD_NUMBER,
  30. ASH.SQL_OPNAME,
  31. (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
  32. ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
  33. ASH.SESSION_TYPE)) ASH
  34. WHERE RANK_ORDER <=
  35. ORDER BY COUNTS DESC;

4、查找最近一天内,最消耗资源的SQL语句

  1. SELECT ASH.INST_ID,
  2. ASH.SQL_ID,
  3. (SELECT VS.SQL_TEXT
  4. FROM GV$SQLAREA VS
  5. WHERE VS.SQL_ID = ASH.SQL_ID
  6. AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
  7. ASH.SQL_CHILD_NUMBER,
  8. ASH.SQL_OPNAME,
  9. ASH.SESSION_INFO,
  10. COUNTS,
  11. PCTLOAD * || '%' PCTLOAD
  12. FROM (SELECT ASH.INST_ID,
  13. ASH.SQL_ID,
  14. ASH.SQL_CHILD_NUMBER,
  15. ASH.SQL_OPNAME,
  16. (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
  17. ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
  18. ASH.SESSION_TYPE) SESSION_INFO,
  19. COUNT(*) COUNTS,
  20. ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), ) PCTLOAD,
  21. DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
  22. FROM GV$ACTIVE_SESSION_HISTORY ASH
  23. WHERE ASH.SESSION_TYPE <> 'BACKGROUND'
  24. AND ASH.SESSION_STATE = 'WAITING'
  25. AND ASH.SAMPLE_TIME > SYSDATE -
  26. AND ASH.WAIT_CLASS = 'USER I/O'
  27. GROUP BY ASH.INST_ID,
  28. ASH.SQL_ID,
  29. ASH.SQL_CHILD_NUMBER,
  30. ASH.SQL_OPNAME,
  31. (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
  32. ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
  33. ASH.SESSION_TYPE)) ASH
  34. WHERE RANK_ORDER <=
  35. ORDER BY COUNTS DESC;

5、查找最近一天内,最消耗资源的会话

  1. SELECT ASH.SESSION_ID,
  2. ASH.SESSION_SERIAL#,
  3. ASH.USER_ID,
  4. ASH.PROGRAM,
  5. SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', , )) "CPU",
  6. SUM(DECODE(ASH.SESSION_STATE, 'WAITING', , )) -
  7. SUM(DECODE(ASH.SESSION_STATE,
  8. 'WAITING',
  9. DECODE(ASH.WAIT_CLASS, 'USER I/O', , ),
  10. )) "WAITING",
  11. SUM(DECODE(ASH.SESSION_STATE,
  12. 'WAITING',
  13. DECODE(ASH.WAIT_CLASS, 'USER I/O', , ),
  14. )) "IO",
  15. SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', , )) "TOTAL"
  16. FROM V$ACTIVE_SESSION_HISTORY ASH
  17. WHERE ASH.SAMPLE_TIME > SYSDATE -
  18. GROUP BY ASH.SESSION_ID, ASH.USER_ID, ASH.SESSION_SERIAL#, ASH.PROGRAM
  19. ORDER BY SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', , ));

 

以上就是oracle查询消耗服务器资源SQL语句的详细内容,更多关于oracle查询消耗服务器资源SQL语句的资料请关注九品源码其它相关文章!