꿈꾸는 개발자, DBA 커뮤니티 구루비

3.8 CVM (Complex View Merging) : Distinct나 Group By가 있는 뷰를 해체하라

CVM

Huristic Query Transformation의 SVM과 대응되는 용어.

SVM : Group BY 혹은 Distinct가 없는 뷰를 해체
CVM : Group BY 나 Distinct가 있는 뷰 혹은 Inline View를 해체.

CVM 예제
{code:sql	borderStyle=solid} SELECT /+ GATHER_PLAN_STATISTICS QB_NAME(outer) / e.* FROM employee e, (SELECT /+ QB_NAME(inner) NO_PUSH_PRED / --> JPPD를 방지하기 위해 힌트 사용함 department_id, AVG (salary) salary FROM employee GROUP BY department_id) avg WHERE e.department_id = avg.department_id AND e.salary >= avg.salary AND e.job_id = 'HR_REP' ;

+

---+

Id

Operation

Name

Rows

Bytes

Cost

Time

+

---+

0	SELECT STATEMENT				4
1	FILTER
2	HASH GROUP BY		1	84	4	00:00:01
3	NESTED LOOPS
4	NESTED LOOPS		31	2604	3	00:00:01
5	TABLE ACCESS BY INDEX ROWID	EMPLOYEE	1	68	2	00:00:01
6	INDEX RANGE SCAN	EMP_JOB_IX	1		1	00:00:01
7	INDEX RANGE SCAN	EMP_DEPARTMENT_IX	10		0
8	TABLE ACCESS BY INDEX ROWID	EMPLOYEE	31	496	1	00:00:01

+

---+
Predicate Information:

--
1 - filter("E"."SALARY">=AVG("SALARY"))
6 - access("E"."JOB_ID"='HR_REP')
7 - access("E"."DEPARTMENT_ID"="DEPARTMENT_ID")

Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
DB_VERSION('11.1.0.6')
ALL_ROWS
OUTLINE_LEAF(@"SEL$87B6C357")
MERGE(@"INNER") <

-
OUTLINE(@"OUTER")
OUTLINE(@"INNER")
INDEX_RS_ASC(@"SEL$87B6C357" "E"@"OUTER" ("EMPLOYEE"."JOB_ID"))
INDEX(@"SEL$87B6C357" "EMPLOYEE"@"INNER" ("EMPLOYEE"."DEPARTMENT_ID"))
LEADING(@"SEL$87B6C357" "E"@"OUTER" "EMPLOYEE"@"INNER")
USE_NL(@"SEL$87B6C357" "EMPLOYEE"@"INNER")
NLJ_BATCHING(@"SEL$87B6C357" "EMPLOYEE"@"INNER")
USE_HASH_AGGREGATION(@"SEL$87B6C357")
END_OUTLINE_DATA
*/

|
||CVM 예제 - QT가 바꾼 쿼리||
|{code:sql|borderStyle=solid}
SELECT   e.employee_id , e.first_name ,
         e.last_name , e.email , e.phone_number ,
         e.hire_date , e.job_id , e.salary ,
         e.commission_pct , e.manager_id ,
         e.department_id 
    FROM employee e, employee employee
   WHERE e.department_id = employee.department_id 
     AND e.job_id = 'HR_REP'
GROUP BY employee.department_id,
         e.ROWID, e.employee_id , e.first_name ,
         e.last_name , e.email , e.phone_number ,
         e.hire_date , e.job_id , e.salary ,
         e.commission_pct , e.manager_id ,
         e.department_id 
HAVING e.salary >= AVG (employee.salary) ;

-----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |     1 |    85 |     4  (25)| 00:00:01 |
|*  1 |  FILTER                         |                   |       |       |            |          |
|   2 |   HASH GROUP BY                 |                   |     1 |    85 |     4  (25)| 00:00:01 |
|   3 |    NESTED LOOPS                 |                   |       |       |            |          |
|   4 |     NESTED LOOPS                |                   |    31 |  2635 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |     1 |    69 |     2   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | EMP_JOB_IX        |     1 |       |     1   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |    31 |   496 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   1 - filter("E"."SALARY">=AVG("EMPLOYEE"."SALARY"))
   6 - access("E"."JOB_ID"='HR_REP')
   7 - access("E"."DEPARTMENT_ID"="EMPLOYEE"."DEPARTMENT_ID")


-- 아래처럼 SQL을 재작성하면 더 효과적이지 않을까??
SELECT   /*+ GATHER_PLAN_STATISTICS */ 
         e.employee_id , min(e.first_name) first_name,
         min(e.last_name) last_name, min(e.email) email, min(e.phone_number) phone_number,
         min(e.hire_date) hire_date, min(e.job_id) job_id, min(e.salary) salary,
         min(e.commission_pct) commission_pct, min(e.manager_id) manager_id,
         min(e.department_id) department_id
    FROM employee e, employee employee
   WHERE e.department_id = employee.department_id 
     AND e.job_id = 'HR_REP'
