Oracle 性能优化实战

风铃中の鬼

原帖由 nj21 于 2011-4-8 14:50 发表
我修改了一下，鬼兄看看对不对？

假设db_file_multiblock_read_count是10，而表中有100条记录，分布在10个block上，highwater mark也就在这里，现在要拿到这100条记录，用全表扫描，只需要读一次；而用index scan，则至少200次block accesses。

出处在这里，不知道我理解得对不对？
What this means is that for each row that will be retrieved via an index scan, at least two block
accesses will be required: at least one index block and one data block. If your final result set contains
100 rows and those 100 rows are retrieved using an index scan, there would be at least 200 block
accesses required.

前半部分和我认为的是一样的，就想你也提到索引是1次读1个块，也就是1次1个I/O。。索引和全表扫描的效率区别就在于用到的I/O的次数。
最后那句索引的解释和我理解的有偏差，这句话出自哪里，难道是askTom？？我想知道200个block accesses是否等同于200次的I/O。。因为照这个说法的话，取1条数据至少要走2倍的I/O数。。。。那从100W数据中取1W条数据走索引就要至少2W次的I/O???
而全表扫描的话，按每条数据40字节，一个块8K不计PCTFREE参数的影响，每个块200条数据，最少分布在5000个块里，一次I/O读16个块需要不到200次的I/O就可以。。。
取1%的数据，索引走2W次，全表走200次，那索引的意义何在。。。。

风铃中の鬼

所以我认为应该是这样。。100条数据分布在10个块里，全表1次I/O扫完，一个块默认8K，一个索引块放100条数据应该还有富余，所以索引的I/O应该是1个索引块+10个数据块 = 11次I/O，个人看法，求高手指正

另外索引由于没有真正意义上的UPDATE的概念，对索引的更新是先删除再插入的操作，所以PCTFREE参数的作用实际是为将来往块中插入数据的预留空间，第1个块一定会最终被填满。。。根据这个概念，一个数据块为8K，PCTFREE为25的话，那全表用的数据块中可能只有6K的数据，而索引块可能会有8K的数据，所以扫描索引块能比扫描数据块获得更多的数据。。。

[ 本帖最后由 风铃中の鬼 于 2011-4-8 15:22 编辑 ]

nj21

前面那段引用来自于Pro Oracle SQL。

我又查阅了一下Oracle Essentials：Oracle Database 10g，3rd，里面有一段描述，但不详细：

An Oracle database issues I/O requests in two basic sizes:

Single database block I/Os

For example, one 8 KB datablock at a time. This type of request reads or writes a specific block. For example, after looking up a row in an index, Oracle uses a single block I/O to retrieve the desired database block.

Multiblock I/Os

For example, 32 database blocks, each consisting of 8 KB, for a total I/O size of 256 KB. Multiblock I/O is used for large-scale operations, such as full table scans. The number of blocks in one multiblock I/O is determined by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT.

The Oracle database can read larger amounts of data with multiblock I/Os, so there are times when a full table scan might actually retrieve data faster than an index-based retrieval (e.g., if the selectivity of the index is low). Oracle can perform multiblock operations faster than the corresponding collection of single-block operations.

我的理解是，通过索引来查找记录，是一条一条记录（row）来读取的。当读取第一条的时候，要至少访问磁盘上的两个block（索引块和数据块），读取第二条的时候，如果index的selectivity很低，可能要继续访问磁盘上的两个块，但是如果selectivity比较高，就直接可以从cache中获得block，不涉及磁盘的IO操作。但是，无论如何，对block的访问次数是比较高的（从磁盘或者cache中）。只是猜测。。。

期待高手解惑。

[ 本帖最后由 nj21 于 2011-4-8 18:11 编辑 ]

newkid

index fast full scan可以一次读多个块。索引上有个Index Clustering Factor表示表数据的物理顺序对这个索引的友好程度，如果很紧凑的话对缓存就很有好处，你就不必再从磁盘读表数据了。

newkid

原帖由 nj21 于 2011-4-8 14:59 发表
建议用dmbs_sql主要是指一些特殊的场合，例如，在一个loop中执行动态sql，明确只需要一次parse的情况。

现在的NATIVE DYNAMIC SQL也会缓存游标，好像是从10G开始有的。所以用DBMS_SQL的机会很少了。

T-Blue

虚心学习。

yyp2009

原帖由 风铃中の鬼 于 2011-4-8 15:18 发表
所以我认为应该是这样。。100条数据分布在10个块里，全表1次I/O扫完，一个块默认8K，一个索引块放100条数据应该还有富余，所以索引的I/O应该是1个索引块+10个数据块 = 11次I/O，个人看法，求高手指正

另外索引由于没有真正意义上的UPDATE的概念，对索引的更新是先删除再插入的操作，所以PCTFREE参数的作用实际是为将来往块中插入数据的预留空间，第1个块一定会最终被填满。。。根据这个概念，一个数据块为8K，PCTFREE为25的话，那全表用的数据块中可能只有6K的数据，而索引块可能会有8K的数据，所以扫描索引块能比扫描数据块获得更多的数据。。。

我觉得：
1 通过索引访问i/o到底多大需要依赖： a，索引列的选择性；b ，对应表的索引的Clustering Factor，c，数据分布的离散程度。
2 索引的dml操作不只是的删除和插入，还需要维护索引对应的存储顺序性和Clustering Factor，其成本更多的还是后者。
3 索引更多看中选择性和数据分布的离散程度。而全表扫描就无所谓选择性和数据分布离散程度了
4 其外碎片也会导致索引额外的i/o

这是我的理解。

请大家指正。

yyp2009

具体情况具体分析！  马克思如果活着我猜想他也会说oracle也是这样的。 我个人觉着……

[ 本帖最后由 yyp2009 于 2011-4-9 22:27 编辑 ]

yyp2009

不过楼主的整理分享，虚心学习了。谢谢！

yyp2009

原帖由 风铃中の鬼 于 2011-4-8 15:07 发表



前半部分和我认为的是一样的，就想你也提到索引是1次读1个块，也就是1次1个I/O。。索引和全表扫描的效率区别就在于用到的I/O的次数。
最后那句索引的解释和我理解的有偏差，这句话出自哪里，难道是askTom？？我想知道200个block accesses是否等同于200次的I/O。。因为照这个说法的话，取1条数据至少要走2倍的I/O数。。。。那从100W数据中取1W条数据走索引就要至少2W次的I/O???

这个我也想问问是从哪里看到的，请指教一下啊？谢谢先！

这个我也想问问是从哪里看到的，请指教一下啊？谢谢先！