A special case of using consistent gets in Oracle

In Oracle databases, consistent gets is useful for judging the performance of a section of SQL, and generally comparing the performance of two segments of SQL is not to see whose execution time is short, but to see whose consistent gets small. But this is not absolute, the following example is a counter example.

One: Normally, data that is not in logical RAM buffer is read through physical reads, and physical reads usually follows a consistent gets. So generally consistent Gets is bigger than physical reads. There is a special case, however, that if the data obtained by physical reads is used directly for hash or sort, then only the physical reads is not recorded as consistent gets. So it's possible to add an order by physical Reads more but consistent gets less. But that's not why I'm here, because I don't have physical reads in this experiment.

Second: The influence of ArraySize. ArraySize refers to the number of rows that are fetched at a time while reading data. This value defaults to 15 and can be viewed using the show ArraySize command. A block of data, for example, has 100 records, it is not read this block to get all the data, take arraysize=15 as an example, there will be 100/15=7 times consistent gets. Setting the arraysize to a larger point can reduce consistent Gets, but sometimes it may consume more resources. If we do select COUNT (0) from test, the Oracle DBA will temporarily set ArraySize to the number of rows in test, so consistent gets will be minimal:

The code is as follows:

Etl@ractest> Select COUNT (0) from test; elapsed:00:00:00.00 Execution Plan Hash value:1950795681--------------

| Id | Operation

| Name | Rows | Cost (%CPU) | Time

| | 0 | SELECT STATEMENT |

| 1 |

6 (0) | 00:00:01 | | 1 | SORT AGGREGATE |

| 1 |

| |

| 2 | TABLE ACCESS full| TEST | 10000 |

6 (0) | 00:00:01 | -------------------------------------------------------------------Note

-Dynamic sampling used for this statement Statistics

0 Recursive calls

0 db Block gets

Consistent gets

0 physical Reads

0 Redo Size

515 Bytes sent via sql*net to client

465 bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

1 rows processed

You can see that select COUNT (0) requires only 23 logical reads. A total of 10,000 data, 10000/15=666.667, good, 667+23=690! and 1th SQL consistent gets surprisingly consistent! This is not a coincidence, this is the formula for calculating the consistent gets. We can also find that the consistent gets of select count (0) and 2nd SQL is surprisingly consistent, all 23!

Tom's explanation is:

When the SELECT * from Test is 1, Oracle also temporarily sets ArraySize to the number of rows in the test table, which takes all the data out first and puts it in the sort area, while reading in the sort area is not counted in consistent gets. So while the 2nd SQL is the same as the consistent gets of select count (0), it is certainly less efficient than select count (0), and we can see from the cost of the execution plan that the 2nd SQL is a 7,select The cost for count (0) is 6, and the cost for the 1th SQL is 6. (The same cost does not mean that execution efficiency is exactly the same)

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/