Optimizing Oracle Optimizer (2009년)
Peculiar Range Scan
- 대부분의 Index Key 가 삭제된 경우
- Oracle 은 조건에 해당하는 Key 값들이 삭제된 것을 미리 앍고 불필요한 Range Scan 을 피할 수 있는가?
- 불행히도 Oracle 은 Leaf Block 을 방문하지 않고서는 Key 의 삭제 여부를 알 수 없다.
- 따라서 비록 Leaf Block 이 모두 비어 있지만 검색 대상이 되는 모든 Leaf Block 을 다 방문해 보아야 한다.
SYS> drop table t1 purge ;
Table dropped.
SYS> create table t1 ( c1 int, c2 int );
Table created.
SYS> create index t1_n1 on t1 ( c1 ) ;
Index created.
SYS> insert into t1 select level, level from dual connect by level <= 10000 ;
10000 rows created.
SYS>@gather t1
PL/SQL procedure successfully completed.
가.1 COUNT(C1)
SYS> select /*+ gather_plan_statistics index(t1) */ count(c1) from t1 where c1 >= 5 ;
COUNT(C1)
----------
9996
before delete
SYS>@stat
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 20 |
|* 2 | INDEX RANGE SCAN| T1_N1 | 1 | 9997 | 20 (0)| 9996 |00:00:00.01 | 20 |
after delete
SYS>delete from t1 where c1 >= 1 ;
10000 rows deleted.
나.1 COUNT(C1)
SYS>select /*+ gather_plan_statistics index(t1) */ count(c1) from t1 where c1 >= 5 ;
SYS>@stat
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 20 |
|* 2 | INDEX RANGE SCAN| T1_N1 | 1 | 9997 | 20 (0)| 0 |00:00:00.01 | 20 |
Index Tree Dump 시도
SYS> exec tree_dump2(v_owner =>'sys',v_name=>'t1_n1');
----- begin tree dump
branch: 0x40ef32 4255538 (0: nrow: 19, level: 1)
leaf: 0x40ef33 4255539 (-1: nrow: 540 rrow: 540)
leaf: 0x40ef34 4255540 (0: nrow: 533 rrow: 533)
leaf: 0x40ef35 4255541 (1: nrow: 533 rrow: 533)
leaf: 0x40ef36 4255542 (2: nrow: 533 rrow: 533)
leaf: 0x40ef37 4255543 (3: nrow: 533 rrow: 533)
leaf: 0x40ef38 4255544 (4: nrow: 533 rrow: 533)
leaf: 0x40f491 4256913 (5: nrow: 533 rrow: 533)
leaf: 0x40f492 4256914 (6: nrow: 533 rrow: 533)
leaf: 0x40f493 4256915 (7: nrow: 533 rrow: 533)
leaf: 0x40f494 4256916 (8: nrow: 533 rrow: 533)
leaf: 0x40f495 4256917 (9: nrow: 533 rrow: 533)
leaf: 0x40f496 4256918 (10: nrow: 533 rrow: 533)
leaf: 0x40f497 4256919 (11: nrow: 533 rrow: 533)
leaf: 0x40f498 4256920 (12: nrow: 533 rrow: 533)
leaf: 0x40f4a1 4256929 (13: nrow: 533 rrow: 533)
leaf: 0x40f4a2 4256930 (14: nrow: 533 rrow: 533)
leaf: 0x40f4a3 4256931 (15: nrow: 533 rrow: 533)
leaf: 0x40f4a4 4256932 (16: nrow: 533 rrow: 533)
leaf: 0x40f4a5 4256933 (17: nrow: 399 rrow: 399)
----- end tree dump
Index 정보
SYS>@dic t1_n1
OWNER : SYS
INDEX_NAME : T1_N1
INDEX_TYPE : NORMAL
TABLE_OWNER : SYS
TABLE_NAME : T1
TABLE_TYPE : TABLE
UNIQUENESS : NONUNIQUE
COMPRESSION : DISABLED
PREFIX_LENGTH :
TABLESPACE_NAME : SYSTEM
INI_TRANS : 2
MAX_TRANS : 255
INITIAL_EXTENT : 65536
NEXT_EXTENT :
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
PCT_THRESHOLD :
INCLUDE_COLUMN :
FREELISTS : 1
FREELIST_GROUPS : 1
PCT_FREE : 10
LOGGING : YES
BLEVEL : 1
LEAF_BLOCKS : 19 <======== Leaf Blocks 수
DISTINCT_KEYS : 10000
AVG_LEAF_BLOCKS_PER_KEY : 1
AVG_DATA_BLOCKS_PER_KEY : 1
CLUSTERING_FACTOR : 18
가.2 C1
SYS> select /*+ gather_plan_statistics index(t1) */ c1 from t1 where c1 >= 5 ;
SYS>@stat
before delete
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
|* 1 | INDEX RANGE SCAN| T1_N1 | 1 | 9997 | 20 (0)| 9996 |00:00:00.01 | 686 |
-------------------------------------------------------------------------------------------------
after delete
SYS> delete from t1 where c1 >= 1 ; -- 이미 테스트 가.1.C1 에서 삭제함 - Remind 차원
10000 rows deleted.
나.2 C1
SYS> select /*+ gather_plan_statistics index(t1) */ c1 from t1 where c1 >= 5 ;
SYS> @stat
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
|* 1 | INDEX RANGE SCAN| T1_N1 | 1 | 9997 | 20 (0)| 0 |00:00:00.01 | 20 |
-------------------------------------------------------------------------------------------------
- 대부분의 Data 를 삭제한 후에는 Fetch 할 Row 가 없으므로 Fetch Call 의 회수에 의한 추가적인 Block 방문이 사라진다.
- 즉, Data 를 삭제하기 전의 Logical Reads 686은 9997 Rows 의 Fetch 에 의해 발생한 것이며, Data 를 삭제한 후에는 Fetch 가 단 1회에 불과하므로 Logical Reads 가 20 에서 증가하지 않는 것이다.
- 1. Index Key 를 삭제(Delete) 해도 Leaf Block 의 수는 동일하다.(위의 예제에서는 20).
- 2. 따라서 읽어야 할 Index Leaf Block 의 수도 동일하다.
- 즉. Index Key를 아무리 삭제해도 모든 Leaf Block 들은 다 탐색 대상이 된다.
- 3. 하지만 Key 가 삭제된 경우에는 실제 "Fetch"가 발생하지 않기 때문에 Fetch Call 회수가 줄어든다.
- 따라서 줄어든 Fetch CAll 회수만큼의 성능 개선 효과가 있을 수 있다.
Remind
- Index Key 를 아무리 삭제해도 Leaf Block 의 수는 줄어들지 않는다는 것을 유의하자.
- Leaf Block 의 수를 줄이는 방벙은 Coalesce 나 Rebuild, Shrink 명령을 수행하는 것 뿐이다.
SYS>drop table t1 purge ;
Table dropped.
SYS>create table t1 ( c1 int, c2 int );
Table created.
SYS>create index t1_n1 on t1 ( c1 ) ;
Index created.
SYS>insert into t1 select level, level from dual connect by level <= 10000 ;
10000 rows created.
SYS>@gather t1
PL/SQL procedure successfully completed.
SYS>select /*+ gather_plan_statistics index(t1) */ c1 from t1 where c1 >= 5 ;
SYS>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
순수한 세션별 통계정보 수집을 위해서 Exit
SYS> @mysid
SYS> @mon_on &v_sid
SYS> select /*+ gather_plan_statistics index(t1) */ c1 from t1 where c1 >= 5 ;
SYS> @mon_off
SYS> select t1.name as name, sum(t1.value) as value1 from t_mon_temp t1
where t1.name in ('buffer is pinned count','session logical reads','user calls')
group by t1.name ;
NAME VALUE1
---------------------------------------------------------------- ----------
user calls 709
session logical reads 781
buffer is pinned count 0
SYS>delete from t1 where c1 >= 1 ;
10000 rows deleted.
SYS>exit
순수한 세션별 통계정보 수집을 위해서 Exit
SYS>@mysid
SYS>@mon_on &v_sid
SYS> select /*+ gather_plan_statistics index(t1) */ c1 from t1 where c1 >= 5 ;
SYS> @mon_off
SYS> select t1.name as name, sum(t1.value) as value1 from t_mon_temp t1
where t1.name in ('buffer is pinned count','session logical reads','user calls')
group by t1.name ;
NAME VALUE1
---------------------------------------------------------------- ----------
user calls 42
session logical reads 98
buffer is pinned count 0
Nested Loops Join 과 Index Range Scan
- Index Range Scan 이 Nested Loops Join 과 함께 사용되는 경우에는 상황이 더 고약해진다.
select /*+ leading(t1) use_nl(t1 t2) index(t2) */ *
from t1, t2
where t1.c1 = t2.c1 ;
TABLE ACCES BY INDEX ROWID T2
NESTED LOOPS
TABLE ACCESS FULL T1
INDEX RANGE SCAN T2_NI
 |
