꿈꾸는 개발자, DBA 커뮤니티 구루비

01. 옵티마이저

옵티마이저란 : 사용자가 요청한 SQL을 가장효율적이고 빠르게 수행할 수 있는 최적의 처리경로를 선택해주는 DBMS의 핵심엔진을 말함.
ㅇ 옵티마이저의 종류

규칙기반(RBO)	비용기반(CBO)
휴리스틱 옵티마이저라고도 함, 미리 정해리 우선순위에 따라 액세스 경로를 평가, 실행계획을 선택함 데이터량, 인덱스 높이, 컬럼 값 분포 등 데이터 특성을 고려하지 않음(대용량 처리 시 비합리적인 경우 발생)	비용(쿼리를 수행하는데 필요한 일량, 또는 시간)을 기반으로 최적화하는 옵티마이저 보통 레코드 개수, 블록 개수, 평균 행 길이, 인덱스 높이 등을 이용함

참고사항 : 동적 샘플링
테이블과 인덱스에 통계정보가 없거나 너무 오래되어 신뢰할 수 없을 때, 옵티마이저가 동적으로 샘플링을 수행할 수 있음

옵티마이저를 구성하는 서브 엔진

구분	설명
SQL파싱	SQL 문장을 개별 구성요소로 분리하고 파싱트리를 만듬, 파싱트리를 만드는 과정중 Syntax/Semantic 체크 실시 Soft 파싱 : Shared Pool에 캐싱돼 있을 경우 바로 실행단계로 넘어감 Hard 파싱 : Shared Pool에 캐싱돼 있지 않을 경우 실행계획부터 생성함
SQL 최적화	시스템 및 오브젝트 통계정보를 이용해 가장 효율적인 실행계획을 선택함 Query Tansformer : 최적화하기 쉬운 동일한 형태의 쿼리로 변환 Estimator : 실행계획 전체에 대한 총 비용 계산(I/O, CPU, MEMORY, 통계정보 등) Plan Generator : 후보군이 될만한 실행계획들 생성	Row-Source Generation	옵티마이저가 생성한 실행계획을 실제 실행가능한 DBMS만의 실행코드로 생성

옵티마이저 모드 변경하는 3가지 방법


alter system set optimizer_mode = all_rows; -- 시스템 레벨 변경
alter session set optimizer_mode = all_rows; -- 세션 레벨 변경
select /* + all rows */ * from t where ...; -- 쿼리 레벨 변경

옵티마이저 모드

구분	설명
RULE	RBO모드 선택
ALL_ROWS	최종 결과집합을 끝까지 FETCH, DML문장은 모드에 상관없이 ALL_ROWS 모드로 작동 UNION, MINUS, FOR UPDATE절을 사용 시 ALL_ROWS 모드로 작동 10g 기본설정
FIRST_ROWS	전체 결과집합 중 일부 로우만 FETCH
FIRST_ROWS_N	전체 결과집합 중 처음 N개만 FETCH(N에 지정가능한 수 1, 10, 100, 1000, 힌트에 사용시에는 0보다 큰 정수)
CHOOSE	액세스 되는 테이블 중 적어도 하나에 통계정보가 있다면 CBO, 없다면, RBO 선택), 9i 기본설정

CBO에서의 FIRST_ROWS 모드, ALL_ROWS 모드 작동원리
{code:sql} – 1 ~ 1000까지의 테이블을 생성 create table t_emp as select * from scott.emp, (select rownum no from dual connect by level <= 1000) order by dbms_random.value ;

– pk생성
alter table t_emp add constraint t_emp_pk primary key(empno, no);

-- 통계수집
begin
dbms_stats.gather_table_stats(
ownname => user
, tabname => 't_emp'
, method_opt => 'for columns sal');
end;
/

set autotrace traceonly exp;

– all_rows 모드시 table full scan으로 처리됨
select /*+ all_rows */ * from t_emp
where sal >= 5000
order by empno, no ;

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0	SELECT STATEMENT		1000	42000	30 (4)	00:00:01
1	SORT ORDER BY		1000	42000	30 (4)	00:00:01
2	TABLE ACCESS FULL	T_EMP	1000	42000	29 (0)	00:00:01

