Re: How frequently: Analysing - question added

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

>> > 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.