最近有同事遇到某客戶數據庫產生大量阻塞,等待事件為:enq HW - contention,最開始采用不斷殺會話的方式,效果不好,問題一直高頻反復。進一步確認SQL是大量的insert,且插入的表中含有LOB字段,根據經驗最終采用設置44951 event緩解了該問題。
具體關於Oracle的44951事件,可參考Maclean的文章:
這篇文章中采用的設置方法是:
alter system set events '44951 trace name context forever, level 1024';
需要注意的是,這種設置方法在重啟數據庫后就會失效,若需要重啟后永久生效,需設置event:
alter system set event='44951 trace name context forever, level 1024' scope=spfile;
但說到這里就需要深入了解下event和events的區別,這里大概測試總結有如下區別:
1.events立即生效,重啟數據庫后消失
2.events無法直接通過show parameter event查看
3.event后面必須有等號,重啟數據庫后生效,可通過show parameter event查看
4.event需要注意不要覆蓋掉之前的event設置,如果這個知識點不理解很容易產生誤操作,需要特別注意
補充說明:
比如在11g的最佳實踐中,通常已經設置10949和28401event:
alter system set event='28401 trace name context forever,level 1','10949 trace name context forever,level 1' sid='*' scope=spfile;
所以如果有需求額外設置44951 event,就需要將之前的也寫上(否則就會覆蓋):
alter system set event='28401 trace name context forever,level 1','10949 trace name context forever,level 1','44951 trace name context forever,level 1024' sid='*' scope=spfile;
events設置的值如何查看?使用下面的腳本(注意循環的范圍要包含要查詢的events):
set serveroutput on
declare
event_level number;
begin
for i in 10000..50000 loop
sys.dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event '||to_char(i)||' set at level '||
to_char(event_level));
end if;
end loop;
end;
/
查詢結果類似如下:
Event 10949 set at level 1
Event 28401 set at level 1
Event 44951 set at level 1024
附:Maclean使用的測試用例:
create user maclean identified by maclean;
grant resource, connect, dba to maclean;
conn maclean/maclean
CREATE TABLE "MACLEAN_LOB" ( "T1" VARCHAR2(200) NOT NULL , "T2" CLOB, "T3" CLOB) tablespace users
LOB ("T2")
STORE AS ( TABLESPACE "USERS" CHUNK 16K PCTVERSION 50 CACHE )
LOB ("T3")
STORE AS ( TABLESPACE "USERS" CHUNK 16K PCTVERSION 50 CACHE );
select segment_space_management from dba_tablespaces where tablespace_name='USERS';
exec dbms_workload_repository.create_snapshot;
--開3個進程並發插入LOB表
begin
for i in 1..10000 loop
insert into maclean.maclean_lob values ('ABC',rpad('Z',32000,'L'),rpad('Z',32000,'L'));
end loop;
commit;
end;
/
exec dbms_workload_repository.create_snapshot;
select bytes/1024,segment_name from dba_segments where segment_name in (select segment_name from dba_lobs where table_name='MACLEAN_LOB' and owner='MACLEAN');
truncate table maclean.maclean_lob;
select bytes/1024,segment_name from dba_segments where segment_name in (select segment_name from dba_lobs where table_name='MACLEAN_LOB' and owner='MACLEAN');
alter system flush buffer_cache;
alter system flush shared_pool;
alter system set events '44951 trace name context forever, level 1024';
exec dbms_workload_repository.create_snapshot;
--開3個進程並發插入LOB表
begin
for i in 1..10000 loop
insert into maclean.maclean_lob values ('ABC',rpad('Z',32000,'L'),rpad('Z',32000,'L'));
end loop;
commit;
end;
/
exec dbms_workload_repository.create_snapshot;
select bytes/1024,segment_name from dba_segments where segment_name in (select segment_name from dba_lobs where table_name='MACLEAN_LOB' and owner='MACLEAN');
以上可以看到雖然設置了44951 level 1024,但並不會因為單次bump hwm的chunks數增加而導致大量空間的浪費。
對比AWR可以發現設置44961 level 1024后 enq HW - contention消耗的DB TIME明顯減少:
此外在10.2.0.3之前還有一種方案即設置LOB的PCTVERSION 為0/100,但是該方案會導致LOB占用的SPACE大幅上升,所以不推薦,你有大量的理由至少升級DB到10.2.0.5.9。
通過這個測試用例實驗效果,確實設置前后的效果明顯:
注意:這里主要看enq HW - contention的優化效果,其他event也有調整優化空間,但不在本文討論范圍。