Oracle性能相关的常用脚本介绍
程序员文章站
2022-05-23 09:48:03
在缺乏的可视化工具来监控数据库性能的情形下,常用的脚本就派上用场了,下面提供几个关于Oracle性能相关的脚本供大家参考。以下脚本均在Oracle 10g测试通过,Oracle 1...
在缺乏的可视化工具来监控数据库性能的情形下,常用的脚本就派上用场了,下面提供几个关于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
3、寻找最近30分钟导致资源过高开销的事件
--filename:top_event_in_30_min.sql --Last 30 minutes result those resources that are in high demand on your system. SET LINESIZE 180 COL event FORMAT a60 COL total_wait_time FORMAT 999999999999999999 SELECT active_session_history.event, SUM ( active_session_history.wait_time + active_session_history.time_waited) total_wait_time FROM v$active_session_history active_session_history WHERE active_session_history.sample_time BETWEEN SYSDATE - 60 / 2880 AND SYSDATE AND active_session_history.event IS NOT NULL GROUP BY active_session_history.event ORDER BY 2 DESC;