We often look at the execution plan of a SQL statement in actual work, for example:
After SQLPlus uses the command set autotrace on, the execution plan is shown below:
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 Calls 2 DB Block Gets 1 consistent gets 0 physical reads 0 redo size 383 bytes sent via SQL * Net to client 503 bytes received via SQL * Net from client 2 SQL * Net roundtrips to / from client 0 sorts (memory) 0 sorts (disk) 1 rows processed wherein recursive What is the specific meaning of both Calls, DB Block gets, consistent Get?
The specific explanation of now sorted is as follows:
· Recursive Calls. Number of recursive calls generated 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 calls are basically SQL performed 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 would be recursive calls. Space management, security checks, calling PL / SQL from SQL-all incur recursive SQL calls. · 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 r etrievals 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 The THEM. (DB Block Gets: Number of requested data in Buffer) · Consistent Gets. Number of Times A Consistent Read Was Requested for a black. this is how much is is how much blocks you process in "constent 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: Number of requested data in the Buffer rollback).. · 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) After the instance is started, the number of buffer cache data blocks is read from the disk. Number of Sort Operations That Required At Least One Disk Write. Sorts That Require I / O To Disk Areasing The size of the initialization parameter sort_area_size: Sorting from disk)
Physical Reads is usually our most concerned. If this value is high, you want to request a lot of data from disk to buffer cache, usually, there is a large number of SQL statements that have a lot of full table scans, which affects the performance of the database. Therefore, try to avoid the statement to make a full mete scan, SQL statement scanned by the full surface, it is recommended to increase the relevant index, optimize the SQL statement to resolve.
There is a conversion formula between the three parameters of Physical Reads, DB Block Gets and Consistent Gets:
The use hits rate of the data buffer = 1 - (Physical Reads / (DB Block Gets Consistent Gets).
In the SQL statement, it is reflected 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 result of querying the hit rate of Buffer Cache should be over 90%, otherwise you need to increase the size of the data buffer.
