首页 > 数据库 > Oracle > [ASK TOM ]JAVA AND BIND VARIABLE


原创 Oracle 作者:cyzhang1983 时间:2010-07-07 11:36:59 0 删除 编辑

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

Submitted on 1-Jun-2003 20:12 Central time zone
Last updated 26-May-2010 7:38

You Asked

Hi Tom,

I got given a statspack report to check out.

The Parse CPU to Parse Elapsed seems terrible.
In you book Expert 1 on 1 page 481
your example was 87.88% and you say for every CPU second
it spent about 1.13 seconds of wall clock time. How did you
work out 1.13 seconds please?


STATSPACK report for

DB Name DB Id       Instance     Inst Num Release     OPS Host
------- ----------- ------------ -------- ----------- --- ------------
PRD     1921956436  XPS_PRD             1   NO  bd01

                Snap Id     Snap Time      Sessions
                ------- ------------------ --------
 Begin Snap:         26 31-May-03 10:58:42       93
   End Snap:         31 31-May-03 16:46:07       93
    Elapsed:                 347.42 (mins)        

Cache Sizes
         db_block_buffers:     230400          log_buffer:    1048576
            db_block_size:       8192    shared_pool_size:  524288000

Load Profile
~~~~~~~~~~~~                      Per Second       Per Transaction
                               ---------------       ---------------
              Redo size:            146,489.66             29,758.19
          Logical reads:            111,987.73             22,749.40  
          Block changes:                353.31                 71.77
         Physical reads:                446.84                 90.77
        Physical writes:                132.29                 26.87
             User calls:              1,161.46                235.94
                 Parses:                107.85                 21.91  
            Hard parses:                  1.78                  0.36  
                  Sorts:                 71.24                 14.47
                 Logons:                  0.03                  0.01
               Executes:                122.73                 24.93
           Transactions:                  4.92

% Blocks changed per Read:  0.32    Recursive Call %:   5.19          
 Rollback per transaction %:0.00    Rows per Sort:   45.56    
Instance Efficiency Percentages (Target 100%
     Buffer Nowait %:  100.00       Redo NoWait %:  100.00       
     Buffer  Hit   %:   99.60    In-memory Sort %:  100.00        
     Library Hit   %:   99.28        Soft Parse %:   98.35
     Execute to Parse %:   12.12         Latch Hit %:   99.85
Parse CPU to Parse Elapsd %:    2.38     % Non-Parse CPU:   98.98

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   95.29   95.25
    % SQL with executions>1:   36.44   70.67
  % Memory for SQL w/exec>1:   29.17   41.40

Top 5 Wait Events
~~~~~~~~~~~~~~~~~                                    Wait     % Total
Event                                   Waits      Time (cs)   Wt Time
------------------------------------ ------------ ------------ -------
latch free                             1,335,053    3,161,609   74.83
db file sequential read                1,367,706      438,661   10.38
db file scattered read                   697,792      310,837    7.36
log file sync                            105,273      128,159    3.03
log file parallel write                  106,085      108,835    2.58
-> cs - centisecond -  100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)

and we said...

It is just percentages.  if you have 87.88% (.8788) you just divide:

ops$tkyte@ORA920> select 1/.8788 from dual;


If parse cpu to parse elapsed was 87.88%, then for 1.13 seconds must be elapsed for each 
1 cpu second in order to get the ratio 87.88%

Looking at your latch frees, I would say your system doesn't use bind variables (thats in 
the book as well, I think almost every page says "use them or you lose")

347 minutes is 330 minutes TOO LONG for statspack.  Use a 15 minute window!

Use this script:
to find your problem queries.  I've got a feeling however, many of your queries are going 
to pop out in that report.  The developers MUST use binds.  You'll need to get your soft 
parse % up well over 99%


5 stars Developers sent me a link... AGAINST BINDING! June 5, 2003 - 1am Central time zone
Reviewer: Alvin from Philippines
I have a difficult time convincing them to use prepared statements (PS) in JAVA. And for that i get 

STATSPACK report for