– first_rows으로 바꾸니 인덱스 스캔을 사용함
select /*+ first_rows */ * from t_emp
where sal >= 5000
order by empno, no ;

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0	SELECT STATEMENT		1000	42000	13881 (1)	00:02:47
1	TABLE ACCESS BY INDEX ROWID	T_EMP	1000	42000	13881 (1)	00:02:47
2	INDEX FULL SCAN	T_EMP_PK	14000		36 (0)	00:00:01

-- 연봉이 5001 이상인 회원은 없음
-- 교재(371페이지)에서는 TALBE FULL SCAN하는 것과는 다르게 INDEX SCAN으로 조회됨(rows는 줄었음)
set autotrace traceonly exp;
select /*+ first_rows */ * from t_emp
where sal >= 5001
order by empno, no ;

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0	SELECT STATEMENT		500	21000	13881 (1)	00:02:47
1	TABLE ACCESS BY INDEX ROWID	T_EMP	500	21000	13881 (1)	00:02:47
2	INDEX FULL SCAN	T_EMP_PK	14000		36 (0)	00:00:01

– RBO로 할 경우 여전히 인덱스 스캔하는 것을 알 수 있었음
select /*+ rule */ * from t_emp
where sal >= 5001
order by empno, no ;

Id

Operation

Name

0	SELECT STATEMENT
1	TABLE ACCESS BY INDEX ROWID	T_EMP
2	INDEX FULL SCAN	T_EMP_PK

|


|| FIRST_ROWS_N 모드 : CBO로만 동작||
| {code:sql}
-- 연봉이 2000을 넘는 비율이 43%임을 확인
SCOTT> set autotrace off;
SCOTT> select count(*) all_emp
  2       , count(case when sal >= 2000 then 1 end) over_2000
  3       , round(count(case when sal >= 2000 then 1 end) / count(*) * 100) ratio
  4  from   t_emp ;

   ALL_EMP  OVER_2000      RATIO
---------- ---------- ----------
     14000       6000         43


-- 일정비율 이상 Fetch 시 Table Full Scan이 발생하는 것을 확인할 수 있음
SCOTT> set autotrace traceonly exp;
SCOTT> select /*+ first_rows(10) */ * from t_emp
  2  where  sal >= 2000
  3  order by empno, no ;

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |    11 |   462 |    28   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_EMP    |  5999 |   246K|    28   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | T_EMP_PK |    26 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

-- 10건일 때는 Index Full Scan, 100건 이상일 때는 Table Full Scan으로 실행계획이 바뀜을 확인
SCOTT> select /*+ first_rows(100) */ * from t_emp
  2  where  sal >= 2000
  3  order by empno, no ;

------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |  5999 |   246K|       |    96   (2)| 00:00:02 |
|   1 |  SORT ORDER BY     |       |  5999 |   246K|   392K|    96   (2)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| T_EMP |  5999 |   246K|       |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------

|

옵티마이저 모드선택 : 일반적으로 OLTP에서는 FIRST_ROWS DW/배치에서는 ALL_ROWS가 적절한 모드라고 생각되어 왔으나 OLTP, DW, 배치 프로그램 등 모두에서 ALL_ROWS가 적절하다.

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

SQL과 연산자 형태
- 쿼리가 어떻게 작성됐는지에 따라 결과가 같아도 성능에 큰 영향이 있음
- 인덱스, IOT, 클러스터링, 파티셔닝, MV 등 옵티마이징 팩터 | 쿼리를 똑같이 작성해도 인덱스,IOT, 클러스터링 등을 구성했는지, 구성방법에 따라 실행계획가 성능이 달라짐
제약 설정 : PK, FK, Not Null, Check

Not Null 조건여부에 따른 실행계획 차이
{code:sql} – deptn 컬럼에 Not Null이 설정되어 있고, 인덱스가 있으면 index full scan 또는 index fast full scan으로 처리, not null이 설정되지 않으면 table full scan SCOTT> create index t_emp_idx01 on t_emp(deptno);

인덱스가 생성되었습니다.

-- 인덱스는 있으나 deptno컬럼에 not null 조건은 없음
SCOTT> select deptno, count(*) from t_emp group by deptno;

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0	SELECT STATEMENT		14000	177K	30 (4)	00:00:01
1	HASH GROUP BY		14000	177K	30 (4)	00:00:01
2	TABLE ACCESS FULL	T_EMP	14000	177K	29 (0)	00:00:01

– not null 조건 생성
SCOTT> alter table t_emp modify (deptno number(2) constraint deptno_nn not null);

테이블이 변경되었습니다.

