Optimizing Oracle Optimizer (2009년)
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
- 테이블 정보 조회 - 블록 갯수 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
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4428
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.