Sort시 필요한 메모리 공간 확인

SQL에서 정렬을 하기 위해서는 SORT 영역이 필요하다.

SORT 영역이 작을 경우, 데이터를 정렬 하기 위하여 Temp 공간을 사용하게 되며, 이로 인하여 쿼리 성능이 저하될 수 있다.

SORT를 메모리에서만 하는지 TEMP 공간을 사용하는지에 따라서 Optimal / One Pass / Multi Pass 로 구분되어 진다.

Optimal     : Sort가 PGA 영역에서 모두 이루어짐

One Pass : Sort 영역의 메모리 공간에서 한번에 정렬이 이루어 지지 않아서, Temp영역을 이용하여 한 번의 Merge 작업

                   을 통해 정렬을 완료 함.

Multi Pass : Merge 작업이 2회 이상 발생 함. 

SORT 영역은 WORKAREA_SIZE_POLICY 파라메터에 의해서 자동으로 관리 되거나, 수동으로 관리 된다.

WORKAREA_SIZE_POLICY=AUTO

PGA_AGGREGATE_TARGET 파라메터에 저장된 값을 고려하여 정렬 영역의 크기를 결정 함.

이 때 최대로 사용할 수 있는 크기는 _SMM_MAX_SIZE 파라메터와 _SMM_PX_MAX_SIZE(병렬작업) 의 값으로 확인 할 수 있다. 

_SMM_MAX_SIZE와 _SMM_PX_MAX_SIZE 설정값은 kb 임 

WORKAREA_SIZE_POLICY=MANUAL

SORT_AREA_SIZE 값을 사용하여 최대 크기를 지정 함. 

