업그레이드에 COMPATIBLE설정하기

• COMPATIBLE매개변수는 데이터베이스에서사용가능한 기능과 함수를지시한다.

SQL>show parameter compatible
	NAME                                 TYPE        VALUE
	------------------------------------ ----------- ------------------------------
	compatible                           string      10.2.0.1.0
	--> 10.2.0.4 Version이 설치되어 있다고 하더라도 그 기능을 사용할 수 없음

• 일반적으로 COMPATIBLE은 UPGRADE(9.1.0->9.2.0)시에 COMPATIBLE=9.1.0.1로 두게 되면, 새로운 데이터베이스에서는 Release 2의 새로운 버전을 사용 불가. Test가 완료된 이후에 COMPATIBLE을 수정.
• (이유->상위버전에서 하위버전으로 되돌리는건 불가능 하므로)

전체 스캔의 비용을 줄이기 위하여 DB_FILE_MULTIBLOCK_READ_COUNT 설정하기

• Table Full Scan 또는 신속한 Index Full Scan을 수행하는 동안 I/O를 통해 읽을 수 있는 블록의 수를 제어

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16

SQL> set autotrace traceonly explain
SQL> select * from big_table;

Execution Plan
----------------------------------------------------------
Plan hash value: 3993303771

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  3891K|  7492M|  7168   (2)| 00:01:27 |
|   1 |  TABLE ACCESS FULL| BIG_TABLE |  3891K|  7492M|  7168   (2)| 00:01:27 |
-------------------------------------------------------------------------------
SYS@BLOG8DB >ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=32;

Session altered.

SYS@BLOG8DB >set autotrace traceonly explain
SYS@BLOG8DB >select * from scott.big_table;

SQL> ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=32;

Session altered.

SQL> select * from scott.big_table;

Execution Plan
----------------------------------------------------------
Plan hash value: 3993303771

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  3891K|  7492M|  6327   (2)| 00:01:16 |
|   1 |  TABLE ACCESS FULL| BIG_TABLE |  3891K|  7492M|  6327   (2)| 00:01:16 |
-------------------------------------------------------------------------------

• DB_FILE_MULTIBLOCK_READ_COUNT가 높게 설정되면 전체 스캠의 비용이 낮아진다. 이 경우 전체 스캔이 인덱스 액세스보다 더 유리하게 된다.
• DSS혹은 DW 환경에서는 시스템이 허용하는 최대 I/O 크기로 설정하는 것이 바람직하고, Full Table Scan이 빈번하게 발생하지 않는 트랜잭션 시스템에서는 낮은 설정이 적합할 수 있다.

• 다중 블록 크기 데이터베이스를 지원하는 Oracle 9i에서는 이 매개변수가 실제로 블록 읽기의 수가 아닌 I/O크기를 설정하는 데 사용된다.
• 오라클은 db_file_multiblock_read_count에 기본 블록 크기(SYSTEM 테이블 영역의 블록 크기)를 곱하여 나온 수를 시스템의 I/O크기로 사용된다.
• 기본 블록 크기가 8K이고 이 매개변수가 16으로 설정되었다면 16K블록의 경우는 8개의 블록 밖에 읽을 수 없다.

HASH_JOIN_ENABLED를 설정하여 해시 조인 제어하기

• HASH_JOIN_ENABLED => 해쉬 조인을 활성화 하거나 이 기능을 해제
• 이 매개 변수를 TRUE로 설정하는 것이 바람직.
• HASH_AREA_SIZE를 부적절하게 설정하면 해시 조인 자체의 속도가 저하되므로 => 9i이상 버전에서는 PGA_AGGREGATE_TARGET 매개변수를 이용하여 이 문제가 발생되지 않도록 한다.
• 8i이하 또는 Shared Server의 경우일 경우는 HASH_AREA_SIZE를 완전히 무시하는 것보다는 적합한 통게를 수집하는 것이 유리

OPTIMIZER_DYNAMIC_SAMPLING을 설정하여 동적으로 통계 수집하기

