Willy's IT knowledge base: All about Optimizer(1)

제 1 절 옵티마이저

1. 옵티마이저 소개

# 옵티마이저란?
    SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리 경로(실행계획)를 생성해주는 DBMS 내부의 핵심 엔진

# 옵티마이저 최적화 과정 요약
    a. 사용자가 던진 쿼리 수행을 위해. 후보군이 될만한 실행계획 탐색
    b. Data dictionary에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계 정보를 이용해 각 실행계획의 예상 비용 산정
    c. 각 실행계획을 비교해서 최저 비용을 하나는 선택

# 옵티마이저 종류
    a. Rule Based Optimizer(Heuristic Optimizer)
        - 미리 정해 놓은 우선 순위 규칙을 따라 실행 계획 선택
        - 우선순위는 인덱스 구조, 연산자, 조건절 형태에 따라 결정됨
        - 데이터량, 값의 수 (number of distinct value), 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 같은 데이터 특성을 고려하지 않기 때문에 대용량 데이터를 처리하는데 있어 합리적이지 못함.

   select /*+ rule */ * from emp orer by empno

   위 쿼리에서 empno 컬럼에 인덱스가 있으면 무조건 그 인덱스를 이용해 sort order by 연산을 대체함.
   부분범위 처리가 불가능한 상황이라면 Full table scan한고 나서 정렬하는 편이 나은데, 인덱스 컬럼에 의한 order by가
   Full table scan보다 한 단계 높음.

    b. Cost Based Optimizer
        - 비용을 기반으로 최적화 수행
        - 비용이란 쿼리를 수행하는데 소요되는 일량 또는 시간, I/O 비용 모델에서는 I/O Call 횟수만으로 평가, CPU 비용 모델은 I/O비용 모델에서 CPU 연산 비용까지 감안하여 수행 일량을 상대적인 시간 개념으로 환산해 비용 평가
        - 통계정보(레코드 개수, 블록 개수, 평균행 길이, 컬럼값의 수, 컬럼값의 분포, 인덱스 높이, 클러스터링 팩터)            를 기초로 예상 비용을 산정하고, 총 비용이 가장 낮은 실행계획을 선택
   - 오브젝트 통계 항목(레코드 개수, 블록 개수, 평균 행 길이, 컬러 값의 수, 컬럼 값 분포, 인덱스 높이(Height), 클러스터링 팩터
        - 시스템 통계 정보(CPU 속도, 디스크 I/O 속도)

# SQL 최적화 과정

알티베이스와 상당히 유사함. (Admin 매뉴얼의 SQL튜닝 단원 참조)

   a. 쿼리를 내부 표현 방식으로 변환
   b. 표준적인 형태로 변환
   c. 후보군이 될만한 (낮은 레벨의) 프로시저를 선택
   d. 실행계획을 생성하고, 가장 비용이 적은 것을 선택

# 최적화 목표
   1) 전체 처리 속도 최적화
        쿼리 최종 결과 집합을 끝까지 읽는 것을 전제로 시스템 리소스(I/O, CPU, 메모리 등)을 가장 적게 사용하는 실행 계획

   - 옵티마이저 모드 변경 방법
   alter system set optimizer_mode=all_rows -- 시스템 레벨 변경
   alter session set optimizer_mode=all_rows -- 세션 레벨 변경
   select /*+ all_rows */ * from t where ...; -- 쿼리 베벨 벼경


   2) 최초 응답속도 최적화
        전체 결과 집합 중 일부만 읽다가 멈추는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행 계획을 선택
만약 이 모드에서 생성한 실행계획으로 데이터를 끝까지 읽는다면 전체 처리속도 최적화 실행계획보다 더 많은 리소스를 사용하고 전체 수행 속도도 느려질 수 있다.

## 옵티마이저 모드
   - Rule
   - all_rows   : DML, union, minus, for update 절 (기본모드)
   - first_rows
   - first_rows_n
   - choose

   - 옵티마이저 모드 변경 방법
   alter system set optimizer_mode= first_rows_10 -- 시스템 레벨 변경
   alter session set optimizer_mode= first_rows_10 -- 세션 레벨 변경
   select /*+ first_rows_10 */ * from t where ...; -- 쿼리 베벨 벼경

< 옵티마이저모드가all_rows 일경우>

SQL> create table t_emp

2 as

3 select * from scott.emp,(select rownum no from dual connect by level <= 1000)

4 order by dbms_random.value;

Table created.

