[오라클]3-2단계 Temp 세그먼트

소트 영역의 튜닝 분석   대부분의 사용자들이 작성하는 SQL문은 실행 시 분류작업(SORTING)이 발생합니다. 분류작업이 발생하면 별도의 시간과 공간이 추가로 필요하기 때문에 분류작업이 발생하지 않는 경우보다 성능이 저하될 수밖에 없습니다. 또한, 별도의 공간이 분류작업을 하기에 충분하지 못하다면 성능은 더 저하될 수밖에 없는 것입니다. 사용자의 SQL문에서 분류작업이 발생하는 경우는 다음과 같습니다.   - 인덱스를 생성하는 문법을 사용하는 경우(CREATE INDEX ~문) - 인덱스가 있는 테이블에 병렬로 데이터를 입력하는 경우(INSERT INTO~PARALLEL(DEGREE n) - ORDER BY, GROUP BY을 사용하는 경우(SELECT ~ ORDER BY ~ GROUP BY ~) - DISTINCT 키워드를 사용하는 경우(SELECT DISTINCT ~ ) - UNION, INTERSECT, MINUS 연결 연산자를 사용하는 경우(SELECT~ UNION SELECT ~) - 인덱스가 없는 2개의 테이블을 조인하는 경우(SELECT ~ FROM a, b WHERE ~) - ANALYZE 명령문을 사용하는 경우(ANALYZE TABLE ~)   이와 같은 SQL문이 실행된다면 내부적으로 분류작업을 통해 데이터를 리턴해 줍니다.   소트 메모리 영역(Sort Memory Area)   자~ 그럼 생각해 보세요. 이러한 분류작업은 어디서 실행될까요 ? "SQL문을 처리하는 과정"과 "DML문을 처리하는 과정"을 통해 데이터베이스의 구조를 알아보았는데 이러한 분류작업은 어떤 구조에서 처리되는 걸까요? 두 가지 경우에 대해서 알아보아야 할 것입니다. 첫 번째, 전용서버 환경에서는 서버 프로세스에 있는 PGA(PROGRAM GLOBAL AREA) 영역의 SORT AREA 영역에서 분류작업이 실행됩니다. 두 번째, 공유서버 환경에서는 SORT AREA 영역이 공유 풀 영역에 존재하기 때문에 모든 분류작업이 SGA 영역에서 실행됩니다. 그럼, 이 영역의 크기는 어떤 값에 의해 결정될까요 ? INIT<DB명>.ORA 파일에 있는 다음 파라메터 값에 의해 결정됩니다. 만약, 대용량 데이터에 대한 분류작업이 발생하는 경우에는 이 파라메터 값을 높게 설정하여 메모리 영역에서 보다 원활한 분류작업이 발생하도록 해야만 좋은 성능을 기대할 수 있습니다.   SORT_AREA_SIZE = [크기]   또한, 사용자의 SQL문에 의해 사용된 SORT_AREA_SIZE 영역이 분류작업 후 PGA 영역에 계속해서 할당되어 있으면 메모리의 낭비가 발생할 수 있기 때문에 다음과 같은 파라메터에 의해 SORT_AREA_SIZE 영역을 축소(SHRINK) 시킬 수 있습니다. 이 파라메터의 값은 SORT_AREA_SIZE 파라메터 값을 초과하여 정의할 수는 없습니다.   SORT_AREA_RETAINED_SIZE = [크기]   소트 디스크 영역(Sort Disk Area)   자~ 한가지 문제에 대해서 더 알아보겠습니다. 시스템 또는 데이터베이스를 위한 메모리 영역은 항상 크기가 제한되어 있습니다. 만약, 분류작업을 해야할 테이블이 100만 건의 행을 가지고 있다면 모든 행을 메모리 영역에 저장한 후 분류작업을 할 수는 없겠죠 ? 그래서, 필요한 영역이 TEMPORARY 테이블스페이스입니다. 메모리 영역인 SORT_AREA_SIZE에서 1차적인 분류작업이 발생하고 작업이 완료되지 못하면 디스크 영역에 생성되어 있는 TEMPORARY 테이블스페이스에 분류된 내용의 일부를 잠시 저장하게 됩니다. 이러한 연속적인 작업을 통해 대용량 데이터에 대한 분류작업을 실행하게 됩니다. 참~~ TEMPORARY 테이블스페이스는 오라클 유니버설 인스톨러에 의해 오라클 데이터베이스를 설치하면 기본적으로 생성되는 논리적 구조입니다.(보다 자세한 내용은 "오라클 9i의 생김새 알아보기"를 참조하십시오.) 예를 들어, 윈도우 시스템에서 [설정] --> [제어판] --> [시스템] --> [고급] --> [성능옵션]에 보면 가상 메모리를 설정하는 기능이 있습니다. 시스템의 메모리 영역에서 완료되지 못한 사용자 작업의 일부를 잠시 저장해 두는 디스크 상의 공간을 의미합니다. 이와 유사한 개념을 가진 데이터베이스의 논리적 구조가 TEMPORARY 테이블스페이스입니다. TEMPORARY 테이블스페이스는 다음과 같은 문법으로 생성됩니다.     CREATE DATABASE ora90 LOGFILE GROUP 1 ('c:\oracle\oradata\ora90\redo01.log') size 10m, GROUP 2 ('c:\oracle\oradata\ora90\redo02.log') size 10m, GROUP 3 ('c:\oracle\oradata\ora90\redo03.log') size 10m DATAFILE 'c:\oracle\oradata\ora90\system01.dbf' size 100m UNDO TABLESPACE undo DATAFILE 'c:\oracle\oradata\ora90\undo01.dbf' size 50m DEFAULT TEMPORARY TABLESPACE temp TEMPFILE 'c:\oracle\oradata\ora90\temp01.dbf' size 30m EXTENT MANAGEMENT LOCAL UNIFORM size 1m CHARACTER SET ko16ksc5601 NATIONAL CHARACTER SET ko16ksc5601 SET TIME_ZONE = 'Korea/Seoul';   이 문법은 오라클 유니버설 인스톨러에 의해 최초 만들어지는 TEMPORARY 테이블스페이스에 관련된 문법입니다. 데이터베이스 설치 후 모든 사용자들은 TEMP 테이블스페이스에서 모든 분류작업을 하게 됩니다. 또한, 이 테이블스페이스는 기본적으로 로컬리매니저 테이블스페이스 형태로 생성됩니다.   CREATE TEMPORARY TABLESPACE [테이블스페이스명] TEMPFILE '[경로와 파일명] SIZE [크기] EXTENT MANAGEMENT LOCAL UNIFORM SIZE [크기];   이 문법은 데이터베이스 생성 후 추가적으로 TEMPORARY 테이블스페이스를 생성하는 방법입니다. "사용자 관리" 및 "테이블스페이스 생성 시 주의사항"에서 언급했던 대로 좋은 성능을 위해서는 사용자별로 TEMPORARY 테이블스페이스를 생성하여 할당해야 합니다.       충분한 SORT 공간을 할당하라.   그럼, 분류영역에 대한 튜닝 분석방법을 알아보겠습니다. 데이터베이스가 생성되면 기본적으로 TEMPORARY 테이블스페이스가 생성되며 또한, 사용자는 추가적으로 TEMPORARY 테이블스페이스 생성하게 됩니다. 사용자가 분류하려는 테이블의 데이터가 너무 커서 현재 할당되어 있는 TEMPORARY 테이블스페이스 공간으로는 모든 분류작업을 할 수 없을 때 SQL문의 성능은 저하됩니다. 다음은 자료사전을 통해 분류영역을 분석하는 방법입니다.   SQL > select disk.value "Disk", mem.value "Mem", (disk.value / mem.value) * 100 "Ratio" from v$sysstat mem, v$sysstat disk where mem.name = 'sorts (memory)' and disk.name = 'sorts (disk)';   V$SYSSTAT 자료사전을 참조하면 이 영역에 대한 튜닝 여부를 확인할 수 있습니다. "sorts (memory)"는 서버 프로세스가 PGA의 SORT AREA 영역에서 작업한 블록 수이며 "sorts (disk)"는 TEMPOR ARY 테이블스페이스의 디스크 공간에서 작업한 블록 수를 의미합니다. "sorts (disk)" 값이 "sorts (memory)" 값의 5% 미만일 때 분류작업 시 좋은 성능을 기대할 수 있습니다. 만약, 기준치에 적합하지 않다면 SORT_AREA_SIZE 파라메터 값이 너무 작아 성능이 저하되고 있으므로 파라메터의 값을 높게 설정해 주어야 합니다. 또한, V$SORT_USAGE 자료사전을 조회하여 세션별로 TEMPORARY 테이블스페이스를 얼마나 사용하고 있는지를 분석할 수도 있습니다. 다음 예제를 따라 해 보십시오. SYSTEM 사용자로 접속하여 현재 시점의 SORTING 영역의 튜닝상태를 분석해 봅시다.     $ sqlplus scott/tiger SQL > select disk.value "Disk", mem.value "Mem", (disk.value / mem.value) * 100 "Ratio" from v$sysstat mem, v$sysstat disk where mem.name = 'sorts (memory)' and disk.name = 'sorts (disk)';   Disk Mem Ratio 0 628 0   ▲ 대부분의 분류작업은 PGA 영역에서 작업되고 있습니다     $ sqlplus scott/tiger SQL > alter session set sort_area_size=10000000;   ▲ PGA 영역을 보다 크게 설정하는 방법입니다.   SQL > SELECT * FROM big_emp ORDER BY ename   ▲ SORTING이 발생하는 SQL문을 실행하면 SORTING 정보를 메모리와 디스크 공간에 저장하게 될 것입니다.   SQL > select disk.value "Disk", mem.value "Mem", (disk.value / mem.value) * 100 "Ratio" from v$sysstat mem, v$sysstat disk where mem.name = 'sorts (memory)' and disk.name = 'sorts (disk)';   Disk Mem Ratio 1 660 .151515152   ▲ 대부분의 분류작업은 PGA 영역에서 작업되고 있습니다. 디스크 공간에 SORTING 정보가 일부 저장되었군요.   SQL > EXIT   이번에는, SORT_AREA_SIZE 파라메터를 낮게 설정하고 다시 분류영역의 상태를 분석해 봅시다.     $ sqlplus scott/tiger SQL > alter session set sort_area_size=100;   ▲ 이전 상태보다 PGA 영역을 낮게 활성화 합니다.   SQL > SELECT * FROM account ß SORTING ORDER BY customer;   ▲ SORTING이 발생하는 SQL문을 실행하면 SORTING 정보를 메모리와 디스크 공간에 저장하게 될 것 입니다.   SQL > select disk.value "Disk", mem.value "Mem", (disk.value / mem.value) * 100 "Ratio" from v$sysstat mem, v$sysstat disk where mem.name = 'sorts (memory)' and disk.name = 'sorts (disk)';   Disk Mem Ratio 3 674 .445103858   ▲ 5% 미만이면 성능에 큰 영향을 미치지 않습니다. 하지만 , SORT_AREA_SIZE를 높게 설정하면 보다 좋은 성능을 기대할 수 있습니다.   다음은 디스크 영역에 존재하는 TEMPORARY 테이블스페이스의 현재 사용 현황을 분석하는 방법입니다. 또 다른 윈도우-창에서 데이터베이스에 접속한 다음 사용 중인 TEMPORARY 테이블스페이스의 튜닝상태를 분석하십시오.   SQL > col TABLESPACE_NAME format a10 SQL > select tablespace_name, current_users, total_extents, used_extents,extent_hits, max_used_blocks, max_sort_blocks from v$sort_segment; SQL > select tablespace_name, current_users, extent_hits, max_used_blocks, max_sort_blocks from v$sort_segment;   TABLESPACE CURRENT_USERS MAX_SORT_BLOCKS TEMP 0 1024   TABLESPACE : TEMPORARY 테이블스페이스 이름 CURRENT_USERS : 분류작업을 실행하고 있는 사용자 수 MAX_SORT_BLOCKS : 분류작업에 의해 사용된 블록 수   다음은 자료사전은 어떤 사용자에 의해 어떤 TEMPORARY 테이블스페이스가 얼마나 사용되고 있는지를 분석하는 방법입니다. 먼저, 새로운 세션을 하나 더 생성하고 기존에 접속된 세션은 그대로 유지하십시오.     $ sqlplus scott/tiger SQL > SELECT * FROM big_emp ORDER BY ename;   ▲ 결과가 화면에 계속 출력 됩니다.   또 다른 윈도우-창에서 데이터베이스에 접속한 다음 사용 중인 TEMPORARY 테이블스페이스의 튜닝상태를 분석하십시오. 이 분석결과는 현재 분류작업이 진행 중인 정보 만 나타납니다. 즉, 방금 실행했던 SQL문이 완료되기 전에 자료사전을 참조하십시오.   SQL > col USERNAME format a10 SQL > col user format a10 SQL > select USERNAME, USER, CONTENTS, SEGTYPE, EXTENTS, BLOCKS from v$sort_usage;   USERNAME USER CONTENTS SEGTYPE EXTENTS BLOCKS SCOTT SCOTT TEMPORARY SORT 4 1024   ▲ SCOTT 사용자의 분류작업에 의해 4개의 익스텐트를 사용하고 있습니다.   이번에는 TEMPORARY 테이블스페이스를 여러 개 만들어 사용자별로 할당해 줄 수 있도록 재구성해 보겠습니다. 데이터베이스가 설치될 때 생성된 TEMPORARY 테이블스페이스는 데이터베이스를 사용하는 모든 사용자들이 공유하는 공간이므로 경합 현상이 발생하면 WAIT 현상이 발생하게 됩니다. 사용자마다 별도로 TEMPORARY 테이블스페이스를 생성하여 할당한다면 경합현상을 분산시킬 수 있습니다.   SQL > CONNECT system/manager SQL > create tablespace temp10 datafile '$HOME/dbs/temp10.dbf' size 2m TEMPORARY;     SQL > create tablespace temp11 datafile '$HOME/dbs/temp11.dbf' size 2m TEMPORARY;   SCOTT 사용자에게는 TEMP10을, HR 사용자에게는 TEMP11을 할당 하십시오.   SQL > alter user scott temporary tablespace temp10; SQL > alter user hr temporary tablespace temp11;     SQL > connect scott/tiger SQL > SELECT USERNAME, TEMPORARY_TABLESPACE 2 FROM DBA_USERS 3 WHERE USERNAME = 'SCOTT' OR USERNAME = 'HR';   USERNAME TEMPORARY_TABLESPACE HR TEMP11 SCOTT TEMP10       효과적인 SORT 영역의 활용     인덱스 생성시 NOSORT 옵션을 사용하라.     인덱스를 생성하면 기본적으로 분류작업이 발생합니다. 즉, 인덱스는 분류작업을 실행한 후 컬럼 값을 기준으로 인덱싱을 하기 때문입니다. 만약, 테이블의 해당 컬럼이 이미 분류가 되어 있다면 인덱스 생성시 분류작업을 할 필요가 없을 것 입니다. 이언 경우에는 다음과 같이 NOSORT 옵션을 사용하면 분류작업을 하지 않습니다. 하지만, 반드시 해당 컬럼이 분류되어 있어야 합니다.   SQL > CREATE INDEX I_emp_empno ON emp (empno) NOSORT;     UNION 연산자보다 UNION ALL 연산자을 사용하라.     UNION 연산자는 여러 개의 연결된 SQL문에서 중복된 행들은 하나 만 출력되는 집합 연산자입니다. 실행될 때 내부적으로 분류작업이 발생합니다. 만약, 해당 컬럼에 중복된 행이 없거나 또는 중복된 행을 참조해도 무방한 경우에는 되도록 UNION ALL 연산자를 사용하는 것이 불필요한 TEMPORARY 테이블스페이스 공간을 사용하지 않는 방법입니다.       DISTINCT 키워드의 사용을 자제하라.     DISTINCT 키워드는 해당 컬럼을 참조할 때 중복된 값들은 하나 만 출력되는 키워드 입니다. 실행될 떄 내부적으로 분류작업이 발생합니다. 반드시, 사용해야 하는 경우이외에 사용을 자제하는 것이 불필요한 TEMPORARY 테이블스페이스 공간을 사용하지 않는 방법입니다.       ORDER BY절의 사용을 자제하라.     개발자들이 실행하는 대부분의 SQL문에는 ORDER BY 절이 항상 사용된다고 해도 무방할 정도로 자주 사용되는 문법절 입니다. 하지만, 빈번한 분류작업은 불필요한 분류공간을 사용하게 되기 떄문에 성능에 도움이 되지 않습니다. 되도록 적절한 인덱스를 사용한다면 원하는 형태의 결과를 참조할 수 있으므로 인덱스를 사용하십시오.     분류작업을 할 때 모든 컬럼의 사용을 자제하라.     분류작업을 할 때 SELECT절에 불필요한 컬럼들을 정의하면 분류공간이 낭비되므로 참조해야 만 하는 컬럼들 만 정의하십시오.(다음 페이지에서 자세히 소개됩니다.)   SQL > SELECT ename FROM big_emp ORDER BY ename;   ▲ ENAME 컬럼 만 분류작업 합니다.   SQL > SELECT * FROM big_emp ORDER BY ename;   ▲ 전체 컬럼을 분류작업 합니다.           분류영역의 크기 계산   개발자들이 실행하는 SQL문에서 SORTING이 발생하면 SORTING 작업을 하기 위해 임시공간이 필요한데 이 공간을 TEMPORARY 테이블스페이스라고 합니다. 오라클 데이터베이스를 설치하면 최초 하나의 임시공간이 생성되는데, 이 공간에 대한 경합현상을 피하기 위해서는 사용자별로 임시공간을 할당해 주는 것이 가장 좋습니다. 그렇다면, 추가적으로 TEMPORARY 테이블스페이스를 생성할 때 그 크기는 어느 정도가 되어야 할까요 ? 다르게 해석해 보면, 개발자들이 실행하는 SQL문에서 SORTING이 발생할 때 얼마나 많은 SORTING 정보가 생성되는지를 알 수 있다면 그 크기를 쉽게 산정 해 볼 수 있을 것 입니다. 위의 왼쪽그림은 SELECT문에 의해 발생되는 SORTING 정보의 크기를 산정하는 방법입니다. 먼저, 왼쪽그림은 SELECT절에 '*'를 정의한 후 ORDER BY절에 의해 SORTING하는 경우입니다. 만약, 테이블 하나의 컬럼 수가 10개이고, 행 길이는 100 BYTE 이며 3000 행이 저장되어 있다면 이 문장이 실행되면서 필요로 하는 SORTING 영역의 크기는 다음과 같습니다.   SORTING 영역의 크기 = 전체 행수 X (행의 길이 + (2 X 컬럼 수)) = 3000 X (100 + 2 X 10) = 420,000 BYTE   컬럼 수에 2를 곱하는 이유는 하나의 컬럼 당 2 BYTE의 오버헤드가 필요하기 때문 입니다. 계산결과 이 SELECT문은 실행 시 42,000 BYTE의 SORTING 공간을 사용하게 될 것입니다. 이번에는 오른쪽 그림의 경우입니다. 왼쪽그림과 같은 원리, 같은 공식에 의해 SORTING 영역을 계산해 보면 다음과 같습니다.   SORTING 영역의 크기 = 전체 행수 X (행의 길이 + (2 X 컬럼 수)) = 3000 X (8 + 2 X 2) = 36,000 BYTE   이번 경우에는 ENAME 컬럼 만을 참조하며, 36,000 BYTE의 SORTING 공간을 사용하게 될 것입니다. 두 개의 SQL문에서 계산된 SORTING 영역의 크기를 산정해 보면 개발자가 실행하는 SQL문이 어떻게 작성되었느냐에 따라 임시공간의 사용범위가 달라진다는 것을 확인해 보았습니다. 되도록 최적의 SQL문을 작성하는 것이 임시공간을 효과적으로 사용할 수 있을 뿐 아니라 SQL문의 성능을 향상시키는 방법이기도 합니다. 다음 예제를 따라 해 보십시오. (데이터베이스를 재 시작한 다음 실습을 하십시오.)   SQL > SELECT ename FROM big_emp ORDER BY ename;   TABLESPACE _NAME CURRENT _USERS EXTENT _HITS MAX_USED _BLOCKS MAX_SORT _BLOCKS TEMP 1 1 256 256   ▲ 특정 컬럼 만 참조하는 경우 분류작업을 위해 최대 256 블록공간이 사용되고 있습니다.   SQL > SELECT * FROM big_emp ORDER BY ename;   TABLESPACE _NAME CURRENT _USERS EXTENT _HITS MAX_USED _BLOCKS MAX_SORT _BLOCKS TEMP 1 1 512 512   ▲ 모든 컬럼을 참조하는 경우 분류작업을 위해 최대 512 블록공간이 사용되고 있습니다.   두 개의 SQL문은 거의 같은 결과가 리턴되지만 두 번째 문장은 분류영역을 최소한도로 사용하기 떄문에 더 빠른 성능을 보여줄 것입니다.