– Index Fast Full Scan으로 처리
SCOTT> select deptno, count(*) from t_emp group by deptno;

-

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

-

0	SELECT STATEMENT		14000	177K	10 (10)	00:00:01
1	HASH GROUP BY		14000	177K	10 (10)	00:00:01
2	INDEX FAST FULL SCAN	T_EMP_IDX01	14000	177K	9 (0)	00:00:01

-

|
* 옵티마이저 힌트 : 옵티마이저는 1.문법적으로 맞지 않는 경우, 2.잘못된 참조 사용, 3.의미적으로 맞지 않는 힌트사용, 4.논리적으로 불가능한 엑세스 경로가 아닌경우 힌트를 그대로 따름
|| 힌트를 사용자가 준 경우와 그렇지 않은 경우 속도차이 ||
|{code:sql}
-- ordered 힌트를 주면 옵티마이저는 사용자가 지정한 순서로만 실행계획 후보군을 선정하고 비용을 계산한다. -> 실행계획 생성시간이 짧아져 수행속도에 차이가 발생
-- 아래 5개의 빈 테이블을 조인하기 위한 최적화 속도에 큰 차이가 있음을 확인할 수 있다.
-- 제대로 사용된 힌트는 유용하지만, 잘못 사용된 힌트는 성능을 떨어뜨릴 수 있다.
SCOTT>
create table t1 ( a number, b varchar2(100) );
create table t2 ( a number, b varchar2(100) );
create table t3 ( a number, b varchar2(100) );
create table t4 ( a number, b varchar2(100) );
create table t5 ( a number, b varchar2(100) );

SYS> alter system flush shared_pool;

시스템이 변경되었습니다.

SCOTT> set timing on
declare
  l_cnt number;
begin
  for i in 1..10000
  loop
    execute immediate ' select /*+ ordered */ count(*)' ||
                      ' from t1, t2, t3, t4, t5 ' ||
                      ' where  t1.a = ' || i ||
                      ' and    t2.a = ' || i ||
                      ' and    t3.a = ' || i ||
                      ' and    t4.a = ' || i ||
                      ' and    t5.a = ' || I into l_cnt;
  end loop;
end;
/

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:07.70


SYS> alter system flush shared_pool;

시스템이 변경되었습니다.

-- 힌트가 없을 경우 오라클이 5개의 테이블(데이터가 없지만)을 조인방법을 찾아내기 위해 상당한 시간을 소모했다.
SCOTT>
declare
  l_cnt number;
begin
  for i in 1..10000
  loop
    execute immediate ' select count(*)' ||
                      ' from t1, t2, t3, t4, t5 ' ||
                      ' where  t1.a = ' || i ||
                      ' and    t2.a = ' || i ||
                      ' and    t3.a = ' || i ||
                      ' and    t4.a = ' || i ||
                      ' and    t5.a = ' || I into l_cnt;
  end loop;
end;
/


PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:17.92

|

통계정보(오브젝트 통계, 시스템 통계) : CBO의 모든 판단 기준은 모두 통계정보에서 나옴
옵티마이저 관련 파라미터 : SQL, 데이터 등 환경이 동일한데, 오라클 버전을 업그레이드하면 다르게 작동하는 경우가 있음
DBMS 버전과 종류 : 같은 쿼리더라도 SQL SERVER와 ORACLE에서 동작하는 방식이 다를 수 있음

03. 옵티마이저의 한계

부족한 옵티마이징 팩터 :옵티마이저는 주어진 환경에서만 최선의 결과를 도출(예 : 통계정보가 없으면 잘못된 실행계획이 나올수 있음)
부정확한 통계 : 현실적인 제약으로 100% 정확한 통계를 유지하기 어려우며, 샘플링된 통계데이터와 실제데이터에 불일치가 발생할 수 있음
히스토그램의 한계 : 히스토그램 버킷 개수가 254개까지만 허용됨, 컬럼의 Distinct Value 개수가 그 이상이면 데이터의 정확한 분포를 반영할 수 없음
바인드 변수 사용 시 균등분포 가정 : 정확한 히스토그램을 보유하고 있어도 바인드 변수를 사용한 SQL에서는 무용지물임(뒷부분에 사례가 있음)
결합 선택도 산정의 어려움

데이터간에 상관관계가 있는 경우