SQL> alter table t_emp add constraint t_emp_pk primary key(empno, no);

SQL> begin

2 dbms_stats.gather_table_stats(

3 ownname => user

4 ,tabname => 't_emp'

5 ,method_opt=>'for columns sal');

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly exp

SQL> select /*+ all_rows */ * from t_emp

2 where sal >= 5000

3 order by empno, no;

Execution Plan

----------------------------------------------------------

Plan hash value: 1175086354

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1000 | 38000 | 31 (4)| 00:00:01 |

| 1 | SORT ORDER BY | | 1000 | 38000 | 31 (4)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| T_EMP | 1000 | 38000 | 30 (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("SAL">=5000)

< 옵티마이저모드가first_rows 일경우>

- first_rows는비용과규칙을혼합한형태의옵티마이저모드,

- order by 컬럼에인덱스가있으면Table Full scan 비용과비교해보지도않고, 무조건그인덱스를이용해sort order by 연산대체

- 사용자가Fetch 도중에멈출것이란가정하에실행계획수립

SQL> select /*+ first_rows */ * from t_emp

2 where sal >= 5000

3 order by empno, no;

Execution Plan

----------------------------------------------------------

Plan hash value: 1185322641

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1000 | 38000 | 15884 (1)| 00:03:11 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T_EMP | 1000 | 38000 | 15884 (1)| 00:03:11 |

| 2 | INDEX FULL SCAN | T_EMP_PK | 16000 | | 41 (0)| 00:00:01 | <-- 비용보다규칙우선시

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("SAL">=5000)

select count(*) all_emp
   , count(case when sal >= 5000 then 1 end) over_5000
   , round(count(case when sal >= 5000 then 1 end) / count(*) * 100) ratio
from t_emp;

   ALL_EMP OVER_5000      RATIO
---------- ---------- ----------
     16000       1000          6

6% 밖에되지않는데, 첫번째Fetch 분량을얻기까지많은인덱스스캔과테이블액세스를거쳐야하므로,
first_rows 모드이더라도, 최초응답속도도생각만큼좋지않을수있음.
sal 조건을만족하는레코드가앞쪽에몰려있거나, (empno, no값이작을수록sal 값이큰경우)
array sie가아주작을때이점
그럼에도옵티마이저는무조건index full scan 선택

< 옵티마이저가FIRST_ROWS_N 모드일경우>

- 처음n개로우만Fetch 하는것을전제로, 가장빠른응답속도를낼수있는실행계획선택
- 1, 10, 100, 1000 네가지만선택가능
- 완전한CBO 모드

select /*+ first_rows(100) */ * from t_emp
where sal >= 5000
order by empno, no;

Execution Plan
----------------------------------------------------------
Plan hash value: 1175086354

----------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 1000 | 38000 |    31   (4)| 00:00:01 |
|   1 | SORT ORDER BY     |       | 1000 | 38000 |    31   (4)| 00:00:01 |
|* 2 |   TABLE ACCESS FULL| T_EMP | 1000 | 38000 |    30   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter("SAL">=5000)

SQL> select count(*) from t_emp;

COUNT(*)
----------
16000

SQL> select /*+ first_rows(100) */ * from t_emp
2 where sal >= 1000
3 order by empno, no;

Execution Plan
----------------------------------------------------------
Plan hash value: 1175086354

----------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 13000 |   445K|    31   (4)| 00:00:01 |
|   1 | SORT ORDER BY     |       | 13000 |   445K|    31   (4)| 00:00:01 |
|* 2 |   TABLE ACCESS FULL| T_EMP | 13000 |   445K|    30   (0)| 00:00:01 | <-- Table full scan으로바뀜
----------------------------------------------------------------------------

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

   2 - filter("SAL">=1000)

# CHOOSE
   액세스되는테이블중적어도하나에통계정보가있다면CBO, 그중에서도all_rows 모드를선택,
   통계정보가없으면RBO를선택

         SQL Server 상에서
        select * from t where OPTION(fast 10)
        select * from t where OPTION(fastfirstrow)

# 옵티마이저 행동에 영향을 미치는 요소

   1) SQL과 연산자 형태
   2) Optimizing Factor - Index, IOT, Clustering, Partitioning, MV
   3) DBMS 제약 설정 - PK, FK, Check constraint, Not null
        ex) 인덱스 컬럼에 Not Null 제약이 설정돼 있으면 옵티마이저는 전체 개수를 구하는 Count 쿼리에 인덱스 활용 가능
   4) Optimizer Hint
   5) 통계 정보 - 절대적임
   6) Optimizer 관련 파라미터 -
   7) DBMS 버전과 종류