DB Name         DB Id    Instance     Inst Num Release     OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
xxxx          xxxxxxxxxxx                    1   NO  dbname

                Snap Id     Snap Time      Sessions
                ------- ------------------ --------
 Begin Snap:         31 05-Jun-03 03:02:19      139
   End Snap:         32 05-Jun-03 04:09:33      139
    Elapsed:                  67.23 (mins)

Cache Sizes
           db_block_buffers:      87500          log_buffer:     163840
              db_block_size:       8192    shared_pool_size:  512000000

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:              8,970.49              2,589.41
              Logical reads:              6,458.52              1,864.31
              Block changes:                 47.08                 13.59
             Physical reads:                 68.60                 19.80
            Physical writes:                 27.95                  8.07
                 User calls:                301.12                 86.92
                     Parses:                 52.53                 15.16
                Hard parses:                 10.26                  2.96
                      Sorts:                 21.27                  6.14
                     Logons:                  0.14                  0.04
                   Executes:                 52.37                 15.12
               Transactions:                  3.46

  % Blocks changed per Read:    0.73    Recursive Call %:   17.58
 Rollback per transaction %:    0.00       Rows per Sort:    5.01

Instance Efficiency Percentages (Target 100%)
            Buffer Nowait %:  100.00       Redo NoWait %:   99.98
            Buffer  Hit   %:   98.94    In-memory Sort %:   99.94
            Library Hit   %:   87.04        Soft Parse %:   80.47
         Execute to Parse %:   -0.30         Latch Hit %:   99.98
Parse CPU to Parse Elapsd %:   83.51     % Non-Parse CPU:   99.99

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   84.24   86.20
    % SQL with executions>1:   51.80   65.49
  % Memory for SQL w/exec>1:   22.83   42.57


How come i have a high Non parse CPU ? shouldn't the cpu be parsing all the time based from the 
stats ? 

                               Get                                  Spin &
Latch Name                    Requests         Misses      Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
library cache                   3,310,698       8,945      11,174 3036/1648/33
shared pool                     1,556,136       1,570         365 1329/134/95/
redo writing                       77,126       1,018           2 1016/2/0/0/0
row cache objects               1,527,365         488           7 482/5/1/0/0
cache buffers chains           51,607,754         231          30 211/17/1/2/0
session idle bit                2,631,371         129          46 86/41/1/1/0
session allocation                408,936          34           7 27/7/0/0/0
cache buffers lru chain           198,693          10           4 6/4/0/0/0
enqueues                          275,358           8           2 6/2/0/0/0
latch wait list                     6,111           7           2 5/2/0/0/0

It's bad i know... and i get 4031 errors !! and i'm not a bit surprised !! 

1. my question is why does exec statspack.snap take around 10 mins++ to finish ?
2. what does this mean 'library cache            kglhdgn: child:                  0     10,531   

The head java leader is getting annoyed of me asking them to use PS. I was surprise that they found 
the above link.

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 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 

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");
    Integer iters = new Integer(arr[0]);
    doStatement (con, iters.intValue() );
    doPreparedStatement(con, iters.intValue() );

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++)
      ("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();
    ("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 =
       ("insert into testxxxperf " +
        "(id, code, descr, insert_user, insert_date)"
        + " values (?,?,?, user, sysdate)");

    for (int i = 0; i < count; i++)
      ps.setString(2,"PS - code" + i);
      ps.setString(3,"PS - desc" + i);
    long end = new Date().getTime();
    ("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 

  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 "+
                       "( ?, ? )" );


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++)
      ("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();
    //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.
5 stars Amazing! June 5, 2003 - 9am Central time zone
Reviewer: Christo Kutrovsky from Ottawa, ON Canada
That's a very important piece of code to know.

I am not yet involved in JAVA, but I sure will be some day, and I'll need to know how to prove the 
BIND variables point.

Thank you Tom.

2 stars Lots of PreparedStatement questions left unanswered March 15, 2004 - 10pm Central time zone
Reviewer: Old, but young at SQL from Silicon Valley
Thanks for the education about bind variables and the chapter from your book (and many related 
similar pages).  I'm getting 00604 and an 04031 errors, but I don't know why.  It doesn't happen 
here, just in the field, with the exact same environment, and near identical usage.  

Perhaps it's the lack of PreparedStatement use in places, but I don't use connections that long, 
yet the errors keep happening- it seems like closing connections (not pooling, at least in our 
code...) isn't releasing the memory!
Q: Shouldn't all these resources be released when I close the connection?

I get the multi-vs-single user issue, which doesn't apply to this project, but not the shared 
memory issue.  
Q) If I do sequential inserts using Statement (not PreparedStatement), does it keep around the 
memory and the latches after I use the statement object a second time? 
Q) How about if I close the statement and then create a new one?  
The Java docs imply that re-use and closing both release associated Java and database resources.

