Select count(*) from dba_free_space > 250000

A customer outsourced their Oracle servers to us recently and one of them is a 8.1.6.0.0 (Yeah, I know) SE on AIX on top of which runs a fairly simple ERP system.

Today, we noticed that when the BMC Patrol agent ran to collect tablespace and extent stats everything died performancewise. After having reduced the Patrol query to the absolute minimum the problem was found to be that selecting from dba_free_space resulted in more than 250.000 rows.

After coalescing the tablespaces (which reduced dba_free_space count(*) to 1400) queries on dba_free_space still ran really slow .. so far I suspect that it might be a HWM problem since selecting count(*) from dba_free_space still results in almost 500.000 consistent gets.

I checked the C_TS# cluster where FET$ and TS$ are clustered and it consists of 640 extents totalling almost 6MB. But still .. 500.000 gets .. after all dba_free_space consists "only" of FET$, TS$, FILE$ and x$ktfbfe (which is only for LMT ?)

The DB has about 20 datafiles and 7 tablespaces and none are locally managed.

I just thought I would run this by you all and see if you have any brilliant ideas ;-)

/Barney Received on Fri Jul 12 2002 - 12:50:13 CDT