(1) 필요한 최소 블록만 읽도록 쿼리 작성

• 쿼리 작성시 불필요한 자료를 모두 가져오도록 작성하지 않아야한다.

(2) 최적의 옵티마이징 팩터 제공

• 전략적인 인텍스 구성
• DBMS가 제공하는 다양한 기능 활용
• 파티션
• 클러스터
• IOT
• MV
• FBI
• 분석함수등
• 옵티미이저모드 설정

SQL> create table t
 2  as
 3  select * from all_objects
 4  order by dbms_random.value;

Table created.

SQL> create index t_idx on t (owner,created);

Index created.

SQL> begin
 2  dbms_stats.gather_table_stats
 3     (ownname        => USER
 4  ,tabname   => 'T'
 5  ,estimate_percent => 100
 6  ,block_sample      => true
 7  ,method_opt        => 'for all columns size auto'
 8  );
 9  end;
10  /

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly exp
SQL> alter session set optimizer_mode='ALL_ROWS';

Session altered.


SQL>  select * from t
 2  where owner='SYS'
 3* order by created
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 961378228

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   766 | 65110 |    46   (5)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |   766 | 65110 |    46   (5)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T    |   766 | 65110 |    45   (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("OWNER"='SYS')

SQL>

• 정렬된 결과 집합 전체를 Fetch하여 최적화를 수행
• 인텍스를 사용하지않고 table을 full scan후 정렬하는 방식으로 동작
• 다량의 데이터를 인덱스를 경유해 random 액세스하는 부하를 최소화

SQL>
SQL> alter session set optimizer_mode ='FIRST_ROWS';

Session altered.

SQL> select * from t
 2  where owner='SYS'
 3  order by created;

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   766 | 65110 |   710   (1)| 00:00:09 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   766 | 65110 |   710   (1)| 00:00:09 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |   766 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OWNER"='SYS')
      filter("OWNER"='SYS')

SQL>

• 전체 결과 집합에서 처음 일부만 Fetch
• 쿼리 결과를 끝까지 Fetch한다면 Full Scan보다 더 느리게 작동할수도 있음

• 통계정보
• dbms_stats.gather_table_stats 
• 테이블에대한 object통계수집
• dbms_stats.gather_system_stats
• 시스템통계수집
• cpu속도
• 평균적인 single block 읽기속도
• 평균적인 Multiblock 읽기 숙도
• 평균적인 multiblock i/o개수

• 애플리케이셔 특성에 따라서 힌트를 적극적으로 활용하여 실행경로를 고정시킬경우도 생길수 있으며
• 힌트 사용시에느 최적의 실행계획으로 처리되는지 반드시 확인해야함