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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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?
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.
Qlemo"Batchelor", 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';
   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).
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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.