select min(empno) mn, max(empno) mx from emp a

-----------------------------------------------------------

-----------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 4 | 1 |

| 1 | SORT AGGREGATE | | 1 | 4 | |

| 2 | INDEX FULL SCAN| PK_EMP | 16 | 64 | 1 |

-----------------------------------------------------------

오라클8 버전부터index full scan 수행, empno가PK 컬럼임.

알티베이스는Table full scan 함

iSQL> select min(eno) mn, max(eno) mx from EMPLOYEES a;

MN MX

---------------------------

1 20

1 row selected.

------------------------------------------------------------

PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 8, COST: 0.00 )

GROUP-AGGREGATION ( ITEM_SIZE: 24, GROUP_COUNT: 1, BUCKET_COUNT: 1, ACCESS: 1, COST: 0.00 )

SCAN ( TABLE: EMPLOYEES A, FULL SCAN, ACCESS: 20, COST: 0.00 )

------------------------------------------------------------

iSQL> desc employees;

[ TABLESPACE : SYS_TBS_MEM_DATA ]

[ ATTRIBUTE ]

------------------------------------------------------------------------------

NAME TYPE IS NULL

------------------------------------------------------------------------------

ENO INTEGER FIXED NOT NULL

E_LASTNAME CHAR(20) FIXED NOT NULL

E_FIRSTNAME CHAR(20) FIXED NOT NULL

EMP_JOB VARCHAR(15) FIXED

EMP_TEL CHAR(15) FIXED

DNO SMALLINT FIXED

SALARY NUMERIC(10, 2) FIXED

SEX CHAR(1) FIXED

BIRTH CHAR(6) FIXED

JOIN_DATE DATE FIXED

STATUS CHAR(1) FIXED

[ INDEX ]

------------------------------------------------------------------------------

NAME TYPE IS UNIQUE COLUMN

------------------------------------------------------------------------------

__SYS_IDX_ID_646 BTREE UNIQUE ENO ASC

EMP_IDX1 BTREE DNO ASC

[ PRIMARY KEY ]

------------------------------------------------------------------------------

# Join Elimination 사례

1:M 관계에서M쪽집합을기준으로1쪽집합과Outer 조인하면결과건수는M쪽집합으로고정되므로,

조인조건외에어디에도1쪽집합을참조하지않는다면1쪽집합과는조인액세스를하지않아도됨.

1쪽테이블조인컬럼에PK가설정되어있어야함. ---> join Elimination

select e.empno, e.ename, e.sal, e.hiredate

from emp e, dept d

where d.deptno(+) = e.deptno;

오라클11g 부터는table full scan 수행

Rows Row Source Operation

------- ---------------------------------------------------

16 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=0 us cost=3 size=352 card=16)

알티베이스는join Elimination이작동안함.

iSQL> select e.eno, e.e_lastname, e.salary, e.join_date

2 from employees e, DEPARTMENTS d

3 where d.dno(+) = e.dno;

------------------------------------------------------------

PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 44, COST: 0.01 )

LEFT-OUTER-JOIN ( METHOD: INDEX_NL, COST: 0.00 )

SCAN ( TABLE: EMPLOYEES E, FULL SCAN, ACCESS: 20, COST: 0.00 )

SCAN ( TABLE: DEPARTMENTS D, INDEX: __SYS_IDX_ID_645, RANGE SCAN, ACCESS: 20, COST: 0.00 )

------------------------------------------------------------

