OraFAQ Forum: Performance Tuning » Frequent changed table stats problem

Home » RDBMS Server » Performance Tuning » Frequent changed table stats problem (Oracle 10.2.0.4, AIX 5.3 64bit )

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Frequent changed table stats problem [message #562841]

Mon, 06 August 2012 05:16

snowball
Messages: 213
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

BlackSwan
Messages: 20247
Registered: January 2009

Senior Member

>Is this resonable?
It is reasonable if you obtain the desired results.

Report message to a moderator

Re: Frequent changed table stats problem [message #562984 is a reply to message #562876]

Tue, 07 August 2012 10:11

snowball
Messages: 213
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?

Report message to a moderator

Re: Frequent changed table stats problem [message #562985 is a reply to message #562984]

Tue, 07 August 2012 10:17

BlackSwan
Messages: 20247
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.

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: 1787
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 #564160 is a reply to message #562841]

Tue, 21 August 2012 13:50

LNossov
Messages: 282
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

Report message to a moderator

Re: Frequent changed table stats problem [message #564166 is a reply to message #562841]

Tue, 21 August 2012 14:01

LNossov
Messages: 282
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?

Report message to a moderator

Re: Frequent changed table stats problem [message #564169 is a reply to message #564166]

Tue, 21 August 2012 14:55

Kevin Meade
Messages: 1787
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

Report message to a moderator

Re: Frequent changed table stats problem [message #564170 is a reply to message #564169]

Tue, 21 August 2012 15:42

LNossov
Messages: 282
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

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: 1787
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: 282
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

Report message to a moderator

Re: Frequent changed table stats problem [message #564286 is a reply to message #564199]

Wed, 22 August 2012 08:44

msol25
Messages: 214
Registered: June 2011
Location: Mumbai

Senior Member

Dear Snowball,

Please apply exchange partition concept on your update records
and create partition for every day insert records using exchange
partition concept and then create local index for that partition
and then calculate the statistics for that partition only.

Thanks & Regards
Saurabh

[Updated on: Wed, 22 August 2012 09:10]

Report message to a moderator

Previous Topic:	ORA-00439: feature not enabled: Bit-mapped indexes
Next Topic:	restart database after increase the db_cache_size?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 20 02:22:02 CDT 2013

Total time taken to generate the page: 0.23841 seconds