About DATABASE

출처:http://www.oracleinternal.com/

데 이터베이스를 복사하는 절차에 대해서 알아보겠습니다.
orcl DB를 clon으로 복사할것이며 대략적인 절차는 다음과 같습니다.

5. clone DB로 접속후 control을 생성하기 위해 nomount로 시작합니다.

OS$ export ORACLE_SID=clone
OS$ sqlplus / as sysdba

한마디로 스페이스 감시라고 해더라도, 그 목적 및 실현 수단은 여러가지입니다. 본장에서는 그 중에서도 이른바 「단편화」라고 하는 키워드에 주목해  단편화의 발생을 감시한다고 하는 관점에서 각종 스페이스 감시의 대상·방법에 대해 해설하겠습니다. 감시를 실시하고 문제를 밝혀낸 후, 그 문제를 해결하는 방법에 대해서는 다음 장인 제 6부에서 해설하겠습니다.이번은 SQL가 많이 소개됩니다만, 특별한 기술이 없는 한 SYSTEM 유저로 실행하고 있습니다.다른 유저로 실행하는 경우, 검색 대상의 딕쇼내리나 권한등을 적당 조정하면 된다. 덧붙여 본장으로 소개한 SQL는 실행 예의 형식이 되어 있으므로, SQL문을 재이용할 수 있도록 Code Tips에도 게재해 있습니다.
Code Tips

단편화란

Oracle에 있어서의 단편화란, Oracle의 물리 영역이 어떠한 형태로 불연속이 되어 있는 상태를 말합니다. 단편화가 발생하면 발생의 정도로 따라 주로 이하와 같은 영향이 발생한다.

• 영역이 효율적으로 이용되지 않고, 실데이터량에 비해 보다큰 디스크 용량을 소비해 버린다.
• I/O에 시간이 걸려, 퍼포먼스가 떨어진다.

한마디로 단편화 라고 해도 Oracle의 경우 DB의 물리 구조가 계층 구조가 되어 있어, 계층에 따라 나타나는 단편화의 현상에 차이가 있습니다.표 1으로 DB의 물리 구조에 대응한 단편화 현상을 정리했습니다. 앞으로 각각의 단편화 현상에 대하고 설명합니다. 단편화 현상의 해소 방법에 대해서는 제6회에서 정리해 해설하겠습니다.

◆ 제2장 데이터파일(데이블스페이스)레벨의 단편화