테스트를 통하여 SORT시에 사용 되어 지는 메모리의 양을 확인해 볼 수 있다.

  - 테스트 테이블 생성 SQL> CREATE TABLE t1 AS SELECT * FROM dba_objects ; - 테스트를 위해서 파라메터 변경 SQL> ALTER SESSION SET workarea_size_policy=manual ; SQL> ALTER SESSION SET sort_area_size = 0 ; - 테스트 쿼리 실행 SQL> SELECT /*+ gather_plan_statistics */ * 2 FROM ( SELECT rownum rn , t1.* 3 FROM t1 4 ORDER BY object_id desc ) 5 WHERE rn BETWEEN 10 AND 20 ; - 플랜 확인 SQL> SELECT * 2 FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last rows cost bytes +alias +outline +predicate')) ; PLAN_TABLE_OUTPUT SQL_ID 3gpz090fq0myt, child number 1 ------------------------------------- SELECT /*+ gather_plan_statistics */ * FROM ( SELECT rownum rn , t1.* FROM t1 ORDER BY object_id desc ) WHERE rn BETWEEN 10 AND 20 Plan hash value: 1270797092 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 13584 (100)| 11 |00:00:01.64 | 1393 | 7308 | 7308 | | | | | |* 1 | VIEW | | 1 | 77491 | 16M| | 13584 (1)| 11 |00:00:01.64 | 1393 | 7308 | 7308 | | | | | | 2 | SORT ORDER BY | | 1 | 77491 | 15M| 18M| 13584 (1)| 72907 |00:00:01.63 | 1393 | 7308 | 7308 | 11M| 1303K|99328 (11| 10240 | | 3 | COUNT | | 1 | | | | | 72907 |00:00:00.02 | 1044 | 0 | 0 | | | | | | 4 | TABLE ACCESS FULL| T1 | 1 | 77491 | 15M| | 292 (1)| 72907 |00:00:00.01 | 1044 | 0 | 0 | | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$2 / from$_subquery$_001@SEL$1 2 - SEL$2 4 - SEL$2 / T1@SEL$2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') OPT_PARAM('_optimizer_cost_model' 'cpu') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1") NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1") FULL(@"SEL$2" "T1"@"SEL$2") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("RN"<=20 AND "RN">=10)) Note ----- - dynamic sampling used for this statement (level=2) - 컬럼 설명 E-Rows : Optimizer가 예상한 Row 수 E-Bytes : Optimizer가 예상한 Row의 byte E-Temp : Optimizer가 예상한 Temp 사용량 Cost (%CPU) : Optimizer가 예상한 CPU 사용량 A-Rows : 실제 Row 수 A-Time : 실제 시간 Buffers : Logical Reads Reads : Physical Reads Writes : Physical Writes OMem : Optimal execution에 필요한 메모리 1Mem : One Pass execution에 필요한 메모리 Used-Mem : 실제 사용한 PGA 메모리/ 메모리 사용량 뒤의 괄호 안의 숫자가 merge를 수행한 횟수 임. Used-Tmp : 실제 사용한 Temp (kb 단위임) 10240 = 10240kb = 10M Mas-Tmp : 최대 Temp 사용량 (kb 단위임)    

    - 메모리를 늘려서 수행 해 봄 (Optimal Execution이 되도록) SQL> ALTER SESSION SET sort_area_size = 11534336 ; SQL> SELECT /*+ gather_plan_statistics */ * 2 FROM ( SELECT rownum rn , t1.* 3 FROM t1 4 ORDER BY object_id desc ) 5 WHERE rn BETWEEN 10 AND 20 ; SQL> SELECT * 2 FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last rows cost bytes +alias +outline +predicate')) ; PLAN_TABLE_OUTPUT SQL_ID 3gpz090fq0myt, child number 2 ------------------------------------- SELECT /*+ gather_plan_statistics */ * FROM ( SELECT rownum rn , t1.* FROM t1 ORDER BY object_id desc ) WHERE rn BETWEEN 10 AND 20 Plan hash value: 1270797092 ----------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 3781 (100)| 11 |00:00:00.10 | 1044 | | | | |* 1 | VIEW | | 1 | 77491 | 16M| | 3781 (1)| 11 |00:00:00.10 | 1044 | | | | | 2 | SORT ORDER BY | | 1 | 77491 | 15M| 18M| 3781 (1)| 72907 |00:00:00.09 | 1044 | 10M| 1265K| 9685K (0)| | 3 | COUNT | | 1 | | | | | 72907 |00:00:00.02 | 1044 | | | | | 4 | TABLE ACCESS FULL| T1 | 1 | 77491 | 15M| | 292 (1)| 72907 |00:00:00.01 | 1044 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$2 / from$_subquery$_001@SEL$1 2 - SEL$2 4 - SEL$2 / T1@SEL$2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') OPT_PARAM('_optimizer_cost_model' 'cpu') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1") NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1") FULL(@"SEL$2" "T1"@"SEL$2") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("RN"<=20 AND "RN">=10)) Note ----- - dynamic sampling used for this statement (level=2)    

    - 메모리를 줄여서 수행해 봄 (One Pass execution이 되도록) SQL> ALTER SESSION SET sort_area_size = 5767168 ; SQL> SELECT /*+ gather_plan_statistics */ * 2 FROM ( SELECT rownum rn , t1.* 3 FROM t1 4 ORDER BY object_id desc ) 5 WHERE rn BETWEEN 10 AND 20 ; SQL> SELECT * 2 FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last rows cost bytes +alias +outline +predicate')) ; PLAN_TABLE_OUTPUT SQL_ID 3gpz090fq0myt, child number 3 ------------------------------------- SELECT /*+ gather_plan_statistics */ * FROM ( SELECT rownum rn , t1.* FROM t1 ORDER BY object_id desc ) WHERE rn BETWEEN 10 AND 20 Plan hash value: 1270797092 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 3781 (100)| 11 |00:00:00.14 | 1047 | 1123 | 1123 | | | | | |* 1 | VIEW | | 1 | 77491 | 16M| | 3781 (1)| 11 |00:00:00.14 | 1047 | 1123 | 1123 | | | | | | 2 | SORT ORDER BY | | 1 | 77491 | 15M| 18M| 3781 (1)| 72907 |00:00:00.13 | 1047 | 1123 | 1123 | 9M| 1231K| 5086K (1)| 9216 | | 3 | COUNT | | 1 | | | | | 72907 |00:00:00.02 | 1044 | 0 | 0 | | | | | | 4 | TABLE ACCESS FULL| T1 | 1 | 77491 | 15M| | 292 (1)| 72907 |00:00:00.01 | 1044 | 0 | 0 | | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$2 / from$_subquery$_001@SEL$1 2 - SEL$2 4 - SEL$2 / T1@SEL$2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') OPT_PARAM('_optimizer_cost_model' 'cpu') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1") NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1") FULL(@"SEL$2" "T1"@"SEL$2") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("RN"<=20 AND "RN">=10)) Note ----- - dynamic sampling used for this statement (level=2)  

위의 테스트 결과 SORT에 필요한 메모리 양과 사용되어지는 TEMP의 양을 확인 할 수 있다.