jump to navigation

Monitoring tablespaces with dynamic thresholds 13 December 2010

Posted by David Alejo Marcos in SQL - PL/SQL.
Tags: ,
1 comment so far

One of the problems we face while monitoring tablespaces is the difference in size. Some of our tablespaces are 10-50 GB while other are close to TB.

10% free space on a 10GB tablespace is not much on a fast growing system, while 10% on a 1TB tablespace is enough to keep us going for 1 month.

The problem:

We need a query that performs really quick, it is not heavy on the system and is clever enough to alert us if 20% is critical, warning or can wait.

The solution:

I came up with the following query:

SELECT (CASE
 WHEN free_space_mb <=  DECODE (allocation_type, 'UNIFORM', min_extlen, maxextent) * free_extents THEN 'CRITICAL'
 WHEN free_space_mb <=  DECODE (allocation_type, 'UNIFORM', min_extlen, maxextent) * free_extents + 20 THEN 'WARNING'
 ELSE 'N/A'
 END)
 alert,
 tablespace_name,
 space_used_mb,
 extend_bytes/1024/1024 extend_mb,
 free_space/1024/1024 free_mb,
 pct_free,
 free_extents,
 free_space_mb,
 max_size_mb,
 maxextent
 FROM (SELECT c.tablespace_name,
 NVL (ROUND ( (a.extend_bytes + b.free_space) / (bytes + a.extend_bytes) * 100,2), 0)  pct_free,
 NVL ( ROUND ( (a.extend_bytes + b.free_space) / 1024 / 1024, 2), 0) free_space_mb,
 (CASE
 WHEN NVL ( ROUND ( (maxbytes) / power(1024,3), 2), 0) <=  30 THEN 60
 WHEN NVL ( ROUND ( (maxbytes) / power(1024,3), 2), 0) <= 100 THEN 120
 WHEN NVL ( ROUND ( (maxbytes) / power(1024,3), 2), 0) <= 300 THEN 200
 WHEN NVL ( ROUND ( (maxbytes) / power(1024,3), 2), 0) <= 800 THEN 300
 ELSE 340
 END) free_extents,
 a.extend_bytes, b.free_space,
 ROUND (maxbytes / 1024 / 1024, 2) max_size_mb,
 nvl (round(a.bytes - b.free_space ,2) /1024/1024, 0) space_used_mb,
 c.allocation_type,
 GREATEST (c.min_extlen / 1024 / 1024, 64) min_extlen,
--               (SELECT  NVL(MAX (bytes) / 1024 / 1024, 64) maxextent
--                   FROM dba_extents
--                  WHERE tablespace_name = a.tablespace_name) maxextent
 64 maxextent
 FROM (  SELECT tablespace_name,
 SUM(DECODE (SIGN (maxbytes - BYTES), -1, 0, maxbytes - BYTES)) AS extend_bytes,
 SUM (BYTES) AS BYTES,
 SUM (maxbytes) maxbytes
 FROM DBA_DATA_FILES
 GROUP BY tablespace_name) A,
 (  SELECT tablespace_name,
 SUM (BYTES) AS free_space,
 MAX (BYTES) largest
 FROM DBA_FREE_SPACE
 GROUP BY tablespace_name) b,
 dba_tablespaces c
 WHERE     c.contents not in ('UNDO','TEMPORARY')
 AND b.tablespace_name(+) = c.tablespace_name
 AND a.tablespace_name = c.tablespace_name
 AND NVL (ROUND ( (a.extend_bytes + b.free_space) / (bytes + a.extend_bytes) * 100,2), 0) < 20
) A
WHERE free_space_mb <= DECODE (allocation_type, 'UNIFORM', min_extlen, maxextent) * free_extents + 20;

and this is how it works:

1.- Line 43. We do not raise alerts for Undo or Temp tablespaces.
2.- Line 44. Outer join between dba_tablespaces and dba_free_space is necessary or you will not receive alerts once you do not have any free space.
3.- Line 46. Only evaluate tablespaces with less than 20% free space.
4.- Line 30. I started monitoring the largest extent for the tablespace being monitored (query below), but we hit some bugs with dba_extents not being able to have statistics, generating a bad plan and running for up to 2 minutes. If you are using tablespaces with Extent Allocation = Automatic, the biggest extent will be 64 MB, so I decided to use a hard-coded value instead.

(SELECT  NVL(MAX (bytes) / 1024 / 1024, 64) maxextent
    FROM dba_extents
  WHERE tablespace_name = a.tablespace_name) maxextent

5.- Lines 18 to 24. This is where the logic happens. Depending of the size of the tablespace (including max autoextend), we define thresholds under free_extents. This values might need to be adjusted to your systems.
6.- Lines 1 to 6. Depending of the results on point 5, we define alerts as N/A (Not Applicable, Warning or Critical). Worth mentioning the distinction between Extent Allocation Uniform or Automatic. I assume a max extent of 64 MB for both to have consistency.

Please, share your thoughts about this query, there is always more than one way to achieve the same goal using SQL.

As always, comments are welcome.

Advertisements