视窗
loading...
您现在的位置:首页 > IT认证 > oracle认证 >

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

銆€

銆€

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

相关文章

无相关信息
更新时间2022-03-13 11:18:59【至顶部↑】
联系我们 | 邮件: webmaster@jscj.com | 客服热线电话:4008816886(QQ同号) |  娣诲姞瀹㈡湇寰俊

付款方式留言簿投诉中心网站纠错二维码手机版

客服电话: