[hobbit] dbcheck.pl - Oracle - tablespace with AutoExtent

[Aiello, Steve (GE, Corporate, consultant)]

I am still introducing Francesco's dbcheck script to my DBAs. I have
multiple DBA groups, and setting monitoring up with thresholds &
settings that they can all agree on is difficult sometimes. But since
they are the ones getting page, I can understand their concern on having
monitoring setup right. I still have a todo to email them the exact SQL
queries being used by the checks, I have had reports from one group that
some of the checks can be Database intensive. Currently I have all my DB
checks running at 5 min intervals, so I am looking forward to
identifying the 'intensive' queries and moving them to a much longer run
interval (great feature). For now I have just stopped those particular
test from running.

As to TableSpace checks, my DBAs take into account 3 parameters to
determine if diskspace is an issue. FreeSpace, PercentFree, and Extend
(I think). Some of our TableSpaces are very small, and when thresholds
are just set to check Percents we get alot of false-positives. So I have
made use of dbcheck's Custome query option to replace the TblSpace
check. Basically the SQL performs all the check logic, and sets a field
flag with a 1 (green), 2 (yellow), or 3 (red) value.  dbchecks does a
check only on this field to set the status of the test. Below I have
provided the CUSTOM TblSpace check. Pardon the very long, one line SQL.
It does not provide for great readability.

[CUSTOM TblSpace]
Dbtype=Oracle
Query=select a.tablespace_name, nvl(b.total_mb,0) tsize, nvl((b.total_mb
- c.free_mb),0) used, nvl(c.free_mb,0) free, nvl(round((((b.total_mb -
c.free_mb)*100)/b.total_mb),2),0) Usedpct, d.autoextensible Autoextent,
CASE WHEN nvl(round((((b.total_mb - c.free_mb)*100)/b.total_mb),2),0)>97
AND c.free_mb < 200 THEN '2' WHEN nvl(round((((b.total_mb -
c.free_mb)*100)/b.total_mb),2),0)>94 AND c.free_mb < 400 THEN '1' ELSE
'0' END Status  from (select tablespace_name, (sum(bytes)/1024)/1024
total_mb from dba_data_files group by tablespace_name) b, (select
tablespace_name, nvl(round(((sum(bytes)/1024)/1024),2),0) free_mb from
dba_free_space group by tablespace_name) c, (select distinct
tablespace_name,autoextensible from dba_data_files where
autoextensible='NO') d, dba_tablespaces a where a.tablespace_name =
b.tablespace_name (+) and a.tablespace_name = c.tablespace_name (+) and
a.tablespace_name = d.tablespace_name and a.contents not in
('TEMPORARY','UNDO') and a.tablespace_name not like '%RBS%' and a.status
<> 'READ ONLY' UNION ALL select a.tablespace_name, nvl(b.total_mb,0)
tsize, nvl((b.total_mb - c.free_mb),0) used, nvl(c.free_mb,0) free,
nvl(round((((b.total_mb - c.free_mb)*100)/b.total_mb),2),0) Usedpct,
d.autoextensible Autoextent, CASE WHEN fr < 200 OR ex < 2 THEN '2' WHEN
fr < 400 OR ex < 5 THEN '1' ELSE '0' END Status  from (select
tablespace_name, (sum(bytes)/1024)/1024 total_mb from dba_data_files
group by tablespace_name) b, (select tablespace_name,
nvl(round(((sum(bytes)/1024)/1024),2),0) free_mb from dba_free_space
group by tablespace_name) c, (select distinct
tablespace_name,autoextensible,(maxbytes - bytes)/1024/1024 fr,(maxbytes
- bytes)*blocks/(increment_by*bytes) ex from dba_data_files where
autoextensible='YES') d, dba_tablespaces a where a.tablespace_name =
b.tablespace_name (+) and a.tablespace_name = c.tablespace_name (+) and
a.tablespace_name = d.tablespace_name and a.c
ontents not in ('TEMPORARY','UNDO') and a.tablespace_name not like
'%RBS%' and a
.status <> 'READ ONLY' order by 1
Valtocheck=7:1:2:=:1
ColumnName=TableSpaceName;Size(MB);Used(MB);Available(MB);Used%;AutoExte
nt
Columnsize=;;;;;;0
Columnalign=l;r;r;r;r;r
displaylegend=no
docheckdefault=yes
showmatchedrule=no

> -----Original Message-----
> From: Eric van de Meerakker [mailto:eric-list-1 at softlution.com] 
> Sent: Thursday, January 04, 2007 8:01 AM
> To: hobbit at hswn.dk
> Subject: Re: [hobbit] dbcheck.pl - Oracle - tablespace with AutoExtent
> 
> 
> 
> Hi Henrik,
> 
> My knowledge of Oracle is not too much higher, but IMO you 
> DBA is right. If the autoextend property is set on a 
> tablespace, these cases should not cause a red status. I 
> think the best way would be to check the Used size as a 
> percentage of the "Autoextent(Size)" size, not the current 
> "Size" size. I've experienced the same issue with BB's Oracle 
> montoring scripts.
> 
> As a secondary monitoring point, disk monitoring should be 
> enabled on the disks containing the tablespaces. They may 
> well fill up before the max size is reached, preventing 
> Oracle from extending the tablespaces to the maximum size 
> allowed. Of course, if the Oracle data files do not reside on 
> a filesystem, this cannot be done.
> 
> 
> Cheers,
> 
> Eric.
> 
> 
> Henrik Stoerner wrote:
> > I've started using Francesco's dbcheck script - very nice package, 
> > really. However, one of my DBA's is a bit unhappy with the 
> tablespace 
> > check - it reports yellow/red status for tables that have 
> been defined 
> > with AutoExtent enabled.
> > 
> > Right now I get a report like this:
> > 
> > Tablespace check (def. warning  Use% >= 90%, def. alert  
> Use% >= 95%)
> > TableSpace/DBSpace           Size    Used    Free  Use% 
> Autoextent(Size)
> > BASIC_DATA2_1976_88        300.0M  215.0M   85.0M   72% YES 
> (64.0G)    
> > BASIC_DATA2_1989Q1         400.0M  382.0M   18.0M   96% YES 
> (64.0G)    
> > BASIC_DATA2_1989Q2         500.0M  483.0M   17.0M   97% YES 
> (64.0G)    
> > 
> > with the last two tables causing a red status. My DBA thinks they 
> > should not trigger an alert, because they will 
> automatically increase 
> > in size if needed.
> > 
> > Can anyone shed some light on this? My knowledge of Oracle 
> is close to 
> > zero, so I really don't have any idea whether he is right or not.
> > 
> > 
> > Regards,
> > Henrik
> > 
> > 
> > To unsubscribe from the hobbit list, send an e-mail to 
> > hobbit-unsubscribe at hswn.dk
> > 
> > 
> > 
> 
> To unsubscribe from the hobbit list, send an e-mail to 
> hobbit-unsubscribe at hswn.dk
> 
> 
>