sql의 조회 패턴 별 실행계획을 분기하자

여러조건을 가진 sql의 실행계획 분기하기

조회 대상테이블은 USERNAME, USERID, EMAIL의 컬럼을 가지고 있으며, INDEX 정보는 다음과 같다.

INDEX_NAME	COLUMN LIST
IDX_TB_ADDR_01	USERID, NAME
IDX_TB_ADDR_02	USERID, EMAIL

변수 설정


define b1 = A
define b2 = A
define b3 = '' -> Null로 설정

Ex sql


SELECT /*+ INDEX(A IDX_TB_ADDR_01) */ --> WHERE 절 조회 패턴에 상관없이 실행계획 고정
  COUNT(*)
FROM tb_addr a
WHERE userid = '&b1'
  AND (name LIKE '&b2' || '%'
      AND email LIKE '&b3' || '%')
;

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |  2556   (1)| 00:00:31 |
|   1 |  SORT AGGREGATE              |                |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TB_ADDR        | 16365 |   479K|  2556   (1)| 00:00:31 |
|*  3 |    INDEX RANGE SCAN          | IDX_TB_ADDR_01 | 16365 |       |    47   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EMAIL" IS NOT NULL AND "EMAIL" LIKE '%')
   3 - access("USERID"='A' AND "NAME" LIKE 'A%')
       filter("NAME" LIKE 'A%')

16:14:12 SCOTT @ GUNTEST>

위 SQL결과로는 IDX_TB_ADDR_01의 INDEX SCAN으로 조건절(USERID, NAME)에 알맞게 접근이 진행이 되고 있다.

변수 설정


define b1 = A
define b2 = '' <-- Null 로 설정
define b3 = 4

{code:sql}
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |   656   (1)| 00:00:08 |
|   1 |  SORT AGGREGATE              |                |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TB_ADDR        | 24147 |   707K|   656   (1)| 00:00:08 |
|*  3 |    INDEX RANGE SCAN          | IDX_TB_ADDR_01 |   526 |       |   575   (1)| 00:00:07 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EMAIL" LIKE '4%')
   3 - access("USERID"='A')
       filter("NAME" IS NOT NULL AND "NAME" LIKE '%')

email 조건으로 수행 시, 해당 sql은 HINT상으로 IDX_TB_ADDR_01(userid, name) index scan을 수행하여 IDX_TB_ADDR_02 존재하지만, 활용하지 못 하고 비효율이 발생한다.
따라서 name과 emai 조건에 대해 조회패턴을 분석한 후, 각 패턴별로 최적수행이 가능토록 sql을 분기해 주는 것이 좋다.

분기된 SQL


SELECT /*+ INDEX(A IDX_TB_ADDR_01) */* ---> NAME 으로만 검색할 경우
FROM tb_addr
WHERE '&b2' IS NOT NULL
  AND '&b3' IS NULL
  AND userid = '&b1'
  AND name LIKE '&b2' || '%'
UNION ALL
SELECT /*+ INDEX(A IDX_TB_ADDR_02) */* ---> EMAIL 으로만 검색할 경우
FROM tb_addr a
WHERE '&b2' IS NULL
  AND '&b3' IS NOT NULL
  AND userid = '&b1'
  AND email LIKE '&b3' || '%'
UNION ALL
SELECT /*+ INDEX(A IDX_TB_ADDR_01) */* ---> NAME, EMAIL 모두 검색할 경우
FROM tb_addr a
WHERE '&b2' IS NOT NULL
  AND '&b3' IS NOT NULL
  AND userid = '&b1'
  AND name LIKE '&b2' || '%'
  AND email LIKE '&b3' || '%'
UNION ALL
SELECT /*+ INDEX(A IDX_TB_ADDR_01) */* ---> NAME, EMAIL 모두 NULL인경우
FROM tb_addr a
WHERE '&b2' IS NULL
  AND '&b3' IS NULL
  AND userid = '&b1'
