오라클 성능 고도화 원리와 해법 II (2012년)
- 선택도 : 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율
- 히스토그램이 없을 때 선택도 ( 등치 )
- 히스토그램이 없을 때 선택도 ( 범위 )
- 카디널리티
- 선택도 및 카디널리티 계산식 테스트
- Null 값을 포함할 때
- 널값을 제외한 선택도 공식
- 조건절이 두 개 이상일 때
- 범위 검색 조건일 때
- cardinality 힌트를 이용한 실행계획 제어
선택도 : 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율
- 선택도 -> 가디널리티 -> 비용 -> 엑세스 방식, 조인 순서, 조인 방법 등 결정
히스토그램이 없을 때 선택도 ( 등치 )
1 1
선택도 = -------------------- = --------------------
Distinct Value 개수 num_distinct
히스토그램이 없을 때 선택도 ( 범위 )
- num_rows : 1,000,000
- num_distinct : 100
- low_value : 1
- high_value : 1,000
조건절에서 요청한 값 범위
선택도 = --------------------------
전체 값 범위
no > 500 : (1,000-500) / (1,000-1) = 0.5
no >= 500 : (1,000-500) / (1,000-1) + 1/100 = 0.51
no bwtwwen 500 and 1000 : (1,000-500) / (1,000-1) + 1/100 + 1/100 = 0.52
조건절에서 요청한 값 범위
선택도 = --------------------------
전체 값 범위
no > 500
1,000 - 500 ( high_value - 비교값 )
선택도 = -------------------------- = 0.5
1,000 - 1 ( high_value - low_value )
no < 500
500 - 1 ( 비교값 - 1 )
선택도 = -------------------------- = 0.499
1,000 - 1 ( high_value - low_value )
카디널리티
- 특정 액세스 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수를 말한다.
- DV = 10 이면 선택도 = 0.1이고, 총 로우 수가 1,000이라면 카디널리티 100된다.
카디널리티 = 총 로우 수 * 선택도 = num_rows / num_distinct
ex )
선택도 및 카디널리티 계산식 테스트
SQL> CREATE TABLE T_EMP AS
2 SELECT B.NO, A.*
3 FROM (SELECT * FROM SCOTT.EMP WHERE ROWNUM <= 10 ) A
4 , (SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 100) B;
테이블이 생성되었습니다.
SQL> begin
2 dbms_stats.gather_table_stats( user, 'T_EMP', method_opt=> 'for all columns size 1' );
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
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
SQL> SELECT COUNT(*) FROM T_EMP WHERE JOB = 'SALESMAN';
COUNT(*)
----------
400
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 2 | TABLE ACCESS FULL| T_EMP | 1 | 200 | 400 |00:00:00.01 | 8 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='SALESMAN')
SQL> SELECT T.NUM_ROWS, C.NUM_NULLS, C.NUM_DISTINCT
2 , 1/C.NUM_DISTINCT SELECTIVITY
3 , NUM_ROWS/C.NUM_DISTINCT AS CRADINALITY
4 FROM USER_TABLES T, USER_TAB_COLUMNS C
5 WHERE T.TABLE_NAME = 'T_EMP'
6 AND C.TABLE_NAME = T.TABLE_NAME
7 AND C.COLUMN_NAME = 'JOB'
8 ;
NUM_ROWS NUM_NULLS NUM_DISTINCT SELECTIVITY CRADINALITY
---------- --------- ------------ ----------- -----------
1000 0 5 .2 200
-- 히스토그램 맛보기
SQL> begin
2 dbms_stats.gather_table_stats( user, 'T_EMP', method_opt=> 'for all columns size 5' ); --도수 분포 히스토그램
3 end;
4 /
SQL> SELECT COUNT(*) FROM T_EMP WHERE JOB = 'CLERK';
COUNT(*)
----------
100
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> @XPLAN
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 2 | TABLE ACCESS FULL| T_EMP | 1 | 100 | 100 |00:00:00.01 | 8 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='CLERK')
SQL> SELECT COUNT(*) FROM T_EMP WHERE JOB = 'SALESMAN';
COUNT(*)
----------
400
SQL> @XPLAN
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 2 | TABLE ACCESS FULL| T_EMP | 1 | 400 | 400 |00:00:00.01 | 8 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='SALESMAN')
-- 히스토그램 바인드
SQL> SELECT COUNT(*) FROM T_EMP WHERE JOB = :JOB;
COUNT(*)
----------
0
SQL> @XPLAN
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |
|* 2 | TABLE ACCESS FULL| T_EMP | 1 | 200 | 0 |00:00:00.01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"=:JOB)
Null 값을 포함할 때
SQL> update t_emp set job = null where no <= 50;
500 행이 갱신되었습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> select count(*) from t_emp where job is null;
COUNT(*)
----------
500
SQL> begin
2 dbms_stats.gather_table_stats( user, 'T_EMP', method_opt=> 'for all columns size 1' ); --히스토그램 제거
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> COLUMN NUM_NULLS FORMAT 9999999
SQL>
SQL> SELECT NUM_NULLS, NUM_DISTINCT
2 FROM USER_TAB_COLUMNS
3 WHERE TABLE_NAME = 'T_EMP'
4 AND COLUMN_NAME = 'JOB';
NUM_NULLS NUM_DISTINCT
--------- ------------
500 5
SQL>
널값을 제외한 선택도 공식
1 NULL 값을 제외한 로우 수
선택도 = -------------------- * --------------------
Distinct Value 개수 총 로우 수
1 (NUM_ROWS - NUM_NULLS)
= -------------------- * ------------------------
NUM_DISTINCT NUM_ROWS
1 - ( NUM_NULLS / NUM_ROWS )
= ----------------------------
NUM_DISTINCT
1 - ( 500 / 1000 )
= ---------------------------- = 0.1
5
카디널리티 = 1000 * 0.1 = 100
SQL> SELECT COUNT(*) FROM T_EMP WHERE JOB = :JOB;
COUNT(*)
----------
0
SQL> @XPLAN
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |
|* 2 | TABLE ACCESS FULL| T_EMP | 1 | 100 | 0 |00:00:00.01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"=:JOB)
조건절이 두 개 이상일 때
- 카디널리티 : 각 컬럼의 선택도 * 전체 로우수
SQL> SELECT T.NUM_ROWS, C.NUM_NULLS, C.NUM_DISTINCT
2 , ( 1-C.NUM_NULLS/T.NUM_ROWS)/C.NUM_DISTINCT SELECTIVITY
3 FROM USER_TABLES T, USER_TAB_COLUMNS C
4 WHERE T.TABLE_NAME = 'T_EMP'
5 AND C.TABLE_NAME = T.TABLE_NAME
6 AND C.COLUMN_NAME IN( 'JOB', 'DEPTNO' )
7 ;
NUM_ROWS NUM_NULLS NUM_DISTINCT SELECTIVITY
---------- --------- ------------ -----------
1000 500 5 .1
1000 0 3 .333333333
SQL> SELECT COUNT(*) FROM T_EMP WHERE JOB = :JOB AND DEPTNO = :DEPTNO;
COUNT(*)
----------
0
SQL> @XPLAN
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |
|* 2 | TABLE ACCESS FULL| T_EMP | 1 | 33 | 0 |00:00:00.01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("JOB"=:JOB AND "DEPTNO"=:DEPTNO))
범위 검색 조건일 때
조건절에서 요청한 값 범위
선택도 = --------------------------
전체 값 범위
SQL> CREATE TABLE T
2 AS
3 SELECT ROWNUM NO1
4 , CASE WHEN ROWNUM <= 1000 OR ROWNUM > 9000 THEN ROWNUM ELSE 5000 END NO2
5 FROM DUAL
6 CONNECT BY LEVEL <= 10000;
테이블이 생성되었습니다.
SQL> begin
2 dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all columns size 1' ); --히스토그램 제거
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT MAX( NO1 ) AS MAX_1, MIN( NO1 ) AS MIN_1
2 , MAX( NO2 ) AS MAX_2, MIN( NO2 ) AS MIN_2
3 FROM T;
MAX_1 MIN_1 MAX_2 MIN_2
---------- ---------- ---------- ----------
10000 1 10000 1
-- NO1 : 1 ~ 10000
-- NO2 : 1~1000, 9001 ~ 10000
SQL> SELECT COUNT(*) FROM T WHERE NO1 > 3000;
COUNT(*)
----------
7000
SQL> @XPLAN
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 19 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 19 |
|* 2 | TABLE ACCESS FULL| T | 1 | 7001 | 7000 |00:00:00.01 | 19 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NO1">3000)
19 개의 행이 선택되었습니다.
SQL> SELECT COUNT(*) FROM T WHERE NO2 > 3000;
COUNT(*)
----------
9000
SQL> @XPLAN
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 19 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 19 |
|* 2 | TABLE ACCESS FULL| T | 1 | 7001 | 9000 |00:00:00.01 | 19 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NO2">3000)
SQL> begin
2 dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all columns size 254' ); --히스토그램 생성
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT COUNT(*) FROM T WHERE NO1 > 3000;
COUNT(*)
----------
7000
SQL> @XPLAN
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 19 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 19 |
|* 2 | TABLE ACCESS FULL| T | 1 | 7001 | 7000 |00:00:00.01 | 19 |
-------------------------------------------------------------------------------------
SQL> SELECT COUNT(*) FROM T WHERE NO2 > 3000;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 19 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 19 |
|* 2 | TABLE ACCESS FULL| T | 1 | 7001 | 9000 |00:00:00.01 | 19 |
-------------------------------------------------------------------------------------
SQL> SELECT COUNT(*) FROM T WHERE NO1 > 5000;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 19 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 19 |
|* 2 | TABLE ACCESS FULL| T | 1 | 5047 | 5000 |00:00:00.01 | 19 |
-------------------------------------------------------------------------------------
SQL> SELECT COUNT(*) FROM T WHERE NO2 > 5000;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 19 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 19 |
|* 2 | TABLE ACCESS FULL| T | 1 | 1004 | 1000 |00:00:00.01 | 19 |
-------------------------------------------------------------------------------------
cardinality 힌트를 이용한 실행계획 제어
SQL> SELECT /*+ USE_HASH( D E ) */ * FROM SCOTT.DEPT D, SCOTT.EMP E
2 WHERE D.DEPTNO = E.DEPTNO
3 ;
...
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 14 | | | |
|* 1 | HASH JOIN | | 1 | 14 | 14 |00:00:00.01 | 14 | 1000K| 1000K| 747K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 7 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
SQL> SELECT /*+ USE_HASH( D E ) CARDINALITY(D 16) */ * FROM SCOTT.DEPT D, SCOTT.EMP E
2 WHERE D.DEPTNO = E.DEPTNO
3 ;
...
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 14 | | | |
|* 1 | HASH JOIN | | 1 | 56 | 14 |00:00:00.01 | 14 | 865K| 865K| 664K (0)|
| 2 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL| DEPT | 1 | 16 | 4 |00:00:00.01 | 8 | | | |
----------------------------------------------------------------------------------------------------------------
SQL> SELECT /*+ USE_HASH( D E ) OPT_ESTIMATE( TABLE, D, SCALE_ROWS=4 ) */ * FROM SCOTT.DEPT D, SCOTT.EMP E
2 WHERE D.DEPTNO = E.DEPTNO ;
...
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 14 | | | |
|* 1 | HASH JOIN | | 1 | 5726 | 14 |00:00:00.01 | 14 | 865K| 865K| 632K (0)|
| 2 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL| DEPT | 1 | 1636 | 4 |00:00:00.01 | 8 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3279
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.