GROUP BY employee.department_id, e.employee_id
HAVING min(e.salary) >= AVG (employee.salary) ;

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |      1 |        |      1 |00:00:00.01 |       4 |       |       |          |
|*  1 |  FILTER                         |                   |      1 |        |      1 |00:00:00.01 |       4 |       |       |          |
|   2 |   HASH GROUP BY                 |                   |      1 |      1 |      1 |00:00:00.01 |       4 |   712K|   712K|  479K (0)|
|   3 |    NESTED LOOPS                 |                   |      1 |        |      1 |00:00:00.01 |       4 |       |       |          |
|   4 |     NESTED LOOPS                |                   |      1 |     31 |      1 |00:00:00.01 |       3 |       |       |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | EMP_JOB_IX        |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|*  7 |      INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      1 |     10 |      1 |00:00:00.01 |       1 |       |       |          |
|   8 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |      1 |     31 |      1 |00:00:00.01 |       1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(MIN("E"."SALARY")>=SUM("EMPLOYEE"."SALARY")/COUNT("EMPLOYEE"."SALARY"))
   6 - access("E"."JOB_ID"='HR_REP')
   7 - access("E"."DEPARTMENT_ID"="EMPLOYEE"."DEPARTMENT_ID")

--> 일량(buffers)은 같고, pga 사용량(Used-Mem)도 거의 동일

|

CVM 예제 - 10053 Trace

