Trevor -- Thanks for the question regarding "Parse CPU to Parse Elapsed", version 8.1.7

Followup June 5, 2003 - 7am Central time zone:

1) because your database is a mess and the java guys are KILLING YOU.  Literally KILLING YOU.

It is funny you know.  I use that exact example in my next book -- to prove that the java guy in 
that chapter has got not a single clue about what he is talking about -- not at all



The test is 100% flawed.  Here is a short excerpt from my forthcoming book on this topic:

Bind Variables and Java

One of the recent questions on the website asktom.Oracle.com recently was this interesting one. It 
was about programming in Java with JDBC.  This discussion applies equally to Visual Basic 
programmers using VB with ODBC as the concept of "Statements" and "PreparedStatements" exists in 
ODBC in more or less the same fashion.  It questioned the use of Statements versus a 
PreparedStatement. When using Statements in JDBC - you must use the "string concatenation 
approach". Using PreparedStatements allows you to use bind variables. The question was:
Tom -- Please briefly skim this link (link omitted for obvious reasons) which gives an excerpt for 
JDBC performance.  It says always use statements (no bind variables allowed) instead of 
preparedstatements because they perform better without discussing the impact on the database, only 
in terms of a single apps metrics.  Is this accurate or is this information just extremely short 
sighted with regards to overall db impact?

Well, that was easy - I gave them the proof from above - case closed. PreparedStatements with bind 
variables are absolutely without question the only way to do it. Of course, later on, I got a 
followup:

For a moment keep aside shared pool, hard parse and soft parse and talk about PreparedStatement and 
Statement as they are the only way to execute statements from java.  I wrote this benchmark code 
that shows a Statement performs better than a PreparedStatement unless you execute the same 
statement a whole lot of times.  So, I reproduced the findings of the above link and prove that 
Statements are better than PreparedStatements.


I had some issues with this one - they missed the point. They start with "for a moment keep aside 
shared pool, hard parse and soft parse". Well, if we were to ignore those - we totally miss the 
boat on this topic as they are the only things to consider. The facts are:

o Hard Parsing incurs many latches
o Latches are serialization devices
o Serialization is not a scalable thing
o Therefore as you add users, the system that uses Statements instead of PreparedStatements with 
bind variables will fail.

I quite simply could not observe their request to put aside the shared pool and hard/soft parse. 
They are the relevant topics - they must be considered. That was my initial response - but you 
know, this bothered me so much, I had to explore it further. So, starting with their benchmark code 
which simply inserted into a database table, I made it a multi-user benchmark to demonstrate the 
fact that if you expand this simple, single user benchmark out to a real world example with 
multiple users - you will see clearly what the issue is and why you need to avoid statements.


But, an interesting thing happened. I could not reproduce their findings that a Statement in JDBC 
without bind variables versus a PreparedStatement using bind variables. When I ran their code - I 
could, using my code - I found that a single statement executed using either of a Statement or 
PreparedStatement took the same amount of time initially and if we executed the SQL over and over - 
the PreparedStatement was always much faster. This conflicted with their observations totally.

So, I set out to find out why. We'll walk through this process here because it does cover two 
interesting things:

o If for some reason your test isn't meeting your hypothesis - either your hypothesis is wrong or 
your test is flawed
o The seemingly simple, convincing test can be the most misleading thing in the world


We'll walk through this simple benchmark now, starting with their original test case and working up 
to the "real thing". It used a single table TESTXXXPERF which was created using the script 
perftest.sql as follows:


scott@ORA920> drop table testxxxperf;
Table dropped.

scott@ORA920> create table testxxxperf
  2  ( id number,
  3    code varchar2(25),
  4    descr varchar2(25),
  5    insert_user varchar2(30),
  6    insert_date date );
Table created.

scott@ORA920> exit 


Then, the main java code was supplied. It consisted of three subroutines basically - a main that 
connected to the database and then called a routine to insert into that table using statement and 
then called a routine to do the same with prepared statements. The code piece by piece is:

import java.sql.*;
import oracle.jdbc.OracleDriver;
import java.util.Date;
public class perftest
{
  public static void main (String arr[]) throws Exception
  {
    Connection con = null;
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    con = DriverManager.getConnection
    ("jdbc:oracle:thin:@aria-dev:1521:ora920", "scott", "tiger");
    con.setAutoCommit(false);
    Integer iters = new Integer(arr[0]);
    doStatement (con, iters.intValue() );
    doPreparedStatement(con, iters.intValue() );
    con.commit();
    con.close();
  }


That is the main routine which simply connects to my Oracle 9iR2 instance as scott/tiger - disables 
the autocommit JDBC uses by default and then invokes the subroutine to execute a Statement N times 
and then a PreparedStatement N times. I set it up to allow us to pass "N" into the Java routine so 
we can run multiple simulations. Next, we'll look at the doStatement routine:


