[10/18] OSS Avoiding rowchaining migration

Oracle Database 11gR2

Performance Tuning Cookbook

 

Ciro Fiorillo PACKT PUBLISHING enterprise

 

Optimizing Storage Structures

 

Oracle Database에서 사용할 수 있는 몇 가지를 보게 될 것인데 먼저 테이블을 저장할 때 발생하는 병목 현상을 피하는 유용한 방법을 볼 것이다

 

그 다음은 인덱스 구성 테이블 그리고 파티셔닝 옵션에 대한 방법을 볼 수 있다 insert, update 그리고 delete 작업에 의한 문제점이 발생할 수 있으나 우리의 쿼리는 올바른 인덱스를 사용한다면 어플리케이션의 성능을 대폭 향상시킬 수 있다.

 

언제나 성능 튜닝은 데이터베이스에 실행되는 다양한 작업들에 평균적인 속도를 최대한 유지하는 것을 필요로 하며 다양한 요구에 대한 균형을 유지해야 한다.

 

9i부터 테이블스페이스마다 block size를 다르게 지정할 수 있다. OLTP 환경에서는 block size가 작은게 유리하고 DW OLAP(대량의 데이터 FULL SCAN일때는 block size가 큰게 좋다.

 

Avoiding row Chaining

 

SYS@orcl>alter system set db_16k_cache_size = 16m scope=both;   System altered.   SYS@orcl>show parameter cache_size   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_size             big integer 0 db_16k_cache_size                    big integer 16M db_2k_cache_size                     big integer 0 db_32k_cache_size                    big integer 0 db_4k_cache_size                     big integer 0 db_8k_cache_size                     big integer 0 db_cache_size                        big integer 0 db_flash_cache_size                  big integer 0 db_keep_cache_size                   big integer 0 db_recycle_cache_size                big integer 0

 

Row chain을 감지하는 방법과 방지하는 방법을 알아볼것이다.

 

HR@orcl>create table hr.big_rows (   2  id number not null,   3  field1 char(2000) default 'A' not null,   4  field2 char(2000) default 'B' not null,   5  field3 char(2000) default 'C' not null,   6  field4 char(2000) default 'D' not null,   7  field5 char(2000) default 'E' not null,   8  constraint PK_BIG_ROWS primary key (ID))   9  tablespace example;   Table created.   HR@orcl>insert into hr.big_rows (id)   2  select rownum from all_objects where rownum < 101;   100 rows created.   HR@orcl>analyze table hr.big_rows compute statistics;   Table analyzed.     HR@orcl>select chain_cnt from all_tables   2  where owner = 'HR' and table_name = 'BIG_ROWS';    CHAIN_CNT ----------        100

 

 

 

다른 방법

SYS@orcl>create tablespace ts_16k blocksize 16k   2  datafile '/home/oracle/ts_16k.dbf' size 10m   3  extent management local uniform size 1m;   Tablespace created.   SYS@orcl>alter table hr.big_rows move tablespace ts_16k;   Table altered.   SYS@orcl>alter index hr.pk_big_rows rebuild;   Index altered.   SYS@orcl>analyze table hr.big_rows compute statistics;   Table analyzed.   SYS@orcl>select chain_cnt from all_tables   2  where owner = 'HR' and table_name = 'BIG_ROWS';    CHAIN_CNT ----------          0   SYS@orcl>drop tablespace ts_16k including contents and datafiles;   Tablespace dropped.

 

How it works

 

우리는 DB block size 보다 큰 row 길이의 big_rows 테이블을 만들었다

분석 결과 100개의 rows를 담고 있는걸 알 수 있고 이는 DB block에 맞지 않는 사이즈다 그래서 DB엔진은 첫번째 부분의 rows를 블록 저장하고 다른 블록에 저장한다

 

이 row chain을 피하려면 우리는 더 큰 db_block_size 를 가진 테이블스페이스의 테이블에 move해야 하고 우리는 ts_16k라는 block size 16k의 테이블스페이스를 만들었다

 

우리는 big_rows 테이블을 옮겼고 새롭게 테이블 스페이스를 만들고 옮긴 후 index를 재구성했다 우리는 테이블 통계에 대해 다시 분석했다 이동 후 rows chain은 big_rows에서 사라졌다

 

There’s more…

 

테이블을 이동한 후 인덱스를 다시 작성해야 한다 인덱스에는 테이블의 rowid가 포함되어 있는데 이동하면 그것이 변경되기에 행의 위치를 파악하기 위해서는 다시 작성이 필요하다

 

Block size가 큰 경우의 장점은 무엇일까? 한번의 I/O에 많은 데이터를 읽어 올 수 있고 data buffer cache의 hit가 일어날 확률이 올라간다(하나의 블록에 많은 양의 데이터가 포함되어 있어서)

 

단점으론 블록에 대한 경합 발생 확률이 올라가는데 이는 동시 트랜잭션이 많이 발생하는 테이블의 경우 block header의 MAXTRANS를 초과하거나 블록 내의 여유 공간이 부족할 경우와 ITL 슬롯의 할당이 불가능할 경우 allocate ITL entry 이벤트를 대기하게 되니까

 

작을 경우의 장점은 경합 발생 비율이 감소하나 단점으론 cache hit률 저하 I/O 증가가 있다

 

Avoiding row migration

SYS@orcl>create table hr.big_rows (   2  id number not null,   3  field1 char(2000) default 'A' not null,   4  field2 char(2000),   5  field3 char(2000),   6  field4 char(1000),   7  constraint pk_big_rows primary key (id)) 8  tablespace example pctfree 10;   Table created.   SYS@orcl>insert into hr.big_rows (id) 2  select rownum from all_objects where rownum < 101;   100 rows created.   SYS@orcl>analyze table hr.big_rows compute statistics;   Table analyzed.   SYS@orcl>select chain_cnt from all_tables 2  where owner = 'HR' and table_name = 'BIG_ROWS';    CHAIN_CNT ----------          0 SYS@orcl>update hr.big_rows set field2 = 'B', field3 = 'C', field4 = 'D' 2  where mod(id,2) = 1;   50 rows updated.   SYS@orcl>analyze table hr.big_rows compute statistics;   Table analyzed.   SYS@orcl>select chain_cnt from all_tables   2  where owner = 'HR' and table_name = 'BIG_ROWS';    CHAIN_CNT ----------         50 SYS@orcl>create table hr.chained_rows (   2  owner_name         varchar2(30),   3  table_name         varchar2(30),   4  cluster_name       varchar2(30),   5  partition_name     varchar2(30),   6  subpartition_name  varchar2(30),   7  head_rowid         rowid,   8  analyze_timestamp  date   9  );   Table created.   SYS@orcl>analyze table hr.big_rows list chained rows   2  into hr.chained_rows;   Table analyzed.   SYS@orcl>select count(*) from hr.chained_rows;     COUNT(*) ----------         50

 

 

SYS@orcl>create global temporary table hr.temp_big_rows as select * from hr.big_rows where 1=0;   Table created.   SYS@orcl>insert into hr.temp_big_rows   2  select b.* from hr.big_rows b, hr.chained_rows t   3  where t.owner_name = 'HR' and t.table_name = 'BIG_ROWS'   4* and t.head_rowid = b.rowid 50 rows created.   SYS@orcl>delete from hr.big_rows b where exists (   2  select t.rowid from hr.chained_rows t   3  where t.owner_name = 'HR' and t.table_name = 'BIG_ROWS'   4  and t.head_rowid = b.rowid);   50 rows deleted.   SYS@orcl>insert into hr.big_rows select * from hr.temp_big_rows; 50 rows created.   SYS@orcl>analyze table hr.big_rows compute statistics; Table analyzed.   SYS@orcl>select chain_cnt from all_tables where owner = 'HR' and table_name = 'BIG_ROWS';    CHAIN_CNT ----------          0 SYS@orcl>drop table hr.temp_big_rows;   Table dropped.   SYS@orcl>drop table hr.chained_rows;   Table dropped.   SYS@orcl>drop table hr.big_rows; Table dropped.

How It works…

 

우리는 testdb의 example TBS의 db_block 8k보다 더 작은 row 길이를 가진 big_rows 테이블을 만들었다 100개의 rows를 가진 테이블이고 분석해보면 다른 chain / migrated row가 없는걸 알 수 있다. Row가 update되서 migrated될 경우 기존 db block에 포함되지 않는걸 알 수 있고 따라서 database 엔진은 새로운 주소의 row 위치를 기존 row에 저장한다.

 

유효하지 않은 인덱스 사용을 피할수 있는데 그건 기존 rowid가 주소값을 가지고 있기 때문이다

Row migration을 피하기 위해 PCTFREE 파라미터 값을 높게 설정할 수 있는데 예를 들어 테이블의 PCTFREE 값이 10이면 update를 위해 사용될 공간이 10%라는 것이다

 

Row migrated 문제를 해결하기 위해 우리는 migrated row를 temporary table에 저장하고 기존 위치를 지웠다 FK 제약조건 그리고 트리거는 비활성화 했고 그 규칙을 위반하는 것을 피했다

 

There’s more

 

Row migration은 row chain과 유사하게 데이터베이스 엔진의 하나의 블록을 읽기 위해 더 많은 블록을 엑세스 하기 때문에 성능저하를 야기한다

 

Row chaining과 row migration의 가장 큰 차이는 row chaining은 작은 db block size로 설계된 rows일 경우고 row migration은 매우 작은 PCTFREE 파라미터 값을 가진 테이블일 경우다

 

Row migration을 해결하는 두 가지 방법이 있는데 alter table move을 하거나 혹은 EXP/IMP를 통해 해결이 가능하다 종종 PCTFREE 값을 매우 낮게 설정해서 공간 낭비를 피하는 방법을 쓰지만 이것은 좋은 방법이 아니다 예를 들어 insert-only 테이블의 경우에는 PCTFREE 0 parameter값을 0으로 사용할 수 있다 PCTFREE 파라미터에 다른 값을 사용하여 테이블의 크기를 예측하는 방법에 사용되는 DBMS_SPACE 패키지 CREATE_TABLE_COST를 사용할 수 있다.

 

 

SET SERVEROUTPUT ON declare   l_used_bytes number;   l_alloc_bytes number; begin   dbms_space.create_table_cost (     tablespace_name => 'EXAMPLE',     avg_row_size => 4500,     row_count => 100,     pct_free => 10,     used_bytes => l_used_bytes,     alloc_bytes => l_alloc_bytes   );   dbms_output.put_line('Used Bytes: '||l_used_bytes);   dbms_output.put_line('Allocated Bytes: '||l_alloc_bytes); end;  16  / Used Bytes: 819200 Allocated Bytes: 851968   PL/SQL procedure successfully completed.

 

USING LOBs

 

일반적인 data type으로는 담을 수 없는 대량의 데이터를 저장하기 위해 고안된 타입이며 기존 기술인 LONG과 LONG RAW를 대체하는 역할을 한다 하나의 row에 여러 개의 LOB들을 저장할 수 있고 BFILE은 외부에 파일로 저장된다

 

테이블이나 외부파일 임시저장소 등에 LOB를 저장할 영역을 할당해 놓고 해당 영역을 가리키도록 구성되어 있는데 이렇게 가리키는 녀석을 LOB Locator라고 부르고 실제 저장된 LOB 값을 LOB Value라고 부른다 일반 변수타입과는 달리 타입 하나에 2가지 객체가 포함되어 있다

 

LOB는 실제 타입은 아니고 여러 타입을 묶어서 통칭하는 분류어다

 

LOB value가 저장되는 위치에 따라서 각기 다른 특성을 가지는데

 

1. 외부 ( External LOB )

 - 현재로선 파일(BFILE)이 유일한 External LOB Type다

 - 값이 DB 외부에 저장되며 Read-Only다

 - 복사시 Lob locator만 복사되고 테이블에 insert 해도 실제 값이 복사되지 않고 locator만 복사

 - Lob locator는 여러 곳에 복사될 수 있다.

 

2. 내부 ( Internal LOB )

 2-1. 테이블( Persistent ) – 값이 테이블에 저장된다

 2-2. 임시저장소(Temporary) – 값이 변수에 저장된다.

- Read – Write 둘 다 가능

- Lob locator와 Lob Value가 같이 붙어다니며 복제시 인스턴스가 새로 생성됨

 즉 같은 대상을 여러 개의 LOB 변수가 가리키지 않으며 수정해도 원본 변경 안됨

- 함수 인자에 NOCOPY 옵션을 줘서 복사에 수행되는 부담을 덜 수 있음

 

LOB 타입 기준 분류

 

BFILE – External LOB를 불러올 때 사용하는 타입 읽기 전용 타입 Lob locator만 복사됨

BLOB – Binary data 저장용 Raw type과 쉽게 호환되나 CLOB와의 상호변환은 character set

문제를 감안해야 한다

CLOB – Text data 저장용

NCLOB – CLOB의 유니코드 지원버전입 national character set과 관계없이 UCS-2 호환형식 저장

 

LOB 변수 생성 및 값 대입

 

1. 외부 LOB( BFILE )

 - 외부 파일을 읽어와서 locator를 지정하는 방식으로 BFILENAME 함수를 이용해서 locator를 지정할 수 있다 단 파일명과 디렉터리를 지정해 줘야 하며 create directory로 directory가 미리 지정되어 있어야 한다

 

2. 내부 LOB

 2-1 테이블(Persistent)

- Select나 DML 문을 이용해서 locator를 지정할 수 있다

 2-2 임시(Temporary)

- DBMS_LOB.CREATETEMPORARY(…)를 이용해서 locator를 지정할 수 있다

- 또는 다른 LOB를 복사해서 생성하는 경우도 있다

- 둘 중 한가지가 아니라면 invalid locator error 발생

 

성능 관련 이슈 – OUT NOCOPY

 

큰 사이즈의 데이터를 다루다보니 데이터가 이동할 때 부담이 크다 함수나 프로시저 호출시 OUT 파라미터(IN OUT 포함)에 NOCOPY 옵션을 주면 데이터를 복사하지 않고 원본을 바로 접근할 수 있게 한다(참조 전달) NOCOPY 옵션을 주지 않으면 값을 통째로 복사해서 넘기게 된다(값 전달)

 

이 둘의 차이는 큰 파일을 복사하는 것과 바로가기를 생성하는 것의 차이다 대신 NOCOPY는 변경값이 바로바로 적용되기 때문에 side-effect가 발생할 가능성이 높다 (함수의 두 인자에 동일 변수를 넣거나 하면 두 값이 동시에 바뀐다 IN 파라미터는 NOCOPY 옵션을 주지 않아도 기본으로 NOCOPY로 동작한다

 

 

 

 

SYS@orcl>grant create any directory to sh;   Grant succeeded.   SYS@orcl>create tablespace assm_ts   2  datafile '/home/oracle/assm_ts.dbf' size 100m   3  extent management local   4  segment space management auto;   Tablespace created.   SH@orcl>create table mycustomers as select * from customers;   Table created.   SH@orcl>alter table mycustomers add (c_file blob)   2  lob(c_file) store as securefile (   3  tablespace assm_ts   4  enable storage in row   5  nocache logging   6  );   Table altered.   SH@orcl>create directory testblob as '/u01/app/oracle/product/11.2.0/db_1/rdbms/admin';   Directory created.   1  declare   2    l_file bfile;   3    l_blob blob;   4    l_size number;   5  begin   6    l_file := bfilename('TESTBLOB', 'catalog.sql');   7    dbms_lob.fileopen(l_file);   8    l_size := dbms_lob.getlength(l_file);   9    for J in 1 .. 100 loop  10      update MyCustomers SET c_file = empty_blob() where CUST_ID = J  11  returning c_file into l_blob;  12      dbms_lob.loadfromfile(l_blob, l_file, l_size);  13    end loop;  14    commit;  15    dbms_lob.close(l_file);  16* end; SH@orcl>/   PL/SQL procedure successfully completed.   1  declare   2    l_segment_name VARCHAR2(30);   3    l_segment_size_blocks NUMBER;   4    l_segment_size_bytes NUMBER;   5    l_used_blocks NUMBER;   6    l_used_bytes NUMBER;   7    l_expired_blocks NUMBER;   8    l_expired_bytes NUMBER;   9    l_unexpired_blocks NUMBER;  10    l_unexpired_bytes NUMBER;  11  begin  12    select segment_name into l_segment_name from user_lobs where  13  table_name = 'MYCUSTOMERS';  14    DBMS_OUTPUT.put_line('segment name: ' || l_segment_name);  15    DBMS_SPACE.SPACE_USAGE(  16     segment_owner       => USER,  17     segment_name        => l_segment_name,  18     segment_type        => 'LOB',  19     segment_size_blocks => l_segment_size_blocks,  20     segment_size_bytes  => l_segment_size_bytes,  21     used_blocks         => l_used_blocks,  22     used_bytes          => l_used_bytes,  23     expired_blocks      => l_expired_blocks,  24     expired_bytes       => l_expired_bytes,  25     unexpired_blocks    => l_unexpired_blocks,  26     unexpired_bytes     => l_unexpired_bytes);  27     DBMS_OUTPUT.put_line('used_blocks ' || l_used_blocks);  28     DBMS_OUTPUT.put_line('used_bytes ' || l_used_bytes);  29* end; SH@orcl>set serveroutput on SH@orcl>/ segment name: SYS_LOB0000075112C00024$$ used_blocks 800 used_bytes 6553600   PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.     SH@orcl>alter table mycustomers modify lob (c_file) (deduplicate);   Table altered.   Alert Log Sun Oct 18 19:03:57 2015 Thread 1 advanced to log sequence 12 (LGWR switch)   Current log# 3 seq# 12 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log   Current log# 3 seq# 12 mem# 1: /u01/app/oracle/oradata/orcl/redo03_b.log Sun Oct 18 19:03:59 2015 Archived Log entry 128 added for thread 1 sequence 11 ID 0x54b08c25 dest 1: Thread 1 advanced to log sequence 13 (LGWR switch)   Current log# 1 seq# 13 mem# 0: /u01/app/oracle/oradata/orcl/redo01.log   Current log# 1 seq# 13 mem# 1: /u01/app/oracle/oradata/orcl/redo01_b.log Sun Oct 18 19:04:01 2015 Archived Log entry 129 added for thread 1 sequence 12 ID 0x54b08c25 dest 1:   DBMS_OUTPUT.put_line('used_blocks ' || l_used_blocks);    DBMS_OUTPUT.put_line('used_bytes ' || l_used_bytes);  29  end;  30  / segment name: SYS_LOB0000075112C00024$$ used_blocks 8 used_bytes 65536   PL/SQL procedure successfully completed.   SH@orcl>alter table mycustomers modify lob (c_file) (compress high);   Table altered.   Alert log Sun Oct 18 19:05:43 2015 Thread 1 cannot allocate new log, sequence 14 Checkpoint not complete   Current log# 1 seq# 13 mem# 0: /u01/app/oracle/oradata/orcl/redo01.log   Current log# 1 seq# 13 mem# 1: /u01/app/oracle/oradata/orcl/redo01_b.log Thread 1 advanced to log sequence 14 (LGWR switch)   Current log# 2 seq# 14 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log   Current log# 2 seq# 14 mem# 1: /u01/app/oracle/oradata/orcl/redo02_b.log Sun Oct 18 19:05:45 2015 Archived Log entry 130 added for thread 1 sequence 13 ID 0x54b08c25 dest 1: Thread 1 cannot allocate new log, sequence 15 Checkpoint not complete   Current log# 2 seq# 14 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log   Current log# 2 seq# 14 mem# 1: /u01/app/oracle/oradata/orcl/redo02_b.log Thread 1 advanced to log sequence 15 (LGWR switch)   Current log# 3 seq# 15 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log   Current log# 3 seq# 15 mem# 1: /u01/app/oracle/oradata/orcl/redo03_b.log Sun Oct 18 19:05:50 2015 Archived Log entry 131 added for thread 1 sequence 14 ID 0x54b08c25 dest 1: DBMS_OUTPUT.put_line('used_blocks ' || l_used_blocks);    DBMS_OUTPUT.put_line('used_bytes ' || l_used_bytes);  29  end;  30  / segment name: SYS_LOB0000075112C00024$$ used_blocks 3 used_bytes 24576   PL/SQL procedure successfully completed.