title: Softer soft parse in oracle using Visual C#
body: <p>I have an application written in Visual C# and connected to Oracle database. I have amended all queries(Insert, Select, Update, Delete) inside the application to use Bind Variables to achieve the <b>soft parse concept in Oracle</b>. </p>
  
  <p>Amending the queries to use Bind Variables (as shown in the below code) has tremendously helped us to achieve a great application performance. So what we did at this level is called <b>soft parse</b>. However, the ultimate solution is to use the <b>softer soft parse</b>.</p>
  
  <p>Below is a <b>dummy method</b> using Bind Variables in the query statement. (Please note that I intended to iterate 5 times just to see the result in oracle Toad tool since <b>my target is to let oracle parse the query once and execute it 5 times which is called softer soft parse in oracle</b> but using the below code, it is being parsed 5 times and executed 5 times which is the soft parse)</p>
  
  <pre><code>private void button8_Click(object sender, EventArgs e)
  {
      List &lt;OracleParameter&gt; bindVariables = new List&lt;OracleParameter&gt;();
      OracleParameter vCNTRNBR = new OracleParameter();
      vCNTRNBR.ParameterName = "BATCH_ID";
      vCNTRNBR.OracleDbType = OracleDbType.Char;
      vCNTRNBR.Size = 8;
      bindVariables.Add(vCNTRNBR);
  
      for (int i = 0; i &lt; 5; i++)
      {
          vCNTRNBR.Value = i.ToString();
          string sel = "SELECT * FROM BATCHTOTAL WHERE BATCHID = :BATCH_ID";
          using (OracleDataReader testReader = this.myEnv.dbcon1.DbmSelect(sel, bindVariables))
          {
              if (testReader != null &amp;&amp; testReader.Read())
                  s1 = testReader["MERCHANTID"].ToString();
              else
                  s1 = "null";
          }
      }
  }
  </code></pre>
  
  <p>Please help me achieving the <b>softer soft parse</b>. I have checked many articles and blogs saying that cursor cache should be enabled in oracle. We have enabled it but the query is still being parsed 5 times instead of one time. Does this mean that the query is not cached?</p>
  
  <p>Here is a good article talking about statement caching:</p>
  
  <p><a href="http://docs.oracle.com/cd/E11882_01/java.112/e16548/stmtcach.htm#JJDBC28650" rel="nofollow">http://docs.oracle.com/cd/E11882_01/java.112/e16548/stmtcach.htm#JJDBC28650</a></p>
  
  <p>Statement caching improves performance by caching executable statements that are used repeatedly, such as in a loop or in a method that is called repeatedly. Starting from JDBC 3.0, JDBC standards define a statement-caching interface.</p>
  
  <p>Statement caching can do the following:</p>
  
  <ol>
  <li><p>Prevent the overhead of repeated cursor creation</p></li>
  <li><p>Prevent repeated statement parsing and creation</p></li>
  <li><p>Reuse data structures in the client</p></li>
  </ol>
  
  <p>Your help is really appreciated.</p>
  
  <p>Thank you in advance</p>