'분류 전체보기' 카테고리의 글 목록 (4 Page)

Index Unque Scan은 SQL을 변경시킨다

Oracle/Optimizer 2010. 1. 20. 13:48

INDEX UNIQUE SCAN의 비밀
당신은 INDEX UNIQUE SCAN을 쉬운 Operation쯤으로 여길 것이다. 하지만 여기에는 숨겨진 기능이 있다. 대표적인 경우가 Unique 인덱스를 사용하여 INDEX UNIQUE SCAN Operation이 나오면 Distinct를 제거하는 기능이다. 이 기능의 이름이 없으므로 DEUI*(Distinct Elimination using Unique Index)라 부르기로 하자. 이제 아래의 SQL을 보자.

SELECT DISTINCT d.department_id, l.city, l.country_id

FROM department d, location l

WHERE d.location_id = l.location_id

AND d.department_id = 10 ;

--------------------------------------------------+-----------------------------------+

--------------------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 3 | |

| 1 | NESTED LOOPS | | 1 | 22 | 2 | 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT| 1 | 7 | 1 | 00:00:01 |

| 3 | INDEX UNIQUE SCAN | DEPT_ID_PK| 1 | | 0 | |

| 4 | TABLE ACCESS BY INDEX ROWID | LOCATION | 23 | 345 | 1 | 00:00:01 |

| 5 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | | 0 | |

--------------------------------------------------+-----------------------------------+

Predicate Information:

----------------------

3 - access("D"."DEPARTMENT_ID"=10)

5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

SQL이 변경되었다
Distinct에 의한 Sort Unique 혹은 Hash Unique가 사라졌다. 그 이유는 테이블 department와 location에서 모두 INDEX UNIQUE SCAN을 사용하였기 때문이다. 하지만 반대로 Unique 인덱스를 사용하지 않는다면 DEUI는 절대 수행되지 않는다. 아래의 SQL은 위의 SQL에서 힌트만 추가한 것이다.

SELECT /*+ FULL(d) */ DISTINCT d.department_id, l.city, l.country_id

FROM department d, location l

WHERE d.location_id = l.location_id

AND d.department_id = 10 ;

---------------------------------------------------+-----------------------------------+

---------------------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 5 | |

| 1 | HASH UNIQUE | | 1 | 22 | 5 | 00:00:01 |

| 2 | NESTED LOOPS | | | | | |

| 3 | NESTED LOOPS | | 1 | 22 | 4 | 00:00:01 |

| 4 | TABLE ACCESS FULL | DEPARTMENT| 1 | 7 | 3 | 00:00:01 |

| 5 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | | 0 | |

| 6 | TABLE ACCESS BY INDEX ROWID | LOCATION | 23 | 345 | 1 | 00:00:01 |

---------------------------------------------------+-----------------------------------+

Predicate Information:

----------------------

4 - filter("D"."DEPARTMENT_ID"=10)

5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

순간의 실수로
Department 테이블을 Full Scan 하자 Plan 상에 HASH UNIQUE가 나타났다. 다시 말해서 옵티마이져의 실수로 다른 종류의 인덱스를 사용하거나 Full Scan이 된다면 Query Transformation은 발생되지 않는다.

결론

우리가 무심코 사용하는 Unique Index의 INDEX UNIQUE SCAN Operation은 DEUI라는 기능을 내장하고 있다. 당연한 기능이라고 여기겠지만 이것을 모르면 많은 것을 놓친다. 서브쿼리가 Unnesting되어 Driving 집합이 되면 메인쿼리의 집합을 보존하기 위하여 Default로 Distinct가 추가된다. 이제 INDEX UNIQUE SCAN을 사용하는 서브쿼리가 Unnesting되어 Driving 집합이 될 때 Distinct가 사라진 비밀을 이야기 할 수 있겠는가? Unique 한 값을 가지는 컬럼이나 컬럼조합을 Normal 인덱스가 아닌 Unique 인덱스로 만들어야 할 이유를 알겠는가? 단순한 Operation 하나라도 무시할 수 없는 이유가 된다.

PS
다음시간에는 DEUI의 사촌격인 DE에 대하여 논의하자.
이번 내용도 집필중인 책의 일부분이다. 이제 어떤 것을 주제로 한 책인지 감이 오지 않는가?

'Oracle > Optimizer' 카테고리의 다른 글

해결사 되기 (15)	2010.02.04
Distinct Elimination : 불필요한 Distinct를 제거하라 (6)	2010.01.25
내가 사용한 Hint 가 무시되는 이유 (10)	2010.01.04
Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다 (14)	2009.12.07
MERGE 문과 IN 조건이 만난다면 (8)	2009.11.26

Posted by extremedb

,

오렌지나 TOAD에서 Predicate Information을 참조하는 방법

Oracle/Data Access Pattern 2010. 1. 12. 00:01

지인으로부터 아래와 같은 질문을 받았다.

질문 : Predicate Information을 참조하려면 DBMS_XPLAN 패키지를 사용해야만 합니까?

저희 개발자들은 DBMS_XPLAN 패키지를 사용할 권한이 없습니다.
따라서 오렌지나 TOAD에서 간단히 볼 수 있는 방법이 필요합니다. 가능 합니까?

답변 : 볼 수 있습니다. 단 PLAN_TABLE을 볼수 있는 권한은 있어야 합니다.

요청: 그건 있습니다. 방법을 블로그에 올려주시면 나머지 사람들도 볼수 있겠네요. 올려주시죠.

이렇게 해서 이글을 작성 하게 되었다. 이런 질문을 받았다는 것은 2가지 의미로 해석할 수 있다. 첫번째, 의외로 오렌지나 TOAD의 기능을 모르는 사람이 많이 있을 수 있다는 의미다. 두번째, 튜닝에 필요한 권한이 개발자에게 없다는 안타까움 이다. 이 정책은 매우 아쉬운 선택이며 앞으로 개선되기를 기대해본다. 하지만 수정이 필요한 법이나 악법도 법이므로 수정되기 전까진 따라야 한다.

Predicate Information과 관련된 가장 흔한 오류는 10046 이벤트 + tkprof를 사용하면 Predicate Information을 볼수 있다고 착각 하는 것이다. 절대 볼수 없다.

Predicate Information이 뭐지?
Predicate Information이란 인덱스 scan 시의 컬럼 액세스 정보, 조인정보, filter 정보를 각 Opreation 단위로 나타낸 것이다. 아래의 예제를 보자.

explain plan for

SELECT /*+ LEADING(e) USE_NL(d) */

e.employee_id, e.first_name, e.last_name, e.email, e.salary

FROM employee e, department d

WHERE e.department_id = d.department_id

AND e.job_id = 'SH_CLERK';

select * from table(dbms_xplan.display);

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 20 | 860 | 3 (0)|

|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 20 | 860 | 3 (0)|

|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 20 | | 1 (0)|

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("E"."DEPARTMENT_ID" IS NOT NULL) --> FILER 정보가 출력됨

2 - access("E"."JOB_ID"='SH_CLERK') --> INDEX SCAN 정보 혹은 JOIN 정보가 출력됨

FILTER와 ACCESS 정보는 중요하다

위에서 출력된 Predicate Information을 보면 FILTER 와 INDEX SCAN 정보를 정확히 볼 수 있다. 특히 인덱스가 여러 개의 컬럼으로 구성된 경우 몇 번째 컬럼까지 액세스 되었는지 보려면 Predicate Information이 필수적인 것이다. 예를 들어 인덱스가 COL1 + COL2 + COL3로 되어 있는데 Predicate Information에서 INDEX를 SCAN에 사용된 컬럼이 하나뿐이고 COL2와 COL3는 테이블의 FILTER로 풀린다면 성능에 문제가 될 수 있다. 따라서 Predicate Information을 확인 하는 것은 매우 중요한 것이다.

문제는 이처럼 중요한 정보를 DBMS_XPLAN 패키지를 사용하지 않고 'TOAD나 오렌지에서 어떻게 볼수 있냐' 이다.

지금부터 따라 해보기 바란다.

1. TOAD에서 Predicate Information 보기

먼저 토드 화면에서 EXPLAIN PLAN을 실행한다. EXPLAIN PLAN은 구급차 아이콘을 누르면 된다.

그러면 위와 같은 화면이 출력될 것이다. 위의 화면에서는 Predicate Information가 없다. 지금부터 Predicate Information을 추가해보자.

먼저 TOAD 화면의 하단(Explain Plan) 탭에서 오른쪽 버튼을 클릭한다. 연이어 Adjust Content를 선택한다.

그러면 위와 같은 화면이 뜨는데 여기서 Access Predicates와 Filter Predicates의 Visible 항목을 체크하고 OK를 클릭한다.

위와 같이 TOAD에서 Access Predicates와 Filter Predicates가 깔끔하게 출력되었다.

2. 오렌지에서 Predicate Information 보기

오렌지를 사용한다면 PLAN TOOL에서 Show Plan버튼을 클릭하거나 Function 키 F5를 누르고 하단의 세번째 탭을 클릭하면 아래의 그림처럼 Predicate Information을 볼 수 있다.

확인하는 습관이 필요해
이로써 어디서든 무엇을 사용하든 버튼 클릭 만으로 Predicate Information을 볼 수 있게 되었다. 이제부터 Predicate Information을 애용하기 바란다. 특히 Index Scan 시에 몇번째 컬럼까지 이용하였는지 확인하는 습관이 필요하다.

PS :
필자는 TOAD나 오렌지의 제조사나 판매사와는 상관없는 사람이다. 단지 가끔 이용할 뿐...

'Oracle > Data Access Pattern' 카테고리의 다른 글

Bloom Filter와 Group By의 관계 (10)	2010.09.09
Inlist Iterator를 사용하지 말아야 할 때 (13)	2010.06.23
Partition Access Pattern (13)	2009.12.24
모든 상식을 의심하라 (16)	2009.11.05
Oracle Data Access Pattern을 정복하라 (18)	2009.10.29

Posted by extremedb

,

내가 사용한 Hint 가 무시되는 이유

Oracle/Optimizer 2010. 1. 4. 00:49

Query Transformation을 모르면 튜닝을 할 수 없다

위의 말을 보고 많은 독자들이 말도 안 된다고 생각할 것이다. Logical Optimizer와 그 결과물인 Query Transformation을 잘 알지 못했지만 지금껏 튜닝을 성공적으로 했다고 생각하는 사람이 많이 있기 때문이다. 하지만 과연 그럴까? 아래의 SQL 을 보고 Query Transformation 과 Logical Optimizer 가 얼마나 중요한지 알아보자.

여기서 한단계 더 나아가서 뷰(인라인뷰가 아니다) 내부의 테이블에 대하여 조인순서 및 Access Path 를 바꿀 수 있는 방법에 대해 논의 해보자.

준비
테스트를 위하여 인덱스 3개와 뷰 하나를 만들자.

CREATE INDEX loc_postal_idx ON location (postal_code);

CREATE INDEX dept_name_idx ON department (department_name);

CREATE INDEX coun_region_idx ON country (region_id);

CREATE OR REPLACE VIEW v_dept AS

SELECT d.department_id, d.department_name, d.manager_id, l.location_id,

l.postal_code, l.city, c.country_id, c.country_name, c.region_id

FROM department d, location l, country c

WHERE d.location_id = l.location_id

AND l.country_id = c.country_id;

실행시켜보자

이제 모든 준비가 끝났다. 아래는 매우 짧고 쉬운 SQL 이다. SQL을 실행시키고 연이어DBMS_XPLAN.display_cursor 를 실행한 후의 결과 중에서 필요한 부분만 발췌 하였다.

SELECT /*+ gather_plan_statistics */

e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

FROM employee e, v_dept v

WHERE e.department_id = v.department_id

AND v.department_name = 'Shipping'

AND v.postal_code = '99236'

AND v.region_id = 2

AND e.job_id = 'ST_CLERK';

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 1 | NESTED LOOPS | | 20 |00:00:00.01 | 11 |

| 2 | NESTED LOOPS | | 45 |00:00:00.01 | 8 |

| 3 | NESTED LOOPS | | 1 |00:00:00.01 | 6 |

| 4 | NESTED LOOPS | | 1 |00:00:00.01 | 5 |

| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 |00:00:00.01 | 3 |

|* 6 | INDEX RANGE SCAN | DEPT_NAME_IDX | 1 |00:00:00.01 | 2 |

|* 7 | TABLE ACCESS BY INDEX ROWID| LOCATION | 1 |00:00:00.01 | 2 |

|* 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 |00:00:00.01 | 1 |

|* 9 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 1 |00:00:00.01 | 1 |

|* 10 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 45 |00:00:00.01 | 2 |

|* 11 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 |00:00:00.01 | 3 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

6 - access("D"."DEPARTMENT_NAME"='Shipping')

7 - filter("L"."POSTAL_CODE"='99236')

8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

9 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")

filter("C"."REGION_ID"=2)

10 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

11 - filter("E"."JOB_ID"='ST_CLERK')

조인순서를 바꿀 수 있겠는가

위의 실행계획에서 조인순서는 V_dept --> Employee 이다. 만약 이 상태에서 여러분이 조인의 순서를 Employee --> V_dept 로 바꿀 수 있겠는가? 아마 아래처럼 힌트를 사용할 것이다.

SELECT /*+ gather_plan_statistics LEADING(E V) */

e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

..이후생략

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 1 | NESTED LOOPS | | 45 |00:00:00.01 | 11 |

| 2 | NESTED LOOPS | | 45 |00:00:00.01 | 8 |

| 3 | NESTED LOOPS | | 1 |00:00:00.01 | 6 |

| 4 | NESTED LOOPS | | 1 |00:00:00.01 | 5 |

| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 |00:00:00.01 | 3 |

|* 6 | INDEX RANGE SCAN | DEPT_NAME_IDX | 1 |00:00:00.01 | 2 |

|* 7 | TABLE ACCESS BY INDEX ROWID| LOCATION | 1 |00:00:00.01 | 2 |

|* 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 |00:00:00.01 | 1 |

|* 9 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 1 |00:00:00.01 | 1 |

|* 10 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 45 |00:00:00.01 | 2 |

| 11 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 45 |00:00:00.01 | 3 |

---------------------------------------------------------------------------------------------

힌트가 무시 되었다 원인은?

조인의 순서가 전혀 변하지 않았다. 이상하지 않은가? 간단하게 생각되는 SQL의 조인순서도 변경할 수 없다. 이유는 Query Transformation 때문이다. Outline 정보를 보면 실마리를 찾을 수 있다.

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

…중간생략

MERGE(@"SEL$2")

…중간생략

END_OUTLINE_DATA

*/

원인은 Logical Optimizer 에 의한 Query Transformation 이다

뷰 V_dept 에 View Merging 이 발생한 것이다. View Merging은 Query Transformation 의 한 종류이며 뷰를 해체하여 정상적인 조인으로 바꾸는 작업이다. Query Transformation이 발생하면 많은 경우에 쿼리블럭명이 바뀌어 버린다. 따라서 바뀐 쿼리블럭명을 지정하여 힌트를 사용하거나 Query Transformation 이 발생하지 않게 하면 힌트가 제대로 적용된다.

SELECT /*+ gather_plan_statistics NO_MERGE(V) LEADING(E V) */

e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

FROM employee e, v_dept v

WHERE e.department_id = v.department_id

AND v.department_name = 'Shipping'

AND v.postal_code = '99236'

AND v.region_id = 2

AND e.job_id = 'ST_CLERK';

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

|* 1 | HASH JOIN | | 20 |00:00:00.02 | 8 |

| 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 |00:00:00.02 | 2 |

|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 20 |00:00:00.02 | 1 |

| 4 | VIEW | V_DEPT | 1 |00:00:00.01 | 6 |

| 5 | NESTED LOOPS | | 1 |00:00:00.01 | 6 |

| 6 | NESTED LOOPS | | 1 |00:00:00.01 | 5 |

| 7 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 |00:00:00.01 | 3 |

|* 8 | INDEX RANGE SCAN | DEPT_NAME_IDX | 1 |00:00:00.01 | 2 |

|* 9 | TABLE ACCESS BY INDEX ROWID| LOCATION | 1 |00:00:00.01 | 2 |

|* 10 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 |00:00:00.01 | 1 |

|* 11 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 1 |00:00:00.01 | 1 |

-------------------------------------------------------------------------------------------

Query Transformation 이 발생하지 않으면 조인순서 변경이 가능해

No_merge 힌트를 사용하여 Query Transformation 이 발생하지 않게 하였더니 조인 순서가 Employee --> V_dept 로 바뀌었다. 이제 알겠는가? Query Transformation과 Logical Optimizer를 모른다면 힌트도 먹통이 된다. 이래서는 제대로 된 튜닝을 할 수 없다.

뷰 내부의 테이블에 대한 조인순서의 변경은 가능한가
한 단계 더 나아가 보자. No_merge 힌트를 사용한 상태에서 뷰 내부의 테이블들에 대해서 조인순서를 바꾸고 싶다. 즉 조인순서를 Employee --> 뷰(Country --> Location --> Department) 로 바꾸어야 한다. 이때 여러분은 어떻게 할 것인가? 아래처럼 Global Hint 를 사용하면 된다.

SELECT /*+ gather_plan_statistics NO_MERGE(V) LEADING(E V) LEADING(V.C V.L V.D) */

e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

FROM employee e, v_dept v

WHERE e.department_id = v.department_id

AND v.department_name = 'Shipping'

AND v.postal_code = '99236'

AND v.region_id = 2

AND e.job_id = 'ST_CLERK';

Leading 힌트를 두 번 사용하였다. 그 이유는 위의 SQL은 쿼리블럭 2개로 구성되어 있기 때문이다. 즉 전체 SQL 에 대한 Leading 힌트가 필요하며 V_dept 에 대한 Leading 힌트가 각각 필요하다. V_dept 에 대한 Leading 힌트를 사용할 때 Dot 표기법을 사용해야 한다. 즉 뷰 내부에 존재하는 테이블들의 Alias 를 사용해야 한다. 이 방법은 Global Hint 사용 방법 중에 Dot 표기법을 사용한 것이다.

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

|* 1 | HASH JOIN | | 20 |00:00:00.01 | 9 |

| 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 |00:00:00.01 | 2 |

|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 20 |00:00:00.01 | 1 |

| 4 | VIEW | V_DEPT | 1 |00:00:00.01 | 7 |

| 5 | NESTED LOOPS | | 1 |00:00:00.01 | 7 |

