|
 |
|
Identifying Oracle sparse Tables
Don Burleson
Updated 16 July 2015 |
Sparse tables generally occur when an table is defined with many
free lists (when not using ASSM), and the table has heavy insert and delete activity. This
causes the table to extend, even though it may be largely empty.
Extension occurs because each free list is unaware of the contents of
other free lists inside each free list group.
This state can usually
be detected by selecting tables whose actual size (number of rows
times average row length) is greater than the size of the next extent
for the table. Of course, we must set the number of free lists to the
number of simultaneous insert of update operations, so cannot reduce
the number of freelists without introducing segment header contention.
Listing 5-9 shows the sparse tables found in our sample database.
Listing 5-10 contains the script that produced the report. The query
selects tables that contain multiple free lists, with more than one
extent, where there is excessive free space.
To see excessive free space, we calculate the average row length
(avg_row_len) in the data dictionary view and the number of
rows (num_rows) with a weekly table analyze (i.e. dbms_stats). When we multiply the number of rows in the table
by the average row length, we approximate the actual consumed size of
the data within the table. We then compare this value with the actual
number of allocated bytes in the table. The idea is that a sparse
table will have far more allocated space than consumed space.
Table Extents Bytes M
NEXT M Empty M Row space M Blocks M Pct Full
---------- ------- -------
------ ------- ----------- -------- --------
TST03 65
1,241 20 14 118 1,241 .10
LIKP 3
148 49 24 76 148 .52
VBRK 2
124 4 0 69 124 .56
STXL 35
1,775 40 7 1,021 1,775 .57
VBAK 5
234 49 0 136 234 .58
KOCLU 27
1,889 49 27 1,144 1,889 .61
VBUP 2 866 49 0 570 866
.66
VBUK 2 147 28 0 103 147
.70
VBAP 46 4,314 50 0 3,034 4,314
.70
NAST 3 137 10 2 97 137
.71
VBPA 5 582 32 0 426 582
.73
LIPS 7
2,350 49 0 1,735 2,350 .74
VBRP 45
2,675 49 0 2,029 2,675 .76
WFPRC 30
123 10 7 95 123 .77
VLPMA 16
575 25 23 444 575 .77
EDIDOC 18
432 20 13 337 432 .78
VRPMA 24
700 20 7 549 700 .78
A sparse table report.
As we stated earlier, sparse tables are caused by an imbalance in
multiple free lists, and are evidenced by tables that are continuing
to extend although they are not very full. In the example report, we might take a closer look at the KOCLU, VBRP and
TST03 tables. This oracle script shows
portions of the script that generated
this report.
column c1 heading
"Tablespace";
column c2 heading "Owner";
column c3 heading "Table";
column c4 heading "Bytes M"
format 9,999;
column c5 heading "Extents"
format 999;
column c7 heading "Empty M"
format 9,999;
column c6 heading "Blocks M"
format 9,999;
column c8 heading "NEXT M"
format 999;
column c9 heading "Row space
M" format 9,999;
column c10 heading "Pct
Full" format .99;
select
substr(dt.table_name,1,10) c3,
ds.extents c5,
ds.bytes/1048576 c4,
dt.next_extent/1048576
c8,
(dt.empty_blocks*4096)/1048576
c7,
(avg_row_len*num_rows)/1048576 c9,
(ds.blocks*4096)/1048576
c6,
(avg_row_len*num_rows)/(ds.blocks*4096) c10
from sys.dba_segments ds,
sys.dba_tables dt
see code depot for full script
where ds.tablespace_name =
dt.tablespace_name
and ds.owner = dt.owner
and ds.segment_name =
dt.table_name
and dt.freelists > 1
and ds.extents > 1
and dt.owner not in ('SYS','SYSTEM')
and
(avg_row_len*num_rows)/1048576 > 50
and ds.bytes/1048576 > 20
order by c10;
The sparse.sql report to identify
sparse tables.
|
and include the URL for the page.
