eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

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

ASKTOM about :Consistenet gets and arraysize
modb.pro

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.


历史上的今天...
    >> 2008-03-10文章:
           AIX 常用命令汇总

By eygle on 2009-03-10 11:50 | Comments (9) | Oracle摘 | 2228 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com