Oracle性能调优: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 |
时间:2013-06-21 责任编辑:admin
【免责声明和版权说明】
本站部分信息来源于互联网,对转载的信息我们力求标明信息的出处,我们尊重作者的版权著作权,对我们转载的信息如有异议或有侵权或违法信息请及时联系我们,本站会立即删除,对原创内容我们愿意支付适当的稿酬,联系方式:邮件:
webmaster@jscj.com 电话:4008816886