« AIX5.3中将Oracle SGA PIN在内存中的步骤 |
文摘首页
| What is Oracle consistent gets? »
ASKTOM about :Consistenet gets and arraysize
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. |
历史上的今天...
>>
2008-03-10文章:
By eygle on 2009-03-10 11:50 |
Comments (9) |
Oracle摘 | 2228 |
OracleRead