oracle的ash时间点怎么输入,oracle ash 分析最近时间段的top sql event

@D:\app\UcJmh\product\11.2.0\dbhome_2\RDBMS\ADMIN\ashrpt.sql

–发现那些SQL运行了大量的PARSE

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS

FROM V$SQLAREA

ORDER BY PARSE_CALLS DESC;

–SYS的总的PARSE情况

SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE ‘parse count%’;

–CPU空间及繁忙情况

SELECT * FROM V$OSSTAT;

–查看每个Session的CPU利用情况：

SELECT SS.SID, SE.COMMAND, SS.VALUE CPU, SE.USERNAME, SE.PROGRAM

FROM V$SESSTAT SS, V$SESSION SE

WHERE SS.STATISTIC# IN

(SELECT STATISTIC#

FROM V$STATNAME

WHERE NAME = ‘CPU used by this session’)

AND SE.SID = SS.SID

AND SS.SID > 6

ORDER BY CPU DESC;

–比较一下哪个session的CPU使用时间最多，然后查看该Session的具体情况：

SELECT S.SID, S.EVENT, S.MACHINE, S.OSUSER, S.WAIT_TIME, W.SEQ#, Q.SQL_TEXT

FROM V$SESSION_WAIT W, V$SESSION S, V$PROCESS P, V$SQLAREA Q

WHERE S.PADDR = P.ADDR

AND S.SID = &P

AND S.SQL_ADDRESS = Q.ADDRESS;

—占用CPU最高的10个Session及其SQL语句

SELECT S.SID,

W.WAIT_TIME,

W.SECONDS_IN_WAIT,

W.STATE,

W.WAIT_TIME_MICRO,

W.TIME_REMAINING_MICRO,

W.TIME_SINCE_LAST_WAIT_MICRO,

P.USERNAME,

STATUS,

SERVER,

SCHEMANAME,

OSUSER,

MACHINE,

P.TERMINAL,

P.PROGRAM,

LOGON_TIME,

W.EVENT,

W.WAIT_CLASS,

TRACEFILE,

SQL_TEXT,

LAST_ACTIVE_TIME

FROM V$SESSION_WAIT W, V$SESSION S, V$PROCESS P, V$SQLAREA Q

WHERE S.PADDR = P.ADDR

AND S.SID IN (SELECT SID

FROM (SELECT SS.SID

FROM V$SESSTAT SS, V$SESSION SE

WHERE SS.STATISTIC# IN

(SELECT STATISTIC#

FROM V$STATNAME

WHERE NAME = ‘CPU used by this session’)

AND SE.SID = SS.SID

AND SS.SID > 6

ORDER BY SS.VALUE DESC)

WHERE ROWNUM < 11)

AND S.SQL_ADDRESS = Q.ADDRESS;