Oracle自動采集AWR Gets TOP SQL腳本

原創 codePanda 云和恩墨技術服務團隊 2025-08-11

1203

一、背景

最近在做一個Oracle數據庫優化專題，這是一個即將遷移到OceanBase的系統，其他營賬核心庫都遷移完成，該系統遷移評估中CPU使用率、系統負載有點高，需要將Oracle一個系統的負載降低到30-40左右。

其中有三個方案：

1）將3-3-3的OB集群擴展成4-4-4；

2）分庫，將一部分業務遷移出去；

3）優化TOP SQL降低Oracle負載；

本問主要講第三點：優化TOP SQL降低Oracle負載中如何自動化、定時地采集高邏輯讀的TOP SQL。

按top排序可以是最高耗時，最高cpu，邏輯讀、物理讀、執行次數，作為一個OLTP的數據庫常為IO密集型，資源消耗往往消耗在IO上頻繁的單塊、多塊讀寫，索引訪問上，所以邏輯讀次數較高的SQL很可能是性能瓶頸的根源，尤其是在負載較高的系統環境下，定位并優化這些SQL可以有效降低系統資源消耗，提升整體性能。

二、需求分析

自動定時采集最近15天內的高邏輯讀SQL（Buffer Gets TOP SQL）（根據AWR保留周期）；
按數據庫每天的事務數、會話連接數、數據庫負載、CPU使用率等指標選取系統最忙的時間段采集，比如白天（08:00-18:00）與夜間（20:00-06:00）時間段，分別采集不同時間段數據，確保覆蓋全天負載；
支持多實例環境，自動遍歷所有實例進行采集；
過濾低影響SQL，聚焦占比超過1%的高邏輯讀SQL；
避免重復采集，保證數據唯一性和完整性；
結果存儲至自定義歷史表，方便后續分析和趨勢對比。

三、核心實現腳本設計

腳本通過PL/SQL塊實現，核心流程如下：

時間段計算與FLAG標記
根據當前日期，循環最近15天。
對于每一天，判斷應采集白天段（08:00-18:00，FLAG=1）還是夜間段（20:00-06:00，FLAG=0）。
數據存在性檢查
先檢測該時間段數據是否已經存在，避免重復插入。
多實例循環采集
遍歷gv$instance，針對每個實例獨立采集數據。
高效聚合計算
利用AWR視圖如dba_hist_snapshot、dba_hist_sqlstat和dba_hist_sqltext，計算邏輯讀總量及各SQL的邏輯讀占比。
篩選與排序
初始優化只插入邏輯讀占比超過1%的SQL結果排序存入歷史表。

關鍵代碼片段示例

 ------------- 建表
CREATE TABLE DBMT.GETS_STAT_HIST (
    INSTANCE_NUMBER   NUMBER,
    INSTANCE_NAME     VARCHAR2(100),
    HOST_NAME         VARCHAR2(100),
    snap_time	      	DATE,  -- 對應 t.time
    BUFFER_GETS       NUMBER,
    EXECUTIONS        NUMBER,
    GETS_PER_EXEC     NUMBER,
    PCT_TOTAL         NUMBER(10,2),
    CPU_TIME_S        NUMBER,
    ELAPSED_TIME_S    NUMBER,
    SQL_ID            VARCHAR2(13),
    SQL_MODULE        VARCHAR2(1000),
    SQL_TEXT          CLOB,
    FLAG              NUMBER          -- 日/夜標識等
);
 
 
-- 解決重復插入問題 
ALTER TABLE DBMT.GETS_STAT_HIST ADD CONSTRAINT uq_gets_stat UNIQUE (INSTANCE_NUMBER, SNAP_TIME, SQL_ID, FLAG);
 
set serveroutput on

DECLARE
    v_flag          NUMBER;
    v_begin_time    DATE;
    v_end_time      DATE;
    v_now           DATE := SYSDATE;
    v_day_offset    NUMBER;
    v_exists        NUMBER;
    v_dbid          NUMBER;
    v_target_date   DATE;  -- 新增目標日期變量
