???????????????????????????????????????￡?????????????ó??????????????????Oracle???????????????ο?????????????Oracle 10g?????????Oracle 11g????????????????

????1??????BUFFER_GETS??????SQL???

--filename: top_sql_by_buffer_gets.sql
--Identify heavy SQL (Get the SQL with heavy BUFFER_GETS)
SET LINESIZE 190
COL sql_text FORMAT a100 WRAP
SET PAGESIZE 100

SELECT *
  FROM (  SELECT sql_text??
                 sql_id??
                 executions??
                 disk_reads??
                 buffer_gets
            FROM v$sqlarea
           WHERE DECODE (executions?? 0?? buffer_gets?? buffer_gets / executions) >
                    (SELECT AVG (DECODE (executions?? 0?? buffer_gets?? buffer_gets / executions))
                            + STDDEV (DECODE (executions?? 0?? buffer_gets?? buffer_gets / executions))
                       FROM v$sqlarea)
                 AND parsing_user_id != 3D
        ORDER BY 4 DESC) x
 WHERE ROWNUM <= 10;

????2??????DISK_READS??????SQL???

--filename:top_sql_disk_reads.sql
--Identify heavy SQL (Get the SQL with heavy DISK_READS)
SET LINESIZE 190
COL sql_text FORMAT a100 WRAP
SET PAGESIZE 100

SELECT *
  FROM (  SELECT sql_text??
                 sql_id??
                 executions??
                 disk_reads??
                 buffer_gets
            FROM v$sqlarea
           WHERE DECODE (executions?? 0?? disk_reads?? disk_reads / executions) >
                    (SELECT AVG (DECODE (executions?? 0?? disk_reads?? disk_reads / executions))
                            + STDDEV (DECODE (executions?? 0?? disk_reads?? disk_reads / executions))
                       FROM v$sqlarea)
                 AND parsing_user_id != 3D
        ORDER BY 3 DESC) x
 WHERE ROWNUM <= 10

????3??????30??????????????????????

--filename:top_event_in_30_min.sql
--Last 30 minutes result those resources that are in high demand on your system.
SET LINESIZE 180
COL event FORMAT a60
COL total_wait_time FORMAT 999999999999999999

  SELECT active_session_history.event??
         SUM (
            active_session_history.wait_time
            + active_session_history.time_waited)
            total_wait_time
    FROM v$active_session_history active_session_history
   WHERE active_session_history.sample_time BETWEEN SYSDATE - 60 / 2880
                                                AND SYSDATE
         AND active_session_history.event IS NOT NULL
GROUP BY active_session_history.event
ORDER BY 2 DESC;