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.