Reducing The Cost of the Tablespace Used Request (Main screen)

[ccorgnet]

Hie Sergei

Everything is in the subject. We have a very huge database 11g where
we suffer tremendous "control file sequential read" on the main screen
request i suppose

Is there a setting which could prevents us from running it "too"
often ?

Very huge means : 15 TO, 3500 datafiles and I'm afraid a very
fragmented dictionary

Thanks by advance



Here's the query ::
select decode(t.tablespace_name,'LOCAL','*',' ')||
t.tablespace_name
||decode(t.contents,'TEMPORARY',' (TEMPORARY/'||
b.file_type||')','') ts,

t.status
tst,
b.bytes/
1024 vt,
b.bytes_used/
1024 vo,
b.bytes_free/
1024 vr,
ceil(b.bytes_used*100/
b.bytes) tx
from (
select df.tablespace_name tablespace_name,
df.bytes bytes,
nvl(u.bytes_used,0) bytes_used,
nvl(f.bytes_free,0) bytes_free,
'DATAFILE' file_type
from (
select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name
) df,
(
select tablespace_name,
sum(bytes) bytes_used
from dba_segments
group by tablespace_name
) u,
(
select tablespace_name,
sum(bytes) bytes_free
from dba_free_space
group by tablespace_name
) f
where df.tablespace_name = u.tablespace_name (+)
and df.tablespace_name = f.tablespace_name (+)
union
select tf.tablespace_name,
sum(tf.bytes) bytes,
sum(ep.bytes_used) bytes_used,
sum(tf.bytes)-sum(ep.bytes_used) bytes_free,
'TEMPFILE' file_type
from dba_temp_files tf,
v$temp_extent_pool ep
where
ep.file_id (+) = tf.file_id
group by tf.tablespace_name
) b,
dba_tablespaces t
where t.tablespace_name = b.tablespace_name
order by tx desc, vo desc