下看下面的實驗吧：

sqlplus /nolog;

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 20 16:12:02 2009

SQL> conn cc/cc
Connected.
SQL> create table cc as selc ect * from tb_ac001;

Table created.

SQL> se set s serveroutput on
SQL> set autotreace ace traceonly ;
SQL> select * from cc;

142998 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 436806892

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 138K| 94M| 5000 (1)| 00:01:01 |
| 1 | TABLE ACCESS FULL| CC | 138K| 94M| 5000 (1)| 00:01:01 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
1288 recursive calls
0 db block gets
14310 consistent gets
4906 physical reads
0 redo size
36639583 bytes sent via SQL*Net to client
105332 bytes received via SQL*Net from client
9535 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
142998 rows processed

SQL> execx show _space' ('cc','shop88'( );
Total Blocks............................4992
Total Bytes.............................40894464
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................2
Last Used Ext BlockId...................195593
Last Used Block.........................123

PL/SQL procedure successfully completed.

SQL> select * from cc;

142998 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 436806892

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 138K| 94M| 5000 (1)| 00:01:01 |
| 1 | TABLE ACCESS FULL| CC | 138K| 94M| 5000 (1)| 00:01:01 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
14182 consistent gets
0 physical reads
0 redo size
36639583 bytes sent via SQL*Net to client
105332 bytes received via SQL*Net from client
9535 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
142998 rows processed

SQL> /

142998 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 436806892

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 138K| 94M| 5000 (1)| 00:01:01 |
| 1 | TABLE ACCESS FULL| CC | 138K| 94M| 5000 (1)| 00:01:01 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14113 consistent gets
0 physical reads
0 redo size
36639583 bytes sent via SQL*Net to client
105332 bytes received via SQL*Net from client
9535 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
142998 rows processed

SQL> /

142998 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 436806892

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 138K| 94M| 5000 (1)| 00:01:01 |
| 1 | TABLE ACCESS FULL| CC | 138K| 94M| 5000 (1)| 00:01:01 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14113 consistent gets
0 physical reads
0 redo size
36639583 bytes sent via SQL*Net to client
105332 bytes received via SQL*Net from client
9535 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
142998 rows processed

SQL> /

142998 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 436806892

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 138K| 94M| 5000 (1)| 00:01:01 |
| 1 | TABLE ACCESS FULL| CC | 138K| 94M| 5000 (1)| 00:01:01 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14113 consistent gets
0 physical reads
0 redo size
36639583 bytes sent via SQL*Net to client
105332 bytes received via SQL*Net from client
9535 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
142998 rows processed

SQL> set
SQL>
SQL>
SQL>
SQL>
SQL> set array 1000
SQL> /

142998 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 436806892

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 138K| 94M| 5000 (1)| 00:01:01 |
| 1 | TABLE ACCESS FULL| CC | 138K| 94M| 5000 (1)| 00:01:01 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5047 consistent gets
0 physical reads
0 redo size
34892857 bytes sent via SQL*Net to client
2031 bytes received via SQL*Net from client
144 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
142998 rows processed

SQL> /

142998 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 436806892

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 138K| 94M| 5000 (1)| 00:01:01 |
| 1 | TABLE ACCESS FULL| CC | 138K| 94M| 5000 (1)| 00:01:01 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5047 consistent gets
0 physical reads
0 redo size
34892857 bytes sent via SQL*Net to client
2031 bytes received via SQL*Net from client
144 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
142998 rows processed

上面的實驗驗證了下面這段話：

When a "consistent get" is done, this doesn't necessarily mean that Oracle had to do any rollback/undo of DML in order to get the consistent image. Rather, it just means that Oracle requested a copy of the block as of the point in time that the query was started (a "consistent snapshot"). So, I believe it is quite normal to have many, many consistent gets even if there is NO DML occuring.

同時也指出了在sqlplus裡面的array的設定對 consistent get的影響

全文引用如下

