 Bind Variables and complex SQL slow? [message #114837] |
Tue, 05 April 2005 16:49  |
PanamaMike
Messages: 4
Registered: April 2005
|
Junior Member |
|
|
I've recently been involved in the development of a WSAD application that runs against an Oracle 9.2 database. We've recently encountered an odd situation. When making use of bind
variables for a complex SQL query, one that has multiple table joins and conditions, the performance of the SQL is a lot slower than the same exact SQL using literal values.
When looking at the explain plan, the SQL that uses bind variables has a much more expensive plan making use of Hash join , full table scans, and reads in a lot of records. When the same SQL is executed with literals, the cost goes from 14k to around 20 with only a handful of records being read. Consequently, the literal SQL takes less than 1 sec to execute where the bind variable equivalent takes 1:30 a minute 30 sec. to execute. Even on a second run.
Does anyone know why the bind variable SQL is so much slower and worse than the literal counterpart? We where thinking the Peak bind variables setting might help, but it doesn't seem to do anything.
Again.
Oracle 9i 9.2.
SQL via JDBC TOAD.
Any help is much appreciated.
Regards,
Mike
|
|
|
|
| Re: Bind Variables and complex SQL slow? [message #114986 is a reply to message #114837] |
Wed, 06 April 2005 12:33  |
PanamaMike
Messages: 4
Registered: April 2005
|
Junior Member |
|
|
Looks like our problem is due to an ORACLE 9.2.0.5 bug.
Bug 3668224 Bind peeking does not occur for predicates of the form COL <operator> FUNC(:bind)
Meaning that, if we use UPPER or TO_DATE function in the WHERE clause, Bind peeking won't occur. and the explain plan would be different for each SQL in our case.
This issue is fixed in
9.2.0.6 (Server Patch Set)
10.1.0.4 (Server Patch Set)
10g Release 2 (future version)
However, the 9.2.0.6 upgrade introduces a bug that's worse.
Mike
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
