개요

방법

1. Selectivity와 Cardinality

비용 산정은 Selectivity, Cardinality, Cost, Data Access 방법, Index와 Join 방식(Index, Nested loop, Sort Merge, Hash)등을 거쳐 결정됩니다.

1.1 Selectivity

Selectivity(선택도)는 Base Table, View, Join 또는 Group by 구문을 이용한 질의 결과들의 모음인 Row set에 특정한 조건을 적용하는 경우 그 조건을 만족하는 Row들의 비율입니다. 즉, 조건에 예상되는 Row의 비율입니다.

• 히스토그램이 있는 경우 히스토그램으로 Selectivity를 산정합니다.
• 히스토그램이 없거나 조건절에 바인드 변수를 사용할 경우 Optimizer는 데이터 분포도가 균등하다고 가정하고 Selectivity를 산정합니다.

히스토그램의 Bucket 개수가 1인 경우도 히스토그램이 없다고 가정하지만 실제 DD Table을 조회하면 기본적인 정보는 저장되어 있습니다.

A='B' 같은 조건은 Row set에서 특정 수를 걸러내는 필터 역할을 하기 때문에 이러한 제한이 조건에 있는 Selectivity는 Row set에서 얼마나 많은 Row들이 해당 제한에 걸러지는가를 보여줍니다.

Selectivity의 값은 0.0부터 1.0까지입니다. 0.0은 Row set에서 선택되어지는 행이 하나도 없음을 뜻하고 1.0은 모든 행이 선택되어진다는 의미입니다.

통계 정보가 없거나 히스토그램으로 표현할 수 없는 타입의 Column일 경우 Selectivity의 내부 기본값을 사용하며 조건절의 형식에 따라 기본값이 다릅니다. 예를 들면 동등 연산자 (A='B')가 범위 연산자 (A='B')가 범위 연산자 (A<'B')보다 기본값이 작습니다. 동등 연산자는 범위 연산자 보다 적은 수의 Row를 반환한다고 생각하기 때문입니다.

만약 통계 정보가 존재할 경우 Selectivity 값을 산정할 때 사용합니다. 예를 들면 동등 연산자 (A='B')에서 Selectivity는 해당 Column의 유일값(Distinct value)의 개수에 따라 반비례하여 설정됩니다. 이 때 Column에 히스토그램이 있으면 유일값(Distinct value) 대신 사용하게 됩니다.

다음은 Selectivity 산정 공식에 대한 설명입니다.

• 히스토그램이 없고 동등 연산자(=)에 대한 Selectivity 산정 공식

Selectivity = 1/distinct value (유일값 개수)

• 히스토그램이 없고 범위 연산자(>,<,between)에 대한 Selectivity 산정 공식

Selectivity = 조건절에서 요청한 값의 범위/전체값 범위

다음은 num_rows, num_distinct, low_value, high_value 확인 방법과 사용자 쿼리 중 조건절에 맞는 특정 Column의 Selectivity를 산정하는 예입니다.

SQL> SELECT num_distinct, density, low_value, high_value, last_analyzed,column_name
 FROM USER_TAB_COL_STATISTICS
 WHERE table_name='CMS_CATEGORY';

-- 쿼리 조건별 각 column의 selectivity를 산정한다.
-- 쿼리에서 산출한 값이 아래와 같을 때 각 Column의 조건절 예는 다음과 같다.
 num_rows    num_distinct  low_value  high_value
------------ ------------- ---------- ---------------
1,000,000    100           1          1,000

* no < 500
 Selectivity = (비교값-low_value)/(high_value-low_value+1)
 = (500-1)/(1,000-1 +1)=0.499
 
 * no <= 500
 Selectivity = (비교값-low_value)/(high_value-low_value+1) + (1/num_distinct)
 = (500-1)/(1,000-1 +1) + 1/100 = 0.509
 
 * no > 500
 Selectivity = (1 - (Selectivity of no <= 500))
 = 1 - 0.509 = 0.491
 
 * no >= 500
 Selectivity = (1 - (Selectivity of no < 500))
 = 1 - 0.499 = 0.501
 
 * no between 100 and 500
 Selectivity = (Selectivity of no >= 100) x (Selectivity of no <= 500)
 = (1 - (Selectivity of no < 100)) x (Selectivity of no <= 500)
 = (1 - (100-1)/(1,000-1 +1)) x ((500-1)/(1,000-1 +1) + 1/100) = 0.459