  static void doStatement(Connection con, int count)
  throws Exception
  {
    long start = new Date().getTime();
    Statement st = con.createStatement();

    for (int i = 0; i < count; i++)
    {
      st.executeUpdate
      ("insert into testxxxperf " +
       "(id, code, descr, insert_user, insert_date)" +
       " values (" + i  + ", 'ST - code" + i + "'" +
       ", 'St - descr" + i + "'" + ", user, sysdate ) ");
    }
    long end = new Date().getTime();
    st.close();
    con.commit();
    System.out.println
    ("statement " + count + " times in " +
      (end - start) + " milli seconds");
  }


Very straight forward - it simply creates a statement object and then loops "count" times and 
builds a unique - never before seen INSERT statement and executes it. It is somewhat scaled back 
from reality in that it is not checking for quotes in strings and fixing them up - but we'll let 
that go for now. Also note that it retrieves the time before and after executing the statement and 
prints out the results.  Next, we look at the prepared statement:


  static void doPreparedStatement (Connection con, int count)
  throws Exception
  {
    long start = new Date().getTime();
    PreparedStatement ps =
       con.prepareStatement
       ("insert into testxxxperf " +
        "(id, code, descr, insert_user, insert_date)"
        + " values (?,?,?, user, sysdate)");

    for (int i = 0; i < count; i++)
    {
      ps.setInt(1,i);
      ps.setString(2,"PS - code" + i);
      ps.setString(3,"PS - desc" + i);
      ps.executeUpdate();
    }
    long end = new Date().getTime();
    con.commit();
    System.out.println
    ("pstatement " + count + " times in " + 
     (end - start) + " milli seconds");
  }
}


Basically the same code but this uses a PreparedStatement to insert "count" rows. It accomplishes 
the same exact task as the doStatement routine - just using a PreparedStatement. Lastly, I set up a 
shell script to execute this:


!#/bin/csh -f
sqlplus scott/tiger @perftest
java perftest $1


A CMD file for Windows might look like:


sqlplus scott/tiger @perftest
java perftest %1


Now, I ran this with inputs of 1 (do one statement/prepared statement), 10, 100 and 1,000 and the 
results were:


Rows to Insert    Statement    PrepareStatement
1            0.05 seconds    0.92 seconds
10            0.34 seconds    1.03 seconds
100            2.69 seconds    2.35 seconds
1000            26.68 seconds   15.74 seconds