I also get the parse-time issue- that's big.  But I don't get why more latching occurs with 
Q) Does some latching occur during the parse?  Why?
I have only 3 processes using the database at a time, and only a few very lightly used tables are 
possibly read and written at the same time.

Note: I prefer Statement over PreparedStatement because the latter are so hard to debug.  If 
there's a bad value, it doesn't even say which field!  The errors are obscure. I guess I should 
subclass it and all the setType methods and salt away the values and reconstruct them for printing 
if there's an exception- 
Q) Has anyone done this?

About 90% of the time PreparedStatement seems to work.  So I went back to Statement for the rest.  
Statement is so much easier to use/debug that it saves a lot of development time, ie, money, so I 
use it for selects, which I do many of, but each only once per hour (and never multi-user).

I understand the lower parsing time, but so many of my statements are infrequent.  

Q) Sometimes I have constants in my PreparedStatements.  Is this bad?  (I put jobs in a table: 
time,0,0,0,0,0.  Later on when parts complete, they update the column.)  What if I put the whole 
string literal in a PreparedStatement.
Q) Is a PreparedStatement containing no ? fields still more efficient than a Statement?  Is it less 
efficient than putting some/all the fields in later with ps.setInteger()?

I appreciate the help, and will try using PreparedStatements again where they failed before.  I'd 
like some more help at times- I know you're busy.  In the 6 months I've been on this project this 
site has refused questions...  
Q) Where can I go to buy some help, per question or per hour?

(other pet peeve:  you can't query a connection for the number of open statements.  If this were 
possible, I could leave connections open with confidence...  I guess I have to take the time to 
subclass everything and keep track myself, sigh..., and trap exceptions in the class, and when one 
happens, close the statement and then re-throw...)


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 

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 

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);

new questions and performed:

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

followups like this one.......

but what resources are out there?  how about

o ->*  The internet usenet newsgroups.  There you'll get 
lots of people willing to provide you advice.

o -> 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.

2 stars Answers to your questions, and a few things are not yet clear March 16, 2004 - 2pm Central time zone
Reviewer: Old, but young at SQL from Silicon Valley
It seems like from your answer, if I create a statement, use it and close it, it keeps using more 
and more shared memory until I close the connection. I can accept that.  (Note that it still 
doesn't make sense- why would Oracle keep it around?  If people aren't getting it, perhaps you're 
not sharing a piece of the reason.  I can take it on faith, but I can internalize it much more 
deeply if I know why.)

Maybe the problem is different though- You say:
> statements/prepared statements are no different than a file.

So which has more memory in use at a single time:
   Statement foo = conn.createStatement();
   foo.execute("select foo from bar where xxx");
   foo.execute("select foo2 from bar where yyy");
   Statement foo;
   ResultSet rs;
   foo = conn.createStatement();
   rs = foo.execute("select foo from bar where xxx");
   foo = conn.createStatement();
   rs = foo.execute("select foo2 from bar where yyy");

I was assuming they were the same, based on the docs, that re-using foo closes rs AND any resources 
associated with the first select. But my results would be explained if it were not true, that 
Statement.execute() opens a file till the close() (besides the ResultSet),  rather than a 
createStatement() opens a file.

> if connections are not being released, you are using a connection pool 

Nope, no pooling:

  Open a connection,
     create statements A and B
        do 3-10 queries with statement A
           each with 1-20000 inserts with statement B
     close the statements
  close the connection

So I'll change the inserts to all use PreparedStatements.  If I can't get them to work, I'll 
close/reopen the statement every 50 or so inserts.

