FreeLists / oracle-l / RE: want to stop analyze once the database is stable

["John Kanagaraj" <john.kanagaraj@xxxxxxx>]

Zhu (and all!)

Just recovering from a Monthend close on a huge Oracle Apps database,
and somehow it is related to this discussion :)

The background is this: Stats are collected on this 8i (still!) database
once a month, mid-month. Since this is an Apps Database, stats are
updated via a special "FND_STATS" call that wraps code around DBMS_STATS
and includes code to generate histograms for a specific set of columns
using the "FOR COLUMNS" clause. OraApps (aka E-Business Suite) requires
a fixed set of CBO related init.ora parameters that does not allow any
'tweaking' :(

The issue is this: There are a number of tables, especially in the
Inventory/Material/Cost accounting schemas that grows by leaps and
bounds. As the month progresses, the 'open' items increases and thus
queries/updates that deal with such rows take longer and longer and get
worse as time progresses on account of table/row count increases.
Because of the size of the tables, number of indexes/columns (one large
15 GB table contains 157 columns and 25 indexes!) and impact of
collecting stats on a 24x7 (cursor invalidations resulting in Forms
freezing up!) we analyze once a month as close to the monthend as
possible to capture just the right status. Performance varies widely
sometimes as we don't know how the data changes, and it _does_ change
when the periods are closed. There are just too many tables, too many
indexes, too many interfaces, too many users and too many reports to
track and fix... :(  

I cannot afford to gather stats and I cannot afford NOT to gather stats
:(

Sorry for ranting!

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
 
Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com
 
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Wolfgang Breitling
Sent: Sunday, April 02, 2006 10:07 AM
To: zhuchao@xxxxxxxxx
Cc: oracle-l
Subject: Re: want to stop analyze once the database is stable

Zhu,

I HAVE to reply to this ( and you know it and maybe even counted on it
:-) ).
The way I see it, you have two choices:

a) Do the daily analyze and then deal with the problems it causes
b) NOT do the daily analyze and then deal with the problems IT causes

I guess everyone can see where I'm heading with this. I see nothing
wrong with stopping the daily analyze, particularly given that it causes
problems.

Of course, Christian's point of finding out why, and which, change in
statistics causes the plan to go bad is valid. But it can just as easily
be turned around. You have got a stable system that doesn't change very
much structurally. Sure, there are transaction tables tables that keep
growing but they have already reached a size where the optimizer has
settled on a plan that is likely correct even if the table doubles or
triples in size.
There WILL be tables, columns, partitions (if you have them) or indexes
for which you need to keep refreshing the statistics. Keep an eye on
columns with monotonically growing (or decreasing ) values which are
also used in equality or range predicates, especially if these (the
predicates) include most recent values which could be beyond what stale
statistics say. But the vast majority of objects do not need a constant
refresh of statistics.

At 08:56 PM 4/1/2006, zhu chao wrote:
>hi, all,
>     We have a 3rd party application running on our database, and the 
>vendor asked us to run daily analyze , for those tables with 
>dba_segments/dba_tables shows 5%+ difference for blocks.
>    And we consistenly run into problem with the SQL plan changed,  as 
>sometimes analyze caused the plan goes bad.
>
>     I want to stop the anlyze, as our current plan is ok, which can 
>meet our SLA requirement. And our database is already 5TB+, I think if 
>we stop analyze, and the CBO statistics does not get updated, the plan 
>should be ok and stable.
>
>    How about your opinion? We are still running oracle 8174 on
solaris8.
>
>--
>Regards
>Zhu Chao
>www.cnoug.org
>--
>http://www.freelists.org/webpage/oracle-l

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com 

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l