ORACLE 9i FRAGMENTATION
fragmentation_sample.zipfrom Oracle
database internals by Riyaj
http://orainternals.wordpress.com/2009/01/15/a-stroll-through-shared-pool-heaps/
1. FRAGMENTATION 의 발생
A.
메모리의 단편화는 하드파싱에 의해
심화되며 특히 LITERAL SQL에 의해 가속된다.
B.
단편화가 심화되면 나타나는 증상으로
파싱시간이 길어지거나 파싱이 실패하는 (ORA - 04031) 에러가 발생할
수 있다.
2. FRAGMENTATION 실습
A.
SHARED_POOL
SIZE를 최소화
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
shared_pool_size big integer 16777216
SQL> select 16777216/1024/1024 mb from
dual;
MB
----------
16
*현재의 shared pool size는 16메가이다.
SQL> alter system set shared_pool_size =
512k scope=both;
System altered.
*shared pool size 를 512k 로 지정하였다.
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
shared_pool_size big integer 16777216
* 변함없이 16메가인 걸 확인하였다.
shared pool 은 granule 이라는 단위로 할당된다. 이 크기는 sga_max_size 의 크기에 좌우되며 오라클 버전에
따라 달라진다.
9i 의 경우 128m 을 기점으로 미만인 경우
4m 가 이상인 경우 16m 이 할당단위이며
10g 의 경우 1024m 을 기점으로 미만인 경우 4m 이상인 경우 16m 이 할당된다.
SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
sga_max_size big integer 252777592
SQL> select 252777592/1024/1024 mb from
dual;
MB
----------
241.067497
*이 플랫폼은 9i 이며 sga 사이즈가 128m 이상이다. 즉
granule 이 16m 이며 이 값이 최소값이다. ( 더
작아질 수 없다. )
B.
FLUSH
SHARED_POOL
SQL> alter system flush shared_pool;
*위의 명령어로 shared pool 을 비웠다.
System altered.
C.
DUMP
SHARED_POOL
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump heapdump 2
Statement processed.
*level 2 는 shared_pool 에 대해 dump를 수행한다.
SQL> oradebug tracefile_name
/oracle/admin/PROD/udump/prod_ora_12527.trc
SQL> !vi
/oracle/admin/PROD/udump/prod_ora_12527.trc
D.
LITERAL
SQL 의 수행
아래의
쿼리를 에러가 떨어질 때까지 ( 혹은 여러 번 ) 수행한다.
SQL> declare
2 tmp number;
3 begin
4 for i in 1..1000000 loop
5 execute immediate
'select ‘||i||’ from dual' into tmp;
6 end loop;
7 end;
8 /
*execute
immediate 는
dynamic sql을 사용하기 위한 구문이고 이는 구문에 대해
항상 하드파싱을 수행하며 이가 의미하는
것은 매번 execution plan 을
생성하겠다는 의미이다.
E.
DUMP
SHARED_POOL
C 와 같은 방법으로 DUMP 를 뜬 후 파일명을 각각 수정하여 비교해보았다.
RH3@PROD : /oracle/admin/PROD/udump>$ls
-la | grep frag
-rw-r-----
1 oracle dba 5570451 Mar 12 19:14 sp_frag_af.trc
-rw-r-----
1 oracle dba 350498 Mar 12 14:43 sp_frag_bf.trc
크기가 급격하게 증가한 걸 확인할 수 있다.
<< sp_frag_bf.trc 의 내용>>
FREE LIST 를 열어 BUCKET 부분을 확인하면 아래와 같다.
*Bucket 이란 단어는 FREE LISTS 항목에만
있다.
FREE LISTS:
Bucket 0 size=16
Bucket 1 size=20
Chunk 5cf527b8 sz= 20 free
" "
Bucket 2 size=24
Bucket 3 size=28
Chunk 5ceb7724 sz= 28 free
" "
Chunk 5cf5574c sz= 28 free
" "
Bucket
4 size=32
Chunk 5cf57b94 sz= 32 free
" "
Chunk 5cf5a108 sz= 32 free
" "
Bucket 5 size=36
Chunk 5cf6710c sz= 36 free
" "
Bucket 6 size=40
Bucket 7 size=44
Chunk 5cf4d398 sz= 44
free " "
Bucket 8 size=48
Bucket 9 size=52
Bucket 10 size=56
Chunk 5cf633d4 sz= 56 free
" "
Bucket 11 size=60
Chunk 5ce64c8c sz= 60 free
" "
Bucket 12 size=64
Bucket 13 size=68
<… 중략
…>
Bucket 253 size=32780
Chunk 5cb420b0 sz= 39920 free
" "
Chunk 5cc2b3a0 sz= 39516 free
" "
Chunk 5cc92e54 sz= 54752 free
" "
Chunk 5cea97e8 sz= 40372 free
" "
Chunk 5ccce284 sz= 33524 free
" "
Chunk 5ce32124 sz= 36388 free
" "
Chunk 5cb0b0b4 sz= 64596 free
" "
Chunk 5cc8a220 sz= 35332 free
" "
Chunk 5ce8b12c sz= 57684 free
" "
Chunk 5cef3704 sz= 47516 free
" "
Chunk 5cd2161c sz= 38076 free
" "
Bucket 254 size=65548
Chunk 5cc65898 sz= 102300 free
" "
Chunk 5cb2c77c sz= 82948 free
" "
Chunk 5c0a4000 sz= 10861900
free " "
Chunk 5cc08bfc sz= 91832 free
" "
Chunk 5cd35e88 sz= 76468 free
" "
Chunk 5cb6a83c sz= 331972 free
" "
Chunk 5cc47168 sz= 116176 free
" "
Chunk 5cdd6c5c sz= 301064 free
" "
Chunk 5cdb674c sz= 79540 free
" "
Chunk 5ccb8b84 sz= 86528 free
" "
*Bucket 항목을 보면 size 값이 각각 정해져있는데 이 값에 대하여 bucket 에 링크되어있는 chunk 들의 크기는 항상크다. 또 이 chunk 들은 상위 넘버의 bucket 사이즈보다 항상작다.
Bucket 은 254까지 있다. /*+ 이 값이 달라지는 경우에 대해 추후 조사 */ Bucket 의 number 가 증가할수록 그에 딸려있는 free chunk 의 크기는 증가한다. 예를 들어 bucket#1 이 1~10 크기의 값이 링크되어있다고 하면
bucket#2 는 11~20 bucket#3은
21~30 식으로 증가한다.
<< sp_frag_af.trc 의 내용>>
FREE LISTS:
Bucket 0 size=16
Bucket 1 size=20
Chunk 5cc83ed4
sz= 20 free
" "
Chunk 5cd9b198
sz= 20 free
" "
Chunk 5bea9294
sz= 20 free
" "
Chunk 5cbece44
sz= 20 free
" "
Chunk 5c40f544
sz= 20 free
" "
Chunk 5cacee04
sz= 20 free
" "
Chunk 5cee56ac
sz= 20 free
" "
Chunk 5c9f9674
sz= 20 free
" "
Chunk 5c62b1e4
sz= 20 free
" "
Chunk 5cc34bcc
sz= 20 free
" "
Chunk 5cc8d338
sz= 20 free
" "
Chunk 5c1538f0 sz= 20
free " "
<… 중략
…>
Bucket 247 size=3884
Bucket 248 size=3948
Bucket 249 size=4012
Bucket 250 size=4108
Bucket 251 size=8204
Bucket 252 size=16396
Bucket 253 size=32780
Bucket 254 size=65548
위의 값을
보면 한가지 중요한 차이점이 있다. literal sql을 수행하기 전의 상태와 달리 위에서는 후반부
번호의 bucket 에 링크된 chunk가 존재하지 않는다. 이 이유는 literal sql 로 인해 알맞은 사이즈의 free chunk 를 소진하고 다음으로
큰 free chunk 를 쪼개서 사용하기 때문이다. 결과적으로
큰 크기의 chunk는 쪼개지고 쪼개져서 최종적으로 남은 작은 크기의
chunk 가 낮은 번호의 bucket 에 추가되게 된다.
이것은 bucket 1 을 서로 비교해 보면 알 수 있다.
F.
FLUSH
SHARED_POOL
shared pool 을 flush 하면 작게 쪼개진 free chunk 들이 병합이 되는지 확인해 보도록 하겠다.
SQL> alter system flush shared_pool;
System altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump heapdump 2
Statement processed.
SQL> oradebug tracefile_name
/oracle/admin/PROD/udump/prod_ora_14738.trc
<< sp_frag_flsh.trc 의 내용 >>
FREE LISTS:
Bucket 0 size=16
Bucket 1 size=20
Chunk 5cf5edd4
sz= 20 free
" "
Chunk 5ce210a8
sz= 20 free
" "
Chunk 5cf4af38
sz= 20 free
" "
Chunk 5cf318fc
sz= 20 free
" "
Chunk 5cebfac4
sz= 20 free
" "
Chunk 5cf527b8
sz= 20 free
" "
Bucket 2 size=24
Bucket 3 size=28
Chunk 5cf5574c
sz= 28 free
" "
Bucket 4 size=32
Chunk 5cf57b94
sz= 32 free
" "
Chunk 5cf5a108 sz= 32
free " "
<< … 중략 …>>
Bucket 254 size=65548
Chunk 5bf2a280 sz= 155768 free
" "
Chunk 5c45a074 sz= 286780 free
" "
Chunk 5c405b00 sz= 74656 free
" "
Chunk 5bed308c sz= 298948 free
" "
Chunk 5c7c6298 sz= 187512 free
" "
Chunk 5c4b0c00 sz= 196852 free
" "
<<… 중략 …>>
가장
처음 ( literal sql 수행 전 ) 과 비교하면 free chunk 의 분포가 다르긴 하지만 두번째 ( literal sql 수행
후 ) 와 비교하면 골고루 재분포 된 것을 확인 할 수 있다. 결과적으로 free chunk 의
merge 를 확인 할 수 있었다.
/*+
ORA-04031 에러 발생시킨 후 다시 테스트 */