# 옵티마이저의 한계

   1) 옵티마이저 팩터 부족
   2) 통계 정보의 부정확성
        select * from 사원 where 직급 = '부장' and 연봉 >= 5000;
        직급 종류가 [부장, 과장, 대리, 사원]으로 각각 25 %의 비중을 갖는다.
                전체 사원이 1000명이고, 연봉 >= 5000 조건에 부합하는 사원 비중이 10 % 이면
        옵티마이저가 추정하는 사원수는 1000 * 0.25 * 0.1 = 25
        실제 직급과 연봉 간에는 상관관계가 매우 높아서,
        만약 모든 부장의 연봉이 5000 만원 이상이라면
        조건에 부합하는 사원 수는 1000 * 0.25 * 1 = 250 이다.

        위와 같은 경우 모든 컬럼 간 상관 관계와 결합 분포를 미리 저장해두면 좋겠지만,
                테이블 컬럼이 많을 수록 잠재적인 컬럼 조합의 수는 기하급수적으로 증가하기 때문에
        거의 불가능함.

   3) 바인드 변수 사용시 균등분포 가정
        조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정하고 비용 계산

   4) 비현실적인 가정
        예전 DBMS 버전에서는 Single Block I/O와 Multiblock I/O의 비용을 같게 평가함.
        데이터 블록의 캐싱 효과도 고려하지 않음

   5) 규칙에 의존하는 CBO
        최적화 목표를 최초 응답 속도에 맞추면( optimizer_mode = first_rows) order by 소트를 대체할 인덱스가
        있을 때 무조건 해당 인덱스를 사용함.

   6) 하드웨어 성능 특성
        옵티마이저 개발팀이 사용한 하드웨어 사양에 맞춰져 있기 때문에 실제 운영 시스템과 달라
        잘못된 실행 계획을 수립할 수 있다. application 특성(I/O 패턴, 부하정도 등)에 의해서도 하드웨어 성능은 달라짐
        시스템 통계 정보를 수집하게 되면서 어느정도 해결할 수 있음.
        오라클 설치시 기본적으로 NoWorkload 시스템 통계를 수집함.
                   알티베이스는 별도로 수행해주어야 함.

# 통계 정보를 이용한 비용 계산 원리

1. 테이블 통계

analyze table emp compute statistics for table;
analyze table emp estimate statistics sample 5000 row for table;
analyze table emp estimate statistics sample 50 percent for table;

begin
   dbms_stats.gather_table_stats('scott','emp', cascade=>false, method_opt=>'for columns');
end;
/

select num_rows, blocks, empty_blocks, avg_space, avg_row_len, avg_space_freelist_blocks, num_freelist_blocks, sample_size, last_analyzed
from dba_tables
where owner='SCOTT'
and table_name='EMP';

NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS SAMPLE_SIZE LAST_ANALYZED
---------- ---------- ------------ ---------- ----------- ------------------------- ------------------- ----------- ---------------
        16          5            0          0          34                         0                   0          16 19-FEB-14

2. 인덱스 통계

analyze index emp_pk compute statistics;

테이블에 속한 모든 인덱스 통계를 수집할 때
analyze table emp compute statistics for ALL INDEXES;

테이블과 인덱스 통계를 함께 수집할 때
analyze table emp compute statistics for table for all indexes;

# dbms_stats 패키지 이용범

-- 특정 인덱스 통계만 수집

begin
   dbms_stats.gather_index_stats( ownname => 'scott', indname => 'pk_emp');
end;
/

-- 테이블에 속한 모든 인덱스 통계도 같이 수집

begin
   dbms_stats.gather_table_stats('scott','emp',cascade=>true);
end;
/

select blevel, leaf_blocks, clustering_factor, num_rows, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key
   ,sample_size, last_analyzed
from dba_indexes
where owner='SCOTT'
and table_name='EMP'
and index_name='PK_EMP';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR   NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY SAMPLE_SIZE LAST_ANALYZED
---------- ----------- ----------------- ---------- ------------- ----------------------- ----------------------- ----------- ---------------
         0           1                 2         16            16                       1                       1          16 01-APR-14

인덱스 생성시 자동으로 통계정보 수집
create index emp_ename_idx on emp(ename) compute statistics;
- 알티베이스는 인덱스 생성시 자동으로 해당 인덱스에 대한 통계정보수집을 한다.

alter index emp_ename_idx rebuild compute statistics;

3. 컬럼 통계

analyze table emp compute statistics for all columns size 254 <--- 최대 버킷 수 지정, 최대값은 254
명시하지 않으면 기본값은 75, 히스토그램을 생성하지 않을려면 1로 설정

# 일부 컬럼에 대한 통계 수집
analyze table emp compute statistics for COLUMNS ENAME SIZE 10, SALE SIZE 20;

버킷 개수를 동일하게 설정
analyze table emp compute statistics for COLUMNS SIZE 20 ENAME, SAL, HIREDATE;

analyze table emp compute statistics
for table
for all indexes
for all indexed columns size 254;

-- 컬럼 통계 조회
select num_distinct, low_value, high_value, density, num_nulls, num_buckets,
   last_analyzed, sample_size, avg_col_len, histogram
from dba_tab_columns
where owner = 'SCOTT'
and table_name ='EMP'
and column_name='DEPTNO'

