Ask Tom之中文化20060113——Consistent Gets（一）

Ask Tom之中文化20060113——Consistent Gets（一）

译者说明：这篇至今长达4.3年的帖子相当之冗长，其中有不少详尽的解释。长度决定无法一次性译完，也无法全部翻译。因此下文中有部分内容是译者自己总结了一些问题，合并了一些评论及回复给出的。此后将力争尽快挑选精彩的评论和回复翻译完成。

consistent gets——非常困惑
版本8.1.6
原提交于美国东部时间2001年6月28日16:59，最后更新于2006年1月12日11:49

问：
Tom：
create table test( a int);
begin
for i in 1..10000 loop
insert into test values (i);
end loop;
end;

set autotrace on
select count(0) from test;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST'

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
20 consistent gets
0 physical reads
0 redo size
369 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


select * from test where a=10;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
21 consistent gets
0 physical reads
0 redo size
360 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


select * from test;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
686 consistent gets
0 physical reads
0 redo size
185864 bytes sent via SQL*Net to client
74351 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10001 rows processed



问题：
1.当查询count(0)，Oracle究竟在干什么？我们没有索引，Oracle会访问每个块来查询这个数么？如果是这样，为什么select * from test的consistenet gets要小很多呢？它们都是全扫描表哦。

2.为什么select * from test比select * from test where a=10有更高的consistenet gets呢？尽管前者返回更多的行，但都没有索引，Oracle需要访问每一个块来获取值，应当是相同的consistenet gets啊。

此致

答：
问题1 select count(0)就如同

select count(*)
from ( select 0 from t )
/

是的，Oracle访问每一块查找行来返回你0。

问题2 这是你arraysize的副作用。你应当是用8K的块，因为我准确的重现了这一切。
我们期望得到大概20次一次性读是吧。SQLPlus的缺省array size是15行/次。10000/15 = 666.66666，666+20 = 686——哇，686就是我们的consistenet gets！
这就是所发生的事情。当你获取15行，Oracle暂停，把数据返回给你。当你再回去取下15行，它再次获取缓存来继续你的查询。看看不同的array size会发生什么。

ops$tkyte@8i> select * from test;

10000 rows selected.

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
686 consistent gets
0 physical reads
0 redo size
108813 bytes sent via SQL*Net to client
46265 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

ops$tkyte@8i> set arraysize 1000
ops$tkyte@8i> select * from test;

10000 rows selected.


Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
30 consistent gets
0 physical reads
0 redo size
86266 bytes sent via SQL*Net to client
942 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

ops$tkyte@8i> set arraysize 5000
ops$tkyte@8i> select * from test;

10000 rows selected.


Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
22 consistent gets
0 physical reads
0 redo size
149793 bytes sent via SQL*Net to client
382 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

另一个需要注意的事情是当array size过大，数据传输量反而增长。在array size上有个边际递减，因此不要走向极端。

--------------------------------------------------------------------------------

评论：
June 29, 2001
评论者：A reader
是否意味着，当我们全表扫描我们希望大一点的array size，而当我们根据rowid查询，我们需要小一点的？

回复：
将arraysize设置到某个常数，如果它过大对你的单行读取（single row fetches）没什么影响，但如果过小，（读取——译注）多行（不仅仅是全扫描）会影响到你。


Tested it but got different results April 30, 2002
评论者：ZS　来自澳大利亚
我现在有点困惑了。尽管你解释得不错，但我在我的环境下测试得到了不同的结果。我的查询select *返回122行。
Arraysize为15 --&gt consistenet gets为1051 (122/15=8)即1043 + 8 = 1051（这没问题）。所以我猜想根据上面的例子当我count(*)，consistenet gets应当是1043，是么？但
Arraysize为15下count(*) --&gt consistenet gets为920。我不知道这是怎么产生的。
我做了更多的测试：
Arraysize为122下select * --&gt consistenet gets = 1043
Arraysize为122下select count(*) --&gt consistenet gets = 920

