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:
- Create a table to store the statistics generated by
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) ; - Create a procedure and job to run the
dbms_spaceprocedures (space_usageandunused_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) - Run queries on the table’s data.
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_usageworks only for ASM segments.dbms_space‘sunused_space,space_usageandfree_spacedo 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).- The above code and procedures are examples only. Use them at your own risk, and tailor them as you wish. And,
- Feel free to test my assumptions as extensively as you wish. And I welcome your feedback.
I hope you find this useful.
Enjoy!
Marc
Share this
You May Also Like
These Related Stories
No Comments Yet
Let us know what you think