| 6 | NESTED LOOPS | | 1 |00:00:00.01 | 6 |

|* 7 | HASH JOIN | | 1 |00:00:00.01 | 4 |

|* 8 | INDEX RANGE SCAN | COUN_REGION_IDX | 5 |00:00:00.01 | 1 |

| 9 | TABLE ACCESS BY INDEX ROWID| LOCATION | 1 |00:00:00.01 | 3 |

|* 10 | INDEX RANGE SCAN | LOC_POSTAL_IDX | 1 |00:00:00.01 | 2 |

|* 11 | INDEX RANGE SCAN | DEPT_NAME_IDX | 1 |00:00:00.01 | 2 |

|* 12 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 |00:00:00.01 | 1 |

--------------------------------------------------------------------------------------------

Global Hint 는 매우 유용해

성공적으로 조인순서가 Employee --> 뷰(Country --> Location --> Department)로 바뀌었다. Global Hint 를 사용하자 힌트가 제대로 적용된다. 이 방법은 특히 뷰나 인라인뷰를 Control 할 때 유용하므로 반드시 익혀두기 바란다.

Query Transformation 이 발생했을 경우는 힌트를 어떻게 적용할 수 있나

이제 원래 목적인 Query Transformation 이 발생했을 경우에 조인순서를 바꾸는 방법에 대해 논의 해보자. 위에서 배운 Global Hint 를 여기에 적용할 것이다.

SELECT /*+ gather_plan_statistics LEADING(E V.C V.L V.D) */

e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

FROM employee e, v_dept v

WHERE e.department_id = v.department_id

AND v.department_name = 'Shipping'

AND v.postal_code = '99236'

AND v.region_id = 2

AND e.job_id = 'ST_CLERK';

위의 SQL은 No_merge 힌트가 사라졌으므로 Query Transformation 이 발생된다. 그래서 위에서 배운 대로 Dot 표기법을 활용하여 Leading 힌트를 사용 하였다. 힌트가 적용될까?

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 1 | NESTED LOOPS | | 20 |00:00:00.01 | 11 |

| 2 | NESTED LOOPS | | 45 |00:00:00.01 | 8 |

| 3 | NESTED LOOPS | | 1 |00:00:00.01 | 6 |

| 4 | NESTED LOOPS | | 1 |00:00:00.01 | 5 |

| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 |00:00:00.01 | 3 |

|* 6 | INDEX RANGE SCAN | DEPT_NAME_IDX | 1 |00:00:00.01 | 2 |

|* 7 | TABLE ACCESS BY INDEX ROWID| LOCATION | 1 |00:00:00.01 | 2 |

|* 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 |00:00:00.01 | 1 |

|* 9 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 1 |00:00:00.01 | 1 |

|* 10 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 45 |00:00:00.01 | 2 |

|* 11 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 |00:00:00.01 | 3 |

---------------------------------------------------------------------------------------------

실패사례: 힌트가 무시 되었다

힌트가 전혀 먹혀 들지 않는다. 그 이유는 View Merging이 발생하여 새로운 쿼리블럭이 생성되었기 때문이다. 아래의 Query Block Name 정보를 보면 쿼리블럭명과 각 테이블의 Alias 를 조회할 수 있다. / 를 기준으로 왼쪽이 쿼리블럭명이고 오른쪽이 각 테이블의 Alias 이다. 제일 왼쪽의 숫자는 실행계획상의 Id 와 일치한다.

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$F5BB74E1

5 - SEL$F5BB74E1 / D@SEL$2

6 - SEL$F5BB74E1 / D@SEL$2

7 - SEL$F5BB74E1 / L@SEL$2

8 - SEL$F5BB74E1 / L@SEL$2

9 - SEL$F5BB74E1 / C@SEL$2

10 - SEL$F5BB74E1 / E@SEL$1

11 - SEL$F5BB74E1 / E@SEL$1

힌트에 쿼리블럭명과 Object의 Alias를 사용해야 가능해

쿼리블럭명은 SEL$F5BB74E1 이며 Object의 Alias 들은 D@SEL$2, L@SEL$2, C@SEL$2, E@SEL$1 임을 알 수 있다. 따라서 이 정보들을 이용하여 아래처럼 힌트를 바꾸어 보자.

SELECT /*+ gather_plan_statistics LEADING(@SEL$F5BB74E1 E@SEL$1 C@SEL$2 L@SEL$2 D@SEL$2 ) */

e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

FROM employee e, v_dept v

WHERE e.department_id = v.department_id

AND v.department_name = 'Shipping'

AND v.postal_code = '99236'

AND v.region_id = 2

AND e.job_id = 'ST_CLERK';

위의 힌트처럼 쿼리블럭명을 처음에 지정하고 그 뒤에는 조인될 순서대로 Object Alias 를 배치하기만 하면 된다.

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

|* 1 | HASH JOIN | | 20 |00:00:00.01 | 8 |

|* 2 | HASH JOIN | | 20 |00:00:00.01 | 5 |

| 3 | MERGE JOIN CARTESIAN | | 100 |00:00:00.01 | 3 |

| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 20 |00:00:00.01 | 2 |

|* 5 | INDEX RANGE SCAN | EMP_JOB_IX | 20 |00:00:00.01 | 1 |

| 6 | BUFFER SORT | | 100 |00:00:00.01 | 1 |

|* 7 | INDEX RANGE SCAN | COUN_REGION_IDX | 5 |00:00:00.01 | 1 |

| 8 | TABLE ACCESS BY INDEX ROWID | LOCATION | 1 |00:00:00.01 | 2 |

|* 9 | INDEX RANGE SCAN | LOC_POSTAL_IDX | 1 |00:00:00.01 | 1 |

| 10 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 |00:00:00.01 | 3 |

|* 11 | INDEX RANGE SCAN | DEPT_NAME_IDX | 1 |00:00:00.01 | 2 |

------------------------------------------------------------------------------------------

쿼리블럭 표기법은 Leading Hint 뿐만 아니라 모든 힌트에 적용 가능해

조인 순서가 Employee --> Country --> Location --> Department 로 바뀌었다. 이 방법은 Global Hint 사용 방법 중에 쿼리블럭 표기법을 사용한 것이다. 이 방법은 특히 View Merging 과 같은 Query Transformation 이 발생하여 쿼리블럭이 새로 생성된 경우 매우 유용하다. 이 방법으로 모든 힌트를 사용할 수 있다. 아래의 Outline Data 는 이런 점을 잘 설명 해준다.

Outline Data

-------------

/*+

…중간생략

MERGE(@"SEL$2")

…중간생략

INDEX_RS_ASC(@"SEL$F5BB74E1" "E"@"SEL$1" ("EMPLOYEE"."JOB_ID"))

INDEX(@"SEL$F5BB74E1" "C"@"SEL$2" ("COUNTRY"."REGION_ID"))

…중간생략

USE_HASH(@"SEL$F5BB74E1" "D"@"SEL$2")

END_OUTLINE_DATA

*/

PS
위의 내용 또한 이번에 출간될 책의 일부분이다. 블로그에 책의 내용이 많이 올라가서 걱정이다.^^

'Oracle > Optimizer' 카테고리의 다른 글

Distinct Elimination : 불필요한 Distinct를 제거하라 (6)	2010.01.25
Index Unque Scan은 SQL을 변경시킨다 (6)	2010.01.20
Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다 (14)	2009.12.07
MERGE 문과 IN 조건이 만난다면 (8)	2009.11.26
Transformer - SQL 튜닝의 새로운 패러다임 (11)	2009.10.15

Posted by extremedb

,

Partition Access Pattern

Oracle/Data Access Pattern 2009. 12. 24. 17:25

지난 2009년 10월달에 Oracle Data Access Pattern을 정복하라 라는 글을 통하여 데이터의 접근방법에 대하여 알아보았다. 오늘은 파티션 데이터의 접근방법에 대하여 알아보자. 필자가 이글을 올리는 이유는 실행계획에 Partition Access Pattern 이 나오지만 해석을 못하는 사람이 많이 있기 때문이다. 오늘 이글을 읽고 이해한다면 파티션에 어떻게 접근하는지 접근하는 방법은 어떤것인지 모두 알수 있다.

기본적인 Partition의 종류는 3가지이다.

1.RANGE
2.LIST
3.HASH

하지만 위의 3가지를 엑세스 패턴으로 나누고자 한다면 매우 종류가 많아진다.

Partition RANGE Access Pattern

1.PARTITION RANGE SINGLE

2.2.PARTITION RANGE ITERATOR

3.3.PARTITION RANGE INLIST

4.4.PARTITION RANGE ALL

5.5.PARTITION RANGE EMPTY

6.6.PARTITION RANGE OR

7.7.PARTITION RANGE SUBQUERY

8.8.PARTITION RANGE JOIN-FILTER

9.9.PARTITION RANGE MULTI-COLUMN

Partition LIST Access Pattern

1.
1.PARTITION LIST SINGLE

2.2.PARTITION LIST ITERATOR

3.3.PARTITION LIST INLIST

4.4.PARTITION LIST ALL

5.5.PARTITION LIST EMPTY

6.6.PARTITION LIST OR

7.7.PARTITION LIST SUBQUERY

8.8.PARTITION LIST JOIN-FILTER

Partition HASH Access Pattern

1.
1.PARTITION HASH SINGLE

2.2.PARTITION HASH ITERATOR

3.3.PARTITION HASH INLIST

4.4.PARTITION HASH ALL

5.5.PARTITION HASH SUBQUERY

6.6.PARTITION HASH JOIN-FILTER

총 23가지이다.
이모든 것을 언제 다 배운단 말인가?
이럴때 필자가 정리한 파일이 도움이 될것이다.
반드시 Partition Pruning 과 Access Pattern 을 정복하기 바란다.

invalid-file

Partition Access Pattern

PS :
첨부된 문서는 Oracle 11.1까지의 Partition Pruning 과 Access Pattern을 정리한 것이다.
배포시 출처를 밝혀주기 바란다.

참조서적:
1.Trouble Shooting Oracle Performance (Christian Antognini)
2.오라클 메뉴얼

'Oracle > Data Access Pattern' 카테고리의 다른 글

Inlist Iterator를 사용하지 말아야 할 때 (13)	2010.06.23
오렌지나 TOAD에서 Predicate Information을 참조하는 방법 (7)	2010.01.12
모든 상식을 의심하라 (16)	2009.11.05
Oracle Data Access Pattern을 정복하라 (18)	2009.10.29
Oracle11g 에서 Full Table Scan의 성능 향상 (8)	2009.07.22

Posted by extremedb

,

역정규화 무엇이 문제인가?

Modeling 2009. 12. 14. 18:00

아래의 글은 모델러에 따라 생각이 다를 수 있음을 밝혀둔다. 모델링에는 정답이 없다. 하지만 모범답안을 찾기 위해 노력해야 한다.

역정규화는 정합성과 확장성을 방해한다.
많은 종류의 모델링 책에 위와 같은 이유로 물리모델링단계에서 역정규화를 해야 한다고 되어 있다. 즉 정합성과 확장성이 나빠짐으로 논리모델링시에는 정규화를 하고 물리모델링 시에는 역정규화를 해야 한다는 것이다. 하지만 과연 그럴까? 몇몇의 경우는 물리모델링에서 역정규화를 적용하는 것이 옳을 수도 있다. 하지만 항상 그런 것은 아니다. 오늘은 역정규화에 잘못된 관행에 대하여 알아보도록 하자.

역정규화를 해야 하는 이유는 두 가지이다.

첫 번째, 성능관점이다. 대표적인 것이 항상 조인이 발생함으로 조인에 의한 성능저하를 막아보자는 것이다. 또 다른 예제는 목적성(집계) 테이블이다. Source 테이블을 직접 Group By + SUM 을 한다면 SQL의 성능이 느려지는 것은 불을 보듯 뻔한 것이다. 목적성 테이블에는 상태를 나타내는 테이블도 있다. 대표적인 것이 상품의 현재고 테이블이다.

두 번째, 개발 생산성 관점이다. 대표적인 예제는 고객테이블의 고객전화번호이다. 예를 들면 자택전화번호, 직장본사전화번호, 직장 파견지 전화번호, 핸드폰번호, 기타전화번호로 전화번호가 여러 개인경우에 고객연락처를 별도로 분리하여 정규화를 하게 된다. 이럴 때 PK 는 고객번호 + 연락처구분코드 가 되며 테이블의 확장성이 좋아진다. 하지만 모든 조회 화면에서 고객의 전화번호를 가로로 나열한다면? 성능은 물론이고 개발자의 생산성 또한 좋지 못할 것이다.

위의 두 가지 관점을 모두 가지고 있는 경우가 있다. 대표적인 것이 재고 테이블이다. 재고테이블은 성능의 관점과 개발생산성의 관점을 모두 고려한 것이다. 제조나 유통 프로젝트의 경우 재고 테이블이 없다면 개발하는데 엄청난 공수를 들여야 할 것이다. 이론적으로 상품의 현재고는 다음과 같이 구할 수 있다.

현재고 = 전월 이월수량 + 이번 달 입고수량 - 이번 달 출고수량 - 출고 되지 않은 주문 수량 + 재고로 사용할 수 있는 이번 달 반품수량 + 기타

논리모델링에서 역정규화를 해야 되는 경우
논리 모델링 단계에서 재고엔티티가 없어도 위의 식을 사용한다면 논리적으로 문제될 것이 없다. 하지만 업무의 핵심인 입고, 출고, 반품, 주문 등에 의하여 지속적으로 영향을 받는 업무적으로도 중요한 테이블이 된다. 또한 재고실사(실제로 재고가 몇 개인지 검증)를 하게 되면 재고테이블의 수량에 직접 영향을 끼친다. 이러한 여러 가지 이유로 재고 테이블은 논리모델링에서 나오는 것이 좋다. 이유는 성능이나 개발생산성 관점뿐만 아니라 업무적인 관점과 집합의 중요성을 감안 해야하며 중요한 엔티티가 없을 경우 Communication 이 어려워지기 때문이다. 심지어 개념모델링에서도 재고를 엔티티로 나타내는 모델러도 있다.

Table Generation 이후에 역정규화를 해야 하는 경우
성능상의 문제로 역정규화를 고려하는 경우이다. 필자가 역정규화를 적용한 대부분의 경우가 여기에 해당한다. 예를 들어 고객테이블의 고객명을 역정규화 해야 한다고 생각해 보자. 물리모델링단계 이후에 Table Generation 이 끝나고 데이터 이관작업이 끝나고 인덱스 디자인 작업이 끝나야 SQL 의 성능이 느린지 아닌지 알 수 있다.

나는 질문한다
나는 물리모델링단계에서 모든 역정규화를 적용했다는 사람에게 묻고 싶다. 도대체 무엇을 보고 물리모델링 단계에서 SQL이 느린지 알 수 있단 말인가? 물리 모델링 단계에서는 SQL을 알 수 없는 것은 물론이고 물리적인 테이블이나 인덱스가 없다. SQL을 실행시킬수도 없는 환경에서 모든 역정규화를 적용했단 말인가? 책에 나와 있다고 혹은 고참이 그렇게 한다고 혹은 경험이나 감으로 역정규화를 적용 했는가? 이런 일이 반복되어서는 안된다.

구체적인 예를 들어보자
성능목표를 “모든 조회화면의 90% 가 2.5초 이내에 수행되어야 한다.” 라고 정의 했다면 해당 SQL이 2.5 초가 걸리는지 안 걸리는 지는 데이터가 이행이 되고 쓸만한 인덱스가 설계된 이후에나 알수 있다.

뻔한 것은 물리모델링 시에 역정규화 할 수 있다
대표적인 것이 고객테이블의 고객전화번호이다. 예를 들면 자택전화번호, 직장 본사 전화번호, 직장 파견지 전화번호, 핸드폰번호로 전화번호가 여러 개인경우에 고객연락처를 별도로 분리하여 논리 모델링시에 정규화를 하게 된다. 이럴 때 PK 는 고객번호 + 연락처구분코드 가 되며 테이블의 확장성이 좋아진다.

하지만 물리모델링 시에 화면 설계서를 볼 수 있고 거의 모든 조회 화면에서 고객의 전화번호를 가로로 나열한다면? 이때는 물리모델링 시에 역정규화를 하는 것이 옳다. 개발생산성과 성능의 향상은 불을 보듯 뻔한 것이므로… 하지만 이 경우에도 전화번호의 종류가 계속하여 늘어난다면 역정규화를 적용하는 것은 다시 생각해보아야 한다.

일부의 집계테이블도 물리모델링 단계에서 역정규화가 가능하다
화면 정의서에서 원본 테이블을 지속적으로 Group By + Sum 하는 형태라면 물리모델링시에 집계테이블을 생성 할 수 있다. 하지만 이런 경우라 할지라도 화면에 해당하는 SQL 이 성능 목표인 2.5초가 넘는지 정확히 알 수 없다. 인덱스 나 파티션, 클러스터링 팩터에 의하여 달라진다. 또한 화면의 조회조건이 어떻게 되느냐에 따라 SQL의 성능은 달라진다. 어느 프로젝트의 경우 성능목표보다 6배 이상 빠른 경우에도 역정규화를 적용하여 데이터의 정합성에 문제가 된 적이 있다.

역정규화는 최소화 되어야 해
개발생산성과 성능이란 두 가지 관점에서 역정규화는 필요하다. 하지만 역정규화는 정합성과 확장성을 해치기 때문에 최소화 되어야 한다. 모델러의 경험이나 감으로 역정규화를 하는 경우가 많다. 그렇게 하면 필요치 않은 역정규화가 발생할 수 있다. 모델링의 각 단계(논리모델링/물리모델링/Generation 이후)에서 해야 할 것과 하지 말아야 할 것을 잘 따져서 해야 한다.

성능관점의 역정규화는 데이터 이행 이후에 고려하라
특히 성능관점에서는 Table Generation 이후 + 인덱스 디자인 이후 + 데이터 이행 이후에 목표성능을 만족하지 않는 경우에만 적용하는 것이 역정규화를 최소화 한다는 관점에서 가장 바람 직 하다. 필자가 이런 글을 쓰는 이유는 ‘역정규화는 무조건 물리모델링단계에서 하는 것’ 으로 생각하는 모델러가 대부분이기 때문이다.

