OEM Tablespace Space Used % Alerts and the AUTOEXTEND effect

I performed a simple test to try and help clarify a rather confusing topic about when OEM will send out alerts for tablespace usage and what happens when autoextend is turned on and what about the MAXSIZE and UNLIMITED parameters of the AUTOEXTEND ON clause.

A number of Metalink support notes discuss this topic like:

Database Tablespace Full Metric Alerts not Generated In Grid Control [ID 357049.1]
Troubleshooting a Database Tablespace Used(%) Alert problem [ID 403264.1]
Oracle Recommended Patches For Tablespace Monitoring using the Tablespace Space Used % metric [ID 849498.1]

What happens if autoextend is turned on but in some cases we have specified an upper limit for the MAXSIZE parameter or have just left it to UNLIMITED? How will OEM handle those cases?

To clarify things, OEM alert notification mechanism will query the DBA_TABLESPACE_USAGE_METRICS view to determine the Used % in a tablespace in relation to the Warning and Critical thresholds which have been set. This is true for Oracle 10g and above and if we are using Locally Managed tablespaces.

It does not look at DBA_FREE_SPACE or DBA_TABLESPACES for that matter to determine how much of free space is there in a tablespace. In fact many of us had these custom scripts which query these two views and in case of databases which had many tablespaces like an Oracle Applications environment, this query could take a long time to return the results and was quite a ‘costly’ query.

It is important to note that this metric takes into consideration the available disk space into which the tablespace’s datafile can extend if autoextend is turned on.

With a 8k block size, on most Unix systems the maximum a datafile can extend upto with autoextend turned on is 32 Gb. In that case the DBA_TABLESPACE_USAGE_METRICS view will use 32 Gb as the basis for calculating the available free space in the tablespace because if a tablespace consists of one datafile, that is the maximum size the tablespace can grow to.

In the figure below of the OEM Tablespaces screen (Database Instance Home Page –> Administration –> Tablespaces), we see some tablespaces which have used space greater 95%, but we have not received any alert from OEM even though the Critical threshold has been crossed.

Why is that the case?

This is because of the AUTOEXTEND setting. In the figure above, some tablespaces have datafiles which have autoextend turned on and some do not have autoextend enabled.

In this case, the datafile of the SYSTEM tablespace does have autoextend turned on, and even though the SYSTEM tablespace is 99% full, OEM does not consider it a candidate for an alert notification because the DBA_TABLESPACE_USAGE_METRICS view shows it only as 15.71% used.

SQL>  SELECT TABLESPACE_NAME TBSP_NAME

  2  , USED_SPACE

  3  , TABLESPACE_SIZE TBSP_SIZE

  4  , USED_PERCENT

  5  FROM SYS.DBA_TABLESPACE_USAGE_METRICS

  6  order by 4;

TBSP_NAME                      USED_SPACE  TBSP_SIZE USED_PERCENT

—————————— ———- ———- ————

…

…
SYSTEM                              91512     582427   15.7121837
So how did Oracle compute this 15.71% used figure? (91512/582427*100)
Note the USED_SPACE and TBSP_SIZE columns are in blocks – in this case the DB_BLOCK_SIZE of the database is 8k or 8192 bytes.
The maximum size which the file can extend upto via autoextend is 582427 blocks or 4450 Mb
582427 (blocks) *8192 (each block size)  / 1048576 (to convert to MB) = 4450 MB 
The SYSTEM tablespace is made up of one datafile and that datafile is about 714 MB used.  (91512* 8192/1048576)
Let us create a tablespace TESTME of size 50 Mb.
SQL>  create tablespace testme
  2  datafile '/u01/oradata/HDESK11G/testme01.dbf' size 50m;

We now create a table and use the TESTME tablespace to store the table data
SQL> create table sh.test
  2   tablespace testme
  3   as select * from sh.sales;

Table created.