• CBO는 쿼리의 모든 객체에 대해 수집한 통계에 의존하지만 통계가 없는 객체에 대해서는 기본 통계를 만든다.
• 하지만 통계가 있는 객체와 없는 객체가 섞인 쿼리에서는 이들 기본값이 사용되어 Optimizer가 잘못된 계획을 선택 할 수 있다.
• 이 Parameter를 설정하면 Optimizer는 쿼리에 참조된 객체에 대한 통계를 충분히 수집하여 의미 있는 정확한 계획을 만든다.
  
  

OPTIMIZER_FEATURES_ENABLE을 설정하여 기능 성택하기

• 쿼리를 최적화 할때에 optimizer가 고려해야 할 기능과 함수를 제어한다.
• 업그레이드를 허용하면서도 테스트 과정에서 Optimizer가 이전 릴리즈에서처럼 동작할 수 있도록 해 주지만 새 릴리즈에서도
• 이전 릴리즈에서 선택되었던 계획이 선택되다는 것을 보장하지는 않는다.(릴리즈 마다 비용할당하는 방식이 다르므로)
• Ex)OPTIMIZER_FEATURES_ENABLE=8.1.4로 설정시 pl/sql에서 실행되는 사용자가 작성한 재귀 SQL이 SESSION Optimizer 모드를 사용하지 않고 항상 CHOOSE를 사용
• OPTIMIZER_FEATURES_ENABLE=8.1.6로 설정시 사용자가 작성한 재귀 SQL이 세션으로부터 Optimizer 목표를 상속받는 데 이용될 수 있는 기능이 추가.

OPTIMZER_MAX_PERMUTATIONS를 설정하여 순열 제어하기

• 최적화기가 고려할 조인 순서의 수에 영향을 미친다.
• 기본값은 OPTIMIZER_FEATURES_ENABLED 설정 따라 달라짐 9.0.0 이하는 기본값 80000, 그렇지 않은 경우는 2000
• Parsing에 너무 오랜 시간이 걸리는 쿼리를 가지고 있는 경우에만 Parse 시간을 줄이기 위하여 이 설정을 고려해 볼 수 있다. 그럭나 일반적으로는 기본값이 설정된 채로 이 매개변수를 둔다.

OPTIMIZER_MODE를 설정하여 모드 선택하기

OPTIMIZER_MODE 설정은 오라클에세 두 가지를 알려준다

1. 사용하고자 하는 optimizer (RBO,CBO)
2. 쿼리 최적화 방법(ALL_ROWS, FIRST_ROWS)

CHOOSE 모드

• OPTIMIZER_MODE 가 CHOOSE 설정되면 CBO 또는 RBO를 사용한다.

다음과 같은 조건이 모두 만족되면 RBO를 사용한다.

1. 쿼리에 참조된 객체가 모두 통계를 가지고 있지 않다.
2. 쿼리에 참조된 객체가 하나도 CBO를 요청하지 않았다. 예를 들면 IOT, 분할된 테이블, 그리고 병렬 처리의 수준이 기본값이 아닌 테이블은 CBO를 필요로 한다.
3. CBO를 필요로 하는 구조물을 사용하지 않았다. 예를 들면 도메인 인덱스(Oracle Text, interMedia라고도 함). 그리고 특정 ANSI 조인 구조물은 CBO를 필요로 한다.
4. 쿼리에 CBO 힌트가 포함되어 있지 않다. 엑세스 경로(전체, 해시, 기타 등등)를 지정한 모든 힌트, 쿼리 변환(통합, 다시쓰기, 기타 등등), 조인 순서, 조인 작업(중첩된 루프, 해시, 기타 등등), 또는 병렬 처리는 CBO의 힌트이다. 쿼리는 특정 힌트(예를 들면, /*+ rule /, /+ append */)를 포함하면 RBO를 사용한다.

RULE모드

다음 조건을 모두 만족할때 RBO를 호출한다.

1. 쿼리에 참조된 객체가 CBO를 전혀 필요로 하지 않음
2. CBO를 필요로 하는 구조물이 사용되지 않음
3. 쿼리에 CBO 힌트가 포함되지 않음

ALL_ROWS모드

