꿈꾸는 개발자, DBA 커뮤니티 구루비

테이블과 인덱스에 설정된 사용 가능 목록이 정확히 얼마나 되는지를 알 수 있을까?
얼마나 많은 사용 가능 목록 그룹이 있어야 최적의 성능을 발휘할 수 있을까?
정확히 어떤 값의 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

세그먼트 공간 관리를 오라클에 맡겨라 (by kkabong) [2009.04.30]

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

PCTFREE, PCTUSED

1. PCTFREE

2. PCTUSED

FreeList 에 대한 성능 체크

결론

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

결론

ASSM 의 경우