이펙티브 오라클 (2008년)
목록
세그먼트 공간 관리를 오라클에 맡겨라 0 0 1,950

by 구루비스터디 PCTFREE PCTUSED FreeList [2009.04.30]

  • 테이블과 인덱스에 설정된 사용 가능 목록이 정확히 얼마나 되는지를 알 수 있을까?
  • 얼마나 많은 사용 가능 목록 그룹이 있어야 최적의 성능을 발휘할 수 있을까?
  • 정확히 어떤 값의 PCTUSED 가 세그먼트에 설정되어야 하는가?
  • 이들 중에 하나에라도 예라고 답할 수 없는 경우에는 ASSM(Automatic Segment Space Management)을 사용하여야 한다.

사용가능목록과 사요가능목록 그룹에 대한 이해


PCTFREE, PCTUSED

1. PCTFREE
  • 1) 수정시 늘어나는 데이터를 수용하기 위한 공간
  • 2) 데이터의 수정이 빈번한 경우에는 default값(10) 이상으로 설정합니다.
  • 3) default = 10(%)


2. PCTUSED
  • 1) 재사용되기위해 필요한 블럭의 사용량을 설정 합니다.
  • 2) default = 60(%)
  • 3) 삭제가 거의 발생하지 않는 경우에는 90정도로 큰 값을 설정하고,수정작업이 자주 발생하면서 Row size 가 증가할 때에는 40 정도로 낮은 값을 설정해야 합니다.


FreeList 에 대한 성능 체크


* Paralle 수행을 위한 환경 체크
sys@ARTDOM>show parameters job_queue_processes

NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM
\-----------------------------\- \----------\- \-----------------------------\-
job_queue_processes            integer     10


* Tablespace 생성
sys@ARTDOM>create tablespace manual ;
Tablespace created.


* Table 생성 \[ Freelists 1 로 생성 \]
sys@ARTDOM>create table t ( x date, y char(255) default 'x' )
2  storage ( freelists &1 )
3  tablespace manual ;
Enter value for 1: 1
old   2: storage ( freelists &1 )
new   2: storage ( freelists 1 )

Table created.
* Parallel Insert Procedure 작성 \[ do_insert \]
sys@ARTDOM>create or replace procedure do_insert
  as
  l_stop date default sysdate+5/24/60 ;
  l_date date default sysdate;
  begin
  while ( l_date < l_stop )
   loop
   insert into t ( x )
   values ( sysdate )
   returning x into l_date ;
   commit ;
  end loop ;
  end ;
  /

Procedure created.

* Table Count Check
sys@ARTDOM>select count( * ) from t ;
0

* 성능 Snap 작성
perfstat@ARTDOM>exec perfstat.statspack.snap


* PCT_FREE, PCT_USED, FREELISTS 체크
select TABLE_NAME,TABLESPACE_NAME,PCT_FREE,PCT_USED,FREELISTS
from dba_tables where table_name ='T';
T	MANUAL	10	40	1


* Insert 작업 수행
sys@ARTDOM>declare
  l_job number ;
  begin
  for i in 1..5
  loop
  dbms_job.submit(l_job, 'do_insert;');
  end loop ;
  commit ;
  end ;
  /

PL/SQL procedure successfully completed.

sys@ARTDOM>host sleep 400

* 성능 Snap 작성
sys@ARTDOM>exec perfstat.statspack.snap ;
PL/SQL procedure successfully completed.

* 성능 Report 작성
sys@ARTDOM>@/app/oracle/product/10.1.0/rdbms/admin/spreport.sql








* FREELISTS 5 Table 생성 후 테스트 진행 
sys@ARTDOM>drop table t ;

Table dropped.

sys@ARTDOM>create table t ( x date, y char(255) default 'x')
  storage ( freelists &1 )
  tablespace manual ;

Enter value for 1: 5

Table created.

* 성능 Snap 작성
sys@ARTDOM>exec perfstat.statspack.snap ;
PL/SQL procedure successfully completed.


sys@ARTDOM>declare
  l_job number ;
  begin
  for i in 1..5
  loop
  dbms_job.submit(l_job, 'do_insert;');
  end loop ;
  commit ;
  end ;
  /

PL/SQL procedure successfully completed.

* 성능 Snap 작성
sys@ARTDOM>host sleep 400

sys@ARTDOM>exec perfstat.statspack.snap ;
PL/SQL procedure successfully completed.


* 성능 Report 작성
sys@ARTDOM>@/app/oracle/product/10.1.0/rdbms/admin/spreport.sql


결론
  • buffer busy waits 에 대한 Wait Event 가 326,811 ==> 49,673 으로 감소 하였고, Transaction 수는 오히려 줄었다.
  • 4,631.29 ==> 3,312.91 감소
  • 책 내용대로라면 buffer busy waits 가 줄면서 Transaction 이 증가하여야 한다.
  • 이 부분은 테스트 검증 필요함


다중 사용 가능 목록 그룹(FreeList Group)

  • 일반적으로 RAC(Real Application Cluster) 또는 OPS(Oracle Parallel Server)환경에서 인스턴스 사이의 경합을 관리하고 회피하기 위하여 각 인스턴스에 독자적인 사용
  • 가능 목록 집합을 제공하려고 사용된다.