파일 레벨의 단편화 아무리 블록 레벨이나 세그먼트(segment) 레벨 등의 세세한 레벨로 단편화를 해소해도, 데이터 파일의 레벨로 단편화가 발생하고 있으면 별로 의미가 없습니다.특히 하나의 디스크에 데이타베이스가 깔려있고 게다가  초기의 물리 설계를 대충한 시스템에 대해선  격렬하게 단편화가 발생해 시스템의 퍼포먼스를 떨어뜨리고 있을 가능성이 있습니다.또, 테이블 스페이스의 설정으로 자동 확장을 하고 있었을 경우, 파일 레벨의 단편화가 일어나기 쉬워집니다. 파일 레벨의 단편화의 상황은 OS레벨의 이야기가 된다.  예를 들면 Windows라면 defrag 툴이라고 하는 OS의 기능이용한다.   High Water mark란 데이터 파일 레벨의 미사용 영역의 발생에 대해 설명하기전에 사전 지식으로서 하이 워터 마크(이하 HWM)에 대해 해설합니다. Oracle에 있어서의 HWM와는 과거에 데이터가 격납된 것이 있는 제일 높은(마지막) 위치를 나타내는 지표가 됩니다.하이 워터 마크의 개념은 데이터 파일과 세그먼트(segment)에 존재합니다.   그림1:high water mark의 개념 데이터 파일 레벨의 미사용 영역의 발생　HWM 이후의 미사용 영역 데이터 파일내의 미사용 영역은, HWM 이후의 미사용 영역 및 HWM 이전의 세그먼트(segment)간의 미사용 영역으로부터 구성됩니다.여기에서는 전자에 대해 언급하겠습니다.HWM 이후의 미사용 영역은 데이터의 검색이나 갱신에는 영향을 주지 않습니다만, 격납하고 있는 데이터 용량에 비교해 데이터 파일의 사이즈가 크기 때문에 실데이터량에 비교해 백업 및restore에 시간을 필요로 하게 됩니다.반면 앞으로의 데이터 파일의 확장을 막기 위해서 사전 확보하고 있는 경우도 있으므로 통틀어 HWM 이후의 미사용 영역이 큰 것이 나쁜 일이라고는 말할 수 없습니다.차후의 데이터의 증가 예상과의 균형으로 사이즈의 조정을 실시 한다.어느 테이블스페이스에 존재하는 데이터 파일의 HWM 이후의 미사용 영역의 합계에 관한 정보는 이하의 SQL로 파악하는 것이 가능합니다.   ＜테이블 스페이스의 데이터 파일마다의 HWM 이후의 미사용 영역의 사이즈를 구하기＞ SQL> select sumdf.file_name "Datafile 명",   to_char(sumdf.total_bytes, 'FM999,999,999,990') "tablespace size",   to_char(sumfs.free_bytes, 'FM999,999,999,990')     "HWM이후 미사용 영역사이즈" from (select df.file_id, df.file_name, sum(df.bytes) total_bytes       from dba_data_files df       where df.tablespace_name = upper('&&tsname')       group by df.file_id, df.file_name) sumdf left outer join (select fs.file_id, fs.bytes free_bytes                  from (select fs2.file_id, fs2.bytes, fs2.block_id,                          max(fs2.block_id) over (partition by                          fs2.file_id) max_block                        from dba_free_space fs2                        where fs2.tablespace_name = upper('&&tsname')                        ) fs                  where fs.block_id = fs.max_block) sumfs on (sumdf.file_id = sumfs.file_id);   tsname에 값을 입력하시오: USERS3 구6: where df.tablespace_name = upper('&&tsname') 신6: where df.tablespace_name = upper('USERS3') 구12: where fs2.tablespace_name = upper('&&tsname')) fs 신12: where fs2.tablespace_name = upper('USERS3')) fs   Datafile명 -------------------------------------------------------------------------------- tablespace size                              HWM이후 미사용 영역사이즈 -------------------------------- -------------------------------- D:\ORACLE\ORADATA\TEST1\USERS03.DBF 786,432,000                                 34,504,704 D:\ORACLE\ORADATA\TEST1\USERS03_2.ORA 134,217,728                                 133,464,064   상기 SQL의 실행에 있어서 tsname에는 테이블 스페이스명을 입력해 주세요.DBA_FREE_SAPCE 딕쇼내리의 각 데이터 파일마다로 블록 ID가 제일 큰 빈영역을 추출하고 있습니다.    하는 김에 한 테이블 스페이스 전체의 미사용 영역을 요구하는 SQL도 게재해 보았습니다.   ＜어느 테이블 스페이스의 미사용 영역의 총사이즈를 구하기＞ SQL>   tselect to_char(sumdf.total_bytes, 'FM999,999,999,990')   "tablespace size",   to_char(sumdf.total_bytes - sumfs.free_bytes, 'FM999,999,999,990')   " 사용완료 영역 size",   to_char(sumfs.free_bytes, 'FM999,999,999,990') "미사용영 역사이즈",   to_char((nvl(sumfs.free_bytes, 0) / sumdf.total_bytes) * 100,    'FM990.99') || '%' " 사용율" from (select df.tablespace_name, sum(df.bytes) total_bytes       from dba_data_files df       group by df.tablespace_name) sumdf left outer join (select fs.tablespace_name, sum(fs.bytes) free_bytes                  from dba_free_space fs                  group by fs.tablespace_name) sumfs on (sumdf.tablespace_name = sumfs.tablespace_name) where sumdf.tablespace_name = upper('&tsname');   tsname에 값을 입력하시오: USERS3 旧 13: where sumdf.tablespace_name = upper('&tsname') 新13: where sumdf.tablespace_name = upper('USERS3')   tablespace size                            사용완료 영역 size -------------------------------- -------------------------------- 미사용영역사이즈                         사용율 -------------------------------- ---------------- 920,649,728                               487,653,376 432,996,352                               47.03%   상기 SQL의 실행에 있어서 tsname에는 테이블 스페이스명을 입력해 주세요. 이쪽은 비어있는 영역 모두를 집계하고 있습니다. 데이터 파일 레벨의 미사용 영역의 발생　HWM 이전의 세그먼트(segment)간의 미사용 영역 데이터 파일내의 미사용 영역은, HWM 이후의 미사용 영역 및 HWM 이전의 세그먼트(segment)간의 미사용 영역으로부터 구성됩니다.여기에서는 후자에 대해 언급하겠습니다. 세그먼트(segment)간의 미사용 영역은 물리 단위에서는 extent가 됩니다.로컬 관리표 영역에서, 특히 UNIFORM 사이즈 지정의 경우는 이러한 영역도 효율적으로 이용됩니다만, AUTOALLOCATE 지정의 경우는 미사용인 채 남을 가능성이 있습니다.HWM 이전의 세그먼트(segment)간에 어느 정도의 미사용 extent가 존재하는지에 대해서는 이하의 SQL로 조사할 수 있습니다.   ＜HWM 이전의 세그먼트(segment)간의 미사용 영역을 구하기＞ SQL> select sumdf.file_name " 데이터 파일명",   to_char(sumfs.free_bytes, 'FM999,999,999,990') "미사용영역사이즈" from (select df.file_id, df.file_name from dba_data_files df       where df.tablespace_name = upper('&&tsname')) sumdf left outer join (select fs.file_id, fs.bytes free_bytes                  from (select fs2.file_id, fs2.bytes, fs2.block_id,                          max(fs2.block_id) over (partition by                          fs2.file_id) max_block                        from dba_free_space fs2                        where fs2.tablespace_name = upper('&&tsname')                       ) fs                  where fs.block_id <> fs.max_block) sumfs on (sumdf.file_id = sumfs.file_id);   tsname에 값을 입력하시오: USERS3 구4: where df.tablespace_name = upper('&&tsname')) sumdf 신4: where df.tablespace_name = upper('USERS3')) sumdf 구9: where fs2.tablespace_name = upper('&&tsname')) fs 신9: where fs2.tablespace_name = upper('USERS3')) fs   데이터 파일명                                                   미사용영역사이즈 -------------------------------------------- ----------------- D:\ORACLE\ORADATA\TEST1\USERS03.DBF             32,768 D:\ORACLE\ORADATA\TEST1\USERS03.DBF             16,384 D:\ORACLE\ORADATA\TEST1\USERS03.DBF             278,528 D:\ORACLE\ORADATA\TEST1\USERS03.DBF             32,768 D:\ORACLE\ORADATA\TEST1\USERS03.DBF             81,920    .........(이하생략) 상기 SQL의 실행에 있어서  tsname에는 테이블 스페이스명 입력한다.이 결과 많은 행이 표시되었다고 해도, 세그먼트(segment)간의 미사용 영역의 수가 많은 것에 의해 퍼포먼스에 관한 영향은, 로컬 관리표 영역의 경우는 경미합니다.또, 백업이나 restore의 처리 시간에는 HWM 이후의 미사용 영역과 같이 영향을 줍니다.또 빈 영역의 수는 아니고 합계 사이즈가 많은 경우는, 자세한 것은 다음 번에 해설합니다만 데이터 파일의 축소의 효과가 작아져 버립니다.