결론:
바둑을 잘 두려면 정석은 모두 알아야 한다. 하지만 정석을 알고 난 이후에 프로기사가 되려면 정석을 까먹어야 한다. 틀에 박하지 않은 새로운 정석을 만들어야 하기 때문이고 실제로 프로기사들은 새로운 정석을 계속 만들어 낸다. ‘역정규화는 무조건 물리모델링단계에서 하는 것’ 이란 말이 아주 잘못된 말은 아니다. 하지만 개념을 알았다면 까먹어라. 더 좋은 새로운 정석을 만들기 위해서…

PS
1. 현실적으로 모델링의 기간이 매우 짧으므로 물리모델링단계에서 모든 것을 해야 하는 경우도 있다. 하지만 이런 경우를 원칙으로 가이드를 하거나 책을 쓰는 것은 옳지 않다.
2. 프로젝트 에서 물리모델링 기간을 시스템 Open 직전까지 가져가는 경우가 있다. 이런 경우는 물리모델링단계에서 역정규화를 하는 것이 옳다. 하지만 이때에도 뻔한 경우가 아니라면 데이터 이행 이후에 쓸만한 인덱스가 있어도 성능이 나오지 않는 경우에 역정규화를 고려하는 것이 가장 정확하다.

'Modeling' 카테고리의 다른 글

3부 - 변경이력 테이블에 종료일자가 필요한가? (8)	2010.12.03
2부 - 변경이력 테이블에 종료일자가 필요한가? (10)	2010.11.24
변경이력 테이블에 종료일자가 필요한가? (30)	2010.11.17
Varchar2(8) VS Date 어느 것이 우월한가? (18)	2009.10.21
모델링에 관한 즐거운 토론 (10)	2008.10.19

Posted by extremedb

,

Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다

Oracle/Optimizer 2009. 12. 7. 00:01

이번 내용은 난위도가 있으므로 'Interleaving은 선수조건이 필요하다' 단락을 읽고 이해가 가는 사람만 보기 바란다. 필자의 글을 꾸준히 구독한 독자라면 어렵지 않게 볼수 있을 것이다.

전략적 결혼
사극을 보면 권력가들이 자신의 세력를 확장하기 위해 자신의 딸을 왕에게 추천하여 결혼시키는 것을 심심치 않게 볼수 있다. 자신의 목표를 이루기 위한 전단계의 포석이라고 할수 있다. 딸이 왕자를 순산하고 그 왕자가 차세대 왕이 된다면 자신은 최고의 권력을 가지는것과 마찬가지 이다. CBQT가 수행될 때에도 이러한 전략적 결혼과 같은것이 존재한다는 사실을 알고 있는가? CBQT를 최적화 하기 위해서 Transformation 간의 관계는 필수적이다. 오라클에서는 Interleaving이라는 기법을 사용하고 있다.

Interleaving은 선수조건이 필요하다
하나의 Transformation(T2) 을 수행하기 위해서 다른 Transformation(T1)이 반드시 먼저 실행되어야 하는 경우가 있다. 이럴 때 Interleaving 기법을 이용하여 T1이 수행되면 마치 기다렸다는 듯이 T2가 실행된다. 대표적인 경우가 CSU(Complex Subquery Unnesting)가 실행되면 연이어 CVM(Complex View Merging)혹은 JPPD(Join Predicate Push Down)가 실행되는 경우이다.

서브쿼리가 Unnesting 되면 초기상태는 인라인뷰로 생성된다. 인라인뷰를 제거하고 정상적인 조인으로 바꾸는 기능이 CVM 이라는 것은 이미 알것이다. 즉 CVM을 시작하기 위해서는 CSU라는 전단계의 포석이 반드시 이루어 져야만 한다. 이 규칙은 JPPD에도 똑같이 적용된다.

숫자표기법의 이해
Interleaving 에 대한 10053 Trace 내용을 분석하려면 숫자 표기법을 알아야 한다. 예를 들어 Complex Subquery가 하나 있는 SQL은 아래와 같이 0 과 1 로 Unnesting 상태를 나타낼수 있다.

CSU(1) : 서브쿼리가 Unnesting 됨
CSU(0) : 서브쿼리가 Unnesting 되지 않음.

만약 서브쿼리가 2개(SUBQ2, SUBQ1)라면 아래처럼 표현된다.
CSU(1,1) : 모든 서브쿼리가 Unnesting 됨
CSU(1,0) : SUBQ2만 Unnesting 됨
CSU(0,1) : SUBQ1만 Unnesting 됨
CSU(0,0) : 모든 서브쿼리가 Unnesting 되지 않음.

이러한 표기법은 CSU 뿐만 아니라 CVM, JPPD 등에도 똑같이 적용되며 10053 Trace에서 자주 나타나므로 반드시 알아두어야 한다.

Interleaving의 용도
Interleaving은 주로 CSU 수행시의 비용계산 오류를 줄이는 용도로 사용한다. 예를 들어 CSU(0)이 최저 Cost로 선택되었다면 JPPD의 입장에서는 전혀 다른 결과를 가져올 수 있다. CSU(0)이 아닌 CSU(1) 상태에서 JPPD을 적용하는 것이 최저 Cost가 될 수 있다. 따라서 Interleaving은 CBQT간에 최저 Cost를 구하기 위해 대화를 하는 기능이라고 할 수 있다. Interleaving 기능이 존재함으로써 CSU + CVM(혹은 JPPD)을 모두 고려한 최적의 Cost를 구 수 있다. 이제 아래의 SQL을 보자.

SELECT /*+ QB_NAME(MAIN_VIEW) */

e1.employee_id, e1.manager_id, e1.salary

FROM employee e1

WHERE e1.department_id = 10

and (e1.manager_id, e1.salary) in (select /*+ QB_NAME(SUBQ) */

e2.manager_id, max(e2.salary)

from employee e2

group by e2.manager_id )

and rownum = 1

위의 SQL에 해당하는 10053 Trace의 내용을 미리 예상해보자. 먼저 서브쿼리에 Group By를 사용하였으므로 CSU가 발생할 것이다. 연이어 CSU의 과정 중에 Interleaving 이 발생하여 CVM및 JPPD의 Cost가 같이 고려될 것이다. 하지만 위의 SQL은 조건절에 Rownum을 사용하였으므로 CVM이 발생할 수 없다. 따라서 JPPD만 고려될 것이다. 이제 우리가 예상한 내용이 맞는지 확인 해보자. 아래의 Trace 내용은 필요한 부분만 발췌하여 요약한 것이다.

*****************************

Cost-Based Subquery Unnesting
*****************************
SU: Using search type: exhaustive

SU: Starting iteration 1, state space = (2) : (1)

Cost: 6.0035 Degree: 1 Card: 1.0000 Bytes: 41

SU: Considering interleaved complex view merging

CVM: CVM bypassed: Outer QBc referencing ROWNUM.

Cost: 5.0035 Degree: 1 Card: 1.0000 Bytes: 41

SU: Interleaved cost better than best so far.
SU: Finished interleaved complex view merging

SU: Considering interleaved join pred push down

JPPD: Using search type: linear

JPPD: Considering join predicate push-down

JPPD: Starting iteration 1, state space = (2) : (0)

Cost: 6.0035 Degree: 1 Card: 1.0000 Bytes: 41

JPPD: Starting iteration 2, state space = (2) : (1)

Cost: 4.0010 Degree: 1 Card: 1.0000 Bytes: 30

JPPD: Selected interleaved query.

SU: Finished interleaved join pred push down

SU: Updated best state, Cost = 4.00

SU: Starting iteration 2, state space = (2) : (0)

Cost: 6.0037 Degree: 1 Card: 1.0000 Bytes: 15

SU: Not update best state, Cost = 6.00

SU: Will unnest subquery SUBQ (#2)

SU: Reconstructing original query from best state.

우리의 예상대로 먼저 CSU가 발생되었다. 연이어 Interleaving 기능에 의해 CVM이 고려되고 있지만 Rownum 제약사항 때문에 CVM이 발생되지 못한다.(CVM bypassed 부분 참조) 하지만 불완전 Costing(CVM을 제외한 최적화)은 계속 진행된다.

CVM과 JPPD 는 동시에 고려된다
CVM 과정이 끝나면 JPPD의 Costing 과정이 진행된다. Interleaving이 CVM뿐만 아니라 JPPD도 동시에 고려하고 있음을 알 수 있다. JPPD를 적용한 Iteration(JPPD: Starting iteration 2, state space = (2) : (1) 부분 참조) 의 Cost가 4.0010으로 최적임을 알 수 있다. 이로서 모든 Interleaving이 완료되었다.( SU: Finished interleaved join pred push down 부분 참조) 마지막으로 CSU를 수행하지 않는 Iteration의 Cost를 구하고 모든 CSU과정을 마치게 된다.

CSU의 최저 Cost는 CSU + CVM + JPPD를 모두 고려한것
한가지 주의해야 할 사항은 4.0010은 JPPD의 Cost가 되는 것이 아니라 CSU의 Cost 이다. SU: Updated best state, Cost = 4.00 부분을 보면 이러한 사실을 알 수 있다. 즉 CSU의 Cost는 단순히 CSU 자체의 Cost만 고려하는 것이 아니라 CSU + CVM + JPPD를 모두 고려한 후 최적의 Cost를 갖는 경우를 선택하는 것을 알 수 있다. Interleaving 기능으로 인하여 더욱 정확한 Cost를 구하는 것이 가능한 것이다.

PS
1)위의 내용은 내년초 출간될 책의 일부분을 발췌한 것이다.
2)아이러니 하게도 맨처음에 설명한 전략적 결혼과 같은 비근대적인 방법을 21세기의 우리사회에서도 가끔 볼수 있다.

'Oracle > Optimizer' 카테고리의 다른 글

Index Unque Scan은 SQL을 변경시킨다 (6)	2010.01.20
내가 사용한 Hint 가 무시되는 이유 (10)	2010.01.04
MERGE 문과 IN 조건이 만난다면 (8)	2009.11.26
Transformer - SQL 튜닝의 새로운 패러다임 (11)	2009.10.15
Temp Table Transformation (5)	2009.09.28

Posted by extremedb

,

MERGE 문과 IN 조건이 만난다면

Oracle/Optimizer 2009. 11. 26. 14:22

질문을 받다
독자로 부터 다음과 같은 질문을 받았다. "MERGE 문에 IN 조건을 사용한다면 아래의 Plan처럼 심각한 성능저하가 발생 하였다. 왜그런가?"

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 1 | MERGE | T2 | 1 | | 2 |00:25:50.73 | 50M| 821 |

| 2 | VIEW | | 1 | | 100K|00:25:52.38 | 50M| 795 |

| 3 | NESTED LOOPS OUTER | | 1 | 100K| 100K|00:25:52.28 | 50M| 795 |

|* 4 | TABLE ACCESS FULL | T1 | 1 | 100K| 100K|00:00:00.60 | 316 | 629 |

| 5 | VIEW | | 100K| 1 | 100K|00:25:34.26 | 50M| 166 |

|* 6 | FILTER | | 100K| | 100K|00:25:33.92 | 50M| 166 |

|* 7 | TABLE ACCESS FULL| T2 | 100K| 1 | 100K|00:25:33.68 | 50M| 166 |

--------------------------------------------------------------------------------------------------

T1의 조건을 만족하는 건수만큼 T2가 FULL SCAN을 반복하고 있다. 이것은 재앙이나 다름없다. 어떤 원리로 성능저하가 발생하는지 MERGE문을 실행할 때 내부적으로 발생하는 일들의 특징과 순서를 알아보자.

무조건 아우터 조인이 발생해
MERGE문을 실행하면 Target 쪽 테이블에는 무조건 아우터 조인으로 바뀐다. 왜냐하면 Match 되지 않는 경우(조인에 실패한 경우)에도 INSERT 해야 하기 때문이다. 그리고 아우터 조인은 다시 LATERAL VIEW로 바뀐다. 왜냐하면 View Merging이 실패할 경우 FPD(Filter Push Down)이나 JPPD(JOIN PREDICATE PUSH DOWN)이 적용 되어야 하기 때문이다. LATERAL VIEW의 개념은 아래의 POST를 참조하라.

http://scidb.tistory.com/entry/Outer-Join-의-재조명

쿼리변환 순서가 중요하다
아래는 MERGE문 실행시 쿼리변환이 발생하는 순서 이다.

1.먼저 Transformer(Logical Optimizer)는 IN 조건을 OR 로 바꾼다.

2.TRANSFORMER(Logical Optimizer)는 아우터 조인되는 쪽을 LATERAL VIEW로 바꾼다.

3.LATERAL VIEW가 해체(View Merging이라 불림) 되어 평범한 아우터 조인으로 바뀐다. 이때 View Merging에 실패하면 심각한 성능저하가 발생할 수 있다. 위의 Plan을 보면 T2 쪽의 View 가 해체되지 못했다. 이것이 실마리가 될 것이다.

아래의 스크립트를 실행하여 실제로 이런 일들이 발생하는지 Test 환경을 만들어 보자.

create table t1(c1 varchar2(10), c2 int, c3 int, c4 int);

create table t2(c1 varchar2(10), c2 int, c3 int, c4 int);

insert into t1

select decode(mod(level,2),0,'A','B'), level, level, level

from dual connect by level <= 100000

;

insert into t2

select decode(mod(level,2),0,'A','B'), level, level, level

from dual connect by level <= 100000

;

analyze table t1 compute statistics;

analyze table t2 compute statistics;

Merge 문을 사용해보자

-- case 1
MERGE /*+ gather_plan_statistics */ INTO t2

USING (SELECT *

FROM t1

WHERE c1 IN ('A', 'B')) x

ON ( x.c1 = t2.c1

AND x.c2 = t2.c2

AND x.c3 = t2.c3

AND t2.c1 = 'A')

WHEN MATCHED THEN

UPDATE SET t2.c4 = x.c4

WHEN NOT MATCHED THEN

INSERT (t2.c1, t2.c2, t2.c3, t2.c4)

VALUES (x.c1, x.c2, x.c3, x.c4) ;

select *
from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 1 | MERGE | T2 | 1 | 2 |00:00:12.52 | 105K| 655 | |

| 2 | VIEW | | 1 | 100K|00:00:00.65 | 632 | 626 | |

|* 3 | HASH JOIN RIGHT OUTER| | 1 | 100K|00:00:00.65 | 632 | 626 | 4686K (0)|

|* 4 | TABLE ACCESS FULL | T2 | 1 | 50000 |00:00:00.02 | 316 | 313 | |

|* 5 | TABLE ACCESS FULL | T1 | 1 | 100K|00:00:00.01 | 316 | 313 | |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")

4 - filter("T2"."C1"='A')

5 - filter(("C1"='A' OR "C1"='B'))

아주 정상적인 PLAN 이다.

Merge 문에 IN 조건을 사용해보자

-- case 2
MERGE /*+ gather_plan_statistics */ INTO t2

USING (SELECT *

FROM t1

WHERE c1 IN ('A', 'B')) x

ON ( x.c1 = t2.c1

AND x.c2 = t2.c2

AND x.c3 = t2.c3

AND t2.c1 IN ('A', 'B'))

WHEN MATCHED THEN

UPDATE SET t2.c4 = x.c4

WHEN NOT MATCHED THEN

INSERT (t2.c1, t2.c2, t2.c3, t2.c4)

VALUES (x.c1, x.c2, x.c3, x.c4) ;

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 1 | MERGE | T2 | 1 | | 2 |00:25:50.73 | 50M| 821 |

| 2 | VIEW | | 1 | | 100K|00:25:52.38 | 50M| 795 |

| 3 | NESTED LOOPS OUTER | | 1 | 100K| 100K|00:25:52.28 | 50M| 795 |

|* 4 | TABLE ACCESS FULL | T1 | 1 | 100K| 100K|00:00:00.60 | 316 | 629 |

| 5 | VIEW | | 100K| 1 | 100K|00:25:34.26 | 50M| 166 |

|* 6 | FILTER | | 100K| | 100K|00:25:33.92 | 50M| 166 |

|* 7 | TABLE ACCESS FULL| T2 | 100K| 1 | 100K|00:25:33.68 | 50M| 166 |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - filter(("C1"='A' OR "C1"='B'))

6 - filter(("T1"."C1"='A' OR "T1"."C1"='B'))

7 - filter(("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3" AND

INTERNAL_FUNCTION("T2"."C1")))

10만번 반복된다
Starts 항목에 주목하라 FTS(Full Table Scan)을 10만번 반복 실행하였다. CASE2 에서 t2.c1 IN ('A', 'B') 조건을 사용하였더니 최악의 PLAN이 만들어 졌으며 Buffers 가 50M 에 육박하고 시간상으로도 25분 이상 걸렸다. 왜 그럴까? 아래의 SQL처럼 Case1과 Case2 실행시에 Logical Optimizer에 의하여 변경된 SQL을 보면 이유를 알수 있다.

SELECT /*+ CASE1 NO_MERGE 상태 */

lv.RID,

lv.C1, lv.C2, lv.C3, lv.C4,

X.C1, X.C2, X.C3, X.C4

FROM (SELECT T1.C1 C1,T1.C2 C2,T1.C3 C3,T1.C4 C4

FROM T1

WHERE T1.C1='A' OR T1.C1='B'

) X,

LATERAL( SELECT T2.C1, T2.C2, T2.C3, T2.C4, T2.ROWID AS RID

FROM T2

WHERE X.C1=T2.C1

AND X.C2=T2.C2

AND X.C3=T2.C3

AND T2.C1='A' )(+) lv

정상적으로 뷰가 해체되다
Case1의 LATERAL VIEW 내부의 T2.C1='A' 조건은 아우터 조인으로 바꿀 수 있으므로 View Merging이 발생하여 인라인뷰 X와 LATERAL VIEW lv가 아래처럼 평범한 아우터 조인으로 바뀐다.

SELECT /*+ CASE1 MERGE 상태 */

T2.ROWID RID,

T2.C1 ,T2.C2 C2,T2.C3 C3,T2.C4 C4,

T1.C1 ,T1.C2 C2,T1.C3 C3,T1.C4 C4

FROM T1, T2

WHERE T1.C3=T2.C3(+)

AND T1.C2=T2.C2(+)

AND T1.C1=T2.C1(+)

AND T2.C1(+)='A'

AND (T1.C1='A' OR T1.C1='B')

이때 옵티마이져가 내부적으로 MERGE 힌트를 사용한다. 10053 Trace에도 다음처럼 쿼리블럭 SEL$2 와 SEL$3이 SEL$1에 MERGE 되었다는 정보가 포함되어 있다.

