by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

enq HW - contention

Added by 이경화, last edited by 이경화 on 1월 13, 2011



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.

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.