◆ 제3장 세그먼트 레벨의 단편화

수위가 높은 하이 워터 마크 HWM가 어떠한 것일까에 대해서는 제2장으로 해설했습니다.단지, 제2장으로 해설하고 있지 않는 중요한 포인트가 한 개 있습니다.그것은, HWM는 자동에서는 결코 내려가지 않는다는 점입니다.구체적인 낮추는 방법은 다음 번에 설명합니다만  예를 들면 전건을 DELETE문으로 삭제했다고 해도, HWM의 위치는 그대로입니다. 그림2:DELETE로 움직이지 않는 하이 워터 마크 세그먼트 (segment) 레벨의 HWM는 주로 전건검색 다이렉트·로드/다이렉트·로드·인서트 에 영향을 줍니다.이하 구체적으로 해설합니다. （1）HWM의 전건검색에 대한 영향 테이블이나 인덱스의 풀 스캔을 실행하는 경우, 실제의 스캔 범위는 테이블이나 인덱스 전체가 아니고, HWM의 위치까지를 스캔 합니다.이것에 의해 그림 3의 상부와 같이 실제로 용량을 확보하고 있는 사이즈에 비교해 데이터량이 적은 경우의 처리 시간을 단축하고 있습니다.그러나, HWM가 자동으로 내려갈리 없습니다. 그 때문에  일단 많이 데이터가 들어가 있는 상태로부터 대량 삭제가 있으면, 그림 3의 하부와 같이, 실제로는 데이터가 들어가 있지 않음에도 불구하고 HWM의 위치까지 스캔 해 버려, 실데이터량에 비교해 검색에 시간이 걸려 버립니다. 그림3:하이 워터 마크의 풀 스캔시의 영향 （2）HWM의 다이렉트 처리에 대한 영향 다이렉트·로드나 다이렉트·로드·인서트는 INSERT문에 의해 데이터를 삽입하는 것이 아니라, 먼저 블록에 저장된 포맷 이미지를 작성해, 그 블록 이미지를 직접 씁니다.그렇기 때문에 조금이라도 데이터가 들어가 있는 블록에는 쓸 수 없습니다. HWM 이후의 블록은 비어 있는 것이 보증되고 있기 때문에, 다이렉트 처리는 그림 4의 상부와 같이 HWM 이후의 블록에 데이터를 씁니다.이러한 처리에 의해 다이렉트 처리는 퍼포먼스를 확보하고 있습니다.덧붙여서 패러렐·다이렉트·로드 때는 HWM 이후의 extent로부터 씁니다. 만약 HWM 이전의 영역에 빈 곳이 많은 경우, 그림 4의 하부와 같이 세그먼트(segment)내에 큰 빈 영역이 생기게 됩니다.만약 이 테이블에 다이렉트 처리에 의한 데이터 삽입 밖에 없는 경우, 이 빈영역은 사용되지 않는채 남아 버립니다.   그림4:하이 워터 마크의 다이렉트 처리에의 영향 （3）HWM의 위치를 찾는 방법 HWM의 위치를 알려면  DBMS_SPACE 패키지에 있는 UNUSED_SPACE 프로시저를 이용합니다.이 프로시저를 이용하기 위해서는, ANALYZE 혹은 ANALYZE ANY 시스템 권한이 필요합니다. 다만 실제로 ANALYZE를 실시하는 것은 아니기 때문에, 룰 베이스로 운용하고 있는 시스템에서도 이용 가능합니다. 또 빈영역 관리를 프리 리스트가 아닌 자동 세그먼트(segment) 관리(ASSM)를 이용하고 있는 경우는, UNUSED_SPACE 프로시저말고 SPACE_USA GE프로시저를 이용하지 않으면 잘못된 결과가 되어 버립니다.다음의 UNUSED_SPACE 프로시저의 이용 예를 이용해 HWM의 위치가 어떻게 표현되는지를 설명합니다.   ＜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가 존재하는 것을 나타내고 있습니다.   그림5:하이 워터 마크의 위치 세 그먼트(segment) 레벨의 미사용 영역의 발생　HWM 이후의 미사용 영역 제2장의 데이터 파일의 HWM 이후의 미사용 영역과 같은 영향이 있습니다. HWM 이후의 미사용 영역은 이하와 같은 방법으로 조사할수 있다. （1）DBMS_SPACE.UNUSED_SPACE프로시져의 사용 앞서 기술한 DBMS_SPACE.UNUSED_SPACE 프로시저를 이용하는 것으로 세그먼트(segment)의 HWM 이후의 미사용 영역의 크기를 계산할 수 있습니다. 아래의 샘플 스크립트를 참조해 주세요. ＜HWM이후의 미사용영역 구하기(1)＞   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프로시져가 정상 종료하였습니다. 상 기 SQL의 실행에 있어서, uname에는 세그먼트(segment) 소유자명을, sname에는 세그먼트(segment)명을, stype에는 세그먼트(segment)의 타입(TABLE/TABLE PARTITION/TABLE SUBPARTITION/INDEX/INDEX PARTITION/INDEX SUBPARTITION/CLUSTER/LOB)을 입력한다. （2）ANALYZE 실행 후 딕쇼내리를 참조한다 테이블의 경우는 ANALYZE를 실행한 후의???_TABLES 딕쇼내리의 BLOCKS와 EMPTY_BLOCKS의 값을 검색하는 것으로도 HWM 이후의 미사용 영역의 크기를 구할 수 있습니다.BLOCKS는 세그먼트(segment)내의 사용이 끝난 블록수, EMPTY_BLOCKS는 세그먼트(segment)내의 미사용 블록수(HWM 이후)를 나타냅니다. ＜HWM 이후의 미사용 영역 구하기(2)＞    SQL> analyze table scott.customers compute statistics; 테이블이 분석되었습니다. SQL>          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에는 테이블명을 입력한다. 이 방법에 의한 세그먼트(segment) 용량과 (1)의 DBMS_SPACE.UNUSED_SPACE 프로시저를 이용하는 방법에 따르는 세그먼트(segment) 용량의 1 블록분 (본시험대에서는, 블록 사이즈를 8,192바이트로 하고 있습니다)의 차이는, 세그먼트(segment) 헤더를 포함하는지 포함하지 않는지의 차이입니다.전자가 세그먼트(segment) 헤더를 포함한 크기가 되어 있습니다. 세그먼트(segment) 레벨의 미사용 영역의 발생　HWM 이전의 미사용 영역 HWM 이전의 미사용 영역의 영향은, 본장의 최초로 설명한 HWM의 해설을 참조. 계산방법은, 테이블만의 대응입니다만, 이하와 같은 SQL로 산출할 수 있습니다. ＜HWM이전의 미사용영역 구하기＞ 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 커멘드를 실행할 필요가 있습니다.

