Re: How frequently: Analysing - question added

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Thu, 20 Feb 2003 16:25:55 +0100
Message-ID: <b32s4c$1hu9ep$1@ID-152732.news.dfncis.de>

Hi, I would like to know not only how often, but which schemas should be analyzed ...

.. it is because I heard a couple of times that analyzing SYSTEM
can bring performance down,
so that You should drop those statistics.

My question is:

1. *Why* can analyze of SYSTEM be a reason at all to bring performance down ?
2. Can SYSTEM be analyzed *accidently* (if so, how) ? I usually only analyze particular user schemas ... (I was surprised to see that in my system some SYSTEM tabs had been analyzed not too long ago)

--My system: Oracle 8.1.7. EE, AIX 4.3.3, still only DMTs :( , so that DD (uet$, fet$) is frequently updated ...

tia, Jan

"Brent" <bpathakis_at_yahoo.com> schrieb im Newsbeitrag news:1736c3ae.0302191718.5d9f092_at_posting.google.com...
> 76434.1353_at_compuserve.com wrote in message
news:<3e53c8e3.21360464_at_news.hccnet.nl>...
> > How often should I analyse the user tables in the system?
> > Currently a job is doing this every morning which causes some log
> > switching (5 x 10MB).
> >
> > The tables are slowly growing everyday but the structure or the number
> > of tables and indexes is not changing.
> > How frequently analysing is advised?
> > Thanks

>

> Generally speaking, you want to re-analyze a table
> when about 10% of the table has changed due to
> updates/inserts/deletes.

>

> What I do is set the tables to monitoring, then have have a job:

>


>          dbms_stats.gather_schema_stats(my_schema, cascade => true,

>                                         options => 'GATHER STALE');

>

> And have this run once a day. It only analyzes a table if it's
> marked stale (10% has changed). That way, my large tables get
> analyzed relatively infrequently (every 2-3 months), and my smaller
> ones more frequently.

>

> Remeber though, that it's not necessarily the structure of your
> tables but whether the number rows, data distrubition, etc... has
> changed.
Received on Thu Feb 20 2003 - 09:25:55 CST