SQL Tuning challenge

From: Stephen Andert <andert_at_gmail.com>
Date: Tue, 26 Apr 2005 08:29:23 -0700
Message-ID: <6d45e2105042608297cdbc7d@mail.gmail.com>

Warning!!! This is some ugly SQL. It works, but I know there has to be a more elegant (and efficient) method.

Free space, used space and total space is tracked for all databases by tablespace. Based on the growth (delta of space used) over the past 90 days, a figure called MB per day (average growth per day) is calculated. Dividing current free space by avg growth per day gives an estimated "Days till tablespace is empty". There are some "gotchas" with this logic such as when a one-time load horribly skews the MB/day figure requiring a huge addition of space that will never be used, resulting in wasted space. Management is more ok with wasting space that with running out of space.

I am sure there is a cleaner solution than what this is doing. Anyone care to give me a push in the right direction?

Thanks
Stephen

select

    DBNAME,
    name,

    max(mbytes) ,
    max(free_sp),
    min(free_sp),
   (max(free_sp)-min(free_sp))/90 "MB/day",
   (select free_sp from TBLSPACE_GROWTH a
        where a.dbname =3D b.dbname
        and a.name =3D b.name
        and a.cdate =3D (select max(trunc(c.cdate)) from TBLSPACE_GROWTH c
                       where a.dbname =3D c.dbname
                         and a.name =3D c.name
                         and c.CDATE >=3D trunc(sysdate -90))) "Curr Free M=

B",

   ((select free_sp from TBLSPACE_GROWTH a

        where a.dbname =3D b.dbname
        and a.name =3D b.name
        and a.cdate =3D (select max(trunc(c.cdate)) from TBLSPACE_GROWTH c
                       where a.dbname =3D c.dbname
                         and a.name =3D c.name
                         and c.CDATE >=3D trunc(sysdate

-90)))/((max(free_sp)-min(free_sp))/90)) "Days"
from TBLSPACE_GROWTH b
where

   CDATE >=3D trunc(sysdate -90)
group by

   DBNAME, name
having (max(free_sp)-min(free_sp)) > 0

   and ((select free_sp from TBLSPACE_GROWTH a

        where a.dbname =3D b.dbname
        and a.name =3D b.name
        and a.cdate =3D (select max(trunc(c.cdate)) from TBLSPACE_GROWTH c
                       where a.dbname =3D c.dbname
                         and a.name =3D c.name
                         and c.CDATE >=3D trunc(sysdate

-90)))/((max(free_sp)-min(free_sp))/90)) < 100
order by

   dbname, "Days", name;
--

http://www.freelists.org/webpage/oracle-l Received on Tue Apr 26 2005 - 11:33:49 CDT