Re: Analysing SYS tables

>
> Well, after analyzing a few of the sys tables namely,
 SEG$,UET$,FET$ and a
> few others, the performance of the queries became catastrophic!. A
 report
> that normally took +- 20 mins (extract next extent sizes) now took
 a full 5
> hours!. It did not affect performance on the applications except
 where the
> application needed to drop tables. A table drop now took 8 mins as
 opposed
> to seconds.
>
>

The SEG$, UET$, and FET$ tables are actually the ones you should not analyze as this introduces the risk of internal deadlock (ANALYZE can

require disk space, which results in inserts and updates on seg$, uet$
and fet$).

There should be a couple of indexes (cluster indexes) on these tables,
but the CBI us quite likely to think that they are very poor indexes in
very many cases.

An interesting problem can arise on FET$ particularly (even without analyzing the index) If you have created and dropped a very large number of
objects in a single tablespace then the single TS$ entry for that tablespace
will be associated with a large number of FET$ rows. Subsequent calls for
free space in that tablespace can be VERY slow.

In a recent Oracle 8 experiment, I created a partition table with 4 indexes
and 400 partitions in a single tablespace (too lazy to created multiple tablespaces).
When I dropped the table I acquired 20,000 free extents of the same size in the
one tablespace. It took 4 minutes to create a new (non-partitioned) table of one
extent, and over an hour to drop the tablespace. (I stopped a coalesce after 25
minutes). Received on Mon Dec 08 1997 - 00:00:00 CST