1.2 Cardinality

Cardinality는 Access 단계를 거치고 난 후 출력될 것으로 예상되는 예측 Row 수이며, 해당 Where 조건에 의해 몇 개의 Row가 출력 되는지를 나타냅니다. 이 수치가 높다는 것은 겹치는 데이터가 적고 분포도가 높아 Selectivity(선택도)가 낮다는 것을 의미합니다. 실행 계획에서 사용하는 모든 Cardinality는 예측 수치입니다.

Row의 개수가 늘어나면 I/O cost가 증가하고 Row마다 연산을 많이 할 경우 CPU cost에도 부정적 영향을 미칩니다.

• Cardinality 산정 공식

Cardinality = Base Cardinality * Selectivity = num_rows * Selectivity

• Base cardinality

특정 Table의 전체 Row 개수이며 통계 정보를 수집하면 얻을 수 있다. 만약 통계 정보가 없거나 유효하지 않다면 Table block 개수를 가져와서 단위 Block 크기와 곱한 전체 byte 수를 예측한 평균 Row 길이로 나누어 계산합니다.

1.3 Selectivity와 Cardinality 산정

다음은 Selectivity와 Cardinality를 산정하는 예입니다.

• Table을 생성 (create_table_for_histogram.sql)

SQL> @create_table_for_histogram.sql

Table을 생성할 때 PCTFREE 90 옵션을 주는 이유는 Table block 개수를 충분히 늘리기 위해서입니다. (Row 크기가 작아서 데이터를 많이 부어도 Block이 많이 증가하지 않은 경우 사용)

• Index를 생성 (create_index_for_histogram.sql)

SQL> CREATE INDEX ARMY_IDX_NAME ON ARMY (NAME);
SQL> CREATE INDEX ARMY_IDX_JOB ON ARMY (JOB);
SQL> CREATE INDEX ARMY_IDX_SAL ON ARMY (SAL);
SQL> CREATE INDEX ARMY_IDX_DEPT ON ARMY (DEPT);

ARMY Table은 ID, NAME, JOB, SAL, DEPT Column으로 구성되어 있습니다.

• 쿼리에서 Unique하게 부여되는 값, 다양하게 분포된 값, 고유값 등을 확인합니다.
• ID는 Row별로 Unique하게 부여되는 값입니다.

-- ID는 Row별로 Unique하게 부여되는 값이다
SQL> SELECT ID, COUNT(*) FROM ARMY GROUP BY ID ORDER BY ID;

ID         COUNT(*)
---------- ----------
1          
1
2          1
3          1
4          1
5          1
6          1
7          1
8          1
9          1
10         1
...

-- NAME, SAL은 골고루 다양하게 분포되어 있다.
SQL> SELECT NAME, COUNT(*) FROM ARMY GROUP BY NAME ORDER BY NAME;

NAME       
COUNT(*)
---------- ----------
AACIJ      1
AAEYC      1
AAIAH      1
AAIYM      1
AAJIE      1
AAJTS      1
AALEZ      1
AANNQ      1
AAOKH      1
AAOQX      1
...

SQL> SELECT SAL, COUNT(*) FROM ARMY GROUP BY SAL ORDER BY SAL;
 SAL        
COUNT(*)
---------- ----------
100               78
200              102       
300              110       
400               96        
500              101     
600               90  
700               91 
800              106
900              113 
1000             102
...

-- JOB과 DEPT는 각각 5개의 고유값으로만 구성되어 있고 JOB의 경우는 INFANTRY인 Row가 절반을 넘게차지하고 있다.
SQL>  SELECT JOB, COUNT(*) FROM ARMY GROUP BY JOB ORDER BY COUNT(*) DESC;

 JOB                 
COUNT(*)
-------------------- ----------
INFANTRY                  8000
ARCHER                    1000
CAVALRY                    500
WIZARD                     300
GENERAL                    200

SQL> SELECT DEPT, COUNT(*) FROM ARMY GROUP BY DEPT ORDER BY DEPT;

 DEPT                 COUNT(*)