select * from 사원 where 직급 = '부장' and 연봉 >= 5000;
ㅇ전체 사원수가 1,000명, 직급이 {부장, 과장, 대리, 사원}이며 각 25%의 비중을 갖고 있고, 히스토그램상 '연봉>=5000'이 10%라면 오라클은 위 결과를 25(=1000*0.25*0.1)명 이라고 계산
ㅇ하지만 실제는 부장과 연봉에는 상관관계가 매우 높아서 모든 부장의 연봉이 5,000만원 이상이라면 위 쿼리 결과는 250명이 나올 수 있다.

비현실적인 가정 : 예로 Single Block I/O와 Multi Block I/O의 비용을 같게 계산하는 것(실제 Single Block의 비용이 더 낮음)
규칙에 의존하는 CBO : CBO에서도 부분적으로 규칙에 의존함 (first_rows 모드에서 order by 소트를 대체할 인덱스가 있으면 무조건 인덱스 사용)
알파벳순 인덱스 선택 규칙
- 두 대안 인덱스의 예상비용이 같을 때 알파벳 순에서 앞에 것을 선택
- 따라서 인덱스의 명명 규칙에 PK가 가장 앞에 위치하도록 지정해야 함(예: PK, X01, X02 .... )

예상비용이 같은 인덱스의 선택방법 : 알파벳순
{code:sql} SCOTT> create table t 2 as 3 select rownum a, rownum b from dual 4 connect by level <= 10000;

테이블이 생성되었습니다.

-- 동일한 비용이 예상되는 인덱스 2개 생성
SCOTT> create index t_x01 on t(a);

인덱스가 생성되었습니다.

SCOTT> create index t_x02 on t(b);

인덱스가 생성되었습니다.

SCOTT> exec dbms_stats.gather_table_stats(user, 't');

PL/SQL 처리가 정상적으로 완료되었습니다.

– t_x01 인덱스 사용
SCOTT> set autotrace traceonly exp;
SCOTT> select * from t where a = 1 and b = 1;

-

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

-

0	SELECT STATEMENT		1	8	2 (0)	00:00:01
1	TABLE ACCESS BY INDEX ROWID	T	1	8	2 (0)	00:00:01
2	INDEX RANGE SCAN	T_X01	1		1 (0)	00:00:01

-

-- 인덱스 이름 변경
SCOTT> alter index t_x01 rename to t_x03;

인덱스가 변경되었습니다.

– t_x02인덱스 사용
-- 예상비용이 같은 인덱스는 단순하게 알파벳순으로 인덱스를 선택함을 알 수 있다.
SCOTT> select * from t where a = 1 and b = 1;

-

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

-

0	SELECT STATEMENT		1	8	2 (0)	00:00:01
1	TABLE ACCESS BY INDEX ROWID	T	1	8	2 (0)	00:00:01
2	INDEX RANGE SCAN	T_X02	1		1 (0)	00:00:01

-

|

* 하드웨어 성능 특성
** 기본적으로 옵티마이저 개발팀이 사용한 하드웨어 사양에 맞춰져 있음
** 9i부터 사용자의 시스템 통계정보를 수집해서 사용할 수 있도록 개발됨



h3. 04. 통계정보
* 실행계획을 수립할 때 사용하는 통계정보 : 테이블통계, 인덱스통계, 컬럼통계, 시스템통계
* 테이블 통계
|| 테이블 통계 수집방법 ||
|{code:sql}
analyze table emp compute statistics for TABLE;   -- 전수조사
analyze table emp estimate statistics sample 5000 rows for TABLE;  -- 5000개 샘플링
analyze table emp estimate statistics sample 50 percent for TABLE;  -- 50% 샘플링

-- 오라클사는 analyze 대신 아래 패키지 이용을 권고함
begin
    dbms_stats.gather_table_stats('scott', 'emp'
        , cascade=> false, method_opt=>'for columns');
end;

-- 통계정보 확인
SCOTT> SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN
  2       , AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS, SAMPLE_SIZE, LAST_ANALYZED
  3    FROM DBA_TABLES
  4   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_ANA
---------- ---------- ------------ ---------- ----------- ------------------------- ------------------- ----------- --------
        14          5            3       7954          38                         0                   0          14 16/07/13

|

인덱스 통계

인덱스 통계 수집방법
{code:sql} analyze INDEX emp_pk compute statistics; -- 특정 인덱스 analyze table emp compute statistics for ALL INDEXES; -- 특정 테이블에 속한 모든 인덱스 analyze table emp compute statistics for TABLE for ALL INDEXES; -- 테이블과 인덱스 통계를 한번에 수집