• 전체 처리량을 최적화 (마지막 행을 가능한 빨리 얻음)

FIRST_ROWS모드

• 초기 응답 시간을 최적화 (첫번째 행을 가능한 빨리 얻음)

QUERY_REWRITE_ENABLED와 QUERY_REWRITE_INTEGRITY를 이용하여 쿼리 다시 쓰기

• Query Rewrite
• 데이터베이스 내에 있는 대체 객체를 이용하여 사용자 쿼리를 완전히 다르지만 동일한 쿼리로 고쳐 쓸 수 있는 능력을 제어한다.

• Query Rewrite시 필요한 파라미터

 
OPTIMIZER_MODE : all_rows,first_rows,choose(통계치정보가있어야함)
QUERY_REWRITE_ENABLED : {true(default)|false|force}
- true - Cost-based Rewrite
- false - Rewrite없음
- force - 강제 Rewrite
QUERY_REWRITE_INTEGRITY
- stale_tolerated - 데이터 일관성이 보장되지 않아도 query rewrite수행.
- trusted - 데이터 일관성이 보장된다고 가정하고 query rewrite수행.
- enforced(default) - 변경전 sql과 변경후 sql의 결과값이 같다는 것을 옵티마이져가 확신하면
query rewrite수행.

select b.prod_name,c.CUST_FIRST_NAME||' '||c.CUST_LAST_NAME NAME,a.QUANTITY_SOLD
from sales a,products b,customers c
where a.prod_id=b.prod_id and a.cust_id=c.cust_id;
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 54M| | 1943 (5)| 00:00:24 | | |
|* 1 | HASH JOIN | | 918K| 54M| | 1943 (5)| 00:00:24 | | |
| 2 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | | 3 (0)| 00:00:01 | | |
|* 3 | HASH JOIN | | 918K| 28M| 1736K| 1924 (5)| 00:00:24 | | |
| 4 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1083K| | 331 (2)| 00:00:04 | | |
| 5 | PARTITION RANGE ALL| | 918K| 10M| | 431 (10)| 00:00:06 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 10M| | 431 (10)| 00:00:06 | 1 | 28 - 6|
----------------------------------------------------------------------------------------------------------

Create materialized view m_sale_list
Build immediate
Refresh
Complete
On demand
Enable query rewrite
As
select b.prod_name,c.CUST_FIRST_NAME||' '||c.CUST_LAST_NAME NAME,a.QUANTITY_SOLD
from sales a,products b,customers c
where a.prod_id=b.prod_id and a.cust_id=c.cust_id;

select b.prod_name,c.CUST_FIRST_NAME||' '||c.CUST_LAST_NAME NAME,a.QUANTITY_SOLD
from sales a,products b,customers c
where a.prod_id=b.prod_id and a.cust_id=c.cust_id;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 805K| 55M| 1405 (3)| 00:00:17 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| M_SALE_LIST | 805K| 55M| 1405 (3)| 00:00:17 |

단, 옵티마이져가 판단시, query rewrite를 수행한 실행계획보다 옵티마이져가 세운 실행계획의 cost가 더 낮다면, 기본적으로 query rewrite를 수행하지않는다.

Select b.prod_name,a.QUANTITY_SOLD
from sales a,products b,customers c
where a.prod_id=b.prod_id and a.cust_id=c.cust_id;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 41M| 750 (48)| 00:00:09 | | |
| 1 | NESTED LOOPS | | 918K| 41M| 750 (48)| 00:00:09 | | |
|* 2 | HASH JOIN | | 918K| 36M| 450 (13)| 00:00:06 | | |
| 3 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | 3 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL| | 918K| 10M| 431 (10)| 00:00:06 | 1 | 28 |
| 5 | TABLE ACCESS FULL | SALES | 918K| 10M| 431 (10)| 00:00:06 | 1 | 28 |
|* 6 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | 5 | 0 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------
Query rewrite 힌트를 준경우

select/*+rewrite*/ b.prod_name,a.QUANTITY_SOLD
from sales a,products b,customers c
where a.prod_id=b.prod_id and a.cust_id=c.cust_id;

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 918K| 35M| 1409 (3)| 00:00:17 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| M_SALE_LIST | 918K| 35M| 1409 (3)| 00:00:17 |

