SQL> alter session set optimizer_mode = all_rows;
세션이 변경되었습니다.
SQL> show parameter optimizer_mode;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL> analyze table ITEM compute statistics;
테이블이 분석되었습니다.
SQL> show parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192 --8k
SQL> show parameter db_file_multiblock_read_count;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
- Full scan시 비용계산
-- Cost = 전체블럭 수 / db_file_multiblock_read_count
- 한번에 Block에서 가져오는 크기 = 8k * 16 = 131k
SQL> analyze table cost_optimizer_data compute statistics;
테이블이 분석되었습니다.
SQL> select table_name, blocks from dba_tables where table_name = 'COST_OPTIMIZER_DATA';
TABLE_NAME BLOCKS
------------------------------ ----------
COST_OPTIMIZER_DATA 173
- ITEM테이블 Full scan시 비용
- Cost = 173/16 = 10.8125
- 인덱스 스캔시 비용계산
-- Cost = 선택도 * 클러스터링 팩터
-- 선택도 : 조건에 선택되는 비율(distinct column/count(column_name))
-- 클러스터링 팩터 : 액세스하고자하는 인덱스가 블럭에 모여있는 정도(DBA_INDEXES)
SQL> analyze table cost_optimizer_data compute statistics;
테이블이 분석되었습니다.
SQL> create index cost_optimizer_data_idx on cost_optimizer_data(object_name); --17940/29535 선택도 = 0.6074..
인덱스가 생성되었습니다.
SQL> analyze index cost_optimizer_data_idx compute statistics;
인덱스가 분석되었습니다.
SQL> create index cost_optimizer_data_idx2 on cost_optimizer_data(object_type); --33/29535 선택도 = 0.0011..
인덱스가 생성되었습니다.
SQL> analyze index cost_optimizer_data_idx2 compute statistics;
인덱스가 분석되었습니다.
- 통계정보가 존재할때..
SQL> select index_name, clustering_factor
2 from user_indexes
3 where index_name like 'COST_OPTIMIZER_DATA_IDX%';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
COST_OPTIMIZER_DATA_IDX 21801 -- 0.6074 * 21801 = 13241.9274
COST_OPTIMIZER_DATA_IDX2 567 -- 0.0011 * 567 = 0.6237
SQL> set autotrace on;
SQL>SELECT object_name, object_type, object_id
2 FROM cost_optimizer_data
3 WHERE object_name LIKE 'oracle%'
4 AND object_type = 'JAVA CLASS';
..결과 생략..
744 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=35)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COST_OPTIMIZER_DATA' (COST=4 Card=1 Bytes=35)
2 1 INDEX (RANGE SCAN) OF 'COST_OPTIMIZER_DATA_IDX' (NON-UNIQUE) (Cost=2 Card=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1486 consistent gets
0 physical reads
0 redo size
33606 bytes sent via SQL*Net to client
1042 bytes received via SQL*Net from client
51 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
744 rows processed
- 통계정보가 존재하지 않을때
SQL> analyze index cost_optimizer_data_idx delete statistics;
인덱스가 분석되었습니다.
SQL> analyze index cost_optimizer_data_idx2 delete statistics;
인덱스가 분석되었습니다.
SQL> select index_name, clustering_factor
2 from user_indexes
3 where index_name LIKE 'COST_OPTIMIZER_DATA_IDX%';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
COST_OPTIMIZER_DATA_IDX2
COST_OPTIMIZER_DATA_IDX3
SQL>SELECT object_name, object_type, object_id
2 FROM cost_optimizer_data
3 WHERE object_name LIKE 'oracle%'
4 AND object_type = 'JAVA CLASS';
..결과 생략..
744 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COST_OPTIMIZER_DATA'
2 1 INDEX (RANGE SCAN) OF 'COST_OPTIMIZER_DATA_IDX2' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
206 consistent gets
0 physical reads
0 redo size
33637 bytes sent via SQL*Net to client
1042 bytes received via SQL*Net from client
51 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
744 rows processed
--rule기준 옵티마이져와 비교
SQL> alter session set optimizer_mode = rule;
세션이 변경되었습니다.
SQL>SELECT object_name, object_type, object_id
2 FROM cost_optimizer_data
3 WHERE object_name LIKE 'oracle%'
4 AND object_type = 'JAVA CLASS';
결과 생략...
744 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COST_OPTIMIZER_DATA'
2 1 INDEX (RANGE SCAN) OF 'COST_OPTIMIZER_DATA_IDX2' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
206 consistent gets
0 physical reads
0 redo size
33637 bytes sent via SQL*Net to client
1042 bytes received via SQL*Net from client
51 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
744 rows processed
SQL>
-사용된 예제SQL
SELECT object_name, object_type, object_id
FROM cost_optimizer_data
WHERE object_name LIKE 'oracle%'
AND object_type = 'JAVA CLASS'
-통계정보가 존재할 경우
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=35)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COST_OPTIMIZER_DATA' (COST=4 Card=1 Bytes=35)
2 1 INDEX (RANGE SCAN) OF 'COST_OPTIMIZER_DATA_IDX' (NON-UNIQUE) (Cost=2 Card=2)
-통계정보가 존재하지 않을 경우
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COST_OPTIMIZER_DATA'
2 1 INDEX (RANGE SCAN) OF 'COST_OPTIMIZER_DATA_IDX2' (NON-UNIQUE)
-Rule기반 옵티마이져일때와 비교
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COST_OPTIMIZER_DATA'
2 1 INDEX (RANGE SCAN) OF 'COST_OPTIMIZER_DATA_IDX2' (NON-UNIQUE)