Home » RDBMS Server » Performance Tuning » bind variable peeking and collecting statistics
| bind variable peeking and collecting statistics [message #141891] |
Wed, 12 October 2005 13:19  |
cybotto
Messages: 12
Registered: October 2005
|
Junior Member |
|
|
Hi,
After spending hours tuning a SQL statement on developing and test database it hit the production system and query performance is even worse now.
When removing the bind variable and using dynamic SQL instead the query runs fine again how it should. Oracles new feature of bind variable peeking which is more annoyance.
Somehow not using bind for that particular query is not an answer as well since is getting parsed lots of time.
Now my worry is that when collecting statistics every night, does is invalidate peeking bind variable?
If yes than it would be a kind of gamble and really depends what values the query is getting.
Or better can this feature been switched off?
|
|
|
|
| Re: bind variable peeking and collecting statistics [message #141898 is a reply to message #141891] |
Wed, 12 October 2005 14:01  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Do you really have to gather statistics every night? Does your data change that frequently?
And how exactly do you gather statistics?
How do you remove bind variables and use dynamic sql instead? How is that possible? Doesn't make sense to me.
And yeah, I would think peeking is by its nature in a way a gamble and dependent on the first query ran. One option is to run a specific query of your own choosing after startup so that the value it sees when it peeks is a good one and representative of most of your situations.
And if you only have a couple of different values being used in the query, can consider not using binds in that case.
And you can test whether gathering statistics invalidates a plan by running a query, checking for it in the dictionary, gathering statistics on the table, then checking for it to still be there in the dictionary.
|
|
|
|
| Re: bind variable peeking and collecting statistics [message #141909 is a reply to message #141891] |
Wed, 12 October 2005 15:13 |
cybotto
Messages: 12
Registered: October 2005
|
Junior Member |
|
|
That was not my idea to gather statistics every night, I can't change it before getting some more evidence than gathering statistics is a litte bit unpredictable. It still uses the ANALYZE command which make it maybe more worse. But that is not the point.
Consider
OPEN ref_cur FOR 'SELECT .... WHERE id = :var AND ...';
or
OPEN ref_cur FOR 'SELECT .... WHERE id = '''||var''' AND ...';
First approach is using bind variable, second not. Now first query runs for ages on production and second query runs fine in 95% of all cases. The data is highly skewed. Histogrammes will not help as well, since there are too many distinct values.
I'm not willing to run every troublesome query in middle of the night or whenever database is getting restarting. The application has tons of queries for any possible combination.
Which dictionary view can I check for that?
|
|
|
|
| Re: bind variable peeking and collecting statistics [message #142442 is a reply to message #141891] |
Fri, 14 October 2005 10:32 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Couple thoughts.
Yes, use dbms_stats instead of the old analyze command. And do not gather them every night unless the data changes that much. At least only gather them on tables for which data changes. It would be a waste of resources to gather stats when nothing has changed. But more than that, gathering stats invalidates plans on associated objects. That at the least will require a hard parse on all queries which slows things down. And as you mention it makes things less predictable.
And this is not just about bind variables, affects all queries.
(as far as the invalidations go, I started a new topic on that).
Histograms, in my view, should always be calculated on all columns with the largest bucket size possible if sufficient time and resources are available to do so. If you are gathering every night (I know, not your idea) then your system obviously has extra time on its hands. Histograms provide information to the CBO, the more information the better. They are not just for skewed data, but also help the CBO determine cardinality. David Aldridge has a small writeup on his oracle sponge site. Also, you don't always know if it is skewed if you don't calculate histograms.
And what I meant about not understanding was that you said using dynamic sql instead of using bind variables. But that is the only time you use binds, because oracle takes care of that for you in static sql. What I mean is that both of your ref cursor statements are dynamic, regardless of binding.
But to your specific problem. You don't shutdown your database every night do you? Should only be done once a month or 3 months or whenever hardware/software upgrades are done. Otherwise don't bring it down if can help it. But as part of your db or app initialization process it can run several dummy or seed queries to get things going. Automate it in a script, don't run it yourself in the middle of night or anything.
You may also want to consider plan stability. Find the execution plan you want and then set it so that the query uses that plan all the time. The docs should cover how. The above idea of seeding the query is essentially faking the same thing.
And it wouldn't be for every combination of query criteria used, it would be just one for each separate query with binds. If you have select * from table where x = :1, and substitute 100 different values for :1, then you'd only have one stable plan or only run the query once on startup and you would supply a value for :1 that got you the plan you wanted the rest of them to use.
But the reason for knowing the different combos you use is that if instead of substituting 100 different values for a given query, you only had 3, then you could not use binds in that particular case, as there would only be a small number of different queries taking up space in the shared pool.
And finally, Tom Kyte talks about binds a lot and about peeking, both on his site and in his books. Should search there.
|
|
|
|
| Re: bind variable peeking and collecting statistics [message #142489 is a reply to message #142442] |
Fri, 14 October 2005 17:15 |
cybotto
Messages: 12
Registered: October 2005
|
Junior Member |
|
|
Thanks for your thought and the proof on the other threat.
With the dynamic, yes the SQL is generated dynamicly but behaves like a static cursor with no real penalty apart trying to pin it in the buffer cache successfully for repeating selects like in functions which execute many times for a request.
For bind variables, there are in that case 3 million different values on one side and 20 thousand on the other side each table has around 3 million rows with two tables joining them having unique indexes, for 95% of all queries it is better to start from the table with three millions rows, it will take less than second, the other 5% will maybe take 10 seconds. But when starting from the other side it will take minutes for 95% of queries.
Tom Kyte is always playing around with both statement (DBMS_STATS and ANALYZE) to my surprise, tells not to use hints but than using some on and off, especially FIRST_ROWS which doesn't make sense when using
INSRT /*+ FIRST_ROWS */ INTO tab_new
SELECT ...
FROM ...
WHERE ...
but which actually works most of the time better than with no hint. It's kind of a dilemma using hints but getting told not to use them, but than is running unpredictable.
For histogrammes that is another issue what is not feasable in our system since there are millions of rows, some have thousands of references some have one or even none all lying very close to each other.
Still looking for a proof for bind variables, it looks there are invalidated, at least when statistics change a lot, hmmm.
|
|
|
|
| Re: bind variable peeking and collecting statistics [message #142958 is a reply to message #141891] |
Tue, 18 October 2005 08:34 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
On hints, I think what Tom suggests, and I agree with, is to feel free to use hints to indicate general directions / goals to the optimizer that it should consider for this particular query. The all rows and first rows are perfect examples. It may be that in general your system should be set to all rows, but on a few queries you want to tell the optimizer hey I'm interested in getting back data quickly.
Cardinality is another such general advice sort of comment, as you are letting CBO know about the size of say a temp table that you load for a project.
These differ though from 'execution plan directive' type hints. Those that tell the cbo hey, do it this way via this index or this join or this fts. The problem is that you are taking away the freedom of the cbo to make the best decisions given the myriad of parameters that can affect query performance. This can really become a problem when:
a) those parameters change, ie new data entered new indexes or loss of indexes or loss of data. Or system hardware speeds or init parameters or any number of things
b) you upgrade versions, either through a simple patch or a full version, and the cbo behaves differently causing each such hardcoded hint to need to be revisited because the hint may make things worse than they were
The 'exact directive do this' sort of hints should be used as a last resort, that is all. But feel free to use the other hints, the 'general advice' sort of hints, more often because like statistics themselves, they are providing more info to the cbo
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 17:07:40 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
