Re: space queries slow

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 11 Sep 2002 22:27:28 +0100
Message-ID: <3D7FB540.478@yahoo.com>

Robert Walkup wrote:
>
> We have a database that has a 1GB system tablespace. We have in-house
> written monitoring software than monitors the free space. We started out
> using dba_free_space view and eventually started using the tables
> directly because the queries were so slow. The queries are still slow
> and resource intensive. We have spent quit a bit of time tuning the
> queries. Since we can't rebuild the database we need to find a work
> around. Thus, I was wondering if there was query or check I could
> perform to see if any changes had been made to any of the
> tablespaces/datafiles and then if changes have been made run the
> standard space/extent queries?
>
> the queries we normally run are...
>
> 1. check for adequate free space
>
> 2. check to make sure segments can extend the next time a space request
> is made.
>
> 3. check to insure segments are not approaching max_extents.
>
> I could put the queries here, but there pretty much the basic queries
> you would get out of a monitoring/tunning. book. If possible, I would
> like to run the previous mentioned check then only run the 3 queries in
> event a change has been made to the database?
>
> database version is 8.1.7
>
> Thanks
> Robert

How about

select a.tablespace_name, a.largest_extent, b.largest_next from (select tablespace_name, max(bytes) largest_extent

      from dba_free_space
      group by tablespace_name) a,
     (select tablespace_name, max(next_extent) largest_next
      from dba_segments
      group by tablespace_name ) b

where a.tablespace_name(+) = b.tablespace_name /

If the largest next is bigger than largest extent then you have an issue.

I wouldn't worry about maxextents as a limiting factor - set them massively high, and occasionally run a query not to see if a segment is getting close to the limit, but to see if a segments has got higher then some threshold (simply because this may indicate unusual growth).

If the above query is slow, then swap out dba_segments with the top part of SYS_DBA_SEGS (ie remove the rollback segs and temp seg stuff).

But lets face it, if you need to run this more than once per day, then its a management problem you've got...

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."

Received on Wed Sep 11 2002 - 16:27:28 CDT