Registered qb: SEL$5428C7F1 0x9f7b318 (VIEW MERGE SEL$1; SEL$2 SEL$3)

Out Line 정보에도 이런 사항이 잘 나타난다.

Outline Data

-------------

/*+

…생략
MERGE(@"SEL$2") --> 쿼리블럭 2 와

MERGE(@"SEL$3") --> 쿼리블럭 3 이 MERGE 되어 LATERAL VIEW 가 없어짐
…생략

*/

이제 case 2를 분석 해보자.

SELECT /*+ CASE2 NO_MERGE 상태 */

lv.RID,

lv.C1, lv.C2, lv.C3, lv.C4,

X.C1, X.C2, X.C3, X.C4

FROM (SELECT T1.C1 C1,T1.C2 C2,T1.C3 C3,T1.C4 C4

FROM T1

WHERE T1.C1='A' OR T1.C1='B') X,

LATERAL( SELECT T2.C1, T2.C2, T2.C3, T2.C4, T2.ROWID AS RID

FROM T2

WHERE X.C1=T2.C1

AND X.C2=T2.C2

AND X.C3=T2.C3

AND (T2.C1='A' OR T2.C1='B') ) (+) lv

제약조건 때문에...
위의 SQL 은 LATERAL VIEW를 적용한 모습이다. 그런데 (T2.C1='A' OR T2.C1='B') 조건 때문에 아우터 조인으로 바꾸질 못한다. 이것은 오라클 제약사항 이다. 제약조건이 있을 경우는 View Merging이 실패한다. 아래처럼 말이다.

SELECT /*+ CASE2 MERGE 상태 */

lv.RID,

lv.C1, lv.C2, lv.C3, lv.C4,

X.C1, X.C2, X.C3, X.C4

FROM T1,

LATERAL( SELECT T2.C1, T2.C2, T2.C3, T2.C4, T2.ROWID AS RID

FROM T2

WHERE X.C1=T2.C1

AND X.C2=T2.C2

AND X.C3=T2.C3

AND (T2.C1='A' OR T2.C1='B') ) (+) lv

WHERE T1.C1='A' OR T1.C1='B'

IN 이 발목을 잡다
인라인뷰 X 만 View Merging이 발생하였다. 결국 IN 혹은 OR 조건이 View Merging이 되지 못하도록 발목을 잡은 셈이다. 그리하여 Lateral View 가 살아남게 되었다. Lateral View는 스칼라 서브쿼리처럼 동작하게 된다. 다시 말하면 LATERAL VIEW 는 Hash 조인으로 실행되지 못한다. 문제의 Plan에서 Nested Loop 조인이 발생한 이유도 여기 있다.

그렇다면 이 문제를 어떻게 해결할 수 있을까?

문제 해결방법 3가지
1) 만약 인덱스를 만들 수 있다면 문제가 해결된다. 아래는 T2 에 (C1, C2, C3) 인덱스를 만든후 CASE 2를 다시 실행한 Plan 이다.

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 1 | MERGE | T2 | 1 | 2 |00:00:16.95 | 303K| 934 |

| 2 | VIEW | | 1 | 100K|00:00:03.92 | 200K| 932 |

| 3 | NESTED LOOPS OUTER | | 1 | 100K|00:00:03.92 | 200K| 932 |

|* 4 | TABLE ACCESS FULL | T1 | 1 | 100K|00:00:00.12 | 316 | 313 |

| 5 | VIEW | | 100K| 100K|00:00:02.51 | 200K| 619 |

|* 6 | FILTER | | 100K| 100K|00:00:02.29 | 200K| 619 |

| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 100K| 100K|00:00:02.09 | 200K| 619 |

|* 8 | INDEX RANGE SCAN | T2_IDX | 100K| 100K|00:00:01.24 | 100K| 320 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - filter(("C1"='A' OR "C1"='B'))

6 - filter(("T1"."C1"='A' OR "T1"."C1"='B'))

8 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3")

filter(("T2"."C1"='A' OR "T2"."C1"='B'))

2) 인덱스를 만들 수 없는 경우라면 아래처럼 Between 을 사용하면 된다. Between 은 아우터 조인이 가능하다.

MERGE /*+ gather_plan_statistics */ INTO t2

USING (SELECT *

FROM t1

WHERE c1 IN ('A', 'B')) x

ON ( x.c1 = t2.c1

AND x.c2 = t2.c2

AND x.c3 = t2.c3

AND t2.c1 between 'A' AND 'B')

WHEN MATCHED THEN

UPDATE SET t2.c4 = x.c4

WHEN NOT MATCHED THEN

INSERT (t2.c1, t2.c2, t2.c3, t2.c4)

VALUES (x.c1, x.c2, x.c3, x.c4) ;

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 1 | MERGE | T2 | 1 | 2 |00:00:14.99 | 104K| 817 | |

| 2 | VIEW | | 1 | 100K|00:00:00.58 | 816 | 810 | |

|* 3 | HASH JOIN OUTER | | 1 | 100K|00:00:00.48 | 816 | 810 | 7600K (0)|

|* 4 | TABLE ACCESS FULL| T1 | 1 | 100K|00:00:00.01 | 316 | 313 | |

|* 5 | TABLE ACCESS FULL| T2 | 1 | 100K|00:00:00.01 | 500 | 497 | |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")

4 - filter(("C1"='A' OR "C1"='B'))

5 - filter(("T2"."C1"<='B' AND "T2"."C1">='A'))

정상적으로 Hash 조인이 발생하였다.

3) Between 을 사용할 수 없는 경우라면 아래처럼 Decode를 사용하면 된다. Decode 또한 아우터 조인이 가능하다.

MERGE /*+ gather_plan_statistics */ INTO t2

USING (SELECT *

FROM t1

WHERE c1 IN ('A', 'B')) x

ON ( x.c1 = t2.c1

AND x.c2 = t2.c2

AND x.c3 = t2.c3

AND t2.c1 = decode(t2.c1, 'A', 'A', 'B'))

WHEN MATCHED THEN

UPDATE SET t2.c4 = x.c4

WHEN NOT MATCHED THEN

INSERT (t2.c1, t2.c2, t2.c3, t2.c4)

VALUES (x.c1, x.c2, x.c3, x.c4) ;

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 1 | MERGE | T2 | 1 | 2 |00:00:15.16 | 104K| 967 | |

| 2 | VIEW | | 1 | 100K|00:00:00.72 | 816 | 810 | |

|* 3 | HASH JOIN RIGHT OUTER| | 1 | 100K|00:00:00.72 | 816 | 810 | 8568K (0)|

|* 4 | TABLE ACCESS FULL | T2 | 1 | 100K|00:00:00.02 | 500 | 497 | |

|* 5 | TABLE ACCESS FULL | T1 | 1 | 100K|00:00:00.01 | 316 | 313 | |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")

4 - filter("T2"."C1"=DECODE("T2"."C1",'A','A','B'))

5 - filter(("C1"='A' OR "C1"='B'))

정상적으로 Hash 조인이 발생하였다.

결론

Merge문 사용시 On 절에 Target 테이블의 조건으로 IN 이나 OR를 사용하면 View Merging 이 발생하지 않는다. 따라서 LATERAL VIEW가 해체되지 못하며 LATERAL VIEW의 특성상 Nested Loop 조인이 적용된다. 이때 후행 테이블은 적절한 인덱스가 없다면 Full Table Scan이 발생하여 재앙과 같은 성능저하 현상이 발생된다. 이때 BETWEEN 이나 DECODE등 상황에 맞는 해결책을 사용할 수 있다.

모르면 못한다
결국 Query Transformation의 원리와 순서 그리고 제약조건을 알게 된다면 누가 해법을 말해주지 않아도 자연스럽게 알 수 있다. IN 과 OR의 아우터 조인 제약조건은 누구나 알고 있으므로 문제가 될수 없다. 문제는 Query Transformation을 모른다면 튜닝을 못하는 시대가 이미 왔다는 사실이다. 안타깝게도 이런 원리를 설명해주는 서적은 어디에도 없다. 물론 몇가지 Query Transformation을 소개한 책은 있지만 Logical Optimizer를 주제로 하는 서적은 없다. 다시 말하면 우리는 튜닝을 하지 못할 환경에 살고 있다.

'Oracle > Optimizer' 카테고리의 다른 글

내가 사용한 Hint 가 무시되는 이유 (10)	2010.01.04
Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다 (14)	2009.12.07
Transformer - SQL 튜닝의 새로운 패러다임 (11)	2009.10.15
Temp Table Transformation (5)	2009.09.28
스칼라 서브쿼리를 서브쿼리로 변환하라 (11)	2009.09.10

Posted by extremedb

,

유명한 Function Based Index 의 버그

Oracle/TroubleShooting 2009. 11. 19. 14:53

일부 스크립트에 오타가 있어 수정했음을 알립니다.(2009.11.19)

거의 모든 시스템에 버그가 존재해
아주 유명한 FBI 인덱스 관련 버그를 소개한다. 이 버그는 Wrong result 버그로 분류되며 특징은 조회가 되지 않는다는 것이다. 최근까지 Patch 가 나오지 않아 악명이 높았다. 필자의 경험으로는 거의 모든 시스템에서 이 버그가 재연되었다.

버그 재연방법
CHAR 컬럼의 길이가 2 BYTE 이상이고 이 컬럼에 SUBSTR 함수로 FBI인덱스 생성할 때.

이제 아래의 스크립트를 사용하여 버그를 재연해보자.

환경 : Oracle 11.1.0.6

drop table fbi_test;

create table fbi_test(col1 char(14) );

insert into fbi_test values('01234567890123');
commit;

create index fbi_idx on fbi_test(substr(col1,1,10));

analyze table fbi_test compute statistics;
analyze index fbi_idx compute statistics;

이제 모든 준비가 끝났으므로 데이터를 조회해보자.

select /*+ gather_plan_statistics index(a fbi_idx ) */ *
from fbi_test a
where col1 = '01234567890123' ;

no rows selected.

한 건도 나오지 않는다. 이것은 분명 버그이다.

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| FBI_TEST |    1 |    1 |    0 |00:00:00.01 | 1 |
|* 2 | INDEX RANGE SCAN    | FBI_IDX |    1 |    1 |    0 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"='01234567890123')
   2 - access("FBI_TEST"."SYS_NC00002$"='0123456789')

위의 Plan을 보면 Virtual Column을 사용하였다. 오라클은 FBI를 만들면 Virtual Column을 생성 한다.
실행계획상에서 Virtual Column을 사용하는 경우는 위와 같이 조회 되지 않는 버그가 발생한다.
이런 상황에서 버그를 피하려면 해당 인덱스를 사용하지 않거나(Full Scan 이용) 아니면 아래와 같이 명시적으로 SQL을 수정해야 한다.

select /*+ gather_plan_statistics index(a fbi_idx ) */ *
from fbi_test a
where col1 = '01234567890123'
and substr(col1,1,10) = '0123456789' ;

COL1
--------------
01234567890123

1 row selected.

정상적으로 1건이 나왔다.

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| FBI_TEST |    1 |    1 |    1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN    | FBI_IDX |    1 |    1 |    1 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"='01234567890123')
   2 - access("FBI_TEST"."SYS_NC00002$"='0123456789')

편법은 안돼... 패치가 정답이다
하지만 이런 편법으로 문제를 해결하는 것은 임시방편 밖에 되지 못한다. 또한 모든 개발자들이 substr 함수를 추가하여 개발한다고 보장 할 수 없다. 이 편법을 사용 하더라도 조건절에 > 혹은 < 사용시에는 여전히 조회가 되지 않는다.

select /*+ gather_plan_statistics index(a fbi_idx ) */ *
from fbi_test a
where col1 > '0123456789012'
and substr(col1,1,10) > '012345678' ;

no rows selected.

한가지 다행스러운 점은 최근에 BUG Patch 2개가 새로 나왔다는 것이다. 또한 아쉬운 점은 HP 시스템용 Patch는 아직 없다는 것이다. 각자의 플랫폼에 맞는 Patch가 있는지 확인해보라.

문서번호 : 6131467
패치 ID : 6131467

'Oracle > TroubleShooting' 카테고리의 다른 글

COPY_T를 대신하여 Connect By 문을 사용하는가? (11)	2010.03.05
한방 Query를 사용하지 말아야 할 때 (17)	2010.02.19
SQL 튜닝이 불가한 경우 (12)	2009.09.16
천당과 지옥의 차이 (3)	2009.06.17
Sequence 의 오작동 (무조건 채번되는 Sequence) (0)	2009.02.19

Posted by extremedb

,

오라클에서 is_number, is_date 함수 사용하기

Oracle/PL/SQL Pattern 2009. 11. 11. 21:36

지인에게서 전화가 오다
지인 : 데이터를 체크해야 하는데 오라클에 is_number, is_date 함수가 없어서 데이터를 체크하기가 불편합니다.
데이터를 오라클에서 가져와서 자바에서 체크하고 있습니다. 그러다 보니 너무 느립니다.
필자 : 그럴 필요 없습니다.
지인 : 물론 External Function을 사용하면 자바를 사용하여 오라클에 함수를 생성할수도 있겠지요.
필자 : 그냥 PL/SQL 로 하시면 됩니다.
지인 : 네?

무서운 일이다. 전체 데이터를 Network를 타고 가져와서 자바로 체크하다니... Network I/O 가 엄청 날것이다.

오라클에서 제공하는 함수가 없다
"오라클에서 is_number, is_date 함수가 없어서 데이터를 체크하기가 불편하다" 이말은 옳다. 하지만 오라클에서 체크함수를 제공하지 않는 이유는 아마도 개발자가 너무도 쉽게 만들 수 있어서 그런 것이 아닐까?
is_number, is_date 함수를 직접 만들어 보자.

CREATE OR REPLACE FUNCTION is_number(v_str_number IN varchar2)

RETURN NUMBER

IS /* 데이터가 number 형인지 검사하는 함수임. 1 이 나오면 NUMBER 형임 */

V_NUM NUMBER;

BEGIN

V_NUM := TO_NUMBER(v_str_number);

RETURN 1;

EXCEPTION

WHEN OTHERS THEN RETURN 0 ;

END;

CREATE OR REPLACE FUNCTION is_date(v_str_date IN varchar2, V_FORMAT IN VARCHAR2 DEFAULT 'YYYYMMDD')

RETURN NUMBER

IS /* 데이터가 DATE 형인지 검사하는 함수임. 1 이 나오면 DATE 형임 */

V_DATE DATE;

BEGIN

V_DATE := TO_DATE(v_str_date, V_FORMAT);

RETURN 1;

EXCEPTION

WHEN OTHERS THEN RETURN 0 ;

END;

너무나 쉽게 생성 되었다. 그럼 이제 사용해보자.

함수사용법

select is_number('abcd'), is_number('1234'),

is_date('20090230'), is_date('20090228')

from dual ;

결과:

IS_NUMBER('ABCD') IS_NUMBER('1234') IS_DATE('20090230') IS_DATE('20090228')

----------------- ----------------- ------------------- -------------------

0 1 0 1

1 row selected.

number 형 에서 벗어나는 데이터와 date 형 에서 벗어나는 데이터를 가려 내었다. 타 DBMS 에서 사용할 수 있는 함수와 기능이 똑같다. 이렇게 해서 개발자의 문제가 일시적으로 해결되었다.

-------------------------------------------------------------------------------------------------------------------------

문제는 성능이다
다음날 다시 전화가 왔다. 일회성이 아닌 지속적으로 데이터를 체크해야 하는데 이전보다는 빨라졌지만 여전히 성능이 느리다는 것이었다. 이제부터 함수의 성능에 대해 논의 해보자. 먼저 올바른 데이터 1000만 건을 만들고 number형이 아닌 데이터와 date형이 아닌 데이터를 1건 추가해보자.

drop table test_tbl purge;

create table test_tbl nologging as

select a.*

from (select to_char(level) as varchar_num, to_char(level + sysdate, 'YYYYMMDD') as varchar_date

from dual

connect by level <= 100) a,

(select level from dual connect by level <= 100000) b ;

insert into test_tbl values('ABCD', '20090230');

commit;

이제 함수를 실행 해보자.

alter session set statistics_level = all;

alter system flush buffer_cache;

select /*+ gather_plan_statistics */ *

from test_tbl a

where is_number(varchar_num) = 0;

결과 :

VARCHAR_NUM VARCHAR_DATE

---------------- ------------

ABCD 20090230

select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 |00:00:45.47 | 23463 | 23447 |

|* 1 | TABLE ACCESS FULL| TEST_TBL | 1 |00:00:45.47 | 23463 | 23447 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("IS_NUMBER"("VARCHAR_NUM")=0)

함수를 사용하면 너무 느리다
함수를 천만번 수행하는데 무려 45초 이상 걸렸다. 너무나 느려서 사용할 수 없는 수준이다. 함수를 빠르게 실행하기 위해서 Deterministic 형 함수로 수정해보자. Deterministic 함수는 Input 에 대한 Output 의 값이 항상 같을 때만 사용해야 한다. Deterministic 함수를 사용하면 같은 값의 Input이 여러 번 들어올 경우 한번만 수행할 수 있다. 하지만 Deterministic 함수도 비효율이 있다. 이 Post 의 마지막에 Deterministic 함수 의 비효율과 관련된 Link를 표시하였으므로 반드시 읽어보기 바란다.

-- Deterministic 함수로 바꿈

CREATE OR REPLACE FUNCTION is_number(v_str_number IN varchar2)

RETURN NUMBER DETERMINISTIC IS

… 이후 생략

/

DETERMINISTIC 함수를 사용해보자

alter system flush buffer_cache;

select /*+ gather_plan_statistics */ *

from test_tbl a

where is_number(varchar_num) = 0;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 |00:00:07.50 | 23463 | 23447 |

|* 1 | TABLE ACCESS FULL| TEST_TBL | 1 |00:00:07.50 | 23463 | 23447 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("IS_NUMBER"("VARCHAR_NUM")=0)

대단한 성능향상이다. 수행시간이 45초 에서 7초로 줄어들었다. 하지만 여기서 멈출순 없다.

alter system flush buffer_cache;

select /*+ gather_plan_statistics */ *

from test_tbl a

where (select is_number(varchar_num) from dual) = 0;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 |00:00:03.00 | 23439 | 23433 |

|* 1 | FILTER | | 1 |00:00:03.00 | 23439 | 23433 |

| 2 | TABLE ACCESS FULL| TEST_TBL | 10M|00:00:00.01 | 23439 | 23433 |

