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
您现在的位置：
