Do I need to reorganise my table?

It’s a tricky question. I have explained here by using a 33GB SAP BW driving table called VBAP which needed a major work. At end I had to rebuild this table using Quest Shareplex.

VBAP Table acquired 3,873,549 blocks = 30.2 GB in Space , The total number of rows were 15,900,000
So Rows per block = 15,900,000/3,873,549 blocks= 4 rows per block

The oracle analyze stats show Avg row length of 1256 bytes so for a 8KB block size

Ideal block count would be = 15,900,000/6 rows per block(avg row len 1256 bytes) = 2,650,000 Blocks  not 3,873,549 blocks
Oracle currently using 45% more space due to row chaining.

I have used SQL script below to locate number of blocks for a 16 million row table. The row dist count below indicate on avg we have chained rows going down upto 3 database blocks.

select rows_per_block,

count(*) blocks,
sum(rows_per_block) sum_rows
from
(
select dbms_rowid.rowid_block_number(rowid),
count(*) rows_per_block
from SAPR3.vbap
group by dbms_rowid.rowid_block_number(rowid)
)
group by rows_per_block
order by 1 desc;

select to_char(floor(rows_per_block/10)*10,’fm990′)||”’s’ rows_per_block,
count(*) blocks,
sum(rows_per_block) sum_rows
from
(
select dbms_rowid.rowid_block_number(rowid),
count(*) rows_per_block
from t1
group by dbms_rowid.rowid_block_number(rowid)
)
group by to_char(floor(rows_per_block/10)*10,’fm990′)||”’s’
order by 1 desc;

ROWS_PER_BLOCK BLOCKS SUM_ROWS
53         1     53
51         4     204
50         3     150
49         6     294
48         5     240
47         19     893
46         35     1610
45         52     2340
44         77     3388
43         143     6149
42         181     7602
41         272     11152
40         387     15480
39         606     23634
38         814     30932
37         1119     41403
36         1475     53100
35         1985     69475
34         2627     89318
33         3472     114576
32         4262     136384
31         5299     164269
30         6662     199860
29         8107     235103

Why 1… 50 rows allocated in a single block?

It must be due to bespoke development done on table. I feel when table was originally populated , it had very less data & SAP-Oracle put those many rows into a single block but over period of time things have changed and new columns for bespoke development and data pushed row chaining on table. In short I can see a need for rebuilding this table.

% of Rows retrieved thru Row Chaining
http://www.akadia.com/services/ora_chained_rows.html

SELECT name,value FROM v$sysstat WHERE name like ‘%table%fetch%ro%’;

NAME VALUE
——————————————————————————-
table fetch by rowid 68617756592
table fetch continued row 4330753

Table fetch by rowid : Number of ROWS retrieved thru Direct ROWID
table fetch continued row : Number of ROWS going thru ROW CHAINING