You asked why PreparedStatement is hard to debug.

With a Statement, I can print out the String either before the exception happens, or as part of the 
exception.  Then I can copy/paste it into SQLPlus and get a real error message that points me to 
some exact place in the query.

With a PreparedStatement, I have to write my own code that assembles the string and prints it out.  
Sometimes I assembled the statement by hand and it worked fine, but the PreparedStatement still 
complained about a bind variable (whoever left out WHICH variable and its value out of the error 
msg should be shot.)  I know it's not the way to run a project, but I don't have much time, and the 
Statement worked, and there's no hint in the JDBC API docs that there was anything wrong with 
Statement, except I figured it took a lot longer, but performance was fine.  Until now.  Yes, I'll 
fix it.

Often in development I'd work and re-work queries.  Doing this in a string, the Select query LOOKS 
like itself.  In my queries, which return lots of rows, the "parameters" configure the query rather 
than pinpointing one answer, so seeing them in the query helps a lot.  In some of them the database 
table was a parameter.  So semantically, the "variables" are different from what Oracle thinks of 
as the "bind variables."  Having it all laid out is clearer.  

Putting in a ?, it's easier to set things in the wrong order.  Say I rework three things in the the 
query and then go fix the order and ooops, I fixed the last two stmt.setString() but not the first. 
 Since the data in my queries is meaningful to the programmer, separating the data from the query 
makes it more error prone.  Being called in to learn a new industry and their poorly designed 
database and undocumented database/application/industry is bad enough, using programming constructs 
that obscure clarity is a hindrance.  (In fact, were I to do it over again, the only change I'd 
make is to PLAN to change to PreparedStatements at the END.)

Does it really matter if the 1-10 Selects are Statements vs PreparedStatements?  Each happens once 
per connection so it won't save any time.  I can easily change to closing each one after I use it.  
I don't have time to change all the Selects, and it has to ship, so too many changes is 

Q) How do I get Oracle to tell me the amount of connections and statements that are open and the 
amount of shared mem in use?

Again, many thanks for the help, and the pointers to others.

I have two days to fix a problem that takes 4 days to manifest...  


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 

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.

3 stars The end is near, thanks! March 16, 2004 - 8pm Central time zone
Reviewer: Old, but young at SQL from Silicon Valley
> But over in your shared ...  A waste of energy, we compiled a subroutine .. and .. manage it 
until .. age it out of the shared pool subpool ...
> We keep it around in the hopes that the applications were designed to be 

So if I am running the same query once every hour, that's fine to use as a Statement, because the 
procedure et al will either have aged out or will be re-used?

It just seems that when available memory in the pool gets low, Oracle should jettison these cached 
procedures, instead of throwing an out-of-memory error...

But thanks, that explains most of it.  But it still leaves 2 questions:

1. If my PreparedStatement contains literals, like "SELECT foo FROM table WHERE a=0 AND b=?", does 
the use of '0' cause the same sort of memory pool loss as a Statement?  Is it better?  Perhaps when 
this is closed the procedure made for it is completely freed?

2. How can I monitor the shared memory pools in SQLPlus?

Thanks much,

