Soft parsing and hard parsing in Oracle __oracle

Question one: which process is responsible for hard parsing. The shared pool setting is unreasonable besides the low hit rate.

When it comes to soft parsing (soft prase) and hard parsing (hard prase), let's not mention Oracle 's handling of SQL . When you send an SQL statement to Oracle, Oracle will process the SQL several steps before executing and obtaining the results:

1, grammar check (syntax check)

Check that the spelling of this SQL is grammatically correct.

2. Semantic check (semantic check)

such as checking whether an Access object exists in an SQL statement and whether the user has appropriate permissions.

3. Parsing SQL statements (prase)

Using the internal algorithm to parse the SQL, generate the parse tree (parse) and the Execution plan (execution).

4. Execute SQL, return result (execute and returns)

In which, soft and hard parsing occurs in the third process.

Oracle uses the internal hash algorithm to obtain the hash value of the SQL and then searches the library cache for the existence of the hash value.

If present, compare this SQL with cache;

Assuming "the same", the existing parse tree and execution plan will be used, and the optimizer's relative work is omitted. This is also the process of soft parsing.

Admittedly, if any of the above 2 assumptions are not true, the optimizer will create the parse tree and generate the action of the execution plan. This process is called hard parsing.

Creating a parse tree and generating an execution plan is an expensive action for SQL execution, so you should try to avoid hard parsing and use soft parsing as much as possible.

This is why in many projects, it is advocated that the development designer work with the same code to keep the code consistent and to use the binding variable more often in the program.

/****************************************************/

question two, everyone is saying that using bind Var (binding variable) in SQL improves performance, so how does he improve performance?

Using bind Var improves performance primarily because it saves time by avoiding unnecessary hard analysis (Hard Parse) while saving a lot of CPU resources.

When a client submits an SQL to Oracle, Oracle parses it first (Parse), and then submits the parsing results to the optimizer (optimiser) for optimization and obtains Oracle's optimal query plan. And then follow this optimal plan to execute the SQL statement (of course, in this case, if only a soft parse would be a small part of the step).

However, when Oracle receives client-submitted SQL, it first looks in the shared pool to see if there are any previously parsed SQL exactly the same as the one you just received (note that this is exactly the same, requiring the exact same character level on the statement. Also requires that the objects involved must be exactly the same. When the same parser is found, it will no longer parse the new SQL directly before it is parsed. This saves the parsing time and the CPU resources consumed during parsing. In particular, the large number of short SQL running in OLTP, the effect will be more obvious. Because a two-SQL time may not have much of a feeling, but the equivalent of the larger after the more obvious feeling.

It says hard parsing (Hard Parse), what is this Hard Parse?

Parse are mainly divided into three kinds:

1, Hard Parse (hard resolution)

2, Soft Parse (soft analysis)

3, Soft Soft Parse (it seems that some of the data does not include this in it)

Hard Parse is mentioned above to parse the submitted SQL completely again (this will be done when it is not found in the shared pool), with a total of 5 steps to follow:

1: Grammar Analysis

2: Permissions and object checking

3: In the shared pool check if there are exactly the same before fully resolved-if present, skip 4 and 5 directly, run SQL (at this point soft parse)

4: Select execution Plan

5: Generate execution plan

Soft Parse skips the next two steps in the hard parse if it finds the exact same SQL parsing result in the shared pool.

Soft Soft Parse is actually when the Session_cursor_cache parameter is set, cursor is directly cache in the current session of the PGA, in the parsing of the only need for its syntax analysis, Permission object analysis can be transferred to the PGA search, if found that the exact same cursor, you can go directly to the results, it is the realization of the Soft Soft Parse.

However, in calculating the number of parsing times is only calculated hard parse and Soft parse (in fact, Soft Soft parse does not seem to be done parse): Soft parse percent calculation: Round (100* (1-:hprs/:prse), 2) [HPRS: Hard resolution times; Prse: Resolution times] Parse ratio calculation: Round (100* (1-prse/exec), 2) [EXEC: number of executions]