Skip to main content

Search

Welcome to Tibero GTS!

How to Analyze Tablespace Usage with DBMS_SPACE (SPACE_USAGE)

Document Type | Technical Information

Category | Monitoring/Inspection

Document Number | TMOTI028

 

Overview

This document provides guidance on how to analyze tablespace usage using the DBMS_SPACE package, specifically the SPACE_USAGE query.
SPACE_USAGE query refers to the space below the segment's High Water Mark (HWM), which means it provides information about the currently used space.
 
 

Method

How to Use the SPACE_USAGE Query

SQL> set serveroutput on 
 

declare 
 l_tabname varchar2(30) := 'TableName'; 
 l_fs1_bytes number; 
 l_fs2_bytes number; 
 l_fs3_bytes number; 
 l_fs4_bytes number; 
 l_fs1_blocks number; 
 l_fs2_blocks number; 
 l_fs3_blocks number; 
 l_fs4_blocks number; 
 l_full_bytes number; 
 l_full_blocks number; 
 l_unformatted_bytes number; 
 l_unformatted_blocks number; 
begin 
 dbms_space.space_usage( 
   segment_owner => user, 
   segment_name => l_tabname, 
   segment_type => 'TABLE', 
   fs1_bytes => l_fs1_bytes, 
   fs1_blocks => l_fs1_blocks, 
   fs2_bytes => l_fs2_bytes, 
   fs2_blocks => l_fs2_blocks, 
   fs3_bytes => l_fs3_bytes, 
   fs3_blocks => l_fs3_blocks, 
   fs4_bytes => l_fs4_bytes, 
   fs4_blocks => l_fs4_blocks, 
   full_bytes => l_full_bytes, 
   full_blocks => l_full_blocks, 
   unformatted_blocks => l_unformatted_blocks, 
   unformatted_bytes => l_unformatted_bytes 
   ); 
 dbms_output.put_line('0-25%       Free   = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes); 
 dbms_output.put_line('25-50%      Free   = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes); 
 dbms_output.put_line('50-75%      Free   = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes); 
 dbms_output.put_line('75-100%     Free   = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes); 
 dbms_output.put_line('Full        Blocks = '||l_full_blocks||' Bytes = 
'||l_full_bytes); 
 dbms_output.put_line('Unformatted Blocks = '||l_unformatted_blocks||' Bytes = 
'||l_unformatted_bytes); 
end; 
/ 
 

set serveroutput off

 

Example of Result Analysis After Using SPACE_USAGE

As shown below, the development table uses a total of 58 blocks, while the production table uses a total of 49,924 blocks.
That is, although the number of data rows between the development and production environments was not significantly different, the difference in block usage caused a large difference in full scan execution time.

Development Environment Results (excerpt from sqltrace)

0-25%       Free   = 46 Bytes = 376832 
25-50%      Free   = 0 Bytes = 0 
50-75%      Free   = 1 Bytes = 8192 
75-100%     Free   = 11 Bytes = 90112 
Full        Blocks = 0 Bytes = 0 
Unformatted Blocks = 0 Bytes = 0 

table access (full) TableName(40938) (et=302, cr=59, cu=4, co=26, cpu=0, ro=5000)

Production Environment Results (excerpt from sqltrace)

0-25%       Free   = 32 Bytes = 262144 
25-50%      Free   = 1 Bytes = 8192 
50-75%      Free   = 0 Bytes = 0 
75-100%     Free   = 49877 Bytes = 408592384 
Full        Blocks = 14 Bytes = 114688 
Unformatted Blocks = 0 Bytes = 0 

table access (full) TableName(40905) (et=191878, cr=49925, cu=112, co=20760, cpu=0, 
ro=5275)

 

SPACE_USAGE Analysis and Solutions

  • When performing a full table scan, all blocks allocated to the table (blocks below the high water mark) are read, which can cause significant differences in processing time.
  • A simple solution in such cases is to back up the data, drop the table, and then recreate it.
 
 
 

Related to