◆ 제5장 블록 레벨의 단편화

행 이행 어느 레코드에 대해서 갱신을 했을 때에, PCTFREE로 확보한 영역을 가지고 있더라고 원래 존재하고 있던 블록에 들어가지 않는 길이가 되어 버렸다고 해 봅시다. 이 경우 Oracle는 해당 레코드를 다른 블록에 격납합니다. 이 때, 원래의 블록에 새로운 격납처에의 포인터를 남깁니다.이러한 상태를 행 이행이라고 부릅니다. 행 이행이 발생하면 해당 레코드는 본래 1 블록에 들어가는 길이 임에도 불구하고 2 블록에 건너 격납됩니다. 즉 이 레코드에 액세스 하기 위해서는 2 블록 읽어들일 필요가 있는 것입니다.행 이행이 발생하고 있는 레코드에의 액세스가 많으면 읽기 I/O의 증가나 캐쉬 히트율의 저하라고 하는 영향이 발생합니다. 그림6:행 이행 행 연쇄 행 연쇄는 행 이행과 같이 레코드가 복수 블록에 걸치는 현상입니다만, 행 연쇄는 단지 블록에 격납 가능한 사이즈 이상의 레코드가 복수 블록에 건너 격납되는 현상입니다. 물리적으로 큰 레코드를 넣으려 하고 있는 이상 어쩔 수 없는 현상입니다만, 다발하면 행 이행과 같은 영향이 있습니다. 그림7:행 연쇄 행 이행·행 연쇄를 조사한다 행 이행과 행 연쇄는 같은 방법으로 조사합니다. 테이블을 ANALYZE 한 후,???_TABLES 딕쇼내리의 CHAIN_CNT열에 행 이행내지 행 연쇄하고 있는 레코드수가 격납됩니다. 즉, 행 이행과 행 연쇄는 따로 따로 파악할 수 없습니다. 블록 레벨의 미사용 영역의 발생 본쳅터 이전에 문제가 발생하고 있지 않는데 세그먼트(segment) 용량에 비교해 격납 가능한 데이터량이 적은 경우는 블록의 내용이 효율적으로 이용되어 있지 않은 경우를 생각할 수 있습니다. 예를 들면 데이터 격납 가능 영역이 3000바이트에 대해서 레코드 사이즈가 평균 2000바이트의 경우, 단순 계산이라면 1 블록에 대해 1000바이트의 쓸데없는 공간이 발생하게 됩니다. 테이블이면 DELETE의 기회에 비교해 PCTUSED의 값이 낮은 경우에 블록 레벨의 미사용 영역이 발생하기 쉬워집니다. 인덱스의 경우는 DELETE가 많은 경우, 인덱스 대상열의 값이 승순은 아니고 랜덤이나 내림차순에 INSERT 되는 경우, 인덱스 대상열에 대해서 갱신이 발생하는 것이 많은 경우에 블록 레벨의 미사용 영역이 발생하기 쉬워집니다. 용량 견적 서비스등을 이용하고, 이론적인 견적치에 대해서 실제의 용량(HWM까지의 용량)이 너무 큰 경우는 재편성을 검토한다. 특히 풀 스캔의 경우는 견적치와 실제치의 비에 가까운 레벨로 처리 시간이 걸립니다.