BEGIN
    SELECT dbid INTO v_dbid FROM v$database;

    FOR v_day_offset IN 1 .. 15 LOOP
        -- 計算目標日期（過去第N天的00:00）
        v_target_date := TRUNC(v_now) - v_day_offset;
        
        -- 根據目標日期的時段設置時間段（非當前時間）
        IF TO_CHAR(v_target_date + 12/24, 'HH24') BETWEEN '06' AND '18' THEN  -- 目標日中午12點代表白天
            v_flag := 1;  -- 白天段
            v_begin_time := v_target_date + 8/24;   -- 目標日08:00
            v_end_time   := v_target_date + 18/24;   -- 目標日18:00
        ELSE
            v_flag := 0;  -- 夜間段
            v_begin_time := v_target_date - 1 + 20/24;  -- 目標日前一天20:00
            v_end_time   := v_target_date + 6/24;        -- 目標日06:00
        END IF;

        -- 高效檢查記錄是否存在
        BEGIN
            SELECT 1 INTO v_exists
            FROM DBMT.GETS_STAT_HIST
            WHERE FLAG = v_flag
              AND snap_time = TRUNC(v_begin_time)
              AND ROWNUM = 1;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN v_exists := 0;
        END;

        IF v_exists > 0 THEN
            DBMS_OUTPUT.PUT_LINE('已存在記錄: ' || TO_CHAR(v_begin_time,'YYYY-MM-DD') || ' FLAG=' || v_flag);
            CONTINUE;
        END IF;

        DBMS_OUTPUT.PUT_LINE('收集時間段: ' ||
            TO_CHAR(v_begin_time,'YYYY-MM-DD HH24:MI') || ' - ' ||
            TO_CHAR(v_end_time,'YYYY-MM-DD HH24:MI') || ', FLAG=' || v_flag);

        -- 遍歷每個實例
        FOR r_inst IN (SELECT instance_number, instance_name, host_name FROM gv$instance) LOOP
           -- 修復：刪除重復的 INSERT 語句
           INSERT INTO DBMT.GETS_STAT_HIST (
               INSTANCE_NUMBER, INSTANCE_NAME, HOST_NAME,
               snap_time, BUFFER_GETS, EXECUTIONS, GETS_PER_EXEC, PCT_TOTAL,
               CPU_TIME_S, ELAPSED_TIME_S, SQL_ID, SQL_MODULE, SQL_TEXT, FLAG
           )
           WITH t AS (
               SELECT instance_number,
                      MIN(snap_id) beg_snap,
                      MAX(snap_id) end_snap,
                      TRUNC(MIN(begin_interval_time)) AS snap_time
                 FROM dba_hist_snapshot
                WHERE end_interval_time >= v_begin_time
                  AND begin_interval_time <= v_end_time
                  AND instance_number = r_inst.instance_number
                GROUP BY instance_number
           ),
           total_gets AS (  -- 新增：預先計算總邏輯讀避免重復查詢
               SELECT t.instance_number,
                      SUM(e.value - b.value) AS total_logical_reads
                 FROM dba_hist_sysstat b,
                      dba_hist_sysstat e,
                      t
                WHERE b.snap_id = t.beg_snap
                  AND e.snap_id = t.end_snap
                  AND b.dbid = v_dbid
                  AND e.dbid = v_dbid
                  AND b.instance_number = t.instance_number
                  AND e.instance_number = t.instance_number
                  AND b.stat_name = 'session logical reads'
                  AND e.stat_name = 'session logical reads'
                GROUP BY t.instance_number
           ),
           gets_stat AS (
               SELECT sqt.instance_number,
                      sqt.buffer_gets,
                      sqt.executions,
                      ROUND(DECODE(sqt.executions, 0, NULL, sqt.buffer_gets / sqt.executions), 2) AS gets_per_exec,
                      -- 處理除零錯誤：總邏輯讀為0時返回NULL
                      ROUND(DECODE(tg.total_logical_reads, 0, NULL, 
                            (100 * sqt.buffer_gets) / tg.total_logical_reads), 2) AS pct_total,
                      ROUND(NVL(sqt.cpu_time / 1000000, 0), 2) AS cpu_time_s,
                      ROUND(NVL(sqt.elapsed_time / 1000000, 0), 2) AS elapsed_time_s, 
                      sqt.sql_id,
                      TO_CLOB('Module: ' || NVL(sqt.module, 'N/A')) AS sql_module,
                      NVL(st.sql_text, TO_CLOB('** SQL Text Not Available **')) AS sql_text,
                      t.snap_time,
                      -- 新增排序字段
                      ROW_NUMBER() OVER (ORDER BY (100 * sqt.buffer_gets) / tg.total_logical_reads DESC NULLS LAST) AS rn
                 FROM (
                       SELECT sql_id,
                              module,
                              instance_number,
                              SUM(buffer_gets_delta) AS buffer_gets,
                              SUM(executions_delta) AS executions,
                              SUM(cpu_time_delta) AS cpu_time,
                              SUM(elapsed_time_delta) AS elapsed_time
                         FROM dba_hist_sqlstat
                        WHERE dbid = v_dbid
                          AND instance_number = r_inst.instance_number
                          AND snap_id BETWEEN (SELECT beg_snap FROM t) + 1 AND (SELECT end_snap FROM t)
                        GROUP BY sql_id, module, instance_number
                      ) sqt
                 JOIN t ON t.instance_number = sqt.instance_number
                 LEFT JOIN dba_hist_sqltext st 
                   ON st.sql_id = sqt.sql_id AND st.dbid = v_dbid
                 JOIN total_gets tg 
                   ON tg.instance_number = t.instance_number  -- 關聯總邏輯讀
                WHERE tg.total_logical_reads > 0  -- 忽略無邏輯讀的時段
           )
           SELECT r_inst.instance_number,
                  r_inst.instance_name,
                  r_inst.host_name,
                  gs.snap_time,  -- 直接插入日期類型（非字符串）
                  gs.buffer_gets,
                  gs.executions,
                  gs.gets_per_exec,
                  gs.pct_total,
                  gs.cpu_time_s,
                  gs.elapsed_time_s,
                  gs.sql_id,
                  gs.sql_module,
                  gs.sql_text,
                  v_flag
             FROM gets_stat gs
            WHERE gs.rn <= 65   -- 取邏輯讀占比最高的65條
              AND gs.pct_total > 1;  -- 過濾低占比SQL
        END LOOP;
        COMMIT;
    END LOOP;
