在缺乏的可视化工具来监控数据库性能的情形下,常用的脚本就派上用场了,下面提供几个关于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;
如对本文有疑问, 点击进行留言回复!!
Oracle查询用户,表结构,表索引,存储过程,触发器等信息整理
oracle的自连接、内连接、外连接、交叉连接、自然连接、等值连接、不等值连接
Oracle数据库使用replace函数批量删除数据中的双引号
网友评论