-- 특정 인텍스 통계만 수집
begin
dbms_stats.gather_index_stats
(ownname => 'scott'
, indname => 'emp_pk');
end;
/

-- 테이블에 속한 모든 인텍스 통계도 같이 수집
begin
dbms_stats.gather_table_stats ('scott', 'emp', cascade=>true);
end;
/

-- 통계정보 확인
SCOTT> SELECT BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, DISTINCT_KEYS
2 , AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, SAMPLE_SIZE, LAST_ANALYZED
3 FROM DBA_INDEXES
4 WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'T_EMP' AND INDEX_NAME = 'EMP_PK';

BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY SAMPLE_SIZE LAST_ANA

--

---

-

--

-

---

1 30 1269 14000 14 2 90 14000 16/07/13

-- 인덱스를 만들거나 재생성할 때 통계도 같이 수집할 수 있음
– 10g부터는 이 옵션을 명시하지 않아도 자동으로 수집됨
create index emp_ename_idx on t_emp (ename) COMPUTE STATISTICS;
alter index emp_ename_idx rebuild COMPUTE STATISTICS;

|

* 컬럼통계
|| 컬럼통계 수집방법(히스토그램) ||
| {code:sql}
-- 히스토그램의 최대 버킷 개수는 1~254까지 지정가능함
analyze table emp compute statistics for ALL COLUMNS SIZE 254;

-- 일부 컬럼에 대한 통계만 수집한다.
analyze table emp compute statistics for COLUMNS ENAME SIZE 10, SAL SIZE 20;

-- 일괄적으로 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
  FROM DBA_TAB_COLUMNS
     , 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';

SCOTT> /

NUM_DISTINCT LOW_VALUE            HIGH_VALUE    DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANA SAMPLE_SIZE AVG_COL_LEN HISTOGRAM
------------ -------------------- ---------- ---------- ---------- ----------- -------- ----------- ----------- ---------------
           3 C10B                 C11F       .035714286          0           3 16/07/14          14           2 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

|

시스템 통계
- CPU속도, 평균적인 SINGLE BLOCK I/O, 평균적인 MULTIBLOCK I/O 등을 수집

시스템 통계정보 조회방법
{code:sql} -- 시스템 통계조회(cpuspeednw, ioseektim, iotfrspeed는 10g에 추가, 나머지는 9i부터 제공) SCOTT> SELECT SNAME, PNAME, PVAL1, PVAL2 FROM SYS.AUX_STATS$;

SNAME PNAME PVAL1 PVAL2

--

SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 08-13-2009 23:35
SYSSTATS_INFO DSTOP 08-13-2009 23:35
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 2696.05568
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR

13 개의 행이 선택되었습니다.

|
** Workload 시스템 통계 : 9i에서 처음도입, 실제 애플리케이션에서 일정 시간 동안 발생한 시스템 부하를 측정
|| Workload 시스템 통계 수집방법 ||
|{code:sql}
-- 시스템 통계를 담을 테이블 생성
begin
  dbms_stats.create_stat_table(
          ownname => USER
        , stattab => 'mystats'
        , tblspace => 'USERS'
        , global_temporary => FALSE
  );
end;
/

-- 방법1 : 720분 수집하고 자동으로 종료
begin
  dbms_stats.gather_system_stats(
             gathering_mode => 'interval',
             interval => 720,
             stattab => 'mystats',
             statid => 'OLTP');
end;
/

-- 방법2 : 별도 종료 명령 시까지 통계수집
-- 수집시작
begin
  dbms_stats.gather_system_stats(
          gathering_mode => 'start'
        , stattab => 'mystats'
        , statid => 'OLTP');
end;
/
-- 수집종료
begin
  dbms_stats.gather_system_stats(
        gathering_mode => 'stop', 
        stattab => 'mystats', 
        statid =>'OLTP');
end;
/

|

- NoWorkload 시스템 통계 : 10g부터 도입된 기능, 오라클 최초 기동 시 측정된 값을 이용해 아래 공식으로 계산된 CPU 비용 모델
- NoWorkload 시스템 통계항목

