Oracle性能调优：Oracle性能相关常用脚本（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;

--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