Tom,

The javadoc from the oracle.jdbc.pool.OracleConnectionCacheImpl class in the JDBC2 drivers distributed with v8.1.7 have setStmtCacheSize() and getStmtCacheSize() methods, which imply that some of the much earlier drivers (Oracle-provided JDBC3 drivers have only been out since 10g??) can do statement caching.

I just hope the documentation isn't misleading :)

matthew

Tom,

My friend referred to me the following link to a oreilly book that says that

There's a popular belief that using a PreparedStatement object is faster than using a Statement object. After all, a prepared statement has to verify its metadata against the database only once, while a statement has to do it every time. So how could it be any other way? Well, the truth of the matter is that it takes about 65 iterations of a prepared statement before its total time for execution catches up with a statement. This has performance implications for your application, and exploring these issues is what this section is all about.

Here is the link:

</code> http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html <code>

Please let me know your thoughts.

Satish.

you righted an article?

Above you state that use of jdbc 3.0 statement caching with prepared statements is the best possible situation. I know we were discussing prepared statements - but where do callable statements (jdbc calling a stored proc) fit in? I assume it is better to use callable statements from jdbc?

But, I could use a stored procedure and a jdbc callable statement for an insert or update too. I can bind my variables for a callable statement just like a prepared statement. Would it be better to use stored procedures and callable statements, or has the jdbc 3.0 client-side statement caching made prepared statements preferable?

As tom points out in the doc.

But Tom
"but why would you use a callable statement when you don't need to?"
Well is not the code in Java versus code in PL/SQL
among other reasons that you have advocated yourself
a good reason to stick to callable statements even
for normal DMLs that could be done by Prepared statement?

Thanx.

callable statement because of other reasons
a. adding a trace and tuning is easier
b. adding another statement to the logic later on
is easier
c. it is accessible to other third party apps and so on.

Unless I missed something?

At work, my code only contains calls to PL/SQL code
even if they could be written within Java as PreparedStatement.

But that is "splitting hair" IMHO :-)

Typically when you use PreparedStatement, you dont use
it to call stored procedures? You should not - even
if your procedure does not return any output. It does
not return one today - but what if it does tommorow -
you have to make more changes than required. There is
no benefit to it and I doubt if most people are even
aware of the distinction.

Also, a, b and c are true assuming the above is true and
we are talking of the choice between using SQL in Java
versus stored procedures...

So did I miss something again?

And Marshall, what is your use scenario - were you
thinking of invoking stored procedures using
PreparedStatement?

I planned to always create stored procedures in packages. I thought they had to be called from java/jdbc using callable statements. I have Tom's books and I thought they advocated always using pl/sql code in packages - maybe that's what I wanted to hear. If a prepared statement can call a stored procedure, I did not know that.

Well, if you use prepared statement, you have
to put your insert, select statement etc in Java. If you
used a stored procedure then you dont have to do
that. This also means that granting privileges is
more secure(execute to stored procedures versus
direct insert privileges on the table to the user)
Tuning is easier because it is easier to just take
your PL/SQL code and tune it in most cases
using tkprof rather than change your SQL in Java,
compile, restart your middle tier and so on.
All the reasons that I learnt from your books and
this site, Tom (just like Marshall did I suppose):-)

So you can use either PreparedStatement or CallableStatement
but using stored procedures in conjunction with
CallableStatement gives you many advantages
(some mentioned above).

The only place where you should switch to using PreparedStatement is when you need to use update batching feature of JDBC. If other tools (such as the usual external tables etc.) have been ruled out for good reasons then it makes sense to use PreparedStatement since Oracle does not implement true batching for CallableStatement objects even though it supports the use of standard batching syntax for these objects. This is of course assuming you are using Oracle supplied JDBC drivers.

It sounded like someone had kidnapped Tom and was writing on his behalf for a little bit there. It was kind of funny how Menon was trying to convince Tom using Tom's own words of why he follows the methodology Tom teaches.

You can create the equivalent effect of batch inserts by passing arrays to pl/sql for bulk inserts. It is no where near as convenient though as just batching single row inserts from the client. I am weighing these options myself. The client application needs to delete cascade and than bulk insert lots of rows from its memory, but I don't want all the users of this client app to have insert and delete on all the base tables to delete old data and reinsert all the new data. so stored procedures are the only approach, but have the added hassle of passing arrays to pl/sql from C++.

"You can create the equivalent effect of batch inserts by passing arrays to pl/sql for bulk inserts. "

Yes - that is another (somewhat more painful at times but
perfectly valid) alternative to update batching - thanx
for pointing it out.

Also, hopefully I came across as giving credit where
it is due to Tom for whatever I "kidnapped" from him :)

Best Regards

Thanks.

In our Java application, we always call stored procedures for any database operations like insert/update/delete etc.