* FREELIST GROUP 에 대한 테스트 
* FREELIST GROUPS 을 1로 생성
sys@ARTDOM>create table t ( x date, y char(255) default 'x')
2  storage ( freelists 5 FREELIST GROUPS 1 )
3  tablespace manual ;

Table created.

sys@ARTDOM>exec perfstat.statspack.snap

PL/SQL procedure successfully completed.

sys@ARTDOM>declare
2  l_job number ;
3  begin
4  for i in 1..10
5  loop dbms_job.submit ( l_job, 'do_insert;');
6  end loop ;
7  commit ;
8  end ;
9  /

PL/SQL procedure successfully completed.

sys@ARTDOM>host sleep 400

sys@ARTDOM>exec perfstat.statspack.snap ;

PL/SQL procedure successfully completed.

sys@ARTDOM>@/app/oracle/product/10.1.0/rdbms/admin/spreport.sql







* FREELIST GROUPS 을 5로 생성

sys@ARTDOM>drop table t ;

Table dropped.

sys@ARTDOM>create table t ( x date, y char(255) default 'x')
2  storage ( freelists 5 FREELIST GROUPS 5 )
3  tablespace manual ;

Table created.



sys@ARTDOM>exec perfstat.statspack.snap

PL/SQL procedure successfully completed.

sys@ARTDOM>declare
  l_job number ;
  begin
  for i in 1..10
  loop dbms_job.submit ( l_job, 'do_insert;');
  end loop ;
  commit ;
  end ;
  /
PL/SQL procedure successfully completed.

sys@ARTDOM>host sleep 400

sys@ARTDOM>exec perfstat.statspack.snap

PL/SQL procedure successfully completed.

sys@ARTDOM>@/app/oracle/product/10.1.0/rdbms/admin/spreport.sql
결론
  • Freelist Group 변경에 대해서는 그렇게 효과가 크기 않은 듯 싶다.
  • 해당 파라미터 자체가 RAC/OPS 에 Focus 가 있는 파라미터이기 때문인듯 싶다.
  • 그러나 전체적으로 buffer busy wait 을 약간 줄고, Transaction 을 늘었다.
  • buffer busy waits : 100.948 ==> 98.305
  • Transaction : 6,164.85 ==> 7.258.51


ASSM 의 경우

  • ASSM을 사용하면 사용 가능 목록(Freelist), 사용 가능 목록 그룹(Freelist Group), 그리고, PCTUSED 설정을 튜닝할 수 있다.

* SEGMENT SPACE MANAGEMENT AUTO VS SEGMENT SPACE MANAGEMENT MANUAL  
sys@ARTDOM>create tablespace auto_tbs datafile
  size 64M autoextend on next 8m extent management local autoallocate
  segment space management auto ;
Tablespace created.

sys@ARTDOM>create tablespace manual_tbs datafile
  size 64m autoextend on next 8m extent management local autoallocate
  segment space management manual ;
Tablespace created.

* SEGMENT SPACE MANAGEMENT AUTO TBS 에 TABLE 생성 후 INSERT 진행 
sys@ARTDOM>create table t ( x date, y char(255) default 'x')
  tablespace auto_tbs ;
Table created.

sys@ARTDOM>exec perfstat.statspack.snap ;
PL/SQL procedure successfully completed.

sys@ARTDOM>declare
  l_job number ;
  begin for i in 1..10
  loop dbms_job.submit(l_job,'do_insert;');
  end loop ;
  commit ;
  end ;
  /

PL/SQL procedure successfully completed.

sys@ARTDOM>host sleep 400

sys@ARTDOM>exec perfstat.statspack.snap ;

PL/SQL procedure successfully completed.

sys@ARTDOM> @/app/oracle/product/10.1.0/rdbms/admin/spreport.sql







* SEGMENT SPACE MANAGEMENT MANUAL TBS 에 TABLE 생성 후 INSERT 진행 
sys@ARTDOM>create table t ( x date, y char(255) default 'x')
  tablespace auto_tbs ;
Table created.

sys@ARTDOM>exec perfstat.statspack.snap ;
PL/SQL procedure successfully completed.

sys@ARTDOM>declare
  l_job number ;
  begin for i in 1..10
  loop dbms_job.submit(l_job,'do_insert;');
  end loop ;
  commit ;
  end ;
  /

PL/SQL procedure successfully completed.

sys@ARTDOM>host sleep 400

sys@ARTDOM>exec perfstat.statspack.snap ;

PL/SQL procedure successfully completed.

sys@ARTDOM> @/app/oracle/product/10.1.0/rdbms/admin/spreport.sql



이 결과에서 가장 흥미로운 이벤트는 버퍼 사용중 대기이다. 이와 같이 삽입이
비번하게 발생하는 경우에는 사용 가능 목록 경합에 의해 대기가 발생한다.
ASSM을 사용하면 이와 같이 대기가 획기적으로 줄어든다.
                 
Buffer busy wait : 820,283  ==>  18,614
Transaction      : 4,638.16 ==> 6,625.10
"구루비 데이터베이스 스터디모임" 에서 2008년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3544

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입