이러한 parameter는 여러가지 용도로 사용됩니다.

 1. 향후 지원될 기능을 미리 넣어두고 test하기 위해.(오라클내에서)

 2. 어떤 문제가 발생한 경우 자세한 trace를 뜨기 위해.

 3. 임의의 기능에 대한 변경 및 제약을 가하기 위해.

예를 들면 _trace_files_public 은 sql trace file이 oracle udump 에 생기는데,

Unix의 경우, oracle owner의 권한으로만 read, write를 할수 있게 되죠.

 그래서 일반 다른 os user(dba가 아닌)가 sql trace를 떠서 확인해 보려면

권한이 없어서 볼수 가 없읍니다.

  이때, 위의 _trace_files_public=true로 지정하게 되면 other user(일반 user)도

읽을수 있는 권한으로 trace file이 생깁니다.

 이러한 hidden parameter를 조회 할 수 있는 sql은 아래와 같습니다.

sys user로 접속해서...

select ksppinm
from x$ksppi
where substr(ksppinm,1,1) = '_';

출처 : http://scidb.tistory.com/entry/%EC%98%A4%EB%A0%8C%EC%A7%80%EB%82%98-TOAD%EC%97%90%EC%84%9C-Predicate-Information%EC%9D%84-%EC%B0%B8%EC%A1%B0%ED%95%98%EB%8A%94-%EB%B0%A9%EB%B2%95

