비용기반의 오라클 원리 (2009년)
쿼리변환 - 필터링
0
0
56,100
by 구루비스터디 쿼리 변환 Query Transformation LEADING INLINE MATERIALIZE Complex View Merging Pushing Predicates NO_PUSH_PRED [2023.09.23]
II. 필터링
1. 개요
- 옵티마이저가 발전하면서 오라클은 다양한 변환기법들을 사용해서 서브쿼리를 제거하는 쪽으로 많은 진화가 이루어졌기 때문에 필터 연산응 좀처럼 사용되지 않는 추세이지만 이 Operation이 정확히 어떤 작업을 하는것인지는 알아야 하므로 아래에서 이를 확인해보고자 한다.
1) 테스트 테이블 생성
CREATE TABLE PARENT(ID1 NUMBER NOT NULL,
SMALL_VC1 VARCHAR2(10),
SMALL_VC2 VARCHAR2(10),
PADDING VARCHAR2(200),
CONSTRAINT PAR_PK PRIMARY KEY(ID1));
CREATE TABLE CHILD(ID1 NUMBER NOT NULL,
ID2 NUMBER NOT NULL,
SMALL_VC1 VARCHAR2(10),
SMALL_VC2 VARCHAR2(10),
PADDING VARCHAR2(200),
CONSTRAINT CHI_PK PRIMARY KEY (ID1,ID2));
CREATE TABLE SUBTEST (ID1 NUMBER NOT NULL,
SMALL_VC1 VARCHAR2(10),
SMALL_VC2 VARCHAR2(10),
PADDING VARCHAR2(200),
CONSTRAINT SUB_PK PRIMARY KEY(ID1));
INSERT INTO PARENT
SELECT ROWNUM,
TO_CHAR(ROWNUM),
TO_CHAR(ROWNUM),
RPAD(TO_CHAR(ROWNUM), 100)
FROM ALL_OBJECTS
WHERE ROWNUM <= 3000;
COMMIT;
BEGIN
FOR I IN 1..8 LOOP
INSERT INTO CHILD
SELECT ROWNUM,
I,
TO_CHAR(ROWNUM),
TO_CHAR(ROWNUM),
RPAD(TO_CHAR(ROWNUM), 100)
FROM PARENT;
COMMIT;
END LOOP;
END;
/
INSERT INTO SUBTEST
SELECT * FROM PARENT;
COMMIT;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,
'PARENT',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,
'CHILD',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,
'SUBTEST',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/
2) 8i 테스트
- 부모-자식 관계를 갖는 두 테이블을 조인하는 쿼리문
- 각 부모 레코드가 관련된 여덟 개의 자식 레코드를 갖도록 데이터를 구성함
- 부모 테이블이 가진 값을 근거로 데이터 일부를 제거할 목적으로 서브쿼리를 사용
- 액세스 순서가 PAR -> SUB -> CHI와 PAR -> CHI -> SUB일 경우 어떤 차이가 나는지 체크
-- 8i version. subquery postponed
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '8.1.7';
ALTER SESSION SET STATISTICS_LEVEL = ALL ;
SELECT PAR.SMALL_VC1,
CHI.SMALL_VC1
FROM PARENT PAR,
CHILD CHI
WHERE CHI.ID1 = PAR.ID1
AND PAR.ID1 BETWEEN 100
AND 200
AND EXISTS (SELECT NULL
FROM SUBTEST SUB
WHERE SUB.SMALL_VC1 = PAR.SMALL_VC1
AND SUB.ID1 = PAR.ID1
AND SUB.SMALL_VC2 >= '2');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('40t7w69g69dj7', NULL, 'ALLSTATS COST LAST'));
----------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------
|* 1 | FILTER | | 8 |00:00:00.01 | 1224 |
| 2 | TABLE ACCESS BY INDEX ROWID | CHILD | 808 |00:00:00.01 | 921 |
| 3 | NESTED LOOPS | | 910 |00:00:00.01 | 113 |
| 4 | TABLE ACCESS BY INDEX ROWID| PARENT | 101 |00:00:00.01 | 6 |
|* 5 | INDEX RANGE SCAN | PAR_PK | 101 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | CHI_PK | 808 |00:00:00.01 | 107 |
|* 7 | TABLE ACCESS BY INDEX ROWID | SUBTEST | 1 |00:00:00.01 | 303 |
|* 8 | INDEX UNIQUE SCAN | SUB_PK | 101 |00:00:00.01 | 202 |
----------------------------------------------------------------------------------
-- 8i version. early subquery
-- PUSH_SUBQ, NO_UNNEST 힌트를 사용해도 PAR -> SUB -> CHI 액세스 순서로 유도가
-- 안되서 ROWNUM 방법을 사용함
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '8.1.7';
ALTER SESSION SET STATISTICS_LEVEL = ALL ;
SELECT TEMP.SMALL_VC1,
CHI.SMALL_VC1
FROM (SELECT /*+ PUSH_SUBQ */
PAR.SMALL_VC1,
PAR.ID1,
ROWNUM
FROM PARENT PAR
WHERE PAR.ID1 BETWEEN 100
AND 200
AND EXISTS (SELECT /*+ NO_UNNEST */
NULL
FROM SUBTEST SUB
WHERE SUB.SMALL_VC1 = PAR.SMALL_VC1
AND SUB.ID1 = PAR.ID1
AND SUB.SMALL_VC2 >= '2')) TEMP,
CHILD CHI
WHERE TEMP.ID1 = CHI.ID1
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('8qd5asmzzzytm', NULL, 'ALLSTATS COST LAST'));
------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID | CHILD | 8 |00:00:00.01 | 320 |
| 2 | NESTED LOOPS | | 10 |00:00:00.02 | 312 |
| 3 | VIEW | | 1 |00:00:00.01 | 309 |
| 4 | COUNT | | 1 |00:00:00.01 | 309 |
|* 5 | FILTER | | 1 |00:00:00.01 | 309 |
| 6 | TABLE ACCESS BY INDEX ROWID| PARENT | 101 |00:00:00.01 | 6 |
|* 7 | INDEX RANGE SCAN | PAR_PK | 101 |00:00:00.01 | 3 |
|* 8 | TABLE ACCESS BY INDEX ROWID| SUBTEST | 1 |00:00:00.01 | 303 |
|* 9 | INDEX UNIQUE SCAN | SUB_PK | 101 |00:00:00.01 | 202 |
|* 10 | INDEX RANGE SCAN | CHI_PK | 8 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------
;
- 'subquery postponed'에서는 'Buffers' 값이 1,224이나 'early subquery'에서는 320으로 줄어든것을 확인할 수 있음.
3) 10g 테스트
-- 10g version. subquery postponed
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.3';
ALTER SESSION SET STATISTICS_LEVEL = ALL ;
SELECT /*+ LEADING(PAR CHI) USE_NL(PAR CHI) */
PAR.SMALL_VC1,
CHI.SMALL_VC1
FROM PARENT PAR,
CHILD CHI
WHERE CHI.ID1 = PAR.ID1
AND PAR.ID1 BETWEEN 100
AND 200
AND EXISTS (SELECT /*+ NO_UNNEST */
NULL
FROM SUBTEST SUB
WHERE SUB.SMALL_VC1 = PAR.SMALL_VC1
AND SUB.ID1 = PAR.ID1
AND SUB.SMALL_VC2 >= '2');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1h7kqmbwjcf2s', NULL, 'ALLSTATS COST LAST'));
----------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------
|* 1 | FILTER | | 8 |00:00:00.01 | 1224 |
| 2 | TABLE ACCESS BY INDEX ROWID | CHILD | 808 |00:00:00.01 | 921 |
| 3 | NESTED LOOPS | | 910 |00:00:00.01 | 113 |
| 4 | TABLE ACCESS BY INDEX ROWID| PARENT | 101 |00:00:00.01 | 6 |
|* 5 | INDEX RANGE SCAN | PAR_PK | 101 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | CHI_PK | 808 |00:00:00.01 | 107 |
|* 7 | TABLE ACCESS BY INDEX ROWID | SUBTEST | 1 |00:00:00.01 | 303 |
|* 8 | INDEX UNIQUE SCAN | SUB_PK | 101 |00:00:00.01 | 202 |
----------------------------------------------------------------------------------
-- 10g version. early subquery
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.3';
ALTER SESSION SET STATISTICS_LEVEL = ALL ;
SELECT /*+ LEADING(PAR) */
PAR.SMALL_VC1,
CHI.SMALL_VC1
FROM PARENT PAR,
CHILD CHI
WHERE CHI.ID1 = PAR.ID1
AND PAR.ID1 BETWEEN 100
AND 200
AND EXISTS (SELECT /*+ USE_NL(SUB) */
NULL
FROM SUBTEST SUB
WHERE SUB.SMALL_VC1 = PAR.SMALL_VC1
AND SUB.ID1 = PAR.ID1
AND SUB.SMALL_VC2 >= '2');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('4wv1jvg8nra6v', NULL, 'ALLSTATS COST LAST'));
----------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID | CHILD | 8 |00:00:00.01 | 221 |
| 2 | NESTED LOOPS | | 10 |00:00:00.01 | 213 |
| 3 | NESTED LOOPS SEMI | | 1 |00:00:00.01 | 210 |
| 4 | TABLE ACCESS BY INDEX ROWID| PARENT | 101 |00:00:00.01 | 6 |
|* 5 | INDEX RANGE SCAN | PAR_PK | 101 |00:00:00.01 | 3 |
|* 6 | TABLE ACCESS BY INDEX ROWID| SUBTEST | 1 |00:00:00.01 | 204 |
|* 7 | INDEX UNIQUE SCAN | SUB_PK | 101 |00:00:00.01 | 103 |
|* 8 | INDEX RANGE SCAN | CHI_PK | 8 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------
;
- 서브쿼리를 NO_UNNEST 힌트처럼 Filter가 아닌 UNNEST의 조인 형태로 처리함.
2. 스칼라 서브쿼리
- 스칼라 서브쿼리를 사용할때도 FILTER 실행계획이 나올 수 있다.
1) 테스트 데이터 생성
DROP TABLE EMP;
DROP TABLE GENERATOR;
CREATE TABLE GENERATOR AS
SELECT ROWNUM ID
FROM ALL_OBJECTS
WHERE ROWNUM <= 1000;
CREATE TABLE EMP(DEPT_NO NOT NULL,
SAL,
EMP_NO NOT NULL,
PADDING,
CONSTRAINT E_PK PRIMARY KEY(EMP_NO)
)
AS
SELECT /*+ ORDERED USE_NL(V2) */
MOD(ROWNUM, 6),
ROWNUM,
ROWNUM,
RPAD('X', 60)
FROM GENERATOR V1,
GENERATOR V2
WHERE ROWNUM <= 20000
;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'EMP',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
2) 스칼라 서브쿼리 FILTER 실행계획 확인
EXPLAIN PLAN FOR
SELECT COUNT(AV_SAL)
FROM (SELECT /*+ NO_MERGE */
OUTER.DEPT_NO,
OUTER.SAL,
OUTER.EMP_NO,
OUTER.PADDING,
(SELECT AVG(INNER.SAL)
FROM EMP
INNER WHERE INNER.DEPT_NO = OUTER.DEPT_NO) AV_SAL
FROM EMP OUTER)
WHERE SAL > AV_SAL
;
@XPLAN;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 129 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | VIEW | | 20000 | 507K| 129 (1)| 00:00:02 |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS FULL | EMP | 20000 | 156K| 65 (2)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 8 | | |
|* 6 | TABLE ACCESS FULL| EMP | 3333 | 26664 | 65 (2)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OUTER"."SAL"> (SELECT AVG("INNER"."SAL") FROM "EMP"
"INNER" WHERE "INNER"."DEPT_NO"=:B1))
6 - filter("INNER"."DEPT_NO"=:B1)
- 실행계획을 보면 인라인 뷰 안의 스칼라 서브쿼리 AV_SAL 컬럼을 인라인 뷰 밖에서 조건으로 사용을 하고 있기 때문에 실행계획에서 FILTER 부분에서 확인을 할 수 있다.
- 만약 스칼라 서브쿼리 부분이 조건에 활용되지 않는다면 이 부분은 실행계획에서 확인하기 힘들것이다.
EXPLAIN PLAN FOR
SELECT COUNT(AV_SAL)
FROM (SELECT /*+ NO_MERGE */
OUTER.DEPT_NO,
OUTER.SAL,
OUTER.EMP_NO,
OUTER.PADDING,
(SELECT AVG(INNER.SAL)
FROM EMP
INNER WHERE INNER.DEPT_NO = OUTER.DEPT_NO) AV_SAL
FROM EMP OUTER)
--WHERE SAL > AV_SAL
;
@XPLAN;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 65 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 20000 | 253K| 65 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 20000 | 60000 | 65 (2)| 00:00:01 |
----------------------------------------------------------------------------
- 이렇게 스칼라 서브쿼리가 실행계획에서 보여야 함에도 불구하고 안보이는 이유는 스칼라 서브쿼리 집합 밖에서 그룹함수를 사용하였기 때문이다.
- 일반적으로 View Depth가 깊거나 View 밖에서 그룹함수를 사용할경우 View 안의 스칼라 서브쿼리는 가려서 안보이게 된다.
-- 인라인 뷰 안의 내용만 확인할 경우
EXPLAIN PLAN FOR
SELECT /*+ NO_MERGE */
OUTER.DEPT_NO,
OUTER.SAL,
OUTER.EMP_NO,
OUTER.PADDING,
(SELECT AVG(INNER.SAL)
FROM EMP
INNER WHERE INNER.DEPT_NO = OUTER.DEPT_NO) AV_SAL
FROM EMP OUTER
;
@XPLAN;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 1406K| 65 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| EMP | 3333 | 26664 | 65 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 20000 | 1406K| 65 (2)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("INNER"."DEPT_NO"=:B1)
3. 서브쿼리 팩토링
- 서브쿼리 팩토링이란 9i부터 제공된 구문으로 WITH절을 사용하여 SQL을 사용할 때 집합을 미리 만드는 방법을 일컷는다.
- WITH 절을 쉽게 이해한다면 인라인 뷰의 내용을 WITH절에 선언하고 SELECT 절에서는 테이블처럼 사용한다고 보면 된다.
- 이 방법에는 2가지 종류가 있는데 다음과 같다.
1) WITH절에 /*+ INLINE */ 힌트를 사용
- 위에서 설명한 것처럼 WITH절에 선언한 내용을 인라인 뷰처럼 사용하고자 할때 선언
- 일반적으로 이 힌트를 사용하지 않아도 Default로 사용됨
2) WITH절에 /*+ MATERIALIZE */ 힌트를 사용
- WITH절에서 선언한 집합을 TEMP TABLE을 사용하도록 강제하는 방법
- 이를 사용하는 이유는 WITH절의 내용과 SELECT절 이하 FROM절의 테이블 사이에 View Merging이 일어나지 않도록 할 때 주로 사용한다.
1) /*+ INLINE */ 힌트 사용 예제
DROP TABLE T1;
CREATE TABLE T1
AS
SELECT *
FROM ALL_OBJECTS
WHERE ROWNUM <= 3000
;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'T1',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
EXPLAIN PLAN FOR
WITH GENERATOR AS
(
SELECT /*+ INLINE */
ROWNUM ID
FROM T1
WHERE ROWNUM <= 1000
)
SELECT /*+ ORDERED USE_NL(V2) */
MOD(ROWNUM, 6),
ROWNUM,
ROWNUM,
RPAD('X', 60)
FROM GENERATOR V1,
GENERATOR V2
WHERE ROWNUM <= 20000
;
@XPLAN;
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 262 (1)| 00:00:04 |
|* 1 | COUNT STOPKEY | | | | |
| 2 | NESTED LOOPS | | 20000 | 262 (1)| 00:00:04 |
| 3 | VIEW | | 20 | 1 (0)| 00:00:01 |
|* 4 | COUNT STOPKEY | | | | |
| 5 | TABLE ACCESS FULL| T1 | 3000 | 13 (0)| 00:00:01 |
| 6 | VIEW | | 1000 | 13 (0)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | |
| 8 | TABLE ACCESS FULL| T1 | 3000 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20000)
4 - filter(ROWNUM<=1000)
7 - filter(ROWNUM<=1000)
2) /*+ MATERIALIZE */ 힌트 사용 예제
DROP TABLE T1;
CREATE TABLE T1
AS
SELECT *
FROM ALL_OBJECTS
WHERE ROWNUM <= 3000
;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'T1',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
EXPLAIN PLAN FOR
WITH GENERATOR AS
(
SELECT /*+ MATERIALIZE */
ROWNUM ID
FROM T1
WHERE ROWNUM <= 1000
)
SELECT /*+ ORDERED USE_NL(V2) */
MOD(ROWNUM, 6),
ROWNUM,
ROWNUM,
RPAD('X', 60)
FROM GENERATOR V1,
GENERATOR V2
WHERE ROWNUM <= 20000
;
@XPLAN;
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | | 2030 (1)| 00:00:25 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | TABLE ACCESS FULL | T1 | 3000 | | 13 (0)| 00:00:01 |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | NESTED LOOPS | | 1000K| | 2017 (1)| 00:00:25 |
| 7 | VIEW | | 1000 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6602_EC10A807 | 1000 | 13000 | 2 (0)| 00:00:01 |
| 9 | VIEW | | 1000 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6602_EC10A807 | 1000 | 13000 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=1000)
5 - filter(ROWNUM<=20000)
4. Complex View Merging
- 그룹 함수가 포함된 뷰 또는 인라인 뷰와 그룹 함수가 없는 뷰 또는 인라인 뷰 각각 1개가 조인할 때 이를 Complex View Merging이라고 한다.
- 만약 이 조인이 될 경우 아래의 2 경우를 생각해 볼 수 있다.
- 1) 그룹함수가 있는 집합을 먼저 생성하고 그룹함수가 없는 집합과 조인을 함.
- 2) 그룹함수가 있는 집합 안의 내용과 그룹함수가 없는 집합을 먼저 조인하게 한 후 그 뒤에 그룹함수를 사용하는 방법
1) 그룹함수 집합 먼저 생성 -> 조인
DROP TABLE T;
CREATE TABLE T AS
SELECT MOD(LEVEL, 10) GUBN,
LEVEL VAL
FROM DUAL
CONNECT BY LEVEL <= 1000;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'T',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
EXPLAIN PLAN FOR
SELECT *
FROM (SELECT GUBN, SUM(VAL)
FROM T
GROUP BY GUBN) T_1,
T T_2
WHERE T_1.GUBN = T_2.GUBN
;
@XPLAN;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 32000 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 32000 | 8 (25)| 00:00:01 |
| 2 | VIEW | | 10 | 260 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 10 | 60 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T | 1000 | 6000 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T | 1000 | 6000 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T_1"."GUBN"="T_2"."GUBN")
- 실행계획에서 보는 것처럼 인라인 뷰 T_1을 먼저 액세스한 뒤 'HASH GROUP BY'를 통해 먼저 집합을 만들과 그 뒤에 T_2 테이블을 HASH JOIN 한 것을 볼 수 있다.
2) 조인 -> 그룹함수
EXPLAIN PLAN FOR
SELECT /*+ MERGE(T_1) */
*
FROM (SELECT GUBN, SUM(VAL)
FROM T
GROUP BY GUBN) T_1,
T T_2
WHERE T_1.GUBN = T_2.GUBN
;
@XPLAN;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 585K| | 323 (4)| 00:00:04 |
| 1 | HASH GROUP BY | | 50000 | 585K| 3552K| 323 (4)| 00:00:04 |
|* 2 | HASH JOIN | | 100K| 1171K| | 8 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T | 1000 | 6000 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T | 1000 | 6000 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GUBN"="T_2"."GUBN")
- 이번에는 MERGE 힌트를 사용하여 2개 테이블을 먼저 HASH JOIN하게 한 뒤 그 다음 HASH GROUP BY가 된 것을 확인할 수 있다.
- 일반적으로 2개 이상의 집합이 조인될 때 1개까지는 그룹함수가 있는 집합이 있을 경우 'Complex View Merging'이 가능하지만 그룹함수가 2개 이상일 경우는 'Complex View Merging'이 안되는 경우가 대부분이다.
- 그 이유는 옵티마이저가 2개 이상인 집합에 대해 먼저 조인을 하고 이후에 그룹함수를 사용할 경우 데이터를 보존할 가능성이 희박하다고 보기 때문에 먼저 집합을 보존하고자 'Complex View Merging'을 대부분 하지 않는다.
3) 그룹함수가 2개 이상일 경우
EXPLAIN PLAN FOR
SELECT /*+ MERGE(T_1) MERGE(T_2) */
*
FROM (SELECT GUBN, SUM(VAL)
FROM T
GROUP BY GUBN) T_1,
(SELECT GUBN, SUM(VAL)
FROM T
GROUP BY GUBN) T_2,
T T_3
WHERE T_1.GUBN = T_2.GUBN
AND T_1.GUBN = T_3.GUBN
;
@XPLAN;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 58000 | 12 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 58000 | 12 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 10 | 520 | 9 (34)| 00:00:01 |
| 3 | VIEW | | 10 | 260 | 4 (25)| 00:00:01 |
| 4 | HASH GROUP BY | | 10 | 60 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T | 1000 | 6000 | 3 (0)| 00:00:01 |
| 6 | VIEW | | 10 | 260 | 4 (25)| 00:00:01 |
| 7 | HASH GROUP BY | | 10 | 60 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL| T | 1000 | 6000 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T | 1000 | 6000 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T_1"."GUBN"="T_3"."GUBN")
2 - access("T_1"."GUBN"="T_2"."GUBN")
- 결국 이를 해결하기 위해서는 가급적 그룹함수가 있는 집합이 1개만 유지되도록 해야 한다는 것을 의미하며 2개 이상일 경우 다른 그룹함수 집합을 스칼라 서브쿼리나 펑션으로 대체해야 한다.
5. Pushing Predicates
- 이 Operation은 그룹함수가 없는 뷰 또는 인라인 뷰와 다른 집합 사이에 조인을 하게 될 ? 다른 집합에서 뷰 또는 인라인 뷰로 조건이 침투가 되도록 하는 방법이다.
- 만약 당신이 특정 SQL을 튜닝하게 된다면 이 Operation을 유도하느냐 못하느냐에 따라 수행속도가 아주 많이 차이나는 것을 확인할 수 있을 것이다.
1) 테스트 데이터 생성
DROP TABLE T1;
DROP TABLE T2;
DROP TABLE T3;
CREATE TABLE T1 AS
SELECT ROWNUM - 1 ID1,
TRUNC((ROWNUM - 1) / 10) N1,
LPAD(ROWNUM, 10, '0') SMALL_VC,
RPAD('X', 100) PADDING
FROM ALL_OBJECTS
WHERE ROWNUM <= 5000;
ALTER TABLE T1 ADD CONSTRAINT T1_PK PRIMARY KEY(ID1);
CREATE TABLE T2 AS
SELECT TRUNC((ROWNUM - 1) / 5) ID1,
ROWNUM ID2,
LPAD(ROWNUM, 10, '0') SMALL_VC,
RPAD('X', 100) PADDING
FROM ALL_OBJECTS
WHERE ROWNUM <= 25000;
ALTER TABLE T2 ADD CONSTRAINT T2_PK PRIMARY KEY(ID1, ID2);
CREATE TABLE T3 AS
SELECT TRUNC((ROWNUM - 1) / 5) ID1,
ROWNUM ID2,
LPAD(ROWNUM, 10, '0') SMALL_VC,
RPAD('X', 100) PADDING
FROM ALL_OBJECTS
WHERE ROWNUM <= 25000;
ALTER TABLE T3 ADD CONSTRAINT T3_PK PRIMARY KEY(ID1, ID2);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME =>'T1',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME =>'T2',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME =>'T3',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/
CREATE OR REPLACE VIEW V1 AS
SELECT T2.ID1,
T2.ID2,
T3.SMALL_VC,
T3.PADDING
FROM T2,
T3
WHERE T3.ID1 = T2.ID1
AND T3.ID2 = T2.ID2
;
2) 조건 침투가 안될 경우
ALTER SESSION SET STATISTICS_LEVEL = ALL ;
SELECT /*+ NO_PUSH_PRED(V1) */
T1.*,
V1.*
FROM T1,
V1
WHERE V1.ID1(+) = T1.ID1
AND T1.N1 = 5
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('2hswhprr2pp84', NULL, 'ALLSTATS LAST'));
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN OUTER | | 1 | 50 | 50 |00:00:00.30 | 25545 | 752K| 752K| 1083K (0)|
|* 2 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 |00:00:00.01 | 91 | | | |
| 3 | VIEW | V1 | 1 | 25000 | 25000 |00:00:00.25 | 25454 | | | |
| 4 | NESTED LOOPS | | 1 | 25000 | 25000 |00:00:00.23 | 25454 | | | |
| 5 | TABLE ACCESS FULL| T3 | 1 | 25000 | 25000 |00:00:00.03 | 448 | | | |
|* 6 | INDEX UNIQUE SCAN| T2_PK | 25000 | 1 | 25000 |00:00:00.15 | 25006 | | | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("V1"."ID1"="T1"."ID1")
- /*+ NO_PUSH_PRED(V1) */ 힌트를 사용하여 V1 뷰에 조건이 침투가 안되도록 유도하였다.
- 위에서 보는 것처럼 조건이 침투가 안되서 T3 테이블을 25000건 액세스 한것을 확인할 수 있다.
3) 조건 침투가 될 경우
SELECT T1.*,
V1.*
FROM T1,
V1
WHERE V1.ID1(+) = T1.ID1
AND T1.N1 = 5
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gh71wnftc6m85', NULL, 'ALLSTATS LAST'));
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS OUTER | | 1 | 50 | 50 |00:00:00.01 | 182 |
|* 2 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 |00:00:00.01 | 95 |
| 3 | VIEW PUSHED PREDICATE | V1 | 10 | 1 | 50 |00:00:00.01 | 87 |
| 4 | NESTED LOOPS | | 10 | 1 | 50 |00:00:00.01 | 87 |
| 5 | TABLE ACCESS BY INDEX ROWID| T3 | 10 | 5 | 50 |00:00:00.01 | 32 |
|* 6 | INDEX RANGE SCAN | T3_PK | 10 | 5 | 50 |00:00:00.01 | 18 |
|* 7 | INDEX UNIQUE SCAN | T2_PK | 50 | 1 | 50 |00:00:00.01 | 55 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N1"=5)
6 - access("T3"."ID1"="T1"."ID1")
7 - access("T2"."ID1"="T1"."ID1" AND "T3"."ID2"="T2"."ID2")
filter("T3"."ID1"="T2"."ID1")
- 이번에는 힌트를 제거하여 V1 뷰에 조건이 침투가 되도록 유도하였고 이로 인해 T3 테이블을 50건만 액세스하는 것을 확인할 수 있다.
"코어 오라클 데이터베이스 스터디모임" 에서 2009년에 "비용기반의 오라클 원리
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4422
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.