;
-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                | 29998 |   380K|  6270 (100)| 00:01:16
|   1 |  UNION-ALL                    |                |       |       |            |
|*  2 |   FILTER                      |                |       |       |            |
|*  3 |    TABLE ACCESS FULL          | TB_ADDR        |   249K|  3173K|   698   (2)| 00:00:09
|   4 |   TABLE ACCESS BY INDEX ROWID | TB_ADDR        | 29995 |   380K|  6270   (1)| 00:01:16
|*  5 |    INDEX RANGE SCAN           | IDX_TB_ADDR_02 | 30054 |       |    90   (0)| 00:00:02
|*  6 |   FILTER                      |                |       |       |            |
|*  7 |    TABLE ACCESS BY INDEX ROWID| TB_ADDR        | 29995 |   380K| 32807   (1)| 00:06:34
|*  8 |     INDEX RANGE SCAN          | IDX_TB_ADDR_01 |   249K|       |   563   (1)| 00:00:07
|*  9 |   FILTER                      |                |       |       |            |
|  10 |    TABLE ACCESS BY INDEX ROWID| TB_ADDR        |   249K|  3173K| 32804   (1)| 00:06:34
|* 11 |     INDEX RANGE SCAN          | IDX_TB_ADDR_01 |   249K|       |   561   (1)| 00:00:07
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NULL IS NOT NULL AND NULL IS NOT NULL)
   3 - filter("USERID"='A' AND "NAME" LIKE '%' AND "NAME" IS NOT NULL)
   5 - access("USERID"='A' AND "EMAIL" LIKE '4%')
       filter("EMAIL" LIKE '4%')
   6 - filter(NULL IS NOT NULL)
   7 - filter("EMAIL" LIKE '4%')
   8 - access("USERID"='A')
       filter("NAME" LIKE '%' AND "NAME" IS NOT NULL)
   9 - filter(NULL IS NOT NULL)
  11 - access("USERID"='A')

14:51:34 SCOTT @ GUNTEST>


최근에는 LIKE '%' 형태의 SQL로 여러 조회 패턴을 처리하는 방법을 사용하기 보다는，프로그램 (XML 등)에서 조회 조건을 다이나믹하게 추가하여，
조회 패턴 별로 각각 다른 SQL 이 수행되도록 구현하는 경우가많다.
이 방법은 LIKE '%' 보다 훨씬 개선된 방법이지만, Optimizer 의 잘못된 해석으로 극심한 성능 문제가 발생하거나 잘못된 힌트(고정힌트적용)를 적용하여
성능 문제가 발생하는 경우들은 여전히 존재하므로 조회 패턴별로 실행계획을 분리할 수 있게 SQL 을 작성하는 것을 검토하여야 한다.
또한 여러 패턴을 가진 SQL 이외에도 SQL조건 값의 범위나 LENGTH에 따라 INDEX를 수행할지, FULL TABLE SCAN을 해야 할지 검토하여야 한다.

여러 조회 때턴 처리를 하나의 SQL 로 작성한 경우 (LlKE, NVL, DECODE 등)
조회 변수 값의 범위에 따라 다른 실행계획이 필요한 경우

변수 값의 범위에 따라 SQL를 분리 하자.


선언되는 변수가 날짜형식이나, 숫자 등 범위를 형성할 수 있는 경우를 뜻한다.
추출되는 데이터 건수가 적은 경우에는 INDEX SCAN으로 수행되고, 그렇지 않은 경우에는 FULL TABLE SCAN을 유도하는 것이 바람직하다.
예를 들어 일별 500명의 신규회원이 생성되는 테이블 내에서 하루 단위의 범위 내에서는 INDEX SCAN으로 약 500건의 데이터를 조회하는 것이 효율적이나,
1년치의 가입회원 수를 조회한다면, 약 182,500(500*365)건의 데이터가 추출되므로 FULL TABLE SCAN을 유도한다.

조회 대상테이블은 CUST (REG_EDIT, NO) 컬럼을 가지고 있으며, INDEX 정보는 다음과 같다.

INDEX_NAME	COLUMN LIST
IDX_CUST_01	REG_EDIT, NO

조회 SQL


SELECT /*+ index(CUST IDX_CUST_01) */ COUNT(*)
 FROM CUST
 WHERE REG_DATE BETWEEN '&B1' AND '&B2';

변수 설정(1일 범위) \--> 조회 조건이 특정값인 경우.