지인으로부터 아래와 같은 질문을 받았다.

질문 : Predicate Information을 참조하려면 DBMS_XPLAN 패키지를 사용해야만 합니까?

       저희 개발자들은 DBMS_XPLAN 패키지를 사용할 권한이 없습니다.
       따라서 오렌지나 TOAD에서 간단히 볼 수 있는 방법이 필요합니다. 가능 합니까?

답변 : 볼 수 있습니다. 단 PLAN_TABLE을 볼수 있는 권한은 있어야 합니다.

요청: 그건 있습니다. 방법을 블로그에 올려주시면 나머지 사람들도 볼수 있겠네요. 올려주시죠.

이렇게 해서 이글을 작성 하게 되었다. 이런 질문을 받았다는 것은 2가지 의미로 해석할 수 있다. 첫번째, 의외로 오렌지나 TOAD의 기능을 모르는 사람이 많이 있을 수 있다는 의미다. 두번째, 튜닝에 필요한 권한이 개발자에게 없다는 안타까움 이다. 이 정책은 매우 아쉬운 선택이며 앞으로 개선되기를 기대해본다. 하지만 수정이 필요한 법이나 악법도 법이므로 수정되기 전까진 따라야 한다.

Predicate Information과 관련된 가장 흔한 오류는 10046 이벤트 + tkprof를 사용하면 Predicate Information을 볼수 있다고 착각 하는 것이다. 절대 볼수 없다. 

Predicate Information이 뭐지?
Predicate Information이란 인덱스 scan 시의 컬럼 액세스 정보, 조인정보, filter 정보를 각 Opreation 단위로 나타낸 것이다. 아래의 예제를 보자.
 

explain plan for

SELECT /*+ LEADING(e) USE_NL(d) */

       e.employee_id, e.first_name, e.last_name, e.email, e.salary

  FROM employee e, department d

 WHERE e.department_id = d.department_id

   AND e.job_id = 'SH_CLERK';

select * from table(dbms_xplan.display);  

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

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT            |            |    20 |   860 |     3   (0)|

|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE   |    20 |   860 |     3   (0)|

|*  2 |   INDEX RANGE SCAN          | EMP_JOB_IX |    20 |       |     1   (0)|

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

