4 Replies Latest reply: Feb 3, 2013 12:49 PM by SasDutta RSS

    hard parse vs soft parse

    SasDutta
      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
          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
            You can start with This
            • 3. Re: hard parse vs soft parse
              Toon Koppelaars
              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
                SasDutta
                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