Arraysize为5000下select * --&gt consistenet gets = 1043
Arraysize为5000下select count(*) --&gt consistenet gets = 920

希望你能解释，谢谢
ZS

回复：
count(*)返回多少行？１
array size甚至都没有开始介入。这条语句的运行只需要一次调用。
另外，count(*)还可能“从不碰表”。它可以只使用一个索引。
考虑：

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' )
Free Blocks.............................0
Total Blocks............................320
Total Bytes.............................2621440
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................7
Last Used Ext BlockId...................40969
Last Used Block.........................60

PL/SQL procedure successfully completed.

所以表用了大约316块。

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;
22905 rows selected.

Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
1823 consistent gets
46 physical reads
0 redo size
2704019 bytes sent via SQL*Net to client
169811 bytes received via SQL*Net from client
1528 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22905 rows processed


22905/15 = 1527+316 = 1843这大致就是我们的consistenet gets（我们所期望的）……

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from t;


Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
319 consistent gets
46 physical reads
0 redo size
369 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

count(*)，因为它是一次调用完成的，不需要arraysize，只在每个块上做了一次consistenet gets。所以它有319次consistenet gets（也就大致是表的大小）。
现在给表加上一个主键：

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary
key(object_id);

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from t;

Statistics
----------------------------------------------------------
79 recursive calls
4 db block gets
82 consistent gets
1 physical reads
0 redo size
369 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

我们可以看到consistenet gets下降了。为什么？因为优化器这次在索引中数行数，而不是表。

--------------------------------------------------------------------------------

