本文共 6746 字，大约阅读时间需要 22 分钟。
SET PAGESIZE 1000col sid for a6;col SPID for a10col SERIAL# for a8;col SCHEMANAME for a15;col OSUSER for a15;col username for a15;col MACHINE for a18;col TERMINAL for a15;col LOGON_TIME for a21col "已执行时间_秒" for a12col EVENT for a30;col SQL_TEXT for a80;col FIRST_LOAD_TIME for a20;col LAST_LOAD_TIME for a20;col LAST_ACTIVE_TIME for a20;col SQL_ID FOR A15;PROMPT prompt ==> 当前 TOP CPU SELECT * FROM (SELECT * FROM (SELECT ROUND(CPU_TIME/1000000/decode(EXECUTIONS+USERS_EXECUTING,0,1,EXECUTIONS+USERS_EXECUTING),3) as AVG_CPU_TIME_S, B.EXECUTIONS, USERS_EXECUTING, A.SID, A.SERIAL#, A.STATUS, A.MACHINE, A.OSUSER, A.USERNAME, TO_CHAR(A.LOGON_TIME,'yyyy-mm-dd hh24:mi:ss') AS LOGON_DATE, TRUNC((SYSDATE - A.LOGON_TIME) * 24 * 60*60) AS LOGON_TIME_S, A.LAST_CALL_ET AS LAST_CALL_ET_S, A.EVENT, A.SQL_ID, SUBSTR(B.SQL_TEXT,0,100) AS SQL_TEXT FROM V$SESSION A, V$SQL B WHERE USERS_EXECUTING >= 1 AND USERNAME NOT IN ('SYS') AND A.SQL_ADDRESS = B.ADDRESS AND A.USERNAME IS NOT NULL AND A.SQL_ID=B.SQL_ID AND A.SQL_CHILD_NUMBER=B.CHILD_NUMBER ) WHERE STATUS='ACTIVE' ORDER BY AVG_CPU_TIME_S DESC ) WHERE ROWNUM<=10; PROMPT prompt ==> 当前 TOP IOSELECT * FROM (SELECT * FROM (SELECT ROUND ((B.PHYSICAL_READ_BYTES + B.PHYSICAL_WRITE_BYTES) / DECODE (B.EXECUTIONS+USERS_EXECUTING, 0, 1, B.EXECUTIONS+USERS_EXECUTING)/ 1024/ 1024,2) AS AVG_IO_SIZE_MB, B.EXECUTIONS, USERS_EXECUTING, A.SID, A.SERIAL#, A.STATUS, A.MACHINE, A.OSUSER, A.USERNAME, TO_CHAR(A.LOGON_TIME,'yyyy-mm-dd hh24:mi:ss') AS LOGON_DATE, TRUNC((SYSDATE - A.LOGON_TIME) * 24 * 60*60) AS LOGON_TIME_S, A.LAST_CALL_ET AS LAST_CALL_ET_S, A.EVENT, A.SQL_ID, SUBSTR(B.SQL_TEXT,0,100) AS SQL_TEXT FROM V$SESSION A, V$SQL B WHERE USERS_EXECUTING >= 1 AND USERNAME NOT IN ('SYS') AND A.SQL_ADDRESS = B.ADDRESS AND A.USERNAME IS NOT NULL AND A.SQL_ID=B.SQL_ID AND A.SQL_CHILD_NUMBER=B.CHILD_NUMBER ) WHERE STATUS='ACTIVE' ORDER BY AVG_IO_SIZE_MB DESC ) WHERE ROWNUM<=10; PROMPT prompt ==> 当前 TOP BUFFER_GETSSELECT * FROM (SELECT * FROM (SELECT ROUND(B.BUFFER_GETS/(DECODE (B.EXECUTIONS+USERS_EXECUTING, 0, 1, B.EXECUTIONS+USERS_EXECUTING)),0) AS AVG_BUFFER_GETS, B.EXECUTIONS, USERS_EXECUTING, A.SID, A.SERIAL#, A.STATUS, A.MACHINE, A.OSUSER, A.USERNAME, TO_CHAR(A.LOGON_TIME,'yyyy-mm-dd hh24:mi:ss') AS LOGON_DATE, TRUNC((SYSDATE - A.LOGON_TIME) * 24 * 60*60) AS LOGON_TIME_S, A.LAST_CALL_ET AS LAST_CALL_ET_S, A.EVENT, A.SQL_ID, SUBSTR(B.SQL_TEXT,0,100) AS SQL_TEXT FROM V$SESSION A, V$SQL B WHERE USERS_EXECUTING >= 1 AND USERNAME NOT IN ('SYS') AND A.SQL_ADDRESS = B.ADDRESS AND A.USERNAME IS NOT NULL AND A.SQL_ID=B.SQL_ID AND A.SQL_CHILD_NUMBER=B.CHILD_NUMBER ) WHERE STATUS='ACTIVE' ORDER BY AVG_BUFFER_GETS DESC ) WHERE ROWNUM<=10; PROMPT prompt ==> FULL TABLE SCANSELECT * FROM (SELECT * FROM (SELECT ROUND (B.PHYSICAL_READ_BYTES / DECODE (B.EXECUTIONS+USERS_EXECUTING, 0, 1, B.EXECUTIONS+USERS_EXECUTING)/ 1024/ 1024,2) AS AVG_PHYSICAL_READ_BYTES_MB, B.EXECUTIONS, USERS_EXECUTING, A.SID, A.SERIAL#, A.STATUS, A.MACHINE, A.OSUSER, A.USERNAME, TO_CHAR(A.LOGON_TIME,'yyyy-mm-dd hh24:mi:ss') AS LOGON_DATE, TRUNC((SYSDATE - A.LOGON_TIME) * 24 * 60*60) AS LOGON_TIME_S, A.LAST_CALL_ET AS LAST_CALL_ET_S, A.EVENT, A.SQL_ID, SUBSTR(B.SQL_TEXT,0,100) AS SQL_TEXT from v$session a, v$sql b where USERS_EXECUTING >= 1 and username not in ('SYS') and a.last_call_et / 60 >= 0 and a.sql_address = b.address and a.username is not null AND A.SQL_ID=B.SQL_ID and A.SQL_CHILD_NUMBER=B.CHILD_NUMBER AND (A.SQL_ID,PLAN_HASH_VALUE)IN (SELECT DISTINCT SQL_ID,PLAN_HASH_VALUE FROM V$SQL_PLAN WHERE OPERATION='TABLE ACCESS' AND OPTIONS ='FULL') ) WHERE STATUS='ACTIVE' ORDER BY AVG_PHYSICAL_READ_BYTES_MB DESC ) WHERE ROWNUM<=10; PROMPT ==>>>找出一小时内消耗IO的TOP SQL SELECT ash.sql_id,count(*) FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EVTWHERE ash.sample_time > sysdate -1/24 AND ash.session_state = 'WAITING' AND ash.event_id = evt.event_id AND evt.wait_class = 'USER I/O'GROUP BY ash.sql_id ORDER BY count(*) desc;PROMPT ==>>>找出一小时个内消耗CPU的TOP session select * from (SELECT session_id,count(*) FROM V$ACTIVE_SESSION_HISTORYWHERE session_state = 'ON CPU' AND sample_time > sysdate -1/24GROUP BY session_idORDER BY count(*) desc) where rownum <21;prompt ==>最近7天top cpu select * from (select s.SQL_ID, sum(s.CPU_TIME_DELTA), sum(s.DISK_READS_DELTA), count(*) from DBA_HIST_SQLSTAT s, DBA_HIST_SNAPSHOT p where 1 = 1 and s.SNAP_ID = p.SNAP_ID and EXTRACT(HOUR FROM p.END_INTERVAL_TIME) between 9 and 17 and p.END_INTERVAL_TIME between SYSDATE - 7 and SYSDATE group by s.SQL_ID order by sum(s.CPU_TIME_DELTA) desc) where rownum < 11 prompt ===>awr 中的top sqlselect * from (select sql_id, "CPU + CPU Wait", "User I/O", "Application", "Network", "Concurrency", "Configuration", "Other", "System I/O", "Commit", "Queueing", "Administrative", "Scheduler", ("CPU + CPU Wait" + "User I/O" + "Application" + "Network" + "Concurrency" + "Configuration" + "Other" + "System I/O" + "Commit" + "Queueing" + "Administrative" + "Scheduler") TOTAL from (select ash.sql_id, sum(decode(ash.session_state, 'ON CPU', 1, 0)) "CPU + CPU Wait", sum(decode(ash.WAIT_CLASS, 'User I/O', 1, 0)) "User I/O", sum(decode(ash.WAIT_CLASS, 'Application', 1, 0)) "Application", sum(decode(ash.WAIT_CLASS, 'Network', 1, 0)) "Network", sum(decode(ash.WAIT_CLASS, 'Concurrency', 1, 0)) "Concurrency", sum(decode(ash.WAIT_CLASS, 'Configuration', 1, 0)) "Configuration", sum(decode(ash.WAIT_CLASS, 'Other', 1, 0)) "Other", sum(decode(ash.WAIT_CLASS, 'System I/O', 1, 0)) "System I/O", sum(decode(ash.WAIT_CLASS, 'Commit', 1, 0)) "Commit", sum(decode(ash.WAIT_CLASS, 'Queueing', 1, 0)) "Queueing", sum(decode(ash.WAIT_CLASS, 'Administrative', 1, 0)) "Administrative", sum(decode(ash.WAIT_CLASS, 'Scheduler', 1, 0)) "Scheduler" from V$ACTIVE_SESSION_HISTORY ash where sample_time > sysdate - 30 / 24 / 60 group by ash.sql_id) where sql_id is not null order by total desc ) where rownum < 10; 转载地址：http://qavta.baihongyu.com/