-------------------- ----------
DETAIL                     
1250
ENDLESS                    2500
INFIELD                    2500
PROMISE                    2500
RETHINKING                 1250

• 통계 수치들을 정확하게 하기 위해 100% 샘플링을 사용하고 기본값으로 통계 정보를 수집합니다. (set_default_histogram.sql)

이때 기본값은 FOR ALL COLUMNS SIZE 1입니다.

SQL> CALL DBMS_STATS.GATHER_TABLE_STATS(USER,'ARMY',estimate_percent=100);

• Column별 히스토그램이 어떻게 저장되어 있는지 확인합니다. (view_histogram.sql)

ALL_TAB_COL_STATISTICS뷰를 조회해도 되지만 히스토그램 Bucket 각각의 정보까지는 확인할 수 없으므로 직접 DD Table로 확인합니다.

@view_histogram.sql


NAME        BUCKET_CNT   NULL_CNT DISTINCT_CNT LOW_VAL                       

HIGH_VAL                                    DENSITY  AVG_COL_SIZE
---------- ------------ --------- ------------ ------------------------------ 
------------------------------------------ ---------- ------------
ID            1          0        10000 1.0000000000000000000000000000000000000E+00 
1.0000000000000000000000000000000000000E+04     .0001 2.9801
NAME          1          0         9996 AAAST ZZXVC                                       
                                      .00010004      5
JOB           1          0            5 ARCHER WIZARD                                     
                                             .2   7.67
SAL           1          0           99 1.0000000000000000000000000000000000000E+02 
9.9000000000000000000000000000000000000E+03 .01010101      2
DEPT          1          0            5 DETAIL RETHINKING .2 7.2


SQL> SELECT C.COLUMN_NAME NAME, H.HIST_HEAD_ID, H.BUCKET, H.END_POINT, H.END_POINT_ACTUAL, H.DISTINCT_CNT
FROM _DD_HIST_HEAD D, DBA_TAB_COLUMNS C, _DD_HISTOGRAM H
WHERE C.TABLE_NAME = 'ARMY'
AND C.COLUMN_ID = D.COL_NO
AND D.HIST_HEAD_ID= H.HIST_HEAD_ID;

NAME                 HIST_HEAD_ID     BUCKET  END_POINT END_POINT_ACTUAL      
                   DISTINCT_CNT
------------------- -------------- ---------- ---------- -----------------------------------
-------------------------------
ID                   26 1          10000      1.0000000000000000000000000000000000000E+04 
              10000
 NAME                 27 1          2.5432E+16 ZZXVC                                       
               9996
 JOB                  28 1          2.4569E+16 WIZARD                                     
                   5
 SAL                  29 1          9900       9.9000000000000000000000000000000000000E+03 
                 99
 DEPT                 30 1          2.3157E+16 RETHINKING               
                 5

위 결과를 보면 GATHER_TABLE_STATS를 호출할 때 Bucket 개수를 지정하지 않았기 때문에 기본값 1이 적용되어 각 Column 별 Bucket은 모두 1입니다.

첫번째 쿼리는 히스토그램의 헤더(_DD_HIST_HEAD) 결과로 Column내 전체의 최소값, 최대값, Distinct count 등의 정보를 나타냅니다.

두번째 쿼리는 히소토그램의 단위 Bucket(_DD_HISTOGRAM) 결과로 히스토그램 하나의 Bucket내의 최댓값, Distinct count 등의 정보를 가지고 있습니다. (최소값은 헤더 및 앞 Bucket의 최대값을 통해 알 수 있음)

결과를 분석해보면 ID Column값들은 고유하므로 Distinct count가 전체 Row 개수와 동일한 10,000로 나왔고, JOB이나 DEPT는 정해진 몇 개의 고유값이 있는 그대로 수치가 나온것을 확인할 수 있습니다.

• 예측치와 실제 결과를 비교하기 위해 set autotrace traceonly exp plan stat을 사용합니다.

쿼리를 수행하여 Plan을 확인합니다. (view_plan_detail_for_histogram.sql)

SQL> set lines 150
SQL> set autot traceonly exp plans
SQL> SELECT * FROM ARMY WHERE ID <= 100;
SQL> set autot off