NUM_DISTINCT LOW_VALUE                                                        HIGH_VALUE                                             DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED    SAMPLE_SIZE AVG_COL_LEN HISTOGRAM
------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ----------- --------------- ----------- ----------- ---------------
           3 C10B                                                             C11F                                                        .035714286           2           3 01-APR-14                14           3 FREQUENCY

-- 컬럼 히스토그램 조회
select endpoint_value, endpoint_number
from dba_histograms
where owner = 'SCOTT'
and table_name = 'EMP'
and column_name='DEPTNO'
order by endpoint_value;

ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
            10               3
            20               8
            30              14

4. 시스템 통계

CPU 속도, 평균적인 Single Block I/O 속도, 평균적인 MultiBlock I/O 속도, 평균적인 Multiblock I/O 개수
I/O 서브시스템의 최대 처리량(Throughput), 병령 Slave의 평균 처리량(Throughput)

과거에는 이들 항목이 고정된 상수였음.

# 시스템 통계 정보 조회

select sname, pname, pval1, pval2 from sys.aux_stats$;
SQL> select sname, pname, pval1, pval2 from sys.aux_stats$;

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    08-15-2009 00:49
SYSSTATS_INFO                  DSTOP                                     08-15-2009 00:49
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                      2657.0122      <-- NoWorkload
SYSSTATS_MAIN                  IOSEEKTIM                              10      <-- NoWorkload
SYSSTATS_MAIN                  IOTFRSPEED                           4096      <-- NoWorkload
SYSSTATS_MAIN                  SREADTIM                                       <-- Workload
SYSSTATS_MAIN                  MREADTIM                                          <-- Workload
SYSSTATS_MAIN                  CPUSPEED                                       <-- Workload
SYSSTATS_MAIN                  MBRC                                           <-- Workload

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSSTATS_MAIN                  MAXTHR                                         <-- Workload
SYSSTATS_MAIN                  SLAVETHR                                       <-- Workload

13 rows selected.

# workload 시스템 통계

application으로부터 일정시간동안 발생한 시스템 부하를 측정 보관함으로써 그 특성을 최적화 과정에 반영할 수 있게 한 기능

cpuspeed : 현재 시스템에서 단일 CPU가 초당 실행할 수 있는 표준 오퍼레이션 개수(단위: micro sec)
sreadtim : 평균적인 Single Block I/O 속도 (milli sec )
mreadtim : 평균적인 Multiblock I/O 속도 ( milli sec)
mbrc : Multiblock I/O 방식을 사용할 때 평균적으로 읽은 블록 수
maxthr : I/O 서브시스템의 최대 처리량 ( 바이트/초)
slavethr : 병렬 slave의 평균적인 처리량 ( 바이트/초 )

수집기간 동안 application이 Full Table scan이 발생하지 않는다면 mreadtim와 mbrc 항목이 측정되지
않을 것이며, 병렬 쿼리가 수행되지 않는다면 slavethr 항목이 측정되지 않음다.

# NoWorkload 시스템 통계

관리자가 명시적으로 선택하지 않더라도 CPU 비용 모델이 기본 비용 모델로 사용하기 위해 도입
CPU 비용 모델은 시스템 통계가 있을때만 활성화

1) cpuspeednw ( 기본값 : 데이터베이스 최초 기동시 측정된 값)
   NoWorkload 상태에서 측정된 CPU 속도(단위 : Millions/sec)
2) ioseektim ( 기본값 : 10ms )
   I/O Seek Time을 의미, 데이터를 읽으려고 디스크 헤드(head)를 옮기는 데 걸리는 시간
   대개 5 ~ 15 ms 수치, 디스크 회전 속도와 디스크 또는 RAID 스펙에 따라 달라짐.
   io seek time = seek time + latency time + operating system overhead time
3) iotfrspeed ( 기본값 : 4096 bytes/ms )
   I/O Transfer 속도를 의미, 하나의 OS 프로세스가 I/O 서브시스템으로부터 데이터를 읽는 속도

Workload 시스템 통계를 수집하고 반영하는 순간 NoWorkload 시스템 통계는 무시됨.
Workload 시스템 통계가 수집되기 전까지 아래 공식으로 산정된 추정값 사용

cpuspeed = cpuspeednw
mbrc = db_file_multiblock_read_count
sreadtim = ioseektime + db_block_size / iotfrspeed
mreadtim - ioseektime + mbrc * db_block_size /iotfrspeed

