Oracle性能优化：常用脚本分享

"Oracle性能相关常用脚本（SQL）" 在Oracle数据库的管理与优化过程中，没有合适的可视化工具时，可以借助一些SQL脚本来监控和分析数据库的性能。以下两个脚本是针对Oracle 10g设计的，适用于找出对系统性能影响较大的SQL语句，但在Oracle 11g中可能需要进行相应的调整。 1. 寻找多BUFFER_GETS开销的SQL语句（top_sql_by_buffer_gets.sql） 这个脚本的主要目的是找出那些执行过程中缓冲区获取（BUFFER_GETS）次数异常高的SQL语句，这些语句通常会对数据库性能造成较大影响。通过设置LINESIZE和PAGESIZE来优化输出格式，然后从`v$sqlarea`视图中选取执行次数（executions）、磁盘读取（disk_reads）和缓冲区获取次数，并计算每执行一次的平均缓冲区获取次数。接着，筛选出平均值加上标准差以上的SQL，排除用户ID为3的（通常代表系统进程），最后按每执行一次的缓冲区获取次数降序排列，输出前10个结果。 ```sql 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 != 3 ) ORDER BY 4 DESC WHERE ROWNUM <= 10; ``` 2. 寻找多DISK_READS开销的SQL语句（top_sql_disk_reads.sql） 这个脚本与上一个类似，但专注于找到执行过程中磁盘读取（DISK_READS）次数异常高的SQL语句。它也使用了相同的方法计算每执行一次的平均磁盘读取次数，并筛选出高于平均值加标准差的SQL语句，最后按每执行一次的磁盘读取次数降序排列，同样输出前10个结果。 ```sql 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 != 3 ) ORDER BY 4 DESC WHERE ROWNUM <= 10; ``` 这两个脚本对于日常性能监控和问题定位非常有用，它们可以帮助DBA快速识别出对数据库性能有显著影响的SQL语句，进而采取优化措施，比如改写SQL、创建索引或调整表的分区策略等，以提升系统的整体性能。