| 3 | FAST DUAL | | 101 |00:00:00.01 | 0 | 0 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(=0)

함수사용시 스칼라서브쿼리를 활용하라
천만 건을 체크하는데 3초 밖에 걸리지 않았다. 함수 사용시 스칼라 서브쿼리를 사용하면 비효율 없이 함수 호출을 최소화 할 수 있다. Deterministic 함수든 아니든 상관없이 스칼라 서브쿼리의 효과는 동일하다. 그렇다면 함수 + 스칼라서브쿼리의 조합이 최선인가? 만약 일회성이 아닌 지속적으로 데이터를 체크해야 하는 경우라면 FBI(Function Based Index)를 생성해야 한다.

create index idx_is_number on test_tbl (is_number(varchar_num)) ; -- FBI 생성

alter system flush buffer_cache;

select /*+ gather_plan_statistics index_rs(a idx_is_number) */ *

from test_tbl a

where is_number(varchar_num) = 0;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 |00:00:00.03 | 5 | 4 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TBL | 1 |00:00:00.03 | 5 | 4 |

|* 2 | INDEX RANGE SCAN | IDX_IS_NUMBER | 1 |00:00:00.03 | 4 | 3 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("A"."SYS_NC00003$"=0)

FBI 가 최적이다
Block I/O 수(Buffers 항목)를 비교해보라. 함수 + 스칼라 서브쿼리를 사용하는 것과 인덱스를 사용하는 것은 성능의 비교가 되지 않는다. 지속적으로 데이터를 검증해야 하고 테이블의 건수가 많지만 데이터를 체크하여 만족하지 않는 데이터의 건수가 적은 경우는 인덱스를 사용하는 것이 최적임을 알 수 있다.

이제 내일은 is_date, is_number 함수와 관련된 문제로 필자에게 전화가 오지는 않으리라 믿는다.^^

관련 Post :
http://ukja.tistory.com/159
http://adap.tistory.com/entry/Deterministic-의-진실Multi-buffer

'Oracle > PL/SQL Pattern' 카테고리의 다른 글

PL/SQL-면접문제 (808)	2010.05.07
묵시적인 형변환을 피하라 (414)	2008.05.09
PL/SQL 에서 NUMBER 타입의 성능 테스트 (0)	2008.05.02

Posted by extremedb

,

모든 상식을 의심하라

Oracle/Data Access Pattern 2009. 11. 5. 00:01

상식이 발목을 잡다
SQL 튜닝을 해본 사람들은 "같은 테이블을 반복해서 사용하지 말라" 라는 보편화된 상식을 가지고 있다. 위와 같은 말들은 상창력(상상력 + 창의력)이 필요한 튜닝의 세계에서 오히려 그것을 발휘하지 못하게 발목을 잡을 수 있다. 상식을 비판 없이 따르는 것이 얼마나 위험한지 증명해보자.

환경 Oracle 11.1.0.6

--테스트를 위한 회원탈퇴고객 테이블 생성

CREATE TABLE SH.WITHDRAWAL_CUST AS
SELECT * FROM sh.CUSTOMERS WHERE rownum <= 5000;

--UNIQUE INDEX 생성

CREATE UNIQUE INDEX SH.PK_WITHDRAWAL_CUST ON SH.WITHDRAWAL_CUST (CUST_ID);

--SALES 테이블에 PROD_ID를 선두로 하는 결합인덱스 생성

CREATE INDEX SH.SALES_IDX_02 ON SH.SALES (PROD_ID, CUST_ID);

dbms_stats.gather_table_stats('SH','SALES',cascade=>true);

dbms_stats.gather_table_stats('SH','WITHDRAWAL_CUST',cascade=>true);

후행테이블에서 건수가 줄어드는 경우

회원탈퇴고객 테이블(WITHDRAWAL_CUST)을 생성 하였고 SALES 테이블에 결합인덱스를 생성 하였다. 이제 모든 준비가 끝났으므로 상품번호 144을 구입한 고객 중 회원탈퇴고객을 조회해 보자.

SELECT /*+ GATHER_PLAN_STATISTICS FIRST_ROWS(1) */

s.cust_id, w.cust_first_name, w.cust_last_name,

s.prod_id, s.time_id, s.channel_id, s.quantity_sold

FROM sh.sales s, sh.withdrawal_cust w

WHERE s.prod_id = 144

AND s.cust_id = w.cust_id ;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) ;

상식적인 수준에서 SQL이 작성되었다. 아래의 Plan을 보자.

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 1 | NESTED LOOPS | | 423 |00:00:00.11 | 3708 |

| 2 | NESTED LOOPS | | 423 |00:00:00.11 | 3285 |

| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 4091 |00:00:00.07 | 3209 |

|* 4 | INDEX RANGE SCAN | SALES_IDX_02 | 4091 |00:00:00.01 | 17 |

|* 5 | INDEX UNIQUE SCAN | PK_WITHDRAWAL_CUST | 423 |00:00:00.02 | 76 |

| 6 | TABLE ACCESS BY INDEX ROWID | WITHDRAWAL_CUST | 423 |00:00:00.01 | 423 |

---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("S"."PROD_ID"=144)

5 - access("S"."CUST_ID"="W"."CUST_ID")

최적의 SQL인가?

Plan을 보면 SALES 테이블(테이블 건수: 약 92만건)의 SALES_IDX_02 인덱스를 이용하여 4091건을 Select 하였고 연이어 SALES 테이블을 같은 건수만큼 Select 하였다. 그런 후에 WITHDRAWAL_CUST 테이블의 Unque 인덱스를 엑세스 하여 건수가 423 건으로 거의 10분의 1 수준으로 감소하였다. 건수가 줄어드는 이유는 전체고객에 비하여 회원탈퇴고객이 많지 않은 까닭이다. 많은 사람들이 위의 Plan은 현재 상태에서 최적이라고 생각할 것이다. 아래의 SQL은 결과는 같지만 SALES 테이블을 2번 사용하는 예제이다.

SELECT /*+ GATHER_PLAN_STATISTICS FIRST_ROWS(1) LEADING(S1 W S) */

s.cust_id, w.cust_first_name, w.cust_last_name,

s.prod_id, s.time_id, s.channel_id, s.quantity_sold

FROM sh.sales s1, sh.withdrawal_cust w, sh.sales s

WHERE s1.prod_id = 144

AND s1.cust_id = w.cust_id

AND s1.rowid = s.rowid ;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) ;

위의 SQL을 보면 SALES 테이블을 두 번 사용하였다. 상식 대로라면 같은 테이블의 중복사용으로 Read 한 Block 의 개수(Buffers 항목)가 원본 SQL보다 증가하여야 한다. 과연 그런가? 아래의 Plan을 보자.

--------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS |    |    423 |00:00:00.08 | 842 |
| 2 | NESTED LOOPS    |    |    423 |00:00:00.07 | 516 |
|* 3 |    INDEX RANGE SCAN | SALES_IDX_02 | 4091 |00:00:00.02 |    17 |
| 4 |    TABLE ACCESS BY INDEX ROWID| WITHDRAWAL_CUST    |    423 |00:00:00.04 | 499 |
|* 5 | INDEX UNIQUE SCAN | PK_WITHDRAWAL_CUST |    423 |00:00:00.01 |    76 |
| 6 | TABLE ACCESS BY USER ROWID | SALES    |    423 |00:00:00.01 | 326 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("S1"."PROD_ID"=144)
   5 - access("S1"."CUST_ID"="W"."CUST_ID")

오히려 성능이 향상 되었다
원본 SQL에 비하여 Block 의 I/O 개수가 4배 이상 줄어들었다. 대단하지 않은가? ID 기준으로 3 에서 SALES 테이블의 SALES_IDX_02 인덱스만 엑세스 하고 SALES 테이블(Alias 로는 S1)은 엑세스 하지않는다. 연이어 WITHDRAWAL_CUST 테이블과 조인함으로써 건수를 423으로 줄여 버린다. 건수를 10분의 1로 줄인 후에 마지막으로 Rowid를 이용하여 SALES 테이블(Alias 로는 S)에 S1의 Rowid로 엑세스 함으로서 성능향상이 가능한 것이다.

언제 같은 테이블을 두번 사용해야 하나?
Nested Loop 조인을 사용할때 선행 테이블의 인덱스 Scan후 곧장 선행 테이블로 엑세스 할때 Filter 가 없거나 Filter의 효과가 약하여 건수를 줄여주지 못하고 오히려 후행 테이블에서 건수를 획기적으로 줄여주는 경우가 있다. 이런 경우는 예외없이 같은 테이블을 2번 사용하여야 한다. 물론 Rowid를 이용해야 하는것은 당연하다. 여러분도 SQL 튜닝시 이런 상황을 많이 만났을 것이고 앞으로도 자주 만나게 될것이다. 이런 상황에서는 꼭 위의 방법을 사용하기 바란다.

지식의 저주

상창력을 이용해야 하는 경우는 상식이 오히려 방해가 될 수 있다. 이것은 지식의 저주라 불린다. 경험이 많거나 아는것이 많을수록 지식의 저주에 걸리게 될 확률은 높아진다. 누구라도 이런것이 조금씩은 있기 마련이다. 하지만 이런 종류의 저주를 많이 혹은 오래 받은 사람은 발전이 없거나 더디게 마련이다.

회피하기
지식의 저주를 받지 않은 사람은 역사를 바꾸기도 하였다. 갈릴레이의 지동설, 다윈의 진화론, 에디슨의 여러가지 발명품 등이 그것이다. 발상을 전환하기, 생각을 뒤집어 보기, 다른 방법으로 접근하기, 의도적으로 엉뚱한 생각하기, 동료와 BrainStorming 하기 등등 저주를 피할 방법은 많다. 이 개념을 SQL튜닝에만 한정시키면 곤란하다. 모든 분야에 사용할수 있다. 누가 알겠는가? 이러한 방법을 사용하여 여러분이 역사를 바꿀지……

PS
이렇게 상식을 뒤집는 케이스는 생각보다 많다. 아래에 링크된 Post 가 그런 경우이다.

Transformer - SQL 튜닝의 새로운 패러다임

Top SQL-( Rownum 의 정확한 사용법 )

위의 Link는 필자가 눈으로 확인한 것만 링크로 나타내었다. 실제로는 이것보다 훨씬 많을 것이다.

'Oracle > Data Access Pattern' 카테고리의 다른 글

오렌지나 TOAD에서 Predicate Information을 참조하는 방법 (7)	2010.01.12
Partition Access Pattern (13)	2009.12.24
Oracle Data Access Pattern을 정복하라 (18)	2009.10.29
Oracle11g 에서 Full Table Scan의 성능 향상 (8)	2009.07.22
InList / Concatnation / Range Scan Control 하기 (4)	2008.12.12

Posted by extremedb

,

Oracle Data Access Pattern을 정복하라

Oracle/Data Access Pattern 2009. 10. 29. 00:01

첨부파일의 일부 인덱스명에 오타가 있으니 댓글을 참조하기 바람(2009.11.05)

SQL 튜닝을 할 때 가장 기본적으로 익혀야 하는 것을 꼽으라면 무엇일까?

첫 번째, Data Access Pattern

두 번째, Join Method

위의 두 가지는 기본 중에 기본이다. 이 두 가지는 너무도 중요하기 때문에 튜닝을 시작하는 사람 혹은 튜너를 지망하는 사람들은 보고 또 보아야 한다. 실제로 이두가지를 모두 정복한다면 SQL 튜닝중의 많은 부분을 커버할수 있다. 이것은 비단 오라클 SQL 튜닝만이 아니라 모든 DBMS가 동일 할 것이다.

오늘은 두가지 중에 첫 번째의 Data Access Pattern의 개념에 대해서 이야기 해보자.

Data Access Pattern을 최대한 간단하게 표현하면 아래와 같다.

Index Scan : 인덱스 사용

Full Table Scan : 인덱스 미사용

Rowid : Rowid로 테이블 엑세스

하지만 위의 3가지는 너무나 추상적이다.
위의 3가지를 좀더 상세히 나타내 보자.

Rowid

Index Unique Scan

Index Range Scan

Index Inlist Iterator

Index Skip Scan

Index Full Scan

Index Fast Full Scan

Bit Map Index Combination

Full Table Scan

3가지가 9가지가 되었다. 이제 제법 실전의 Plan에서 볼 수 있는 모습이 나왔다고 생각할 것이다.

하지만 아직 많이 부족하다.
실전의 Plan에는 위의 모습 보다 더욱 상세한 것이 필요하기 때문이다.

Rowid

Index Unique Scan

Index Range Scan

Index Range Scan Descending

Index Range Scan (min/max)

Index Inlist Iterator

Index Inlist Iterator Descending

Index Skip Scan

Index Skip Scan Descending

Index Full Scan

Index Full Scan Descending

Index Full Scan (min/max)

Index Fast Full Scan

BIT MAP OR

BIT MAP AND

BIT MAP MINUS

BIT MAP MERGE

Full Table Scan

드디어 3가지가 18가지가 되었고 실전적인 모습이 되었다.

위의 18가지 중에 한가지라도 찜찜한것이 있으면 첨부파일을 다운받아서 개념을 익히길 바란다.
파일을 배포 할 경우 출처를 밝혀주기 바란다.
파일 3개를 모두 다운 받은후 압축을 풀면 된다.

invalid-file

PS :
여기에 나오지 않은것들이 몇가지 있다. Partition Access Pattern은 이문서에서 언급되지 않았다.
다음 기회에..
Index Join 은 Data Access Pattern에 속하는 것이 아니고 Join method 에 포함되어야 한다.

'Oracle > Data Access Pattern' 카테고리의 다른 글

Partition Access Pattern (13)	2009.12.24
모든 상식을 의심하라 (16)	2009.11.05
Oracle11g 에서 Full Table Scan의 성능 향상 (8)	2009.07.22
InList / Concatnation / Range Scan Control 하기 (4)	2008.12.12
Range Partition 적용 테이블의 MIN/MAX 처리시의 성능저하 현상 (2)	2008.07.28

Posted by extremedb

,

Varchar2(8) VS Date 어느 것이 우월한가?

Modeling 2009. 10. 21. 16:06

일자 데이터 타입이란 YYYYMMDD 형(시/분/초 제외)을 이야기 하는 것이다. 이때 DATE 타입을 선택할 것인가 아니면 VRACHR2(8)을 선택할 것인가의 문제이다. 이것은 성능 문제이기도 하지만 물리 모델링, 개발효율성, 데이터 품질 등을 같이 생각 해야 한다. 물리모델링 시에 많은 모델러들이 일자 데이터 타입과 관련하여 이구동성으로 이야기 하는 것이 아래의 SQL 이다.

SELECT ...

FROM ...

WHERE 기준일자 = TO_DATE('20091021', 'YYYYMMDD') ;

위의 SQL 에서 일자 컬럼에 시/분/초가 포함되어 있다면 조회가 되지 않는다.

그렇다고 SQL 을 아래처럼 작성하는 것은 개발효율성이 떨어지고 성능에도 이롭지 못하다.

SELECT ...

FROM ...

WHERE 기준일자 BETWEEN TO_DATE('20091021','YYYYMMDD') AND TO_DATE('200910212359', 'YYYYMMDDHH24MISS') ;

아니 땐 굴뚝에 연기가 날까?

VARCHAR2(8)을 선호하는 사람들이 주로 이 문제를 제기한다. DATE 타입은 시/분/초가 들어감으로써 세가지 문제(데이터가 조회되지 않을 수 있고, 개발효율성과 성능이 떨어짐)가 발생함으로 VARCHAR2(8)을 사용해야 한다는 것이다.

하지만 과연 이 말이 사실일까? 모든 문제는 모델러가 시/분/초가 들어갈 수 있게 설계를 했기 때문이다. 왜 그런지 아래 스크립트를 보고 증명해보자

-- DATE 형과 VARCHAR2 형을 동시에 가진 테이블 생성

CREATE TABLE DT

( V_DT VARCHAR2(8 BYTE),

D_DT DATE ) ;

--일자 타입이 date 인 경우에 시/분/초가 입력될 경우 걸러내는 Constraint임.

--ex) 시/분/초를 포함하는 SYSDATE를 입력하면 에러를 발생시키기 위함.

--CHECK 절에 OR 가 있는 이유는 NULL 을 허용하기 위해서 이다.

ALTER TABLE DT

ADD CONSTRAINT D_DT_CHK

CHECK (D_DT = TRUNC(D_DT) OR D_DT IS NULL) ;

이제 DATE 타입에 정상적인 데이터와 걸러져야 하는 데이터를 INSERT 해보자

--NULL을 대입해도 에러 발생하지 않음

INSERT INTO DT (V_DT, D_DT) VALUES(NULL, NULL) ;

--SYSDATE는 시/분/초가 들어감으로 INSERT 되면 안됨

INSERT INTO DT (V_DT, D_DT) VALUES (NULL, SYSDATE) ;

ORA-02290: 체크 제약조건(D_DT_CHK)이 위배되었습니다

--에러 발생하지 않음

INSERT INTO DT (V_DT, D_DT) VALUES (NULL, TRUNC(SYSDATE)) ;

Constraint는 데이터 품질을 보장해준다

위에서 보는 바와 같이 Constraint는 시/분/초가 들어가지 않도록 보장해준다.

이것은 성능이 느린 VARCHAR2 타입을 사용하지 말아야 하는 이유가 될 수 있다.

어쩔 수 없이 VARCHAR2(8)을 사용하더라도 Constraint를 사용하라

일자 데이터 타입에 VARCHAR2(8)을 사용할 때 날짜가 아닌 데이터가 들어가는 문제도 마찬가지로 해결할 수 있다. 아래처럼 Constraint를 사용하면 된다.

--일자 타입이 VARCHAR2 인 경우에 잘못된 데이터를 걸러내는 Constraint 임.

--ex) ‘20090230’을 걸러낸다.

--CHECK 절에 OR 가 있는 이유는 NULL 을 허용하기 위해서 이다.

ALTER TABLE DT

ADD CONSTRAINT V_DT_CHK

CHECK (V_DT = TO_CHAR(TO_DATE(V_DT,'YYYYMMDD'), 'YYYYMMDD') OR V_DT IS NULL) ;

이제 VARCHAR2(8)에 일자가 아닌 데이터를 넣어보자

--2월30일은 일자가 아니므로 INSERT 되면 안됨

INSERT INTO DT (V_DT, D_DT) VALUES ('20090230', NULL) ;

