Oracle 效能相關常用指令碼(SQL)

在缺乏的視覺化檢視來監控資料庫效能的情形下，常用的指令碼就派上用場了，下面提供幾個關於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

1 2 3 4 下一頁