For as many OEMs I’ve setup, the one metric that has always amazed me has been the “Tablespace Space Used (%)” metric. This metic is often misunderstood although it “should” be quite simple to understand. What is so hard to understand about percentage (%) used?
In reviewing the documentation for OEM 11g and OEM 12c, the explanation for this metric has not changed much between releases. The calculation that is performed to trigger this metic is really simple math:
Tablespace Space Used (%) = (TotalUsedSpace / MaximumSize) * 100
Once this metric has been triggered, most DBAs start scrambling to perform one of the following task:
- Increase the size of the tablespace
- Reorganizing the entire tablespace (fragmentation issues)
- Relocate segements to another tablespace
- Run Segment Advisor on the tablespace
What I have come to find out is, some times OEM will trigger this metric and the data files may not need any adjustments. In order to get a clearer understanding of what caused this metric to trigger, we need to look at the “fulTbsp.pl” script. This script is located in the $AGENT_HOME/sysman/admin/scripts directory.
In reviewing the “fulTbsp.pl” script, Oracle is not only looking at the current size of the data files and the maxsize of the datafile; they are looking at the file system space as well. The reason for this is to ensure that the data files have enough space to expand if needed.
Now, here is where it can become misleading. By setting the Tablespace Space Used (%) metric for critical to 95, we are thinking that the metric will trigger when the tablespace reaches 95% used, correct. Before rushing to perform the tasks above, lets check and see what space is actually used in the tablespace. In order to do this, Oracle provides us with a DBA view (DBA_TABLESPACE_USAGE_METRICS) to review the percentage of tablespace used. Below I have provided a sample query for getting the usage of a tablespace:
select
tablespace_name,
round(used_space/(1024*1024),2),
round(tablespace_size/(1024*1024),2),
round(used_percent, 2)
from
dba_tablespace_usage_metrics
where
round(used_percent,2) > 90;
Often, I have found that when an alert is triggered for the Tablespace Space Used (%) metric, the data files are less than 90% full. This is due to the alert being triggered because OEM makes the determination that there is not enough space on the file system to expand the data file if needed. If you keep this in mind, you’ll be able to keep a firm grasp on what is going on from the OEM and your tablespaces.
Enjoy!
source: dbasolved
My SQL Home 