玖叶教程网

一、先看资源管理器是Oracle数据看占资源比较高还是java占的比较高

二、如果是数据库占资源比较高 通过以下语句查下看哪个sql语句占资源比较高，在优化具体的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