- Table t1 에서 첫 번째 Block (t1#1)을 읽는다. Logical Reads 가 1 증가한다.
- Row=3 에 해당하는 값을 Table t2 에서 찾기 위해 index 를 Root / Branch / Leaf Block 순으로 Table t2 의 Block(t2#2)을 방문한다. Logical Reads 가 4증가한다. ( Root + Branch + Leaf + Table )
- Row = 2, 10 에 대해 동일한 작업을 반복한다.
- Table t1 에서 두 번째 Block(t1#2) 을 읽는다. Logical Reads 가 1 증가한다.
- Row = 1, 6, 7 에 대해 index Root/Branch/Leaf Block + Table Block 방문을 반복한다.
- 위의 과정을 Table t1 에서 조건을 만족하는 Row 만큼 반복한다.
- 이 과정을 보면 Nested Loop Join 이 넓은 범위의 Data 를 읽는 것이 얼마나 비효율적인지 알 수 있다.
- Oracle 이 넓은 범위의 Data를 읽을 때 필사적으로 Hash Join 을 선호하는 이유가 다른 데 있는 것이 아니다.
- Oracle 도 이런 Nested Loops Join 의 비효율성을 잘 알고 있으며, 이를 조금이라도 개선시키기 위해 노력하고 있다. 아래에 그 증거가 있다.
SYS>drop table t1 purge ;
Table dropped.
SYS>drop table t2 purge ;
Table dropped.
SYS>create table t1 (c1 int, c2 int ) ;
Table created.
SYS>create table t2 ( c1 int , c2 int );
Table created.
SYS>create index t2_n1 on t2 ( c1) ;
Index created.
10G
SYS>insert into t1 select level, level from dual connect by level <= 10000 ;
10000 rows created.
SYS>insert into t2 select level, level from dual connect by level <= 10000 ;
10000 rows created.
8i
SQL> begin for i in 1..10000 loop
2 insert into t1 values ( i, i) ;
3 end loop;
4 commit ;
5 end ;
6 /
PL/SQL procedure successfully completed.
SQL> begin for i in 1..10000 loop
2 insert into t2 values ( i, i) ;
3 end loop;
4 commit ;
5 end ;
6 /
PL/SQL procedure successfully completed
9i
SQL> alter session set sql_trace = true ;
SQL> select /*+ leading(t1) use_nl(t1 t2) index(t2) */ * from t1, t2 where t1.c1 = t2.c1 ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1001 0.08 0.16 2 23036 4 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1003 0.08 0.16 2 23036 4 10000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
10000 NESTED LOOPS
10001 TABLE ACCESS FULL T1
10000 TABLE ACCESS BY INDEX ROWID T2
20000 INDEX RANGE SCAN (object id 415897)
10G
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 49 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 668 0.14 0.13 0 12728 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 0.15 0.14 0 12777 0 10000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
10000 TABLE ACCESS BY INDEX ROWID T2 (cr=12728 pr=0 pw=0 time=190220 us)
20001 NESTED LOOPS (cr=12043 pr=0 pw=0 time=100056 us)
10000 TABLE ACCESS FULL T1 (cr=690 pr=0 pw=0 time=20023 us)
10000 INDEX RANGE SCAN T2_N1 (cr=11353 pr=0 pw=0 time=78354 us)(object id 53549)
- Logical Reads 가 크게 개선되었다. 그 이유는 앞서 언급한 Buffer Pinning 때문이다.
- 한번 Fetch Call 에서 반복적으로 읽는 Block 은 Logical Reads 가 추가적으로 발생하지 않는다.
- Nested Loops Join 이 그만큼 효율적으로 변한 것이다.
반면, Oracle 11g 에서의 일량은 다음과 같다.
- Buffer Pinning 에서 한가지 주의할 것이 있다.
- 비록 Buffer Pinning 에 의해 Logical Reads 가 줄어든 것으로 보고 되지만, 실제로는 읽기 작업의 오버헤드가 어느 정도는 존재한다는 것이다.
- Oracle은 Block 을 Buffer Caceh 에 Pin(고정) 시키고, 추가적인 Latch 획득이나 Chain 의 탐색 없이 Block 을 다시 읽는다.
- 따라서, 읽기 작업의 오버헤드가 완전히 없어지지는 않는다.
- Nested Loops Join(혹은 In List) Operation 이 Index Range Scan 을 수반하는 경우, Index Height 가 성능의 중요한 걸림돌이 된다.
- 비록 Oracle 이 Buffer Pinning 을 통해 Overhead 를 줄이기 위해 노력하지만 태생적인 비효율은 피할 수 없다.
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4429
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.