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.

[@more@]

ITPub博客

relation between consistent gets and arraysize(zt from asktom)

最新文章