이 포스트는 Richard Foote’s Oracle Blog를 번역한 것입니다. 의미 전달 상 의역이 있을 수 있으므로 원문을 참조하실 것을 권합니다.
내가 자주 받는 질문 중 인덱스를 얹어서 이익을 볼 수 있는 최소한의 테이블의 크기는 무엇이냐는 것이다. 예를 들어서 몇 블록으로만 이루어진 테이블을 한 번의 multiblock read operation을 통해 읽을 수 있으면 이런 테이블을 인덱스 해서 얻을 수 있는 이익이 없다는 것이다. 인덱스를 거쳐 테이블로부터 데이터를 읽기 위해서는 인덱스 블록을 읽는 적어도 한 번의 Logical I/O(LIO)와 이렇게 찾아진 ROWID로부터 참조되어지는 테이블 블록을 읽는 한 번의 LIO, 즉 최소한 두 번의 LIO가 필요하다. 만약 Full Table Scan(FTS)이 single multiblock read operation으로 효과적으로 수행될 수 있다면 전체 테이블을 읽는데 한 번의 LIO만 필요하다. 확실히 인덱스를 통한 방법은 항상 cost가 많이 들 것이고 이런 작은 테이블에는 전혀 쓸 모가 없을 것이다.
글쎄… 쓸모가 없다라…
지적하고 싶은 첫 번째는 일반적으로 이야기 해서 FTS는 상대적으로 cost가 많이 드는 operation이라는 것이다. 테이블은 정말 정말 커질 수가 있다. 잠재적으로 수 천 블록이 될 수도 있고 수천 번의 multiblock read operation이 수행될수도 있다. 그러므로, 일반적으로 이야기 하자면, 상대적으로 비싼 FTS를 수행한다면 잠재적으로 수천번의 I/O를 수행할 때 한 두 번의 추가적인 I/O에는 크게 신경을 안 쓸 것이다.
The first thing to point out is that generally speaking, a Full Table Scan is a relatively expensive operation. Tables can be big, really really big, consisting of potentially many many 1,000s of data blocks, potentially requiring many 1,000s of multiblock read operations to be performed. Therefore, generally speaking, if we’re going to perform a relatively expensive FTS, we’re not going to be too concerned if we use an extra I/O or two, as we potentially have to perform 1,000s of I/Os anyways. A shortcut here or there is not going to generally make much of a difference one way or the other.
Note also that with a FTS being this relatively expensive operation, we’re not likely to generally speaking want to perform 1,000s of such FTS operations every minute within our databases. Generally speaking, a FTS is a much less common event than an Index Range Scan operation and so we wouldn’t take advantage of any possible short cuts here or there very often.
However, generally speaking, an index scan is a relatively inexpensive operation, potentially consisting of just a few LIO operations. We may have an index that has a blevel of say 2 (height of 3) and we may typically only want to select a row or two. That would therefore consist of just 3 LIOs of read the index related blocks (the index root block, an index branch block and an index leaf block) plus an I/O or two to read a row or two from the table. It’s potentially just a handful of blocks, just a few little LIOs but if we could somehow save an I/O or two in the process, this could in fact make a huge difference to the relative costs of the Index Range Scan.
Note also that with an Index Range Scan being this relatively inexpensive operation, we’re quite like to generally speaking want to perform lots and lots of such Index operations each and every minute in our databases. Generally speaking, an Index Range scan is a very very common event and so any short cut here or there can be extremely useful and significant and be taken advantage of frequently within the database.
So a FTS has a tendency to be relatively expensive and is not performed anywhere near as as frequently as Index Range Scan operations which have a tendency to be relatively inexpensive. Generally speaking of course.
But Oracle takes this generalisation very much to heart in how it goes about processing these operations.
The next point to make is that if a table has just a few rows and say consists of just the one data block below its High Water Mark (HWM), it doesn’t necessarily mean we only need just the one I/O operation to read the entire table. For example, how does Oracle know there’s just one block worth of data ? How does Oracle know where to actually physically locate this one block worth of data ? How does Oracle know that once its read this block, there aren’t any other data blocks of interest ?
The answer is that it can’t without referencing data dictionary objects and without accessing the table segment header where the extent map is located. Even for a tiny table with only a handful of rows that can reside in only the one table block, it therefore requires more than just the one consistent get operation to read data from the table via a FTS. However, as a FTS is usually a relatively expensive operation, these few little consistent reads here and there to determine the actual number of blocks in the table and the actual location of these blocks is generally going to be a relatively trivial overhead. Oracle though doesn’t differentiate between a small and a larger table when it comes to a FTS, so these extra few consistent reads can potentially be a significant overhead for FTS operations on smaller tables.
As an example, let’s create a little table and see what consistent gets are required to read it via a FTS …
Let’s begin by creating a small table that consists of just 100 little rows.
SQL> CREATE TABLE small AS SELECT rownum id, ‘BOWIE’ name FROM dual CONNECT BY LEVEL <= 100;
Table created.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’SMALL’, estimate_percent=> null, method_opt=> ‘FOR ALL COLUMNS SIZE 1′);
PL/SQL procedure successfully completed.
SQL> SELECT blocks from user_tables WHERE table_name=’SMALL’;
BLOCKS
———-
1
Note that this table consists of just the one data block below the HWM. A table can’t really get much smaller that one block.
Let’s now select just one row from this table. Note we haven’t created an index at this point so Oracle has no choice but to read this one row via a FTS.
SQL> SELECT * FROM small WHERE id = 42;
ID NAME
———- —–
42 BOWIE
Execution Plan
——————————————
|Id | Operation | Name | Rows |
——————————————
| 0 | SELECT STATEMENT | | 1 |
|* 1 | TABLE ACCESS FULL| SMALL | 1 |
——————————————
Statistics
——————————————–
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
465 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Note to read just this one row from this one block table, we have actually performed 4 consistent gets operations. Not 1 consistent get, but 4 consistent gets …
Let’s look at the actual type of consistent gets, by running the following statement in another session before and after executing the above SELECT statement (note SID 134 refers to the session SID that ran the above SELECT statement) :
SQL> SELECT n.name, s.value FROM v$sesstat s, v$statname n
WHERE s.statistic# =n.statistic# AND s.sid = 134 AND n.name LIKE ‘consistent%’;
NAME VALUE
—————————– ——
consistent gets 275851
consistent gets – examination 70901
Note the above figures were the session consistent gets before the SELECT statement and the following consistent gets statistics are after the SELECT statement was executed.
SQL> SELECT n.name, s.value FROM v$sesstat s, v$statname n
WHERE s.statistic# =n.statistic# AND s.sid = 134 AND n.name LIKE ‘consistent%’;
NAME VALUE
—————————– ——
consistent gets 275855 (+4)
consistent gets – examination 70901 (0)
Note that yes indeed, there were 4 consistent gets performed and that none of the consistent gets were the “cheaper” consistent gets examinations. Therefore, the 4 consistent gets used in performing the FTS of the one block table required 4 x 2 = 8 latches.
Now 4 consistent reads to perform a FTS isn’t too bad, even for this little table and 8 latches isn’t exactly a huge number.
However, as we’ll see next, an index on this tiny one block table can do so much better …