Explanation 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: the number of data blocks allowed by the request in 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 and optimize SQL statements for full table scan.

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, ValueFrom v $ sysstatWhere name in ('db block gets', 'consistent gets', 'Physical reads ');

query result buffer cache the hit rate is 90 % or above. Otherwise, increase the data buffer size.