RE: SQL Tuning challenge
- From: "Goulet, Dick" <DGoulet@xxxxxxxx>
- To: <andert@xxxxxxxxx>, "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Apr 2005 11:35:05 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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;
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
