RE: SQL Tuning challenge

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Tue, 26 Apr 2005 11:35:05 -0400
Message-ID: <4001DEAF7DF9BD498B58B45051FBEA65026A09B3@25exch1.vicorpower.vicr.com>

You might want to look into some of the analytics included in the rdbms, like lead & lag. I use them to calculate a daily usage from stored facts & then average that.=20

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stephen Andert Sent: Tuesday, April 26, 2005 11:29 AM
To: Oracle-L
Subject: SQL Tuning challenge

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 =3D3D b.dbname
        and a.name =3D3D b.name
        and a.cdate =3D3D (select max(trunc(c.cdate)) from =

TBLSPACE_GROWTH
c

                       where a.dbname =3D3D c.dbname
                         and a.name =3D3D c.name
                         and c.CDATE >=3D3D trunc(sysdate -90))) "Curr

Free M=3D
B",

   ((select free_sp from TBLSPACE_GROWTH a

        where a.dbname =3D3D b.dbname
        and a.name =3D3D b.name
        and a.cdate =3D3D (select max(trunc(c.cdate)) from =

TBLSPACE_GROWTH
c

                       where a.dbname =3D3D c.dbname
                         and a.name =3D3D c.name
                         and c.CDATE >=3D3D trunc(sysdate

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

   CDATE >=3D3D 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 =3D3D b.dbname
        and a.name =3D3D b.name
        and a.cdate =3D3D (select max(trunc(c.cdate)) from =

TBLSPACE_GROWTH
c

                       where a.dbname =3D3D c.dbname
                         and a.name =3D3D c.name
                         and c.CDATE >=3D3D trunc(sysdate

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

   dbname, "Days", name;
--

http://www.freelists.org/webpage/oracle-l
--

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