# Workload 통계와 주요 차이점
- Workload는 실제 application에서 발생하는 부하를 기준으로 각 항목의 통계치를 측정
NoWorkload는 모든 데이터파일 중에서 오라클이 무작위로 I/O를 발생 시켜 통계 수집

고로, 시스템 부하 정도에 따라 NoWorkload 시스템 통계 정보도 달라질 수 있다.

# NoWorkload 시스템 통계 수집 방법
begin
   dbms_stats.gather_system_stats(gathering_mode => 'NOWORKLOAD' );
end;
/


1) 선택도(Selectivity) : 전체 대상 레코드 중에서 특정 조건에 선택될 것으로 예상되는 레코드 비율

   선택도 -> 카디널리티 -> 비용 -> 액세스 방식, 조인 순서, 조인 방법 등 결정

   히스토그램이 없거나, 있더라도 조건절에 바인드 변수를 사용하면 옵티마이저는 데이터 분포가 균일하다고 가정함

    A. 히스토그램이 없을 때, 등치(=) 조건에 대한 선택도

   선택도 = 1 / (Distinct Value 개수) = 1 / num distinct

         B. 부등호, between 같은 범위 검색 조건에 대한 선택도(상수 조건일 때)
         선택도 = 조건절에서 요청한 값 범위 / 전체 값 범위

          값의 범위는 컬럼 통계 정보로 수집된 high_value, low_value, num_distinct 등을 이용해 구함.

num_rows : 1000000
num_distinct : 100
low_value : 1
high_value : 1000

no > 500    : (1000-500) / (100-1) = 0.5
no >= 500   : ( 1000 - 500 ) / (1000 - 1 ) + 1/100 = 0.51
no between 500 and 1000 : ( 1000 - 500) / (1000-1) + 1/100 + 1/100 = 0.52

선택도 = ( high_value - 비교값 ) / ( high_value - low_value )

중요. 컬럼 히스토그램이 없을 때 옵티마이저는 '조건절에서 요청한 값 범위'에 속한 값들이
   '전체 값 범위'에 고르게 분포돼 있음을 가정함.

   2) 카디널리티( Cardinality) : 특정 액세스 단계를 거치고 난 후 출력될 것으로 예상되는 결과 건수

    컬럼 히스토그램이 없을 때 등치(=) 조건에 대한 카디널리티
   카디널리티 = 총 로우 수 * 선택도 = num_rows / num_distinct

    ex) Distinct value 개수가 10, 선택도는 0.1, 총 로우수가 1000일 때
1000 * ( 1 / 10 ) = 100

num_rows는 테이블 통계( dba_tables, dba_tab_statistics)에서 확인 가능
num_distinct는 컬럼 통계( dba_tab_columns, dba_tab_col_statistics )에서 확인 가능
    ex)   select * from 사원 where 부서 = :부서

   부서 컬럼의 Distinct value 개수가 10이면 선택도는 0.1 (=1/10)
   총 사원 수가 1000명일 때 카디널리티는 100(=1000 * 0.1)이 된다.
   옵티마이저는 결과집합이 100건일 것으로 추정

   조건절이 두개 이상일 때
        select * from 사원 where 부서 = :부서 and 직급 = :직급

   직급 도메인이 [부장, 과장, 대리, 사원]이면 Distinct value 개수가 4이므로,
   선택도는 0.25(=1/4)
   카디널리티는 25 (=1000 * 0.1 * 0.25)

# 선택도및카디널리티계산식테스트

create table t_emp

select b.no, a.*

from (select * from scott.emp where rownum <= 10 ) a

,(select rownum no from dual connect by level <= 100) b;

-- 통계정보수집(size를1로설정했으므로히스토그램을생성하지않는다.)

begin

dbms_stats.gather_table_stats( user, 't_emp', method_opt => 'for all columns size 1');

end;

select job, count(*) from t_emp group by job order by job;

SQL> select job, count(*) from t_emp group by job order by job;

JOB COUNT(*)

--------- ----------

ANALYST 100

CLERK 100

MANAGER 300

PRESIDENT 100

SALESMAN 400

explain plan for

select * from t_emp where job = 'CLERK';

@?/rdbms/admin/utlxpls

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 205437092

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 200 | 7800 | 4 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T_EMP | 200 | 7800 | 4 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

1 - filter("JOB"='CLERK')

13 rows selected.

explain plan for

select * from t_emp where job = 'SALESMAN';

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 205437092

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 200 | 7800 | 4 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T_EMP | 200 | 7800 | 4 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

