查找最消耗资源的SQL

sql

select machine,program, count(1) from v$session group by machine,program order by 3 desc

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

SELECT ASH.INST_ID,
ASH.SQL_ID,
(SELECT VS.SQL_TEXT
FROM GVSQLAREA 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 GVACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> ‘BACKGROUND’
AND ASH.SESSION_STATE = ‘ON CPU’
AND SAMPLE_TIME > SYSDATE - 1
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 - 1
GROUP BY SESSION_ID
ORDER BY COUNT() DESC;

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

SELECT ASH.INST_ID,
ASH.SQL_ID,
(SELECT VS.SQL_TEXT
FROM GVSQLAREA 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 GVACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> ‘BACKGROUND’
AND ASH.SESSION_STATE = ‘WAITING’
AND ASH.SAMPLE_TIME > SYSDATE - 1
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 GVSQLAREA 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 GVACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> ‘BACKGROUND’
AND ASH.SESSION_STATE = ‘WAITING’
AND ASH.SAMPLE_TIME > SYSDATE - 1
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
GROUP BY ASH.SESSION_ID, ASH.USER_ID, ASH.SESSION_SERIAL#, ASH.PROGRAM
ORDER BY SUM(DECODE(ASH.SESSION_STATE, ‘ON CPU’, 1, 1));

1、在以上图中使用系统进程PID查询对应的物理地址

SELECT v.addr,v.* FROM v$process v
WHERE v.SPID = ‘’5256;

2、通过该物理地址查找对应的SQL_ID

SELECT t.SQL_ID,t.* FROM v$session t
WHERE t.paddr= ‘000000025C5EB9F8’;

3、通过SQL_ID来查找对应的SQL语句

SELECT sql_text FROM v$sql
WHERE sql_id = ‘dqu970xzs3gpv’;

dbd_vehicle_gpsinfo

以上3句合并为一句：

SELECT s.sql_text
FROM v$sql s, v$session t,
v$process v
WHERE s.sql_id = t.SQL_ID
AND t.PADDR = v.ADDR
AND v.SPID = ‘15266’;

通过此sql语句具体分析问题