 Bind variable in a data warehouse [message #332805] |
Wed, 09 July 2008 15:11  |
stegau
Messages: 4
Registered: August 2007
Location: Montréal
|
Junior Member |
|
|
Hi,
We have an application that generates SQL in our database with Bind variables.
Our database is a data warehouse. The DB is partitioned and tables in problems have about 300-400 GB.
When our requests have binds variables, the response time is very long. When they are not present, the response time is better.
Thus, the access path is less good with binds variables since Oracle is unable to find the desired partition.
It does not seem that we make a modification of the tool.
How could we say to Oracle does not take the bind variables to parsing? How do I say to Oracle always take the values and re-parsing without bind variables?
thank you in advance.
-------------------------
Français :
Bonjour,
Nous avons une application qui génère des requêtes SQL dans notre base de données avec Bind variables.
Notre base de données est partitionnées et les tables en problèmes ont environ 300-400 Go.
Lorsque nos requêtes ont des binds variables, le temps réponse est très long. Lorsqu'elles ne sont pas présentes, le temps réponse est meilleur.
Donc, l'access path est moins bon avec des binds variables puisque Oracle n'est pas capable de trouver la partition désirée.
Il ne semble pas qu'on faire une modification de l'outil.
Comment pourrions-nous dire à Oracle de ne pas prendre les bind variables pour faire le parsing? Comment puis-je dire à Oracle de toujours prendre les valeurs et de refaire le parsing?
merci à l'avance.
|
|
|
|
|
|
| Re: Bind variable in a data warehouse [message #332833 is a reply to message #332805] |
Wed, 09 July 2008 22:20  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| stegau wrote on Thu, 10 July 2008 06:11 | How could we say to Oracle does not take the bind variables to parsing? How do I say to Oracle always take the values and re-parsing without bind variables?
|
In 9i, you can't. Oracle will generate a plan that it considers "optimal" for any bind variable value, and wil then use that plan regardless of the value passed. For some values it may be optimal, for others it may not.
In 10g, Oracle permits "bind variable peeking", whereby the FIRST time you run the SQL it will look at the value of the bind variable and optimise the plan accordingly. Unfortunately, all subsequent executions use the same plan even if the bind value changes.
In 11g, Oracle still uses Bind Variable Peeking, but allows "re-peeking", so the plan may change with the variable value.
Alternatively, you can look into Plan Stability (in the Performance Tuning Manual), or add hints to the SQL so that it chooses the same plan every time.
Ross Leishman
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
