修補DBA短板：監控SQL優化案例兩則

作者介紹

蔣健，雲趣網路科技聯合創始人，11g OCM，多年Oracle設計、管理及實施經驗，精通資料庫優化，Oracle CBO及並行原理，曾為多個行業的客戶的Oracle系統實施小型機到X86跨平台遷移和資料庫優化服務。雲趣鷹眼監控核心設計和開發者，資深Python Web開發者。

一、表空間信息查詢慢

前幾天跟銷售拜訪潛在客戶時，客戶提到他們近期很頭疼的問題：他們自己寫的一個查詢表空間的語句從上周開始跑不出來了，做了很多嘗試未果，比較苦惱，畢竟作為DBA不知道庫的空間使用情況，客戶心裡是有點發毛的。

聽完客戶描述，我也馬上回復說這個問題很常見，都在我們日常巡檢的範圍內，解決起來很簡單。一般查詢表空間的語句都是用到了DBA_FREE_SPACE這張視圖，查詢起來慢，常見原因一般如下：

數據字典信息/固化視圖統計信息過舊；

回收棧內對象過多。

處理方式：

exec dmbs_stats.gather_fixed_objects_stats;

exec dbms_stats.gather_dictionary_stats(degree =>8,cascade =>true);

purge recyclebin;

客戶聽完介紹的解決方案，表示認同並希望現場幫他們處理好問題，熱情地帶我去他的工位。雖然庫並非生產資料庫，但訪問這個庫依然需要通過堡壘機，而且命令需要手敲。

客戶的演示

客戶通過shell腳本SQLplus進入資料庫後運行語句類似以下SQL：

SELECT UPPER(F.TABLESPACE_NAME) AS "表空間名稱",

ROUND(D.AVAILB_BYTES ,2) AS "表空間大小(G)",

ROUND(D.MAX_BYTES,2) AS "最終表空間大小(G)",

ROUND((D.AVAILB_BYTES - F.USED_BYTES),2) AS "已使用空間(G)",

TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,

2), "999.99") AS "使用比",

ROUND(F.USED_BYTES, 6) AS "空閑空間(G)",

F.MAX_BYTES AS "最大塊(M)"

FROM (

SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,

ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT DD.TABLESPACE_NAME,

ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES,

ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6) MAX_BYTES

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 4 DESC

客戶運行了腳本，果然卡住了一樣，無返回結果。演示完，客戶讓出了座位，示意我可以上機操作了。因為不是生產庫，客戶也表示可以隨意操作，於是沒有去進一步確認信息，順利完成了上面列的操作。

有趣的地方來了

客戶開始驗證效果的時候，比較尷尬，依然卡在那兒。客戶地方沒有監控，也沒有我習慣的腳本，客戶打開plSQLdeveloper後，我看了一下在運行的SQL等待事件是單塊兒讀，我有點猶豫要不要手敲那些工具SQL的時候，有趣的地方也來了~ 客戶的DBA開始討論起了原因：

一個客戶DBA說，他覺得是IO太慢，因為庫不是放在存儲上的，而且raid可能用的是raid 5之類。

另一個客戶DBA說，可能是資料庫太大了，性能有影響。

為了避免問題走偏，我快速打了個快照，做了awr報告，確認了一下單塊讀約3ms，數據文件個數約800個。證偽了以上假設。

那到底為什麼SQL查詢還是不快呢？我有點猶豫，畢竟堡壘機命令都手敲不能直接跑自己的腳本包。客戶主要負責的DBA此時主動給台階讓我下，讓我找時間再看看，回去了再研究，畢竟他們也快下班了。

意外的效果

這個台階當然不能下…… 在10046跟moitor報告中，個人還是更傾向monitor報告，於是腳本加了monitor的hint，打算再跑一次。另外一個會話準備編寫腳本的時候，SQL瞬間出了結果。常用腳本這裡也貼一下：

set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000 feeDBAck off

spool &1..&2

--active/html/EM

select dbms_SQLtune.report_SQL_monitor(type=>"&2", SQL_id=>"&1", SQL_exec_id=>null, report_level=>"ALL") monitor_report from dual;

spool off

客戶DBA說這個hint有效果，但是我也不打算糊弄客戶，跟客戶解釋了這個hint跟跑起來快了沒什麼關係，並用 /*+ xxx */這個改動，再次運行，果然也很快。客戶運行原來的語句，依然跑不出來。這時候，基本已經知道原因了。