ORA-01839: 지정된 월에 대한 날짜가 부적합합니다

--에러 발생하지 않음

INSERT INTO DT (V_DT, D_DT) VALUES ('20090228', NULL) ;

성공적으로 일자가 아닌 String 을 걸러 내었다.

결론

이제 알 것이다. Constraint가 데이터 품질을 향상시키고 개발효율성을 높이며 성능에 까지 영향을 끼친다는 사실을

반박하라. 시/분/초가 들어감으로 VARCHAR2(8)을 사용해야 한다는 주장을

사용하라. 성능과 데이터 품질을 향상 시키는 DATE 타입을

오라클에서 시/분/초는 제외하고 일자만 저장되는 데이터 타입을 제공한다면 하는 아쉬움이 진하게 남는다. 미래의 버젼에 이러한 요구사항을 해결한 데이터 타입이 나오길 기대한다.

PS

DATE 타입을 성급히 적용하면 안 된다. 기존의 시스템은 일자가 아닌 데이터도 문제 없이 처리가 되었을 것이다. 하지만 DATE 타입으로 바꾸면 에러로 떨어진다. 따라서 에러의 처리정책과 처리Logic 등이 세워진 이후에 적용하라. 이런 골치 아픈 문제 때문에 VARCHAR2(8)을 사용하는 것은 말이 안 된다. 원칙적으로 에러로 떨어지는 것이 정당한 것이고 데이터를 수정하고 다시 처리하는 것이 옳다.

아래는 DATE 타입과 VARCHAR2 타입의 장단점 이므로 참고하기 바란다.

----------------------------------------------------------------------------------------------------------------------

성능상으로는 DATE 형이 우월하다

여기에는 두 가지 이유가 있다.

첫 번째, DATE 타입은 옵티마이져가 날짜임을 인식한다.

SELECT ...

FROM ...