Since we have not specified any AUTOEXTEND for the datafile of the TESTME tablespace, the TBSP_SIZE column for this tablespace shows 6400 blocks which is equal to 50 MB and the tablespace is about 74% full after we have created and populated the TEST table.
TBSP_NAME                      USED_SPACE  TBSP_SIZE USED_PERCENT
------------------------------ ---------- ---------- ------------
...
...
SYSTEM                              91512     552558   16.5615193
TESTME                               4736       6400           74

Let us now force a space used alert to be fired from OEM 
SQL> insert into  sh.test
  2  select * from sh.sales;
insert into  sh.test
                *
ERROR at line 1:
ORA-01653: unable to extend table SH.TEST by 128 in tablespace TESTME

We can see that the tablespace TESTME is now 100% full  and an alert has been raised as well.
TBSP_NAME                      USED_SPACE  TBSP_SIZE USED_PERCENT
------------------------------ ---------- ---------- ------------
...
...
TESTME                               6400       6400          100

SELECT REASON
, METRIC_VALUE
, MESSAGE_TYPE
, TO_CHAR(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS')
, HOST_ID
FROM SYS.DBA_OUTSTANDING_ALERTS;


SQL> /

REASON
----------------------------------------------
METRIC_VALUE MESSAGE_TYPE TO_CHAR(CREATION_TIM
------------ ------------ --------------------
HOST_ID
----------------------------------------------
Tablespace [TESTME] is [100 percent] full
         100 Warning      15-NOV-2011 08:38:39
kens-orasql-001.corporateict.domain

Now we turn on AUTOEXTEND for the datafile of the TESTME tablespace – note in this case we specify the MAXIZE as UNLIMITED

SQL> alter database datafile '/u01/oradata/HDESK11G/testme01.dbf' autoextend on next 100M maxsize unlimited;

Database altered.

Now we see that the DBA_TABLESPACE_USAGE_METRICS view shows the tablespace as only 1.37% used. 
Currently 50 MB is being used (the file size initially was 50 Mb and all the space in the datafile was used when we inserted rows into the table), but the TBSP_SIZE is showing 466770 (blocks) which is about 3646 Mb ( 466770*8192/1048576).
So why has Oracle chosen this figure?
If we run a df -k command, we see that the /u01 file system where this datafile of the TESTME tablespace is located has about 3.7 GB of free space and with AUTOEXTEND turned on, Oracle estimates that this is the maximum that the datafile can extend upto.

TBSP_NAME                      USED_SPACE  TBSP_SIZE USED_PERCENT
------------------------------ ---------- ---------- ------------
...
....
TESTME                               6400     466770   1.37112497

SQL> !df -k |grep u01

Filesystem           1K-blocks      Used Available Use% Mounted on

/dev/xvdb1            20635700  16007140   3780324  82% /u01

Now what happens if we instead specify an upper file size limit to which the datafile can extend upto with autoextend turned on.

SQL> alter database datafile '/u01/oradata/HDESK11G/testme01.dbf' autoextend on next 100M maxsize 500M;

Database altered.

We now see that the TESTME tablespace is 10% full because the maximum size that this tablespace can now grow to is 64000 blocks or 500 Mb and currently it has 50 Mb of used space.
The tablespaces screen from OEM however shows the tablepace as 33% full because it has grown the datafile to 150 MB from 50 Mb as we had specified the NEXT increment as 100 MB when we had enabled autoextend for the datafile of the TESTME tablesapace.
TBSP_NAME                      USED_SPACE  TBSP_SIZE USED_PERCENT
------------------------------ ---------- ---------- ------------
...
...
TESTME                               6400      64000           10

But what we see from OEM Tablespaces screen (Database Instance Home Page –> Administration –> Tablespaces), the tablespace is 33% full and not 10%.
Remember, this is not what OEM will use to determine the tablespace free space available and when it needs to raise an alert if the space usage threshold is crossed.