雖然講道理，收集了統計，SQL的執行計劃應該是會失效，並重新解析的，這裡顯然那個SQL的執行計劃應該還是原來的，沒有變動。考慮到不是生產庫，就直接flush了shared pool。再次運行那個問題SQL，順利秒出結果。

二、ASH信息採集慢

某客戶反饋監控上線後，在監控的數十套資料庫中有一套庫監控SQL的資料庫時間佔比較高，監控自身顯示是一條採集ASH數據的語句。經過了解，其他庫該語句執行均在1s以內，在其中一個資料庫上運行時間可達4分鐘，監控中我們馬上發現了該SQL的監視報告。

監視報告快速定位

打開報告，通過Wait Activity中可發現55%時間是read by other session等待事件，還有36%為gc相關等待事件，結合2GB的IO，可以基本可知問題原因為SQL讀取IO量過多，在RAC高並發環境下，性能問題被放大。

那麼，這2GB的IO來源於哪兒呢？執行計劃很長，截取部分如下：

第一部分IO佔54%來源WRH$_ACTIVE_SESSION_HISTORY：

第二部分IO佔46%來源WRH$_ACTIVE_SESSION_HISTORY：

通過報告可明顯觀察到整個SQL消耗的IO基本來源於WRH$_ACTIVE_SESSION_HISTORY這張表。監視報告中選中plan，再以Tabular方式查看，可查看訪問表的謂詞，正常情況下這個表是會分區的，目前看起來數據都集中在了一個分區。

運行以下代碼段確認信息：

代碼段如下：

set serveroutput on

declare

CURSOR cur_part IS

SELECT partition_name from DBA_tab_partitions

WHERE table_name = "WRH$_ACTIVE_SESSION_HISTORY";

query1 varchar2(200);

query2 varchar2(200);

TYPE partrec IS RECORD (snapid number, dbid number);

TYPE partlist IS TABLE OF partrec;

Outlist partlist;

begin

dbms_output.put_line("PARTITION NAME SNAP_ID DBID");

dbms_output.put_line("--------------------------- ------- ----------");

for part in cur_part loop

query1 := "select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ("||part.partition_name||") group by dbid";

execute immediate query1 bulk collect into OutList;

if OutList.count > 0 then

for i in OutList.first..OutList.last loop

dbms_output.put_line(part.partition_name||" Min "||OutList(i).snapid||" "||OutList(i).dbid);

end loop;

end if;

query2 := "select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ("||part.partition_name||") group by dbid";

execute immediate query2 bulk collect into OutList;

if OutList.count > 0 then

for i in OutList.first..OutList.last loop

dbms_output.put_line(part.partition_name||" Max "||OutList(i).snapid||" "||OutList(i).dbid);

dbms_output.put_line("---");

end loop;

end if;

end loop;

end;

/

（上下滑動查看完整代碼）

如下可發現表並自動未分區：

正常情況下分區是自動創建並清理的，如下：

PARTITION NAME SNAP_ID DBID

--------------------------- ------- ----------

---

---

---

---

---

---

以下為清除ASH數據的方式：

exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id =>2810,high_snap_id =>18000);

alter table WRH$_ACTIVE_SESSION_HISTORY shrink space;

DROP_SNAPSHOT_RANGE 處理方式上本質為delete相關數據，實際執行時間很長（這次夜間執行用了6小時），shrink操作也是執行了半個小時。可以考慮直接把這個大分區truncate掉（當然會丟部分性能數據）。

alter table sys.wrh$_active_session_history truncate partition WRH$_ACTIVE_xxxx_xxxx update global indexes;

上述操作完成後，SQL已經能秒出了，然而源頭問題分區表WRH$_ACTIVE_SESSION_HISTORY 是否能自動新建分區，並自動清理過期ASH數據通過以下命令（官方推薦處理方式，參考文檔 387914.1）並不一定達到想要的效果。

alter session set "_swrf_test_action" = 72;

本文處理時有點經驗主義，這步操作沒抱太大期望，結果順利達到預期效果。也有相當部分場景執行命令後還是不能自動分區，這時候，沒錯要打補丁了。資料庫版本從11.2.0.2到12.1.0.1，可在線打。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！