END;
/
 
收集時間段: 2025-08-10 08:00 - 2025-08-10 18:00, FLAG=1
收集時間段: 2025-08-09 08:00 - 2025-08-09 18:00, FLAG=1
收集時間段: 2025-08-08 08:00 - 2025-08-08 18:00, FLAG=1
收集時間段: 2025-08-07 08:00 - 2025-08-07 18:00, FLAG=1
收集時間段: 2025-08-06 08:00 - 2025-08-06 18:00, FLAG=1
收集時間段: 2025-08-05 08:00 - 2025-08-05 18:00, FLAG=1
收集時間段: 2025-08-04 08:00 - 2025-08-04 18:00, FLAG=1
收集時間段: 2025-08-03 08:00 - 2025-08-03 18:00, FLAG=1
收集時間段: 2025-08-02 08:00 - 2025-08-02 18:00, FLAG=1
收集時間段: 2025-08-01 08:00 - 2025-08-01 18:00, FLAG=1
收集時間段: 2025-07-31 08:00 - 2025-07-31 18:00, FLAG=1
收集時間段: 2025-07-30 08:00 - 2025-07-30 18:00, FLAG=1
收集時間段: 2025-07-29 08:00 - 2025-07-29 18:00, FLAG=1
收集時間段: 2025-07-28 08:00 - 2025-07-28 18:00, FLAG=1
收集時間段: 2025-07-27 08:00 - 2025-07-27 18:00, FLAG=1

PL/SQL procedure successfully completed.

后續有權限也可接到定時任務里

-- 06:10 執行：收集前夜間段（FLAG=0）
-- 18:10 執行：收集白天段（FLAG=1）
10 6,18 * * * /path/to/collect_gets_stat.sh >> /path/to/log/gets_stat.log 2>&1

四、分析SQL

該腳本取得SQL的邏輯讀和AWR報告一致，取20250810早8：00-20250810 18：00的AWR報告，對比查詢結果和AWR結果一致。

附上SQL分析

-- 根據 sql_id 估算一天產生的邏輯讀
SELECT AVG(BUFFER_GETS) * 24 / 10 ,ROUND((AVG(BUFFER_GETS) * 24 / 10 * 8192)/ POWER(1024, 4), 2 ) AS LOGICAL_READ_TB_24H
FROM DBMT.GETS_STAT_HIST WHERE
  SNAP_TIME >= TO_DATE('20250724', 'yyyymmdd')
  AND INSTANCE_NUMBER = 1
  AND sql_id = 'cyypmad5n2b1m';

 
 -- 查詢實例1 2025-07-28 高邏輯讀TOP SQL
  select * from DBMT.GETS_STAT_HIST where INSTANCE_NUMBER=1 and SNAP_TIME=trunc(to_date('20250728','yyyymmdd'));

-- 查詢實例1 2025-07-28 白天/夜晚 8：00-18：00/20:00-6:00 的高邏輯讀TOP SQL
 select * from DBMT.GETS_STAT_HIST where INSTANCE_NUMBER=1 and FLAG=1 and SNAP_TIME=trunc(to_date('20250728','yyyymmdd'));

 select sum(BUFFER_GETS),sum(EXECUTIONS),sum(CPU_TIME_S),sum(ELAPSED_TIME_S),SQL_ID,
    COUNT(SQL_ID)
 from DBMT.GETS_STAT_HIST where SNAP_TIME>=(to_date('20250723','yyyymmdd')) and INSTANCE_NUMBER=1 and FLAG=0
 group by SQL_ID
 order by sum(BUFFER_GETS) desc;
 
 
