오라클 성능 고도화 원리와 해법 II (2016년)
h1.카디널리티
(1) 선택도
- 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율
* 등치조건에 대한 선택도
1 1
선택도 = -------------------- = --------------
Distinct Value 개수 num_distinct
* 범위검색 조건에 대한 선택도 ( Between , 부등호 )
조건절에서 요청한 값 범위 1
선택도 = ------------------------
Distinct Value 개수
(2) 카디널리티
- 카디널리티 (Cardnality)는 특정 액세스 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수를 말한다
* 카디널리티 = 총 로우 수 × 선택도
* 컬럼히스토리가 없을때
카디널리티 = 총 로우 수 × 선택도 = num rows / num distinct
통계확인 table
- 테이블 통계 : dba_tables, dba_tab_statistics
- 컬 럼 통계 : dba_tab_columns, dba_tab_cotstatistics
-- 선택도 및 카디널리티 계산식 테스트
create table scott.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
Table SCOTT.T_EMP이(가) 생성되었습니다.
-- 통계정보 수집 (size를 l로 설정했으므로 히스토그램은 생성하지 않는다.)
begin
dbms_stats.gather_table_stats( 'scott', 't_emp' , method_opt => 'for all columns size 1' );
end;
PL/SQL 프로시저가 성공적으로 완료되었습니다.
select job, count(*) from scott.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 scott.t_emp where job = 'CLERK' ;
select * from table(dbms_xplan.display());
-- 결과
---------------------------------------------------------------------------
| 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 scott.t_emp where job = 'SALESMAN' ;
select * from table(dbms_xplan.display());
-- 결과
---------------------------------------------------------------------------
| 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 |
---------------------------------------------------------------------------
/* CLERK과 SALESMAN 모두 카디널리티 (=Rows)를 200으로 추정 */
-- 선택도 및 카디널리티 계산
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( 'scott', 't_emp' , method_opt => 'for all columns size 5' );
end;
PL/SQL 프로시저가 성공적으로 완료되었습니다.
explain plan for
select * from scott.t_emp where job = 'CLERK' ;
select * from table(dbms_xplan.display());
-- 결과
---------------------------------------------------------------------------
| 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 |
---------------------------------------------------------------------------
explain plan for
select * from scott.t_emp where job = 'SALESMAN' ;
select * from table(dbms_xplan.display());
-- 결과
---------------------------------------------------------------------------
| 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 |
---------------------------------------------------------------------------
-- 바인딩변수 ( 'SALESMAN' )
explain plan for
select * from scott.t_emp where job = &JOB ;
select * from table(dbms_xplan.display());
-- 결과
---------------------------------------------------------------------------
| 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 |
---------------------------------------------------------------------------
(3) NULL 값을 포함할 때
-- 널값은 값의 종류로 가져가지않는다.
l Null 값을 제외한 로우 수
선택도 = -------------------- x -------------------------
Distinct Value 개수 총 로우 수
1 (num_rows - num_nulls)
= ----------------- x ------------------------
num_distinct num_rows
1 - (num nulls / num rows)
= ----------------------------------
num distinct
(4) 조건절이 두 개 이상일 때
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 0 5 .2
DEPTNO 1000 0 3 .3333333333333333333333333333333333
=====================================================================================================================================
조건절이 두 개 이상일 때의 카디널리티
* 각 컬럼의 선택도와 전체 로우 수의 곱
예상 카디널리티 = 1000 * 0.2 * 0.33 = 66
explain plan for
select * from scott.t_emp where job = :job and deptno = :deptno ;
select * from table(dbms_xplan.display());
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67 | 2814 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_EMP | 67 | 2814 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
(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
Table T이(가) 생성되었습니다.
-- 통계정보 수집 (size를 l로 설정했으므로 히스토그램은 생성하지 않는다.)
begin
dbms_stats.gather_table_stats( 'scott', 't' , method_opt => 'for all columns size 1' );
end;
explain plan for
select * from t where no2 between 3000 and 4000;
select * from table(dbms_xplan.display());
--------------------------------------------------------------------------
| 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( 'scott', 't' , method_opt => 'for all columns size 254' );
end;
PL/SQL 프로시저가 성공적으로 완료되었습니다.
explain plan for
select * from t where no2 between 3000 and 4000;
select * from table(dbms_xplan.display());
--------------------------------------------------------------------------
| 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 힌트를 이용한 실행계획 제어
-- 옵티마이저가 계산한 카디널리티가 부정확할 때는 힌트를 이용해 사용자가 직접 카디널리티 정보를제공할수있다.
explain plan for
select /*+ use_hash(d e) */ * from dept d, emp e
where d.deptno = e.deptno ;
select * from table(dbms_xplan.display());
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 812 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
-- leading , swap_join_inputs 힌트를 이용할수있다.
explain plan for
select /*+ use_hash(d e) cardinality(d 16) */ *
from dept d, emp e
where d.deptno = e . deptno ;
select * from table(dbms_xplan.display());
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 3248 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 56 | 3248 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 16 | 320 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
-- 결과는 똑같음..
"구루비 주주클럽 스터디모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3352
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.