• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 96
  • Last Modified:

Optimization and Soft parse?

Dear Experts,

I am seeing different anwers for Soft parse,

If the query is already available in library cache, a new plan will be created or any previously created plan for the same sql will be used.
0
sakthikumar
Asked:
sakthikumar
1 Solution
 
Gerwin Jansen, EE MVETopic Advisor Commented:
A soft parse is recorded when the Oracle Server checks the shared pool for a SQL statement and finds a version of the statement that it can reuse.
http://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_instance_throughput_softparse_pct.html
0
 
sakthikumarAuthor Commented:
This is fine, You know there are many steps in processing the sqls before result is given to client.
like semantic checks , syntactic checks, plan generation .. etc.,

which of these will be eliminated for a soft parse?
0
 
sdstuberCommented:
the steps you mention are those involved in a hard parse.

soft parse, as noted in the first post, is the act of looking of a previously hard parsed statement and using that plan (which means the semantic checks, syntax checks, plan generation, etc have already been completed)  so all of them will be eliminated in a hard parse

soft parse is a lightweight operation, but still non-zero resource consumption.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
There is still some parsing done, e.g. to eliminate changed literals. E.g.
   select * from tbl1 where a = '1';
and
   select * from tbl1 where a = '2';
can still use the same plan. An exact comparison would not find a match. But that part is tried to a low level degree only, otherwise the effort needed would be near having to perform a hard parse (with cached meta data).
0
 
monocerosCommented:
Regardless of hard or soft parse all statements follow the same set of operations (note there is some disagreement on the ordering depending on sources and the age of the source)

0. Hash the SQL statement.  
1. Syntax parse
2. Semantic parse - existence of tables/columns and access permission.
3. Query Transformation - simply the query.
4. Optimization - determine the "best" execution plan.
5. Create executable -
6. Fetch rows

Or an older explaination (from here http://www.jlcomp.demon.co.uk/faq/soft_hard_parse.html)

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.

In either case the main difference (and most expensive if you ignore the execution time) is steps 4-5 in the first flow and step 8, 9 in the second flow. I.e. parsing the SQL and determining the execution plan. Also there is the overhead of finding space in the shared pool for the parsed statement and aging out another statement.

(The steps in determining if two statements are identical is  more complex than just hashing of course: the text must be exactly the same, all objects must resolve to the same objects, the optimiser goal must be the same, bind variables must be the same types and the same NLS setting must be in place - and probably more).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now