SQL ID: 1434
Plan Hash Value: 2324995388
Execution Plan
--------------------------------------------------------------------------------
1 TABLE ACCESS (ROWID): ARMY (Cost:7, %CPU:0, Rows:100)
2   
INDEX (RANGE SCAN): ARMY_PK (Cost:2, %CPU:0, Rows:100)

Predicate Information
--------------------------------------------------------------------------------
2 - access: ("ARMY"."ID" <= 100) (0.010)

Execution Stat
--------------------------------------------------------------------------------
1 TABLE ACCESS (ROWID): ARMY (Time:.26 ms, Rows:100, CR:5, CU:0, Starts:1)
2   
INDEX (RANGE SCAN): ARMY_PK (Time:.06 ms, Rows:100, CR:2, CU:0, Starts:1)

Execution plan에서 보이는 Rows는 예측치이고, Execution Stat의 Rows는 실제 Plan 노드에서 결과로 올라가는 Row 개수입니다. ID 값은 균등하게 분포되어 있기 때문에 이와 같은 경우에서는 매우 정확하게 수치가 예측되었습니다.

1. 통계 정보 오차의 실제 계획

다음은 DEPT Column에 대한 조건문이 있는 쿼리 결과입니다. (view_plan_detail_for_histogram.sql)

SQL> set lines 150
SQL> set autot traceonly exp plans
SQL> SELECT * FROM ARMY WHERE DEPT = 'PROMISE';

SQL ID: 1446
Plan Hash Value: 1631011846
Execution Plan
----------------------------------------------------------------------------------
1  TABLE ACCESS (FULL): ARMY (Cost:211, %CPU:0, Rows:2000)

Predicate Information
----------------------------------------------------------------------------------
1 - filter: ("ARMY"."DEPT" = 'PROMISE') (0.200)

Execution Stat
----------------------------------------------------------------------------------
1 TABLE ACCESS (FULL): ARMY (Time:5.54 ms, Rows:2500, CR:481, CU:27, Starts:1)

Optimizer에서 히스토그램이 없을때는 하나의 Bucket내에 존재하는 값들의 분포도는 균등하다고 가정하기 때문 에 위의 결과 PROMISE와 함께 RETHINKING 두 값 모두 동일한 Cardinality가 예측될 것입니다. 즉, 10,000개 중 고유값이 5개이므로 10,000/5 = 2,000개로 예측한 것입니다. 

하지만 실제로는 PROMISE는 2,500건, RETHINKING은 1,250건이 존재하므로 오차가 발생했다. 위의 예제에서는 통계 결과를 기반으로 한 Optimizer의 실행 계획에 큰 문제가 되지 않지만 다음의 예제에서는 성능 저하로 이어질 수 있습니다.

SQL> SELECT * FROM ARMY WHERE JOB = 'INFANTRY';
SQL ID: 1452 

Plan Hash Value: 749881390 
Execution Plan
-------------------------------------------------------------------------------- 
1 TABLE ACCESS (ROWID): ARMY (Cost:197, %CPU:0, Rows:2000) 
2   INDEX (RANGE SCAN): ARMY_IDX_JOB (Cost:6, %CPU:0, Rows:2000)

Predicate Information
-------------------------------------------------------------------------------- 
2 - access: ("ARMY"."JOB" = 'INFANTRY') (0.200) 

Execution Stat
--------------------------------------------------------------------------------
1 TABLE ACCESS (ROWID): ARMY (Time:23.1 ms, Rows:8000, CR:472, CU:0, Starts:1) 
2   INDEX (RANGE SCAN): ARMY_IDX_JOB (Time:.68 ms, Rows:8000, CR:26, CU:0, Starts:1)

JOB Column의 히스토그램에 의하면 고유값은 5개이고 총 Row 개수는 10,000이므로 INFANTRY 조건을 만족하는 Row 개수를 2,000개로 예측한 것은 Optimizer가 맞게 예측했다는 것을 말합니다. 

하지만 실제로는 8,000개가 존재했고 2,000개라고 예측하여 INDEX RANGE SCAN + ROWID SCAN을 태웠는데 실제로는 8,000번의 ROWID SCAN이 발생하였다. 이는 잘못된 통계 정보 때문에 실행 계획까지 부정적 영향을 미친 예로 이 때문에 발생한 문제가 실제 더 대용량의 상황이었다면 치명적인 성능 저하로 이어질 수 있습니다.