define b1 = '20131130'
define b2 = '20131130'

Execution Plan
----------------------------------------------------------
Plan hash value: 4090629016

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |     9 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_CUST_01 |   100 |   900 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("REG_DATE"='20131130')

변수 설정(1년 범위) \--> 조회 조건이 전체 범위인 경우.


define b1 = '20121130'
define b2 = '20201130'

Execution Plan
----------------------------------------------------------
Plan hash value: 4090629016

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |     9 |   712   (1)| 00:00:09 |
|   1 |  SORT AGGREGATE   |             |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_CUST_01 |   253K|  2226K|   712   (1)| 00:00:09 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("REG_DATE">='20121130' AND "REG_DATE"<='20201130')

위 조건을 동일하게 Full scan으로 수행시


Execution Plan
----------------------------------------------------------
Plan hash value: 1009484579

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |   662   (2)| 00:00:08 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| CUST |   253K|  2226K|   662   (2)| 00:00:08 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("REG_DATE"<='20201130' AND "REG_DATE">='20121130')

범위에 따라 분기된 SQL

범위조건 중에서 단일 조건인 경우와 전체 범위 조건인 경우을 조회패턴을 분리 후, UNION ALL로 결합하였다.


select /*+ index(CUST IDX_CUST_01) */
       count(*)
from   CUST
where  ('&b2' - '&b1') < 10000 -- 1년 이하로 차이나는 경우 Index scan 유도
and reg_date between '&b1' and '&b2'
union all
select /*+ FULL(CUST) */
       count(*)
from   CUST
where  ('&b2' - '&b1') > 10000 -- 1년 이상 차이나는 경우는 Full scan을 유도
and reg_date between '&b1' and '&b2'
;

Result

단일조건


Execution Plan
----------------------------------------------------------
Plan hash value: 2414340225

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     2 |    18 |     3   (0)| 00:00:01 |
|   1 |  UNION-ALL           |             |       |       |            |          |
|   2 |   SORT AGGREGATE     |             |     1 |     9 |            |          |
|*  3 |    INDEX RANGE SCAN  | IDX_CUST_01 |   100 |   900 |     3   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE     |             |     1 |     9 |            |          |
|*  5 |    FILTER            |             |       |       |            |          |
|*  6 |     TABLE ACCESS FULL| CUST        |   100 |   900 |   661   (2)| 00:00:08 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("REG_DATE"='20131201')
   5 - filter(NULL IS NOT NULL)
   6 - filter("REG_DATE"='20131201')

전역 범위 조건


Execution Plan
----------------------------------------------------------
Plan hash value: 1525413787

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     2 |    18 |   662 (100)| 00:00:08 |
|   1 |  UNION-ALL          |             |       |       |            |          |
|   2 |   SORT AGGREGATE    |             |     1 |     9 |            |          |
|*  3 |    FILTER           |             |       |       |            |          |
|*  4 |     INDEX RANGE SCAN| IDX_CUST_01 |   253K|  2227K|   713   (1)| 00:00:09 |
|   5 |   SORT AGGREGATE    |             |     1 |     9 |            |          |
|*  6 |    TABLE ACCESS FULL| CUST        |   253K|  2227K|   662   (2)| 00:00:08 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(NULL IS NOT NULL)
   4 - access("REG_DATE">='20121201' AND "REG_DATE"<='20201201')
   6 - filter("REG_DATE"<='20201201' AND "REG_DATE">='20121201')

10g 이상에서 V$SQL_BIND_CAPTURE와 DBA_HIST_SQLBIND View를 조회하여 바인드 변수 값을 확인 해볼 수 있다.


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 조회 끝 값

Bind 변수의 경우의 수와 범위를 확인하여 패턴별로 실행계획을 분리할 수 있게 SQL을 작성하는 것을 검토한다.

2. SQL의 조회 패턴 별 실행계획을 분기하자. (by shotme) [2013.11.30]

sql의 조회 패턴 별 실행계획을 분기하자

여러조건을 가진 sql의 실행계획 분기하기

분기된 SQL

변수 값의 범위에 따라 SQL를 분리 하자.

범위에 따라 분기된 SQL