|
|
|
![]()
| [일반] [튜닝] 계속 swap paging 발생시에 |
|
| 호석 님께서 쓴 글입니다 - 211.♡.74.31 |
읽음:3960 |
|
|
sort_area_size Oracle
2006/12/26 11:28
http://blog.naver.com/bat1000/40032497842
Temporary tablespace,sort_area_size
2006/03/03 오후 9:23 | oracle
## temporary tablespace의 용량이 너무 크게 쌓여간다면 sort_area_size를 조정하자 2005-11-03 ##
* 정리한 문서라 두서가 없음 ^^
1. temporary tablespace의 용량이 많이 늘어남을 주시함.
- 적게 늘어가게 하려면?? 에서 출발
2. sort_area_size, sort_area_retained_size 조회
1) sort_area_size란?
SORT_AREA_SIZE에 대해서는 흔히 잘못된 이해를 하게된다. 대부분의
사용자들은 이 값이 모든 사용자들이 sort 작업에 사용하게 되는
공용 메모리 영역의 크기로 이해를 하는데, 실제로는 사용자 프로세스
별로 사용하게 되는 sort 영역의 크기를 나타낸다. 앞에서 살펴본
두개의 파라미터와 달리, SORT_AREA_SIZE는 SGA영역에 속하지 않는다.
만약 sort_area_size 값이 너무 작다면, sort 작업 대부분이 사용자의
temporary tablespace에서 디스크를 사용하여 이루어 지게 된다.
SQL 처리시 order by 나, group by 등을 사용할 경우에는 sort 작업이
발생하나. index 생성등에도 sort가 발생한다.
- index 생성시
- order by나 group by
- select 문장에서 distinct function
- union, intersect, minus operation
- sort-merge joins
- analyze command
메모리 sort는 디스크 sort에 비해 훨씬 좋은 성능을 보이므로,
지속적으로 SORT_AREA_SIZE 값을 모니터하여 튜닝을 하는것이 바람직하다.
하지만, 이 값을 너무 크게 지정할 경우, swapping이 발생하면서
시스템 성능이 급격하게 저하될 수 있다.
* SORT_AREA_SIZE는 세션별로도 지정가능하며, 지정하기 위해서는
ALTER SESSION 권한이 있어야 한다. 특정 세션에서 시스템상의
모든 메모리를 사용하도록 할 경우 시스템 성능이 급격히 저하
될 수도 있다.
2) sort_area_retained_size란?
init.ora 파일에서 지정하는 sort 작업 관련된 파라미터로
SORT_AREA_RETAINED_SIZE 도 있다. 이 값은 sort 가 끝난 후에도
유지하고자 하는 SORT_AREA_SIZE를 나타낸다. 이 파라미터는
SORT_AREA_SIZE 값과 같거나 적게 지정되어야 한다.
SORT_AREA_RETAINED_SIZE는 SORT_AREA_SIZE와 마찬가지로 적절한
값이 지정되어야 하는데, 소트작업을 수행하기 위해 할당된
메모리 영역이 소트 작업이 끝난 후가 아니라 세션이 종료될 때
까지 유지될 수 있기 때문이다. SORT_AREA_SIZE 값은 다른 파라미터와
마찬가지로 시스템에 가용한 실제 메모리 크기 이내에서 조정되어야
한다. 일반적으로 권고되는 SORT_AREA_SIZE 값은 65k 에서 1M 사이
에서 결정된다.
- show parameter sort_area_size
- show parameter sort_area_retained_size
- sort_size는 필요 이상으로 크게 할당시 낭비 가능성 있음(세션마다 할당, 1.3MB=65kb*20user)
- 9i인경우 workarea_size_policy 파라미터에서 자동분배(auto)지정하면 sort_area_size를 무효화 하고 대신에 pga_aggregate_target 초기화 파라미터를 사용하면, PGA용으로 할당된 전체 메모리 용량의 상한치를 정할 수 있다.
이때에는 개개의 서버 프로세스의 필요량에 따라 PGA가 할당됨
3. sort 감시 (sort의 횟수와 sort의 대상 행수 조사, sort_area_size를 늘려야 하는지 판별)
1) select * from v$sysstat where name like '%sorts%';
- sorts(memory) 메모리 내에서 실행되고 소트된 횟수
- sorts(disk) 일시적으로 세그먼트의 O/O가 필요한 소트의 횟수
- sorts(rows) 소트의 대상 행수
2) select m.value memory, d.value disk from v$sysstat m, v$sysstat d
where m.name='sorts(memory)' and d.name ='sorts(disk)';
- Memory란은 메모리 소트의 횟수, Disk란은 디스크 소트의 횟수
- 디스크 소트가 전체(메모리 소트 + 디스크 소트)의 5%를 초과할 때에는 소트용의 메모리 부족을 나타낸다.
- sort_area_size 또는 pga_aggregate_target에서 소트용의 메모리 영역의 사이즈를 적절히 크게 설정하면 디스크 소트 횟수를 줄일 수 있다.
- 여기서, 모든 문서(?)를 뒤져봤지만 '적절히'라는 의미를 모르겠다. sga size처럼 계산 방법이 있는것도 아니고..-_- 하여간 가장 근첩한 내용은 위처럼 v$sysstat를 조회한 값으로 '적절한'값을 예상할 뿐.. -_-
- sort_area_size는 초기 디폴트 64kb로 설정된다.
3. 메모리 할당 - sort_area_size 영역은 Server 구성에 따라 달라진다.
- 각 session별로 첫번째 sort가 수행되는 순간 memory가 할당되고 sort가
진행된다. sort가 수행되기 위해 필요한 영역이 점차 증가하다가, sort가
완전히 끝나거나 SORT_AREA_SIZE 크기에 도달하면 더 이상 memory를
할당하지 않는다.
이렇게 SORT_AREA_SIZE initialization parameter는 하나의 sort에 의해
사용되어질 real main memory의 최대 크기를 나타낸다. 이 parameter의
default값은 64K bytes이며, 이 parameter에 의해 만들어지는 sort memory
영역은 다음과 같이, server configuration에 따라 다른 부분에 위치하게 된다.
(1) dedicated server configuration
sort area가 Program Global Area (PGA)에 할당된다. PGA는 이 외에도
user 권한등을 포함한 session information, cursor status, stack
space 등을 포함한다.
(2) multi threaded server (MTS) configuration
sort area가 User Global Area(UGA)에 할당된다. 이 UGA는 SGA의 한
구성 요소인 shared pool내에 위치한다.
sort가 완전히 끝이 나면, sort영역은 SORT_AREA_RETAINED_SIZE parameter에
의해 정해진 memory크기로 줄어든다. 즉, SORT_AREA_RETAINED_SIZE는 다음
sort를 위해서 미리 확보해 두는 영역이다. 만약 SORT_AREA_RETAINED_SIZE가
0으로 설정되면 같은 process라도 매번 sort시다마 새로 memory를
SORT_AREA_SIZE만큼 할당하게 되는 것이다.
SORT_AREA_RETAINED_SIZE의 default값은 SORT_AREA_SIZE와 같아서, 이전
sort시에 사용된 memory부분이 항상 다음 sort시에 그대로 이용된다.
process가 완전히 종료되면 이 영역도 해제되어 MTS라면 shared pool영역으로
반환되고, dedicated방식인 경우 operating system영역으로 반환된다.
4. 세션 레벨 지정
- 이 두 initial parameter는 session level에서도 수정이 가능하다, 예를 들어
두 paramter를 모두 1m로 설정하는 명령은 다음과 같다.
SQL>alter session set
sort_area_size = 1024000
sort_area_retained_size=1024000;
만약 앞으로 새로 생성되는 모든 session에 대해서 영향을 받도록 하려면
alter system 명령을 이용하여야 한다. 예를 들면 다음과 같다.
SQL>alter system set
sort_area_size=102400 deferred
sort_area_retained_size=102400 deferred;
5. user별 지정
3.번 메모리 할당에서 설명한 SORT_AREA_SIZE의 memory외에도 추가적으로 sort시 공간이
필요하다면, 이때는 이미 sort가 된 data들은 disk에 쓰여지고, memory내의
sort영역을 비운 다음 그 영역을 새로운 sort되어져야 할 data들이 사용하도록
하면서 전체 data를 sort한다. 물론 이렇게 부분적으로 sort가 된 단위들은
나중에 다시 merge단계를 거쳐 전에 data가 모두 sort가 되도록 한다. 이와
같이 일부 정렬된 data들이 disk에 씌여지면 temporary segment가 생성되게
된다.
이렇게 정렬된 data들이 임시로 씌여지는 temporary tablespace는 각
user마다 생성시 지정되며, 임의로 변경 가능하다.
예를 들면 다음과 같다.
SQL>create user scott identified by tiger
default tablespace users
temporary tablespace temp;
SQL>alter user scott temporary tablespace newtemp;
5.tablespace의 space management
(dictionary managed vs. locally managed)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
tablespace는 extent단위로 space를 할당하는데, free space와 used space를
관리하는 방법은 dictionary managed방식과 oracle8i에서 새로 소개된
locally managed, 이렇게 두가지로 구분될 수 있다.
locally managed tablespace에 관한 자세한 사항은 <Bul:11860>을 참조하도록
한다.
(1) dictionary managed tablespace
이것은 oracle8.0과 그 이전 version에서, tablespace에서 extent를 할당하고
반환하는 방법으로 사용되는 것이다.
data dictionary table에 extent를 할당하거나 해제함에 따라 변경되는
extent정보를 저장한다. user에게는 dba_extents나 dba_free_space와 같은
view를 통해 조회가능하다.
이러한 형태의 tablespace내에 저장되는 segment는 storage option인
initial, next, pctincrease 에 의해 필요한 extent를 free space로 부터
할당받아가게 되는데 temporary segment를 위한 tablespace라면, 그 tablespace
(주로 TEMP라고 이름지어짐) 에 생성되는 모든 temporary segment는
tablespace의 default storage의 값을 segment별로 적용하게 된다.
(2) locally managed tablespace
이것은 8i에서만 사용가능한 방법으로, tablespace 자체가 자신의 extent에
대한 정보를 가지고 관리한다. locally managed tablespace에 속한 각
datafile들은 각 datafile마다 bitmap을 가지고 있어서, 각 block의
free와 used상태를 유지한다. bitmap의 각 block은 하나의 block 혹은
하나의 block의 group을 나타낸다.
이렇게 extent를 자신이 관리하는 tablespace는, 일정한 크기의 extent
size를 유지하는 UNIFORM형태나, system에서 자동으로 가변적인 extent
size를 결정하는 AUTOALLOCATE방식, 두가지로 지정가능하다.
이러한 형태의 tablespace는 INITIAL, NEXT, PCTINCREASE, MINEXTENTS
등의 storage option을 사용할 수 없다.
locally managed tablespace는 다음과 같은 이점을 가진다.
- extent를 local형태로 관리하면, recursive space management operation을
줄일 수 있다. 즉 dictionary table에 대한 조회나 dml을 피할 수 있다.
- 자동으로 bitmap만을 통해서, free space가 인접해있는지가 확인이 되기
때문에 extent를 coalescing할 필요가 없어진다.
tablespace를 생성할 때는 위의 두 형태 중 하나로 tablespace를 생성하여야
하며, alter 문장을 통해서 형태를 변경할 수는 없다.
6. permanent tablespace에 temporary segment생성
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
일반적으로 TEMP라는 이름을 가지는 user가 temporary tablespace로 지정하는
tablespace는 temporary나 permanent 중 하나로 지정가능하다.
permanent로 지정하게 되면 sort가 필요할 때마다 temporary segment를
tablespace에 생성하게 되고 sort가 끝나면 SMON이 이 segment를 해제한다.
일반적으로는 이렇게 permanent로 지정을 하여도 db user의 temporary로
지정된 tablespace에는 일반 user의 schema object를 생성하지는 않지만,
실제적으로는 생성하여도 생성된 object의 사용에 지장은 없다.
그런데 permanent로 지정하게 되면 다음과 같은 단점이 있다.
- transaction level로 sort가 필요할 때마다 temporary segment를 생성하게
되므로 여러개의 temporary segment가 생성되어 진다.
- temporary segment가 자주 생성되고 다시 없어짐에 따라 fragmentation이
발생할 가능성이 크다.
- SMON이 sort가 완료되면 temporary segment를 free시키는 역할을 하는데,
srot segment가 매우 많이 생성되게 되면, SMON이 그 segemnt를 지우는데
많은 시간을 할애하게 된다. 이렇게 되면 전체적인 db performance에
영향을 주게 된다.
permanent type으로 temporary segment를 위한 tablespace를 생성하는
문장의 예를 기술하였다.
(Oracle 8.1에서는 이렇게 선언하면 default로 dictionary-managed형태로
생성된다)
SQL>create tablespace temp datafile '/oracle/oradata/temp01.dbf'
default storage(initial 1m next 1m pctincrease 0);
Oracle 8.1이상에서 temporary segment를 위해 locally-managed permenent
tablespace를 생성하는 예문은 다음과 같다.
단, 반드시 UNIFORM extent size만이 가능하다.
SQL>create tablespace temp datafile '/oracle/oradata/temp01.dbf
extent management local uniform size 1m;
7.temporary tablespace에 temporary segment생성
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oracle7.3부터 sort operation을 더욱 효율적으로 사용하기 위해 temp
tablespace를 명시적으로 temporary로 지정하는 것이 가능하게 되었다.
이렇게 함으로써, temporary tablespace에 sort space(temporary segment)를
할당하고 해제하는 작업의 반복을 피할 수 있다.
특히, 이러한 지정은 OPS환경에서 성능 향상에 도움이 되며, 동시에 sort작업이
여러개가 수행되어 memory sort만으로는 부족한 경우에 성능 향상을 기대할 수
있다.
이러한 형태의 tablespace에 생성되는 temporary (sort) segment는
db startup 후 처음수행되는 sort작업이 memory내의 sort를 마치고, disk로
내려 쓸때 생성된다. 이렇게 일단 temporary segment가 생성되면 이 segment는
db가 restartup될때까지 줄어들거나 해제되지 않는다. sort가 필요한
다른 transaction들은 미리 생성된 temporary segment를 공유하게 되나,
extent를 공유하지는 않는다.
sort 작업은 사용가능한 extent가 존재하지 않으면 새로운 extent를 할당하면서
temporary segment를 늘려나가며, 이미 설명한 바와 같이 db가 running되어
있는 동안은 줄어들지 않는다. 그러나 sort가 끝나게 되면 사용한 extent를
free로 표시하여 다른 transaction이 재사용가능하도록 한다. 결국 temporary
segment는 무한정 계속 늘어나기 보다는 일정한 size정도까지 늘어난 후에는
extent별로 재사용 되어지는 것이다.
이러한 temporary tablespace내의 sort segment에 대한 정보는
Sort Extent Pool (SEP)에 저장하며, 이것은 SGA내에 위치한다.
temporary tablespace를 이용하여 sort를 수행하여야 하는 모든 문장은
SGA내의 이 SEP를 확인하여 free extent를 찾아낸다. 이렇게 각 operation후에
extent가 할당, 해제되는 과정이 필요없기 때문에 기존의 permanent
tablespace에 temporary segment를 생성하는 것보다 db전반적인 속도가
향상되는 것이다.
앞에서 이 temporary tablespace형태가 특히 OPS에서 성능 향상을 가져올
수 있다고 하였는데 그 이유는 이 SEP에서 할당되는 extent는 다른
instance끼리 같은 extent를 사용하는 경우는 없도록 instance id를 이용하여
할당받도록 하여 항상 같은 instance에만 할당이 된다.
즉, ping이 발생할 확률이 줄게 되어 속도의 향상을 가져오게 된다.
temporary tablespace내의 sort segment의 해제 작업은 db shutdown시에
자동으로 이루어지는 것이 아니고 db startup시에 SMON에 의해서 수행된다.
database가 open되고 나면, temporary tablespace에서 extent를 해제하여
coalescing작업을 수행한다. 이러한 작업으로 인해 db가 startup된 직후
SMON이 CPU를 많이 사용하는 경우가 발생하는 것이다.
만약에 temporary tablespace의 NEXT값이 너무 작게 지정되어 매우 많은
갯수의 extent가 많들어지게 되면, SMON의 이러한 작업으로 인해 사용가능한
CPU resource가 거의 없어짐으로 인해 db사용에 문제가 야기될 수 있다.
tablespace를 temporary로 지정하는 문장의 예는 다음과 같다.
(Oracle 8.1에서는 이렇게 선언하면 default로 dictionary-managed형태로
생성된다)
SQL>create tablespace temp datafile '/oracle/oradata/temp01.dbf'
default storage(initial 1m next 1m pctincrease 0)
temporary;
Oracle 8.1이상에서 temporary segment를 위해 locally-managed temporary
tablespace를 생성하는 예문은 다음과 같다.
단, temporary tablespace에서는 반드시 UNIFORM extent size만이 가능하다.
SQL>create temporary tablespace temp
datafile '/oracle/oradata/temp01.dbf
extent management local uniform size 1m;
이렇게 temporary tablespace로 생성된 tablespace에는 table이나 index와
같은 permanent한 schema object는 생성될 수 없다.
dictionary-managed temporary tablespace의 경우는 다음과 같은 문장에
의해 permanent로 변경이 가능하다.
SQL>alter tablespace temp permanent;
8.guidelines
~~~~~~~~~~~~
- temporary segment가 자주할당되고, 해제되는 것을 피하고자 한다면 temporary
type의 tablespace를 사용한다.
- 하나 이상의 temporary tablespace를 사용하는 것도 동시에 sort가 발생하는
경우 도움이 된다. 즉 db user마다 temporary tablespace를 달리 지정하라는
것인데, 이것은 db user별로 업무 분할이 잘 되어 있는 OPS에서 특히 유용하다.
그 외에도 각 tablespace를 다른 disk에 위치시키는 경우 속도 향상을 가져올
수 있고, db user별로 업무가 나뉘는 경우 각 업무에 필요한 sort space를
따로 관리하는 장점이 있다.
- temporary tablespace의 default storage를 지정하는 경우, INITIAL은 NEXT와
같게 하고 pctincrease는 항상 0으로 한다.
pctincrease가 0이면 temporary tablespace의 모든 extent는 같은 크기의
조각을 가지게 되고, 이렇게 되면 coalescing이 수행되지 않아도 새로운
sort작업이 기존에 발생한 extent를 그대로 이용할 수있게 된다.
- NEXT는 다음과 같은 공식에 의해서 설정하는 것이 유리하다.
n*s + b
n: 양수
s: SORT_AREA_SIZE parameter의 값
b: DB_BLOCK_SIZE parameter의 값
이렇게 하는 이유는 temporary segment는 항상 sort_area_size만큼씩
memory에서 쓰여지게 되며 db_block_size만큼의 header block을 extent마다
가지기 때문이다.
9.temporary segments에 대한 정보 확인
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
v$sort_segment view에서 sort segment를 포함하는 temporary tablespace를
확인할 수 있다.
extent_size : extent의 크기, Oracle block갯수
tatal_extents : 빈 것과, 사용중인 것 모두 합해 segment에 포함된 tatal
extent의 갯수
used_extents : 현재 사용중인 extent의 갯수
free_extents : 현재 free로 표시되어 있는 extent의 total갯수
max_used_size : 하나의 operation, 즉 한 transaction에 의한 한번의 sort와
같은 작업을 위해 사용된 extent의 갯수 중 최대로 큰 경우
현재 user에 의해 사용되어지고 있는 temporary segment내의 공간을
확인하려면, v$sort_usage와 v$session view를 이용한다.
단, sort_usage는 sort시에만 정보가 나타나는 것을 주의해야 한다.
예를 들어 다음과 같이 조회할 수 있다.
select s.username, u.tablespace, u.contents, u.extents, u.blocks
from v$session s, v$sort_usage u
where s.saddr=u.session_addr;
USERNAME TABLESPACE CONTENTS EXTENTS BLOCKS
-------- ----------- ---------- -------- --------
SCOTT TEMP TEMPORARY 10 10240000
--------------------------------------------------------------------------------
이야기 둘 **********************************************************************
1) System적 변경
- DB 를 다시 create 할 경우 DB_BLOCK_SIZE를 증가시킨다.
이 block size 가 큰 경우, 더 작은 I/O cycle 이 발생한다.
이 변경값이 permanent한 경우는 변경 전과 비교한 모든 효과를 고려한다.
- 1개의 커다란 rollback segment 를 생성하고, 이외의 rollback segment는 모두 offline 한다.
- 1개의 rollback segment는 import 되어질 table 중 가장 큰 것의 50%
정도로 잡는다. import 는 기본적으로 insert into table_name values
(,,,,,) 이고 이 경우 rollback segment 에는 rowid 만 들어가게 되므로
2개의 같은 size 인 extent를 갖는 rollback 을 생성하면 된다.
- import 가 끝날 때까지 database 를 NOARCHIVELOG mode로 유지한다.
이는 archive log 를 생성하는 overhead 를 없앨 수 있다.
- rollback segment와 마찬가지로, 커다란 redo log file을 생성한다.
클수록 log switch 가 발생하지 않으므로 import 시는 좋다.
작은 size 의 redo log 는 offline 하도록 한다. alert.log 에 나타나는
'Thread 1 cannot allocate new log, sequence 17, Checkpoint not
complete'의 메시지는 좀 더 크거나 더 많은 갯수의 redo log file 이
필요함을 나타낸다.
- 가능하다면 table, rollback, redo log file 이 다른 disk에 있도록 한다.
이는 i/o contention을 줄일 수 있다.
2) Init.ora Parameter 변경
- LOG_CHECKPOINT_INTERVAL을 redo log file의 size 보다 크게 준다.
이 숫자는 OS block 을 의미하여 unix 에서는 512 byte 이다.
이를 크게 하면 log swich time을 줄일 수 있다.
- SORT_AREA_SIZE를 증가시킨다.
인덱스를 아직 생성하지 않았다 하더라도, unique, primary key는 있기
때문이다.
이 값의 증가는 같은 machine 안에 별도의 어떤 작업이 있는가,
free memoty 가 얼마 만큼 있느냐에 따라 다르긴 하지만 평상 시의 5-10
배로 한다.
만일 machine이 swapping이나 paging이 발생하면 더욱 크게 해준다.
3) Import Options 변경
- COMMIT=N option을 사용한다.
이를 사용하면 buffer의 data 가 insert되고 commit함이 아니라, 각
object(table) 의 모든 data가 insert 후 commit 된다.
만일 rollback 이 작다면 이 option 을 이용할 수 없다.
- BUFFER 크기를 크게 한다.
이것 역시 시스템의 다른 activity 나, 데이타베이스 크기에 따라 다르다.
이 값이 크면 export file을 access 하는 횟수를 줄일 수 있다.
- import 시 INDEXES=N option을 사용한다.
만일 index를 생성 시는 SORT_AREA_SIZE 는 더욱 커야 한다.
----------------------------------------------------------------------------------
|
|
|
|
|
|
