Skip to Main Content
  • Questions
  • Does this code really use BIND variable?

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Learner.

Asked: May 01, 2007 - 12:34 pm UTC

Last updated: May 14, 2007 - 2:06 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Tom

This is one of those "new moon" days where I get a chance to really ask you a new question.

Using one of your scripts to find out the list of unbound SQLS, I came across this particular query in v$sqlarea. But I am not sure whether the report is correct or my understanding is wrong.
            String query_string = "select account_id from customer_accounts where cust_key like 'LON%' and sales_man_id = ?";
            String myStr = "London Customer";
            PreparedStatement PrepStat = dbConn.prepareStatement(query_string);
            PrepStat.setString(1, p_Sales_ID);
            ResultSet myResults = prepStat.executeQuery();
            while (myResults .next()){
                valAmount = 0;
            }
            myResults.close();
            PrepStat.close();


How can this part of code be rewritten efficiently to use BINDS?

and Tom said...

that is using bind variables correctly.

No matter how many times that statement is parsed (so sad that it is parsed every time :( ) it will be:

where cust_key like 'LON%' and sales_man_id = ?

they are looking for a sales man record in a particular, constant cust_key. As long as the cust_key remains LON% everytime the query is executed, it should NOT be bound!


Bind only that which varies (changes) from execution to execution.
Use literals for all else.

Rating

  (22 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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?

Tom Kyte
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.
Tom Kyte
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!
Tom Kyte
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.
Tom Kyte
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)
Tom Kyte
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
Tom Kyte
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

"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

This is totally random. Alberto seems very knowledgeable I was hoping you wouldn't mind looking at a problem we have that no one can solve regarding connection cache on the app server to see if anything pops into your mind.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:22140261281764#84483600346414666

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


Tom Kyte
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.


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