This content has been marked as final. Show 4 replies
SasDutta wrote:GOOGLE "bind variable peeking"
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
The answer to your concern is called "adaptive cursor sharing".
Thanks for your reply. This feature is with Oracle 11g. What about Oracle 10g and below. In Oracle 10g how can i do soft parse as well as get optimum execution plan?