So, at first glance - it looks like they might have something here. If you were to ignore the 
database (which I'm not inclined to do personally). If I just look at this test - I might conclude 
that if I'm not going to execute the same statement over and over - about 100 times - I would best 
be served by using a Statement. The problem is there is a FLAW in our test! I discovered this flaw 
when I rewrote the code a little to go "multi-user". I knew in a multi-user test, using 
System.out.println would not be a very "scalable" testing tool. It would be hard to collect and 
analyze the results. So, I did what I always do when benchmarking and setup a database table to 
hold the timing results. The slightly modified Java code had an extra subroutine "saveTimes" to 
save the timing information into the database. That routine you can add to the test program above 
is:


  static PreparedStatement saveTimesPs;
  static void saveTimes( Connection con,
                         String which,
                         long elap ) throws Exception
  {
    if ( saveTimesPs == null )
        saveTimesPs = con.prepareStatement
                      ("insert into timings " +
                       "( which, elap ) values "+
                       "( ?, ? )" );

    saveTimesPs.setString(1,which);
    saveTimesPs.setLong(2,elap);
    saveTimesPs.executeUpdate();
  }

Then, I modified the doStatement and doPreparedStatement routines like this:

  static void doStatement (Connection con,
                           int count) throws Exception
  {
    long start = new Date().getTime();
    Statement st = con.createStatement();
    for (int i = 0; i < count; i++)
    {
      st.executeUpdate
      ("insert into testxxxperf " +
       "(id, code, descr, insert_user, insert_date)" +
       " values (" + i  +
       ", 'ST - code" + i + "'" +
       ", 'St - descr" + i + "'" +
       ", user, sysdate ) ");
    }
    st.close();
    con.commit();
    long end = new Date().getTime();
    //System.out.println( "STMT" + " (" + (end-start) + ")" );
    saveTimes( con, "STMT", end-start );
  }

And I did likewise for the PreparedStatement routine. This would simply save the 
times in a database table:

create table timings ( which varchar2(10), elap number );


so we could run a query to get average/min/max timings from multiple users. So, remembering that 
the only thing I changed was to comment out the System.out.printlns and add a routine to record the 
time - I ran this in single user mode to test. I found:



Rows to Insert    Statement    PrepareStatement
1               0.05 seconds    0.05 seconds
10              0.30 seconds    0.18 seconds
100             2.69 seconds    1.44 seconds
1000           28.25 seconds    15.25 seconds

That's different - very different. Surprisingly different. All of a sudden - there is not only no 
penalty ever for using a PreparedStatement - but it quickly benefits us in single user mode even to 
use it. What could be the cause.
The code being timed was no different. Not a single byte of code was changed. Sure, we commented 
out a System.out.println and added a call to saveTimes - but that code was never timed before. So, 
what did change then? Well, it turns out the saveTimes routine was the culprit here. If you look at 
that code - it uses a PreparedStatement. It "warmed up" the PreparedStatement class. It paid a one 
time penalty to load that class - java dynamically loads classes as you use them. The simple act of 
connecting did that for the Statement class (it is used during the connection to Oracle). Once the 
timing of the initial load of the PreparedStatement class was factored out - it turns out that a 
PreparedStatement is no more expensive to execute than a Statement is in JDBC. The entire premise 
of a Statement being "lighter weight", "more efficient" for small numbers of statements was flawed 
- wrong. If you used a single PreparedStatement anywhere in your code - you would have paid this 
"load" penalty (which is pretty small when you look at it over all) for ALL PreparedStatements.


That was the interesting part of this example - that the basic test itself was flawed, we were 
timing an unrelated "thing". Since most non-trivial Java JDBC programs are going to have to use a 
PreparedStatement somewhere - they all pay this "load" penalty. Not only that but this "load 
penalty" isn't a penalty at all - but simply the price of admission to building a scalable 
application on Oracle. If you don't use Prepared statements - if you insist on using Statements and 
"gluing the values in" - opening yourself up to the SQL Injection security flaw and buggy code - 
your application will not scale as you add users. There is no "maybe" here, there is no "might not 
scale", your application will not scale - period.
 

Followup March 16, 2004 - 7am Central time zone:

if connections are not being released, you are using a connection pool of some sort.  but -- you 
would know it as you would be setting it up.

yes, if you log off, all resources associated with that connection are released.  However, if you 
are seeing ora-4031's and you haven't been using prepared statements every where and you have lots 
of activity, well, it is pretty straightforward what is happening (you are doing it to yourself by 
using statements).  


Prepared statements are *not* just about single user vs multi-user.  They are about "doing it 
right" vs "doing it wrong".  They kill performance in both cases.  Not only that but the "common" 
solution is to make the shared pool really large which just pushed the problem back a couple of 
feet and made it BIGGER (the correct approach is to downsize the shared pool, make it smaller, not 
bigger -- while you fix your bug)

If you are doing inserts with a statement -- there IS no second time.  If you are doing inserts 
with a statement -- each insert is unique, never before seen.  It is quite simply the very *worst* 
thing on the planet you can do.  Nothing worse really.

Latches are quick in/out type of locks.  latches are held very brief moments while we access a 
shared data structure (like the shared pool).  You use them very briefly.  You don't even need to 
finish the statement to give them up.


Using statements is bad bad bad.  cannot say it any other way.


Why is is hard to understand that statements result in higher  latching then pstatements?  with 
statements every execute is a parse.  a parse takes many latches.  with prepared statements, only a 
parse is a parse -- you can execute over and over and over without parsing.  

Tell me -- would you compile your "methods" (aka subroutines) before each invocation and then throw 
out the code?  would you compile your subroutines thousands of times in a single program execution? 
 No, you wouldn't.  so, why are you doing just that??? that is what you are doing by using a 
statement.


Show me how a statement makes "debugging" easier -- do that for me ok?   So, you get an ora-1401.  
How the HECK does a statement help you out there?????  How how how -- just give me a teeny tiny 
small example???  I don't get it.


It wastes time
It is harder to develop with
It costs $$$$$$$ to use statements.  Look at the money you are wasting right here, right now.  This 
is cheaper?????????????????


(can you guess what MY pet peeve is by now?)


You do understand that statements introduce a HUGE security risk right?  (search google for "sql 
injection")

You do understand that statements consume HUGE java resources right?  (betcha just create String 
temporaries all over the place)

You do understand that statements literally can kill the database right? (you are there, you should 
know that -- you are doing it and seeing the results)


A prepared statement with "?" does not make sense.  I cannot beleive you do the same exact insert 
(what happened to the primary key fields eh?) over and over.  But yes, if in fact you have a 
statement with all literal values that you execute over and over  -- a prepared statement is more 
efficient than a statement -- but I cannot see the use case for such a beast.


In the 6 months you've been on this project this site has taken

ask_tom@ASKUS> select count(*) from WWC_ASK_SUBMITTED_QUESTIONS$
  2  where timestamp > add_months(sysdate,-6);
 
  COUNT(*)
----------
      1013

new questions and performed:

ask_tom@ASKUS> select count(*) from WWC_ASK_REVIEW_FOLLOWUPS$
  2  where created_on > add_months(sysdate,-6);
 
  COUNT(*)
----------
      5387

followups like this one.......

but what resources are out there?  how about


o groups.google.com -> comp.databases.oracle.*  The internet usenet newsgroups.  There you'll get 
lots of people willing to provide you advice.

o otn.oracle.com -> get support -> discussion forums.  Ditto


Sorry, we did not write the jdbc spec, we just implemented it.  However, it would be somewhat 
trivial I believe to have a hash table based on you connection and every time you parse, you add to 
the hash table, when you close, you remove.  Not very difficult (sigh)


Tell me this -- when you used to process FILES in the olden days -- did you leak file handles?  or 
did you close them when you were done, open them when you needed them.  statements/prepared 
statements are no different than a file.




 

Followup March 16, 2004 - 2pm Central time zone:

that is not what I said at all -- if you use a statement, close it, it is gone from the client 
side.

But over in your shared pool you have a bunch of stuff that has to be managed, but that no one is 
every going to use again.  A waste of energy, we compiled a subroutine that'll never get used a 
again and now we have to manage it until we figure that out and hopefully age it out of the shared 
pool subpool it is in over time...

We keep it around in the hopes that the applications were designed to be efficient ;)  sometimes we 
are wrong and the applications are not designed that way.



I cannot compare those two snippets as the first piece of code is not "sensible".  you don't just 
"execute" a SELECT.  You have to get and process a result set (else -- why bother??)


If you executed that select AND you lost the "result set" (which is not the statement but yet 
another object) that would be like opening a file and not closeing it and letting the file handle 
go out of scope.  For all we know, you'll come back in 5 minutes and want that result set -- we 
cannot close it, only the well formed program can do that.

Maybe what you really want to do (if you want things fast, easy to code, easy to debug, 
maintainable, BOUND) is to use PLSQL actually.  Java is a seriously hard language to write code 
(especially database code) in by hand.  If you code your data logic in PLSQL you'll be coding 
circles around the other developers.

a) you query the v$ tables -- a wealth of information exists therein.  

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch3.htm#1109131

