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 time 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?
Best Answer
Anonymous blocks are cached as well. You can check it by querying V$SQLAREA.
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:
The last line shows the point.