A 'consistent get' is your server process telling the database "I need this dba (data block address) consistent with the point in time represented by this SCN, x."

So, lots of things can happen here. First, Oracle will look in the buffer cache for a CR (consistent read) buffer of the block that's consistent w/ the requested SCN. It may find it, if it does, that's counted as a 'consistent get' and either a 'consistent gets - no work' or 'consistent gets - cleanouts only', depending on whether the block needed to be cleaned out. (See V$SYSSTAT/V$SESSTAT for the statistics.) If it doesn't, it may take an existing CR buffer and roll it back further, or it may clone the current block and roll it back. If it needs to apply rollback (aka undo) then it will increment 'consistent gets' and either 'consistent gets - rollbacks only' or 'consistent gets - cleanouts and rollbacks'.

So, each 'consistent get' is your server process successfully getting access to the contents of a dba consistent w/ a particular SCN. This number should represent the number of buffer gets required to satisfy a particular query.

Now, 'db block gets'. A 'db block get' is a copy of the 'current mode block'. That is, the data in the block, as it exists currently, or at this point in time. Note that while multiple CR copies of a block may exist in the buffer cache, there can only ever be one current mode copy of a block in the buffer cache at any one time. (RAC is a special case, w/ shared current and exclusive current, but I'm not going to get into that here.) So, a 'db block get' is a buffer get in current mode. 'db block gets' are usually associated w/ DML, and in that scenario, will implicitly lock one or more rows in that block. Also, there is a notable case where db block gets can occur with a select statement. That will happen when you're doing a FULL TABLE SCAN on a table or a FAST FULL SCAN on an index. In those cases, the segment header is read (usually multiple times for some unknown reason) in current mode.

Next, 'physical reads': A physical read will occur any time a consistent get or a db block get goes looking for block and can't find it in the buffer cache. So, for each block read from disk, physical reads will be incremented. Gets which result in physical reads are counted both as as get and as a read in the statistics. So, if you do 10 consistent gets and 5 of them require physical reads, you should see consistent gets incremented by 10 and physical reads incremented by 5.

Now, what's up w/ arraysize? Well, arraysize is the client side setting for SQL*Plus specifying the size of the array that will receive result sets. The default, as you learned, is 15. Now, suppose you have a table where there are 30 records per block, and 3,000 rows in the table. But, your arraysize is 15. So, your server process will get the first 15 rows, and return them from the first block buffer. Now, for the next 15, you need to get that same block again, for rows 16-30. So, you need to do two buffer gets per block to get all the rows. For a 3,000 row table, you'll do (approximately) 3,000/15 = 200 buffer gets. If you change your arraysize to 30, you can get away w/ visitng each block only once and do 3,000/30 = 100 buffer gets.

So, consider that even after you've optimized a particular SQL statement, if the arraysize is too small, you're going to force your server process to do excess database calls (FETCH calls), and extra buffer gets as well. This can best be illustrated with a test similar to what you did, but try looking at the raw trace file for FETCH calls. The number of FETCH calls ought to be very close to (number of rows returned / arraysize). The 'r=xxx' in the FETCH call data in the trace file is the number of rows returned, which is probably what your arraysize is set to.

So, db block gets, consistent gets, and physical reads are all measured in buffers (or blocks). If the same block is requested multiple times, it will be counted that many times in these statistics. Oracle will always access data from the buffer cache by the buffer. All 'get' operations are by the buffer, never by row. After the buffer is 'gotten', Oracle parses the block to get the data for the relevant rows.

When a "consistent get" is done, this doesn't necessarily mean that Oracle had to do any rollback/undo of DML in order to get the consistent image. Rather, it just means that Oracle requested a copy of the block as of the point in time that the query was started (a "consistent snapshot"). So, I believe it is quite normal to have many, many consistent gets even if there is NO DML occuring.

隨便不好意思一下，我不知道上文的出處，誰知道，可以給我留言。

[@more@]

consistent gets 到底指的是什麼?

相關文章