SQL tuning

[phraim]

Hello,

The following sql sometimes takes several hours to finish. My goal is to tune these scripts to shorten the time of execution. Any suggestions will be appreciated.

set linesize 130
set pagesize 20000
set termout off
column tablespace_name format a18
column t format 999999999 heading 'Extents|Left'
column owner format a8 heading 'Owner'
column next_extent format 9999999999 heading 'Next|Extent'
column segment_type format a15 heading 'Type'
column segment_name format a30 heading 'Segment Name'
column partition_name format a10 heading 'Partition'

spool extents.lst

ttitle 'Segments That CanNOT Extend Again'
select owner owner, segment_name, segment_type, next_extent, tablespace_name
from dba_segments ds
where next_extent > (select max(bytes) from dba_free_space df
where df.tablespace_name = ds.tablespace_name)
/
ttitle 'Tables That Can Only Extend 3 or Less|Times Before Running Out of Space'
select owner, table_name, df.tablespace_name, next_extent
from dba_free_space df, dba_tables dt
where dt.tablespace_name = df.tablespace_name
and bytes >= next_extent
and dt.owner <> 'SYSTEM'
group by owner, table_name, df.tablespace_name, next_extent
having sum(floor(bytes/next_extent)) < 4
order by 1
/
ttitle 'Indexes That Can Only Extend 3 or Less Times| Before Running Out of Space'
select owner, index_name, df.tablespace_name, next_extent
from dba_free_space df, dba_indexes di
where di.tablespace_name = df.tablespace_name
and bytes >= next_extent
and di.owner <> 'SYSTEM'
group by owner, index_name, df.tablespace_name, next_extent
having sum(floor(bytes/next_extent)) < 4
order by 1
/
ttitle 'Segments That Have More Than 100 Extents'
select owner,segment_type, segment_name, tablespace_name, partition_name,
extents from DBA_Segments where extents > 100
/
spool off
set termout on
exit

Note - These scripts work on any oracle database

Thanks
phraim

[slimdave]

Which SQL takes longest?

You might consider changing the correlated subquery on the first SQL into an inline view, to which you just join.

You might consider combining the second and third queries into a single query by using dba_segments instead of dba_tables and dba_indexes

You might like to eliminate the fourth query, or at least push the 100 up to 1000.

You definatly should consider locally managed tablespaces with uniform extent size, or even if you are using DMT's still you should have initial_extent = next_extent, pctfree = 0, and uniform extent sizes in any one tablespace.

[phraim]

Thanks slimdave.