Ah, the absence of a parse - the sound of silence...

In a given session - in order to execute a sql statment, that sql statement has to be:

a) parsed at least once (soft or hard, doesn't matter)
b) parsed AT MOST once

You can parse it once and execute it over and over and over and over (and so on).


Or, the developer could decide to parse it every time they want to execute it (the *developer* makes this decision - there is nothing a DBA can do about it - if the developer said "parse this", we parse it and nothing can stop that)


For example, let's say the developer was writing a routine - and it was known that this routine would be called over and over - maybe it would be called once every time the connection was grabbed from the connection pool. They could code the routine like this:

my-routine( my-inputs )
{
PreparedStmt p;

    p = conn.prepareStatement( some_sql_that_never_changes );
    .. bind here...
    ResultSet rset = p.executeQuery();
    ....
    p.closeStatement;
}

Now, ever time that is called - there will be a soft parse at least. *every single time*.

The could instead

PreparedStmt p;  -- make P a non-local, non-stack variable, so it persists

my-routine( my-inputs )
{
    if (p=null)
    {
         p = conn.prepareStatement( some_sql_that_never_changes );
    }
    .. bind here...
    ResultSet rset = p.executeQuery();
    ....
    // do not close!
}

Now, that would parse that statement ONCE and then just reuse it over and over and over and over again and again.


Or, the developer could enable statement caching depending on the language

http://docs.oracle.com/cd/B10500_01/java.920/a96654/stmtcach.htm

it has been around for a long time. That would make (in this case) java behave like PL/SQL.

Which leads me to the next way to reduce parsing - just move your SQL into plsql! Suppose you have a method that does five sql statements and right now every time it is invoked - it prepares them all, executes them and closes them. If that method is invoked 1,000,000 times in a connection - it will do 5,000,000 parses.

Now, if you simply move the five sql statements into plsql (plsql transparently caches open sql for us, example below) - and your method still prepares the single call to the stored procedure - your parses for 1,000,000 executions will be 1,000,005 instead of 5,000,000. That is a great drop.

This happens because in plsql - when you say "close cursor", plsql doesn't close the cursor (both explicit and implicit cursors work this way in plsql). It caches it open in the event you run that code again. It uses session_cached_cursors to figure out the size of its statement cache and it does the caching in a way that will NOT cause an ora-1000 "max open cursors exceeded". This cursor cache is "friendly" in that if you try to open a cursor AND we are out of cursors - plsql will close one of its cursors for you.

We can see this cursor cache easily:

ops$tkyte%ORA11GR2> create or replace procedure p
  2  as
  3  begin
  4          for x in (select 1 x from dual) loop null; end loop;
  5          for x in (select 2 x from dual) loop null; end loop;
  6          for x in (select 3 x from dual) loop null; end loop;
  7          for x in (select 4 x from dual) loop null; end loop;
  8          for x in (select 5 x from dual) loop null; end loop;
  9  end;
 10  /

Procedure created.

ops$tkyte%ORA11GR2> exec dbms_monitor.session_trace_enable

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec p

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec p

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec p

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec p

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec p

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec p

PL/SQL procedure successfully completed.



SELECT 1 X FROM DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0          0          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.00       0.00          0          0          0           6
********************************************************************************
SELECT 2 X FROM DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0          0          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.00       0.00          0          0          0           6

As you can see - each sql was executed six times but only parsed once.



So - to achieve this absence of a parse - you can:

a) program it that way
b) use 'magic' (statement caching) when available
c) move it into plsql