May 01, 2002
评论者：A reader
你好Tom，
我们这样计算命中率：(1-(phycial_read/(db_block_get+consistance get))
但根据你这里的回复，我想consistenet gets或者db_block_gets是物理读（不是逻辑的）。
可以解释一下么？

回复：
db_block_gets + consistent_gets = LOGICAL IO（逻辑IO）
physical_reads = PHYSICAL IO（物理IO）
我没看出我可能在这里误导了你——你可以指出上面哪个部分告诉你它们是物理IO么？

--------------------------------------------------------------------------------

May 01, 2002
评论者：A reader
Tom
我想我在这里混淆了逻辑IO和物理IO。非常抱歉，不是你的错。
但我确实有些疑惑。
如果我第一次递交一个查询，内存中一无所有，那么db_block_gets和consistant gets都是物理读。（对么？）
如果我再次递交这个查询，很多块已经在内存中了，就是逻辑IO了。对么？

谢谢。

回复：
你可能是对的，你也可能不对。其他查询和/或操作可能已经缓存了数据。
对于第二点，你可能是对的，也可能不对，这依赖于：
o 访问的数据量
o buffer cache的大小
o 系统其他还在运行什么
o 数据库所用的不同特性/功能（如：并行查询）
总体上是对的，你会期望在很多情况下第二次执行大多是逻辑IO。

--------------------------------------------------------------------------------

Reader May 01, 2002
评论：A reader
Consistenet gets基于为一致性读重建块。所以它一个仅关于读取的db_blocks的函数。如果你说它可以被arraysize改变，你是否是指由于arraysize，一些块被多次读取，也就是同一个块有大于1次的一致性读？
谢谢

回复：
不是，你的表述是错误的。
一个consistent get是在读一致性模式下（read consistent mode）——时间点模式——读的一个块。它可能也可能不涉及到重建（回滚）。
DB Block Gets是CURRENT模式读（CURRENT mode gets）——当前的块读取。
有些块是被多次处理的，是的，处理中块会有多于1次一致性读。考虑：

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T')
Free Blocks.............................0
Total Blocks............................320
Total Bytes.............................2621440
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................7
Last Used Ext BlockId...................40969
Last Used Block.........................60

PL/SQL procedure successfully completed.

表有316块，22,908行……

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly statistics;

ops$tkyte@ORA817DEV.US.ORACLE.COM> set arraysize 15
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;
22908 rows selected.

对于arraysize=15，我们期望：
22908/15 + 316 = 1843 次一致模式读取（consistent mode gets）。db block gets——为FULL SCAN，和我们select的数据本身无关。

Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
1824 consistent gets
170 physical reads
0 redo size
2704448 bytes sent via SQL*Net to client
169922 bytes received via SQL*Net from client
1529 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22908 rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> set arraysize 100
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

22908 rows selected.

Now, with 100 as the arraysize, we expect

22908/100 + 316 = 545 次一致模式读（consistent mode gets）。

Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
546 consistent gets
180 physical reads
0 redo size
2557774 bytes sent via SQL*Net to client
25844 bytes received via SQL*Net from client
231 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22908 rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> set arraysize 1000
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

22908 rows selected.

现在对于arraysize = 1000我们期望：

22908/1000+316 = 338 次一致模式读（consistent mode gets）。

Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
342 consistent gets
222 physical reads
0 redo size
2534383 bytes sent via SQL*Net to client
2867 bytes received via SQL*Net from client
24 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22908 rows processed

所以，是的，当array读取大小比要读取的行数小时，一致模式（consistent mode）下块被读取不止一次。这是因为我们处理一个块时到一半就获得足够的行返回给客户端，我们就放弃这个块。当他们要之后的N行时，我们回到那个处理了一半的块然后继续。

--------------------------------------------------------------------------------

how does this formula works? May 01, 2002
评论者：A reader

“22908/15 + 316 = 1843 次一致模式读取（consistent mode gets）。db block gets——为FULL SCAN，和我们select的数据本身无关。”
你是如何知道22908/15 + 316就是consistent gets总量的？

回复：
读原来的问题和回答，我在那里提到了。
在这个例子里：
22908 = 行数
15 = arraysize.

22908/15 = 你获取的次数

所以这是你需要读一个块多于一次的次数。
315是表的块数（在一个完美世界里我们要执行315次consistent gets。对于arraysize为15和22908行，我们要加上22908/15次consistent gets）……

--------------------------------------------------------------------------------

I got that srt of idea but I was confused May 01, 2002
评论者：A reader
你好，那么SQL*PLUS中一个低arraysize会歪曲autotrace统计并影响性能？我猜想如果autotrace不那么精确，TKPROF也一样么？
我们如何设置arraysize来提高类似select * from table_a的性能呢，比如在Developer Reports或Business Objects之类的AD HOC Tools中。

回复：
不——它不会“歪曲”它们。你可以在SQLPlus中使用array size来查看你的应用干了些什么。
Array Size在SQLPlus中缺省是15
JDBC中10
pro*c中2
OCI中1
ODBC中未知（不懂，从来没用过）

这不是歪曲，这是事实！它显示你使用不同arraysize来读取（数据）时真实发生的现象。
TKPROF会显示完全一样的信息。一样——从JDBC到SQLPlus到Pro*C（SQLPlus仅是一个OCI应用程序）。
对于reports，参考：

ARRAYSIZE (RWBLD60)
描述ARRAYSIZE是用于Oracle数组化处理（array processing）中的array大小，以K为单位。
语法：
[ARRAYSIZE=]n

至于business objects——参考它们的文档。

--------------------------------------------------------------------------------

[以下一个问答由译者归并之后大约4个问答而来，如果不能明白，请参考原文中Tom不厌其烦的解释]
问：开头那个例子里的20是什么？row/arraysize+block_size是怎么来的？
答：20是表的块数。
block_size是要读的块数，row/arraysize是由于不能一次处理完而需要额外读取的次数。
[总结完毕]

--------------------------------------------------------------------------------

[以下开始仅选译部分精彩问答]
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
原文标题：Thanks for the question regarding "consistent gets -- Very puzzling", version 8.1.6
原文地址：
[url]http://asktom.oracle.com/pls/ask/f?p=4950:8:12938966801863764274::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:880343948514[/url]
--------------------------------------------------------------------------------
原文版权归属asktom.oracle.com所有，翻译 by ern@**
2006-01-13 11:40

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/92530/viewspace-442935/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/92530/viewspace-442935/