Followup March 16, 2004 - 8pm Central time zone:

if it is EXACTLY the same query -- yes.  a statement would be OK in that particular case.

Oracle tries to jettison them but...

the shared pool is sub-divided into sub pools - especially as it gets larger and larger (soon it 
takes longer to age out than to actually do the work you ask us).  So, the larger you make it in a 
system that isn't binding -- the worse it actually gets.

1) a=0 is GOOD when coupled with "and b=?".  the a=0 is invariant, the b=? is variant.  you bind 
things that change (like you parameterize inputs to subroutines)....

2) select * from v$sgastat


PLSQL rocks, you will like it. 

Followup March 17, 2004 - 7am Central time zone:

The prepared statement, when closed, will be just like a closed statement. 

Followup March 21, 2004 - 9am Central time zone:

that is like asking how long does it take ice to melt or paint to dry.

it depends.  

they may never age out.
they may age out really fast.
they may age out pretty soon.
they may age out after a long time. 

Followup March 22, 2004 - 3pm Central time zone:

there is no way to gauge this.

depends on the size of your shared pool
which subpool your statement hashes to
what other statements just happen to get assigned to the same place.
etc....

DDL btw is not in the shared pool, therefore, bind variable = not used for DDL as it is not 
shareable. 

Followup March 22, 2004 - 9pm Central time zone:

that means your subclass isn't doing the binding right?

java.sql.SQLException: ORA-01006: bind variable does not exist

you are "binding a variable that doesn't exist".. doesn't matter if you can print a pretty string, 
you are not calling the bind routine with the proper inputs. 

