tags:

views:

139

answers:

1

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?

+5  A: 

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.

egorius
+1 Another thing: PrepareCall() returns CallableStatement object, which can by invoked many times to get result of concrete already prepared statement without parsing.
ThinkJet
Thanks a lot egorius.
Samuel