1 - filter("JOB"='SALESMAN')

13 rows selected.

select t.num_rows, c.num_nulls, c.num_distinct, 1/c.num_distinct selectivity, num_rows/c.num_distinct cardinality

from user_tables t, user_tab_columns c

where t.table_name = 'T_EMP'

and c.table_name = t.table_name

and c.column_name = 'JOB';

NUM_ROWS NUM_NULLS NUM_DISTINCT SELECTIVITY CARDINALITY

---------- ---------- ------------ ----------- -----------

1000 100 5 .2 200

-- 컬럼히스토그램정보수집

begin

dbms_stats.gather_table_stats('scott','t_emp', cascade=>false, method_opt=>'for all columns size 5');

end;

SQL> explain plan for

2 select * from t_emp where job = 'CLERK';

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 205437092

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 100 | 3900 | 4 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T_EMP | 100 | 3900 | 4 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

1 - filter("JOB"='CLERK')

13 rows selected.

SQL> explain plan for

2 select * from t_emp where job = 'SALESMAN';

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 205437092

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 400 | 15600 | 4 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T_EMP | 400 | 15600 | 4 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

1 - filter("JOB"='SALESMAN')

13 rows selected.

- 바인드변수를사용하면히스토그램이생성됐더라도, 평균분포로가정하여cardinality를구함.
explain plan for
select * from t_emp where job = :job;

@?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |   200 | 7800 |     4   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_EMP |   200 | 7800 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("JOB"=:JOB)

13 rows selected.

3. NULL 값을포함할때
오라클은null을공집합으로취급한다. 알티베이스도마찬가지.

iSQL> create table test (i1 integer, i2 varchar(10));
Create success.
iSQL>
iSQL> insert into test values(1,'AAAAAA');
1 row inserted.
iSQL> insert into test values(2,'BBBBBB');
1 row inserted.
iSQL> insert into test (i1) values(3);
1 row inserted.
iSQL>
iSQL>
iSQL> select count(*) from test where i2 is null;
COUNT
-----------------------
1
1 row selected.
iSQL> select count(*) from test where i2 = null;
COUNT
-----------------------
0
1 row selected.

- Null 값으로변경
update t_emp set job=NULL where no <= 50;
SQL> update t_emp set job=NULL where no <= 50;

500 rows updated.

commit;

select count(*) from t_emp where job is null;

COUNT(*)
----------
       500

select count(*) from t_emp where job = null;

COUNT(*)
----------
         0

select * from t_emp where job = :job;
바인드변수에어떤값이입력되든(NULL 값이입력되더라도) job이Null인레코드는결과집합에서제외

begin
   dbms_stats.gather_table_stats('scott','t_emp', cascade=>false, method_opt=>'for all columns size 1');
   -- 컬럼히스토그램정보제거
end;
/

select num_nulls, num_distinct
from user_tab_columns
where table_name='T_EMP'
and column_name='JOB';

NUM_NULLS NUM_DISTINCT
---------- ------------
       500            5

선택도= ( 1 - ( 500/1000 )) / 5 = 0.1
카디널리티= 1000 * 0.1 = 100

# Null 값을고려한(컬럼히스토그램정보가없는상태에서) 선택도공식

선택도   = 1 / Distinct value 개수* Null값을제외한row 수/ 총row tn
   = (1 / num_distinct) * (( num_rows - num_nulls ) / num_rows)
   = ( 1 - (num_nulls / num_rows)) / num_distinct

explain plan for
select * from t_emp where job = :job;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |   100 | 3600 |     4   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_EMP |   100 | 3600 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("JOB"=:JOB)

13 rows selected.

4. 조건절이2 개이상일때

각컬럼의선택도와전체로우수를곱해서구함.

select c.column_name, t.num_rows, c.num_nulls, c.num_distinct,
   ( 1 - c.num_nulls/t.num_rows)/c.num_distinct selectivity
from user_tables t, user_tab_columns c
where t.table_name = 'T_EMP'
and c.table_name = t.table_name
and c.column_name in ('DEPTNO','JOB');

COLUMN_NAME                      NUM_ROWS NUM_NULLS NUM_DISTINCT SELECTIVITY
------------------------------ ---------- ---------- ------------ -----------
JOB                                  1000        500            5          .1
DEPTNO                               1000        100            3          .3

job과deptno 컬럼의선택도가각각0.1과0.3 이므로카디널리티는1000 * 0.1 *0.3 = 30