-- 查詢 20250723至今 實例一白天的TOP SQL
 select INSTANCE_NUMBER,SNAP_TIME,BUFFER_GETS,EXECUTIONS,GETS_PER_EXEC,CPU_TIME_S,ELAPSED_TIME_S,SQL_ID,SQL_TEXT,
    COUNT(SQL_ID) OVER (PARTITION BY SQL_ID) AS SQL_COUNT,
    SUM(BUFFER_GETS) OVER (PARTITION BY SQL_ID ) AS BUFFER_GETS_TOTAL
 from DBMT.GETS_STAT_HIST where SNAP_TIME>=(to_date('20250723','yyyymmdd')) and INSTANCE_NUMBER=1 and FLAG=1
 order by SQL_COUNT desc,BUFFER_GETS_TOTAL desc ;
  
-- 查詢 20250723至今 實例一白天的TOP SQL，每個sql按照BUFFER_GETS大小排序
   select INSTANCE_NUMBER,SNAP_TIME,BUFFER_GETS,EXECUTIONS,GETS_PER_EXEC,CPU_TIME_S,ELAPSED_TIME_S,SQL_ID,SQL_TEXT,
    COUNT(SQL_ID) OVER (PARTITION BY SQL_ID) AS SQL_COUNT,
    SUM(BUFFER_GETS) OVER (PARTITION BY SQL_ID) AS BUFFER_GETS_TOTAL,
	 row_number() over(partition by sql_id order by  BUFFER_GETS) rn
 from DBMT.GETS_STAT_HIST where SNAP_TIME>=(to_date('20250723','yyyymmdd'))  and INSTANCE_NUMBER=1 and FLAG=0
 order by SQL_COUNT desc,rn desc

在每次分析完sql可以查看sql是否還在TOP里，根據sql分析清單（excel等）插入分析進度

比如：新增status列和dbmt.sqltune_list優化列表

 -- status : 0：待優化 1：已建議 2：已分析、無優化空間  9：已優化

alter table DBMT.GETS_STAT_HIST add status  int default 0; 

COMMENT ON COLUMN DBMT.GETS_STAT_HIST.FLAG IS 'FLAG=1 ：早 08:00 - 晚 18:00    FLAG=0 ：晚 20:00 - 早 06:00';
COMMENT ON COLUMN DBMT.GETS_STAT_HIST.STATUS IS '0：待優化 1：已建議 2：已分析、無優化空間  9：已優化';

-- 新增優化列表
create table dbmt.sqltune_list(db_name varchar2(20),sql_id varchar2(100),status varchar2(20));
alter table DBMT.GETS_STAT_HIST add status  int default 0; 

insert into dbmt.sqltune_list values('orcl2','0sg6jv5u27cxb','已優化');
insert into dbmt.sqltune_list values('orcl2','7pku4wk5skhbu','已建議');
insert into dbmt.sqltune_list values('orcl1','3bmmahm7duj1m','已優化');
insert into dbmt.sqltune_list values('orcl1','1m0w8j99p8um0','已優化');
insert into dbmt.sqltune_list values('orcl1','07rsrdcyvxwkv','已優化');
insert into dbmt.sqltune_list values('orcl1','3txts3r0v2rrv','已分析、無優化空間');

優化時先挑出現頻率最高的TOP SQL

-- 查詢出現次數最多的TOP　SQL
 select INSTANCE_NUMBER,SNAP_TIME,BUFFER_GETS,EXECUTIONS,GETS_PER_EXEC,CPU_TIME_S,ELAPSED_TIME_S,a.SQL_ID,SQL_TEXT,FLAG,a.status,b.status,
    COUNT(a.SQL_ID) OVER (PARTITION BY a.SQL_ID) AS SQL_COUNT,
    SUM(BUFFER_GETS) OVER (PARTITION BY a.SQL_ID ) AS BUFFER_GETS_TOTAL
 from DBMT.GETS_STAT_HIST a left join dbmt.sqltune_list b on a.sql_id=b.sql_id where SNAP_TIME>=(to_date('20250728','yyyymmdd')) and INSTANCE_NUMBER=1 
 -- and status=0 
 and flag=1
 order by BUFFER_GETS_TOTAL  desc, SQL_COUNT desc ;

單次邏輯讀最高的SQL

select INSTANCE_NAME,a.sql_id,max(BUFFER_GETS) BUFFER_GETS,max(EXECUTIONS),max(GETS_PER_EXEC) GETS_PER_EXEC ,FLAG,b.status
from DBMT.GETS_STAT_HIST a left join dbmt.sqltune_list b on a.sql_id=b.sql_id where SNAP_TIME>=(to_date('20250723','yyyymmdd')) and INSTANCE_NUMBER=2
group by INSTANCE_NAME,a.sql_id,FLAG,b.status
 order by BUFFER_GETS desc;