Home » RDBMS Server » Performance Tuning » Bind variable in a data warehouse
icon5.gif  Bind variable in a data warehouse [message #332805] Wed, 09 July 2008 15:11 Go to next message
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 #332806 is a reply to message #332805] Wed, 09 July 2008 15:19 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

How can anyone reproduce your problem based upon the complete lack of details in your post?
Re: Bind variable in a data warehouse [message #332833 is a reply to message #332805] Wed, 09 July 2008 22:20 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Bind variable in a data warehouse [message #333174 is a reply to message #332833] Thu, 10 July 2008 12:34 Go to previous messageGo to next message
stegau
Messages: 4
Registered: August 2007
Location: Montréal
Junior Member
How do you say to Oracle, before making the parsing, to retain the values instead take the bind variables?
Re: Bind variable in a data warehouse [message #333383 is a reply to message #333174] Fri, 11 July 2008 07:54 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can't.

Read again what I wrote above. If it is not clear, explain why it is not clear and I will try to clarify.

Ross Leishman
Previous Topic: Query running slow
Next Topic: GATHERING STATISTICS
Goto Forum:
  


Current Time: Sun Dec 04 10:55:21 CST 2016

Total time taken to generate the page: 0.07606 seconds