Monitoring tablespaces with dynamic thresholds 13 December 2010Posted by David Alejo Marcos in SQL - PL/SQL.
Tags: SQL, SQL - PL/SQL
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.
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.
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.