 Frequent changed table stats problem [message #562841] |
Mon, 06 August 2012 05:16  |
snowball
Messages: 229
Registered: April 2006
Location: China
|
Senior Member |
|
|
Hi, Guys
We have a table around serverial billion records.
In most of the time, this table change(many actions with delete and insert). When executing some sqls we found some of them are not optimal execution plan, because the statistics may be stale. But manually gather the statistics may cost a lot of time and also bring very bad performance.
The where condition in issue sqls mostly contain a date time range to fetch some records.
Some of my fellows, suggest to use dbms_stats.SET_*_STATISTICS procedures (such as SET_COLUMN_STATS, SET_INDEX_STATS, etc) to configure the statistics in order to let those sql have a correct plan.
Is this resonable? Any suggestion?
Thanks very much,
Milo
[Updated on: Mon, 06 August 2012 05:18] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Frequent changed table stats problem [message #564086 is a reply to message #562985] |
Tue, 21 August 2012 06:23  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
It is one of the techniques that advanced tuners use when faced with difficult situations. In that respect it is quite reasonable.
However, you should accept some facts about it:
- this is an advanced tuning method. Most people do not understand statistics in Oracle well and thus doing this is difficult for them.
- this requires some rigour. For example, if you collect stats, these values may be overwritten. So you need a stats collection plan. Do you have one?
- may not help. Though a valuable tool, there are situations where these numbers are ignored in favor of defaults in which case it may not help. Do not get let this stop you from moving forward. Keep thinking till you find a solution that works for you.
- you need to document and train. Since this is an advanced method of tuning, you should be thinking about how to explain it to others, and how to train them to be able to do it themselves. Otherwise, no one will have an opportunity to appreciate your brilliance and carry on your brilliant scheme.
Kevin
[Edit MC: change code tags to list ones for the enumeration]
[Updated on: Tue, 21 August 2012 08:06] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Frequent changed table stats problem [message #564171 is a reply to message #564170] |
Tue, 21 August 2012 15:44 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
That is OK. I lost most of my ego years ago. Besides, I don't own the info, I was just passing along what someone taught me.
Naturally you are correct, statistics is only one reason. But it is as good a place to start as any.
Maybe you could post some examples of you using v$sql_shared_cursor in diagnosing or othewise analyzing some performance facts. If you examples make sense to me, I will steal them and use them to teach others.
Kevin
[Updated on: Tue, 21 August 2012 15:46] Report message to a moderator |
|
|
|
| Re: Frequent changed table stats problem [message #564199 is a reply to message #564171] |
Wed, 22 August 2012 01:14 |
 |
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
Hello Kevin
a changing of the execution plan always happens via creating a new child cursor.
You can find the reasons of creating a new child cursor in the view v$sql_shared_cursor. Some of them can be relevant for the changing of the execution plan.
For ex.
ROLL_INVALID_MISMATCH='Y' means invalidation of the cursor in <_optimizer_invalidation_period> seconds after the generations of the op stats using auto_invalidate mode,
OPTIMIZER_MISMATCH = 'Y' means changing of the optimizer environment (parameters),
STB_OBJECT_MISMATCH = 'Y' means applying of sql profile or of sql plan baselines,
OPTIMIZER_MODE_MISMATCH = 'Y' means changing of the op mode (for ex. all_rows to first_rows_10),
OUTLINE_MISMATCH = 'Y' means applying of the stored outlines,
and so on.
If you find out some of these reasons, the likelihood that they caused at the same time the changing of the execution plan is high.
Even if the reason seems not to be relevant for the changing of the execution plan, it can be helpful for the analysis.
It is clear, you can use this approach only if your old cursor is still in the sql area while creating a new one.
Kind regards
Leonid
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.