Re: Statspack analysis

One more thing - could you post the full text of the SQL?

You can do so by querying -

select sql_text from v$sqltext where hash_value =3097108327 order by piece ;

(or, alternately find out the hash_value after querying V$SQLAREA for the SQL doing most "buffer_gets" and then find the full_text)

This structure looks odd to me -

select
a.tablespace_name,a.bytes_full,b.bytes_total,a.bytes_full/b.bytes_total status, b.bytes_total-a.bytes_full free from
(select tablespace_name,sum(bytes) bytes_full from dba_extents where
tablespace_name='HBMSTAB' group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes_total from dba_data_files

If we know tablespace name - the scalar sub-queries can just be -

select sum(bytes) bytes_full from dba_extents where tablespace_name='HBMSTAB'

rather than

select tablespace_name,sum(bytes) bytes_full from dba_extents where tablespace_name='HBMSTAB' group by tablespace_name Received on Fri Mar 31 2006 - 13:35:56 CST