Are anonymous blocks stored parsed in the SGA?

I have discovered recently that's possible to call anoymous blocks from jdbc like this:

    String plsql = "BEGIN" +
                   " :result := foobar( booleanparameter => :mypar > 2);" +
                   "END;";

    con.prepareCall(plsql);

Which is great, because I can use this to "wrap" some function calls and overcome some jdbc limitations. For example, I can't pass boolean vars to pl/sql procedures, and can't change the procedures signature since there is lots of code which depends on them. Adding new "wrapping" procedures isn't easy too because of internal policy reasons.

So it's seems like an acceptable solution, but, I'm concerned about parsing overhead. Are anonymous blocks like this stored parsed in the SGA or are they parsed every t开发者_Go百科ime they are called?

Thanks

Update 1: I have made a quick beanshell script to look into v$sqlarea as egorius suggests:

String  plsql = "BEGIN :myresult := dbms_random.random ; END;";
OracleDriver oracledrv = new OracleDriver();
Connection   con = oracledrv.connect(connstr, new Properties());

for (int i = 0 ; i < 1000 ; i++ ) {
    CallableStatement cb = con.prepareCall(plsql);
    cb.registerOutParameter("myresult", Types.INTEGER);
    cb.execute();
    System.out.println("random ->" +cb.getInt("myresult"));
    cb.close();
}
con.close();

And this is what I get int v$sqlarea (I have run it twice):

SQL_TEXT
--------------------------------------------------------------------------------

PARSE_CALLS EXECUTIONS
----------- ----------
BEGIN :myresult := dbms_random.random ; END;
       2000       2000

Does this mean that is preparsed or not?

---

Anonymous blocks are cached as well. You can check it by querying V$SQLAREA.

SQL> declare abcabc number; begin null; end;
2 /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> select sql_text, executions from v$sqlarea where sql_text like '%abcabc%';

SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------
declare abcabc number; begin null; end;
2

select sql_text, executions from v$sqlarea where sql_text like '%abcabc%'
1

EDIT:

You'll always have what is called SOFT PARSE. It's needed for syntax and semantic check of a query. After that, if exactly the same query exists in library cache, HARD PARSE will be skipped. (See this Ask Tom question for a good explanation).

Here is excerpt from tkprofed 10046 trace file:

declare abcabc number; begin null; end;

call     count       cpu    elapsed       disk      query    current        rows  
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           2
Fetch        0      0.00       0.00          0          0          0           0  
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           2

Misses in library cache during parse: 1

The last line shows the point.

---

In regards to always soft parsing, that is not necessarily so. Oracle allows both Java and PL/SQL have the ability to parse a statement just once per session. This happens when a cursor is not closed, but reused - no parsing. SQL Parsing Info: http://docs.oracle.com/cd/E11882_01/server.112/e25789/sqllangu.htm Private SQL area: http://docs.oracle.com/cd/E11882_01/server.112/e25789/memory.htm#i17716