"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:<b32s4c$1hu9ep$1_at_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.
Basically, only the user created schemas should be analyzed.
Analyzing sys and / or system can cause serious problems.
Received on Thu Feb 20 2003 - 14:34:50 CST
