Secret of oracle logical IO: Index Scan
[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2009-11-11 02:29:55
Share to
Things become easy, let study the index scan case.
SQL代码
- HELLODBA.COM>conn demo/demo
- Connected.
- HELLODBA.COM>set linesize 300
- HELLODBA.COM>alter system flush buffer_cache;
- System altered.
- HELLODBA.COM>ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
- Session altered.
- HELLODBA.COM>set autot trace
- HELLODBA.COM>select * from t_test2 where owner = 'OUTLN';
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1900296288
- --------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 3 | 630 | 1 (0)| 00:00:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T_TEST2 | 3 | 630 | 1 (0)| 00:00:01 |
- |* 2 | INDEX RANGE SCAN | T_TEST2_IDX1 | 3 | | 1 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("OWNER"='OUTLN')
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 6 consistent gets
- 5 physical reads
- 0 redo size
- 3622 bytes sent via SQL*Net to client
- 385 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 3 rows processed
There are 6 Logical reads. Check the trace output, just find 5 pins.
SQL代码
- WAIT #2: nam='db file sequential read' ela= 15454 file#=5 block#=66164 blocks=1 obj#=100897 tim=999288447
- WAIT #2: nam='db file sequential read' ela= 35067 file#=5 block#=66176 blocks=1 obj#=100897 tim=999323645
- pin kdiwh09: kdiixs dba 1410280:1 time 999323748
- WAIT #2: nam='db file sequential read' ela= 14164 file#=5 block#=37922 blocks=1 obj#=97820 tim=999337989
- pin kdswh05: kdsgrp dba 1409422:1 time 999338144
- FETCH #2:c=0,e=65288,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=999338229
- WAIT #2: nam='SQL*Net message from client' ela= 718 driver id=1111838976 #bytes=1 p3=0 obj#=97820 tim=999339051
- pin kdiwh16: kdifxs dba 1410280:1 time 999339159
- WAIT #2: nam='db file sequential read' ela= 3066 file#=5 block#=37944 blocks=1 obj#=97820 tim=999342291
- pin kdswh05: kdsgrp dba 1409438:1 time 999342355
- WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=97820 tim=999342411
- WAIT #2: nam='db file sequential read' ela= 1935 file#=5 block#=37958 blocks=1 obj#=97820 tim=999344410
- pin kdswh05: kdsgrp dba 1409446:1 time 999344463
- FETCH #2:c=0,e=5388,p=2,cr=3,cu=0,mis=0,r=2,dep=0,og=4,tim=999344518
P.S. Guess the operation meaning,
- kdiixs: Index Scan;
- kdsgrp: Access table by index rowid.
Where is the other reads? Look at the 1st 'db file sequential read', the block was physically read without pining. What's kind of such block? Get its datablock address first,
SQL代码
- HELLODBA.COM>select to_char(dbms_utility.make_data_block_address(5, 66164), 'XXXXXXXX') from dual;
- TO_CHAR(D
- ---------
- 1410274
Then, dump the index tree.
SQL代码
- HELLODBA.COM>alter session set events 'immediate trace name treedump level 100897';
- Session altered.
Here is the tree,
SQL代码
- ----- begin tree dump
- pin kdxwh40: kdxdtree dba 1410274:1 time 2048438778
- branch: 0x1410274 21037684 (0: nrow: 6, level: 1)
- pin release 5331 kdxwh40: kdxdtree dba 1410274:1
- pin kdxwh40: kdxdtree dba 1410275:1 time 2048462836
- leaf: 0x1410275 21037685 (-1: nrow: 195 rrow: 195)
- pin release 5645 kdxwh40: kdxdtree dba 1410275:1
- pin kdxwh40: kdxdtree dba 1410280:1 time 2048471550
- leaf: 0x1410280 21037696 (0: nrow: 228 rrow: 228)
- pin release 5817 kdxwh40: kdxdtree dba 1410280:1
- pin kdxwh40: kdxdtree dba 1410276:1 time 2048480732
- leaf: 0x1410276 21037686 (1: nrow: 461 rrow: 461)
- pin release 5854 kdxwh40: kdxdtree dba 1410276:1
- pin kdxwh40: kdxdtree dba 1410277:1 time 2048489433
- leaf: 0x1410277 21037687 (2: nrow: 475 rrow: 475)
- pin release 6145 kdxwh40: kdxdtree dba 1410277:1
- pin kdxwh40: kdxdtree dba 1410278:1 time 2048498455
- leaf: 0x1410278 21037688 (3: nrow: 399 rrow: 399)
- pin release 5644 kdxwh40: kdxdtree dba 1410278:1
- pin kdxwh40: kdxdtree dba 1410279:1 time 2048507256
- leaf: 0x1410279 21037689 (4: nrow: 314 rrow: 314)
- pin release 5747 kdxwh40: kdxdtree dba 1410279:1
- ----- end tree dump
That block is a branch block. Means the branch block will not be pin in the cache. You can test other higher index tree to prove it.
So far, it comes out the Logical reads, 1 branch node read + 2 leaf node scan + 3 access table block by index rowid = 6 Logical reads.
--- Fuyuncat Mark ---