eygle.com   eygle.com
eygle.com  
 

« AIX5.3中将Oracle SGA PIN在内存中的步骤 | Digest首页 | What is Oracle consistent gets? »

ASKTOM about :Consistenet gets and arraysize

链接:
Quote From ASKTOM: http://asktom.oracle.com

Question:
Consistenet gets is based upon re-constructing a block for consistent read. 
Hence it is a function of only the
number of db_blocks to be read.
If you say that it is altered by the arraysize, do you suggest that,
due to arraysize,
some blocks are read muliple times and hence some blocks have > 1
consistent read in the process
Thanks
Followup:
No, you are wrong in your statement.
A consistent get is a block gotten in read consistent mode (point in time mode).
It MAY or MAY NOT involve reconstruction (rolling back).
Db Block Gets are CURRENT mode gets -- blocks read "as of right now".

Some blocks are processed more then once, yes, the blocks will have more then 1
consistent read in the process. Consider:

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.
Table has 316 blocks, 22,908 rows..

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.
here with an array size of 15, we expect
22908/15 + 316 = 1843 consistent mode gets. db block gets -- they were for
performing the FULL SCAN, they had nothing to do with the data itself we
selected


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.
now, with arraysize = 1000, we expect:
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

so yes, the blocks are gotten in consistent mode MORE THEN ONCE when the array
fetch size is lower then the number of rows to be retrieved in this case

This is because we'll be 1/2 way through processing a block -- have enough rows
to return to the client -- and we'll give UP that block. When they ask for the
next N rows, we need to get that halfway processed block again and pick up where
we left off.

By eygle on 2009-03-10 11:50 | Comments (0) | Posted to Oracle摘 | Edit |

相关文章 随机文章
  • What is Oracle consistent gets?
  • Oracle Kernel Layer & ORA-600 Code Info
  • 怎样使用OCI编写多线程的ORACLE应用软件
  • IBM AIX Oracle 9i RAC 性能因素 - udp及其他
  • Oracle10g RAC环境下DataGuard备库搭建实例
  • 乳糖不耐症与正确饮奶
    分析师称Live品牌给微软带来负面影响
    E.F.codd全关系系统的十二条基本准则
    9i Performance Tuning Guide 读书笔记一
    EXP-00091 Exporting questionable statistics
    网上相关主题:
    Google

    留言 (0)

    发表留言:



    Remember Me?
    (输入验证码后方可评论,谢谢支持)



    CopyRight © 2004 eygle.com, All rights reserved.