explain plan for
select * from t_emp where job = :job and deptno = :deptno;

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |    30 | 1080 |     4   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_EMP |    30 | 1080 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("JOB"=:JOB AND "DEPTNO"=TO_NUMBER(:DEPTNO))

13 rows selected.

5. 범위조건일때

create table t
as
select rownum no1,
   case when rownum <= 1000 or rownum > 9000 then rownum else 5000 end no2
from dual
connect by level <= 10000;

begin
   dbms_stats.gather_table_stats('scott','t', cascade=>false, method_opt=>'for all columns size 1');
   -- 컬럼히스토그램정보제거
end;
/

explain plan for
select * from t where no1 > 5000;

explain plan for
select * from t where no2 > 3000;

위두쿼리의카디널리티가항상똑같이나온다. 왜이러지?

SQL> explain plan for
2 select * from t where no1 > 5000;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 5001 | 35007 |     7   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    | 5001 | 35007 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("NO1">5000)

13 rows selected.

SQL> explain plan for
2 select * from t where no2 > 5000;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 5001 | 35007 |     7   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    | 5001 | 35007 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("NO2">5000)

13 rows selected.

explain plan for
select * from t where no2 between 3000 and 4000;

SQL> explain plan for
2 select * from t where no2 between 3000 and 4000;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 1010 | 7070 |     7   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    | 1010 | 7070 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("NO2"<=4000 AND "NO2">=3000)

13 rows selected.

begin
   dbms_stats.gather_table_stats(user, 't', method_opt => 'for all columns size 254');
end;
/

버킷개수가254개뿐이므로값의종류가많을경우정확한cardinality를추정하기어렵다.

SQL> explain plan for
2 select * from t where no2 between 3000 and 4000;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     5 |    35 |     7   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    |     5 |    35 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("NO2"<=4000 AND "NO2">=3000)

13 rows selected.

6. cardinality 힌트를이용한실행계획제어

explain plan for
select /*+ use_hash(d e) */ * from dept d, emp e
where d.deptno = e.deptno

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   756 |     7 (15)| 00:00:01 |
|* 1 | HASH JOIN         |      |    14 |   756 |     7 (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|* 3 |   TABLE ACCESS FULL| EMP |    14 |   476 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("D"."DEPTNO"="E"."DEPTNO")
   3 - filter("E"."DEPTNO" IS NOT NULL)

16 rows selected.

- emp 테이블을Build input으로하여hash join을수행하고자할때,
   힌트를이용해dept 테이블을cardinality 16으로조정할수있다.

explain plan for
select /*+ use_hash(d e) cardinality(d 16) */ * from dept d, emp e
where d.deptno = e.deptno;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1123238657

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    56 | 3024 |     7 (15)| 00:00:01 |
|* 1 | HASH JOIN         |      |    56 | 3024 |     7 (15)| 00:00:01 |
|* 2 |   TABLE ACCESS FULL| EMP |    14 |   476 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |    16 |   320 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("D"."DEPTNO"="E"."DEPTNO")
   2 - filter("E"."DEPTNO" IS NOT NULL)

16 rows selected.

- opt_estimate 힌트를이용해옵티마이저가예상한cardinality에4를곱하여조정가능함.
explain plan for
select /*+ use_hash(d e) opt_estimate(table, d, scale_rows=4) */ * from dept d, emp e
where d.deptno = e.deptno;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1123238657

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    56 | 3024 |     7 (15)| 00:00:01 |
|* 1 | HASH JOIN         |      |    56 | 3024 |     7 (15)| 00:00:01 |
|* 2 |   TABLE ACCESS FULL| EMP |    14 |   476 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |    16 |   320 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("D"."DEPTNO"="E"."DEPTNO")
   2 - filter("E"."DEPTNO" IS NOT NULL)

16 rows selected.

   3) 히스토그램
        분포가 균일하지 않는 컬럼으로 조회할 때 효과 발휘
      A. 히스토그램 유형

      높이균형( Height-Balanced ) 히스토그램
        도수분포( Frequency ) 히스토그램

# 오라클에서히스토그램관련정보조회

dbs_histograms, dba_tab_histograms, dba_tab_columns뷰의histogram 컬럼

참고. 알티베이스는위비슷한뷰가존재하지않음. V$dbms_stat 밖에없는데, 오라클만큼많은정보가없음

create sequence seq ;

create table member( memb_id number, age number(2) );

exec dbms_random.seed(0);