Special case of Oracle Database consistent gets

Oracle DatabaseMedium,Consistent getsIt is very useful when determining the performance of a SQL statement. Generally, comparing the performance of two SQL statements is not based on who executes the SQL statement for a short time, but on whose consistent gets is small. However, this is not absolute. The following example is a counterexample.

The inverse example is as follows:

 
 

  
  
ETL @ RACTEST> create table test (a int );
  
  
Table created. Elapsed: 00:00:00. 05
  
  
ETL @ RACTEST> begin
  
  
2 for I in 1 .. 10000 loop
  
  
3 insert into test values (I );
  
  
4 end loop;
  
  
5 end;
  
  
6/PL/SQL procedure successfully completed. Elapsed: 00:00:00. 44
  
  
ETL @ RACTEST> set autot trace
  
  
ETL @ RACTEST> select * from test;
  
  
10000 rows selected. Elapsed: 00:00:00. 05 Execution Plan hash value: 1357081020 -------------------------------------------------------------
  
  
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | 0 | select statement | 10000 | 126K | 6 (0) | 00:00:01 |
  
  
| 1 | table access full | TEST | 10000 | 126K | 6 (0) | 00:00:01 |
  
  
-------------------------------------------------------------------------- Note-dynamic sampling used for this statement
  
  
Statistics 0 recursive CILS
  
  
0 db block gets
  
  
690 consistent gets
  
  
0 physical reads
  
  
0 redo size
  
  
214231 bytes sent via SQL * Net to client
  
  
7791 bytes encoded ed via SQL * Net from client
  
  
668 SQL * Net roundtrips to/from client
  
  
0 sorts (memory)
  
  
0 sorts (disk)
  
  
10000 rows processed: select * reads 690 memory blocks. ETL @ RACTEST> select * from test order by 1; 10000 rows selected. Elapsed: 00:00:00. 04 Execution Plan hash value: 2007178810 --------------------------------------------------------------------
  
  
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | 0 | select statement | 10000 | 126K | 7 (15) | 00:00:01 |
  
  
| 1 | sort order by | 10000 | 126K | 7 (15) | 00:00:01 |
  
  
| 2 | table access full | TEST | 10000 | 126K | 6 (0) | 00:00:01 |
  
  
--------------------------------------------------------------------------- Note-dynamic sampling used for this statement
  
  
Statistics 0 recursive CILS
  
  
0 db block gets
  
  
23 consistent gets
  
  
0 physical reads
  
  
0 redo size
  
  
174288 bytes sent via SQL * Net to client
  
  
7791 bytes encoded ed via SQL * Net from client
  
  
668 SQL * Net roundtrips to/from client
  
  
1 sorts (memory)
  
  
0 sorts (disk)
  
  
10000 rows processed
 
 

Let's look at order by. There are only 23 logical reads!

1. select * from test;

2. select * from test order by 1;

There is no doubt that 1st SQL statements are more efficient than 2nd SQL statements. But why are there so few consistent gets for 2nd SQL statements? I was puzzled at first and finally found the answer in ASK TOM.

Cause:

1. Generally, data not in the logical RAM buffer needs to be read through physical reads, while physical reads usually follows a consistent gets. Therefore, consistent gets is generally larger than physical reads. However, there is a special case. If the data obtained by physical reads is directly used for HASH or SORT, it is only recorded as physical reads and not as consistent gets. Therefore, if order by is added, physical reads may be large but consistent gets may be small. However, this is not the cause of my phenomenon, because I have no physical reads in this experiment.

Ii. Influence of arraysize. Arraysize indicates the number of rows read at a time. The default value is 15. You can use the show arraysize command to view the value. For example, if a data block contains 100 records, all data is not obtained once the block is read. For example, if arraysize = 15, 100/15 = 7 consistent gets is required. Setting the arraysize to a greater value can reduce the consistent gets, but sometimes it may consume more resources. If we perform the select count (0) from test; operation, Oracle will temporarily set the arraysize to the number of rows for test, so the consistent gets will be very few:

The Code is as follows:

 
 

  
  
ETL@RACTEST> select count(0) from test; Elapsed: 00:00:00.00 Execution Plan 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  
  
  
         23  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 
 
 

We can see that select count (0) only requires 23 logical reads. A total of 10000 data records, 10000/15 = 666.667, good, 667 + 23 = 690! It is surprisingly consistent with the consistent gets of the 1st SQL statements! This is not a coincidence. This is the calculation formula of consistent gets. We can also find that the consistent gets of the select count (0) and 2nd SQL statements are surprisingly consistent, both of which are 23!

TOM explained the following:
In select * from test order by 1;, Oracle also sets the arraysize as the number of rows in the test table temporarily. It extracts all the data and places it in the sort area for sorting, reading in the sort area is not included in the consistent gets. Therefore, although the 2nd SQL statements are the same as the consistent gets of select count (0), the efficiency of the SQL statements must be lower than that of select count (0). We can see the COST in the execution plan, the COST of the 2nd SQL statements is 7, the COST of select count (0) is 6, and the COST of the 1st SQL statements is also 6. The same COST does not mean the execution efficiency is the same)

The Oracle database consistent gets is introduced here. I hope this introduction will be helpful to you!