Re: How frequently: Analysing - question added

Thank You, Howard, for your explanations, this made things clear to me ! I am going to delete statistics on SYSTEM tabs.

Jan

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> schrieb im Newsbeitrag news:pan.2003.02.20.19.11.31.525960_at_yahoo.com.au...
> On Thu, 20 Feb 2003 16:25:55 +0100, Jan Gelbrich wrote:
>
> > 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
> > ?
>
> In general, it is an extremely bad idea to take code which has been
> written and tuned with the RULE-based optimizer in mind and suddenly throw
> the COST-based optimizer at it. Whilst perhaps tuned perfection on the
> other, all sorts of weird and wonderful execution plans result when the
> optimizer mode suddenly changes like that.
>
> One thing that can change the optimizer mode is the calculation of
> statistics on tables: if there are no statistics, then the cost-based
> optimizer has no basis on which to work out costs, and basically falls
> back on the rule-based approach. Conversely, the sudden arrival of
> statistcs means that that which used to be rule-based optimized now
> becomes cost-based.
>
> Guess what? The recursive SQL that Oracle uses to handle the data
> dictionary has been around (some of it) for nigh-on 20+ years, and it
> definitely was written with the rule-based optimizer in mind, because the
> cost-based optimizer was but a glint in Larry's eye at the time!
>
> Now do you see why statistics on SYSTEM tables are a disaster (and to
> clarify: I presume you mean 'tables in the SYSTEM tablespace -that is, the
> data dictionary tables, actually owned by SYS)? Their
> existence will suddenly mean all data dictionary recursive SQL will begin
> using cost-based optimisation, for which that code was never intended.
> Performance goes through the floor as a result.
>
> > 2. Can SYSTEM be analyzed *accidently* (if so, how) ?
>
> I don't know what you have in mind by saying 'accidentally'. If you mean
> can the analysis of, say, Scott's schema suddenly result in the collection
> of statistics for SYS (and hence the data dictionary), then the answer is
> definitely no.
>
> If you mean 'can a well-meaning but misguided junior DBA calculate
> statistics on these tables at all', the answer is definitely yes. And it's
> time to delete them when they're discovered.
>
> Regards
> HJR
>
> > 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 Fri Feb 21 2003 - 02:14:10 CST