This discussion is archived
4 Replies Latest reply: Feb 3, 2013 10:49 AM by 562599 RSS

hard parse vs soft parse

562599 Newbie
Currently Being Moderated
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
  • 1. Re: hard parse vs soft parse
    sb92075 Guru
    Currently Being Moderated
    SasDutta wrote:
    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
    GOOGLE "bind variable peeking"
  • 2. Re: hard parse vs soft parse
    jeneesh Guru
    Currently Being Moderated
    You can start with This
  • 3. Re: hard parse vs soft parse
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    The answer to your concern is called "adaptive cursor sharing".


    http://www.oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1.php
  • 4. Re: hard parse vs soft parse
    562599 Newbie
    Currently Being Moderated
    Hi

    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?

    Thanks
    SasDutta

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points