Re: Select count(*) from dba_free_space > 250000

Hi,

I had the same problem.
The solution, for me, was to recreate the view DBA_FREE_SPACE without accessing LMT, since I didn't use this feature.

Regards

barney_solomon_at_hotmail.com (Barney Solomon) wrote in message news:<87b30294.0207120950.556a447_at_posting.google.com>...
> 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 Sun Jul 14 2002 - 04:15:47 CDT