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
銆€
銆€
銆愬厤璐e0鏄庡拰鐗堟潈璇存槑銆�
鏈珯閮ㄥ垎淇℃伅鏉ユ簮浜庝簰鑱旂綉锛�瀵硅浆杞界殑淇℃伅鎴戜滑鍔涙眰鏍囨槑淇℃伅鐨勫嚭澶勶紝鎴戜滑灏婇噸浣滆€呯殑鐗堟潈钁椾綔鏉冿紝瀵规垜浠浆杞界殑淇℃伅濡傛湁寮傝鎴栨湁渚垫潈鎴栬繚娉曚俊鎭鍙婃椂鑱旂郴鎴戜滑锛屾湰绔欎細绔嬪嵆鍒犻櫎锛�瀵瑰師鍒涘唴瀹规垜浠効鎰忔敮浠橀€傚綋鐨勭ǹ閰紝鑱旂郴鏂瑰紡锛氶偖浠�:
webmaster@jscj.com銆€鐢佃瘽锛�4008816886