Bind Variables
Learner, May 02, 2007 - 12:30 pm UTC
Tom
Thanks for your feedback. The reason why I asked you was that this query in a span of 2 hours was parsed 2890 times and executed the same number of times. But I am not sure I really understand why it should be parsed every time before being executed? In the following query
where cust_key like 'LON%' and sales_man_id = ?
because it's always going to be 'LON%' and different sales_man_id (which is bound), why won't Oracle pick up a cached and parsed SQL from the cache rather than parsing it again and again?
May 02, 2007 - 6:06 pm UTC
it should not be parsed everytime
your programmers are doing that - they parse it, we just respond to what they tell us to do.
even if it said "where cust_key like ? and sales_man_id = ?" the programmer told us PARSE THIS OVER AND OVER
everytime they prepare a statement, we parse it for them - totally in response to them telling us to do that.
Soft Parsing is occurring
John, May 02, 2007 - 1:07 pm UTC
Learner:
The parse count you see is soft parse as opposed to hard parse. To cut the parse to just one you need to change your application code to only parse one time. Search this site for examples, there are many.
Georg, May 02, 2007 - 4:14 pm UTC
Adding to suggestion by Georg
Stew Ashton, May 03, 2007 - 7:59 am UTC
In the original post, there was no mention of the environment this code runs in. If this is a J2EE Web application, there is no way for the code to "parse once, execute many" because the code itself appears and disappears "many" times.
This is why the Web Application Server, which does stick around, handles the connection pool and the cache of prepared statements. If properly configured, it will cause the statement to be parsed the first time only; the next time, it will return the appropriate PreparedStatement object from the cache.
If this is indeed a J2EE Web application, the code is correctly written, but statement caching is not implemented correctly in the Web Application Server.
If not J2EE, I totally agree with Tom and I think Georg made a good suggestion.
May 03, 2007 - 10:12 pm UTC
sure there is, jdbc statement caching!!!!
and the code itself can certainly cache the statements if it wanted to - there is nothing stopping it from using "class global variables"
parse
A reader, May 03, 2007 - 3:10 pm UTC
Hi Tom,
So what's the right way to write this piece of Java code such that the statement is not parsed over and over again?
Thanks!
May 04, 2007 - 12:41 pm UTC
jdbc statement caching or
prepare it once, never close it, just keep binding and executing it.
To Reader
Andrew York, May 03, 2007 - 3:43 pm UTC
Only call PrepareStatement once! But if you have a multithreaded app (i.e. a Web Based App) be aware that you will have to share and synchronize on that statemet. The performance benefit of preparing once will turn into a bottleneck.
Example:
Thread-A sets param 1 to 'Hello'
Thread-B sets param 1 to 'GoodBye'
Thread-A calles execute
The result for Thread-A ends up becoming what Thread-B wanted.
May 04, 2007 - 12:42 pm UTC
... But if you have a multithreaded app (i.e. a Web Based App) ...
that seems a funny way to say that - why are web based applications inherently multi-threaded?
and each thread could/should have it's own sets of resources.
Re: Statement caching
Stew Ashton, May 04, 2007 - 3:16 am UTC
Yes, Tom, both jdbc statement caching and defining a variable at the class level will work in Java, but why do this yourself if someone else is already doing it for you?
My shop uses Websphere Application Server (WAS), which handles the connection pooling and jdbc statement caching itself. The jdbc methods are subclassed; if the application says:
PreparedStatement PrepStat = dbConn.prepareStatement(query_string);
WAS will check the cache and return a previously prepared statement if it exists; if not, it will invoke the JDBC driver and Oracle will parse the statement. The whole point of a J2EE Server is to handle this persistent stuff and let the application code just come and go. You often say "don't do yourself what the database can do for you". Same here: don't handle connection pooling and statement caching yourself if your J2EE Server will do it for you.
Bottom line: the above code
is correct if it is meant to run within WAS (or another J2EE Server that does the same thing). If it runs stand-alone or in an environment that doesn't provide statement caching, the above code is wrong exactly as you stated, and your and Georg's remarks are on the money.
You cannot determine "the right way to write this piece of Java code" without knowing where it is running. If a J2EE Server is being used, it might just be a configuration problem. Perhaps the original poster would tell us where this code is running?
R: Andrew York and multi-threading
Stew Ashton, May 04, 2007 - 3:40 am UTC
Andrew, three solutions have been mentioned: J2EE Server statement caching, jdbc statement caching and "roll your own" statement caching. I believe you are talking about "roll your own".
The multi-threading issue starts (and ends) with the connection. To scale, the application would have to manage a connection pool and each thread would have to get a different connection from the pool. Now each statement must be prepared once for each connection, you cannot share a PreparedStatement among connections, so you basically have as many statement caches as connections. Once a thread has its own connection, it will have its own statement cache and there will be no further risk of conflict.
Sound like something you want your developers to be maintaining instead of writing business logic? I guess this is why they provide this service in J2EE Servers and JDBC drivers...
Statement caching: test case
Stew Ashton, May 04, 2007 - 11:07 am UTC
Well, I wasn't right enough: the code above could be caching statements with either a J2EE Server or JDBC. Here's an example with JDBC statement caching.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
public class ipilotTest {
public static void main(String[] argv) throws SQLException,
ClassNotFoundException {
Connection dbConn = null;
try {
OracleDataSource ods = new OracleDataSource();
ods.setServerName("PARVA7601017");
ods.setPortNumber(1521);
ods.setDatabaseName("ADM1FRR0");
ods.setUser("ipilot");
ods.setPassword(argv[0]);
ods.setDriverType("thin");
ods.setMaxStatements(1); // set cache to 1
ods.setImplicitCachingEnabled(true); // turn on cache
dbConn = ods.getConnection();
dbConn.setAutoCommit(false);
((OracleConnection) dbConn).setDefaultRowPrefetch(100);
Statement Stat = dbConn.createStatement();
Stat.execute("alter session set sql_trace=true");
for (int i=0; i<10; i++) {
// BEGIN SNIPPET EQUIVALENT TO ORIGINAL POST
String query_string = "select * from user_tables where table_name like ?";
String myStr = "%H%";
PreparedStatement PrepStat = dbConn.prepareStatement(query_string);
PrepStat.setString(1, myStr);
ResultSet myResults = PrepStat.executeQuery();
while (myResults .next()){
}
myResults.close();
PrepStat.close();
// END SNIPPET EQUIVALENT TO ORIGINAL POST
}
} catch (Exception e) {
e.printStackTrace();
} catch (Throwable t) {
System.err.println(t.getLocalizedMessage());
}
finally {
if (dbConn != null) {
dbConn.close();
}
}
}
}
>> TKPROF extract
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 2 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 0.07 0.07 0 6030 0 450
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 0.08 0.07 0 6032 0 450
Now comment out ods.setMaxStatements(1)and ods.setImplicitCachingEnabled(true).
>> TKPROF extract
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 0.06 0.07 0 6030 0 450
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30 0.06 0.07 0 6030 0 450
Followup
Andrew York, May 04, 2007 - 3:10 pm UTC
Tom,
Why are web applications inherently multithreaded: Because the servers they run on typically handle multiple users in seperate threads. It may not be an actual instance of the Thread class, but if there is any notion servicing more than one user at a time, you'd best assume that two request can be running at the same time (no matter how the "Threading" is done.) In the 7 years I've been writing web apps, I've never worked on one that only served one user at a time. If such a thing existed, then a user's 2400bps dialup connection could block all other users until that request finished. (2400bps / 8 = 300Bps; this page alone would take almost 50 seconds to serve up)
May 04, 2007 - 4:02 pm UTC
right, but that does not imply multi-threaded. on Unix, multi-process is the norm in many cases.
eg: Oracle itself is multi-process on unix, multi-threaded on windows.
And the gist is "may not be an actual instance of the thread class"
the guys writing the java programs in general have no clue there is threading going on - they would not serialize around a prepared statement like that - they get their own resources.
Alberto Dell'Era, May 04, 2007 - 6:58 pm UTC
To summarize what Stew Ashton said, in a Java App server each thread
asks a connection to the connection pool
uses the connection (it's the only thread using it)
returns the connection to the connection pool
and each connection has its own PreparedStatement cache (can't be anything different, since a cached PreparedStatement "points" to an open cursor in the Oracle server process, and the open cursor is private to the process).
Actually if I understand correctly what's written below, you can c/connection/session in the pseudocode above if the app server is using "OCI Driver Connection Pooling":
http://download-uk.oracle.com/docs/cd/B19306_01/java.102/b14355/ociconpl.htm#CIHHBDGJ "It is possible for a high-end application server or transaction monitor to multiplex several sessions over fewer physical connections on a call-level basis, thereby achieving a high degree of scalability by pooling of connections and back-end Oracle server processes."
So you get all the possible advantages of multi-threading without its complexities (e.g. thread think time doesn't "lock" a connection, only a session).
PreparedStatement Pool with Proxy User
Jose Caodaglio, May 04, 2007 - 8:17 pm UTC
Tom
I have learned here that each connection can have its own PreparedStatement Pool and it can be enabled implicity or explicity
Will the PreparedStatement pool work with Proxy User, since you call getProxyConnection(... ,ProxyUser) from OracleOCIConnectionPool and the same Physical connection could be used by another Proxy user?
Thanks
May 08, 2007 - 10:00 am UTC
not really - the prepared statement would be cached for a session and with ntier proxy authentication you are creating new sessions after the 'grab' from the connection pool
scripts to find out the list of unbound SQLS
sarayu, May 05, 2007 - 8:18 pm UTC
Hi tom,
May i know where i can get
"scripts to find out the list of unbound SQLS"
Thanks
May 08, 2007 - 10:21 am UTC
"scripts to find out the list of unbound SQLS"
Alberto Dell'Era, May 07, 2007 - 3:43 pm UTC
to Alberto
Alexander, May 08, 2007 - 11:00 am UTC
to Alexander
Alberto Dell'Era, May 08, 2007 - 6:00 pm UTC
Alexander, I've taken a close look and I have only a shot in the dark to offer - the problem seems to me a PreparedStatement cache corruption (or in general a corruption in the state of the JDBC driver) since you mention that recycling the instance (and so reopening the physical JDBC connections) causes the problem go away, and manifest only once after. You might try removing the setQueryTimeout() to see if this not-so-frequently used feature triggers the "bug"; maybe (shot-cubed in the dark) when an update times out, sometimes it might leave the connection, and/or its cache, in an unstable state. Please note that I'm guessing like mad.
Thanks tom and Albert
Sarayu, May 09, 2007 - 1:44 am UTC
Tom,
i used your function remove_constants in our database and found there are more than 30 sql statements which we have to work.
80% of our programs are pro*c, 10% are java and 10% is pl/sql.
I am providing one sql statement here which is from pro*c program:
Actual statement is:
EXEC SQL SELECT nvl(A,' ') INTO :allowCode
FROM TABLE_B
WHERE ID = :profileId AND GRP = :groupId
AND DEV_CDE = :productDevice;
Output from the table t after using the remove_constants is:
SELECT NVL(A, '#')
FROM TABLE_B WHERE ID = '#' AND GRP = '#'
AND DEV_CDE = '#'
106
I assume pro*c already does use bind variable for the sqls given above. How can we ensure that we are going to have all these kind of statements using bind variables.
Thanks again.
Sarayu
May 11, 2007 - 9:30 am UTC
your pro*c code is not doing what you say
do you see the quotes - when you look into v$sql do you see:
a) where id = '1' and grp = '2' and dev_cde = '3'
b) where id = :1 and grp = :2 and dev_cde = :3
if you see a, then what you say is not true.
if you do not see a, I will be surprised.
to Alberto
Alexander, May 09, 2007 - 9:23 am UTC
Thanks, I really appreciate you looking at it. It's on of those things that's so obscure, really tough to track down. It's one of those "another pair of eyes" things. I think you have a case though, I like your idea. Thanks again.
details about my query
sarayu, May 14, 2007 - 5:54 am UTC
Thank you very much for your assistance tom.
Query in the program
--------------------
EXEC SQL
SELECT PRFL_ID, GRP_ID INTO :profileId, :groupId
FROM TABLE_X
WHERE PROD_DEV_CDE = :productDevice
AND SBCR_ID = :subscriberId
AND GRP_ID = :corrNum;
Output from the query using t1 table after using remove_constants
------------------------------------------------------------------
SELECT PRFL_ID, GRP_ID FROM TABLE_X WHERE PROD_DEV_CDE = '#' AND SBCR_ID = '#' AND GRP_ID = '#'
Query output from V$SQL
------------------------
select prfl_id, grp_id from table_x where prod_dev_cde = 'X' and sbcr_id = '12345' and grp_id = '000'.
Can you please help me to identify how to use bind variables in such a query
and also can you please tell me what kind of sql statements in pro*c wont use bind variables.
I thought the above query uses which is using host variables uses bind variables.
May 14, 2007 - 2:06 pm UTC
sorry, someone is tricking you - that pro*c code WOULD NOT result in the sql you see.
Look at the "case" for example, totally different. someone else is executing that query from somewhere else and not using bind variables.
Very informative
sibgat, May 14, 2007 - 8:59 am UTC
Thanks
If you are in application server, watch out for OOME
Vlad Sadilovskiy, June 08, 2007 - 11:29 pm UTC
FYI
Unfortunately, I didn't find one little post on OTN forum that would save me this headache. Had to setup my own test to find what the problem was.
So, to share this experience with whomever is trying to use statement cache in JDBC application. I decided to post it here as well.
Our custom build statement cache solution appeared to be not very well scalable. When anyone tried to increase number of concurrent connections or the size of the cache or enable a feature to keep the cache to subsequent client he/she would get OutOfMemoryError exceptions.
I noticed during my testing by fetching from different "width" tables and playing with prefetch row count, that the wider the row or higher prefetch count were the faster my cache had grown. I debugged my code for many hours looking whether I was closing result sets or for something obvious. Nope everything was fine.
In the end it appeared that JDBC OracleStatement kept reference to so called assessors arrays (like a cache of prefetched rows data) for a result set that had been last executed even when the result set was already closed. The size of that data was proportional to the product of the fetched data (in my tests table) width and prefetch row count. I've tried then JDBC implicit and explicit caching and was getting precisely same memory footprint.
Opened SR and after few weeks was advised by a nice Oracle TS lady that there could be one workaround. It was to use property FreeMemoryOnEnterImplicitCache and so instead of this:
conn = (OracleConnection) DriverManager.getConnection(url, user, password);
use this
OracleDataSource ods = new OracleDataSource();
Properties props = new Properties();
props.put("user", user);
props.put("password", password);
props.put("oracle.jdbc.FreeMemoryOnEnterImplicitCache", true);
ods.setConnectionProperties(props);
ods.setURL(url);
conn = ods.getConnection();
Ultimately, I didn't find a way custom or explicit cache with key can be used effectively, especially if they are LRU caches (the later appeared to be LRU type of cache) - that residue data is going to get into OldGeneration before the statement is reused and the arrays are repopulated.
I really hope the "bug" is going to be fixed in JDBC 10.2.0.4.
- Vlad Sadilovskiy
There was a typo in the message. It's "accessors" array
Vlad Sadilovskiy, June 08, 2007 - 11:32 pm UTC