Followup March 23, 2004 - 6am Central time zone:

No, it means you have a bug in the code you layered on top of the code.

 

Followup May 18, 2004 - 2pm Central time zone:

if you have my book effective Oracle by design -- i go into this in great detail.


Basically: 

select * from emp where ename = :x;


-- no matter what you put into :x, it'll treated as a string to be compared to ENAME.


select * from emp where ename = '&X';

and I supply

KING' or 'a' = 'a

as &X, all of a sudden, I dump the entire table instead of a row, or even:

KING' or (select some_function_I_normally_wouldnt_run(x) from dual) = '1

and I run a function I shouldn't and so on.  You are injecting ANY SQL you want into the system 
without binds.




 

Followup May 19, 2004 - 7am Central time zone:

it is "measurement errors" and different granularities of the clocks.

If you have either

"Effective Oracle by Design" or
"Optimizing Oracle Performance" by Cary Millsap

we both go into this -- he does more so.

Basically, when you measure lots of tiny events -- you can sometimes get just 0's and 1's back (if 
they take about the time your clock ticks take) -- or they could be off by 1 clock tick in general. 
 add up a couple thousand of them and they tend to average out -- but it can be all by a little.

basically -- it just means in your case that cpu and ela are the same in effect. 

Followup May 19, 2004 - 9am Central time zone:

get them my book "Effective Oracle by Design"

there i not only benchmark it in that "language du-jour", but explain the sql injection in detail.

they cannot make the leap? they don't understand the FUNDEMENTAL difference between:


  select * from emp where ename = :x

and 

  "select * from emp where ename = '" & anything_the_end_users_want_to_send_us & "'";


??????? really?  they don't get that simple concept -- that the end user filling in a form can send 
anything they want?


have they googled

"sql injection"

on the web????  


as a database manager, I reject out of hand as buggy any code that isn't making use of bind 
variables.  developers (of which I happen to be one actually) -- can do whatever they want in test, 
but in production -- they don't make the rules.  really.  


Here is a snippet from the book:



(3)Without Bind Variables, Your Code Is Less Secure
Another concern when you don't use bind variables revolves around security, specifically the risk 
of SQL injection. To understand how it works, suppose that we have an application that asks a user 
for a username and password. We execute this query:
select count(*) from user_table where username = THAT_USER
and password = THAT_PASSWORD
This seems innocent enough right? Well, let's use SQL*Plus to test that theory and see what happens 
with and without bind variables.
Tom: Please re-break the highlighted code throughout the chapter. The maximum code lengths for 
Oracle books are 78 characters (regular font) and 82 characters (small font). Thanks, Monika
ops$tkyte@ORA920> create table user_table 
  2 ( username varchar2(30), password varchar2(30) );
Table created.

ops$tkyte@ORA920> insert into user_table values 
  2 ( 'tom', 'top_secret_password' );
1 row created.

ops$tkyte@ORA920> commit;
Commit complete.

ops$tkyte@ORA920> accept Uname prompt "Enter username: "
Enter username: tom
ops$tkyte@ORA920> accept Pword prompt "Enter password: "
Enter password: i_dont_know' or 'x' = 'x
Notice the password we just used. It incorporates a little SQL there doesn't it? Since we are just 
gluing strings together, not binding, the end user can actually type in arbitrary SQL and have it 
executed! Our application takes this string now and continues on:
ops$tkyte@ORA920> select count(*)
  2    from user_table
  3   where username = '&Uname'
  4     and password = '&Pword'
  5  /
old   3:  where username = '&Uname'
new   3:  where username = 'tom'
old   4:    and password = '&Pword'
new   4:    and password = 'i_dont_know' or 'x' = 'x'

  COUNT(*)
----------
         1
Look at that. Apparently, the password `i_dont_know' or `x' = `x' is our password. But if we use 
bind variables instead and accept the exact input from the end user, we see this:
ops$tkyte@ORA920> variable uname varchar2(30);
ops$tkyte@ORA920> variable pword varchar2(30);
ops$tkyte@ORA920> exec :uname := 'tom'; 
ops$tkyte@ORA920> exec :pword := 'i_dont_know'' or ''x'' = ''x';

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select count(*)
  2    from user_table
  3   where username = :uname
  4     and password = :pword
  5  /

  COUNT(*)
----------
         0
We get the correct answer. 
Think about this the next time you put an application out on the Internet. How many hidden 
vulnerabilities might you have lurking in there if you develop your application using string 
concatenation instead of bind variables? Think of the "neat" side effects someone could have on 
yo