오라클 성능 고도화 원리와 해법 II (2016년)
카디널리티 0 0 2,890

by 구루비 카디널리티 선택도 CARDINALITY [2017.04.28]


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

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입