3.14 GBP (Group By Placement) Group By를 먼저 수행하고 Join 하라 (by darkturtle) [2013.06.28]
제 PC 가 이상한건지, 잘 안올라가네요. 우선 에버노트 공유 기능으로 발표 자료 올려요..
주중에 다시 올리겠습니다. (__)(--)(__)
( 꼬릿말 ) 예전에 HTML 형식 비슷하게 Edit 하는 메뉴가 안보이는데 없어졌나요 ㅇㅇ?
Logical Optimizer 2번째 발표 - 1
http://www.evernote.com/shard/s283/sh/b0d2ce03-001d-48c8-890a-4c1a5546564a/8eeb7170ce31da6add9ac105b977ca5e
Logical Optimizer 2번째 발표 - 2
http://www.evernote.com/shard/s283/sh/bb0dd5f6-99fc-437f-b486-5f3cfecef322/2c44fed07b54ec97f571faad8ea1db60
Logical Optimizer 2번째 발표 - 3
http://www.evernote.com/shard/s283/sh/28294b01-1d88-4b3d-87db-19e2b959f7e3/567d25d930b47800d4e0c3a7e672edec
다시
3.14. GBP ( Group By Placement ) : Group By 를 먼저 수행하고 Join 하라
가. 개념
: 조인을 수행하기 전에 Group By 를 먼저 수행하여 건수를 줄이고 나중에 조인을 수행함으로써 조인 건수를 획기적으로 감소 시키는게 목적
나. 힌트
: PLACE_GROUP_BY / NO_PLACE_GROUP_BY
다. 파라미터
: _optimizer_group_by_plance = true ( Default )
라. 예제 :
- Group By 가 두 번 발생, Sales 테이블을 Cust_id 로 먼저 Group By 한 이후에 조인
- ( 목적 : 대용량 테이블인 Sales 테이블을 조인 기준 컬럼인 Cust_id 로 먼저 Group By 수행 후에 조인함으로써 조인의 부하를 줄이기 위해 )
h2.오라클이 변경한 SQL
마. 10053 Trace
- GBP 기능을 사용할 수 잇는지 체크 하고
- GBP 변환이 가능하다고 판단되면 변환 방식(Search Type) 을 결정 ==> ( Exahustive)
- Iteration 과정(Iteration 1 과 Iteration 2 Cost 비교 생략됨 Why. Place_group_by 힌트를 통해서 Iteration 1 고정 )
- GBP 결과로 쿼리블럭 "SEL$62C25666" 가 생성되었으며, 그 내부의 From 절에 C(Customer)와 인라인뷰 VW_GBC_5 이 존재함을 알 수 있다.
****************************************
Cost-Based Group-By/Distinct Placement
****************************************
GBP/DP: Checking validity of GBP/DP for query block SEL$1 (#1)
GBP: Checking validity of group-by placement for query block SEL$1 (#1)
GBP: Using search type: exhaustive
GBP: Considering group-by placement on query block SEL$1 (#1)
GBP: Starting iteration 1, state space = (1,2) : (0,0)
GBP: Original query
...
GBP: Costing transformed query.
...
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$62C25666 nbfros=2 flg=0
fro(0): flg=1 objn=0 hint_alias="VW_GBC_5"@"SEL$0737CF93"
fro(1): flg=0 objn=102419 hint_alias="C"@"SEL$1"
3.15. GBPD ( Group By Push Down ) : Parallel Query 수행 시 Group by를 한번 더 수행하라.
가. 개념 :
Group By Push Down 의 정의
- Group By Push Down 이란 Group By 를 미리 수행 하여 데이터의 건수를 대폭 줄인 후에 TQ 에 데이터를 전달함으로써 일량을 줄이고, 성능 향상 목적
- Group By Push Down 은 Parallel Query 에 Group By 가 포함되어 있는 경우 발생
- GBPD는 CBQT 제어 파라미터를 Off 하여도 발생함, 그러나, CBPD 발생 시 Cost 로 판단하여, 불완전 CBQT로 분류함
- DW용 SQL 작성 시 Fact 테이블(대용량)은 먼저 Group By 한 후에 Dimension 테이블(소용량 코드 테이블)과 조인을 통한 성능 향상(=GBP)과 같은 개념
- 아주 복잡한 SQL 이나 옵티마이져가 판단을 잘못할 경우에 Group By Push Down 을 수동으로 발생 시켜 성능 향상
#. 참고 : TQ(Table Queue )
1. TQ 는 Process 간의 데이터를 주고받는 기능을 함
2. 하나의 TQ는 여러 개의 Parallel Slave 를 가짐
3. TQ는 Parallel Query 수행시 생성됨
나. 힌트
: GBY_PUSHDOWN/NO_GBY_PUSHDOWN (11g)
_groupby_nopushdown_cut_ratio = 0 을 통해서 Group By Push down 을 강제할 수 있다.(10g, Default 는 3, 옵티마이져 자동 판단)
다. 파라미터 :
라. 예제 :
- 원래 수행되어야 하는 Group By 는 ID 기준으로 3번이지만, ID 6번에서 먼저 Group By 가 수행됨
- 그 이유는 ID 5번 TQ10000 에게 데이터를 전달하기 전에 GROUP BY 를 통해 데이터를 줄여서 성능을 향상시키기 위함
- ID 5 번에서 GROUP BY 된 72 ROW 에 대한 데이터만 처리 할 것으로 예상 ( E-rows )
- TQ 일량이 줄어드는 정도와 GROUP BY 혹은 SORT GROUP BY 에 의한 추가 부하(XPLAN 에서 USED-TMP 참조)를 비교 할 것
GROUP BY PUSH DOWN 이 적용되지 않는 PLAN
: 정상적으로 Group By 가 한번만 수행됨 : TQ의 일량이 Group By Push Down 이 적용된 SQL 에 비해 918 K 로 약 12750 배 증가 (( 918*1024) /72 )
마. 10053 Trace
*********************************
Number of join permutations tried: 1
*********************************
Enumerating distribution method (advanced)
GROUP BY adjustment factor: 1.000000
GROUP BY cardinality: 72.000000, TABLE cardinality: 918843.000000
Costing group-by pushdown:
SORT ressource Sort statistics
Sort width: 162 Area size: 143360 Max Area size: 28730368
Degree: 1
Blocks to Sort: 422 Row size: 15 Total Rows: 229711
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 212689832
Total Temp space used: 0
Distribution cost: resc_cpu: 91885304 resp_cpu: 22971326
Costing final group-by:
Subtracting no-pushdown group-by:
SORT ressource Sort statistics
3.16. JF ( Join Factorization ) : Union / Union All 사용시 공통적으로 사용하는 테이블을 분리 시켜라.
가. 개념
- Union 혹은 Union All 사용시 위와 아래에서 공통적으로 테이블을 사용할 때 발생
- JF의 목적은 Union 으로 구분된 위와 아래에서 공통적으로 사용하는 테이블을 뷰 밖으로 분리시켜 중복 사용 피함으로써 성능 향상 (11gR2 소개됨)
- 복합한 쿼리에서는 JF 수행되지 않을 수 있다, 힌트를 사용해서 제어
나. 힌트
: FACTORIZE_JOIN / NO_FACTORIZE_JOIN
다. 파라미터
: _optimizer_join_factorization = true ( Default ) , False 설정 시 JE 기능 사용 불가
라. 예제
- "SQL 작성시 같은 테이블을 반복해서 사용하지 마라"
- 대용량 테이블인 판매 테이블(Sales)을 비효율적으로 2번 Scan 할것으로 예상 !!!
- 그러나, channel 테이블을 정확히 2번 Unique Scan 하였고, Sales 테이블은 1번만 Full Scan 함
Oracle 이 재작성한 SQL
: PLAN 에서 볼 수 있듯이 VW_JF_SET$0A277F6D 으로 시작하는 인라인뷰 명은 JF 가 수행되었음을 나타냄
힌트 사용을 통한 제어 ( FACTORIZE_JOIN )
: SET$1 ==>전체에 해당하는 쿼리 블록, SEL$1 => Union All 중 윗부분, SEL$2 => Union All 중 아랫 부분
마. 10053 Trace
먼저 10053 Trace 용어 설명 부분에 JF 가 아래처럼 추가
The following abbreviations are used by optimizer trace.
....
JF - join factorization
1. Search Type 이 Exhaustive 이므로 가능한 모든 경우의 수를 고려하겠다는 의미 ( Exhaustive )
2. JF 수행 검사
- 첫번째. 쿼리블럭 2와 3의 Channel© 분리 고려 ==> WHERE 조건(Filter) 에 의해서 실패
- 두번째. 쿼리블럭 2와 3의 Sales(S) 분리 고려 ==> Validataion Check
3. ( JF 대상은 S 로 확정 ) Cost 비교를 통해서 확정 절차 남음
***********************************
Cost-Based Join Factorization
***********************************
Join-Factorization on query block SET$1 (#1)
JF: Using search type: exhaustive
JF: Checking validity of join factorization for query block SET$1 (#1)
JF: Generate basic transformation units
Validating JF unit: (branch: {2, 3} table: {S, S})
passed JF validation
JF: unit inserted into state space: (branch: {2, 3} table: {S, S})
(branch: {2, 3} table: {S, S})
Validating JF unit: (branch: {2, 3} table: {C, C})
rejected: table filter predicates do not match
JF: Generate transformation units from basic units
JF: Starting iteration 1, state space = {}
JF: Transformed query
******* UNPARSED QUERY IS *******
(SELECT /*+ USE_HASH ("C") USE_HASH ("S") */ "S"."PROD_ID" "PROD_ID","S"."CUST_ID" "CUST_ID","S"."QUANTITY_SOLD" "QUANTITY_SOLD","S"."AMOUNT_SOLD" "AMOUNT_SOLD","C"."CHANNEL_DESC" "CHANNEL_DESC" FROM "TLO"."SALES" "S","TLO"."CHANNELS" "C" WHERE "C"."CHANNEL_ID"="S"."CHANNEL_ID" AND "C"."CHANNEL_ID"=3) UNION ALL (SELECT /*+ USE_HASH ("C") USE_HASH ("S") */ "S"."PROD_ID" "PROD_ID","S"."CUST_ID" "CUST_ID","S"."QUANTITY_SOLD" "QUANTITY_SOLD","S"."AMOUNT_SOLD" "AMOUNT_SOLD","C"."CHANNEL_DESC" "CHANNEL_DESC" FROM "TLO"."SALES" "S","TLO"."CHANNELS" "C" WHERE "C"."CHANNEL_ID"="S"."CHANNEL_ID" AND "C"."CHANNEL_ID"=9)
FPD: Considering simple filter push in query block SET$1 (#1)
??
FPD: Considering simple filter push in query block SEL$1 (#3)
"C"."CHANNEL_ID"="S"."CHANNEL_ID" AND "C"."CHANNEL_ID"=3
try to generate transitive predicate from check constraints for query block SEL$1 (#3)
finally: "S"."CHANNEL_ID"=3 AND "C"."CHANNEL_ID"=3
FPD: Considering simple filter push in query block SEL$2 (#2)
"C"."CHANNEL_ID"="S"."CHANNEL_ID" AND "C"."CHANNEL_ID"=9
try to generate transitive predicate from check constraints for query block SEL$2 (#2)
finally: "S"."CHANNEL_ID"=9 AND "C"."CHANNEL_ID"=9
JF: Costing transformed query.
CBQT: Looking for cost annotations for query block SEL$1, key = SEL$1_00000000_0
CBQT: Could not find stored cost annotations.
CBQT: Looking for cost annotations for query block SEL$2, key = SEL$2_00000000_0
CBQT: Could not find stored cost annotations.
kkoqbc: optimizing query block SEL$1 (#3)
:
call(in-use=4584, alloc=16360), compile(in-use=188776, alloc=202068), execution(in-use=3116, alloc=4060)
kkoqbc-subheap (create addr=0xb6a2fad0)
- 쿼리 블록 정보
- 쿼리블럭 2 는 SEL$2, 쿼리블럭 3 는 SEL$1
- (JF 수행되지 않음) Iteration 1의 Cost 는 1600.30
- 이후 Iteration 2 시작 ( Starting iteration 2 )
kkoqbc: finish optimizing query block SET$1 (#1)
CBQT: Saved costed qb# 3 (SEL$1), key = SEL$1_00000000_0
CBQT: Saved costed qb# 2 (SEL$2), key = SEL$2_00000000_0
CBQT: Saved costed qb# 1 (SET$1), key = SET$1_00000000_0
JF: Updated best state, Cost = 1600.30
JF: Starting iteration 2, state space = {(branch: {2, 3} table: {S, S})}
Registered qb: SET$540DB43F 0xb699aaf4 (JOIN FACTORIZATION SET QUERY BLOCK SET$1; SET$1; LIST)
- JF 가 수행된 쿼리블럭의 cost 는 1201.84(1600.30 대비) 으로 보다 저렴하다.
- 따라서 Logical Optimizer 는 원본 쿼리 블럭에 JF를 적용함
*********************************
Number of join permutations tried: 1
*********************************
JF: Updated best state, Cost = 1201.84
JF: Transferring best state space to preseved query.
JF: Transferring best state space to original query.
3.17. ST ( Start Transformation )
: From 절의 (소량의) Dimension 테이블을 서브쿼리로 변환하고 DSJ 기능을 이용하여 Bitmap 연산을 수행하라.
!!! 책 예제는 11.1 사용 예제
가. 개념 대용량 Fact 테이블과 소용량 Dimenstion 테이블들을 조인하는 방법
Dimension 테이블이 서브쿼리로 변환되는 특징( From 절에 있던 Dimension 테이블은 그대로 존재, Dimenstion 테이블이 2개 )
나. 힌트
STAR_TRANSFORMATION / NO_STAR_TRANSFORMATION ( 가끔 FACT 테이블 못 찾는 경우 FACT 힌트 추가 )
다. 파라미터
- star_transformation_enabled ( Default : False, ALTER SESSION SET "star_transformation_enabled" = false ; )
- DW 환경에서는 star_transformation_enabled 와 _b_tree_bitmap_plans 모두 True 권장
라. 예제
(전제 조건)
- ALTER SESSION SET "star_transformation_enabled" = TRUE ;
- Dimension 테이블과 조인되는 Fact 테이블의 칼럼들에 Bitmap 인덱스가 생성 되어야 함
( 즉 Sales 테이블의 Channel_id, prod_id 컬럼에 각각 Bitmap 인덱스가 존재해야 함)
( Bitmap Operation )
BITMAP INDEX
SINGLE VALUE : 인덱스 블록 내에서 하나의 키 값에 해당하는 비트맵을 검색
RANGE SCAN : 하나의 키 값에 해당하는 여러 개의 비트맵을 검색
FULL SCAN : 시작/종료값이 제공되지 않은 경우 비트맵 전체를 스캔
ITMAP KEY ITERATION
한 테이블에서 얻은 각각의 로우들을 특정 비트맵 인덱스에 대해 연속해서 확인하여 비트맵을 찾는것
뒤에 비트맵 머지가 수행되어 하나의 비트맵으로 합쳐지며 스타 변형 조인에서 나타남
BITMAP MERGE
범 위 스캔으로 얻은 몇 개의 비트맵을 하나로 머지
(Plan 설명)
- From 절의 Dimension(Channel, Product) 테이블에 해당하는 집합이 서브쿼리로 생성되는 Query Transformation 이 발생
추후 각 서브쿼리 간의 교집합(Bitmap And 연산)을 생성하여 Fact 테이블의 액세스 범위를 최소화하기 위한 사전작업
ID 14~15(PRODUCTS) 와 19~20( CHANNELS) - 1번에서 생성된 서브쿼리의 결과들을 이용하여 Fact(SALES) 테이블의 Bitmap 인덱스 액세스를 수행하고, 다시 그 결과를 Bitmap Merge 연산을 수행한다.
이때 Dimension 의 값이 여러 개 일 수도 있으므로 Bitmap Iteration 이 발생 한다. ( ID 12,13,16) 과 ( ID 17,18,21) - 각각의 Bitmap Merge 연산의 결과를 다시 Bitmap AND 연상을 이용하여 교집합을 만든다.
이 과정에서 Fact(Sales) 테이블로의 액세스가 획기적으로 줄어든다. ( ID 11 ) - 3번에서 만들어진 교집합으로 Fact 테이블에 액세스할 Rowid를 만든다. ( ID 10 )
- 4번에서 생성된 Rowid를 이용하여 Fact 테이블을 액세스 한다. ( ID 22, 9)
- 5번에서 생성된 집합과 각각의 Dimenstion 집합을 Hash Join 하고 Group By 를 수행한다. ( ID 1~6)
==> Dimension 테이블을 2번 액스세 한다는 점이 비효율적으로 느껴지겠지만,
ST 수행되지 않을 경우와 비교한다면, Dimension 테이블 중복 액세스는 아무것도 아님
SQL> set linesize 150
SQL> explain plan for SELECT p.prod_id
2 ,c.channel_id
3 ,SUM(quantity_sold) AS qs
4 ,SUM(amount_sold) AS amt
5 FROM sales s, channels c, products p
6 WHERE s.channel_id = c.channel_id
7 AND c.channel_desc ='Internet'
8 AND s.prod_id = p.prod_id
9 AND p.prod_category_desc ='Photo'
10 GROUP BY p.prod_id, c.channel_id ;
Explained.
SQL> set pagesize 2000
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 258650688
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 221 | 122 (2)| 00:00:02 | | |
| 1 | HASH GROUP BY | | 1 | 221 | 122 (2)| 00:00:02 | | |
|* 2 | HASH JOIN | | 115 | 25415 | 121 (1)| 00:00:02 | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 188 | 6 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS FULL | CHANNELS | 1 | 15 | 3 (0)| 00:00:01 | | |
| 5 | BUFFER SORT | | 1 | 173 | 3 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS FULL | PRODUCTS | 1 | 173 | 3 (0)| 00:00:01 | | |
| 7 | VIEW | VW_ST_34C376F1 | 3190 | 102K| 115 (1)| 00:00:02 | | |
| 8 | NESTED LOOPS | | 3190 | 149K| 109 (1)| 00:00:02 | | |
| 9 | PARTITION RANGE ALL | | 3190 | 60618 | 6 (0)| 00:00:01 | 1 | 28 |
| 10 | BITMAP CONVERSION TO ROWIDS| | 3190 | 60618 | 6 (0)| 00:00:01 | | |
| 11 | BITMAP AND | | | | | | | |
| 12 | BITMAP MERGE | | | | | | | |
| 13 | BITMAP KEY ITERATION | | | | | | | |
| 14 | BUFFER SORT | | | | | | | |
|* 15 | TABLE ACCESS FULL | PRODUCTS | 1 | 173 | 3 (0)| 00:00:01 | | |
|* 16 | BITMAP INDEX RANGE SCAN| SALES_PROD_BIX | | | | | 1 | 28 |
| 17 | BITMAP MERGE | | | | | | | |
| 18 | BITMAP KEY ITERATION | | | | | | | |
| 19 | BUFFER SORT | | | | | | | |
|* 20 | TABLE ACCESS FULL | CHANNELS | 1 | 15 | 3 (0)| 00:00:01 | | |
|* 21 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX | | | | | 1 | 28 |
| 22 | TABLE ACCESS BY USER ROWID | SALES | 1 | 29 | 108 (0)| 00:00:02 | ROWID | ROWID |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_2"="C"."CHANNEL_ID" AND "ITEM_1"="P"."PROD_ID")
4 - filter("C"."CHANNEL_DESC"='Internet')
6 - filter("P"."PROD_CATEGORY_DESC"='Photo')
15 - filter("P"."PROD_CATEGORY_DESC"='Photo')
16 - access("S"."PROD_ID"="P"."PROD_ID")
20 - filter("C"."CHANNEL_DESC"='Internet')
21 - access("S"."CHANNEL_ID"="C"."CHANNEL_ID")
Note
-----
- star transformation used for this statement
44 rows selected.
ST VS Non-ST 비교
- SALES 테이블의 액세스 건수가 A-Rows 기준으로 무려 70배(13K 와 118K) 추이가 난다.
- Scan 한 블록 수(Buffers, 699 와 3700)도 6배 정도 차이가 난다.
- Bitmap Merge 연상을 수행하였으므로, PGA(Bitmap Area)를 많이 사용하였을 것이라고 생각하겠지만,
- 오히려 PGA 사용량은 ST 를 수행한 결과 차이 거의 없음 ( 1118K VS 111K ) -- 책은 더 작음
( ST 수행 시 )
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.13 | 699 | | | |
| 1 | HASH GROUP BY | | 1 | 1 | 10 |00:00:00.13 | 699 | 830K| 830K| 1118K (0)|
|* 2 | HASH JOIN | | 1 | 1595 | 13223 |00:00:00.11 | 699 | 1306K| 1306K| 1041K (0)|
| 3 | MERGE JOIN CARTESIAN | | 1 | 1 | 10 |00:00:00.01 | 12 | | | |
|* 4 | TABLE ACCESS FULL | CHANNELS | 1 | 1 | 1 |00:00:00.01 | 6 | | | |
| 5 | BUFFER SORT | | 1 | 10 | 10 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
|* 6 | TABLE ACCESS FULL | PRODUCTS | 1 | 10 | 10 |00:00:00.01 | 6 | | | |
| 7 | VIEW | VW_ST_34C376F1 | 1 | 31904 | 13223 |00:00:00.08 | 687 | | | |
| 8 | NESTED LOOPS | | 1 | 31904 | 13223 |00:00:00.07 | 687 | | | |
| 9 | PARTITION RANGE ALL | | 1 | 31904 | 13223 |00:00:00.02 | 242 | | | |
| 10 | BITMAP CONVERSION TO ROWIDS| | 28 | 31904 | 13223 |00:00:00.01 | 242 | | | |
| 11 | BITMAP AND | | 28 | | 16 |00:00:00.01 | 242 | | | |
| 12 | BITMAP MERGE | | 28 | | 16 |00:00:00.01 | 53 | 1024K| 512K| 8192 (0)|
| 13 | BITMAP KEY ITERATION | | 28 | | 20 |00:00:00.01 | 53 | | | |
| 14 | BUFFER SORT | | 28 | | 28 |00:00:00.01 | 6 | 73728 | 73728 | |
|* 15 | TABLE ACCESS FULL | CHANNELS | 1 | 1 | 1 |00:00:00.01 | 6 | | | |
|* 16 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX | 28 | | 20 |00:00:00.01 | 47 | | | |
| 17 | BITMAP MERGE | | 28 | | 16 |00:00:00.01 | 189 | 1024K| 512K| 3072 (0)|
| 18 | BITMAP KEY ITERATION | | 28 | | 132 |00:00:00.01 | 189 | | | |
| 19 | BUFFER SORT | | 28 | | 160 |00:00:00.01 | 6 | 73728 | 73728 | |
|* 20 | TABLE ACCESS FULL | PRODUCTS | 5 | 10 | 10 |00:00:00.01 | 6 | | | |
|* 21 | BITMAP INDEX RANGE SCAN| SALES_PROD_BIX | 160 | | 132 |00:00:00.01 | 183 | | | |
| 22 | TABLE ACCESS BY USER ROWID | SALES | 13223 | 1 | 13223 |00:00:00.03 | 445 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------
( ST 수행 미수행시 )
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.46 | 3700 | | | |
| 1 | HASH GROUP BY | | 1 | 8 | 10 |00:00:00.46 | 3700 | 830K| 830K| 1111K (0)|
|* 2 | HASH JOIN | | 1 | 31904 | 13223 |00:00:00.29 | 3700 | 1517K| 1517K| 1477K (0)|
|* 3 | TABLE ACCESS FULL | PRODUCTS | 1 | 10 | 10 |00:00:00.01 | 6 | | | |
| 4 | NESTED LOOPS | | 1 | | 118K|00:00:00.56 | 3694 | | | |
| 5 | NESTED LOOPS | | 1 | 229K| 118K|00:00:00.16 | 39 | | | |
|* 6 | TABLE ACCESS FULL | CHANNELS | 1 | 1 | 1 |00:00:00.01 | 6 | | | |
| 7 | PARTITION RANGE ALL | | 1 | | 118K|00:00:00.10 | 33 | | | |
| 8 | BITMAP CONVERSION TO ROWIDS | | 28 | | 118K|00:00:00.04 | 33 | | | |
|* 9 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | 28 | | 20 |00:00:00.01 | 33 | | | |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 118K| 229K| 118K|00:00:00.24 | 3655 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
- Bind 변수(아마도 Cost 비교를 위해서 인듯)와 Bitmap Block Lock 때문에 OLTP 는 부적합, DW 환경에 적합
- DW 환경에서 ST 기능은 획기적인 성능 향상을 기대할 수 있다.
- ST를 수행하려면 Bind 변수를 사용할 수 없으므로 Dynamic SQL으로 적용될 수 밖에 없다.
- OLTP 환경이라면 하루에 몇 번만 수행되는 야간 배치 SQL 등에만 적용하여야 한다.
- Bitmap 인덱스를 생성하면 Row Level Lock 이 아닌 Block Level Lock 이 적용되므로 OLTP 환경에 부적합
(참고) Bree 인덱스로도 ST 기능을 사용할 수 있다. ( 단. _b_tree_bitmap_plans = true )
(참고) 바인드 변수 사용시
SQL> ALTER SESSION SET "star_transformation_enabled" = TRUE;
Statement Processed.
SQL> SELECT /*+ gather_plan_statistics */ p.prod_id
,c.channel_id
,SUM(quantity_sold) AS qs
,SUM(amount_sold) AS amt
FROM sales s, channels c, products p
WHERE s.channel_id = c.channel_id
AND c.channel_desc = :A --'Internet'
AND s.prod_id = p.prod_id
AND p.prod_category_desc = :B --'Photo'
GROUP BY p.prod_id, c.channel_id;
PROD_ID CHANNEL_ID QS AMT
------- ----------- ----------- -----------
136 4 124 4767.1
138 4 543 71203.21
137 4 1701 89044.53
13 4 1424 1376202.76
135 4 1926 98759.33
17 4 755 930450.94
134 4 1037 22091.54
132 4 1590 41347
133 4 2291 72670.59
131 4 1832 36130.95
10 rows selected.
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3jaqhhxup0kjm, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ p.prod_id ,c.channel_id
,SUM(quantity_sold) AS qs ,SUM(amount_sold) AS
amt FROM sales s, channels c, products p WHERE s.channel_id =
c.channel_id AND c.channel_desc = :A --'Internet' AND
s.prod_id = p.prod_id AND p.prod_category_desc = :B --'Photo'
GROUP BY p.prod_id, c.channel_id
Plan hash value: 2213333095
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.46 | 3700 | | | |
| 1 | HASH GROUP BY | | 1 | 8 | 10 |00:00:00.46 | 3700 | 830K| 830K| 2004K (0)|
|* 2 | HASH JOIN | | 1 | 31904 | 13223 |00:00:00.29 | 3700 | 1517K| 1517K| 1152K (0)|
|* 3 | TABLE ACCESS FULL | PRODUCTS | 1 | 10 | 10 |00:00:00.01 | 6 | | | |
| 4 | NESTED LOOPS | | 1 | | 118K|00:00:00.56 | 3694 | | | |
| 5 | NESTED LOOPS | | 1 | 229K| 118K|00:00:00.16 | 39 | | | |
|* 6 | TABLE ACCESS FULL | CHANNELS | 1 | 1 | 1 |00:00:00.01 | 6 | | | |
| 7 | PARTITION RANGE ALL | | 1 | | 118K|00:00:00.10 | 33 | | | |
| 8 | BITMAP CONVERSION TO ROWIDS | | 28 | | 118K|00:00:00.04 | 33 | | | |
|* 9 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | 28 | | 20 |00:00:00.01 | 33 | | | |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 118K| 229K| 118K|00:00:00.24 | 3655 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
마. 10053 Trace
- 테스트 수행시, 책과 동일한 내용의 Trace 가 발견되지 않았음 ㅡㅡ; (11gR1 이 아닌11gR2 로 ST 가 아닌 CBST 가 발생한 것으로 추정)
- ST 수행 시 Outline Data를 분석해보면 재미있는 것을 발견할 수 있다.
- Star Transformation 힌트가 내부적으로 사용되었으며, 그 과정에서 Where 절에 서브쿼리 2개를 생성
- Bitmap Tree(Index Combination 발생 할때 내부적으로 변환되는 힌트) 힌트를 사용 ( ST 에서도 사용됨 )
- ST 변환이 발생하여 쿼리블록 SEL$1에 서브쿼리를 생성하려고 하고 있다.
- 이 과정에서 SEL$D750a531 이 생성되었다.
- Dimension 테이블인 CHANNELS 가 포함된 서브쿼리가 생성되었고, 그 과정에서 쿼리블록 SEL$1189F719 가 생성됨
- OR-Expalnsion 실패 = temp table ( bitmap semi join )
- 연이어 이전 과정에서 생성된 쿼리블록 SEL$1898F719 에 Dimension 테이블인 PRODUCT가 포함된 서브쿼리가 생성됨
- 서브쿼리 생성 과정이 끝나면 쿼리 블럭이 새로 생성된다. 쿼리 블록 SEL$5208623C가 그것이다. ( 모든 변환 과정 끝)
- 하지만 새로 생성된 쿼리블럭 SEL$5208623C에 대해서는 조인방법 및 액세스 방법에 대하여 Physical Optimizer가 최적화를 해야 함.
- ( STAR TRANSFORMATION PLAN 이라고 표기됨)
- 물론 ST를 수행하려면 , DSJ(Driving Semi Join)기능을 사용해야 한다.
- 오라클은 ST 기능을 수행하기 위하여 Dimension 을 Subquery 로 Transformation 하였으며
- Driving Semi Join 과정을 진행한다. CBQT 가 변환한 SQL은 다음과 같다.
SELECT /*+ gather_plan_statistics */ p.prod_id
,c.channel_id
,SUM(quantity_sold) AS qs
,SUM(amount_sold) AS amt
FROM sales s, channels c, 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'
AND s.channel_id = ANY ( SELECT c1.channel_id
FROM channels c1
WHERE c1.channel_desc ='Internet')
AND s.prod_id = ANY ( SELECT p1.prod_id
FROM products p1
WHERE p1.prod_category_desc ='Photo')
GROUP BY p.prod_id, c.channel_id ;
- Dimenstion 테이블이 서브쿼리에 존재함에도 불구하고 메인 쿼리의 From 절에 중복해서 있는 이유는
- 메인 쿼리에서 서브쿼리(Semi Join)의 컬럼을 SELECT 할 수 없기 때문이다.
- ST 기능을 잘 활용 한다면 Drivming Semi Join 의 효율을 더욱 극대화 할 수 있다.
- 왜냐하면 Dimension 이 하나가 아닌 2개 이상이므로 Driving Semin Join 간의 Bitmap And 연산을 수행함으로써
- 교집합만을 구하여 Fact 테이블에 Access 하므로 조인 건수를 획기적으로 줄어든다.
- !!! Driving Semi Join 의 장점과 결과 건수를 줄여주는 Bitmap And 연산자야 말로 ST 기능의 정수 !!!
SELECT p.prod_id, c.channel_id,
SUM (quantity_sold) AS qs ,
SUM (amount_sold) AS arnt
FROM sales s ,channels c , 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'
AND s.channel_id = ANY ( SELECT cl.channel_id
FROM channels cl WHERE cl.channel_desc = 'Internet' )
AND s.prod_id = ANY ( SELECT pl.prod_id
FROM products pl WHERE pl.prod_category_desc = 'Photo' )
GROUP BY p.prod_id, c.channel_id;
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('optimizer_index_cost_adj' 20)
ALL_ROWS
OUTLINE_LEAF(@"SEL$D750A531")
OUTLINE_LEAF(@"SEL$1898F719")
OUTLINE_LEAF(@"SEL$26AA4408")
TABLE_LOOKUP_BY_NL(@"SEL$5208623C" "S"@"SEL$1")
OUTLINE_LEAF(@"SEL$45464E50")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$5208623C")
STAR_TRANSFORMATION(@"SEL$1" "S"@"SEL$1" SUBQUERIES(("C"@"SEL$1") ("P"@"SEL$1")))
FULL(@"SEL$45464E50" "C"@"SEL$1")
FULL(@"SEL$45464E50" "P"@"SEL$1")
NO_ACCESS(@"SEL$45464E50" "VW_ST_34C376F1"@"SEL$45464E50")
LEADING(@"SEL$45464E50" "C"@"SEL$1" "P"@"SEL$1" "VW_ST_34C376F1"@"SEL$45464E50")
USE_MERGE_CARTESIAN(@"SEL$45464E50" "P"@"SEL$1")
USE_HASH(@"SEL$45464E50" "VW_ST_34C376F1"@"SEL$45464E50")
USE_HASH_AGGREGATION(@"SEL$45464E50")
BITMAP_TREE(@"SEL$26AA4408" "S"@"SEL$1" AND(("SALES"."PROD_ID") ("SALES"."CHANNEL_ID")))
ROWID(@"SEL$26AA4408" "SYS_CP_S"@"SEL$26AA4408")
LEADING(@"SEL$26AA4408" "S"@"SEL$1" "SYS_CP_S"@"SEL$26AA4408")
USE_NL(@"SEL$26AA4408" "SYS_CP_S"@"SEL$26AA4408")
FULL(@"SEL$1898F719" "P"@"SEL$1898F719")
SEMIJOIN_DRIVER(@"SEL$1898F719")
FULL(@"SEL$D750A531" "C"@"SEL$D750A531")
SEMIJOIN_DRIVER(@"SEL$D750A531")
END_OUTLINE_DATA
*/
ST: Updated best state, Cost = 116.38
ST: Generating directive for second pass
ST: Fact Table Candidate: PRODUCTS[P] discarded since fact table joined to less than 2 tables
ST: Fact Table Candidate: CHANNELS[C] discarded since fact table joined to less than 2 tables
ST: Fact Table Candidate: SALES[S] valid
ST: Finding dimensions of SALES[S]
ST: Dimension Table Candidate: PRODUCTS[P], Star cols: [PROD_ID-PROD_ID]
ST: Valid Dimension Table: PRODUCTS[P], sel = 0.013889
ST: Dimension Table Candidate: CHANNELS[C], Star cols: [CHANNEL_ID-CHANNEL_ID]
ST: Valid Dimension Table: CHANNELS[C], sel = 0.200000
ST: Determining efficient subqueries for SALES[S]
Registered qb: SEL$D750A531 0x10a28110 (STAR TRANSFORM SUBQUERY SEL$1)
...
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$D750A531 nbfros=1 flg=0
fro(0): flg=4 objn=153706 hint_alias="C"@"SEL$D750A531"
ST: Subquery text:******* UNPARSED QUERY IS *******
SELECT "C"."CHANNEL_ID" "ITEM_1" FROM "TLO"."CHANNELS" "C" WHERE "C"."CHANNEL_DESC"='Internet'
ST: Begin: find best directive for query block SEL$D750A531 (#2)
ST: not valid since query block has less than 3 tables
ST: no directive since Not valid
ST: End: finding best directive for query block SEL$D750A531 (#2)
ST: JBE not valid since dimension column referenced outside
ST: No partition pruning
ST: TTT not valid based on cost since small dimension
ST: TTT not valid since cost validation failed
FPD: Considering simple filter push in query block SEL$D750A531 (#2)
"C"."CHANNEL_DESC"='Internet'
try to generate transitive predicate from check constraints for query block SEL$D750A531 (#2)
finally: "C"."CHANNEL_DESC"='Internet'
...
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1898F719 nbfros=1 flg=0
fro(0): flg=4 objn=153736 hint_alias="P"@"SEL$1898F719"
ST: Subquery text:******* UNPARSED QUERY IS *******
SELECT "P"."PROD_ID" "ITEM_1" FROM "TLO"."PRODUCTS" "P" WHERE "P"."PROD_CATEGORY_DESC"='Photo'
ST: Begin: find best directive for query block SEL$1898F719 (#3)
ST: not valid since query block has less than 3 tables
ST: no directive since Not valid
ST: End: finding best directive for query block SEL$1898F719 (#3)
ST: JBE not valid since dimension column referenced outside
ST: No partition pruning
ST: TTT not valid based on cost since small dimension
ST: TTT not valid since cost validation failed
FPD: Considering simple filter push in query block SEL$1898F719 (#3)
"P"."PROD_CATEGORY_DESC"='Photo'
try to generate transitive predicate from check constraints for query block SEL$1898F719 (#3)
finally: "P"."PROD_CATEGORY_DESC"='Photo'
3.18. CBST (Cost Based Start Transformation )
: Start Transformation 적용 시 Cost Based 환경을 이용하라.
가. 개념
- 11.1.0.7 까지 Star Transformation 은 불완전한 CBQT 였다. ( Why Logical Optimizer 주관이 아님 )
- 따라서, Search Type 도 없으며, Iterlation 또한 없음
- 11gR2 부터 완전환 CBQT 로 변환함
나. 힌트
다. 파라미터
_optimizer_use_cbqt_transformation = true ( Default ) , False 시,예전의 불완전한 ST 기능으로 돌아감
라. 예제
마. 10053 Trace
- ST를 적용하지 않은 과정의 Cost 는 116.38 로 나타났다.
- 연이어 두 번째 Iteration 을 준비하고 있는 모습이 보인다.
- 먼저 Fact 테이블로 Sales 테이블이 결정 되었으며 Dimension 테이블로는 Products 와 Channels 테이블이 선정 되었다.
- 각각의 Dimension 테이블마다 Selectivity 가 계산되었다.
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('optimizer_index_cost_adj' 20)
ALL_ROWS
OUTLINE_LEAF(@"SEL$D750A531")
OUTLINE_LEAF(@"SEL$1898F719")
OUTLINE_LEAF(@"SEL$26AA4408")
TABLE_LOOKUP_BY_NL(@"SEL$5208623C" "S"@"SEL$1")
OUTLINE_LEAF(@"SEL$45464E50")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$5208623C")
STAR_TRANSFORMATION(@"SEL$1" "S"@"SEL$1" SUBQUERIES(("C"@"SEL$1") ("P"@"SEL$1")))
FULL(@"SEL$45464E50" "C"@"SEL$1")
FULL(@"SEL$45464E50" "P"@"SEL$1")
NO_ACCESS(@"SEL$45464E50" "VW_ST_34C376F1"@"SEL$45464E50")
LEADING(@"SEL$45464E50" "C"@"SEL$1" "P"@"SEL$1" "VW_ST_34C376F1"@"SEL$45464E50")
USE_MERGE_CARTESIAN(@"SEL$45464E50" "P"@"SEL$1")
USE_HASH(@"SEL$45464E50" "VW_ST_34C376F1"@"SEL$45464E50")
USE_HASH_AGGREGATION(@"SEL$45464E50")
BITMAP_TREE(@"SEL$26AA4408" "S"@"SEL$1" AND(("SALES"."CHANNEL_ID") ("SALES"."PROD_ID")))
ROWID(@"SEL$26AA4408" "SYS_CP_S"@"SEL$26AA4408")
LEADING(@"SEL$26AA4408" "S"@"SEL$1" "SYS_CP_S"@"SEL$26AA4408")
USE_NL(@"SEL$26AA4408" "SYS_CP_S"@"SEL$26AA4408")
FULL(@"SEL$1898F719" "P"@"SEL$1898F719")
SEMIJOIN_DRIVER(@"SEL$1898F719")
FULL(@"SEL$D750A531" "C"@"SEL$D750A531")
SEMIJOIN_DRIVER(@"SEL$D750A531")
END_OUTLINE_DATA
*/
- 진정한 CBQT 따라서, Search Type 도 존재, Iteration 또한 존재
- "Not Transformed"를 보고, 첫번째 Iteration 은 ST 변환을 수행하지 않는 단계임을 알 수 있다.
***********************************
ST: Star Transformation
***********************************
ST: Query in kkqctTD:******* UNPARSED QUERY IS *******
SELECT "P"."PROD_ID" "PROD_ID","C"."CHANNEL_ID" "CHANNEL_ID",SUM("S"."QUANTITY_SOLD") "QS",SUM("S"."AMOUNT_SOLD") "AMT" FROM "TLO"."
SALES" "S","TLO"."CHANNELS" "C","TLO"."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"
ST: Begin: find best directive for query block SEL$1 (#1)
discarded since not big enough
ST: Using search type: two pass
ST: Generating directive for first pass
ST: Starting iteration 1, directive =
{Not Transformed}
... 중략
ST: Costing transformed query.
kkoqbc: optimizing query block SEL$1 (#1)
- 연이어 Costing 수행
- ST를 적용하지 않은 과정이 Cost 는 568.58로 나타났다.
- second pass연이어 두 번째 Iteration 을 준비고 있는 모습이 보인다.
- 먼저 Fact 테이블로 SALES 이 결정되었으며
- Dimenstion 테이블로 Product 와 Channels 테이블이 선정되었다.
- 각각의 Dimension 테이블마다 Selectivity 가 계산됨 ( sel= ... )
ST: Updated best state, Cost = 568.58
ST: Generating directive for second pass
ST: Fact Table Candidate: PRODUCTS[P] discarded since fact table joined to less than 2 tables
ST: Fact Table Candidate: CHANNELS[C] discarded since fact table joined to less than 2 tables
ST: Fact Table Candidate: SALES[S] valid
ST: Finding dimensions of SALES[S]
ST: Dimension Table Candidate: PRODUCTS[P], Star cols: [PROD_ID-PROD_ID]
ST: Valid Dimension Table: PRODUCTS[P], sel = 0.138889
ST: Dimension Table Candidate: CHANNELS[C], Star cols: [CHANNEL_ID-CHANNEL_ID]
ST: Valid Dimension Table: CHANNELS[C], sel = 0.200000
- ST를 적용하려면 추가적인 서브쿼리가 필요한데 아래 Trace 가 추가적인 서브쿼리를 완성하는 단계
- 먼저 Dimension 테이블인 Channels 에 대하여 ST 변환이 일어나 새로운 쿼리블럭인 "SEL$D750A531" 가 만들어졌다.
- 그리고 연이어 새로 생성된 서브쿼리를 보여주고 있다.
- 마찬가지로 Product 테이블에 대해서도 ST 변환이 일어나 새로운 쿼리블럭이 발생하였으면 연이서 새로 생성된 서브쿼리를 보여주고 있다.
- 각각의 새로운 쿼리블럭들이 마지막에 Costing 되고 있다.
- 두 번째 Iteration 이 발생하기 전에 ST 변황이 미리 발생되는 이유는 두 번재 Iteration 때 각각의 쿼리 블럭을 Copy 하여 사용하기 위함
ST: Determining efficient subqueries for SALES[S]
Registered qb: SEL$D750A531 0x10a29190 (STAR TRANSFORM SUBQUERY SEL$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$D750A531 nbfros=1 flg=0
fro(0): flg=4 objn=153706 hint_alias="C"@"SEL$D750A531"
ST: Subquery text:******* UNPARSED QUERY IS *******
SELECT "C"."CHANNEL_ID" "ITEM_1" FROM "TLO"."CHANNELS" "C" WHERE "C"."CHANNEL_DESC"='Internet'
ST: Begin: find best directive for query block SEL$D750A531 (#2)
ST: not valid since query block has less than 3 tables
ST: no directive since Not valid
ST: End: finding best directive for query block SEL$D750A531 (#2)
ST: JBE not valid since dimension column referenced outside
ST: No partition pruning
ST: TTT not valid based on cost since small dimension
ST: TTT not valid since cost validation failed
FPD: Considering simple filter push in query block SEL$D750A531 (#2)
"C"."CHANNEL_DESC"='Internet'
try to generate transitive predicate from check constraints for query block SEL$D750A531 (#2)
finally: "C"."CHANNEL_DESC"='Internet'
CBQT: Looking for cost annotations for query block SEL$D750A531, key = SEL$D750A531_00002000_0
CBQT: Could not find stored cost annotations.
kkoqbc: optimizing query block SEL$D750A531 (#2)
.. 중간 생략
Registered qb: SEL$1898F719 0x10a40578 (STAR TRANSFORM SUBQUERY SEL$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1898F719 nbfros=1 flg=0
fro(0): flg=4 objn=153736 hint_alias="P"@"SEL$1898F719"
ST: Subquery text:******* UNPARSED QUERY IS *******
SELECT "P"."PROD_ID" "ITEM_1" FROM "TLO"."PRODUCTS" "P" WHERE "P"."PROD_CATEGORY_DESC"='Photo'
..중략
kkoqbc: optimizing query block SEL$1898F719 (#3)
- 마지막으로 Fact 테이블에 해당하는 쿼리블럭이 만들어 졌고 Costing 이 진행되고 있다.
- IN 서브쿼리 대신 = ANY 서브쿼리가 사용됨
- 아직까지 DSJ(Driving Semi Join)기능은 사용되지 않음
ST: fact qbc text******* UNPARSED QUERY IS *******
SELECT "P"."PROD_ID" "PROD_ID","C"."CHANNEL_ID" "CHANNEL_ID",SUM("S"."QUANTITY_SOLD") "QS",SUM("S"."AMOUNT_SOLD") "AMT" FROM "TLO"."
SALES" "S","TLO"."CHANNELS" "C","TLO"."PRODUCTS" "P" WHERE "S"."PROD_ID"=ANY (SELECT "P"."PROD_ID" "ITEM_1" FROM "TLO"."PRODUCTS" "P
" WHERE "P"."PROD_CATEGORY_DESC"='Photo') AND "S"."CHANNEL_ID"=ANY (SELECT "C"."CHANNEL_ID" "ITEM_1" FROM "TLO"."CHANNELS" "C" WHERE
"C"."CHANNEL_DESC"='Internet') AND "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"
- 이제 두 번째 Iteration 이 시작되었으며 Fact 테이블과 Dimenstion 테이블들은 이미 정해져 있다. ( 1차에서 준비됨)
- 미리 준비해놓은 쿼리블럭을 Copy 해서 재사용하고 있음을 알 수 있다. (COPY SEL$D750A531)
- 연이어 ST 변환이 수행된다.
ST: Starting iteration 2, directive =
FACT - SALES[S] has no join index, is not on right of NL, index only check is not ok
SUBQUERY - TRIED, USED, DIM FROS - CHANNELS[C],
SUBQUERY - TRIED, USED, DIM FROS - PRODUCTS[P],
ST: Query before star xformation:******* UNPARSED QUERY IS *******
SELECT "P"."PROD_ID" "PROD_ID","C"."CHANNEL_ID" "CHANNEL_ID",SUM("S"."QUANTITY_SOLD") "QS",SUM("S"."AMOUNT_SOLD") "AMT" FROM "TLO"."
..중간생략
ST: Subquery text:******* UNPARSED QUERY IS *******
SELECT "C"."CHANNEL_ID" "ITEM_1" FROM "TLO"."CHANNELS" "C" WHERE "C"."CHANNEL_DESC"='Internet'
Registered qb: SEL$D750A531 0x10a80da0 (COPY SEL$D750A531)
..중간생략
Registered qb: SEL$5208623C 0x10a61480 (STAR TRANSFORM SEL$1; SEL$1; "S"@"SEL$1" LIST)
- 두 번째 Iteration 에서 ST 가 적용된 SQL의 모습을 보여준다.
- 여기서 DSJ 기능을 이용하고 있다.(SEMIJOIN_DRIVER)
ST: Query after star xformation:******* UNPARSED QUERY IS *******
SELECT "P"."PROD_ID" "PROD_ID","C"."CHANNEL_ID" "CHANNEL_ID",SUM("S"."QUANTITY_SOLD") "QS",SUM("S"."AMOUNT_SOLD") "AMT" FROM "TLO"."
SALES" "S","TLO"."CHANNELS" "C","TLO"."PRODUCTS" "P" WHERE "S"."PROD_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "P"."PROD_ID" "ITEM_1" F
ROM "TLO"."PRODUCTS" "P" WHERE "P"."PROD_CATEGORY_DESC"='Photo') AND "S"."CHANNEL_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "C"."CHANNE
L_ID" "ITEM_1" FROM "TLO"."CHANNELS" "C" WHERE "C"."CHANNEL_DESC"='Internet') AND "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"
... 중간생략
kkoqbc: optimizing query block SEL$D750A531 (#4)
- ST 기능이 적용된 두 번째 Iteration 의 Cost 는 267.92로 첫 번째 Iteration 의 Cost 보다 높다.
( 테스트 568.58 < 267.92, 책 500.57 > 503.57 ) - 하지만 Logical Optimizer 는 ST를 적용하는 실행 계획을 선택한다.
- '부분을 보면 ST가 적용된 실행계획을 선택하고 있다.
- 이상하게 생각하겠지만(비용이 높은데 선택) 이런 선택을 하는 이유는 따로 있다.
- two pass -- Two_pass 인 경우 Query Transformation 적용하는 것을 목표로함
- 따라서 쿼리변환이 적용된 두 번째 Iteration 을 선택하게 된다.( 4.6 장 설명 예정 )
- 이로써 Fact 테이블과 모든 Dimension 테이블이 결정되고 ST의 모든 변환과정 완료
ST: Updated best state, Cost = 267.92
..중간생략
ST: Best state: Total iteration 2, directive =
FACT - <unnamed>[] has no join index, is not on right of NL, index only check is ok 책:<SALES>[S]
SUBQUERY - TRIED, USED, DIM FROS - CHANNELS[C],
SUBQUERY - TRIED, USED, DIM FROS - PRODUCTS[P],
ST: End: finding best directive for query block SEL$45464E50 (#1)
3.19. MVR ( Materialized View Rewrite ) : Materilized View 를 사용하지 않는 SQL을 Materialized View 를 사용하는 SQL 로 바꾸어라.
가. 개념
- Materialzied View(MView)란 일종의 집계 테이블이다.
- 집계 테이블을 Update 하는 작업을 오라클이 대신해주므로 사용자는 작업을 언제 수행할지 결정 하기만 하면 된다.(Refresh 방법)
- Mview 가 존재할 경우, Transformer 는 성능 향상을 목적으로 Mview 를 이용하는 SQL로 재작성함
나. 힌트
REWRITE/NO_REWRITE
다. 파라미터
- query_rewrite_enabled = true default
- _query_cost_rewrite = false 설정 시 무조건 Mview 를 사용하는 Plan 을 작성 Default 는 True
- CQBT 기능을 Control 하는 파라미터인 _optimizer_cost_based_trransformation 을 Off 하여도 수행되므로
- Cost Based Query Transformer 가 변환 작업 수행하는 것이 아니라 Physical Optimization 과정에서 수행됨을 알 수 있음.
- 불완전한 CQBT라고 함( Search Type 과 Iteration 과정이 없음)
라. 예제
- Mview 를 사용하지 않은 SQL 이지만 정상적으로 Mview 를 사용한 Plan 이 생성되었다.
SQL> SELECT /*+ gather_plan_statistics 01 */ a.department_id, b.department_name, SUM(a.salary) salary_sum
2 FROM employee a, department b
3 WHERE a.department_id = b.department_id
4 AND b.department_id = 10
5 GROUP BY a.department_id, b.department_name ;
DEPARTMENT_ID DEPARTMENT_NAME SALARY_SUM
------------- ------------------------------ ----------
10 Administration 4400
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 324wrdma2txaw, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics 01 */ a.department_id,
b.department_name, SUM(a.salary) salary_sum FROM employee a,
department b WHERE a.department_id = b.department_id AND
b.department_id = 10 GROUP BY a.department_id, b.department_name
Plan hash value: 2219879030
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | MAT_VIEW REWRITE ACCESS BY INDEX ROWID| DEPT_SAL | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | IDX_DEPT_SAL_01 | 1 | 1 | 1 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT_SAL"."DEPARTMENT_ID"=10)
22 rows selected.
- Transformer 가 SQL 을 아래처럼 변경한 것이다.
SQL> SELECT department_id, department_name, salary_sum
FROM dept_sal
WHERE department_id =10
마. 10053 Trace
- MVR 기능을 사용하기 위해 오라클은 내부적으로 힌트를 사용하였다.
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_index_cost_adj' 20)
ALL_ROWS
OUTLINE_LEAF(@"SEL$01E878FB")
REWRITE(@"SEL$240F160A" "DEPT_SAL")
OUTLINE(@"SEL$240F160A")
REWRITE(@"SEL$1" "DEPT_SAL")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$01E878FB" "DEPT_SAL"@"SEL$7991DADF" ("DEPT_SAL"."DEPARTMENT_ID"))
END_OUTLINE_DATA
*/
- 10053 Trace 분석
- Mview 의 Transformer 과정은 좀 특이하다.
- Query Parser 로부터 Mview 를 이용하는 SQL 을 받아와 SEL$7991DADF 이라는 쿼리블럭명으로 Shared Pool 에 저장하고 있다.
- 연이어 쿼리블록 SEL$7991DADF 를 이용하여 Shared Pool에 저장된 원본 SQL 을 Rewrite 한다.
- 새로운 쿼리블럭인 SEL$240F160A 이 생성된다.
Copy query block qb# -1 (<unnamed>) : SELECT * FROM "TLO"."DEPT_SAL"
Registered qb: SEL$7991DADF 0x109ab640 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$7991DADF nbfros=1 flg=0
fro(0): flg=4 objn=154046 hint_alias="DEPT_SAL"@"SEL$7991DADF"
Registered qb: SEL$240F160A 0x10936a80 (MV REWRITE SEL$1; SEL$1; DEPT_SAL)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$240F160A nbfros=1 flg=0
fro(0): flg=0 objn=154046 hint_alias="DEPT_SAL"@"SEL$7991DADF"
OJE: Begin: find best directive for query block SEL$240F160A (#0)
OJE: End: finding best directive for query block SEL$240F160A (#0)
query block SEL$1 transformed to SEL$240F160A (#0)
- 첫 번째 Rewrite 힌트가 이러한 용도로 사용된 것이다.
Outline Data 의 REWRITE(@"SEL$240F160A" "DEPT_SAL") 가 그것이다.
Considering Query Transformations on query block SEL$240F160A (#0)
**************************
Query transformations (QT)
**************************
- 이어서 MVR 기능에 의해 새로 생성된 쿼리블럭 SEL$240F160A 의 Query Transformation 이 진행된다.
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
Registered qb: SEL$01E878FB 0x10936a80 (MV REWRITE SEL$240F160A; SEL$240F160A; DEPT_SAL)
Heuristic :CSE, OBYE, DE, SVM, JE, CNT, SSU, PM, FPD 등
- Heuristic Query Transformation 이 완료되면 새로운 쿼리블럭인 SEL$01E878FB 가 생성된다.
- 이때 다시 추가적인 Rewrite 과정이 있다.
- 첫 번째 Rewrtie 할 때 Shared Pool 에 저정된 단순한 형태의 SQL 이지만
- 두 번째는 완벽한 SQL 이 된다.
- 이때에도 내부적인 힌트를 사용하는데 Outline Data 의 REWRITE(@"SEL$1" "DEPT_SAL") 가 그것이다.
- WHERE 절의 FPD 의 과정을 거쳐야 완벽해진다.
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$01E878FB (#1)
kkqctdrvTD-start: :
call(in-use=27904, alloc=49080), compile(in-use=128656, alloc=132680), execution(in-use=4440, alloc=8088)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=27904, alloc=49080), compile(in-use=129344, alloc=132680), execution(in-use=4440, alloc=8088)
kkqctdrvTD-end:
call(in-use=27904, alloc=49080), compile(in-use=129744, alloc=132680), execution(in-use=4440, alloc=8088)
JPPD: Applying transformation directives
query block SEL$240F160A transformed to SEL$01E878FB (#1)
FPD: Considering simple filter push in query block SEL$01E878FB (#1)
"DEPT_SAL"."DEPARTMENT_ID"=10
try to generate transitive predicate from check constraints for query block SEL$01E878FB (#1)
finally: "DEPT_SAL"."DEPARTMENT_ID"=10
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "DEPT_SAL"."DEPARTMENT_ID" "DEPARTMENT_ID","DEPT_SAL"."DEPARTMENT_NAME" "DEPARTMENT_NAME","DEPT_SAL"."SALARY_SUM" "SALARY_SUM
" FROM "TLO"."DEPT_SAL" "DEPT_SAL" WHERE "DEPT_SAL"."DEPARTMENT_ID"=10
kkoqbc: optimizing query block SEL$01E878FB (#1)
- 최초에 Parse 로부터 받은 SQL 은 WHERE 절이 없었으므로 FPD 기능에 의한 WHERE 절 추가가 필요하다.
- 그래서 DEPT_SAL.DEPARTMENT_ID = 10 조건이 추가되었다.
- 연이어 Physical Optimization 이 진행된다.
*********************************
Number of join permutations tried: 1
*********************************
Enumerating distribution method (advanced)
Trying or-Expansion on query block SEL$01E878FB (#1)
Transfer Optimizer annotations for query block SEL$01E878FB (#1)
id=0 frofkks[i] (index start key) predicate="DEPT_SAL"."DEPARTMENT_ID"=10
id=0 frofkke[i] (index stop key) predicate="DEPT_SAL"."DEPARTMENT_ID"=10
Final cost for query block SEL$01E878FB (#1) - All Rows Plan:
Best join order: 1
Cost: 1.0003 Degree: 1 Card: 1.0000 Bytes: 19
Resc: 1.0003 Resc_io: 1.0000 Resc_cpu: 2927
Resp: 1.0003 Resp_io: 1.0000 Resc_cpu: 2927
- Physical Optimization 의 결과로 Mview 를 사용하는 Plan 의 Cost 는 1.0003 임을 알 수 있다.
- Mview 를 사용한 쿼리블럭의 최적화 과정이 모두 종료되었으므로 아래에서는 Mview 를 사용하지 않는
- 쿼리블럭의 최적화가 이루어진다. (양자간의 비교를 위해서 )
Copy query block qb# -1 (<unnamed>) : SELECT /*+ 10053 01 */ a.department_id, b.department_name, SUM(a.salary) salary_sum
FROM employee a, department b
WHERE a.department_id = b.department_id
AND b.department_id = 10
GROUP BY a.department_id, b.department_name
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT: Validity checks passed for 2du4w64ux11ky.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
- 원본 SQL 의 쿼리블럭 SEL$1 의 Transformation 이 진행되고 있다.
- Mview 를 사용한 Query 의 Cost 와 Mview를 사용하지 않고 정상적으로 Query Transformation 이 수행된 SQL 의 Cost 를 비교하기 위함이다.
*********************************
Number of join permutations tried: 1
*********************************
Consider using bloom filter between B[DEPARTMENT] and A[EMPLOYEE] with ??
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because not a hash join
Enumerating distribution method (advanced)
--- Distribution method for
join between B[DEPARTMENT](serial) and A[EMPLOYEE](serial); jm = 12; right side access path = IndexRange
---- NLJ default -> BROADCAST-LEFT
(newjo-save) [0 1 ]
SORT ressource Sort statistics
Sort width: 598 Area size: 536576 Max Area size: 104857600
Degree: 1
Blocks to Sort: 1 Row size: 36 Total Rows: 10
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 8863270
Total Temp space used: 0
Trying or-Expansion on query block SEL$1 (#1)
Transfer Optimizer annotations for query block SEL$1 (#1)
Final cost for query block SEL$1 (#1) - All Rows Plan:
Best join order: 1
Cost: 2.0005 Degree: 1 Card: 10.0000 Bytes: 230
- Mview 를 사용하지 않은 Plan의 Cost는 2.0005 로 나왔다.
- Mview 를 사용하는 Plan 의 Cost 비용이 낮으므로 옵티마이져는 Mview 를 선택
3.20. JBE ( Join Back Elimination ) : Bitmap Join Index 를 사용할 경우 필요 없는 테이블 액세스를 제거하라.
가. 개념
- Bitmap Join 인덱스 사용시 발생
- SELECT 절 및 WHERE 절에 사용된 모든 컬럼이 인덱스(인덱스 구성 칼럼, Covered Index)에 포함될 때 해당 테이블들을 Access 하지 않는다.
- JBE 기능은 주로 DW나 OLAP 에서 사용
나. 주의 사항
- Join Back Elimination 기능은 Star Transformation 이나 Group By Push Down, OR-Expansion 등과 마찬가지로 CBQT 를 제어하는 파라미터의 영향을 받지 않는다.
- 불완전한 CBQT 임. ( _optimizer_cost_based_transformation 에 의해서 제어 되지 않음 )
다. 파라미터
- Join Back Elimination 을 Control 하는 것은 파라미터가 아니라 Evnet 이다.
- 10163 Trace level Event 를 사용하면 기능을 On/Off 할 수 있다.
라. 예제
-- Bitmap Join 인덱스 생성
CREATE BITMAP INDEX emp_join_idx_01 ON employee ( d.location_id)
FROM employee e, department d
WHERE e.department_id = d.department_id ;
- SQL 을 보면 WHERE 절에 d.location_id 조건이 있는데 이렇게 조건이 있는 컬럼들로 Bitmap Join Index 를 구성해야 한다.
- 실제로 emp_join_idx_01 인덱스의 컬럼은 d.location_id 하나뿐이고 나머지는 조인의 조건이다.
SQL> SELECT /*+ gather_plan_statistics INDEX(e emp_join_idx_01) */ COUNT(*)
2 FROM employee e, department d
3 WHERE e.department_id = d.department_id
4 AND d.location_id = 1700 ;
COUNT(*)
----------
18
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7ajwk26jq24mz, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX(e emp_join_idx_01) */ COUNT(*)
FROM employee e, department d WHERE e.department_id =
d.department_id AND d.location_id = 1700
Plan hash value: 518796859
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | BITMAP CONVERSION COUNT | | 1 | 15 | 1 |00:00:00.01 | 1 |
|* 3 | BITMAP INDEX SINGLE VALUE| EMP_JOIN_IDX_01 | 1 | | 1 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."SYS_NC00012$"=1700)
22 rows selected.
- Employee 테이블과 department 테이블을 전혀 Acces 하지 않고 조인도 발생하지 않았다.
- emp_join_idx_01 인덱스만 액세스 했으므로 완벽하게 JBE 가 수행된 것이다.
마. 10053 Trace
- 내부적으로 어떻게 된 것일까? 정답은 Outline Data 에 있다.
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_index_cost_adj' 20)
ALL_ROWS
OUTLINE_LEAF(@"SEL$DBFB62D6")
ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")
OUTLINE(@"SEL$1")
BITMAP_TREE(@"SEL$1" "E"@"SEL$1" AND(("DEPARTMENT"."LOCATION_ID")))
END_OUTLINE_DATA
*/
- JBE를 수행하기 위해 오라클은 내부적으로 BITMAP_TREE 힌트를 사용하였으며
- JE(Join Elimination) 기능을 사용한 것을 볼 수 있다.
- 다시 말하면 JE 기능에 의하여 department 테이블과 조인이 제거되어 Bitmap Join 인덱스만 Access 한 것이다.
***************************************
OST: STAR TRANSFORMATION PLANS
***************************************
Best join order so far: 2
Marked for join-back elimination: "D"@"SEL$1"
Join order[1]: EMPLOYEE[E]#0
***********************
Best so far: Table#: 0 cost: 1.0002 card: 15.2857 bytes: 45
***********************
OST: oldrsc = 2.500834, oldcst = 2.500834, rsc = 1.000165, cst = 1.000165, minRatio = 0
Registered qb: SEL$DBFB62D6 0x10936a80 (BITMAP JOIN INDEX JOINBACK ELIMINATION SEL$1; SEL$1; "D"@"SEL$1")
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$DBFB62D6 nbfros=1 flg=0
fro(0): flg=0 objn=153717 hint_alias="E"@"SEL$1"
OST: ST plan accepted
- JBE 기능의 변환은 10053 Trace 에서는 STAR TRANSFORMATION PLANS 자리에 나타난다.
- JBE가 발생하여 새로운 쿼리블럭인 SEL$DBFB62D6 가 생성되었다.
- 물론 이러한 과정은 Cost Based Query Transformation 의 특징인 JBE 가 수행된 Cost 와 수행되지 않은 Cost 를 계산하여 비교 하지만...
- 결론적으로 JBE 가 수행된 쿼리블럭의 Cost 가 1.0003 으로 가장 낮으므로 JBE 가 선택된 것이다. ( Trace 내용 중에 없음... )
- "ST plan accepted"라는 메시지에서 결론을 알 수 있다.
- 위에서 수행된 실행 계획은 완벽한 JBE 가 수행되어 테이블 Access 가 전혀 없지만 항상 그렇게 되는 것은 아니다.
- Group By 절에 인덱스 컬럼인 d.location_id 컬럼을 추가 했을 뿐인데 JBE 가 수행되지 않았다.
- 아직 JBE 기능이 완벽히 동작하지 않음을 알 수 있다.
- 오라클 메타링크 사이트를 조회해보면 많은 수의 버그들이 존재한다.
- 따라서 항상 실행 계획을 확인하여 JBE가 동작하는지 검증해야 할 것이다.
SQL> SELECT /*+ gather_plan_statistics INDEX(e emp_join_idx_01) */ d.location_id, COUNT(*)
2 FROM employee e, department d
3 WHERE e.department_id = d.department_id
4 AND d.location_id = 1700
5 GROUP BY d.location_id ;
LOCATION_ID COUNT(*)
----------- ----------
1700 18
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------
SQL_ID 8m4602a0tphbz, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX(e emp_join_idx_01) */
d.location_id, COUNT(*) FROM employee e, department d WHERE
e.department_id = d.department_id AND d.location_id = 1700 GROUP BY
d.location_id
Plan hash value: 4010296542
----------------------------------------------------------------------------------------------------------------------------------------
| 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 | SORT GROUP BY NOSORT | | 1 | 1 | 1 |00:00:00.01 | 4 | | | |
|* 2 | HASH JOIN | | 1 | 5 | 18 |00:00:00.01 | 4 | 1517K| 1517K| 1003K (0)|
| 3 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 15 | 18 |00:00:00.01 | 2 | | | |
| 4 | BITMAP CONVERSION TO ROWIDS| | 1 | | 18 |00:00:00.01 | 1 | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | EMP_JOIN_IDX_01 | 1 | | 1 |00:00:00.01 | 1 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 4 | 21 |00:00:00.01 | 2 | | | |
|* 7 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 4 | 21 |00:00:00.01 | 1 | | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
5 - access("E"."SYS_NC00012$"=1700)
7 - access("D"."LOCATION_ID"=1700)
29 rows selected