2

I have a vey huge query. It is rather large, so i will not post it here(it has 6 levels of nested queries with ordering and grouping). Query has 2 parameters that are passed to it via PreparedStatement.setString(index, value). When I execute my query through SQL Developer(replacing query parameters to actual values before it by hand) the query runs about 10 seconds and return approximately 15000 rows. But when I try to run it through java program using PreparedStament with varibales it fails with ORA-01652(unable to extend temp segment). I have tried to use simple Statement from java program - it works fine. Also when I use preparedStatement without variables(don't use setString(), but specify parameters by hand) it works fine too.

So, I suspect that problem is in PreparedStatemnt parameters.

How does the mechanism of that parameters work? Why simple statement works fine but prepared one fails?

maks
  • 5,911
  • 17
  • 79
  • 123
  • What version of Oracle are you using? The way the optimizer handles bind variables changed significantly between 10 and 11. – Jon Heller Jul 29 '13 at 18:41
  • 1
    Do an "explain plan" to see what's happening: http://stackoverflow.com/questions/4376329/jdbc-oracle-fetch-explain-plan-for-query – paulsm4 Jul 29 '13 at 18:56
  • I suspect jonearles is right... an explain plan performed in SQL Developer (though useful to rule stuff out) probably won't help if it returns in 10s. It's more likely that something funky is going on in Java. The first thing to check though is that your queries are _identical_... – Ben Jul 29 '13 at 20:54
  • 2 jonearles: I'm using oracle 10g – maks Jul 29 '13 at 21:12
  • 2 ben: what do you mean under identical? They are identical(in sense of query text), one is prepared and another is simple. Somewhere on the web I've found that in case of prepared statement oracle doesn't use indexes and perform full scan search, but I don't think this is true. – maks Jul 29 '13 at 21:17
  • 2
    Do some googling on "oracle variable bind peeking" Here is a link to one article. http://www.dba-oracle.com/t_bind_variable_peeking_tips.htm My guess is that oracle is using a different plan with the prepared statement and bound parameters. You may need to add a hint to your query such that it uses the same indexes, etc. that are used when then query is run with literals. You may be able to simulate the same problem with using variables in a sqlplus session for the query as is done in your java code. – OldProgrammer Jul 30 '13 at 00:36
  • I have made a plan for statements and they are different. Now I think its time to optimize that query – maks Jul 30 '13 at 12:58

1 Answers1

1

You're probably running into issues with bind variable peeking.

For the same query, the best plan can be significantly different depending on the actual bind variables. In 10g, Oracle builds the execution plan based on the first set of bind variables used. 11g mostly fixed this problem with adaptive cursor sharing, a feature that creates multiple plans for different bind variables.

Here are some ideas for solving this problem:

Use literals This isn't always as bad as people assume. If the good version of your query runs in 10 seconds, the overhead of hard-parsing the query will be negligible. But you may need to be careful to avoid SQL injection.

Force a hard-parse There are a few ways to force Oracle to hard-parse every query. One method is to call DBMS_STATS with NO_INVALIDATE=>FALSE on one of the tables in the query.

Disable bind-variable peeking / hints You can do this by removing the relevant histograms, or using one of the parameters in the link provided by OldProgrammer. This will stabilize your plan, but will not necessarily pick the correct plan. You may also need to use hints to pick the right plan. But then you may not have the right plan for every combination of inputs.

Upgrade to 11g This may not be an option, but this issue is another good reason to start planning an upgrade.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132