Predicate Information (identified by operation id):

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

   1 - filter("E"."DEPARTMENT_ID" IS NOT NULL) --> FILER 정보가 출력됨

   2 - access("E"."JOB_ID"='SH_CLERK')         --> INDEX SCAN 정보 혹은 JOIN 정보가 출력됨

FILTER와 ACCESS 정보는 중요하다

위에서 출력된 Predicate Information을 보면 FILTER 와 INDEX SCAN 정보를 정확히 볼 수 있다. 특히 인덱스가 여러 개의 컬럼으로 구성된 경우 몇 번째 컬럼까지 액세스 되었는지 보려면 Predicate Information이 필수적인 것이다. 예를 들어 인덱스가 COL1 + COL2 + COL3로 되어 있는데 Predicate Information에서 INDEX를 SCAN에 사용된 컬럼이 하나뿐이고 COL2와 COL3는 테이블의 FILTER로 풀린다면 성능에 문제가 될 수 있다. 따라서 Predicate Information을 확인 하는 것은 매우 중요한 것이다.

문제는 이처럼 중요한 정보를 DBMS_XPLAN 패키지를 사용하지 않고 'TOAD나 오렌지에서 어떻게 볼수 있냐' 이다.

지금부터 따라 해보기 바란다.

1. TOAD에서 Predicate Information 보기

먼저 토드 화면에서 EXPLAIN PLAN을 실행한다. EXPLAIN PLAN은 구급차 아이콘을 누르면 된다.

그러면 위와 같은 화면이 출력될 것이다. 위의 화면에서는 Predicate Information가 없다. 지금부터 Predicate Information을 추가해보자.

먼저 TOAD 화면의 하단(Explain Plan) 탭에서 오른쪽 버튼을 클릭한다. 연이어 Adjust Content를 선택한다.

그러면 위와 같은 화면이 뜨는데 여기서 Access Predicates와 Filter Predicates의 Visible 항목을 체크하고 OK를 클릭한다.

위와 같이 TOAD에서 Access Predicates와 Filter Predicates가 깔끔하게 출력되었다.

2. 오렌지에서 Predicate Information 보기

을 볼 수 있다.

확인하는 습관이 필요해
이로써 어디서든 무엇을 사용하든 버튼 클릭 만으로 Predicate Information을 볼 수 있게 되었다. 이제부터 Predicate Information을 애용하기 바란다. 특히 Index Scan 시에 몇번째 컬럼까지 이용하였는지 확인하는 습관이 필요하다.

PS :
필자는 TOAD나 오렌지의 제조사나 판매사와는 상관없는 사람이다. 단지 가끔 이용할 뿐...

No. 17860

(V9I) ORACLE 9I: FAST_START_MTTR_TARGET

=======================================

PURPOSE
-------

Oracle 9i(9.0.1)에서 New Feature인 Fast-Start Time-Based  Recovery
에 대해 알아보도록 한다.

SCOPE
-----
9i~10g Standard Edition에서는 지원하지 않는다.

Explanation

-----------

8I에서는(Note.12240)  new feature로 Bounded recovery 를 위해
FAST_START_IO_TARGET PARAMETER 를 제공하고
Instance Recovery 시간을 최소화 하는데 이용되었다.

참조 Note.12240) FAST_START_IO_TARGET

   Oracle 8i부터 제공되는 FAST_START_IO_TARGET이라는 파라미터는
   instance recovery 시에 읽어야 할 datafile block의 maximum 갯수를 제한
   함으로써 instance recovery를 수행하는 데 드는 시간을 제한할 수 있다.
   이 parameter는 dynamic하고, redo log file 내의 target RBA(Redo Block
   Address)를 indicate하는 데 영향을 주게 되는데, 몇 가지 다른 parameter
   와 함께 recovery 시에 읽어야 할 target RBA를 결정짓는 후보가 된다.

그러나 8I 에서 제공하는 FAST_START_IO_TARGET 사용은
Instance Recovery 를  위해 읽어야할 최소한의 redo block 수를 결정하는데
다음과 같은 고려사항이 있었다.
즉, checkpoint target position을 위한 고려사항으로
FAST_START_IO_TARGET와 LOG_CHECKPOINT_INTERVAL 설정하는데
어느정도가 적합한지에 대한 결정의 어려움이었다.
  
