在缺乏的可视化工具来监控数据库性能的情形下，常用的脚本就派上用场了，下面提供几个关于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
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