SQL redesigning

Applications Programming
1

The following request give the expected result (respecting a certain output formatting)
But i was wondering if the same result could be produced in a more "optimized way" (Oracle 9i by the way)

set feedback off
set pagesize 0
set trimspool on
ttitle off
btitle off
set verify off
set linesize 260
column name heading "TABLESPACE" format A25
column size_mb heading "SIZE (M)" format 99999990.9
column "USED (%)" format 990
select ts name,
    round(size_mb,2) size_mb,
        round(decode(total_size_mb,0,'',(total_size_mb-total_free_mb)*100/total_size_mb),0) "USED (%)",
        autoextensible "AUT"
from
(select a.tablespace_name ts,
        nvl(c.mb,0)+nvl(e.mb,0) size_mb,
              decode(lower(a.contents),'temporary',
                decode(sign(nvl(e.tot_mb,0)-nvl(e.mb,0)),1,'YES','NO'),
                decode(sign(nvl(c.tot_mb,0)-nvl(c.mb,0)),1,'YES','NO'))
                autoextensible,
              decode(lower(a.contents),'temporary',
                decode(sign(nvl(e.tot_mb,0)-nvl(e.mb,0)),1,
                  nvl(e.tot_mb,0),nvl(e.mb,0)),
                decode(sign(nvl(c.tot_mb,0)-nvl(c.mb,0)),1,
                  nvl(c.tot_mb,0),nvl(c.mb,0)))
                total_size_mb,
              decode(lower(a.contents),'temporary',
                decode(sign(nvl(e.tot_mb,0)-nvl(e.mb,0)),1,
                  nvl(e.tot_mb,0)-nvl(f.used_mb,0),
                    nvl(e.mb,0)-nvl(f.used_mb,0)),
                decode(sign(nvl(c.tot_mb,0)-nvl(c.mb,0)),1,
                  nvl(c.tot_mb,0)-nvl(c.mb,0)+nvl(d.free_mb,0),
                    nvl(d.free_mb,0)))
                total_free_mb
       from
         dba_tablespaces a,
         (select tablespace_name ts,
                 sum(extents) ext,
                 count(*) seg
          from dba_segments group by tablespace_name) b,
         (select tablespace_name ts,
                 sum(nvl(bytes,0))/1024/1024 mb,
                 count(*) files,
                 sum(decode(lower(autoextensible),'no',nvl(bytes,0),nvl(maxbytes,0)))/1024/1024 tot_mb
          from dba_data_files group by tablespace_name) c,
         (select tablespace_name ts,
                 sum(nvl(bytes,0))/1024/1024 free_mb
          from dba_free_space group by tablespace_name) d,
         (select tablespace_name ts,
                 sum(nvl(bytes,0)/1024/1024) mb,
                 count(*) files,
                 sum(decode(lower(autoextensible),'no',nvl(bytes,0),
                   nvl(maxbytes,0)))/1024/1024 tot_mb
          from dba_temp_files group by tablespace_name) e,
         (select tablespace_name ts,
                 sum(nvl(bytes_used,0))/1024/1024 used_mb
          from gv$temp_extent_pool group by tablespace_name) f
        where
          a.tablespace_name=b.ts(+) and
          a.tablespace_name=c.ts(+) and
          a.tablespace_name=d.ts(+) and
          a.tablespace_name=e.ts(+) and
          a.tablespace_name=f.ts(+))
order by 1 desc
/
2

Define "optimized". What's the output like now, what would you like?

3

by "optimized" i meant : with least internal operations as possible so the calculation will be quick.

The output looks like

SYSTEM                                850       50 YES
UNDO                            26450        8 YES
TBSUSR                             20        0 YES
TBSAPP40                          30340       85 YES
TBSAPPC                            283168       98 YES
PERFSTAT                             5500       96 YES
4

Do you know about tkprof and alter session set sql_trace true; ? It provides detailed information about the performance of your query.

We cannot do much without at least an explain plan output. Assuming your query is in fact too slow.