Hard parsing and soft parsing in Oracle

The SQL statements in Oracle are parsed before execution. A hard parsing includes the following steps:

1. Load to the shared pool-SQLSource codeIs loaded into the memory.
2. Syntax Parsing-Oracle checks for incorrect syntax spelling.
3. Semantic Resolution-Oracle verifies all table names and column names from the data dictionary and whether you have the right to access the data.
4. Query conversion-if you allow (query_rewrite = true), Oracle converts complex SQL statements into an equivalent simple form.
5. Optimization-create an execution plan based on the statistical information of the mode (Dynamic Sample statistics may be used in 10 Gb ).
6. Create an executable file-Oracle creates an executable file that serves SQL queries and calls local files.

Oracle provides the shared_pool_size parameter to cache SQL, so that we do not need to parse the SQL statement repeatedly. However, if shared_pool_size is too small orCodeWhen a non-reusable SQL statement (for example, containing a direct amount of where name = "Fred") is used, the SQL statement may expire.

What is the difference between soft resolution and hard resolution in Oracle? This is only the first step marked in red. That is to say, soft resolution does not need to be reloaded to the Shared Pool (and related memory allocation ).

 

A high number of resolution calls (> 10/second) indicates that your system has a large number of different SQL statements, or your SQL statements are not reused (for example, no bound variable is used ).

 

Hard parsing requires loading SQL statements into the Shared Pool. Hard Parsing is much worse than soft parsing because it involves the allocation and management of memory in the shared pool. Once loaded, the SQL statement must completely re-check the syntax and semantics and generate executable targets.

 

If shared_pool_size is set too small or SQL statements are not reused, a large amount of hard parsing will occur.

 

Therefore, you must properly set the size of the Shared Pool and reuse the SQL statement using host variables.