- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/display/CORE/enq+HW+-+contention?
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
1. HW락
1. HWM을 여러 프로세스가 동시에 변경하는 것을 막기 위한 락이다
2. HW락을 획득하는 과정에서 경합이 발생하면 enq HW - contention 대기 이벤트 발생한다.
3. 대부분 대량 insert에 의해 발생한다.
4. 대량 update시 언두세그먼트의 급속한 확장이 필요하기 때문에 언두세그먼트에서 HW락 경합 현상 발생한다.
5. HW락의 ID1값은 테이블스페이스 번호, ID2값으 세그먼트 헤더블록의 DBA이다.
6. 세그먼트 공간관리기법을 FLM을 사용라는 경우에 FREELISTS 속성의 기본값이 1로 세팅되기 떼문에 HW락 경합이 발생할 가능성이 높다.
2. 참고 In-memory Undo (IMU) 기능
언두 데이터를 undo segment가 아닌 shared pool내의 KTI-UNDO 영역에 저장하는 기능으로, 오라클 10g 이상에서 추가되었다.
KTI-UNDO 영역에 저장된 언두 데이터는 주기적으로 언드세그먼트에 플러시된다.
이로 인해 언두 세그먼트 확장에 의한 HW락 경합이 줄어드는 효과가 있으며, 언두 블록을 읽고 쓰는 작업이 줄어들었다.
그러나, 아직 IMU에 대한 성능이슈가 알려주 있지 않다고 한다.
3. 테스트 : FLM환경에서 동시에 대량의insert에 의해 enq HW - contention 대기가 발생하는 경우
SYS@TEST3 >select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for Linux: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production SYS@TEST3 >show parameter db_block_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192 SYS@TEST3 > SYS@TEST3 >create tablespace hwm_test_tbs datafile '/data01/oradata/TEST3/hwm01.dbf' size 300M autoextend on 2 extent management local uniform size 1M segment space management manual; Tablespace created. LKWTEST@TEST3 >create table hwm_test ( 2 name1 char(2000) default ' ', 3 name2 char(2000) default ' ', 4 name3 char(2000) default ' ', 5 name4 char(1500) default ' ') tablespace hwm_test_tbs; Table created. LKWTEST@TEST3 >create or replace procedure massive_insert 2 is 3 begin 4 for idx in 1 .. 10000 loop 5 insert into hwm_test(name1) values(' '); 6 commit; 7 end loop; 8 end; 9 / Procedure created. 5개 세션에서 수행 LKWTEST@TEST3 >exec massive_insert; PL/SQL procedure successfully completed. LKWTEST@TEST3 >@lkw_sess_event.sql EVENT TOTAL_WAITS TIME_WAITED ---------------------------------------------------------------- ----------- ----------- db file sequential read 239490 62589 buffer busy waits 152243 7318 direct path read 126461 39 direct path write 126148 23 h5.enq: HW - contention 48434 7776 os thread startup 15128 37403 log buffer space 13742 5397 log file single write 11276 3784 log file sequential read 11276 510 |
4. HW락 경합을 줄이는 방법
1. 9i이상일 경우 ASSM을 사용한다. 8i이하라면 FREELISTS값을 적절하게 설정하고, 필요한 경우 _BUMP_HIGHWATER_MARK_COUNT 파라메터값을 충분하게 지정하여 HWM의 이동을 최소화한다.
2. 적절한 크기의 익스텐트를 사용한다.
3. 8i이상일 경우 LMT를 사용한다.
4-1. FREELISTS, _BUMP_HIGHWATER_MARK_COUNT 설정 변경
SYS@TEST3 >set linesize 120 SYS@TEST3 >col ksppinm format a30 SYS@TEST3 >col "Session Value" format a20 SYS@TEST3 >select a.ksppinm, b.ksppstvl as "Session Value" 2 from x$ksppi a, x$ksppsv b 3 where a.indx=b.indx 4 and a.ksppinm like '%highwater%'; KSPPINM Session Value ------------------------------ -------------------- _bump_highwater_mark_count 0 LKWTEST@TEST3 >drop table hwm_test; Table dropped. LKWTEST@TEST3 >create table hwm_test ( 2 name1 char(2000) default ' ', 3 name2 char(2000) default ' ', 4 name3 char(2000) default ' ', 5 name4 char(1500) default ' ') 6 storage ( freelists 6 ) 7 tablespace hwm_test_tbs; Table created. LKWTEST@TEST3 >create or replace procedure massive_insert 2 is 3 begin 4 for idx in 1 .. 10000 loop 5 insert into hwm_test(name1) values(' '); 6 commit; 7 end loop; 8 end; 9 / Procedure created. LKWTEST@TEST3 >alter session set "_bump_highwater_mark_count"=50; Session altered. LKWTEST@TEST3 >exec massive_insert; PL/SQL procedure successfully completed. LKWTEST@TEST3 >@lkw_sess_event.sql EVENT TOTAL_WAITS TIME_WAITED ---------------------------------------------------------------- ----------- ----------- db file sequential read 239473 62564 events in waitclass Other 197315 608241 direct path read 126367 39 direct path write 126054 23 buffer busy waits 13881 2222 log buffer space 13642 4133 log file sequential read 11268 510 log file single write 11268 3784 enq: HW - contention 9919 6659 db file scattered read 4553 7390 db file single write 918 37 free buffer waits 720 773 latch: cache buffers chains 605 12 latch: shared pool 407 129 |
4-2. ASSM
SYS@TEST3 >create tablespace hwm_test_tbs2 datafile '/data01/oradata/TEST3/hwm02.dbf' size 100M autoextend on extent management local uniform size 1M segment space management auto; Tablespace created. LKWTEST@TEST3 >create table hwm_test2 ( 2 name1 char(2000) default ' ', 3 name2 char(2000) default ' ', 4 name3 char(2000) default ' ', 5 name4 char(1500) default ' ') tablespace hwm_test_tbs2; Table created. LKWTEST@TEST3 >create or replace procedure massive_insert 2 is 3 begin 4 for idx in 1 .. 10000 loop 5 insert into hwm_test2(name1) values(' '); 6 commit; 7 end loop; 8 end; 9 / Procedure created. LKWTEST@TEST3 >exec massive_insert; PL/SQL procedure successfully completed. LKWTEST@TEST3 >@lkw_sess_event.sql EVENT TOTAL_WAITS TIME_WAITED ---------------------------------------------------------------- ----------- ----------- db file sequential read 238372 62386 direct path read 126177 39 direct path write 125960 23 log buffer space 13479 2477 log file single write 11260 3782 log file sequential read 11260 510 buffer busy waits 7396 1667 db file scattered read 4553 7390 enq: HW - contention 2879 1628 latch: shared pool 407 129 latch: cache buffers chains 372 7 log file sync 351 530 |
5. HWM 위치 파악
: dbms_space.unused_space 사용
SYS@TEST3 >create or replace procedure show_space 2 ( p_segname in varchar2, 3 p_owner in varchar2 default user, 4 p_type in varchar2 default 'TABLE', 5 p_partition in varchar2 default NULL ) 6 as 7 l_free_blks number; 8 9 l_total_blocks number; 10 l_total_bytes number; 11 l_unused_blocks number; 12 l_unused_bytes number; 13 l_LastUsedExtFileId number; 14 l_LastUsedExtBlockId number; 15 l_LAST_USED_BLOCK number; 16 procedure p( p_label in varchar2, p_num in number ) 17 is 18 begin 19 dbms_output.put_line( rpad(p_label,40,'.') || p_num ); 20 end; 21 begin 22 for x in ( select tablespace_name 23 from dba_tablespaces 24 where tablespace_name = ( select tablespace_name 25 from dba_segments 26 where segment_type = p_type 27 and segment_name = p_segname 28 and owner = p_owner ) 29 ) 30 loop 31 dbms_space.free_blocks 32 ( segment_owner => p_owner, 33 segment_name => p_segname, 34 segment_type => p_type, 35 partition_name => p_partition, 36 freelist_group_id => 0, 37 free_blks => l_free_blks ); 38 end loop; 39 40 dbms_space.unused_space 41 ( segment_owner => p_owner, 42 segment_name => p_segname, 43 segment_type => p_type, 44 partition_name => p_partition, 45 total_blocks => l_total_blocks, 46 total_bytes => l_total_bytes, 47 unused_blocks => l_unused_blocks, 48 unused_bytes => l_unused_bytes, 49 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, 50 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, 51 LAST_USED_BLOCK => l_LAST_USED_BLOCK ); 52 53 p( 'Free Blocks', l_free_blks ); 54 p( 'Total Blocks', l_total_blocks ); 55 p( 'Total Bytes', l_total_bytes ); 56 p( 'Total MBytes', trunc(l_total_bytes/1024/1024) ); 57 p( 'Unused Blocks', l_unused_blocks ); 58 p( 'Unused Bytes', l_unused_bytes ); 59 p( 'Last Used Ext FileId', l_LastUsedExtFileId ); 60 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); 61 p( 'Last Used Block', l_LAST_USED_BLOCK ); 62 end; 63 / Procedure created. SYS@TEST3 >SET SERVEROUTPUT ON SYS@TEST3 >exec show_space('TEST','LKWTEST','TABLE',NULL); Free Blocks.............................1 Total Blocks............................128 Total Bytes.............................1048576 Total MBytes............................1 Unused Blocks...........................127 Unused Bytes............................1032192 Last Used Ext FileId....................46 Last Used Ext BlockId...................10121 Last Used Block.........................1 PL/SQL procedure successfully completed. SYS@TEST3 >insert into lkwtest.test values (1); 1 row created. SYS@TEST3 >exec show_space('TEST','LKWTEST','TABLE',NULL); Free Blocks.............................1 Total Blocks............................128 Total Bytes.............................1048576 Total MBytes............................1 Unused Blocks...........................126 Unused Bytes............................1032192 Last Used Ext FileId....................46 Last Used Ext BlockId...................10121 Last Used Block.........................2 PL/SQL procedure successfully completed. SYS@TEST3 >select header_file, header_block from dba_segments where segment_name = 'TEST' and owner = 'LKWTEST'; HEADER_FILE HEADER_BLOCK ----------- ------------ 46 10121 SYS@TEST3 >alter system dump datafile 46 block 10121; System altered. --dump file 내용 Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 127 last map 0x00000000 #maps: 0 offset: 4128 Highwater:: 0x0b80278b ext#: 0 blk#: 1 ext size: 127 #blocks in seg. hdr's freelists: 1 #blocks below: 1 mapblk 0x00000000 offset: 0 Unlocked Map Header:: next 0x00000000 #extents: 1 obj#: 131457 flag: 0x40000000 |
참고 LMT & DMT
Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode.
Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table.
Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables.
As only one process can acquire the ST enque at a given time, this often lead to contention.
Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files. Each bit corresponds to a database block or group of blocks.
문서에 대하여
- 최초작성자 : 이경화
- 최초작성일 : 2011년 01월 06일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- 이 문서의 내용은 (주)엑셈에서 출간한 'PRACTICAL OWI IN ORACLE 10G'와 'Advanced OWI in Oracle 10g'를 참고하였습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/display/CORE/enq+HW+-+contention?
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.