DIV CSS 佈局教程網

Oracle 查找高消耗 SQL 語句的方法
編輯:JavaScript綜合知識     

本文介紹如何撰寫查詢,找出 Oracle 資料庫中資源消耗最高的 SQL 語句,協助 DBA 進行效能調優。

前置準備

執行下列腳本前,需先用產生 AWR 報告的 SQL(一般保存在 $ORACLE_HOME 下的 rdbms/admin 中,名稱為 awrrpt.sql,執行時需輸入範圍天數)找出開始與結束的 snapshot 編號:begin_snap 與 end_snap。

查詢腳本

set line 1000
set linesize 200
set pagesize 2000
set long 999999
set echo on
set markup html on
select res.*
from (
  select to_char(d.end_interval_time,'yyyy-mm-dd'),
    a.PARSING_SCHEMA_NAME,
    c.MODULE,
    a.sql_id,
    a.execs as 執行次數,
    ROUND(a.cpu_times / a.execs, 2) as 單次執行時間,
    a.cpu_times as cpu消耗時間,
    ROUND(a.cpu_times / b.sum_time * 100, 2) as 消耗cpu百分比,
    a.buffer_gets as 邏輯讀,
    ROUND(a.buffer_gets / b.sum_buffer * 100, 2) as 邏輯讀百分比,
    a.disk_read as 物理讀,
    ROUND(a.disk_read / b.sum_disk * 100, 2) as 物理讀百分比,
    c.sql_fulltext
  from (
    select PARSING_SCHEMA_NAME, sql_id,
      sum(EXECUTIONS_DELTA) AS execs,
      round(sum(CPU_TIME_DELTA) / 1000000, 2) AS cpu_times,
      round(sum(ELAPSED_TIME_DELTA) / 1000000, 2) AS elapsed_time,
      sum(BUFFER_GETS_DELTA) AS buffer_gets,
      sum(DISK_READS_DELTA) AS disk_read
    from sys.WRH$_SQLSTAT wr, gv$instance i
    where SNAP_ID <= &end_snap
      and snap_id >= &begin_snap
      and wr.INSTANCE_NUMBER = i.INSTANCE_NUMBER
      and i.instance_number = &instance_number
    group by PARSING_SCHEMA_NAME, wr.INSTANCE_NUMBER, sql_id
  ) a,
  (
    SELECT round(SUM(CPU_TIME_DELTA) / 1000000, 2) sum_time,
      SUM(BUFFER_GETS_DELTA) sum_buffer,
      sum(DISK_READS_DELTA) sum_disk
    FROM sys.WRH$_SQLSTAT wr, gv$instance i
    where SNAP_ID <= &end_snap
      and snap_id >= &begin_snap
      and wr.INSTANCE_NUMBER = i.INSTANCE_NUMBER
      and i.instance_number = &instance_number
  ) b,
  v$sqlarea c,
  dba_hist_snapshot d
  where a.execs > 0
    and a.sql_id = c.sql_id
    and a.PARSING_SCHEMA_NAME <> 'SYS'
    and d.snap_id = &end_snap
  order by cpu消耗時間 desc
) res
where rownum < 41;
exit

輸出結果

將腳本輸出的內容貼到記事本並存檔,再把副檔名改為 .html,即可用瀏覽器開啟,輕鬆檢視各 SQL 的執行次數、CPU 消耗、邏輯讀與物理讀等指標,快速鎖定需要優化的高消耗語句。

XML學習教程| jQuery入門知識| AJAX入門| Dreamweaver教程| Fireworks入門知識| SEO技巧| SEO優化集錦|
Copyright © DIV+CSS佈局教程網 All Rights Reserved