WHERE 기준일자 BETWEEN to_date('20091020', 'yyyymmdd') and to_date('20091021', 'yyyymmdd)

기준일자는 DATE 타입이다. 위의 조건대로라면 옵티마이져는 정확히 이틀간(10월 20일부터 21일까지)이라는 것을 인지한다. 따라서 옵티마이져는 인덱스로 SCAN 할 것인지 아니면 FULL TABLE SCAN 할 것인지 판단할 수 있다.

하지만 기준일자가 아래처럼 VARCHAR2(8) 타입이라면 달라진다.

SELECT ...

FROM ...

WHERE 기준일자 BETWEEN '20091020' and '20091021'

옵티마이져는 '20091020'가 일자인지 인식 하지 못한다. 따라서 이틀 치의 데이터를 조회한다는 사실을 인식하지 못한다. String 타입이므로 이것은 당연한 것이다.

두 번째, DATE 타입은 7 byte를 차지하고 VARCHAR2(8) 은 8 byte를 차지한다.

두 가지 이유에 의해서 성능은 DATE 타입이 조금이라도 우월함을 알 수 있다.

그럼에도 불구하고 DATE 타입을 사용하지 않는 가장 큰 이유는 무엇일까? 크게 3가지 이유로 요약된다.

첫 번째, DATE 타입의 문제점은 sysdate 등을 입력할 경우 시/분/초 가 들어감으로 SQL을 실행하면 결과값이 나오지 않는다.(이 문제는 위에서 이미 언급되었음)

두 번째, 년도나 월 데이터를 조회할 때 LIKE를 사용하지 못하고 BETWEEN 을 사용해야 한다는 것이다. 이것은 큰 문제라고 보지 않는다. 조건절이 조금 길어질 뿐 INDEX RANGE SCAN 이라는 점은 같기 때문이다.

세 번째, SYSDATE 등을 INSERT할 때 TRUNCT 등의 함수를 사용하여 시/분/초 등을 잘라내야 한다.

그렇다면 VARCHAR2 타입의 문제점은 없는가?
크게 3가지의 문제점이 있다.

첫 번째, 성능문제(옵티마이져가 일자인지 알 수 없음)

이 문제는 ORACLE 11g를 사용하면 더욱 심각한 차이가 발생할 수 있다.

왜냐하면 Bind Aware 기능이 강화되었기 때문에 변수를 마치 상수처럼 취급할 것이고 이에 따라 DATE 타입이 성능 면에서 훨씬 우월해 질것이다.

두 번째, VARCHAR2 타입의 문제점은 날짜가 아닌 데이터가 들어갈 수 있다는 것이다. 예를 들면 'ABCDEFGH' 혹은 '20090231' 등의 잘못된 데이터가 입력될 수 있다. 이것은 데이터 품질에 치명적이다. 혹자는 'DATE 타입도 시/분/초가 들어가므로 마찬가지 아니냐' 라고 생각할 수 있지만 근본적으로 다르다. DATE 타입을 사용하면 시/분/초는 TRUNC 등의 함수를 사용하여 Cleansing할 수 있지만 VARCHAR2는 그렇게 할 수 없다.

예를 들어 '20091032' 라는 데이터를 Cleansing 해야 한다고 치면 10월 31일로 할 것인가? 아니면 11월 1일로 할 것인가? 이것은 함부로 판단할 수 없는 것이다. 혹자는 'CHECK 기능을 추가하여 모든 프로그램에서 일자가 아닌 것을 CHECK 하면 되지 않냐?' 라고 할 수 있다.

맞는 말이다. 하지만 프로그램을 사용하지 않고 직접 DB에 KEY IN 하여 INSERT 할 수도 있기 때문에 원천적으로 원인을 제거한다고 볼 수 없다.(급한 경우에는 이렇게 하기도 한다)

세 번째, 성능문제에도 불구하고 개발편의성을 위해 VARCHAR2를 사용하였지만 일자연산이 발생하면 오히려 개발생산성이 저하된다.

예를 들면 일자끼리 빼서 차이를 본다든지 아니면 일자에 며칠을 더해서 본다든지 일자에 연산이 일어날 경우 오히려 TO_DATE 등의 함수를 사용해야 한다. 이런 경우는 자주 발생되는 편이다.

이상으로 DATE 타입과 VARCHAR2 타입의 장단점을 살펴보았다.

이 글과 관련된 POST 도 참고하기 바란다.

http://ukja.tistory.com/265

'Modeling' 카테고리의 다른 글

3부 - 변경이력 테이블에 종료일자가 필요한가? (8)	2010.12.03
2부 - 변경이력 테이블에 종료일자가 필요한가? (10)	2010.11.24
변경이력 테이블에 종료일자가 필요한가? (30)	2010.11.17
역정규화 무엇이 문제인가? (7)	2009.12.14
모델링에 관한 즐거운 토론 (10)	2008.10.19

Posted by extremedb

,

Transformer - SQL 튜닝의 새로운 패러다임

Oracle/Optimizer 2009. 10. 15. 16:37

“SQL 작성시 같은 테이블을 반복해서 사용하지 마라”

위와 같은 말을 많이 들어보았을 것이다. 이런 말들은 개발자에게 마치 격언, 명언처럼 취급된다. 오늘도 개발자, DBA, 컨설턴트등 모든 사람들이 위의 명언에 너무도 충실하여 반복되는 테이블을 제거하기 위해 SQL을 재작성 하고 있다. 하지만 이런 말들이 명언이나 격언이 될 수 있을까? 이제는 격언이나 훈수, 명언이라고 생각하는 말도 최소한 상황에 따라 가려서 해야 한다. 왜 그럴까? 아래의 SQL을 보자.

SELECT /*+ use_hash(c s) */

s.prod_id, s.cust_id, s.quantity_sold,

s.amount_sold, c.channel_desc

FROM sales s, channels c

WHERE c.channel_id = s.channel_id

AND c.channel_id = 3

UNION ALL

SELECT /*+ use_hash(c s) */

s.prod_id, s.cust_id, s.quantity_sold,

s.amount_sold, c.channel_desc

FROM sales s, channels c

WHERE c.channel_id = s.channel_id

AND c.channel_id = 9 ;

위의 SQL은 대용량 테이블인 판매 테이블(sales)을 비효율적으로 2번 Scan할 것으로 예상된다. 하지만 아래의 Plan을 보라. 과연 그렇게 수행되는가?

-----------------------------------------------------------+-----------------------
| Id | Operation | Name    | Rows | Bytes | Cost |
-----------------------------------------------------------+-----------------------
| 0 | SELECT STATEMENT    | | | | 495|
| 1 | HASH JOIN    | | 449K | 20M | 495|
| 2 | VIEW    | VW_JF_SET$0A277F6D| 2 |    50 | 2|
| 3 |    UNION-ALL    | | | |    |
| 4 | TABLE ACCESS BY INDEX ROWID | CHANNELS    | 1 |    13 | 1|
| 5 |    INDEX UNIQUE SCAN    | CHANNELS_PK | 1 | | 0|
| 6 | TABLE ACCESS BY INDEX ROWID | CHANNELS    | 1 |    13 | 1|
| 7 |    INDEX UNIQUE SCAN    | CHANNELS_PK | 1 | | 0|
| 8 | PARTITION RANGE ALL | | 897K | 18M | 489|
| 9 |    TABLE ACCESS FULL    | SALES | 897K | 18M | 489|
-----------------------------------------------------------+-----------------------

Predicate Information:
----------------------
1 - access("ITEM_1"="S"."CHANNEL_ID")
5 - access("C"."CHANNEL_ID"=3)
7 - access("C"."CHANNEL_ID"=9)

이제는 Transformer 가 튜너이다.

환상적이지 않은가? channels 테이블을 정확히 2건만 Scan 하였고 sales 테이블은 1번만 Full Scan 하였다. 오라클 Transformer가 SQL을 아래처럼 재작성 한 것이다.

SELECT s.prod_id prod_id, s.cust_id cust_id, s.quantity_sold,

s.amount_sold, vw_jf_set$0a277f6d.item_2 channel_desc

FROM (SELECT c.channel_id AS item_1, c.channel_desc AS item_2

FROM channels c

WHERE c.channel_id = 3

UNION ALL

SELECT c.channel_id AS item_1, c.channel_desc AS item_2

FROM channels c

WHERE c.channel_id = 9) vw_jf_set$0a277f6d,

sales s

WHERE vw_jf_set$0a277f6d.item_1 = s.channel_id ;

위와 같은 상황에서 SQL을 재작성 하는 기능을 JF(Join Factorization)라고 부른다. VW_JF_SET으로 시작되는 인라인뷰 명(Plan 상의 파란색 부분)은 JF가 수행되었음을 나타내는 것이다. 이것은 Oracle11g R2 에서 새로 추가된 대표적인 CBQT(Cost Based Query Transformation)기능 이다.

항상 수행되지는 않는다

CBQT는 Cost로 SQL튜닝을 수행할 것인지 아닌지를 판단한다. 그런데 복잡한 SQL의 경우에는 Cost Estimator가 판단을 잘못하여 JF를 수행하는 것이 비용이 더 비싸다고 판단할 수 있다. 이런 경우에는 Transformer가 SQL을 튜닝(재작성) 하지 않는다. 이럴 때는 아래와 같이 힌트를 사용해야 한다.

SELECT /*+ use_hash(c s) FACTORIZE_JOIN(@SET$1(S@SEL$1 S@SEL$2)) */

s.prod_id, s.cust_id, s.quantity_sold,

s.amount_sold, c.channel_desc

FROM sales s, channels c

WHERE c.channel_id = s.channel_id

AND c.channel_id = 3

UNION ALL

SELECT /*+ use_hash(c s) */

s.prod_id, s.cust_id, s.quantity_sold,

s.amount_sold, c.channel_desc

FROM sales s, channels c

WHERE c.channel_id = s.channel_id

AND c.channel_id = 9 ;

쿼리블럭명 SET$1은 전체에 해당하는 쿼리블럭이고 SET$1 의 내부에 또 다른 쿼리블럭인 SEL$1(Union All로 분리된 것 중의 윗부분), SEL$2(Union All로 분리된 것 중의 아랫부분) 이 존재한다. 만약 Union All 이 하나 더 있다면 쿼리블럭명은 SEL$3가 될 것이다.

JF를 자세히 분석 하려면 10053 Event Trace를 이용하라

먼저 10053 의 용어 설명부분에 JF 가 아래처럼 추가 되었다.

The following abbreviations are used by optimizer trace.

CBQT - cost-based query transformation

JPPD - join predicate push-down

…중간생략

JF - join factorization

아래는 JF 부분에 해당하는 10053 Trace 정보이다.

***********************************

Cost-Based Join Factorization

***********************************

Join-Factorization on query block SET$1 (#1)

JF: Using search type: exhaustive

JF: Generate basic transformation units

…이후생략

결론

이제 격언이나 명언이라고 생각되는 말들도 상황을 가려서 해야 한다는 것을 알겠는가?. 그렇지 않으면 똑똑한 개발자에게 오히려 다음과 같은 말을 들을 것이다. “지금 말씀 하신 것은 예전 이야기 입니다. 요즘은 트랜스포머가 알아서 해줍니다.” 적어도 튜닝의 세계에서는 그렇다.

새로운 패러다임

JF는 Transformer가 수행하는 SQL튜닝의 하나일 뿐이다. Oracle11g R2 기준으로 SQL 튜닝(Query Transformation)의 종류는 필자의 짧은 지식으로도 70개 이상일 것으로 판단 된다. Oracle이 발전해 가면서 SQL튜닝은 사람이 관여하는 것에서 오라클이 자동으로 SQL을 변경해주는 것으로 많은 부분이 바뀌었고 앞으로 이런 추세는 점점 강화될 것이다. Query Transformation 은 단순한 Optimizer의 기능이 아니라 SQL 튜닝의 새로운 패러다임인 것이다. 이제는 직접 튜닝 하는 것에서 벗어나 Transformer가 실수하는 경우 새로운 길을 열어주는 것이 튜너가 가야할 길이 아닌가?

'Oracle > Optimizer' 카테고리의 다른 글

Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다 (14)	2009.12.07
MERGE 문과 IN 조건이 만난다면 (8)	2009.11.26
Temp Table Transformation (5)	2009.09.28
스칼라 서브쿼리를 서브쿼리로 변환하라 (11)	2009.09.10
Semi Join 의 재조명 (5)	2009.08.31

Posted by extremedb

,

오라클 Regular Expressions 완전정복

Oracle/SQL Pattern 2009. 10. 7. 00:01

오라클의 Regular Expressions을 정의 하면 아래와 같다

유닉스의 정규식과 같음.
강력한 Text 분석도구로서 Like 의 한계를 극복함.
Pattern-Matching-Rule의 정의가 자유로움.
다양한 메타문자 제공.

Regular Expressions의 중요성은 아래와 같은 작업시 매우 증대된다

When

•

ETL/전환/이행.

Data Mining.

Data Cleansing.

데이터 검증.

Regular Expressions의 기능은 다음과 같다
What

•

Text에서 특정 중복 단어의 확인
특별한 상태에서 공백의 제거
특정 문자의 파싱(parsing)
Text 에서 전화 번호, 우편 번호, 이메일 주소, 주민등록번호, IP 주소, 파일 이름, 경로 이름 등을 검증 및 추출이 가능
HTML 태그, 숫자, 날짜, 기타 특정 텍스트 데이터와 일치하는 패턴을 확인하고 다른 패턴으로 대체하는 것이 가능
Constraints 로 사용가능

이렇게 중요한 정규식(Regular Expressions)을 잘 모르거나 익숙치 못한 사람들을 위하여 개인적으로 정리한 파일을 올리니 이번기회에 반드시 정복하길 바란다. 의외로 고급 개발자들 중에 Regular Expressions의 사용을 꺼려하는 사람들이 많이 있는것 같다. 이 기능을 써야하는 경우임에도 쓰지 않는다면 코드는 길어질 것이며 유지보수 또한 힘들어 질것이다.

첨부파일에 정리된 내용은 다음과 같다

정규식 기본 Syntax.
함수 사용법.
정규식 고급 Syntax.
11g New Features

주의 사항 :

첨부된 파일에는 Oracle11g R1 까지의 내용을 정리한 것임.
배포시에는 반드시 출처를 밝힐것.
Upload 시 200K 용량제한 때문에 파일을 2개로 분할 압축 하였으므로 모두 다운 받은후 압축을 풀면 됨.

invalid-file

오라클 Regular Expresssions 완전정복1

invalid-file

오라클 Regular Expresssions 완전정복2

'Oracle > SQL Pattern' 카테고리의 다른 글

검색조건을 동적으로 바꾸어야 할 때 (1)	2010.12.14
Oracle Analytic Function의 모든 것 (20)	2010.03.29
간단한 집합개념 Test (7)	2009.07.06
스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기. (7)	2009.06.29
Model 절에 대하여 (10)	2009.05.28

Posted by extremedb

,

볼륨매니저는 역사 속으로

Oracle/News 2009. 9. 30. 10:55

추가적인 정보가 있으니 댓글을 반드시 확인하세요.(2009.10.01)

이젠 Oracle11g R2의 시대
2010년도부터 본격적으로 프로젝트시 Oracle11g R2를 선택하게 될 것이다. Oracle11g R2의 장점은 이미 많이 알려져 있다. 신기능은 수도 없이 많지만 대표적인 것들을 소개하면 아래와 같다.

Top 10 Oracle 11gR2 New Features

Edition-Based Redefinition

DVM은 필수다
이러한 장점에도 불구하고 치명적일 수 있는 점을 소개한다. 그것은 볼륨매니저를 더 이상 사용할 수 없다는 것이다. Oracle11g R2부터 RAC를 사용할 경우 DVM(ASM Dynamic Volume Manager)을 반드시 사용해야만 한다. 기존의 볼륨매니저를 사용할 수 있는 방법이 있는데 그것은 Oracle11g R2를 바로 설치하는 대신에 Oracle11g R1 에서 Oracle11g R2로 업그레이드하는 방법이 있다. 하지만 이것은 어디까지나 트릭이며 추천할 것은 못 된다.

기존의 베리타스등의 Third Party 볼륨매니저는 버려야 하나?

이것은 일종의 끼워팔기 인가?
이젠 Raw Device 대신 ACFS(ASM Cluster File System)을 사용해야 하나?

어찌되었건 내년부터 본격적인 ASM의 시대가 될것이다.

'Oracle > News' 카테고리의 다른 글

오라클은 몇년이나 갈까? (4)	2009.07.31
Science of DataBase 1주년 (22)	2009.07.02
오라클 공룡기업으로 거듭나는가? (0)	2009.04.21

Posted by extremedb

,

Temp Table Transformation

Oracle/Optimizer 2009. 9. 28. 18:09

오라클 Transformer 는 때때로 반복작업이 일어날경우 같은 테이블에 대한 부하가 심해지므로 TEMP 테이블을 만들어서 저장하고 그 테이블을 이용하여 반복적인 작업을 하게되는 경우가 있다. 대표적인 경우가 GROUPING SETS 를 사용하는 경우이다.

SELECT department_id, JOB_ID, AVG (salary) avg_sal

FROM employee a

GROUP BY GROUPING SETS(department_id, JOB_ID) ;

---------------------------------------------------------------+---------------------------+

---------------------------------------------------------------+---------------------------+

| 0 | SELECT STATEMENT | | | 11 | |

| 1 | TEMP TABLE TRANSFORMATION | | | | |

| 2 | LOAD AS SELECT | | | | |

| 3 | TABLE ACCESS FULL | EMPLOYEE | 107 | 3 | 00:00:01 |

| 4 | LOAD AS SELECT | | | | |

| 5 | HASH GROUP BY | | 1 | 3 | 00:00:01 |

| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_434CFB| 1 | 2 | 00:00:01 |

| 7 | LOAD AS SELECT | | | | |

| 8 | HASH GROUP BY | | 1 | 3 | 00:00:01 |

| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_434CFB| 1 | 2 | 00:00:01 |

| 10 | VIEW | | 1 | 2 | 00:00:01 |

| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_434CFB| 1 | 2 | 00:00:01 |

---------------------------------------------------------------+---------------------------+

위의 PLAN 을 보면 먼저 ID 기준으로 3번에서 employee 테이블을 읽어서 필요한 컬럼만 TEMP 테이블에 저장해두고 그테이블을 반복해서 이용(ID 기준으로 6번, 9번, 11번)하게 되는것이다. 그렇다면 왜 3번이나 반복해서 temp 테이블을 사용하는걸까?

그 해답은 10053 trace 에 있다. id 기준으로 6번에 해당하는 SQL 이 아래와 같다.

SELECT /*+ */ NULL C0, C1 C1, BIN_TO_NUM(1, GROUPING(C1)) D0, COUNT(A0), SUM(A0) FROM "SYS"."SYS_TEMP_0FD9D6608_434CFB" GROUP BY (C1)

위에서 C0, C1 은 TEMP 테이블의 컬럼 ALIAS 이며 각각 department_id, JOB_ID를 의미한다. 또한 BIN_TO_NUM 함수를 사용한 이유는 나중에 ID 기준으로 11번째 에서 Group by 단위를 알아내기 위함이다. COUNT(A0), SUM(A0) 를 select 한이유도 11번에서 AVG (salary) 값을 구하기 위해서이다. 참고로 avg(col1) 함수는 논리적으로 sum(col1)/count(col1) 과 같다.

또한 id 기준으로 9번에 해당하는 SQL 이 아래와 같다.

SELECT /*+ */ C0 C0, NULL C1, BIN_TO_NUM(GROUPING(C0), 1) D0, COUNT(A0), SUM(A0) FROM "SYS"."SYS_TEMP_0FD9D6608_434CFB" GROUP BY (C0)

최종적으로 Transformation이 적용된 SQL은 다음과 같다.

with SYS_TEMP_0FD9D6608_434CFB as
(
SELECT department_id AS C0, JOB_ID AS C1, salary AS A0
   FROM employee a
) ,
   TEMP1 as
   (
SELECT NULL C0, C1, COUNT(salary) AS A1, SUM(salary) AS A0
   FROM SYS_TEMP_0FD9D6608_434CFB
GROUP BY C1
   ) ,
   TEMP2 as
   (
SELECT C0, NULL C1, COUNT(salary) AS A1, SUM(salary) AS A0
   FROM SYS_TEMP_0FD9D6608_434CFB
GROUP BY C0
   ) ,
SYS_TEMP_0FD9D6609_434CFB AS
(
   SELECT TEMP1. * FRPM TEMP1
   UNION ALL
   SELECT TEMP1. * FRPM TEMP1
)
SELECT C0 AS DEPARTMENT_ID, C1 AS JOB_ID, A0 AS AVG_SAL
FROM (SELECT C0, C1,
DECODE(A0, 0, TO_NUMBER(NULL), A1/A0) AS A0 --> 분모가 0 일 경우 처리
FROM SYS_TEMP_0FD9D6609_434CFB ) ;

불만
id 기준으로 6번과 9번에서 각각 job_id 와 department_id 로 group by 해놓고 이것을 id 11 번에서 합쳐서 보여주게 된다. 하지만 필자는 이런 변환에 대하여 불만이 있다. 위의 쿼리는 EMPLOYEE 테이블을 GROUP BY 하지 않은 상태로 TEMP 테이블에 적재한다. 이것은 매우 비효율적이다. EMPLOYEE 테이블을 Temp 테이블에 적재시 미리 Group BY 하여 넣을 수가 있다. 아래의 SQL 처럼 미리 GROUP BY 하여 건수를 미리 줄여놓으면 반복작업시의 부하가 획기적으로 줄어들수 있기 때문이다.

select department_id, JOB_ID, sum(sum_sal) / sum(cnt) as avg_sal

from (SELECT department_id, JOB_ID, count(salary) cnt, sum(salary) sum_sal

FROM employee a

GROUP BY department_id, JOB_ID )

GROUP BY GROUPING SETS(department_id, JOB_ID) ;

이렇게 수동으로 SQL을 바꾸는 방법은 Grouping Sets의 변환로직이 변하지 않는한 확실한 튜닝방법이 될수 있으므로 반드시 고려되어야 한다.

'Oracle > Optimizer' 카테고리의 다른 글

MERGE 문과 IN 조건이 만난다면 (8)	2009.11.26
Transformer - SQL 튜닝의 새로운 패러다임 (11)	2009.10.15
스칼라 서브쿼리를 서브쿼리로 변환하라 (11)	2009.09.10
Semi Join 의 재조명 (5)	2009.08.31
Group By 를 먼저 수행하고 Join 하라( Group By Placement ) (4)	2009.08.24

Posted by extremedb

,

SQL 튜닝이 불가한 경우

Oracle/TroubleShooting 2009. 9. 16. 10:17

SQL 튜닝시의 업무적 접근
SQL 튜닝시 Buisiness 관점으로 접근하는 것은 매우 중요한 일이다. 예를 들면 SQL을 아무리 튜닝을 해도 해결되지 않는 경우가 있다. 당신이 아무리 화려한 튜닝 테크닉을 가지고 있다고 해도 말이다. 이런 경우 발상을 전환하는 것이 필요하다. SQL 튜닝을 하지않고 이슈를 해결해야 한다는 것 이다. 튜닝시 이슈가 가장 많이 발생하는 3가지 경우를 소개한다.

조회조건은 제약이 필요없나?
웹화면에서 조회조건으로 일자 From ~ To 조건으로 주는 경우가 다반사이다. 사용자는 많은 기간을 조회하고 싶어한다. 실제로 10년간의 거래내역집계를 조회하는 경우도 보았다. 이런 경우는 반드시 화면에서 기간의 제한을 두어야 한다. 한달로 제한 한다든지 아니면 일주일로 한다든지 상황에 따라서 얼마든지 제약을 줄수 있다. 조회조건 선택시 Combo Box 나 Drop Down List Box 에서 전체보기를 없앤다면 마찬가지로 성능향상을 기대할수 있다. 개발자와의 대화 혹은 화면분석이 필요한 순간이다. 필자는 이러한 작업들을 튜닝 프로젝트시에 반드시 실행해야하는 필수 과정으로 생각한다. 이러한 과정을 사용자 화면 튜닝으로 정의 해야한다.

집계 테이블은 필요한가?
또 다른 예제는 SQL이 집계(Group By + Count)를 하는 경우 아무리 튜닝을 해보아야 느릴수 밖에 없다. 가장 좋은 것은 회의를 통하여 이러한 화면들을 DW시스템으로 넘기는 것이다. 이것은 어느정도의 정치력이 필요하다. 주장하는 사람의 권위와 Power 가 필요하다는 이야기 이다. 하지만 이것이 안될경우 업무팀과 협의 하여 집계 테이블을 만들어야 한다. 집계테이블은 최소화 시켜야 한다. 예를 들어 년별, 지점별 집계 SQL이 있고 월별 지점별 집계 SQL이 있다면 집계 테이블은 월별로 하나만 만들어야 한다. 집계 테이블이 많아지면 정합성을 저해할수 있다.

저작의도를 알면 길이 보인다
계약 테이블과 고객 테이블을 불필요하게 조인 하는 SQL 이 있었다. 하지만 조인만 하고 Select 절에서는 고객 테이블의 컬럼이 하나도 없었다. 상식적으로 보면 계약테이블의 고객번호는 100% 고객 테이블에 있어야 한다. 그렇다면 고객 테이블을 From 절에서 삭제 하면 될까? 아주 위험한 발상이다. 특수한 SQL 의 경우에는 검증용 SQL 을 프로그램으로 만들기도 한다. 이러한 경우 방법은 딱 한가지이다. SQL 을 작성한 개발자에게 질문하여 이것이 계약 테이블의 고객번호를 검증하기 위한 SQL 인지 물어보는 것이다. 경험상 대부분의 경우 불필요한 조인을 삭제할수 있었다. 이러한 상황은 특히 개발자들 끼리 SQL을 Copy 후 약간 수정하여 사용하는 경우 많이 발생 하게된다.

회의나 대화도 중요한 튜닝 Skill 이라는 점
여러분은 위의 3가지 경우를 어떻게 생각하는가? 튜닝의 기술적인 Skill이 아니므로 무시하고 넘어가는 경우를 많이 보아왔다. 절대 문제를 해결할수 없으며 문제를 키울 뿐이다. 이렇게 되지 않으려면 튜닝시에 사용자 혹은 개발자와 자주 회의를 가져야 한다. 경험상 대화를 자주 할수록 문제의 해결 혹은 대안을 빨리 찾는 경우가 많았다. 이때 고객과의 대화의 기술 혹은 설득의 기술이 많은 도움이 된다. 이러한 기술들은 튜닝 자체의 Skill 보다 상위의 개념이며 문제를 해결할 경우 팀원이나 직원들에게 존경을 받거나 부러움의 대상이 될 가능성이 크다.

결론:
실제로 SQL 튜닝 등으로 성능향상이 불가능한 경우는 당신이 생각하는 것 보다 훨씬 많다.
이럴 경우 화려한 튜닝 테크닉으로 문제를 해결을 시도하는 사람, 대화와 설득으로 해결하는 사람 누가 더 뛰어난 튜너인가?

'Oracle > TroubleShooting' 카테고리의 다른 글

한방 Query를 사용하지 말아야 할 때 (17)	2010.02.19
유명한 Function Based Index 의 버그 (9)	2009.11.19
천당과 지옥의 차이 (3)	2009.06.17
Sequence 의 오작동 (무조건 채번되는 Sequence) (0)	2009.02.19
Sequence 의 오작동 (번호를 건너뛰는 Sequence) (4)	2009.02.10

Posted by extremedb

,

스칼라 서브쿼리를 서브쿼리로 변환하라

Oracle/Optimizer 2009. 9. 10. 11:50

개발자의 질문
개발자 한명이 DBA 에게 질문을 던졌다. ‘스칼라 서브쿼리를 사용한 SQL 을 인라인뷰로 싸고 인라인뷰 외부에서 스칼라 서브쿼리를 Filter 조건으로 사용하면 스칼라 서브쿼리가 없어지는 현상이 발생합니다. 이 현상이 정상인가요?’ 그런데 DBA 는 그런일은 발생할 수가 없다고 하였다. 과연 사실일까? 정답은 스칼라 서브쿼리가 없어진다는 것이다. 예리한 눈을 가진 개발자 임에 틀림없다.

백견이 불여일행
SSTS( Scalar Subquery To Subquery ) 는 스칼라 서브쿼리를 서브쿼리로 변경시키는 Transformation 과정이다. 하지만 항상 변환되지 않는다. 스칼라 서브쿼리를 인라인뷰 외부에서 Filter 조건으로 사용할때만 가능하다. 아래의 SQL 을 보자

인덱스 상황 :

EMP_JOB_IX : employee (job_id)

DEPT_ID_PK1 : department(department_id)

SELECT a.employee_id, a.first_name, a.last_name, a.email

FROM (SELECT e.employee_id, e.first_name, e.last_name, email,

(SELECT location_id

FROM department d

WHERE d.department_id = e.department_id) AS location_id

FROM employee e

WHERE e.job_id = 'IT_PROG') a

WHERE a.location_id > 0;

위의 SQL 은 Location 을 스칼라 서브쿼리로 구현하였다. 하지만 스칼라 서브쿼리를 Select 하는데는 사용하지 않고 Where 조건으로 사용하는 것을 주목하라.

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

|* 1 | FILTER | | | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 5 | 195 | 2 (0)|

|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 5 | | 1 (0)|

| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 7 | 1 (0)|

|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK1 | 1 | | 0 (0)|

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(>0)

3 - access("E"."JOB_ID"='IT_PROG')

5 - access("D"."DEPARTMENT_ID"=:B1)

위의 Plan 을 보면 본능적으로 스칼라 서브쿼리가 서브쿼리로 바뀐 것을 알수 있어야 한다. 다시말하면 옵티마이져가 SQL 을 아래처럼 바꾼 것 이다.

SELECT e.employee_id, e.first_name, e.last_name, email

FROM employee e

WHERE e.job_id = 'IT_PROG'

AND (SELECT location_id

FROM department d

WHERE d.department_id = e.department_id) > 0 ;

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

|* 1 | FILTER | | | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 5 | 195 | 2 (0)|

|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 5 | | 1 (0)|

| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 7 | 1 (0)|

|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK1 | 1 | | 0 (0)|

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(>0)

3 - access("E"."JOB_ID"='IT_PROG')

5 - access("D"."DEPARTMENT_ID"=:B1)

실행계획과 Predicate Information 이 완전히 같음을 알수 있다. 이제 10053 Trace 를 분석해보자.

***************************

Order-by elimination (OBYE)

***************************

…중간생략

CVM: Merging SPJ view SEL$2 (#0) into SEL$1 (#0)

Registered qb: SEL$F5BB74E1 0xc4643d0 (VIEW MERGE SEL$1; SEL$2)

---------------------

QUERY BLOCK SIGNATURE

---------------------

signature (): qb_name=SEL$F5BB74E1 nbfros=1 flg=0

fro(0): flg=0 objn=70296 hint_alias="E"@"SEL$2"

View Merging 이 발생하여 SEL$2(인라인뷰 a) 가 SEL$1(메인쿼리)에 통합 되어 버렸다. View Merging 이 발생하여 새로운 쿼리블럭인 SEL$F5BB74E1 가 생성 되었다. 하지만 쿼리블럭 SEL$F5BB74E1 의 From 절을 보면 employee(Alias 로는 E) 만 존재하고 department(D)는 존재 하지 않는다. 그렇다면 스칼라 서브쿼리는 어디로 갔을까? 해답은 FPD(Filter Push Down) 기능에 있다.

**************************

Predicate Move-Around (PM)

**************************

…중간생략

query block SEL$F5BB74E1 (#0) unchanged

FPD: Considering simple filter push in query block SEL$F5BB74E1 (#0)

(SELECT "D"."LOCATION_ID" FROM "DEPARTMENT" "D")>0 AND "SYS_ALIAS_1"."JOB_ID"='IT_PROG'

FPD: Considering simple filter push in query block SEL$3 (#0)

"D"."DEPARTMENT_ID"=:B1

try to generate transitive predicate from check constraints for query block SEL$3 (#0)

finally: "D"."DEPARTMENT_ID"=:B1

FPD 기능에 의해서 위에서 새로 생성된 쿼리블럭 SEL$F5BB74E1 의 조건절에 서브쿼리를 생성하고 있다. 또한 새로 생성된 서브쿼리에 "D"."DEPARTMENT_ID"=:B1 조건을 밀어넣고 있다.

검증

스칼라 서브쿼리가 서브쿼리로 바뀌었으므로 서브쿼리에 사용할수 있는 힌트 Push_subq 를 사용해보자. 이것이 가능해야지만 진정한 서브쿼리이다.

SELECT /*+ PUSH_SUBQ(@SUB) */

a.employee_id, a.first_name, a.last_name, a.email

FROM (SELECT e.employee_id, e.first_name, e.last_name, email,

(SELECT /*+ QB_NAME(SUB) */ location_id

FROM department d

WHERE d.department_id = e.department_id) AS location_id

FROM employee e

WHERE e.job_id = 'IT_PROG') a

WHERE a.location_id > 0;

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

|* 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 39 | 2 (0)|

|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 5 | | 1 (0)|

| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 7 | 1 (0)|

|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK1 | 1 | | 0 (0)|

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(>0)

2 - access("E"."JOB_ID"='IT_PROG')

4 - access("D"."DEPARTMENT_ID"=:B1)

실행계획을 보면 알겠지만 Subquery Pushing 이 발생하여 Id 기준으로 2번과 3번이 동일 Level 상에 존재한다. Subquery Pushing 이 성공적으로 수행된것을 알수 있다.

한단계 더 나아가 보자

CREATE INDEX HR.EMP_JOB_DEPT_IX ON HR.EMPLOYEE (JOB_ID, DEPARTMENT_ID);

EMPLOYEE 테이블에 JOB_ID, DEPARTMENT_ID 를 생성 하였다. 바로 위에서 실행한 SQL 을 다시 실행 해보자.

--> 여기서 PUSH_SUBQ 를 적용한 SQL 실행한다.

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 39 | 2 (0)|

|* 2 | INDEX RANGE SCAN | EMP_JOB_DEPT_IX | 1 | | 1 (0)|

| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 7 | 1 (0)|

|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK1 | 1 | | 0 (0)|

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("E"."JOB_ID"='IT_PROG')

filter(>0)

4 - access("D"."DEPARTMENT_ID"=:B1)

이것은 제공자 서브쿼리가 아니다

실행계획은 마치 제공자 서브쿼리처럼 바뀌었지만 이것은 제공자 서브쿼리가 아니고 Subquery Pushing 에 의한 효과이다. Predicate Information 을 주목하라. ID 기준으로 2번에서 Filter 가 수행된다. . (JOB_ID, DEPARTMENT_ID) 인덱스를 사용하지 않은 SQL의 Predicate Information을 보면 EMPLOYEE 테이블 엑세스 시에 filter 가 발생함을 알수 있다. 이것이 바로 Subquery Pushing 과 인덱스에 의한 Early Filter 의 효과이다. 물론 Early Filter 의 개념은 조인순서상에 서브쿼리를 최대한 먼저 조인 하는것으로 바꾸는 것이다. 하지만 위와 같은 Early Filter의 부가적인 기능도 있음을 알아야 한다.

Subquery Pushing 에 의한 Early Filter 기능은 Using Sub query Method( Filter / Access sub Query ) 글을 참조하라

결론:
이처럼 Oracle Query Transformer 는 스칼라 서브쿼리를 Filter 조건으로 사용할 때 SSTS를 발생시킨다.. 여러분이 Query Transformation 을 의도 하던 의도하지 않던 말이다.

PS :
제목이 이상하다는 의견이 있다. 제목은 옵티마이져가 변환을 수행한다는 의미이다. 수동으로 스칼라 서브쿼리를 서브쿼리로 고치라는 의미가 아니므로 착오가 없길 바란다.

'Oracle > Optimizer' 카테고리의 다른 글

Transformer - SQL 튜닝의 새로운 패러다임 (11)	2009.10.15
Temp Table Transformation (5)	2009.09.28
Semi Join 의 재조명 (5)	2009.08.31
Group By 를 먼저 수행하고 Join 하라( Group By Placement ) (4)	2009.08.24
Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down) (9)	2009.08.12

Posted by extremedb

,

오랜만의 책장 정리

DBMS Books 2009. 9. 4. 00:03

오랜만에 책장 정리를 하였다.
그러다가 책장의 한칸에 필독 List 를 만들었다.
나의 경우 필독 List 란 한번 보았지만 반드시 두번이상 다시 보아야 할 List 와 꼭 보아야 하지만 아직 보지 못한것들을 정리한 것 이다.

언제 이것들을 다 보려나?
보면 알겠지만 오른쪽은 모델링 혹은 Architecture 중간은 오라클 왼쪽은 일할때 도움을 주는 도서 가장 왼쪽은 재미있는 것들 순으로 나열해 보았다.

재즈 속으로 : 이 책은 15년전에 구입한 것 이지만 다시 한번 보려 한다.
   여기서 듀크 앨링턴 부터 허비행콕 까지의 여행을 할수 있었다.

로지컬 씽킹 : 제목이 이상하지만 논리적인 생각과 글쓰기의 기법을 알려주는 책이다.
바바라 민토의 논리의 기술과 내용이 같으므로 둘중에 하나만 보면 된다.

The CRM Hand Book : CRM 프로젝트를 하는사람들에겐 필독서라 할수 있다.
IT 관점에서 바라보는 CRM 도서.
   모델링시 도움을 많이 받았다.

Oracle Performace Firefighting : 스티브 아담스의 책 Oracle8i Internal Services for Waits, Latches, Locks,
and Memory 의 Upgrade 버젼 이라고 보면 된다.
저자는 Craig Shallahamer 라는 사람인데 Cary Millsap 과는 친구 사이이다.
열공중....

DW 2.0 : 2.0 의 시대이다. DBA 2.0, WEB 2.0, 오라클 11g 2.0 ....
이책은 DW 모델링 책이 아니라 Architecture 책이다. 아지 보지 못했다.

Physical Database Design : 몇 안되는 물리 모델링 책이다. 아쉬운 점은 IBM DB2 의 관점이 많다는 것.

Data Model Patterns 시리즈 : 자바에만 Pattern 책이 있는건 아니다. 모델링 도 많이 있다.
   바커의 Case Method 를 더욱 빛나게 한 시리즈 이다.
   국내에 제대로 된 모델 Pattern 책이 한권 이라도 있으면 좋으련만...
   언제쯤 Pattern 책이 나오려나?

편집후기 : 혹시 여러분이 정리한 필독 리스트가 있으면 공유 해주기 바란다.

'DBMS Books' 카테고리의 다른 글

성능문제 발견시 분석/ 진단/ 해결방법을 제시하는 책 (6)	2009.07.13
Jonathan Lewis 의 DBMS Book 추천 (0)	2009.02.25
중급 개발자들이 보아야 할 오라클 책 (14)	2008.11.12
Relational Database Index Design and the Optimizers (0)	2008.07.08
오라클 White Paper 에 대하여 (0)	2008.05.22

Posted by extremedb

,

Semi Join 의 재조명

Oracle/Optimizer 2009. 8. 31. 15:45

예전에 Hash Join Right (Semi/Anti/Outer) 의 용도 라는 글에서 서브쿼리가 Semi Join 으로 적용될때 조인의 순서를 바꿀수 있는 방법에 대하여 설명한바 있다. 이어서 오늘도 예전글과 유사한 Semi Join 의 튜닝에 대해서 이야기 하려 한다.

환경 : Oracle 11.1.0.6
먼저 테이블을 단순한 구조로 만들기 위해서 테이블을 2개 생성한다.
ORACLE 의 Sample 스키마인 SH 를 이용한다.

CREATE TABLE SALES_T AS SELECT * FROM sh.sales;

CREATE TABLE PRODUCTS_T AS SELECT * FROM sh.products ;

ALTER TABLE PRODUCTS_T ADD CONSTRAINT PK_PRODUCTS_T --> PK 및 인덱스 생성
PRIMARY KEY (PROD_ID) USING INDEX;

CREATE BITMAP INDEX SALES_T_PROD_BIX ON SALES_T (PROD_ID); --> BIT MAP 인덱스 생성

dbms_stats.gather_table_stats(user,'SALES_T',cascade=>true);
dbms_stats.gather_table_stats(user,'PRODUCTS_T',cascade=>true);

이제 SQL 을 실행할 준비가 다 되었다.
아래는 상품의 Category 가 Software/Other 인 제품의 상품코드별, 채널별 판매량을 나타낸 SQL 이다.
이러한 SQL 은 주로 DW 에서 나타난다.

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) */
s.prod_id, s.channel_id, SUM (quantity_sold) AS qs, SUM (amount_sold) AS amt
   FROM sales_t s
WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
   p.prod_id
   FROM products_t p
WHERE p.prod_category_desc = 'Software/Other')
GROUP BY s.prod_id, s.channel_id ;

--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Time | Buffers |
--------------------------------------------------------------------------
| 1 | HASH GROUP BY    |    |    1 |00:00:08.12 |    4445 |
|* 2 | HASH JOIN |    |    1 |00:00:06.94 |    4445 |
|* 3 |    TABLE ACCESS FULL| PRODUCTS_T |    1 |00:00:00.01 | 4 |
| 4 |    TABLE ACCESS FULL| SALES_T    |    1 |00:00:01.84 |    4441 |
--------------------------------------------------------------------------

헉 8초 이상 걸렸다!
Subquery Unnesting 이 발생하여 작은쪽 테이블인 Products_t 가 Driving 테이블로 선정되었고 Salse_t 테이블과 Hash Join 을 하게된다. Scan 한 블럭수는 4445블럭 이며 8초 이상 수행되었다. 조인건수(Starts 항목 참조)는 1회에 불과하다. 이것은 최적으로 튜닝된 SQL 인가? 이것을 알아보기 위해 Hash Join 대신 Nested Loop 조인으로 바꿔보자.

SELECT /*+ GATHER_PLAN_STATISTICS */
   prod_id, channel_id, SUM (qs) AS qs, SUM (amt) amt
FROM (SELECT /*+ NO_MERGE USE_NL(PR S) */
s.prod_id, s.channel_id,
quantity_sold AS qs, amount_sold AS amt
   FROM sales_t s, products_t pr
   WHERE s.prod_id = pr.prod_id
AND pr.prod_category_desc = 'Photo')
GROUP BY prod_id, channel_id ;

원본 SQL 의 경우 왠만해서는 Nested Loop Join 으로 바뀌지 않으므로 강제로 서브쿼리를 From 절로 끌어 올려서 Nested Loop Join 으로 유도 하였다.

------------------------------------------------------------------------------------
| Id |Operation |Name    |Starts| A-Time |Buffers|
------------------------------------------------------------------------------------
| 1 | HASH GROUP BY    |    |    1 |00:00:10.07 | 3099|
| 2 | VIEW    |    |    1 |00:00:08.92 | 3099|
| 3 | NESTED LOOPS |    |    1 |00:00:07.70 | 3099|
| 4 |    NESTED LOOPS    |    |    1 |00:00:02.03 | 41|
|* 5 | TABLE ACCESS FULL    |PRODUCTS_T    |    1 |00:00:00.01 |    4|
| 6 | BITMAP CONVERSION TO ROWIDS|    | 26 |00:00:00.41 | 37|
|* 7 |    BITMAP INDEX SINGLE VALUE |SALES_T_PROD_BIX| 26 |00:00:00.01 | 37|
| 8 |    TABLE ACCESS BY INDEX ROWID |SALES_T | 405K|00:00:02.45 | 3058|
------------------------------------------------------------------------------------

Hash Join 보다 더 느려졌다
Nested Loop Join 으로 유도 하자 인덱스는 제대로 사용하였지만 비효율이 극심하게 드러난다. 먼저 Nested Loop Join 이 40만번 이상 수행된다.(Starts항목 참조) 그결과로 View 를 만드는데 8초 이상 수행되었다. Buffer 가 30% 이상 줄어들었지만 조인건수 때문에 시간이 많이 걸리므로 이것은 견딜수 없는 결과이다.

그럼 Hash Join 에 만족해야 하나?
지금까지의 상식으로는 그렇다. 하지만 Driving Semi Join 기법을 사용한다면 생각이 달라질것이다.

Driving Semi Join 이란?
Driving Semi Join 이란 Semi Join 의 변형된 형태로서 Hash Join 의 장점(조인건수의 최소화)과 Nested Loop Join 의 장점(선행테이블이 상수화 되어 후행 테이블에서 인덱스를 효율적으로 사용) 을 합친 개념이다. 물론 Driving Semi Join 이라는 Title 자체의 의미대로 Semi Join 이 Driving 집합이 되는것은 당연하다. 다음의 SQL 을 보자

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) SEMIJOIN_DRIVER(@SUB) */
   s.prod_id, s.channel_id, SUM (quantity_sold) AS qs, SUM (amount_sold) AS amt
   FROM sales_t s
WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
p.prod_id
FROM products_t p
   WHERE p.prod_category_desc = 'Photo')
GROUP BY s.prod_id, s.channel_id ;

----------------------------------------------------------------------------------
| Id | Operation |Name    |Starts| A-Time |Buffers|
----------------------------------------------------------------------------------
| 1| HASH GROUP BY    |    | 1|00:00:03.40| 2231|
| 2| TABLE ACCESS BY INDEX ROWID |SALES_T | 1|00:00:02.04| 2231|
| 3|    BITMAP CONVERSION TO ROWIDS|    | 1|00:00:00.42| 63|
| 4| BITMAP MERGE    |    | 1|00:00:00.02| 63|
| 5|    BITMAP KEY ITERATION |    | 1|00:00:00.02| 63|
|* 6| TABLE ACCESS FULL |PRODUCTS_T    | 1|00:00:00.01|    4|
|* 7| BITMAP INDEX RANGE SCAN |SALES_T_PROD_BIX|    26|00:00:00.02| 59|
----------------------------------------------------------------------------------

환상적이다
Hash Join (8초 이상), Nested Loop Join (10초 이상) 인데 반하여 Driving Semi Join 은 3.4초 만에 끝이 났다. 또한 조인횟수도 26 번에 불과하며 I/O Block 수도 2231 로 Hash Join 의 절반에 해당한다.

환상적인 이유는?
그 이유는 Join 대신에 BITMAP KEY ITERATION 이 수행된 까닭이다.
BITMAP KEY ITERATION 의 원리는 INLIST ITERATOR Operation 과 같다고 볼수 있다.
INLIST ITERATOR 의 개념은 InList / Concatnation / Range Scan Control 하기 을 참조하라.

Driving Semi Join은 B-Tree 인덱스에서도 사용이 가능해
이기능은 Bit Map 인덱스 뿐만 아니라 B-Tree 인덱스 에서도 사용가능하다.
하지만 _b_tree_bitmap_plans 파라미터가 True 로 되어 있어야 한다.
아래의 SQL 을 보자.

DROP INDEX SALES_T_PROD_BIX; --> Bit Map 인덱스 Drop

CREATE INDEX SALES_T_PROD_IX ON HR.SALES_T (PROD_ID); --> B-Tree 인덱스 생성

ALTER SESSION SET "_b_tree_bitmap_plans" = TRUE;

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) SEMIJOIN_DRIVER(@SUB) */
   s.prod_id, s.channel_id, SUM (quantity_sold) AS qs,
   SUM (amount_sold) AS amt
FROM sales_t s
WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
p.prod_id
   FROM products_t p
WHERE p.prod_category_desc = 'Photo')
GROUP BY s.prod_id, s.channel_id;

