The process of 1.sql parsing
- Oracle first converts the SQL text to ASCII characters and calculates its corresponding hash value (Hash_value) based on the hash function. The corresponding bucket is found in the library cache based on the calculated hash value, and the SQL statement in the bucket is compared.
- If it does not exist, obtain a shared pool latch, then find an available chunk on the available chunk linked list (that is, bucket) in the shared pool, and then release the shared pool latch. After obtaining the chunk, this chunk can be considered as entering the library cache. Then, the hard parsing process is performed.
- Check the syntax of the SQL statement to see if there are any syntax errors. such as not writing from and so on. If there is, exit the parsing process.
- Verify that the objects and columns involved in the SQL statement are present in the data dictionary. Exits the parsing process if it does not exist.
- Converts an object into a name. such as the translation of the same noun into the actual object. If the conversion fails, exit the parsing process.
- Checks whether the user has permission to access the object referenced in the SQL statement in the cursor. If there is no permission, exit the parsing process.
- Create an optimal execution plan from the optimizer. This step is the most CPU intensive.
- Load the execution plan, SQL text, etc. generated by the cursor into several heap in the library cache.
- During hard parsing, the process will hold the library Cach latch until the hard parse is finished. After the hard parse is finished, two cursors are generated for the SQL, one is the parent cursor and the other is a child cursor. The parent cursor contains two main types of information: SQL text and an optimization target (optimizer goal). The parent cursor is locked when it is first opened, and is not unlocked until all other sessions have closed the cursor. When the parent cursor is locked, it cannot be swapped out of the library cache, and the library cache will not be swapped out until it is unlocked, and all child cursors corresponding to the parent cursor are swapped out of the library cache. Child cursors include all information about the cursor, such as the specific execution plan, binding variables, and so on.
- A child cursor can be swapped out of the library cache at any time, and when a child cursor is swapped out of the library cache, Oracle can reconstruct a child cursor using the information of the parent cursor, which is called Reload. You can determine the ratio of reload by using the following methods:
SELECT 100*sum (reloads)/sum (Pins) reload_ratio from V$librarycache;
A parent cursor can correspond to multiple child cursors. The number of child cursors can be represented from the V$sqlarea version_count field. Each specific child cursor is reflected in the v$sql. When the value of the specific bound variable differs from the value of the last bound variable (such as the length of the last bound variable's value being 6 bits, and the length of the value of the bound variable being executed is 200 bits) or when the SQL statement is identical, but the referenced object belongs to a different schema, will create a new child cursor.
- If the SQL statement is found in the bucket, the SQL statement was previously run and then soft-parsed. Soft parsing is relative to hard parsing, if the parsing process, can be removed from the hard parse step of one or more words, such parsing is soft parsing. Soft parsing is divided into the following three types.
1) The first type of SQL statement issued by a session is consistent with the SQL statements issued by other sessions in the library cache. At this point, the parsing process can remove the hard parsing of the two steps 5 and 6, but still the hard parsing process of the 2, 3, 4 steps: Table name and column name check, name conversion and permission check.
2) The second is that the SQL statement issued by a session is consistent with the SQL statement issued before the same session in the library cache. At this point, the parsing process can remove the hard parsing of the 2, 3, 5 and 6 of the four steps, but still have to do a permission check, because may change the session user's permissions through grant.
3) The third is when the initialization parameter session_cached_cursors is set, when a session to the same cursor for a third visit, will be in the PGA in the session to create a tag, And the cursor will not be swapped out of the library cache even if it has been closed. This way, when the session executes the same SQL statement later, all steps to resolve are skipped. In this case, it is the most efficient way to parse, but it consumes a lot of memory.
2. Review the process of SQL parsing, internal structure 3.SQL parsing process statistics
Oracle Memory Architecture share pool SQL parsing process