Index Range Scan (by jongmali) [2009.04.30]
[ Index Range Scan ]
가장 보편적으로 사용되는 Operation 이다.
"Index 를 경유한다" 는 표현은 "Index Range Scan"을 수행한다는 것과 거의 동일한 의미
SYS> drop table t1 purge ;
Table dropped.
SYS> create table t1 ( c1 number, c2 number ) ;
Table created.
SYS> create index xak1_t1 on t1 ( c1 ) ;
Index created.
SYS>begin
for i in 1 .. 12 loop
insert into t1 values ( i, i );
commit ;
end loop ;
end ;
/
PL/SQL procedure successfully completed.
SYS>execute dbms_stats.gather_table_stats(ownname => user , tabname =>'t1');
PL/SQL procedure successfully completed.
SYS> select /*+ gather_plan_statistics */ c1 from t1 where c1 >= 5 ;
C1
----------
5
6
7
8
9
10
11
12
8 rows selected.
SYS> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID a0kg1rkv32nfg, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ c1 from t1 where c1 >= 5
Plan hash value: 3186761381
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
|* 1 | INDEX RANGE SCAN| XAK1_T1 | 1 | 9 | 8 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1">=5)
17 rows selected.
[ PAGE 153 그림설명 ]
1. c1 >= 5 조건에 해당 하는 Key 값을 찾기 위해 Index Root Boock(i#1)과 Branch Block(i#2)을 탐색한다.
Logical Reads 가 2 증가한다.
2. c1 >= 5 조건을 만족하는 첫 번째 leaf Block(i#5)을 방문한다.
Logical Reads 가 1 증가한다. 조건을 만족하는 key 값으로 5,6 을 추출해서 Return 한다.
3. 다음 번 Key 7을 추출하기 위해 Leaf Block (i#6)을 방문한다.
Logical Reads 가 1 증가한다. Key 값 7,8,9 을 추출해서 Return 한다.
이 과정을 탐색이 끝날 때까지 계속한다.
Oracle 이 사용하는 기본적인 방식은 하나의 Leaf Block 에 속한 Key 값들을 한번에
읽어 들이는 것이다. 이러한 가정으로 Cost를 계산한다.
그렇다면 Index Range Scan 에 의해 발생하는 Logical Reads 의 최대값은 Leaf Block의 수와 일치하는가 ?
SYS> drop table t1 purge ;
Table dropped.
SYS> create table t1 ( c1 number, c2 number ) ;
Table created.
SYS> create index xak1_t1 on t1 ( c1 ) ;
Index created.
SYS> begin
for i in 1 .. 10000 loop
insert into t1 values ( i, i );
commit ;
end loop ;
end ;
/
SYS> exec dbms_stats.gather_table_stats(ownname => user, tabname =>'t1');
PL/SQL procedure successfully completed.
SYS> select /*+ gather_plan_statistics index(t1) */ c1 from t1 where c1 >= 5 ;
...
10000
9996 rows selected.
SYS>!more stat.sql
select * from table
(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SYS>@stat.sql
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID 4j0t2rwbwjdux, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t1) */ c1 from t1 where c1 >= 5
Plan hash value: 3186761381
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|* 1 | INDEX RANGE SCAN| XAK1_T1 | 1 | 9997 | 20 (0)| 9996 |00:00:00.01 | 686 |
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1">=5)
17 rows selected.
9996 개의 Key 를 추출하며 Logical Reads(Buffers) 는 686 이다.
686 은 XAK1_T1 의 Leaf Block Count 가 아니다. \!\!\!
Cost 가 20 인 이유는 Root Block (1) + LEAF BLOCK COUNT(19) = 20 이기 때문이다.
SYS>@dic.sql
Enter value for 1: XAK1_T1
OWNER : SYS
INDEX_NAME : XAK1_T1
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
DISTINCT_KEYS : 10000
AVG_LEAF_BLOCKS_PER_KEY : 1
AVG_DATA_BLOCKS_PER_KEY : 1
CLUSTERING_FACTOR : 18
STATUS : VALID
NUM_ROWS : 10000
SAMPLE_SIZE : 10000
LAST_ANALYZED : 08-MAR-09
DEGREE : 1
INSTANCES : 1
PARTITIONED : NO
TEMPORARY : N
GENERATED : N
SECONDARY : N
BUFFER_POOL : DEFAULT
USER_STATS : NO
DURATION :
PCT_DIRECT_ACCESS :
ITYP_OWNER :
ITYP_NAME :
PARAMETERS :
GLOBAL_STATS : YES
DOMIDX_STATUS :
DOMIDX_OPSTATUS :
FUNCIDX_STATUS :
JOIN_INDEX : NO
IOT_REDUNDANT_PKEY_ELIM : NO
DROPPED : NO
-----------------
PL/SQL procedure successfully completed.
Logical Reads 값인 686 추적...
SYS>select /*+ gather_plan_statistics index(t1) */ count(*) from t1 where c1 >= 5 ;
COUNT(*)
----------
9996
SYS>@stat.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 7s12nm5rtga08, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t1) */ count(*) from t1 where c1 >= 5
Plan hash value: 2215041688
----------------------------------------------------------------------------------------------------
| 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| XAK1_T1 | 1 | 9997 | 20 (0)| 9996 |00:00:00.01 | 20 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1">=5)
18 rows selected.
Index 의 Key 를 일일이 Fetch 하지 않고 Count 값만 가져오는 경우 Logical Reads 는 20이다.
이 값은 Index 의 Root Block + Leaf Block 수와 완벽하게 일치한다.
즉 한번의 Fetch 만으로 Leaf Block 을 Range Scan 하는 경우에는 Leaf Block 의
수만큼만 Read 가 발생한다. [ 중요한건 Fetch Count ]
이 말을 거꾸로 해석하면 Fetch Call 단위가 바뀔 때마다 매번 Block 을 새로 읽어야
한다는 의미이다.
SQL*PLUS 의 Fetch Array Size 는 15이다.
즉, 9996 개의 Rows를 읽으려면 666(=9996/15)번의 추가적인 BLOCK 방문이
필요하다. 666(추가방문)+20(LEAF BLOCK 수) = 686 이 된다.
이 686 이 Logical Reads 의 수가 된다.
SYS>show array
arraysize 15
Sets the number of rows, called a batch,
that SQL*Plus will fetch from the database at one time.
SYS> show array
arraysize 15
SYS> set arraysize 1
SYS> select /*+ gather_plan_statistics index(t1) */ c1 from t1 where c1 >= 5 ;
SYS> @stat
Plan hash value: 3186761381
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|* 1 | INDEX RANGE SCAN| XAK1_T1 | 1 | 9997 | 20 (0)| 9996 |00:00:00.01 | 5009 |
---------------------------------------------------------------------------------------------------
Buffers = (9996/2) + 20 = 4998 + 20 = 5018 [ 약간 다르네 ㅇ_ㅇ;;]
Range Scan 의 경우 Orale 이 내부적으로 Fetch Array Size 의 최소값을 2로
제한 하기 때문이다. 중요한 것은 Fetch Call 의 회수, 즉 Fetch Array Size 가
Index Range Scan 의 Leaf Block 방문 회수를 결정한다는 것이다.
SYS> set arraysize 10
SYS> select /*\+ gather_plan_statistics index(t1) \*/ c1 from t1 where c1 >= 5 ;
SYS> @stat
Plan hash value: 3186761381
\--------------------------------------------------------------------------------------------------\-
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU) | A-Rows | A-Time | Buffers |
\--------------------------------------------------------------------------------------------------\-
| * 1 | INDEX RANGE SCAN | XAK1_T1 | 1 | 9997 | 20 (0) | 9996 | 00:00:00.01 | 1018 |
\--------------------------------------------------------------------------------------------------\-
Buffers = (9996/10) + 20 = 999.6 + 20 = 1019.6
SYS>set arraysize 100
SYS>select /*+ gather_plan_statistics index(t1) */ c1 from t1 where c1 >= 5 ;
SYS> @stat
Plan hash value: 3186761381
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|* 1 | INDEX RANGE SCAN| XAK1_T1 | 1 | 9997 | 20 (0)| 9996 |00:00:00.01 | 119 |
---------------------------------------------------------------------------------------------------
Buffers = (9996/100) + 20 = 99.96 + 20 = 119.96
SYS>set arraysize 1000
SYS>select /*+ gather_plan_statistics index(t1) */ c1 from t1 where c1 >= 5 ;
SYS> @stat
Plan hash value: 3186761381
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|* 1 | INDEX RANGE SCAN| XAK1_T1 | 1 | 9997 | 20 (0)| 9996 |00:00:00.01 | 30 |
---------------------------------------------------------------------------------------------------
Buffers = (9996/100) + 20 = 9.996 + 20 = 29.996
\*\!\! Buffers 의 차이는 존재하나 모든 Cost 가 20 이다. *
SYS > @mon_init
SYS>@mysid
SID
----------
145
SYS>@mon_on &v_sid
SYS>@mon_show2
SYS>set arraysize 1
SYS>select /*+ gather_plan_statistics index(t1) */ c1 from t1 where c1 >= 5 ;
..
9996 rows selected.
SYS>@mon_off
SYS>set arraysize 1000
SYS>select /*+ gather_plna_statistics index(t1) */ c1 from t1 where c1 >= 5 ;
SYS>@mon_off2
SYS>@mon_show2
01. statistics
NAME DIFF VALUE2 VALUE1
----------------------------------------------- ------------ ------------ ------------
session pga memory max 1,441,792 528,072 1,969,864
session uga memory max 1,310,144 156,628 1,466,772
bytes sent via SQL*Net to client 700,423 2,511 702,934
session uga memory 65,464 91,164 156,628
bytes received via SQL*Net from client 56,541 1,462 58,003
sorts (rows) 10,090 0 10,090
=========================================================================================
session logical reads 5,077 5 5,082
user calls 5,008 16 5,024
=========================================================================================
consistent gets 5,018 4 5,022
consistent gets from cache 5,018 4 5,022
SQL*Net roundtrips to/from client 5,003 10 5,013
no work - consistent read gets 5,008 2 5,010
=========================================================================================
buffer is not pinned count 4,989 0 4,989
=========================================================================================
\- Page 160
1. Index Range Scan 은 정렬된 순서로 Key 를 추출한다.
따라서, Order by 를 대신할 수 있다.
2. Index Range Scan 에서 Index 의 Leaf Block 을 방문하는 회수는 Fetch Call 의
회수에 따라 변한다. 단. Fetch Array Szie 의 최소값은 2이다.
3. 하지만, Cost 계산에서는 Fetch Call 회수는 고려되지 않는다.
Index Range Scan 의 Cost 는 방문해야 할 Block 수에 의해 결정된다.
4. Leaf Block 의 탐색은 연속적인 Single Block I/O 로 이루어진다.
Single Block I/O 는 Random Access I/O 이다.
따라서, 긴 길이의 Leaf Block Chain 을 탐색하는 것은 과다한 Random Access I/O
을 유발하여 성능에 불리하다.
==> 연속해서 Leaf Block 을 읽을지라로, Index Fast Full Scan 경우를 제외하곤
항상 Single Block I/O 로 수행되기 된다.
5. Index 의 높이는 Index Range Scan 의 성능에 큰 영향을 주지 않는다.
일단, 첫 번째 Leaf Block 을 찾고 나면 더 이상 Root Block 과 Branch Block 을
방문할 필요가 없기 때문이다.
Index Range SCan 과 함계 ROWID 를 이용해 Table Row 를 추출하는 경우는 어떠한가 ?
기본적인 동작 방식은 앞서 설명한 Index Range Scan 의 동작 방식과 거의 동일하다.
각 Index Key 에 해당하는 Row 를 찾기 위해 Table Row 를 ROWID 를 이용해 추가적으로
탐색하는 과정이 있다는 차이가 있을 뿐이다.
Fetch Call 의 회수가 Logical Reads 에 미치는 영향을 이 경우에도 비슷하게 관찰된다.
SYS>explain plan for
select c1, c2 from t1 where c1 >= 5 and c1 <= 9 ;
Explained.
SYS>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3393034826
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 42 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 42 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | XAK1_T1 | 6 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
2 - access("C1">=5 AND "C1"<=9)
14 rows selected.
SYS>set arraysize 1
SYS> select /*+ gather_plan_statistics index(t1) */ c2 from t1 where c1> 5 ;
SYS>@stat
Plan hash value: 3393034826
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 9996 | 39 (3)| 9995 |00:00:00.04 | 10014 |
|* 2 | INDEX RANGE SCAN | XAK1_T1 | 1 | 9996 | 20 (0)| 9995 |00:00:00.02 | 5008 |
TABLE Buffers = (9995/2) + 20 = 4997.5 + 20 = 5017.5 + 5017.5(INDEX BUFFERS) = 10035
INDEX Buffers = (9995/2) + 20 = 4997.5 + 20 = 5017.5
SYS>set arraysize 1000
SYS>select /*+ gather_plan_statistics index(t1) */ c2 from t1 where c1> 5 ;
SYS>@stat
Plan hash value: 3393034826
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 9996 | 39 (3)| 9995 |00:00:00.04 | 58 |
|* 2 | INDEX RANGE SCAN | XAK1_T1 | 1 | 9996 | 20 (0)| 9995 |00:00:00.02 | 30 |
TABLE Buffers = (9995/1000) + 20 = 9.995 + 20 = 29.995 + 29.995(INDEX BUFFERS) = 59.99
INDEX Buffers = (9995/1000) + 20 = 9.995 + 20 = 29.995
참고 테이블 정보
\-\- Table 정보 조회 블록 갯수 20 개 유의
SYS> @dic.sql t1
OWNER : SYS
TABLE_NAME : T1
TABLESPACE_NAME : SYSTEM
CLUSTER_NAME :
IOT_NAME :
STATUS : VALID
PCT_FREE : 10
PCT_USED : 40
INI_TRANS : 1
MAX_TRANS : 255
INITIAL_EXTENT : 65536
NEXT_EXTENT :
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
FREELISTS : 1
FREELIST_GROUPS : 1
LOGGING : YES
BACKED_UP : N
NUM_ROWS : 10000 <=== Rows 수는 10,000
BLOCKS : 20 <=== Block 수는 20
EMPTY_BLOCKS : 0
AVG_SPACE : 0
CHAIN_CNT : 0
AVG_ROW_LEN : 7
AVG_SPACE_FREELIST_BLOCKS : 0
NUM_FREELIST_BLOCKS : 0
DEGREE : 1
INSTANCES : 1
CACHE : N
TABLE_LOCK : ENABLED
SAMPLE_SIZE : 10000
LAST_ANALYZED : 08-MAR-09
PARTITIONED : NO
IOT_TYPE :
TEMPORARY : N
SECONDARY : N
NESTED : NO
BUFFER_POOL : DEFAULT
ROW_MOVEMENT : DISABLED
GLOBAL_STATS : YES
USER_STATS : NO
DURATION :
SKIP_CORRUPT : DISABLED
MONITORING : YES
CLUSTER_OWNER :
DEPENDENCIES : DISABLED
COMPRESSION : DISABLED
DROPPED : NO
-----------------
PL/SQL procedure successfully completed.
SYS> select owner, table_name, BLOCKS from dba_tables where owner='SYS' and table_name ='T1';
OWNER TABLE_NAME BLOCKS
------------------------------ ------------------------------ ----------
SYS T1 20
SYS> select OWNER, SEGMENT_NAME, SEGMENT_TYPE, BLOCKS from dba_segments
where owner='SYS' and SEGMENT_NAME ='T1'
OWNER SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------------ ---------------------------------------- ------------------ ----------
SYS T1 TABLE 24
대부분의 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.
{code:SQL}
*나.2 C1*
{code:SQL}
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
- 8i *
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 를 줄이기 위해 노력하지만
태생적인 비효율은 피할 수 없다.
Hints and Parameters
Index Range Scan 을 제어하는 전통적인 Hint 들은 다음과 같다.
- INDEX : Index 사용 여부를 지정한다. Index 이름을 지정하지 않는 경우에는
Optimizer 가 가장 적당한 Index 를 선택하도록 한다. - INDEX_ASC : Index 를 오름 차순(Ascedning)으로 방문하도록 지정한다.
- INDEX_DESC : Index 를 내림 차순(Descending)으로 방문하도록 지정한다.
Oracle 10g 부터 INDEX Hint 를 지정하는 새로운 Naming Convention 이 소개되었다.
전통적인 INDEX Hint
select /*+ index(t1 t1_n1) */ ...
반면 Oracle 10g 부터는 Index 이름이 아닌 Column 이름을 지정하는 것이 가능해졌다.
select /*+ index(t1 t1(c1)) */ ....
select /*+ index(t1 t1(c1,c2)) */ ...
SYS>@gather t2
PL/SQL procedure successfully completed.
SYS>select /*+ gather_plan_statistics index(t2 t2(c1)) */ count(c1) from t2 where c1 > 9950 ;
COUNT(C1)
----------
50
SYS>@stat
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID 0dnp8xgfnb9u9, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t2 t2(c1)) */ count(c1) from t2 where c1 > 9950
Plan hash value: 622415417
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN| T2_N1 | 1 | 50 | 2 (0)| 50 |00:00:00.01 | 2 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1">9950)
Note
-----
- dynamic sampling used for this statement
22 rows selected.
SYS>@check_table_and_indexes.sql sys t2
TABLE_NAME ROW_S BLOCKS EMPTY_BLOCKS AVG_ROW_LEN SAMPLE_SIZE ANA
-------------------- ---------- ---------- ------------ ----------- ----------- -----------------
T2 10000 20 0 7 10,000 20090308 16:41:40
INDEX_NAME ROW_S BLEVEL LEAF_B DIS_KEY KEYPERLEAF CLUSETERING SAMPLE_SIZE ANA
-------------------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- -----------------
T2_N1 10000 1 19 10000 1 18 10,000 20090308 16:41:40
C_NAME DATA_TYPE D_L NULL NUM_DISTINCT DEN NUM_NULLS NUM_BUC ANA SAMPLE_SIZE USER_STATS HIS
-------------------- ---------- ---- ---- ------------ ---- ------------ ------- ----------------- ------------ ---------- ---------------
C1 NUMBER 22 Y 10,000 0 0 1 20090308 16:41:40 10,000 NO NONE
C2 NUMBER 22 Y 10,000 0 0 1 20090308 16:41:40 10,000 NO NONE
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_ACTUAL_VALU
------------------------------ --------------- --------------------
C1 1
C1 0
C2 1
C2 0
SYS>show parameters OPTIMIZER_DYNAMIC_SAMPLING
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
Oracle 10.2.0.3 부터는 Index Range Scan 을 "직접" 지정할 수 있는 Hint 가 추가 되었다.
INDEX_RS, INDEX_RS_ASC, INDEX_RS_DESC Hint 가 그것이다.
이런 Hint 가 추가된 이유는 INDEX Hint 의 애매 모호성 때문이다.
==> 구체적으로 Index Range Scan 을 타도록 확실히
문서에 대하여
- 최초작성일 : 2009년 2월 14일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 조동욱님의 'Optimizing Oracle Optimizer'을 참고하였습니다.*