通过索引查询I/O次数问题

Yong Huang

An index unique scan on an index of height 2 (i.e. index_stats.height=2 or xxx_indexes.blevel+1=2) should generate 2 consistent gets. Plus 1 more get for the table block if a row is found. So 3 consistent gets in total (or 2 if the row doesn't exist so there's no need to visit the table block after checking the index). Sometimes you get 5. The 2 extra gets must be due to recursive calls. You may find it in a SQL trace file. I'm not sure why your first set of tests always have recursive calls, even after repeated tests. Your later tests (in msg #20) finally get rid of them. Do you have a very small shared pool?

Don't say I/O because that term is reserved for physical access ("physical" from Oracle's perspective, not necessarily really physical though). Ignore physical reads because they're hard to predict due to various layers' caching.

sundog315

如果想详细查看Oracle是如何扫描索引的，可以使用10200 Trace一下

alter session set events '10202 trace name context forever';
sql
alter session set events '10202 trace name context off';

sundog315

http://blog.itpub.net/19423/viewspace-1033724/

有兴趣的话，也可以看看这个