Oracle 效能相關常用指令碼____Oracle

在缺乏的視覺化檢視來監控資料庫效能的情形下，常用的指令碼就派上用場了，下面提供幾個關於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 190COL sql_text FORMAT a100 WRAPSET PAGESIZE 100SELECT *  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 190COL sql_text FORMAT a100 WRAPSET PAGESIZE 100SELECT *  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 180COL event FORMAT a60COL 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 NULLGROUP BY active_session_history.eventORDER BY 2 DESC;