여기서 Oracle 8i에서 bounded time recovery를 가능하게 해주는 몇 가지 요인들
에 대해 알아보도록하자.

   (1) Target based on FAST_START_IO_TARGET

   initSID.ora 파라미터 화일에 다음과 같이 지정하면

   FAST_START_IO_TARGET = 10000

   This allows a DBA to specify the maximum number of datafile blocks
   that should be processed during instance recovery.
   This is used in conjunction with other parameters to determine
   target for checkpointing.

   instance recovery하는 동안에 읽어들어야 할 block의 수를 제한해 주며,
   따라서, cache 내에 존재하는 dirty buffer의 갯수를 제한할 수 있다.
   Crash난 시점에 dirty buffer의 갯수가 instance recovery를 수행하는 데
   소요되는 시간에 영향을 주므로, dirty buffer의 갯수가 중요하다는 것
   이다.
 
   Oracle 8.0부터 소개된 DB_BLOCK_MAX_DIRTY_TARGET 이라는 파라미터도
   cache 내에 존재하는 dirty buffer의 갯수를 제한하는 역할을 하는데,
   Oracle 8i에서는 DB_BLOCK_MAX_DIRTY_TARGET 에 지정한 값보다
   FAST_START_IO_TARGET에 지정한 값이 우선이다.

   (2) 90% of size of smallest redo log

   The incremental checkpoint should not lag the tail of the log more
   than 90% of the size of the smallest log file.

   가장 작은 redo log file size의 90% 이상을 incremental checkpoint가
   lag해서는 안 된다는 의미이다. 이것은 다시 말해서, instance recovery
   시 process할 redo block의 수가 1개의 redo log file 이상이면 안 된다
   는 것을 의미하기도 한다.

   (3) LOG_CHECKPOINT_INTERVAL blocks from the end

   The incremental checkpoint should not lag the tail of the log by more
   than N blocks of redo.

   LOG_CHECKPOINT_INTERVAL에 지정한 block의 갯수보다 더 많은 redo block
   을 instance recovery 시에 읽지 않도록 해야 한다.
   즉, 이 파라미터에 지정한 숫자보다 더 많은 갯수의 dirty block이
   memory 내에 있으면 안 된다는 것을 의미한다.

   (4) End of the log LOG_CHECKPOINT_TIMEOUT seconds ago

   The incremental checkpoint should not lag the tail of the log in
   time by more than LOG_CHECKPOINT_TIMEOUT seconds.

   LOG_CHECKPOINT_TIMEOUT 에 지정한 시간(초) 동안 만큼 발생한 redo
   block 갯수 이상을 instance recovery 시에 읽어서는 안 된다는 것을 의미
   한다.

9I에서는 이러한 문제점을 고려하여 설정해야하는 파라미터의 문제점을
자동화 함으로써  Fast-Start Time-Based RECOVERY 가 가능하게 되었다.

FAST_START_MTTR_TARGET 파라미터는 단순히 원하는 Instance recovery 시간을
지정함으로써 기존에 설정해야하는 다음 Parameter 를 지정하지 않아도
Oracle 에서 자동으로 해당 값을 지정하여 Instance recovery 시간을 최소화 한다.

LOG_CHECKPOINT_INTERVAL
FAST_START_IO_TARGET 

FAST_START_MTTR_TARGET 파라미터는 dynamic Parameter 로
다음과 같이 설정 가능하며 단위는 초단위 이다.

ALTER SYSTEM SET PAST_START_MTTR_TARGET = 60;

즉, 위와 같이 60초로 설정하게 되면 오라클 내부적으로 LOG_CHECKPOINT_INTERVAL 와
FAST_START_IO_TARGET 파라미터의 적정치가 해당 시간내에 이루어지기 위해 결정되고
Recovery 시 읽어야할 Redo log block의 갯수가 결정된다.

Recovery할 block 갯수에 대한 정보는 V$INSTANCE_RECOVERY 중
새롭게 추가된 Column인 TARGET_MTTR/ESTIMATED_MTTR/CKPT_BLOCK_WRITES
에서 알 수 있다.

Example
-------
none

Reference Document
------------------
Note. 151062.1
Note. 159891.1