Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

hard parse vs soft parse

SasDuttaFeb 2 2013 — edited Feb 3 2013
Hi

i have a concern over hard parse and soft parse. i have an understanding that in hard parse, a sql query goes through all the steps from syntax check to execution of query. In case of soft parse, if the new query is found in shared pool, it execute already existing parsed query, and execute already existing execution plan. Soft parse happens when we use bind variable in the query. Now based change in bind variable value, data volume of select query might change. Is it very efficient to execute same execution plan for different data volume?
If for given bind variable, the select query returns 10 record and for that index scan by row id is efficient. But for another value of same bind variable, the data volume lets say 1 million. If it executed by soft parse, it will execute the same execution plan. Is that very efficient way of doing things? Is that what actually happens?

Thanks in advance
SasDutta
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 3 2013
Added on Feb 2 2013
4 comments
385 views