한마디로 스페이스 감시라고 해더라도, 그 목적 및 실현 수단은 여러가지입니다. 본장에서는 그 중에서도 이른바 「단편화」라고
하는 키워드에 주목해 단편화의 발생을 감시한다고 하는 관점에서 각종 스페이스 감시의 대상·방법에 대해 해설하겠습니다. 감시를
실시하고 문제를 밝혀낸 후, 그 문제를 해결하는 방법에 대해서는 다음 장인 제 6부에서 해설하겠습니다.이번은 SQL가 많이
소개됩니다만, 특별한 기술이 없는 한 SYSTEM 유저로 실행하고 있습니다.다른 유저로 실행하는 경우, 검색 대상의 딕쇼내리나
권한등을 적당 조정하면 된다. 덧붙여 본장으로 소개한 SQL는 실행 예의 형식이 되어 있으므로, SQL문을 재이용할 수 있도록
Code Tips에도 게재해 있습니다.
Code Tips
단편화란
Oracle에 있어서의 단편화란, Oracle의 물리 영역이 어떠한 형태로 불연속이 되어 있는 상태를 말합니다. 단편화가 발생하면 발생의 정도로 따라 주로 이하와 같은 영향이 발생한다.
- 영역이 효율적으로 이용되지 않고, 실데이터량에 비해 보다큰 디스크 용량을 소비해 버린다.
- I/O에 시간이 걸려, 퍼포먼스가 떨어진다.
한마디로 단편화 라고 해도 Oracle의 경우 DB의 물리 구조가 계층 구조가 되어 있어, 계층에 따라 나타나는 단편화의 현상에 차이가 있습니다.표 1으로 DB의 물리 구조에 대응한 단편화 현상을 정리했습니다. 앞으로 각각의 단편화 현상에 대하고 설명합니다. 단편화 현상의 해소 방법에 대해서는 제6회에서 정리해 해설하겠습니다.
| 계층 | 단편화 대상 |
|---|---|
| 데이터파일 （테이블스페이스） | ・파일 레벨의 단편화 ・데이터 파일 레벨의 미사용 영역의 발생 |
| 세그먼트 | ・위치(값)이 높은 하이 워터 마크 ・세그먼트 레벨의 미사용 영역발생 ・계층이깊은 인덱스 |
| 익스텐트 | ・불연속 extent |
| 블럭 |
・행 이행 블록내의 미사용 영역의 발생 |
| ◆ 제3장 세그먼트 레벨의 단편화 | ||||||||
|
HWM가 어떠한 것일까에 대해서는 제2장으로 해설했습니다.단지, 제2장으로 해설하고 있지 않는 중요한 포인트가 한 개
있습니다.그것은, HWM는 자동에서는 결코 내려가지 않는다는 점입니다.구체적인 낮추는 방법은 다음 번에 설명합니다만 예를 들면
전건을 DELETE문으로 삭제했다고 해도, HWM의 위치는 그대로입니다.
세그먼트 (segment) 레벨의 HWM는 주로
（1）HWM의 전건검색에 대한 영향
테이블이나 인덱스의 풀 스캔을 실행하는
경우, 실제의 스캔 범위는 테이블이나 인덱스 전체가 아니고, HWM의 위치까지를 스캔 합니다.이것에 의해 그림 3의 상부와 같이
실제로 용량을 확보하고 있는 사이즈에 비교해 데이터량이 적은 경우의 처리 시간을 단축하고 있습니다.그러나, HWM가 자동으로
내려갈리 없습니다. 그 때문에 일단 많이 데이터가 들어가 있는 상태로부터 대량 삭제가 있으면, 그림 3의 하부와 같이, 실제로는
데이터가 들어가 있지 않음에도 불구하고 HWM의 위치까지 스캔 해 버려, 실데이터량에 비교해 검색에 시간이 걸려 버립니다.
（2）HWM의 다이렉트 처리에 대한 영향
다이렉트·로드나 다이렉트·로드·인서트는
INSERT문에 의해 데이터를 삽입하는 것이 아니라, 먼저 블록에 저장된 포맷 이미지를 작성해, 그 블록 이미지를 직접
씁니다.그렇기 때문에 조금이라도 데이터가 들어가 있는 블록에는 쓸 수 없습니다. HWM 이후의 블록은 비어 있는 것이 보증되고
있기 때문에, 다이렉트 처리는 그림 4의 상부와 같이 HWM 이후의 블록에 데이터를 씁니다.이러한 처리에 의해 다이렉트 처리는
퍼포먼스를 확보하고 있습니다.덧붙여서 패러렐·다이렉트·로드 때는 HWM 이후의 extent로부터 씁니다. 만약 HWM 이전의
영역에 빈 곳이 많은 경우, 그림 4의 하부와 같이 세그먼트(segment)내에 큰 빈 영역이 생기게 됩니다.만약 이 테이블에
다이렉트 처리에 의한 데이터 삽입 밖에 없는 경우, 이 빈영역은 사용되지 않는채 남아 버립니다.
（3）HWM의 위치를 찾는 방법
HWM의 위치를 알려면
DBMS_SPACE 패키지에 있는 UNUSED_SPACE 프로시저를 이용합니다.이 프로시저를 이용하기 위해서는, ANALYZE
혹은 ANALYZE ANY 시스템 권한이 필요합니다. 다만 실제로 ANALYZE를 실시하는 것은 아니기 때문에, 룰 베이스로
운용하고 있는 시스템에서도 이용 가능합니다. 또 빈영역 관리를 프리 리스트가 아닌 자동 세그먼트(segment) 관리(ASSM)를
이용하고 있는 경우는, UNUSED_SPACE 프로시저말고 SPACE_USA GE프로시저를 이용하지 않으면 잘못된 결과가 되어
버립니다.다음의 UNUSED_SPACE 프로시저의 이용 예를 이용해 HWM의 위치가 어떻게 표현되는지를 설명합니다.
SQL>
SQL> set
serveroutput on
SQL> declare v_total_blocks number; v_total_bytes number; v_unused_blocks number; v_unused_bytes number; v_last_used_extent_file_id number; v_last_used_extent_block_id number; v_last_used_block number; begin dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype', v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block); dbms_output.put_line('HWM가 있는 데이터 파일의 ID ：' || to_char(v_last_used_extent_file_id, '9,999,990')); dbms_output.put_line('HWM가 있는 extent의 개시 블록 ID ：' || to_char(v_last_used_extent_block_id, '9,999,990')); dbms_output.put_line('HWM가 있는 블록의 위치 ：' || to_char(v_last_used_block, '9,999,990')); end; / uname에 값을 입력해 주세요: SCOTT sename에 값을 입력해 주세요: CUSTOMERS stype에 값을 입력해 주세요: TABLE
구 10:
dbms_space.unused_space(upper('&uname'), upper('&sename'),
'&stype',
신 10: dbms_space.unused_space(upper('SCOTT'), upper('CUSTOMERS'), 'TABLE', HWM가 있는 데이터 파일의 ID ： 11 HWM 가 있는 extent의 개시 블록 ID ： 1,033 HWM가 있는 블럭의 위치 ： 6 PL/SQL프로시져가 정상적으로 종료했습니다. 상기 SQL 스크립트의 실행에 있어서, uname에는 세그먼트(segment) 소유자명을, sename에는 세그먼트(segment)명을, stype에는 세그먼트(segment)의 타입(TABLE/TABLE PARTITION/TABLE SUBPARTITION/INDEX/INDEX PARTITION/INDEX SUBPARTITION/CLUSTER/LOB의 어느쪽이든)을 입력한다. 상기 SQL 스크립트의 행 결과를 설명하면「HWM가 있는 데이타 파일의 ID」는 프로시져의 V_LAST_USED_EXTENT_FILE_ID파라미터의 값이 됩니다. DBA_DATA_FILES 딕쇼내리의 FILE_ID열이 V_LAST_USED_EXTENT_FILE_ID파라미터와 일치하는 데이터 파일중에 HWM가 존재하는 것을 나타내고 있습니다
「HWM가 있는 extent의 개시 블록 ID」는, 프로시저의
V_LAST_USED_EXTENT_BLOCK_ID파라미터의 값이 됩니다.???_EXTENTS(이하???(은)는
DBA/ALL/USER의 어느것이든) 딕쇼내리의 FILE_ID열이 V_LAST_USED_EXTENT_FILE_ID와 같고, 한편
BLOCK_ID열이 V_LAST_USED_EXTENT_BLOCK_ID와 일치하는 익스텐트중에 HWM가 존재하는 것을 나타내고
있습니다.「HWM가 있는 블록의 위치」는, 프로시저의 V_LAST_USED_BLOCK 파라미터의 값이 됩니다.해당 extent의
V_LAST_USED_BLOCK 파라미터의 값번째의 블록에 HWM가 존재하는 것을 나타내고 있습니다.
세 그먼트(segment) 레벨의 미사용 영역의 발생 HWM 이후의 미사용 영역 제2장의 데이터 파일의 HWM 이후의 미사용 영역과 같은 영향이 있습니다. HWM 이후의 미사용 영역은 이하와 같은 방법으로 조사할수 있다. （1）DBMS_SPACE.UNUSED_SPACE프로시져의 사용
앞서 기술한
DBMS_SPACE.UNUSED_SPACE 프로시저를 이용하는 것으로 세그먼트(segment)의 HWM 이후의 미사용 영역의
크기를 계산할 수 있습니다. 아래의 샘플 스크립트를 참조해 주세요.
SQL> set
serveroutput on
SQL> declare v_total_blocks number; v_total_bytes number; v_unused_blocks number; v_unused_bytes number; v_last_used_extent_file_id number; v_last_used_extent_block_id number; v_last_used_block number; begin dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype', v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block); dbms_output.put_line('현세그먼트용량：' || to_char(v_total_bytes, '999,999,999,990') || ' バイト'); dbms_output.put_line('소비용량 ：' || to_char(v_total_bytes - v_unused_bytes, '999,999,999,990') || ' バイト'); dbms_output.put_line('나머지용량 ：' || to_char(v_unused_bytes, '999,999,999,990') || ' バイト'); dbms_output.put_line('소비율 ： ' || to_char((v_total_bytes - v_unused_bytes) / v_total_bytes * 100, '990.99') || ' %'); end; / uname에 값을 입력해 주세요: SCOTT sename에 값을 입력해 주세요: CUSTOMERS stype에 값을 입력해 주세요: TABLE 구10: dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype', 신 10: dbms_space.unused_space(upper('SCOTT'), upper('CUSTOMERS'), 'TABLE', 현세그먼트용량 ： 9,437,184 바이트 소비용량 ： 8,593,408 바이트 나머지용량 ： 843,776 바이트 소비율 ： 91.06 % PL/SQL프로시져가 정상 종료하였습니다. （2）ANALYZE 실행 후 딕쇼내리를 참조한다
테이블의 경우는 ANALYZE를 실행한
후의???_TABLES 딕쇼내리의 BLOCKS와 EMPTY_BLOCKS의 값을 검색하는 것으로도 HWM 이후의 미사용 영역의
크기를 구할 수 있습니다.BLOCKS는 세그먼트(segment)내의 사용이 끝난 블록수, EMPTY_BLOCKS는
세그먼트(segment)내의 미사용 블록수(HWM 이후)를 나타냅니다.
select
to_char((blocks + empty_blocks) * 8192, 'FM999,999,999,990') as "테이블
용량",
to_char(empty_blocks * 8192, 'FM999,999,999,990') as "남은용량" from dba_tables where owner = '&uname' and table_name = '&tname'; uname에 값을 입력해 주세요: SCOTT tname에 값을 입력해 주세요: CUSTOMERS 구 3: from dba_tables where owner = '&uname' and table_name = '&tname' 신 3: from dba_tables where owner = 'SCOTT' and table_name = 'CUSTOMERS' 테이블 용량 남은용량 ----------------------- -------------------------------- 9,428,992 843,776 상기의 테이블의 사용량에 관해서 SQL의 실행에 있어서, uname에는 세그먼트(segment) 소유자명을, tname에는
테이블명을 입력한다. HWM 이전의 미사용 영역의 영향은, 본장의 최초로 설명한 HWM의 해설을 참조. 계산방법은, 테이블만의 대응입니다만,
이하와 같은 SQL로 산출할 수 있습니다.
SQL> analyze
table scott.customers compute statistics;
테이블이 분석되었습니다 SQL>
select
to_char(avg_space * blocks, 'FM999,999,999,999') "빈영역"
from dba_tables where owner = '&uname' and table_name = '&tname'; uname 에 값을 입력해 주세요: SCOTT tname에 값을 입력해 주세요: CUSTOMERS 구 2: from dba_tables where owner = '&uname' and table_name = '&tname' 신 2: from dba_tables where owner = 'SCOTT' and table_name = 'CUSTOMERS' 빈 영역 -------------------------------- 595,264 상기의 테이블의 사용량에 관해서 SQL의 실행에 있어서 uname에는 세그먼트(segment) 소유자명을, tname에는
테이블명을 입력해 주세요. B*Tree 인덱스는 계층 구조로 되어 있습니다. 이 계층이 깊으면 검색에 시간이 걸리게 됩니다. 계층의 깊이는, 인덱스를 ANALYZE 한 뒤 INDEX_STATS 딕쇼내리의 HEIGHT열 내지는 ???_INDEXES 딕쇼내리의 BLEVEL열로 조사할 수 있습니다. 이러한 열의 값이 4이상의 경우는, 인덱스를 이용한 검색의 퍼포먼스에 영향이 있으므로 때문에 재편성을 검토 한다. 또한 INDEX_STATS 딕쇼내리를 검색하는 경우는, VALIDATE STRUCTURE 옵션 첨부로 ANALYZE 커멘드를 실행할 필요가 있습니다. |
| ◆ 제4장 익스텐트 레벨의 단편화 |
|
어떤 세그먼트(segment)를 구성하는 extent가 연속해서 확보되어 있지 않아도 퍼포먼스에 거의 영향을 주지 않습니다. 단지 불연속의 extent간의 타세그먼트(segment)의 extent가 미사용 extent가 되었을 경우, 특히 딕쇼내리 관리표 영역에서 세그먼트(segment)마다 개별의 INITIAL/NEXT/PCTINCREASE를 지정해 있는 경우는 불필요 영역의 발생하기 쉬워집니다. 로컬 관리의 경우는 이러한 일이 일어나지 않도록 하는 것으로 특별히 감시할 필요는 없을 것입니다. 감시하고 싶은 경우는 이하와 같은 스크립트로 감시를 할 수 있습니다.
SQL> select
ext2.extent_id "extent ID", ext2.file_id "file ID",
ext2.block_id "개시블럭ID", ext2.blocks "블럭수", case when ext2.extent_id = 0 then '선두 EXTENT' when ext2.old_fid <> ext2.file_id then '데이터파일틀림' 5 when ext2.old_blk_id <> ext2.block_id then '불연속EXTENT' 6 else '연속EXTENT' 7 end "단편화 상태" 8 from (select ext1.extent_id, ext1.file_id, ext1.block_id, ext1.blocks, 9 lag(ext1.file_id, 1) over (order by ext1.extent_id) old_fid, 10 lag(ext1.block_id, 1) over (order by ext1.extent_id) + 11 lag(ext1.blocks, 1) over (order by ext1.extent_id) old_blk_id 12 from dba_extents ext1 13 where ext1.owner = '&uname' and ext1.segment_name = '&sname') ext2; uname에 값을 입력해 주세요: SCOTT sname에 값을 입력해 주세요: C3 구 13: where ext1.owner = '&uname' and ext1.segment_name = '&sname') ext2 신 13: where ext1.owner = 'SCOTT' and ext1.segment_name = 'C3') ext2 extent ID file ID 개시블럭ID 블럭수 단편화 상태 --------- ------- ---------- ------ -------------------------- 0 12 113 2 선두 EXTENT 1 13 93 2 데이터파일틀림 2 12 115 2 데이터파일틀림 3 13 95 2 데이터파일틀림 4 13 97 2 연속EXTENT 5 13 99 2 연속EXTENT 6 13 101 2 연속EXTENT 7 12 119 2 데이터파일틀림 8 12 123 2 불연속EXTENT 9 12 125 2 연속EXTENT 10행이 선택되었습니다. |
| ◆ 제5장 블록 레벨의 단편화 | ||||
행 이행 어느 레코드에 대해서 갱신을 했을 때에, PCTFREE로 확보한 영역을 가지고 있더라고 원래 존재하고 있던 블록에 들어가지 않는 길이가 되어 버렸다고 해 봅시다. 이 경우 Oracle는 해당 레코드를 다른 블록에 격납합니다. 이 때, 원래의 블록에 새로운 격납처에의 포인터를 남깁니다.이러한 상태를 행 이행이라고 부릅니다. 행 이행이 발생하면 해당 레코드는 본래 1 블록에 들어가는 길이 임에도 불구하고 2 블록에 건너 격납됩니다. 즉 이 레코드에 액세스 하기 위해서는 2 블록 읽어들일 필요가 있는 것입니다.행 이행이 발생하고 있는 레코드에의 액세스가 많으면 읽기 I/O의 증가나 캐쉬 히트율의 저하라고 하는 영향이 발생합니다.
행 연쇄는 행 이행과 같이 레코드가 복수 블록에 걸치는 현상입니다만, 행 연쇄는 단지 블록에 격납 가능한 사이즈 이상의
레코드가 복수 블록에 건너 격납되는 현상입니다. 물리적으로 큰 레코드를 넣으려 하고 있는 이상 어쩔 수 없는 현상입니다만,
다발하면 행 이행과 같은 영향이 있습니다.
행 이행과 행 연쇄는 같은 방법으로 조사합니다. 테이블을 ANALYZE 한 후,???_TABLES 딕쇼내리의 CHAIN_CNT열에 행 이행내지 행 연쇄하고 있는 레코드수가 격납됩니다. 즉, 행 이행과 행 연쇄는 따로 따로 파악할 수 없습니다.
본쳅터 이전에 문제가 발생하고 있지 않는데 세그먼트(segment) 용량에 비교해 격납 가능한 데이터량이 적은 경우는 블록의 내용이 효율적으로 이용되어 있지 않은 경우를 생각할 수 있습니다. 예를 들면 데이터 격납 가능 영역이 3000바이트에 대해서 레코드 사이즈가 평균 2000바이트의 경우, 단순 계산이라면 1 블록에 대해 1000바이트의 쓸데없는 공간이 발생하게 됩니다. 테이블이면 DELETE의 기회에 비교해 PCTUSED의 값이 낮은 경우에 블록 레벨의 미사용 영역이 발생하기 쉬워집니다. 인덱스의 경우는 DELETE가 많은 경우, 인덱스 대상열의 값이 승순은 아니고 랜덤이나 내림차순에 INSERT 되는 경우, 인덱스 대상열에 대해서 갱신이 발생하는 것이 많은 경우에 블록 레벨의 미사용 영역이 발생하기 쉬워집니다. 용량 견적 서비스등을 이용하고, 이론적인 견적치에 대해서 실제의 용량(HWM까지의 용량)이 너무 큰 경우는 재편성을 검토한다. 특히 풀 스캔의 경우는 견적치와 실제치의 비에 가까운 레벨로 처리 시간이 걸립니다. |
출처 : http://blog.naver.com/hirokorea?Redirect=Log&logNo=20023344842
[출처] [Oracle 물리설계] 제5부 영역감시 |작성자 샤크
'ORACLE > TUNING' 카테고리의 다른 글
| AWR (Automatic Workload Repository) (0) | 2010.03.23 |
|---|---|
| Statspack 생성/삭제/Sanpshot생성 (0) | 2010.03.23 |
| Oracle Hidden Parameter 란 (0) | 2010.01.26 |
| FAST_START_MTTR_TARGET (0) | 2010.01.18 |
| EXPLAIN PLAN(실행계획) 이란 (0) | 2010.01.12 |