-------------------------------------------------------------------------------------
| Id |Operation    |Name |Starts| A-Time |Buffers|
-------------------------------------------------------------------------------------
| 1| HASH GROUP BY | | 1|00:00:04.94| 3045|
| 2| TABLE ACCESS BY INDEX ROWID    |SALES_T    | 1|00:00:03.76| 3045|
| 3| BITMAP CONVERSION TO ROWIDS | | 1|00:00:02.14|    877|
| 4|    BITMAP MERGE | | 1|00:00:01.73|    877|
| 5| BITMAP KEY ITERATION    | | 1|00:00:01.73|    877|
|* 6|    TABLE ACCESS FULL    |PRODUCTS_T | 1|00:00:00.01|    4|
| 7|    BITMAP CONVERSION FROM ROWIDS| |    26|00:00:01.73|    873|
|* 8| INDEX RANGE SCAN    |SALES_T_PROD_IX|    26|00:00:00.86|    873|
-------------------------------------------------------------------------------------

Bit Map 인덱스에 비하여 약간의 비효율이 있다. 그것은 BITMAP CONVERSION FROM ROWIDS Operation 이 추가되었기 때문이다. 하지만 기존의 Hash Join 이나 Nested Loop Join 보다는 수행시간, I/O Block 수, 조인건수등이 우월함을 알수 있다.

당신은 이미 Driving Semi Join을 사용했다
여러분의 SQL 이 Star Transformation 을 수행하는 경우는 싫든 좋든 Driving Semi Join 기능을 이용하고 있는 것이다. 아래는 Star Transformation 을 수행하는 SQL 의 Plan 과 Outline Data 를 출력한 것이다.

SELECT /*+ gather_plan_statistics */
   p.prod_id,
   c.CHANNEL_ID,
   SUM (quantity_sold) AS qs,
   SUM (amount_sold) AS amt
FROM sh.sales s, sh.CHANNELS c, sh.products p
   WHERE s.CHANNEL_ID = c.CHANNEL_ID
   AND c.CHANNEL_DESC = 'Internet'
   AND s.prod_id = p.prod_id
   AND p.prod_category_desc = 'Photo'
GROUP BY p.prod_id, c.CHANNEL_ID;

------------------------------------------------------------------+
| Id | Operation    | Name |
------------------------------------------------------------------+
| 0 | SELECT STATEMENT |    |
| 1 | HASH GROUP BY |    |
| 2 | HASH JOIN    |    |
| 3 |    TABLE ACCESS FULL | CHANNELS |
| 4 |    HASH JOIN |    |
| 5 | TABLE ACCESS FULL    | PRODUCTS |
| 6 | PARTITION RANGE ALL    |    |
| 7 |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES    |
| 8 | BITMAP CONVERSION TO ROWIDS    |    |
| 9 |    BITMAP AND    |    |
| 10 | BITMAP MERGE |    |
| 11 |    BITMAP KEY ITERATION    |    |
| 12 | BUFFER SORT    |    |
| 13 |    TABLE ACCESS FULL | CHANNELS |
| 14 | BITMAP INDEX RANGE SCAN    | SALES_CHANNEL_BIX|
| 15 | BITMAP MERGE |    |
| 16 |    BITMAP KEY ITERATION    |    |
| 17 | BUFFER SORT    |    |
| 18 |    TABLE ACCESS FULL | PRODUCTS |
| 19 | BITMAP INDEX RANGE SCAN    | SALES_PROD_BIX |
------------------------------------------------------------------+

Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
... 중간생략
STAR_TRANSFORMATION(@"SEL$1" "S"@"SEL$1" SUBQUERIES(("C"@"SEL$1") ("P"@"SEL$1")))
BITMAP_TREE(@"SEL$1" "S"@"SEL$1" AND(("SALES"."CHANNEL_ID") ("SALES"."PROD_ID")))
SEMIJOIN_DRIVER(@"SEL$1898F719")
SEMIJOIN_DRIVER(@"SEL$D750A531")
END_OUTLINE_DATA
*/

Driving Semi Join의 장점은 또 있다
Driving Semi Join Nested Loop Join 시의 조인횟수에 의한 부하나 Hash 조인시의 Full Table Scan + Hashing 작업의 부하를 획기적으로 감소켜준다. 더 좋은것은 Star Transformation 사용시에 바인드 변수를 사용할수 없었지만 Driving Semi Join은 이러한 제약이 없다. 하지만 이 기능은 오라클사의 정식힌트가 아니므로 필자의 경우 데이터 분석 용도로만 사용하고 있다. 이기능이 11.2 버젼에는 정식으로 메뉴얼에 나오길 기대하며 이글을 마칠까 한다.

'Oracle > Optimizer' 카테고리의 다른 글

Temp Table Transformation (5)	2009.09.28
스칼라 서브쿼리를 서브쿼리로 변환하라 (11)	2009.09.10
Group By 를 먼저 수행하고 Join 하라( Group By Placement ) (4)	2009.08.24
Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down) (9)	2009.08.12
11g DBMS_STATS 패키지 성능개선의 3가지 원리 (3)	2009.06.15

Posted by extremedb

,


	오동규 블로그 odong91@naver.com by extremedb

'분류 전체보기'에 해당되는 글 154건

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Modeling' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > TroubleShooting' 카테고리의 다른 글

'Oracle > PL/SQL Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Modeling' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > SQL Pattern' 카테고리의 다른 글

'Oracle > News' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > TroubleShooting' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'DBMS Books' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

카테고리

최근에 올라온 글

최근에 달린 댓글

글 보관함

링크

태그목록

최근에 받은 트랙백

티스토리툴바