Faster selecting tablespace fill level

From: Martin Klier <usn_at_usn-it.de>
Date: Thu, 25 Feb 2010 18:38:08 +0100
Message-ID: <4B86B580.7070702_at_usn-it.de>

Dear list,

is there a way to get the results from the select below, in a more peaceful way?

An execution plan on that piece of SQL opens a glance into performance issues deep and deeper. (My 10g DB calculates cost of over 12k, and 530k buffer gets).

What I basically need:
- realtime fill level (sum of all segments)

• calculation against maximum autoextend value
• free percent and free MB

Thanks for any suggestions,
Martin

select

  TBS.tablespace_name,
  TBS.TBS_MAXBYTES/1024/1024 TBS_MAXMBYTES,
  TBS.TBS_BYTES/1024/1024 TBS_ISMBYTES,
  SEG.DATA_BYTES/1024/1024 TBS_DATA_MBYTES,

  (TBS.TBS_BYTES-SEG.DATA_BYTES)/1024/1024 MBYTES_TO_AUTOX,   (TBS.TBS_MAXBYTES-SEG.DATA_BYTES)/1024/1024 MBYTES_TO_END,   round(((TBS.TBS_MAXBYTES-SEG.DATA_BYTES)/ TBS.TBS_MAXBYTES)*100,2) PERCENT_FREE
from

(select
  ddf.tablespace_name,
  sum(maxbytes) TBS_MAXBYTES,
  sum(bytes) TBS_BYTES
  from dba_data_files ddf
  group by ddf.tablespace_name) TBS,
(select
  ds.tablespace_name,
  sum(ds.bytes) DATA_BYTES
  from dba_segments ds
  group by ds.tablespace_name) SEG
where TBS.tablespace_name=SEG.tablespace_name   and TBS.tablespace_name='IWACS'
order by TBS.tablespace_name

;

-- 
Usn's IT Blog for Linux, Oracle, Asterisk
http://www.usn-it.de

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 25 2010 - 11:38:08 CST