After this ships, I will register and follow that link and learn about Pl/Sql. 

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. 
4 stars One last question (I promise) March 17, 2004 - 12am Central time zone
Reviewer: Old, but young at SQL from Silicon Valley
If I make a PreparedStatement with no bind variables, but still execute it as a prepared statement, 
is this exactly the same as a Statement, or will the PreparedStatement's procedures et al be nuked 
when the PreparedStatement is closed (where the Statement's procedure et al is cached in the shared 

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

The prepared statement, when closed, will be just like a closed statement. 
5 stars Thanks March 17, 2004 - 12pm Central time zone
Reviewer: Old, but young at SQL from Silicon Valley
Running out of memory due to inability to flush a cache seems like a serious Oracle bug.  I'd 
submit it if I knew how.

Thanks much for the education.  It sounds like the way the customer used my program in testing 
aggravated the problem, that the cache would have aged out most queries in production.  With the 
fixes, using PreparedStatements for the voluminous queries, there should be no problems.

Long term, I'll subclass PreparedStatement to handle errors better and systematically and track the 
number of open statement, to support keeping connections open.  Or go to Pl/Sql  (I'll surf this 
site for a book req.)

Thanks MUCH- your fast responses were a godsend on this one.

4 stars How long does it take for something to age out of the cache? March 21, 2004 - 12am Central time zone
Reviewer: Old, but young at SQL from Silicon Valley
How long does it take for a query to age out of the cache?

(I worked 70 hours last week, and thee's a ton of other things to do- I don't want to take the 
extra time to change 20 complex queries into PreparedStatements- each is used once per hour (most 
of the time would be in testing.)  The changes I already made reduced the number of Statements by a 
factor of 100 so far, but my boss is afraid there'll be pressure to change all of them. )


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. 
1 stars March 22, 2004 - 2pm Central time zone
Reviewer: Old, but young at SQL from Silicon Valley
Can you give me any sense of average times, 
or the algorithm used to determine leaving the queue,
or how to use the output of 
   select * from v$sgastat
to determine this?  The customer wants a guarantee that it won't happen again (but wants it shipped 
this week, with other tasks, as well.)

CREATE table tmp_DS AS 
 ORA-01027: bind variables not allowed for data definition operations

Interesting.  I suppose I could make a temporary table, but the values in there, and use its values 
for this query...  I can see why you recommend PL/Sql 

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.

DDL btw is not in the shared pool, therefore, bind variable = not used for DDL as it is not 
3 stars One more, sigh March 22, 2004 - 7pm Central time zone
Reviewer: Old, but young at SQL from Silicon Valley
Okay, I wrote 3 subclassed which delegate to
  Connection, Statement and PrepStatement
For PreparedStatement I also make an array of the 
parameters that come in so if an exception happens,
I can print out the query and paste it into SQLPlus.

Here's what my routine produces:

Error: prep.executeUpdate() with query
  INSERT INTO LastDataFeed
          (t1, s1, t1p, s1p, t2, s2, periodMin, curtime, num) 
   values (?, ?,  ?, ?,  ?, ?,  ?, ?, ?)
  INSERT INTO LastDataFeed
          (t1, s1, t1p, s1p, t2, s2, periodMin, curtime, num) 
   values (1069804800000, '2003-11-26 00:00:00.000',  1069805100000, '2003-11-26 00:05:00.000',  
1070496000000, '00:00:00',  5, '23:28:44', 0);
java.sql.SQLException: ORA-01006: bind variable does not exist

And when I paste it into SQLPlus, it works!

Hmmm, if in my code I respond to the exception by calling
I get the same error.  But if I make a Statement and call

For completeness, the table is:
 Name        Type
 T1         NUMBER
 S1         VARCHAR2(25)
 T1P        NUMBER
 S1P        VARCHAR2(25)
 T2         NUMBER
 S2         VARCHAR2(25)
 NUM        NUMBER(9)

(I did a search on ORA-01006, but the 3 results were just for Pl/Sql...
Maybe I should just hack- if the prepStatement doesn't work, maybe I should submit the mfg'd query 
as a Statement, sigh...)

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. 
1 stars But what does that mean? March 22, 2004 - 11pm Central time zone
Reviewer: Old, but young at SQL from Silicon Valley
Does this mean one of the field names is wrong?
If that were true the string produced out of the query+args would not produce errors as well.

I think the problem was a combination of incomplete docs and presumtuousness on my part.  I was 
also using the PreparedStatement as a Statement-- it inherits Statement's
routine, so I assumed it would execute a query (with no variables) without disturbing the 
prepared-query.  I guess the queryString overwrote the earlier one that had the '?'s, and it was 
complaining that it couldn't bind the vars I gave it to the string with no '?'s...

'My bad.

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.

3 stars New, related problem March 30, 2004 - 5pm Central time zone
Reviewer: Old, but young at SQL from Silicon Valley
Maybe I didn't explain the previous one well.  Executing a statement using a PreparedStatement 
object was nuking the PreparedStatement.
   1 PreparedStatement p = con.prepareStatement("select x from foo where bar=?");
   2 ResultSet rs = p.executeQuery("select count(*) from foo");
   3 ResultSet rs = p.executeQuery();
throws an error.
Making a separate Statement object for executing line 2 allows line 3 to succeed.

The code also makes temporary views that focus on different aspects of an hour of data, then 
queries use these views.  The view name changes, to avoid multithreading problems.  My guess is 
that even preparedStatement queries won't be reused from hour to hour because each references a 
different viewname.  True?  

One solution would be to pool and reuse the view names.  

5 stars SQL Injection May 18, 2004 - 7am Central time zone
Reviewer: Reader
Dear Tom,

Great answers....Can you please explain with regards to the Oracle Architecture as to How does the 
use of bind variables protects against SQL injection.


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

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


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.

5 stars Strange CPU x Elapsed time May 18, 2004 - 5pm Central time zone
Reviewer: A reader
Why might CPU time be greater than elapsed time in tkprof ? I'm using 9ir2. I'm not using binds -- 
this is only a test case.

        join historico on ticket.ID_ULTIMO_HISTORICO = historico.id_historico
        join problema on problema.id_problema = historico.id_problema
        join status on status.id_status = historico.id_status
        --join RESP_CAUSA tbl_resp_item on historico.ID_RESP_ITEM = tbl_resp_item.ID_RESP_CAUSA
        join causa on causa.id_causa = ticket.id_causa
        --join previsao_retorno on historico.id_prev_retorno = previsao_retorno.id_previsao
        join qry_clientes on = ticket.id_cliente
        join qry_operadores on
        join qry_empresa on qry_operadores.id_empresa=qry_empresa.id_empresa where ( in ( 575, 2571, 22720, 2
15, 1390, 1104, 36220, 1917, 26481, 20740, 3824, 170, 349, 33100, 231, 34000, 2128, 21103, 393, 
850, 250, 2627, 67, 447, 676,
884, 2173, 1531, 921, 3360, 5873, 352, 30740, 1453, 38104 ) and (  ( historico.id_status<>2 and 
historico.id_status<>5 ) or (
historico.id_status in (2,5) and historico.data_evento > to_date('2004/05/14 13:50:24', 'YYYY/MM/DD 
HH24:MI:SS') ) ) )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      1.06       1.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.94       0.91        593       1424          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      2.00       1.95        593       1424          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 95

Rows     Row Source Operation
-------  ---------------------------------------------------
    195   NESTED LOOPS
    195    HASH JOIN
    195     NESTED LOOPS
    195      NESTED LOOPS
    195       HASH JOIN
   7783        NESTED LOOPS
   7783         NESTED LOOPS
   7783          HASH JOIN
     32           NESTED LOOPS
     35            INLIST ITERATOR
     35             INDEX RANGE SCAN PK_EMPRESA (object id 42459)
     32            INDEX UNIQUE SCAN PK_CLIENTE (object id 42596)
   7783           INDEX FAST FULL SCAN IDX_T_UL_H (object id 44783)
   7783           BITMAP INDEX SINGLE VALUE NDX_TBL_RESP_CAUSA (object id 41387)
   7783         INDEX UNIQUE SCAN CAUSA_PK11049574450818 (object id 33567)
    195       INDEX UNIQUE SCAN STATUS_PK11049574471311 (object id 33576)
    195      INDEX UNIQUE SCAN PROBLEMA_PK11049484157278 (object id 33573)
   7430     VIEW
    195    INDEX UNIQUE SCAN PK_EMPRESA (object id 42459)


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. 
5 stars SQL Injection May 19, 2004 - 12am Central time zone
Reviewer: Reader
Thanks Tom for the explanation you have provided. But I would appreciate if you give the same 
example in JAVA because I am having a hard time convincing the JAVA developers the same thing. I 
managed to convince them regarding bind variables and Shared Pool usage but I am not able to 
convince them the same for SQL Injection since they are saying how come a Statement object and 
PreparedStatement object matters. I don't have much experience with JAVA so I hope you can give me 
a example which I can show to my JAVA Developers.Regarding your new book that is the first thing I 
am going to purchase this weekend. 

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


  "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'

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> select count(*)
  2    from user_table
  3   where username = :uname
  4     and password = :pword
  5  /

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 

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录