Home » RDBMS Server » Performance Tuning » Frequent changed table stats problem (Oracle 10.2.0.4, AIX 5.3 64bit )
icon5.gif  Frequent changed table stats problem [message #562841] Mon, 06 August 2012 05:16 Go to next message
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 #562876 is a reply to message #562841] Mon, 06 August 2012 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 22781
Registered: January 2009
Senior Member
>Is this resonable?
It is reasonable if you obtain the desired results.
Re: Frequent changed table stats problem [message #562984 is a reply to message #562876] Tue, 07 August 2012 10:11 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, BlackSwan
Thanks. I should backup the statistics of this table, in case of any unexpected result.
So is last chooce of this kind of scenario?
Re: Frequent changed table stats problem [message #562985 is a reply to message #562984] Tue, 07 August 2012 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 22781
Registered: January 2009
Senior Member
>So is last chooce of this kind of scenario?
You should test on TEST DB so you know what to expect when you do the same to Production DB.
Re: Frequent changed table stats problem [message #564086 is a reply to message #562985] Tue, 21 August 2012 06:23 Go to previous messageGo to next message
Kevin Meade
Messages: 1938
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:
  1. this is an advanced tuning method. Most people do not understand statistics in Oracle well and thus doing this is difficult for them.
  2. 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?
  3. 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.
  4. 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 #564160 is a reply to message #562841] Tue, 21 August 2012 13:50 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
Hi Snowball

is your table partitioned? What kind of application is it? Does it use static or generated dynamic sql's?
Could you please upload one example of your problematic sql's with its EXECUTION plan (the best with execution plan stats).

Regards
Leonid
Re: Frequent changed table stats problem [message #564166 is a reply to message #562841] Tue, 21 August 2012 14:01 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
Are you sure, the stale optimizer statistics cause this issue? Did you verify this in v$sql_shared_cursor?
Re: Frequent changed table stats problem [message #564169 is a reply to message #564166] Tue, 21 August 2012 14:55 Go to previous messageGo to next message
Kevin Meade
Messages: 1938
Registered: December 1999
Location: Connecticut USA
Senior Member
no one is sure that stale stats are the root cause of the issue. I have only suggested that even though we might complain about Michel's post on a few counts, the reality is that his post points to the initial cause of most performance issues related to SQL which is that the BASICS of performance for a database have not been properly addressed. In short the idea that the optimizer will FTS if it thinks it is fetching a "large" percentage of a table. Since we are doing FTS, one might suspect that the optimizer has this thought. Since the real data shows that the query is not fetching a "large" percentage of the table, we have to ask why does Oracle think it is?

That is all. The OP should be checking his stats at this point.

Kevin
Re: Frequent changed table stats problem [message #564170 is a reply to message #564169] Tue, 21 August 2012 15:42 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
Optimizer Statistics are only one possible reason of execution plan changing. The view v$sql_shared_cursor could help to find a real reason. I forgot to ask Snowball, how often does the sql run. It can be also important for the analysis.

Kevin, I addressed my last remark to Snowball, not to you. So it was not a doubt about your suggestion Smile.

Kind regards
Leonid
Re: Frequent changed table stats problem [message #564171 is a reply to message #564170] Tue, 21 August 2012 15:44 Go to previous messageGo to next message
Kevin Meade
Messages: 1938
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 Go to previous message
LNossov
Messages: 288
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
Previous Topic: SQL Statement taking too long (merged 3)
Next Topic: ORA-00439: feature not enabled: Bit-mapped indexes
Goto Forum:
  


Current Time: Tue Sep 16 18:33:07 CDT 2014

Total time taken to generate the page: 0.12616 seconds