Tablespace Space Used (%) ~ Am I really full?

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!

 

 

Advertisements

4 comments

  1. Thx Bobby for this, very useful to know of this script fulTbsp.pl

    1. You are welcome. Always trying to help.

  2. Hi – good article, but this view measures in blocks, not bytes so you should join it to the dba_tablespaces view and use the block_size field. Your MB used calc then becomes used_space*block_size/1024/1024. A good combined query is

    column used_megs format 999,999
    column total_size format 999,999
    column used_percent format 99

    break on contents

    select t1.contents,
    t0.tablespace_name,
    t0.used_space*t1.block_size/1024/1024 used_megs,
    t0.tablespace_size*t1.block_size/1024/1024 total_size,
    t0.used_percent
    from dba_tablespace_usage_metrics t0,
    dba_tablespaces t1
    where t0.tablespace_name = t1.tablespace_name
    order by 1, 2;

    1. Sir,

      After digging around for additional information, you are correct the unit of measurement on the dba_tablespace_usage_metric view is done in blocks. Thanks for providing another good example of code. I have added rounding function to the SQL to make it a bit clearer.


      select t1.contents,
      t0.tablespace_name,
      round(t0.used_space*t1.block_size/1024/1024,2) used_megs,
      round(t0.tablespace_size*t1.block_size/1024/1024,2) total_size,
      round(t0.used_percent, 2) used_percent
      from dba_tablespace_usage_metrics t0,
      dba_tablespaces t1
      where t0.tablespace_name = t1.tablespace_name
      order by 1, 2;

      Enjoy!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Mrs. Jaye's Class Blog

Coordinate Algebra & Advanced Algebra

Pardy DBA

ORA-00001: unique constraint (ORA.BLOG_TAGLINE_PK) violated

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs

HeliFromFinland

Heli's thoughts on Database Designing, Oracle SQL Developer Data Modeler, User Groups etc.

Julian Dontcheff's Database Blog

The good DBA is one who learns from his mistakes, the best DBA is one who learns from other DBA's mistakes

Martins Blog

Trying to explain complex things in simple terms

The Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet.com

Stuff that interests me, if not you!

%d bloggers like this: