SQL 튜닝의 시작 (2013년)
SQL의 조회 패턴별 실행계획을 분기하자
0
0
99,999+
by 구루비스터디 [2018.07.14]
SQL의 조회 패턴 별 실행계획을 분리하자
여러 조건을 가진 SQL의 실행계획 분리하기
SELECT /*+ INDEX(A IDX_TB_ADDR_01) */ --> WHERE 절 조회 패턴에 상관없이 실행계획 고정
a.*
FROM imsi.tb_addr a
WHERE userid = :b1
AND (name LIKE :b2||'%' OR
email LIKE :b3||'%')
| INDEX_NAME | COLUMN LIST |
|---|---|
| IDX_TB_ADDR_01 | USERID, NAME |
| IDX_TB_ADDR_02 | USERID, EMAIL |
| IDX_TB_ADDR_03 | UKEY |
| PK_TB_ADDR | USERID, UKEY |
- userid 컬럼만으로 조회 될 때 데이터 건수가 많아 I/O 처리량이 너무 많이 발생함.
- 따라서 userid 컬럼과 추가 조건으로 구성된 결합 인덱스를 이용해 조회가 되야 효율적일 것임.
- 위 SQL은 :b2 조건 값이 null 이면서 email 컬럼 값으로 조회되는 경우 비효율적으로 수행되고 있음.
- IDX_TB_ADDR_01 인덱스에서 userid 값에 해당하는 데이터를 찾은 후 테이블 액세스 후 email 값이 필터 처리되는 비효율이 있음.
- 그러므로 SQL을 패턴 별로 분리한 후 조회되는 조건에 따라 가장 유리한 SQL을 선택하여 수행하도록 해야 됨.
- 하나의 SQL로 해야된다면 UNION ALL 을 이용해서 실행계획을 분리시키는 방법도 있음.
- UNION ALL 사용하는 방법은 분리해야 될 조건이 많다면 SQL이 길어지고 파싱시 부담이 될 수 있음.
테스트
-- 테이블 생성
drop table t1;
create table t1 as
select chr(65 + mod(level,4)) as c1
, chr(65 + mod(level,26)) || 'X' as c2
, level || 'X' as c3
from dual
connect by level <= 1000000
;
-- 인덱스 생성
create index t1_idx01 on t1(c1,c2);
create index t1_idx02 on t1(c1,c3);
-- 실행 쿼리
select /*+ index(t1 t1_idx01) */
count(*)
from t1
where c1 = '&b1'
and c2 like '&b2' || '%'
and c3 like '&b3' || '%'
;
-- 변수 설정 (b3 값 null 설정)
define b1 = A
define b2 = AX
define b3 = ''
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1347 (1)| 00:00:17 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 10245 | 130K| 1347 (1)| 00:00:17 |
|* 3 | INDEX RANGE SCAN | T1_IDX01 | 10245 | | 25 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
-- 효율적으로 T1_IDX01 인덱스 액세스
-- 변수 설정 (b2 값 null 설정)
define b1 = A
define b2 = ''
define b3 = 4X
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 33315 (1)| 00:06:40 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 4 | 52 | 33315 (1)| 00:06:40 |
|* 3 | INDEX RANGE SCAN | T1_IDX01 | 253K| | 574 (2)| 00:00:07 |
-----------------------------------------------------------------------------------------
-- t1_idx02 인덱스를 액세스하는 것이 효율적이지만 실행이 고정되어 있어서 T1_IDX01 인덱스 액세스 함.
-- 개선 후 SQL (SQL을 분리함)
select count(*)
from (select /*+ index(t1 t1_idx01) */
*
from t1
where 1 = &div
and c1 = '&b1'
and c2 like '&b2' || '%'
union all
select /*+ index(t1 t1_idx02) */
*
from t1
where 2 = &div
and c1 = '&b1'
and c3 like '&b3' || '%'
)
;
-- 변수 설정 (b3 값 null 설정)
define div = 1
define b1 = A
define b2 = AX
define b3 = ''
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 25 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 10246 | | 25 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | INDEX RANGE SCAN | T1_IDX01 | 10245 | 51225 | 25 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | INDEX RANGE SCAN| T1_IDX02 | 253K| 2478K| 748 (1)| 00:00:09 |
---------------------------------------------------------------------------------
-- T1_IDX01 인덱스 액세스
-- 변수 설정 (b2 값 null 설정)
define div = 2
define b1 = A
define b2 = ''
define b3 = 4X
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 5 | | 3 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
|* 5 | INDEX RANGE SCAN| T1_IDX01 | 253K| 1239K| 574 (2)| 00:00:07 |
|* 6 | INDEX RANGE SCAN | T1_IDX02 | 4 | 40 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
-- T1_IDX02 인덱스 액세스
변수 값의 범위에 따라 SQL을 분리하자
- SQL을 조회하는 변수 값에 의해 추출되는 데이터 건수의 차이에 따라 다른 실행계획으로 수행되도록 유도하는 것을 말함.
- 예를 들면, 추출 데이터가 적은 경우는 인덱스 스캔으로 많은 경우는 Full Table Scan 수행.
select *
from cust
where reg_date between :B1 and :B2
;
- REG_DATE : 고객 가입날짜 컬럼
- 하루 평균 가입 고객수 : 500명
- 조회 패턴 : 1일 조회가 대부분. 한달에 한번은 최근 일년간의 데이터 추출.
- 조회 패턴에 따른 추출 데이터 건수 : 1일 조회 - 500건, 1년 조회 - 365일 * 500 = 182,500건
테스트
-- 테이블 생성
drop table t1;
create table t1 as
select to_char(sysdate + mod(level,10000),'YYYYMMDD') c1
, trunc(level/10000) c2
from dual
connect by level <= 1000000
;
-- 인덱스 생성
create index t1_idx01 on t1(c1);
-- 실행 쿼리
select /*+ index(t1 t1_idx01) */
*
from t1
where '&b1' = '&b2'
and c1 between '&b1' and '&b2'
union all
select /*+ full(t1) */
*
from t1
where '&b1' <> '&b2'
and c1 between '&b1' and '&b2'
;
-- 변수 설정 (시작일과 종료일이 동일)
define b1 = '20131201'
define b2 = '20131201'
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 1919 | 100 (0)| 00:00:02 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 1900 | 100 (0)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | T1_IDX01 | 100 | | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL | T1 | 100 | 1900 | 667 (3)| 00:00:09 |
-----------------------------------------------------------------------------------------
-- 시작일과 종료일이 동일하므로 상단 쿼리만 실행되어서 인덱스 액세스 함.
-- 변수 설정 (1년 조회)
define b1 = '20131201'
define b2 = '20141131'
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46248 | 858K| 667 (100)| 00:00:09 |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 46247 | 858K| 44734 (1)| 00:08:57 |
|* 4 | INDEX RANGE SCAN | T1_IDX01 | 46247 | | 130 (1)| 00:00:02 |
|* 5 | TABLE ACCESS FULL | T1 | 46247 | 858K| 667 (3)| 00:00:09 |
------------------------------------------------------------------------------------------
-- 시작일과 종료일이 다르므로 하단 쿼리만 실행되어서 풀테이블 스캔 함.
- 10g 이상에서 V$SQL_BIND_CAPTURE와 DBA_HIST_SQLBIND 뷰를 이용해 바인드 변수 값 확인 가능함.
SELECT name,
TO_CHAR(last_captured,'yyyymmdd hh24:mi:ss') last_captured,
datatype,
value_string
FROM DBA_HIST_SQLBIND
WHERE sql_id = :sql_id ;
NAME LAST_CAPTURED DATATYPE VALUE_STRING
---- ----------------- ------- -----------
:B65 20101213 14:46:06 1 201011
:B66 20101213 14:46:06 1 0403
:B67 20101213 14:46:06 1 00000000000 ---> SN_NO 조회 시작 값
:B68 20101213 14:46:06 1 99999999999 ---> SN_NO 조회 끝 값
:B65 20101227 16:06:59 1 201012
:B66 20101227 16:06:59 1 0331
:B67 20101227 16:06:59 1 57011155797 ---> SN_NO 조회 시작 값
:B68 20101227 16:06:59 1 57011155797 ---> SN_NO 조회 끝 값
-- 크게 두 가지 패턴으로 나뉜다는 것을 알 수 있음.
-- 두 가지 패턴에 따른 SQL 분리를 하면 됨.
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3822
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.