TEMPORARY SEGMENTS에 대해서 (Oracle version: 7.X ~ 8.1)

==================================================================

원본출처 : http://211.209.69.159:8000/koug/board.jsp?bbs=tiptech&pg=0&seq=370&act=view

이 문서에서는 sort작업의 수행과 관련하여, memory상의 할당 및 disk상의 temporary segment 생성 등에 관하여 살펴보았다.

특히 temporary segment를 temporary type의 tablespace에 생성하도록 지정한 경우에 대해서 자세히 살펴보았으며, 그러한 작업시 user가 확인할 수 있는 dictionary정보 등을 기술하였다.

이 문서는 아래와 같이 7가지 part로 나누어 설명하였으며, Oracle 7에서

Oracle 8i에 모두 적용되는 사항이다.

1. temporary segment가 생성되는 operation

2. 메모리 할당

3. user별 지정

4. tablespace의 space management

5. permanent tablespace를 사용하는 경우

6. temporary tablespace를 사용하는 경우

7. guideline

8. temporary segments에 대한 정보 확인

1. temporary segment가 생성되는 operation

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

temporary segment를 생성하게 되는 sort operation은 다음과 같이 정리할 수 있다.

- index생성

- ORDER BY나 GROUP BY

- SELECT문장에서 DISTINCT function

- UNION, INTERSECT, MINUS operation

- Sort-Merge joins

- analyze command

이 외에도 unique나 primary key를 생성하거나 enable시키는 문장의 경우

sorting을 위해 temporary segment를 사용하게 되며, CREATE TABLE AS SELECT나 CREATE INDEX의 경우 table이나 index가 만들어지는 과정중에는 segment type이 temporary 로 나타난다.

CREATE TABLE이나 CREATE INDEX 문장이 완성되어 table이나 index가 완전히 생성되면 type이 table과 index로 변경된다. 이러한 CREATE TABLE문장이나 CREATE INDEX 문장이 도중에 space부족으로 실패하면, user의 temporary tablespace가 아닌 table이나 index를 생성하는 tablespace에 temporary segment위한 공간이 부족하다는 오류가 발생하는 것이다. 물론, CREATE INDEX시 이렇나 temporary segment외에도 sort를 위한 temporary segment는 user의 temporary tablespace에 생성된다.

2. memory allocation

~~~~~~~~~~~~~~~~~~~~

각 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영역으로 반환된다.

이 두 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; 

3. user 별 지정

~~~~~~~~~~~~~~

2번에서 설명한 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; 

4. 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 문장을 통해서 형태를 변경할 수는 없다.

5. 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시키는 역할을 하는데, sort 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 permanent tablespace를 생성하는 예문은 다음과 같다.

단, 반드시 UNIFORM extent size만이 가능하다.

        SQL>create tablespace temp datafile '/oracle/oradata/temp01.dbf' 
  extent management local uniform size 1m; 

6. temporary tablespace에 temporary segment 생성

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oracle7.3부터 sort operation을 더욱 효율적으로 사용하기 위해 temp tablespace를 명시적으로 temporary로 지정하는 것이 가능하게 되었다.

이렇게 함으로써, temporary tablespace에 sort space(temporary segment)를 할당하고 해제하는 작업의 반복을 피할 수 있으며, 특히 OPS 환경에서 성능 향상에 도움이 될 수 있다.

이러한 형태의 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; 

7.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마다 가지기 때문이다.

8. temporary segments에 대한 정보 확인

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

v$sort_segment view에서 sort segment를 포함하는 temporary tablespace를 확인할 수 있다.

extent_size : extent의 크기, Oracle block갯수 
total_extents : 빈 것과, 사용 중인 것 모두 합해 segment에 포함된 total 
        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