3.1. SQL과 옵티마이져 (by nakamori) [2011.08.13]
3장 SQL의 실행 계획(Explain plan)
엑세스 효율에 가장 결정적인 영향을 미치는 것 은 수립된 실행계획이다.
목표 : 그러므로 실행계획을 이해하고 제어하는 것이 중요하다.
3.1 SQL 과 옵티마이져
- 논리적으로 처리 가능한 경로는 (인덱스, 클러스터, 옵티마이져 모드, 수립된 통게정보, SQL 문장과 형태 , 시스템 및 네트워크 상태 등) 여러가지에 의해 종합적으로 감안하여 옵티마이져가 실행계획을 수립한다.
- 옵티마이져 버전에 따라 실행게획에 반영되는 통계정보의 차이는 있겠지만 이 중에서는 선택성(Selectivity)에 가장 중요한 미치는 정보는 컬럼값들에 대한 차별적인 분포도(밀도)와 엑세스의 분류 단가 즉 클러스터 팩터 부분이다.
Clustering Factor 란 Index 의 Table에 대한 정렬 정도(orderedness)이다.
이것은 index를 scan하는 동안 방문(access) 하게되는 Table의 Data Block 의 개수이다.
즉 넓은 범위의 Data를 Index를 경유해서 읽을 경우 Clustering Factor 가 Physical Reads 의 발생 빈도에 큰 영향을 미친다.
- 옵티마이저가 가장 실행계획을 수립하는데 절대적으로 영향은 미치는 것은 가장 최소량을 처리할 수 있도록 하는 것 과 가장 싼값으로 엑세스 할 수 있는냐가 가장 큰 요소이다
3.1.1. 옵티마이져와 우리의 역할
- SQL 이라는 언어로 요구를 하면 DBMS는 최적의 경로(자원)를 사용하여 결과를 보여준다. 여기서 최적의 경로를 계산하는 부분이 옵티마이져라고 역활이다.
- RDBMS 의 여러가지 특징 중 하나가 물리적 연결고리가 없어도 논리적 연결고리만 있으면 원하는 데이터를 찾을 수 있다는 점이 중요하다. 이것은 여러가지 논리적 연결고리가 생성 될 수 가 있다는 점이며 여기서 가장 효율적인 연결고리를 찾아 데이터를 엑세스 하는 점이 포인트 이다
- DBMS 을 사용하는데 있어서 옵티마이져를 몰라도 SQL을 질의 한 뒤 원하는 결과를 얻는데는 문제는 없다. 단 원하는 결과가 나오는데 까지 있어서 어떠한 비용(시간, 자원)을 지불 하였는지 알지 못 한다면 이 옵티마이져가 효율적으로 일하는지 판단하기 어렵다. 특히 소량의 결과를 처리할 때 더욱 그렇다.
- 사용자가 준비해야 두어야 할 기본적인 옵티마이징 팩터는 인덱스 구성에 대한 전략과 적절한 SQL 을 작성하는 것이다. 그러나 적절한을 판단하기 위해서는 실행계획을 이해하고, 제어하기 위해서는 옵티마이져를 알아야한다.
- SQL 는 비절차형 언어이기 때문에 적절한 SQL를 작성하기 위해서는 집합적 사고가 필요.
3.1.2 옵티마이져의 형태
- 규칙기준 옵티마이져 (RBO，Rule Based Optimizer): 엑세스를 위해 사용할 도구 인덱스 , 클러스터링 등 상태와 사용된 연산자의 형태에 따라 순위를 부유하는 방식
- 비용기준 옵티마이져 (CBO，Cost-Based Optimizer) : 통계 정보를 바탕으로 실제의 Cost 비용을 산출하여 비교하여 최소비용을 드는 방식 선택.
- Optimizer 를 어느정도 판단할 수 있다면 SQL 를 작성할 때 어떤 실행계획이 가장 유리한지 알 수 있으며 또한 예기치 않은 부하가 발생했다면 옵티마이져가 어떤 실수를 했는지 쉽게 찾아낸다
3.1.2.1 규칙기준 옵티마이져 (RBO，Rule Based Optimizer)
- 규칙기반 옵티마이져는 인덱스 구조나 비교연산자에 따라 순위를 부여하여 이것을 기준으로 최적의 결정을 결정한다.
01 > ROWID 로 1 로우 액세스
02 > 클러스터 조인에 의한 1 로우 액세스
03 > Unique HASH Cluster 에 의한 1 로우 액세스
04 > Unique Index 에 의한 1 로우 엑세스
05 > Cluster 조인
06 > Non Unique Hash cluster key
07 > Non Unique cluster key
08 > Non Unique 결합 인덱스
09 > Non unique 한 컬럼 인덱스
10 > index 에 의한 범위처리
11 > index 에 의한 전체범위처리
12 > Sort Merge 조인
13 > 인덱스 컬럼의 Min, MAX 처리
14 > 인덱스 컬럼의 order by
15 > 전체테이블 스켄
어떤 유형의 인덱스를 어떤 연산자를 사용하였는지 순위차이는 있다.
질의 최적화(query optimization)에서 RBO(Rule Base Optimizer)는 정해진 랭킹(ranking)에 의해 플랜을 결정한다. 같은 랭킹이라면 Where
절의 뒤부터, From절 뒤의 객체가 우선 순위를 갖는다. 한 객체(예 : 테이블)에서 같은 랭킹의 인덱스가 있다면 가장 최근에 만들어진 인덱스를 사용한다. 이는 CBO(Cost Base Optimizer)에서도 같이 적용되는 사항이다.
가) 규칙기준 옵티마이져 장점
- 사용자가 문제점을 미리 예측하고 자신이 원하는 방법으로 실행계획을 제어하기가 쉽다
즉 전력적인 인덱스를 구성하여 SQL 를 구성 하였을 경우 다른 요소에 실행계획이 변동되는 변수가 적다.
나) 규칙기준 옵티마이져 단점
- 현실적인 요소를 무시하여 개산하므로 판단 오차가 크게 나타날 수 있음.
SQL> create index emp_a on emp(deptno);
Index created.
SQL> create index emp_b on emp(deptno, empno);
Index created.
SQL> select /*+rule */* from emp where deptno =10 and empno between 7888 and 8888;
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP |
|* 2 | INDEX RANGE SCAN | EMP_A |
---------------------------------------------
09 > Non unique 한 컬럼 인덱스 EMP_A index
10 > index 에 의한 범위처리 EMP_B index
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"<=8888 AND "EMPNO">=7888)
2 - access("DEPTNO"=10)
위 경우 이상적인 index를 타는 경우는 EMP_B index를 타는 것이다.
access("DEPTNO"=10 AND "EMPNO">=7888 AND "EMPNO"<=8888)
3.1.2.2 비용기준 옵티마이져 (CBO，Cost-Based Optimizer)
- 통계 정보를 바탕으로 실제의 Cost 비용을 산출하여 비교하여 최소비용을 드는 방식 선택
- 통계 정보 형태, 종류 DBMS, Version 에 따라 차이는 있지만 보통 테이블의 로우 수 , 블록 수 ,
블록당 평균 로우수, 로우의 평균길이, 컬럼별 상수값의 종류, 분포도, 컬럼 내 NULL 값의 수,
클러스터링 팩터, 인덱스의 깊이, 최소 최대 값, 리프 블럭 수, 가동시스템의 I/O, CPU 사용정보 등
가지고 있다.
[USER|ALL|DBA]_TABLES
num_rows, blocks, avg_row_len, sample_size, last_analyzed
[USER|ALL|DBA]_INDEXES
BLEVEL ,LEAF_BLOCKS ,DISTINCT_KEYS ,AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR,NUM_ROWS,SAMPLE_SIZE ,LAST_ANALYZED
[USER|ALL|DBA]_TAB_COLUMN
NUM_DISTINCT,LOW_VALUE ,HIGH_VALUE,DENSITY , NUM_NULLS ,NUM_BUCKETS , LAST_ANALYZED ,SAMPLE_SIZE ,AVG_COL_LEN
[USER|ALL|DBA]_TAB_HISTOGRAMS
OWNER , TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER , ENDPOINT_VALUE , ENDPOINT_ACTUAL_VALUE
가) 비용기준 옵티마이져의 장점
- 최대의 장점은 현실을 감안한 판단을 할 수 있다는 것.
통계정보 중 가장 중요한 정보는 분포도 라고 할 수 있으며, 여기서 분포도가 좋다라는 것은 컬럼값의 종류가 많다는 것이며
이것은 곧 조건을 만족하는 처리 범위가 좁다는 의미하므로 일의 양을 결정하는 중요한 요소 이다.
여기서 문제는 분포도 라는 정보가 결합인덱스 또는 다양한 연산자를 사용하는 경우 불완전한 분포드 정보가 나올 가능성이 높다는 점이다.
모든 컬럼 값에 대해 분포도를 가지기에는 어려움이 있으므로 값을 저장하는 방식에는 두가지 있다.
넓이 균형 히스토그램(Width_balanced Histogram) : 값(Value)의 Distinct Count와 Bucket 수가 일치하는 경우 정확한 컬럼 개수 값을 가지고 있다.
높이 균형 히스토그램(Height_balanced Histogram) : 값(Value)의 Distinct Count가 Bucket 수보다 많은 경우 값마다 Bucket을 하나씩 할당
할 수 없으므로 값들을 적당한 Bucket으로 분배
-- 참조 : 2011년 상반기 대용량데이터베이스 스터디 Frequency vs. Height-Balanced (작성자 : 노준기)
- 통계정보의 관리를 통해 최적화를 제어 할 수 잇다.
통계정보를 관리 함으로서 테이블을 모니터링 할 수가 있다. Insert, update, delete 에 대한 발생 정보를 개략적으로 모니터링 가능.
통계정보관리는 제공된 프로시져를 이용하여 관리하는 것이 좋다.
- 옵티마이져를 깊이 이해하지 있지 않더라도 최소한의 성능이 보장된다.
h6.나) 비용기준 옵티마이져의 단점
- 실행게획을 미리 예측하기가 어렵다.
- 버젼에 따라 변화가 심하다.
- 실행계획의 제어가 어렵다.
다) 옵티마이져의 발전 방향
- 옵티마이져가 발전해 가는 방향은 비용기반으로 발전 해 가고 있다.
1992년 Oracle 7에서 CBO가 지원되면서 CBO는 계속적인신기능의 적용으로
발전해 온 반면, RBO는 더 이상의 기능 향상은 없으며,향후는CBO만 지원될 계획이다.
라) 통계정보 관리를 위한 제언
- 자세한 TEST 내역은 2011 상반기 Optimizing Oracle Optimizer 에 나와 있습니다.
- 통계정보를 관리하기 위해서 DBMS 제공하는 많은 편리한 패키지들이 업데이트 되고 있다.
- 시스템 통계정보 수집.
시스템의 CPU, I/O 성능, 메모리성능, 등 성능에 따른 변수가 있을 수 있으므로 시스템 통계를 측정하여 좀 더 정확한
정보를 옵티마이져에게 제공함으로써 좀 더 정확한 통계정보를 가질 수 있다.
시스템 통계정보는 지정한 기간동안 시스템을 모니터링 함으로써 측정하는 부분이다.
(일반적으로 업무시간을 위주로 어느정도 시간의 Delta 값을 측정하는지..)
SYSTEM 통계 정보_info
select * from aux_stats$;
수집
SQL> exec dbms_stats.gather_system_stats(gathering_mode => 'START');
~~~~ 이 시기 동한 발생된 워크로드를 분석해서 aux_stats$ 반영
SQL> exec dbms_stats.gather_system_stats(gathering_mode => 'STOP');
삭제
SQL> exec dbms_stats.delete_system_stats();
- 통계정보 수집.
- 통계 정보 수집하는 경우 주의점은 Sample Data 의 양이다.
일반적으로 5% 이하 선정하는데 바람직하다고 한다. 하지만 데이터 양의 따라 제약조건이 있으므로 검토가 필요.
Analyze & DBMS_STAT 차이점
Analyze | Serial statistics gathering 기능
전체 클러스터 테이블에 대한 통계정보 수집 가능
각 테이블 파티션, 인덱스에 대해서 수집하고 , Global statistics 는 파티션 정보를 가지고 계산( 비정확 )
Analyze 에서만 가능한 기능.
Structural integrity check ( analyze (index/table/cluster) (schema.)(index/table/cluster) validate structure (cascade)(into schema table)
Chained rows 수집 ( analyze table order_hist list chained rows into <user_tabs> )
DBMS_STAT | Parallel statistics gathering 기능
Partition , sub partition 이 있는 경우에는 DBMS_STAT 이용
전체 클러스터 테이블에 대한 통계정보 수집 불가
DBMS_STAT 는 CBO 관련 통계만 수집. (즉 EMPTY_BLOCKS ,AVG_SPACE, CHAIN_CNT 수집 않함)
통계정보를 저장가능, 각 컬럼, 테이블, 인덱스, 스키마 반영가능
DBMS_STAT import /export 가능
- 통계정보 비교.
Oracle 10.2.0.4 통계정보에 DBMS_STATS Package 으로 제공.
* 통계정보를 비교시 일반적으로 현재,과거 를 비교하므로 과거의 통계정보의 백업(export)가 필요하며
자동적으로 백업되는 부분은 DBA_TAB_STATS_HISTORY View 조회된다 (10g 부터)
dbms_stats.diff_table_stats_in_stattab(user,'table1','table1_old');
- 통계정보 백업.
exec dbms_stats.export_table_stats(user,'t1',null,'t1_stat');
exec dbms_stats.import_table_stats(user,'t1',null,'t1_stat');
SQL> exec dbms_stats.gather_table_stats('JIN','EMP');
PL/SQL procedure successfully completed.
SQL> select table_name, stats_update_time from dba_tab_stats_history where table_name = 'EMP'
TABLE_NAME STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
EMP 28-JUL-11 10.00.17.072692 PM +09:00
EMP 25-AUG-11 11.35.21.381488 AM +09:00
SQL> select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where OWNER='JIN' and TABLE_NAME='EMP';
OWNER TABLE_NAME LAST_ANALYZE
------------------------------ ------------------------------ ------------
JIN EMP 25-AUG-11
SQL> exec dbms_stats.restore_table_stats('JIN','EMP','28-JUL-11 10.00.17.072692 PM +09:00');
PL/SQL procedure successfully completed.
SQL> select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where OWNER='JIN' and TABLE_NAME='EMP';
OWNER TABLE_NAME LAST_ANALYZE
------------------------------ ------------------------------ ------------
JIN EMP 28-JUL-11
3.1.2.3 옵티마이져 목표(Goal)의 선택
h6.가) 옵티마이져 모드의 종류
- First_rows
- First_rows_n
- All_rows
- CHOOSE
저자는 default mode 인 All_rows 보다는 First_rows 를 기본 모드를 권장하고 있다.
이유는 대부분의 시스템은 OLTP 업무가 보다 많은 비중을 차지하고 있으며, 온라인 화 면을 통한 처리는 화면의 크기에 제한이 있고,
사람의 인지능력에도 한계가 있으므로 대부분 일단 일정량이 먼저 나타나는 형태이다.
first_row 로 지정했다고 해서 전체범위를 처리하지 않은 것은 아니며, 모드에 따라서 항상 다른 실행계획이 나타나는것은 더욱 아니다.
논리적으로 전체 범위를 엑세스해야만 결과를 제공 할 수 있다면 옵티마이져 모드가 어떤 것이든 거의 동일한 실행계획을 제공한다.
그러나 부분 범위로 처리되는 SQL 이 만약 ALL_ROWS 모드로 최적화 하게 되면 전체결과를 최적화 하는 방식의 실행계획이 나타나는 경우가 많이 있다.
- 옵티마이져 모드가 CHOOSE 인 경우 통계정보가 있는 경우 CBO 로 풀리며, 없는 경우 RBO 로 풀리는 모드이다.
- 하지만 아래 같은 경우에는 통계정보가 없더라도 무조건 CBO 로 풀린다.
Partitioned tables
Index-organized tables
Reverse key indexes
Function-based indexes
SAMPLE clauses in a SELECT statement
Parallel execution and parallel DML
Star transformations
Star joins
Extensible optimizer
Query rewrite (materialized views)
Progress meter
Hash joins
Bitmap indexes
Partition views (release 7.3)
Hint
Parallel DEGREE & INSTANCES - 'DEFAULT'도 해당
h6.나) 옵티마이져 모드의 결정 기준
OLTP 환경 (CHOOSE :9i) > First_rows
신규 OLTP 환경 > First_rows_n
OLAP 환경 > ALL_ROWS
물론 절대적인 기준은 아니다. 다만 저자는 RDBMS 사용하는 목적에 따라서 위와 같은 옵티마이져가
적절하다고말하고 있다.
- 문제는 동작 방식이 First_rows_n 동작 방식이 버젼에 따라 (10gR2) Rule Based logic 이 추가 되었다는 점이다.
- 옵티마이져모드를 운영중에 바꾸는것은 매우 위험하다.
모드를 변경하거나 업그레이드로 인한 변경된 옵티마이져를 적용 할 시 충격을 줄이는 대책
옵티마이져 버젼을 과거에 사용하던 버젼으로 지정하는 방법
Parameter OPTIMIZER_FEATURES_ENABLE
Modifiable ALTER SESSION, ALTER SYSTEM
해당 값을 변경되면 옵티마이져 관련된 Hidden Parameter 도 적용된다.
다만 상위 버젼의 개선된 값이 False 되는 것과 대표적인 값만 바뀐다.
오히러 사라진 Parameter 도 있기 때문에 완벽한 호환성은 아니다.
기존 구 버젼의 Parameter 도 최적화로 instance 튜닝이 되어 있다면 거기에 대한 값도 사용자가 고려해서 반영해야 된다.
아래는 같은 11.2.0.1 > 11.1.0.7 옵티마이져 기능을 바꾸는 경우 추가적으로 변경이 Parameter 도 있다.
PARAMETERS TO CHANGE 11.2.0.1 TO 11.1.0.7 [ID 1096377.1]
These are the parameters that are changed when setting optimizer_features_enable=11.1.0.7 in 11.2.0.1 database.
These values represent OFE=11.1.0.7
alter session set "_optimizer_coalesce_subqueries" = false ;
alter session set "_optimizer_distinct_placement" = false ;
alter session set "_optimizer_unnest_disjunctive_subq" = false ;
alter session set "_optimizer_unnest_corr_set_subq" = false;
alter session set "_optimizer_distinct_agg_transform" = false;
alter session set "_optimizer_fast_pred_transitivity" = false ;
alter session set "_optimizer_fast_access_pred_analysis" = false;
alter session set "_aggregation_optimization_settings" = 32 ;
alter session set "_optimizer_eliminate_filtering_join" = false;
alter session set "_optimizer_join_factorization" = false ;
alter session set "_optimizer_use_cbqt_star_transformation" = false ;
alter session set "_optimizer_connect_by_elim_dups" = false ;
alter session set "_connect_by_use_union_all" = old_plan_mode;
alter session set "_optimizer_table_expansion" = false;
alter session set "_and_pruning_enabled" = false ;
alter session set "_optimizer_use_feedback" = false ;
alter session set "_optimizer_try_st_before_jppd" = false ;
alter session set "_optimizer_undo_cost_change' = '11.1.0.7' ;
- 버젼에 따른 옵티마이져 기능
- 실행계획 요약본을 생성하여 이를 참조하게 하는 아우트라인 활용 (Outline)
h6.다) 옵티마이져 모드와 관련된 파라메터 지정
주의 할 점은 Parameter 들이 통계정보에 미치는 영향이 크기 때문에 신중한 검토가 필요하다.
- CURSOR_SHARING
cursor_sharing = (EXACT(default)| FORCE | SIMILAR )
기본값인 EXACT 는 대소문자, 공백 , 비교되는 상수값이 다르면 공유하지 못한다.
그러므로 상수값만 다른 Dynamic SQL 이 루프내에서 돈다면 파싱 부하가 발생.
(sql공유에 문제가 있는 경우Session level 에 적용을 권장.)
- DB_FILE_MULTIBLOCK_READ_COUNT
db_file_multiblock_read_count
Full_TABLE_SCAN, INDEX_FAST_FULL_SCAN 을 할때 한번에 읽는 블럭의 수 대용량의 배치위주로 작업한다면 늘려주는것이 좋다.
이 파라미터의 수치가 클수록 인덱스 스캔보다는 풀 테이블 스캔의 비중이 높아진다. 옵티마이저의 플랜 결정에 민감하게
영향을 주는 값이며 값이 커지면 풀 테이블 스캔과 병행해서 Sort Merge Join 또는Hash Join의 경향이 커진다.
- OPTIMIZER_INDEX_CACHING
CBO가 Nested Loop Join ,IN-LIST 탐침을 선호하도록 조절하는 파라미터, Nested Loop Join시 버퍼 캐쉬 내에 이너 테이블의
인덱스를 캐쉬화하는 비율(%)을 지정하므로 Nested Loop Join시 성능이 향상되며, 옵티마이저는 비용 계산시 이 비율을
반영하여 Nested Loop Join을 선호하도록 플랜이 선택된다(0~100). 100에 근접할수록 인덱스 액세스 경로가 결정될
가능성이 높다. 기존의 RBO 를CBO로 전환시 옵티마이저를RBO 성향으로 보정하는 데 효과적이다.
- OPTIMIZER_INDEX_COST_ADJ
옵티마이저가 인덱스를 사용하는 위주의 플랜으로 풀릴 것인지 또는 가능한 사용하지 않을 쪽으로 풀릴 것인지의 비중을 지정한다.
CBO는 RBO처럼 인덱스를 사용하도록 플랜이 주로 만들어지게 되나, 인덱스가 있다고 해서 RBO처럼 인덱스를 이용한 플랜으로
처리되는 것은 아니다. 인덱스를 이용하는 플랜 위주로 하고자 한다면 100(%) 이하를, 가능한 인덱스를 사용하지 않고자 한다면
100 이상을 지정한다(1 ~ 10000). 이 파라미터는 기존의 RBO를 CBO로 전 환시 옵티마이저를 RBO의 인덱스 위주
성향으로 보정하는 데 효과적이다.
즉 Index Scan Cost 를 낮춤으로써 Single Block I/O 와 Multi Block I/O Cost 간의 불균형을 해소 하는 역활.
- optimizer_dynamic_sampling
OPTIMIZER_DYNAMIC_SAMPLING (1~10)
(Default = 1(Oracle9i Database),2(Oracle Database 10g))
통계정보가 없는 겨우 소량의 표본을 동적으로 추출하여 통계정보로 활용함을 말함.
더 나은 플랜을 결정하기 위한 목적으로 더 정확한 Selectivity & Cardinality 를 구하기 위한 방법으로 0 ~ 10 레벨이 있으며,
레벨이 높을수록 SQL 문장의실행 시점에 통계정보를 만들기 위해 테이블의 데이타를 샘플링하기 위한 추가적인 Recursive SQL이 발생된다.
DYNAMIC_SAMPLING(0 ~ 10) 힌트를 통해서도 같은 기능을 할 수 있다. 그러나 내부적으로 추가적인 테이블 액세스의 비용이
발생하므로OLTP에서는 주로 사용하지 않는다. 특히 OLTP 환경에서 레벨을 디폴트 값 이상 높여 놓지 않도록 한다.
Oracle Database 10g의 경우 통계정보가 없다면'다이나믹 샘플링'이 적용된다.
- WORKAREA_SIZE_POLICY (AUTO | MANUAL)
옵티마이져 가 (HASH |SORT|BITMAP_MERGE|CREATE_ BITMAP) *_AREA_SIZE를 자동으로 결정하는 PGA 자동 관리 방식으로,
인스턴스에속한 모든PGA의 메모리의 합이PGA_AGGREGATE_TARGET에서 설정된메모리를 가능한 넘지 않는 범위 내에서
Workarea(Sort, Hash, Bitmap 등)를 충분히 사용하고자 하는 방식이다.
플랜은 할당된 Workarea를 가지고 플랜을 결정하게 되므로 풍부한 메모리에 의해 Hash Join, Sort Merge Join등을
선호하는 경향이 높다. 내부적으로 히든 파라미터로*_AREA_SIZE의값을가지 고 플랜을 결정할 수도 있으나 인위적인
설정 없이는 자동 할당된 메모리로 플랜이 결정된다.
- HASH_AREA_SIZE, HASH_JOIN_ENABLED
(Oracle Database 10g : _hash_join_enabled=true)
위의 파라미터 값에 따라서 Hash Join으로유도할수있다. Hash Join이 가능하고 해쉬 메모리가 충분하다면, 플랜에 Hash Join의 경향이 커진다
- SORT_AREA_SIZE , SORT_MULTIBLOCK_READ_COUNT
위의 파라미터의 값에 따라서 Sort Merge Join으로 유도할 수 있다. 소트 메모리가 충분하다면, 플랜에 Sort Merge Join의 경향이 커진다.
HASH JOIN 으로 유도 할 수 있음. (size 가 충분하면 플랜의 hash join 의 경향이 커짐)
- OPTIMIZER_SEARCH_LIMIT (Default = 5)
옵티마이저에게 조인 비용을 계산할 경우, From절에 나오는 테이블의 개수에 따라서 조인의 경우의 수가 있을 수 있으며, 옵티마이저는 이들 각각의 경우의
수에 대한 조인 비용을 계산하게 된다. 물론 일부 예외사항은 있다. 예를 들어,Cartesian Production Join 등은 우선 순위가 낮으므로 뒤로 미뤄질 것이다.
이 파라미터의 값이 5일 경우 From절에 5개의 테이블에 대해서 모든 조인의경우의 수를 가지고 비용을 계산하게 되며, 그 개수는 5!=120개의 경우의 수에
대한 조인 비용을 계산하게 되므로 옵티마이저가 많은 시간을 소모하게 되므로 성능에 영향을 미칠 수도 있다.
- OPTIMIZER_PERCENT_PARALLEL (Default = 0)*
Optimizer_Percent_Parallel의 Parameter는 CBO가 비용을 계산하는 데 영향을 주는 파라미터이다. 즉 수치가 높을수록 병렬성을 이용하여 풀 테이블
스캔으로 테이블을 액세스하려고 한다. 이 값이 0인 경우는 최적의 시리얼 플랜이나 패러렐 플랜을 사용하며, 1~100일 경우는 비용 계산에서 객체의 등급
을 사용한다.
출처: Oracle Database Reference