soft parse and hard parse！ （from jl）

---

When you submit an SQL statement for processing, it passes through a number of stages before your server process finally gets to execute it and give you back the results.

First your command is checked for syntax errors - basically, is the command correctly formed ?

If the syntax check is passed, it is checked for semantic errors - things like do all the objects required exist, does the user have appropriate privileges etc.

The next stage is to pass the command thorough a hashing function to obtain a hash value for the statement. This hash value is used as a lookup in the library cache to see if this command has been used before. If the hash value is found in the cache, then the SQL for that command is compared with yours to see if they are identical (down to letter case etc). If so, the next step is to ensure that all objects referenced in the cached command are the same objects referenced in your new one. If so, then the parse tree and execution plan for the existing command can be used for your as well and the optimiser stage is missed out This is a soft parse.

Assuming that there is not an identical SQL command in the library cache, then your command will be passed on to the Optimiser to work out the best plan of attack to get back the data you want. The optimiser builds a parse tree which will involve processing some recursive SQL. Once a parse tree has been build, an execution plan is created from it. This constitutes a hard parse.

Once an execution plan has been created, or an existing one re-used, the command can be executed.

In summary,

1. Perform syntax check
2. Perform semantic check
3. Perform hash function
4. Perform library cache lookup
5. If hash value found then
6. .....If command is identical to existing one in cache then
7. ..........If the objects referenced in the cached command are the same as the ones in the new command then
8. ...............This is a soft parse, go to step 11
9. This is a hard parse, build parse tree
10. Build execution plan
11. Execute plan.

The building of the parse tree and execution plan are the two most expensive parts of the parsing, and if these have to be done, then we have a hard parse.

Note : In the Oracle Performance Tuning 101 book mentioned below, the order given is that the hashing is done first and if the hash value is not found in the library cache, then the syntax and semantic checks are carried out. Tom Kyte says in his book, and on his web site, that this is not the case. The syntax and semantic checks are always carried out and then the hashing. As Tom works for Oracle, and because he gives good reasons, I'm following Tom's advice on the matter. The Oracle Concepts manual has the same description as Tom.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/194468/viewspace-1043549/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/194468/viewspace-1043549/