통계항목	기본값	설명
cpuspeednw	데이터베이스 최초 기동시 측정값	측정된 CPU속도
ioseektim	10ms	데이터를 읽으려고 디스크 헤드를 옮기는데 걸리는 시간
iotfrspeed	4096 bytes/ms	하나의 OS프로세스가 I/O 서브시스템으로부터 데이터를 읽는 속도

ㅇ cpuspeed = cpuspeednw
ㅇ mbrc = db_file_multiblock_read_count
ㅇ sreadtim = ioseektim + db_block_size / iotfrspeed
ㅇ mreadtim = ioseektim + mbrc * db_block_size / iotfrspeed

NoWorkload 시스템 통계정보 수집방법
{code:sql} begin dbms_stat.gather_system_stats(gathering_mode => 'NOWORKLOAD'); end; {code}

- 참고사항 : NoWorkload 도 시스템 부하가 어느 정도 있는 상태에서 수집되는 것이 바람직함

05. 카티널리티

(1) 선택도 : 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율

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


선택도 = 1/Distinct values 개수 
      = 1 / num_distinct 
      = 조건절에서 요청한 값 범위 / 전체 값 범위 
      = 히스토그램이 있으면 그것으로 선택도 산정
      = 히스토그램이 없거나, 조건절에 바인드 변수를 사용하면 데이터 분포가 균일하다고 가정하여 선택도 계산

no > 500 인경우
선택도 = high_value - 비교값 / high_value - low_value = 1000 - 500 / 1000 - 1 = 0.5
no < 500 인경우
선택도 = 비교값 - low_value / high_value - low_value = 500 - 1 / 1000 - 1 = 0.4999
'= ' 조건이 있는 경우 : 1 / num_distinct 를 더함

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


카디널리티 = 총 로우 수 * 선택도
          = num_rows / num_distinct

선택도 및 카디널리티 계산식
{code:sql} create table t_emp as 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;
/

SCOTT> 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';

-- 히스토그램이 없을 때 카티널리티를 200으로 추정함
SCOTT> @?/rdbms/admin/utlxpls

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---

0	SELECT STATEMENT		200	8400	4 (0)	00:00:01
1	TABLE ACCESS FULL	T_EMP	200	8400	4 (0)	00:00:01

---

explain plan for
select * from t_emp where job = 'SALESMAN' ;

-- 히스토그램이 없을 때 카티널리티를 200으로 추정함
SCOTT> @?/rdbms/admin/utlxpls

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---

0	SELECT STATEMENT		200	8400	4 (0)	00:00:01
1	TABLE ACCESS FULL	T_EMP	200	8400	4 (0)	00:00:01

---

-- 통계정보 조회
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 0 5 .2 200

-- 통계정보 수집(히스토그램)
begin
dbms_stats.gather_table_stats( user, 't_emp'
, method_opt => 'for all columns size 5');
end;
/

-- 히스토그램정보가 있을 땐 실제 데이터 개수와 동일하게 100으로 나옴
explain plan for
select * from t_emp where job = 'CLERK';

SCOTT> @?/rdbms/admin/utlxpls

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---

0	SELECT STATEMENT		100	4200	4 (0)	00:00:01
1	TABLE ACCESS FULL	T_EMP	100	4200	4 (0)	00:00:01

---

-- 히스토그램정보가 있을 땐 실제 데이터 개수와 동일하게 400으로 나옴
explain plan for
select * from t_emp where job = 'SALESMAN' ;

SCOTT> @?/rdbms/admin/utlxpls

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---

0	SELECT STATEMENT		400	16800	4 (0)	00:00:01
1	TABLE ACCESS FULL	T_EMP	400	16800	4 (0)	00:00:01

---

-- 히스토그램이 있더라도 바인드 변수를 사용하면 평균적인 분포를 가정
explain plan for
select * from t_emp where job = :job;

SCOTT> @?/rdbms/admin/utlxpls

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---

0	SELECT STATEMENT		200	8400	4 (0)	00:00:01
1	TABLE ACCESS FULL	T_EMP	200	8400	4 (0)	00:00:01

---

 |

h5. (3) NULL 값을 포함할 때
|| NULL 값을 포함할 때 카디널리티 구하는 방법 ||
|{code:sql}
-- 널값을 생성
SCOTT> update t_emp set job = NULL where no <= 50;

500 행이 갱신되었습니다.

SCOTT> commit;

커밋이 완료되었습니다.

