by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

Index Range Scan




[ 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

[ 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.

{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 을 타도록 확실히

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.