SQL Tuning challenge
- From: Stephen Andert <andert@xxxxxxxxx>
- To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Apr 2005 08:29:23 -0700
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;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
