Quote From ASKTOM:
http://asktom.oracle.comQuestion: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.
|
