Oracle parsing process details

Oracle Parsing process (SQL Parsing Flow digoal)

When a user sends an SQL statement to the oracle database, runs the anonymous statement, or runs the SQL statement deployed in the database, the database works according to a certain working mechanism to understand the working mechanism, it can help developers write efficient code or understand some running symptoms.

This is provided by Oracle on its official website metalink. We can describe the SQL processing process as follows:

Oracle SQL parsing Flowchart

========================

The SQL processing process is described as follows:
1. Check whether an opened cursor exists. If yes, link the cursor directly to the private SQL area (private SQL area) in PGA, and go to Step 11. Otherwise, perform step 2.
2. Check whether the initialization parameter SESSION_CACHED_CURSORS is set. If it is set, you can also point to the private SQL AREA in PGA through the cursor and go to Step 11. Otherwise, perform step 3.
3. Check the settings of HOLD_CURSOR and RELEASE_CURSOR. If RELEASE_CURSOR = no (no by default) and HOLD_CURSOR = yes (no by default), the memory space allocated for the private SQL area is retained after ORACLE executes the SQL statement, the link between cursor and private SQL area is also retained, and is no longer used by precompiled programs. You can also use this pointer to directly obtain the statement in private SQL AREA and go to Step 11.
In the preceding three cases, no parse is actually used, and the statements are obtained directly from the private SQL AREA in the PGA and executed directly. This is fast parse.
If none of these three cases exists, oracle goes to Step 4 for execution.
4. Create a cursor.
5. Syntax Check: Check whether the Syntax is correctly written and whether it complies with the SQL Syntax provided in the SQL Reference Manual.
6. Semantic Analysis: searches for data dictionaries, checklists, and columns, and obtains the syntax Analysis lock on the required objects, in this way, the definition of these objects is not changed during the statement syntax analysis, and the permissions required to access the schema objects involved are verified to be satisfied.
7. Convert the statement into an ASCII equivalent code, and then use the hash algorithm to obtain the hash value.
8. Check whether statements with the same hash value exist in the database cache. If so, go to Step 11. Otherwise, perform Step 9. This is soft parse.
9. Select an execution plan. Select an optimal execution plan from an available execution plan, including decisions related to the storage outline (srored outline) or materialized view (materialized view.
10. Generate a compilation code (p-code) for this statement ).
11. Execute the statement.

After a session executes a statement, the parse result of the statement is saved in the library cache, and a copy is also available in the private SQL area of PGA. Cursor Is always directly linked to private SQL area through a link. If this copy is not found in private, you need to parse the SQL statement and then match the hash value in the library cache. In general, Using cursor does not require any parse, because the statement information, including the execution plan, is obtained directly from the current private SQL area. Once you need to match in the library cache, you must use parse.
Soft parse is not a parse, but a small amount of parse. It only requires syntax check, semantic analysis, and hash statements.

1 2 3 Next Page