| How to treat binds as literals [message #143169] |
Wed, 19 October 2005 06:31  |
kojakhu
Messages: 6
Registered: October 2005
Location: Hungary
|
Junior Member |
|
|
Hi!
I am looking for a way to transparently replace bind variables as if they were literals resulting in a better plan.
The hidden parameter: _optim_peek_user_binds
is not a help cause this only affects the first parse of the statement.
Also my experience with bind peeking suggests that it won't affect all bind variables.
By transaparency I mean that I could rewrite the statement, I just prefer not to if possible.
Does anyone have any thoughts?
|
|
|
|
| Re: How to treat binds as literals [message #143212 is a reply to message #143169] |
Wed, 19 October 2005 08:46  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
"I am looking for a way to transparently replace bind variables as if they were literals resulting in a better plan"
I would disagree that bind variables always lead to worse plans.
My approach would be to tune an individual query if necessary, possibly using plan stability, if binds were indeed leading to a poor plan in that particular query.
|
|
|
|
| Re: How to treat binds as literals [message #143409 is a reply to message #143212] |
Thu, 20 October 2005 06:42 |
kojakhu
Messages: 6
Registered: October 2005
Location: Hungary
|
Junior Member |
|
|
Thx for the reply,
In general I share you approach. But in this specific case I have an already written and in production query (developed by many people before it got to me) with about 200 bind occurances !!!
In gerenal binds won't give you alway a worse plan, but in my specific case they do give me a worse plan than literals.
If I replace the binds to literals the query runs fine. And since it is run about 10 times a day at most, there is not much need for binds really. (It won't blow up my libaray cache.)
I have a working solution for this I just want to know if there is a built-in functionality yet unknown to me.
So if you still know of any way to tranparently treat binds as literals, then let me know. And once again thx for the reply.
|
|
|
|
| Re: How to treat binds as literals [message #143451 is a reply to message #143169] |
Thu, 20 October 2005 08:49 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
You may consider "seeding" your shared pool with the query parsed as you like it. Three options that might do this are:
1. Use optimizer plan stability, discussed in performance tuning guide.
2. Use SQL Tuning Sets, also discussed in performance tuning guide.
3. Rely on Bind Variable Peeking to cheat for you by running your query once after database startup before application starts, with a representative set of literals for the binds. See if oracle will peek at those values in doing its initial (and only, if the query doesn't get aged out) hard parse, hopefully coming up with a suitable plan in the process.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