We do have OUT parameter to indicate any error message occured in sp to notify client.

So in this case, my understanding is that we must use callable statements. At one time, i was always thinking they are one and the same. Now i understood the difference.

Would you mind giving an example for Callable prepared statement.

<<If the statement to be executed has only "inputs" -- prepared statements work

In general, stored procedures return outputs (OUT parameters) for those you must
use callable statements
>>


Thanks

Tom,

My question for the problem is simple, I cannot figure out my the following two statements return different result. I'm trying to capture the benefits of binding with PreparedStatements but the query results are not the same.

Statement stmt = conn.createStatement();
//stmt.execute("alter session set sql_trace=true");
int n = 20;

for (int i = 0; i < n; i++)

{
stmt.execute("select id from data where id = " + i);
System.out
.println("The statement finished" + " " + stmt.toString() + " " + i);

}
This yields:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 40 156.25 18.86 0 0 0 0
Execute 40 0.00 22.26 0 0 0 0
Fetch 40 0.00 16.88 0 100 0 40
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 120 156.25 58.00 0 100 0 40

Now the PreparedStatement (which i'm using pretty much verbatium from Oracle 9i Java Programming)

if (ps == null) {
ab = conn.prepareStatement("alter session set sql_trace=true");
ab.execute();
ps = conn
.prepareStatement("select id from data where id = ?");
int n = 20;
for (int i = 0; i < n; i++) {
ps.setInt(1, i);
ps.execute();
System.out.println("The statement finished " + ps.toString()+ " "
+ i);
}

//ps.close();
}
This produces:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.97 0 0 0 0
Execute 2 0.00 1.62 0 0 0 0
Fetch 2 0.00 1.29 0 5 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 3.88 0 5 0 2

The second time I ran the PreparedStatement it produced zero's across the board in the trace file. Is my logic flawed? I'm mostly concerned with the number of rows, that's how I know I didn't do the amount of work it should have.

Tom,

I made a few corrections per your advice. They look ok to me now but I still get weird different results. Mostly intrestingly, different execute counts, AND neither are 20, which is what I set it too. I did change the query slighty to a count(*) to try and cause more work.

Here's the no bind statement:
public class Poor_code {
/**
* @throws RemoteException
*/
public Poor_code() throws RemoteException {
super();

// TODO Auto-generated constructor stub
}

static Connection conn = null;

public static void main(String[] args) throws Exception {
if (conn == null) {
DriverManager.registerDriver(new OracleDriver());
conn = DriverManager.getConnection(
"DatabaseURL",
"user", "password");
}
Poor_code code = new Poor_code();
code.exeBad();
}

public void exeBad() {
try {

Statement stmt = conn.createStatement();
stmt.execute("alter session set sql_trace=true");
int n = 20;

for (int i = 0; i < n; i++)

{
stmt
.execute("select count(*) from data where id = "
+ i);
System.out.println("The statement finished" + " "
+ stmt.toString() + " " + i);

}

// conn.close();

} catch (Exception e) {
System.out.println("Error caught is" + " " + e);
}
}
}

With bind + 1 soft parse:


public class Good_Code {
static PreparedStatement ps;

static PreparedStatement ab;

static Connection conn = null;

public static void main(String[] args) throws Exception {
if (conn == null) {
DriverManager.registerDriver(new OracleDriver());
conn = DriverManager.getConnection(
"DatabaseURL",
"user", "password");
}

if (ps == null) {
ps = conn
.prepareStatement("select count(*) from data where id = ?");

}
ab = conn.prepareStatement("alter session set sql_trace=true");
ab.execute();

int n = 20;
for (int i = 0; i < n; i++) {
ps.setInt(1, i);
ps.execute();
System.out.println("The statement finished " + ps.toString() + " "
+ i);
}

// ps.close();

}

}

No I don't really care about the results. This is just to demonstrate how much better PreparedStatements are for the database. Of course, it isn't a valid test if my stats are showing different numbers of rows processed.

Ok i changed the select query to

insert into t values(?) or i and here's what i observed:

No binds:

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 20 0.00 124.80 0 0 0 0
Execute 21 781.25 952.38 0 20 100 20
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 41 781.25 1077.18 0 20 100 20

Misses in library cache during parse: 20


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

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

Misses in library cache during parse: 0

Then with:

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 1.02 0 0 0 0
Execute 21 1250.00 892.15 0 1 63 20
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 22 1250.00 893.17 0 1 63 20

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

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

Misses in library cache during parse: 0

So it parsed 19 more times but used more cpu? Can you make any sense out of this....

Now that you mention it, yeah I am using 8i tkprof on 9i traces. I didn't know there was a difference. I'll get a newer version of tkprof and see if that does it.

Well thanks for the demo. I guess the differences are not apparent when doing a small amount of work. The example I was following in the book did 10,000 rows. I was trying to duplicate the effort of an actual statement that our application runs. But since it's not resource intensive, the benefits of binds etc are not visible Like you said, there are other things to consider.

I also noticed that sometimes when I run a trace, it appends to an already existing trace file. I thought it was overwriting it. So if the previous trace had 20 rows, then the next I ran had 20, it would show 40 rows total in the file. Is there any rhyme or reason to why a new trace file is created or just appended to? You've been very helpful as always.

Dedicated and straight connection. I'm not positive about the straight connection though.

Hi Tom,
and may be Menon if he sees this ,

i have been trying to use ImplicitStatement Caching with Oracle JDBC Drivers , but it seems the Implicit Statement Caching Feature is not working properly:

For Example:

import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;

OracleDataSource oracleDataSource = new OracleDataSource();
oracleDataSource.setUser( dbUser );
oracleDataSource.setPassword(dbPwd );
oracleDataSource.setURL(dbUrl);

OracleConnection conn = (OracleConnection) oracleDataSource.getConnection() ;

// Enable implicit Statement cache
conn.setImplicitCachingEnabled( true);

System.out.println("Implicit Caching = "
+ conn.getImplicitCachingEnabled()
);


Here conn.getImplicitCachingEnabled() always returns false.

I have an SQL statement that has an 'in' condition. For example

select * from people where id_prsn in (100, 101, 102, 103);

However, the issue here is that the number of IDs within the in condition is determined at run time. As such, I am not sure how to bind this to the statement. I tried doing the dollowing:

select * from people where id_prsn in (?)

And then binding a string of "100, 101, 102, 103" but that does not work. How can I keep the sql static but bind different numbers of elements at run time? If not, can you give me a better solution than constructing the SQL at runtime based on the number of ids.

Thanks.

Hi, great information in this (old) thread. Can you just clarify then - I have a Java program (Java 1.4) that loads data to an Oracle database (Oracle 9i) using a stored procedure. The stored Procedure does not return any values to the Java program. I am not loading a massive amount of records (maybe up to 15,000 at a time) but would like to use batch processing.

Am I right in saying then that as the SP does not return any values that I should use oracle.jdbc.driver.OraclePreparedStatement (as opposed to oracle.jdbc.driver.OracleCallableStatement) which will allow me to use the setDefaultExecuteBatch(), executeUpdate() and sendBatch() methods and that this will result in "true batching" as opposed to just supporting batching syntax?

Thanks.

Hi Tom,

I am now supporting a J2EE application. The application is using PreparedStatement and bind variables where applicable. However one thing I noticed is that developers are always using PreparedStatement.setString to bind a parameter, regardless of data type of the column in the database table.

I have seen cases where setString is used against a NUMBER, VARCHAR, Timestamp column in database.

For example, consider the following example where I have a table t and a NUMBER type column named num_col:

Table t
------------------

create table t (num_col number primary key, resource_name varchar2(25));
insert into t values(1,'resource 1');
insert into t values(2,'resource 2');
insert into t values(3,'resource 3');
commit;

The Java code, use setString on the number column, as follows:

Java
--------------------
PreparedStatement pstmt = conn.prepareStatement("select * from t where num_col = ?");
pstmt.setString( 1, '1');


Similarly, the developers use setString against VARCHAR, Date, Timestamp, ...etc, you name it. To further set the stage, all queries in this application are SELECT statements. There is no INSERT/UPDATE/DELETE. Data modifications are done at other layers.

In light of this situation, I want to get your advice:

1. What is the implication of the above coding practice?
2. Specifically, would it have negative impact to performance and how?
3. Would it cause the implicit conversion as mentioned in index myth "Why Isn¿t My Index Getting Used?" as written in your Expert Oracle book
4. Assuming that you are the development lead, what possible action(s) would you take?

Thanks.

PreparedStatement pstmt = conn.prepareStatement("select * from t where num_col = ?");
pstmt.setString( 1, '1'); 

Hi Tom,

... They should absolutely use: where date_col = to_date( ?, 'dd-mon-yyyy' ) ...

Yup they do this and setString a date to it. Thus the query should be returning correct data given that the date format is explicitly specified.

... equivalent to select * from t where num_col = TO_NUMBER(?)....

Am I correct to say that this is not a big performance hit since TO_NUMBER is executed once per query.

Or is this always the case? Would Oracle implicitly applied a function to the column as follows:

"select * from t where to_char(num_col) = ?"

This would definitely change the execution plan because the index on primary key num_col is now precluded.

I also want to present my case to the developers from performance perspective. For example, why setString on a datatype other than varchar drags the performance. Would be great if you can shed me some light. Thanks.

... and (implicit) to_number will fail as well

Hi Tom,

Thanks for the analysis and background information.