-- 히스토그램 제거
begin
  dbms_stats.gather_table_stats( user, 't_emp'
        , 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


-- 위 선택도에서 구한 공식과 일치함을 알 수 있다.
explain plan for
select * from t_emp where job = :job ;


SCOTT> @?/rdbms/admin/utlxpls

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

(4) 조건절이 두 개 이상일 때

{code:sql}
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 0 3 .333333333

-- 조건절이 두 개 이상일 땐 각 컬럼의 선택도와 전체 로우수를 곱해주면 됨
– job과 deptno의 선택도가 각각 0.1, 0.33 이므로 1000 * 0.1 * 0.33 = 33

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

SCOTT> @?/rdbms/admin/utlxpls

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---

0	SELECT STATEMENT		33	1287	4 (0)	00:00:01
1	TABLE ACCESS FULL	T_EMP	33	1287	4 (0)	00:00:01

---

|


h5. (5) 범위검색 조건일 때
|| 범위검색 조건일 때 예제 ||
|{code:sql}
-- no1은 숫자가 1 ~ 10,000까지 고르게 분포, 
-- no2는 1 ~ 1,000, 9,001 ~ 10,000까지는 고르게 분포, 이외 구간은 모두 5,000인 테이블 생성
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(user, 't', method_opt => 'for all columns size 1'); 
end;
/

-- no1과 no2의 카디널리티를 모두 같게 계산하는 것을 확인할 수 있다.
SCOTT> set autotrace traceonly exp
SCOTT> select * from t where no1 > 1000;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9001 | 63007 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  9001 | 63007 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------


SCOTT> select * from t where no2 > 1000;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9001 | 63007 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  9001 | 63007 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------


SCOTT> select * from t where no1 > 3000;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  7001 | 49007 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  7001 | 49007 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------


SCOTT> select * from t where no2 > 3000;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  7001 | 49007 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  7001 | 49007 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------


SCOTT> select * from t where no1 > 5000;
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------


SCOTT> select * from t where no2 > 5000;
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------


-- 실제 데이터가 없는데도 불구하고 카디널리티가 1010으로 나옴
SCOTT> select * from t where no2 between 3000 and 4000;
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------


-- 컬럼 히스토그램을 생성하고 나면, 근사치로 카디널리티를 계산해냄.
begin
  dbms_stats.gather_table_stats(user, 't'
    , method_opt => 'for all columns size 254');
end;
/

SCOTT>  select * from t where no2 between 3000 and 4000;
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------

|

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

힌트를 준 경우 예제
{code:sql} – dept의 카디널리티는 4, emp 카디널리티는 14이므로 해시조인 시에는 Build Input으로 dept가 선택 select /+ use_hash(d e) / * from dept d, emp e where d.deptno = e.deptno;

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---

0	SELECT STATEMENT		14	728	7 (15)	00:00:01
1	HASH JOIN		14	728	7 (15)	00:00:01
2	TABLE ACCESS FULL	DEPT	4	80	3 (0)	00:00:01
3	TABLE ACCESS FULL	EMP	14	448	3 (0)	00:00:01

---

-- 힌트를 설정하여 emp가 Build Input으로 사용됨
select /*+ use_hash(d e) cardinality(d 16) */ *
from dept d, emp e
where d.deptno = e.deptno;

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---

0	SELECT STATEMENT		56	2912	7 (15)	00:00:01
1	HASH JOIN		56	2912	7 (15)	00:00:01
2	TABLE ACCESS FULL	EMP	14	448	3 (0)	00:00:01
3	TABLE ACCESS FULL	DEPT	16	320	3 (0)	00:00:01

---

– 10g부터 사용가능한 힌트
select /*+ use_hash(d e) opt_estimate(table, d, scale_rows=4) */ *
from dept d, emp e
where d.deptno = e.deptno;

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---

0	SELECT STATEMENT		56	2912	7 (15)	00:00:01
1	HASH JOIN		56	2912	7 (15)	00:00:01
2	TABLE ACCESS FULL	EMP	14	448	3 (0)	00:00:01
3	TABLE ACCESS FULL	DEPT	16	320	3 (0)	00:00:01

---

03. 옵티마이저 원리 (01. 옵티마이저 ~ 05. 카디널리티) (by jiginet) [2016.07.11]

01. 옵티마이저

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

03. 옵티마이저의 한계

05. 카티널리티

(1) 선택도 : 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율

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

(4) 조건절이 두 개 이상일 때

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