Custermers의 fist_name,last_name 컬럼을 빼고 수행한경우는 query rewrite를 이용하지 않은 경우가 cost가 더 낮으므로 옵티마이져가 query rewrite를 이용하지 않았다.이 경우는 QUERY_REWRITE_INTEGRITY 옵션을 바꾸어도 동일하게 적용되었다.
c.gender컬럼이 추가된 경우에는 모드,옵션에 상관없이 query_rewrite를 이용하지 않았다.

Select b.prod_name,a.QUANTITY_SOLD
from sales a,products b,customers c
where a.prod_id=b.prod_id and a.cust_id=c.cust_id;
query_rewrite_enable모드가 force이므로 cost가 높더라도 query_rewrite를 이용한다.
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 934K| 25M| 1409 (3)| 00:00:17 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| M_SALE_LIST | 934K| 25M| 1409 (3)| 00:00:17 |
--------------------------------------------------------------------------------------------

BITMAP_MERGE_AREA_SIZE, SORT_AREA_SIZE, 그리고 HASH_AREA_SIZE를 이용하여 PGA 메모리 제어하기

• optimizer는 이들의 설정 내용을 살펴보고 이들의 크기에 근거하여 해시 조인보다는 정렬 병합 조인을 선호하도록 결정할 수도 있다.
• Oracle 9i 이사에서 전용 서버를 사용할 경우에 이들 매개 변수는 쓸모가 없으므로 WORKAREA_SIZE_POLICY=AUTO와 PGA_AGGREGATE_TARGET 설정하여 사용한다.

스타 쿼리를 위해 STAR_TRANSFORMATION_ENABLED 사용하기

• 일반적으로 DW에서 스타쿼리가 사용되어야 한다면 비트맵 인덱스가 구성되어야 하며, 이런 유혀의 rEWRITE가 일어날 수 있도록 STAR_TRANSFORMATION_ENABLED=TRUE로 설정되어야 한다.

OPTIMIZER에 영향을 미치는 기타 매개변수 설정하기

PGA_AGGREEGATE_TARGET

• 오라클이 PGA 작업 영역으로 사용해야 하는 RAM이 양을 제어하여 SORT_AREA_SIZE, HASH_AREA_SIZE 등을 대신하여 사용된다. 이 매개변수는 데이터 정렬과 해싱용 메모리의 양을 제한하려고 시도한다

WORKAREA_SIZE_POLICY

• 이 매개변수는 PGA_AGGREGATE_TARGET 매개변수와 같이 이용되어 정렬영역이나 해시영역과 같은 작업영역의 크기가 사용자에 의해 수작업으로 지정되거나 리소스 사용정도에 따라 자동으로 지정될지를 제어한다.
• AUTO - PGA_AGGREGATE_TARGET를 이용하여 동적으로 할당.
• MANUAL - SORT_AREA_SIZE와 HASH_AREA_SIZE를 찾아 사용한다.

PARALLEL

• 병렬쿼리를 계속 사용하고 싶다면 PARAMETER_AUTOMATIC_TUNING과 PARALLEL_ADAPTIVE_MULTI_USER 매개변수를 설정한다
• PARALLEL_ADAPTIVE_MULTI_USER - 시스템의 부하가 변함에 따라 병렬 처리의 정도가 변해야 하는지를 제어
• PARAMETER_AUTOMATIC_TUNING - 병렬 자동 튜닝. 병렬 처리 정도 (특정 문제에 할당될 프로세스/쓰레드의 수)는 시스템의 부하에 따라 자동으로 결정되고 변경된다.

10053 이벤트를 사용하여 CBO선택 추적하기

옵티마이저의 활동 추적과 관련해서

1. 주어진 질의에 대해서 어떠한 최적화 과정을 거쳤는지
2. 최종적으로 어떤 실행 계획을 선택했는지
3. 선택된 실행 계획대로 수행했을 때 걸리는 시간과 자원이 얼마나 소요되었는지를 확인