{code:sql}
*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in query block OUTER (#1) that are valid to merge.
CVM: Checking validity of merging in query block INNER (#2)
CNT: Considering count(col) to count(*) on query block INNER (#2)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: COUNT() to COUNT(*) not done.
CVM: CBQT Marking query block INNER (#2) as valid for CVM.
CVM: Not Merging INNER (#2) into OUTER (#1) due to CBQT directive.
Cost-based complex view merging on query block OUTER (#1) <--
CVM: Using search type: linear <--
CVM: Considering view merging on query block OUTER (#1)
CVM: Starting iteration 1, state space = (2) : (0) <-- cvm이 발생하지 않는 경우를 costing
CVM: Considering view merge (candidate phase) in query block OUTER (#1)
CVM: Considering view merge (candidate phase) in query block INNER (#2)
CNT: Considering count(col) to count(*) on query block INNER (#2)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: COUNT() to COUNT(*) not done.
CVM: CBQT Marking query block INNER (#2) as valid for CVM.
CVM: Not Merging INNER (#2) into OUTER (#1) due to CBQT directive.
FPD: Considering simple filter push in query block OUTER (#1)
"E"."DEPARTMENT_ID"="AVG"."DEPARTMENT_ID" AND "E"."SALARY">="AVG"."SALARY" AND "E"."JOB_ID"='HR_REP'
try to generate transitive predicate from check constraints for query block OUTER (#1)
finally: "E"."DEPARTMENT_ID"="AVG"."DEPARTMENT_ID" AND "E"."SALARY">="AVG"."SALARY" AND "E"."JOB_ID"='HR_REP' <--

......
CVM: Updated best state, Cost = 6.51 <-- cost 6.51
......

CVM: Starting iteration 2, state space = (2) : (1) <--
CVM: Considering view merge (candidate phase) in query block OUTER (#1)
CVM: Considering view merge (candidate phase) in query block INNER (#2)
CNT: Considering count(col) to count(*) on query block INNER (#2)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: COUNT() to COUNT(*) not done.
CVM: CBQT Marking query block INNER (#2) as valid for CVM.
CVM: Merging complex view INNER (#2) into OUTER (#1).
qbcp:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("OUTER") / "E"."EMPLOYEE_ID" "EMPLOYEE_ID","E"."FIRST_NAME" "FIRST_NAME","E"."LAST_NAME" "LAST_NAME","E"."EMAIL" "EMAIL","E"."PHONE_NUMBER" "PHONE_NUMBER","E"."HIRE_DATE" "HIRE_DATE","E"."JOB_ID" "JOB_ID","E"."SALARY" "SALARY","E"."COMMISSION_PCT" "COMMISSION_PCT","E"."MANAGER_ID" "MANAGER_ID","E"."DEPARTMENT_ID" "DEPARTMENT_ID" FROM "TLO"."EMPLOYEE" "E", (SELECT /+ QB_NAME ("INNER") */ "EMPLOYEE"."DEPARTMENT_ID" "DEPARTMENT_ID",AVG("EMPLOYEE"."SALARY") "SALARY" FROM "TLO"."EMPLOYEE" "EMPLOYEE" GROUP BY "EMPLOYEE"."DEPARTMENT_ID") "AVG" WHERE "E"."DEPARTMENT_ID"="AVG"."DEPARTMENT_ID" AND "E"."SALARY">="AVG"."SALARY" AND "E"."JOB_ID"='HR_REP'
vqbcp:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("INNER") */ "EMPLOYEE"."DEPARTMENT_ID" "DEPARTMENT_ID",AVG("EMPLOYEE"."SALARY") "SALARY" FROM "TLO"."EMPLOYEE" "EMPLOYEE" GROUP BY "EMPLOYEE"."DEPARTMENT_ID"
CVM: result OUTER (#1)

- - - - UNPARSED QUERY IS *******
        SELECT /*+ QB_NAME ("INNER") QB_NAME ("OUTER") */ "E"."EMPLOYEE_ID" "EMPLOYEE_ID","E"."FIRST_NAME" "FIRST_NAME","E"."LAST_NAME" "LAST_NAME","E"."EMAIL" "EMAIL","E"."PHONE_NUMBER" "PHONE_NUMBER","E"."HIRE_DATE" "HIRE_DATE","E"."JOB_ID" "JOB_ID","E"."SALARY" "SALARY","E"."COMMISSION_PCT" "COMMISSION_PCT","E"."MANAGER_ID" "MANAGER_ID","E"."DEPARTMENT_ID" "DEPARTMENT_ID" FROM "TLO"."EMPLOYEE" "E","TLO"."EMPLOYEE" "EMPLOYEE" WHERE "E"."DEPARTMENT_ID"="EMPLOYEE"."DEPARTMENT_ID" AND "E"."JOB_ID"='HR_REP' GROUP BY "EMPLOYEE"."DEPARTMENT_ID","E".ROWID,"E"."DEPARTMENT_ID","E"."MANAGER_ID","E"."COMMISSION_PCT","E"."SALARY","E"."JOB_ID","E"."HIRE_DATE","E"."PHONE_NUMBER","E"."EMAIL","E"."LAST_NAME","E"."FIRST_NAME","E"."EMPLOYEE_ID" HAVING "E"."SALARY">=AVG("EMPLOYEE"."SALARY")
        Registered qb: SEL$87B6C357 0xea57e8c (VIEW MERGE OUTER; INNER) <--

-
QUERY BLOCK SIGNATURE

-
signature (): qb_name=SEL$87B6C357 nbfros=2 flg=0 <-- CVM 수행, 쿼리블럭 SEL$87B6C357 생성
fro(0): flg=0 objn=74263 hint_alias="EMPLOYEE"@"INNER" <--
fro(1): flg=0 objn=74263 hint_alias="E"@"OUTER" <--

FPD: Considering simple filter push in query block SEL$87B6C357 (#1)
"E"."DEPARTMENT_ID"="EMPLOYEE"."DEPARTMENT_ID" AND "E"."JOB_ID"='HR_REP'
try to generate transitive predicate from check constraints for query block SEL$87B6C357 (#1)
finally: "E"."DEPARTMENT_ID"="EMPLOYEE"."DEPARTMENT_ID" AND "E"."JOB_ID"='HR_REP'

CVM: Costing transformed query. <--
CBQT: Looking for cost annotations for query block SEL$87B6C357, key = SEL$87B6C357_00000000_0
CBQT: Could not find stored cost annotations.
kkoqbc: optimizing query block SEL$87B6C357 (#1)

........
CVM: Updated best state, Cost = 4.00 <--
CVM: Will merge view INNER (#2)
........

– cost가 더 낮으므로 오라클은 CVM을 선택

|
 * 아래의 제약 조건이 있는 경우는 CVM이 발생하지 않는다.
 ** ROWNUM을 사용한 경우
 ** 인라인뷰 내부에 UNION/UNION ALL/MINUS 등의 집합연사자를 사용한 경우
 ** CONNECT BY 절이 사용된 경우
 ** 집합 함수가 사용된 경우
 ** 인라인뷰에 OUTER 조인이 걸린 경우
 ** 인라인뷰에 ORDER BY 가 사용된 경우
 ** ANALYIC 함수를 사용한 경우
 ** VIEW MERING이 불가능한 쿼리 블럭이 있는 경우
 *** 스칼라 서브 쿼리가 메인쿼리에 있는 경우. 스칼라 서브쿼리는 View Merging이 불가능하므로 전체를 Merging 할 수 없다.
 *** Cursor Expression을 사용한 경우.
 * CVM을 Control 하는 파라미터는 _complex_view_merging (defualt ture)
 * MERGE/NO_MERGE 힌트 사용
 * optimizer_secure_view_merging (defualt true) : 접속한 User가 뷰를 merge할 수 있는 권한이 있는 경우만 View Merge 발생.

3.8 CVM (Complex View Merging) Distinct나 Group By가 있는 뷰를 해체하라 (by jongmali) [2013.07.06]

3.8 CVM (Complex View Merging) : Distinct나 Group By가 있는 뷰를 해체하라