Woohoo, I finally got a bit of spare time to blog — my first blog post ever!
I chose to talk about a technique I used at a client’s site to report the topmost space-wasting objects in an Oracle database. I was looking for a way to detect these objects without having to run some expensive analyze statements or dbms_stats jobs. I found out that I can use the dbms_space package to do this. It worked very well for me and I’m sure lots of DBAs could use this technique too. It is not perfect in all situations as it has some prerequisites that I will list later, but still it does the trick for me.
The dbms_space package has procedures that are very useful for determining how much space is used within db blocks. As far as I know, this information is determined by looking at the tablespace bitmap pages. I haven’t confirmed this yet, but that would explain why it is so much faster than running an analyze or dbms_stats.gather_%_stats.
So, the idea is to generate the space-usage information using dbms_space, store that information into a table, and then run queries on it to report the topmost space-wasters.
Here are simplified versions of the scripts I use:
dbms_space.
connect &your_account/&your_account_password
create table segment_space_stats
(owner varchar2(32),
segment_name varchar2(32),
segment_type varchar2(32),
tablespace_name varchar2(32),
segment_space_management varchar2(32),
unformatted_blocks number,
unformatted_bytes number,
fs1_blocks number,
fs1_bytes number,
fs2_blocks number,
fs2_bytes number,
fs3_blocks number,
fs3_bytes number,
fs4_blocks number,
fs4_bytes number,
full_blocks number,
full_bytes number,
total_blocks number,
total_bytes number,
unused_blocks number,
unused_bytes number,
last_used_extent_file_id number,
last_used_extent_block_id number,
last_used_block number,
timestamp date)
;
dbms_space procedures (space_usage and unused_space) on all applicable objects.
-- You may need to grant these to your_account.
--
-- connect sys as sysdba
-- grant select any tables, select any dictionary, analyze any to &your_account;
-- grant execute on dbms_space to &your_account;
-- connect &your_account/&your_account_password
CREATE OR REPLACE procedure GEN_SEGMENT_SPACE_STATS
as
index_does_not_exist EXCEPTION;
table_does_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(index_does_not_exist, -1418);
PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_last_used_extent_file_id number;
v_last_used_extent_block_id number;
v_last_used_block number;
begin
for s in (select owner, segment_name, segment_type, seg.tablespace_name , segment_space_management
from dba_tablespaces ts, dba_segments seg
where ts.tablespace_name not in ('SYSTEM', 'SYSAUX')
and ts.tablespace_name = seg.tablespace_name
and segment_type in ('TABLE', 'INDEX','CLUSTER','LOB')
and (owner, segment_name, segment_type, seg.tablespace_name ) in (
select owner, segment_name, segment_type, tablespace_name from segment_space_stats))
loop
begin
if s.segment_space_management = 'AUTO' then
dbms_space.space_usage(s.owner, s.segment_name, s.segment_type, v_unformatted_blocks, v_unformatted_bytes,
v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes , v_fs3_blocks, v_fs3_bytes ,
v_fs4_blocks, v_fs4_bytes , v_full_blocks, v_full_bytes , NULL);
end if;
dbms_space.unused_space(s.owner, s.segment_name, s.segment_type, v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id,
v_last_used_extent_block_id, v_last_used_block, NULL);
update SEGMENT_SPACE_STATS
set tablespace_name=s.tablespace_name,
unformatted_blocks=v_unformatted_blocks,
unformatted_bytes=v_unformatted_bytes,
fs1_blocks =v_fs1_blocks,
fs1_bytes =v_fs1_bytes,
fs2_blocks =v_fs2_blocks,
fs2_bytes =v_fs2_bytes,
fs3_blocks =v_fs3_blocks,
fs3_bytes =v_fs3_bytes,
fs4_blocks =v_fs4_blocks,
fs4_bytes =v_fs4_bytes,
full_blocks=v_full_blocks,
full_bytes =v_full_bytes,
timestamp =sysdate,
total_blocks=v_total_blocks,
total_bytes =v_total_bytes,
unused_blocks=v_unused_blocks,
unused_bytes =v_unused_bytes,
last_used_extent_file_id =v_last_used_extent_file_id,
last_used_extent_block_id=v_last_used_extent_block_id,
last_used_block =v_last_used_block
where owner=s.owner
and segment_name=s.segment_name
and segment_type=s.segment_type;
exception
when index_does_not_exist then
null; -- ignore these errors
when table_does_not_exist then
null; -- ignore these errors
when others then
raise;
end;
end loop;
commit;
for s in (select owner, segment_name, segment_type, seg.tablespace_name, segment_space_management
from dba_tablespaces ts, dba_segments seg
where ts.tablespace_name not in ('SYSTEM', 'SYSAUX')
and ts.tablespace_name = seg.tablespace_name
and segment_type in ('TABLE', 'INDEX','CLUSTER','LOB')
minus
select owner, segment_name, segment_type, tablespace_name, segment_space_management
from segment_space_stats)
loop
begin
if s.segment_space_management = 'AUTO' then
dbms_space.space_usage(s.owner, s.segment_name, s.segment_type, v_unformatted_blocks, v_unformatted_bytes,
v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes , v_fs3_blocks, v_fs3_bytes ,
v_fs4_blocks, v_fs4_bytes , v_full_blocks, v_full_bytes , NULL);
end if;
dbms_space.unused_space(s.owner, s.segment_name, s.segment_type, v_total_blocks, v_total_bytes, v_unused_blocks,
v_unused_bytes, v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block, NULL);
insert into SEGMENT_SPACE_STATS
values(s.owner, s.segment_name, s.segment_type, s.tablespace_name, s.segment_space_management,v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes,
v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id, v_last_used_extent_block_id,
v_last_used_block,sysdate);
exception
when index_does_not_exist then
null; -- ignore these errors
when table_does_not_exist then
null; -- ignore these errors
when others then
raise;
end;
end loop;
commit;
delete SEGMENT_SPACE_STATS where (owner, segment_name, segment_type, tablespace_name) not in
(select owner, segment_name, segment_type, tablespace_name from dba_segments
where tablespace_name not in ('SYSTEM', 'SYSAUX')
and segment_type in ('TABLE', 'INDEX','CLUSTER','LOB'));
commit;
end;
/
var v_job_id number;
begin
-- schedule the job to run daily at 4AM...
dbms_job.submit(:v_job_id,'GEN_SEGMENT_SPACE_STATS;',to_date(trunc(sysdate+1)+4/24),'trunc(sysdate+1)+4/24');
commit;
end;
/
exec dbms_job.run(:v_job_id)
TTITLE center underline "Top Ten Largest Empty Segments"
set linesize 140 pagesize 100
col owner for a20
col total_MB for 999999
clear breaks
clear computes
break on report dup
compute sum of total_MB on report
select a.*
from (select owner, segment_name, segment_type, tablespace_name, total_bytes/1048576 total_MB
from segment_space_stats
where full_bytes = 0
order by total_bytes desc) a
where rownum<11
;
Top Ten Largest Empty Segments
______________________________
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME TOTAL_MB
-------------------- -------------------------------- -------------------------------- -------------------------------- --------
PERFSTAT STATS$SQL_PLAN TABLE PERFSTAT_DATA 5
PERFSTAT STATS$SQL_PLAN_USAGE TABLE PERFSTAT_DATA 5
PERFSTAT STATS$SEG_STAT TABLE PERFSTAT_DATA 3
BP_PROD_JOBS JB_SEARCH_RESULT_ALERT TABLE BP_PROD_JOBS_DATA 2
PERFSTAT STATS$BUFFERED_QUEUES TABLE PERFSTAT_DATA 1
PERFSTAT STATS$BUFFERED_QUEUES_PK INDEX PERFSTAT_DATA 1
PERFSTAT STATS$BUFFERED_SUBSCRIBERS TABLE PERFSTAT_DATA 1
PERFSTAT STATS$CR_BLOCK_SERVER_PK INDEX PERFSTAT_DATA 1
PERFSTAT STATS$CR_BLOCK_SERVER TABLE PERFSTAT_DATA 1
PERFSTAT STATS$BUFFERED_SUBSCRIBERS_PK INDEX PERFSTAT_DATA 1
--------
sum 21
10 rows selected.
TTITLE center underline "Top Ten Largest Space Wasters"
set linesize 140 pagesize 20
col owner for a20
col segment_type for a12
col total_MB for 999999
col unformatted_MB for 999999
col unused_MB for 999999
col pct_unused for 999 heading "Pct Unused"
col estmtd_pssbl_svngs for 99999 heading "Estimated|Possible Savings|w Index Rebuild"
clear breaks
clear computes
break on report dup
compute sum of total_MB on report
compute sum of unused_MB on report
select a.*
from (
select owner, segment_name, segment_type,
total_bytes/1048576 total_MB,
unformatted_bytes/1048576 unformatted_MB,
unused_bytes/1048576 unused_MB,
trunc(unused_bytes / total_bytes * 100) pct_unused,
case when segment_type = 'INDEX' and total_blocks >= 256 and fs2_blocks >= 64
then unused_blocks + fs2_blocks
else 0
end * b.block_size/1048576 estmtd_pssbl_svngs
from segment_space_stats a, dba_tablespaces b
where unused_bytes <> 0
and a.tablespace_name = b.tablespace_name
order by unused_bytes desc) a
where rownum<11
;
Top Ten Largest Space Wasters
_____________________________
Estimated
Possible Savings
OWNER SEGMENT_NAME SEGMENT_TYPE TOTAL_MB UNFORMATTED_MB UNUSED_MB Pct Unused w Index Rebuild
-------------------- -------------------------------- ------------ -------- -------------- --------- ---------- ----------------
BP_PROD_JOBS JB_SEARCH_RESULT_IDX INDEX 2229 4 56 2 2179
BP_PROD_MVIEWS USER_INFO_BASIC TABLE 4412 0 48 1 0
BP_PROD_JOBS JB_SEARCH_RESULT TABLE 1216 6 40 3 0
BP_PROD_JOBS JB_SEARCH_RESULT_USJ_IDX INDEX 2109 4 32 1 2041
BP_PROD_MVIEWS USER_INFO TABLE 6039 0 31 0 0
BP_PROD_GW_SUPPORT GWS_ACCESS_LOG_SUM_PK INDEX 1761 1 16 0 323
BP_PROD_JOBS JB_MONSTER_XMIT_LOG TABLE 3108 0 8 0 0
BP_PROD_JOBS DR$JM_SEARCH_DNRM_XML_IDX_2$I TABLE 144 0 8 5 0
BP_PROD_JOBS DR$JM_SEARCH_DNRM_XML_IDX_1$I TABLE 144 0 8 5 0
BP_PROD_GW_SUPPORT GWS_DLY_LOG_20070423_UK INDEX 88 0 6 6 7
-------- ---------
sum 21250 252
10 rows selected.
TTITLE center underline "Top Ten Indexes with non-full blocks"
clear breaks
clear computes
set linesize 145 pagesize 100
col owner for a20
col fs1_blocks for 9999999 heading "< 25%|blocks free"
col fs2_blocks for 9999999 heading "25-50%|blocks free"
col fs3_blocks for 9999999 heading "50-75%|blocks free"
col fs4_blocks for 9999999 heading "75-100%|blocks free"
col full_blocks for 9999999 heading "blocks|full"
col total_blocks for 9999999 heading "total|blocks"
col estmtd_pssbl_svngs for 99999 heading "Estimated|Possible Savings|w Index Rebuild"
col block_size noprint
break on report dup
compute sum of estmtd_pssbl_svngs on report
select a.*
from (
select owner, segment_name,
fs1_blocks ,
fs2_blocks ,
fs3_blocks ,
fs4_blocks ,
full_blocks, total_blocks,
case when segment_type = 'INDEX' and total_blocks >= 256 and fs2_blocks >= 64
then unused_blocks + fs2_blocks
else 0
end * b.block_size/1048576 estmtd_pssbl_svngs,
b.block_size
from segment_space_stats a, dba_tablespaces b
where segment_type = 'INDEX'
and a.tablespace_name = b.tablespace_name
order by fs4_blocks desc, fs3_blocks desc, fs2_blocks desc, fs1_blocks desc) a
where rownum<11
;
Top Ten Indexes with non-full blocks
____________________________________
Estimated
< 25% 25-50% 50-75% 75-100% blocks total Possible Savings
OWNER SEGMENT_NAME blocks free blocks free blocks free blocks free full blocks w Index Rebuild
-------------------- -------------------------------- ----------- ----------- ----------- ----------- -------- -------- ----------------
BP_PROD_JOBS JB_SEARCH_RESULT_IDX 0 271793 0 0 5035 285312 2179
BP_PROD_JOBS JB_SEARCH_RESULT_USJ_IDX 0 257204 0 0 7402 269952 2041
BP_PROD_JOBS JB_SEARCH_RESULT_SCORE_IDX 0 122435 0 0 4443 127488 957
BP_PROD_GW_SUPPORT GWS_ACCESS_LOG_SUM_PK 0 39342 0 0 183215 225408 323
PERFSTAT STATS$SQL_SUMMARY_PK 0 2015 0 0 9354 12288 22
AVAIL STAT_TABLE 0 1556 0 0 20269 22528 16
BP_PROD_INVITE REL_RELATIONSHIP_QUERY_IDX 0 489 0 0 155335 156544 4
PERFSTAT STATS$EVENT_HISTOGRAM_PK 0 245 0 0 6299 6656 2
BP_PROD_JOBS JB_ACTIVITY_LOG_IDX 0 245 0 0 49501 50816 8
BP_PROD_MVIEWS USER_INFO_BASIC_LWR_USRNAM_IDX 0 229 0 0 91468 92160 2
----------------
sum 5554
10 rows selected.
Voila! Isn’t that pretty? I know, I need to explain these reports a bit…
The first report is straightforward. It lists the ten largest empty segments. As you can see, there aren’t any large empty objects in this database. This is mostly due to the work of Oracle’s Automatic Storage Management (ASM).
The second report lists the objects with the most unused space within their currently allocated extents.
The column that probably gets you going “huh?” is the “Estimated Possible Savings w Index Rebuild”. This column is derived based on my own observations of the data generated by dbms_space.space_usage.
I noticed that for indexes, there are never any blocks that are totally empty. That got me curious. It seems that all blocks allocated to the indexes automatically get a usage estimate of at least 25%. I also noticed, based on my analysis of the indexes, that these large indexes with lots of blocks in the 25-50% range are indexes that get updated very frequently, and in fact, get lots of their rows deleted (i.e. rows older than, say, 30 days are deleted).
I was of the impression that lots of old blocks would be reported in the 0-25% utilization range. So I figured, what the heck, let’s rebuild some of these and see if I’m right in suspecting that most of these blocks in the 25-50% range are empty. And, bingo! The space usage went down to about 10% more than the “blocks full” figure. Nice! I might be able to use this to estimate how much space I can save by rebuilding an index. Yes, I know — the problem is an application-design issue, and indexes shouldn’t need to be rebuilt over time. Hey, if we lived in a perfect world, there wouldn’t be any administrators… but that’s another story.
I also noticed that this was not necessarily true for small indexes, hence the case statement I have in the query. Only indexes larger than 256 blocks and with at least 64 blocks in the 25-50% range are considered for space saving. You can use whatever value you like here. These are the ones that work for me. And no, I didn’t do any extensive research or testing with these figures.
A few things that you should be aware of:
dbms_space.space_usage works only for ASM segments.dbms_space‘s unused_space, space_usage and free_space do not require a special license to use. The other procedures in that package are linked to the AWR licensing (at least they were the last time I checked).Ready to optimize your Oracle Database for the future?