Description of the recursive CILS, db block gets, and consistent gets parameters in the execution plan

In actual work, we often look at the execution plan of an SQL statement, for example:

After sqlplus uses the set autotrace on command, the execution plan is shown as follows:

SQL> set autotrace on

SQL> select count (*) from EMP;

Count (*)
----------
12

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose
1 0 sort (aggregate)
2 1 Table Access (full) of 'emp'
Statistics
----------------------------------------------------------
0 recursive cballs
2 dB block gets
1 consistent gets
0 physical reads
0 redo size
383 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed
What are the meanings of recursive CILS, db block gets, and consistent gets?

The specific explanations are as follows:

· Recursive call. Number of recursive callgenerated at both the user and system level.
Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call.
In short, recursive cballs are basically SQL stored med on behalf of your SQL. so, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. these wocould be recursive CILS. space management, security checks, calling PL/SQL from SQL-all incur recursive SQL CILS.
· Db block gets. Number of times a current block was requested.
Current Mode blocks are retrieved as they exist right now, not in a consistent read fashion.
Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current Mode blocks are retrieved as they exist right now, not from a previous point in time.
During a select, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read ). during a modification, you will access the blocks in current mode in order to write to them.
(Db block gets: number of data blocks that can be satisfied by the buffer)
· Consistent gets. Number of times a consistent read was requested for a block.
This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from the rollback segment in order to roll back a block.
This is the mode you read blocks in with a select, for example.
Also, when you do a searched update/delete, you read the blocks in consistent read mode and then get the block in current mode to actually do the modification.
(Consistent gets: the total number of data requests in the buffer of the rollback segment)
· Physical reads. total number of data blocks read from disk. this number equals the value of "physical reads direct" plus all reads into buffer cache. (physical reads: Number of buffer cache data blocks read from the disk after the instance is started)
· Sorts (Disk ). number of sort operations that required at least one disk write. sorts that require I/O to disk are quite resource intensive. try increasing the size of the initialization parameter sort_area_size.

(Sorts (Disk): The number of disks to be sorted)

Physical reads is usually our most concerned. If this value is very high, it means that a large amount of data is requested from the disk to the buffer cache. It usually means that there are a large number of SQL statements scanned from the entire table in the system, this will affect the performance of the database. Therefore, try to avoid full table scan by statements. We recommend that you add related indexes to SQL statements for full table scan and optimize SQL statements to solve this problem.

There is a conversion formula between physical reads, db block gets, and consistent gets:

Data Buffer usage hit rate = 1-(physical reads/(db block gets + consistent gets )).

The SQL statement is as follows:

Use the following statement to view the hit rate of the Data Buffer:

SQL> select name, value from V $ sysstat where name in ('db block gets', 'consistent gets', 'Physical reads ');

The buffer cache hit rate of the query results should be above 90%. Otherwise, the data buffer size needs to be increased.

---------------------------------

B block gets: number of times a current block was requested.
consistent gets: number of times a consistent read was requested for a block.
physical reads: Total number of data blocks read from disk. this number equals the value of "physical reads direct" plus all reads into buffer cache.
---------------------------------------------------------------
the description and relationships of the three concepts are as follows:
1. DB BLOCK Gets (number of blocks in the current request)
The number of blocks in the current mode is the number of blocks being extracted in the operation, rather than the number of blocks generated in the case of consistent read. Under normal circumstances, the block extracted by a query is the data block that exists at the start of the query. The current block is the data block that exists at this time, instead of the number of data blocks before or after this time point.
2. Consistent gets (the data block required for consistent read of data requests in the buffer of the rollback segment)
the concept here is how many chunks need to be processed in the consistent read status when processing this operation. The main reason for these chunks is that during your query, other sessions operate on data blocks and modify the blocks to be queried. However, because our queries are called before these modifications, therefore, you need to query the pre-image of the data block in the rollback segment to ensure data consistency. In this way, consistent reads are generated.
3. Physical reads
refers to the number of data blocks read from the disk. The main reasons are:
1. These blocks do not exist in the database cache.
2. Full table scan.
3. Disk sorting.
the relationship between these three blocks can be roughly summarized:
logical read refers to the number of data blocks read from the memory of oracle. Generally, it is 'consistent gets' + 'db block gets '. If the required data block cannot be found in the memory, it needs to be obtained from the disk, so 'phsical reads' is generated '.