Table ReOrg Analyzer

Table ReOrg Analyzer


Finding and repairing chained rows is an important part of Oracle administration. When an Oracle rows expands,
it sometimes chains onto multiple data blocks. Excessive row chaining can cause a dramatic increase in disk I/O
because several I/O’s are required to fetch the block instead of one single I/O.





Of course, row chaining can be prevented by setting the PCTFREE storage parameter high enough to keep space on each data block
for row expansion. In cases where the data columns contain RAW and LONG RAW columns, row chaining may be unavoidable because the average row
length may exceed the data block size. That is why the query below filters out tables with RAW data types.
Here is the code that we use to generate a report showing all tables with excessive chained rows. Note that the use of this script is
predicated on the use of Oracle’s ANALYZE command to populate the chain_cnt and num_rows columns of the DBA_TABLES data dictionary view.
Once identified, the tables should be reorganized using Create Table As Select (CTAS) or by using the Oracle export-import utilities.



spool chain.lst;
set pages 9999;
column c1 heading "Owner" format a9;
column c2 heading "Table" format a12;
column c3 heading "PCTFREE" format 99;
column c4 heading "PCTUSED" format 99;
column c5 heading "avg row" format 99,999;
column c6 heading "Rows" format 999,999,999;
column c7 heading "Chains" format 999,999,999;
column c8 heading "Pct" format .99;
set heading off;
select 'Tables with chained rows and no RAW columns.' from dual;
set heading on;
select
owner c1,
table_name c2,
pct_free c3,
pct_used c4,
avg_row_len c5,
num_rows c6,
chain_cnt c7,
chain_cnt/num_rows c8
from
dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
(select table_name from dba_tab_columns
where
data_type in ('RAW','LONG RAW')
)
and
chain_cnt > 0
order by
chain_cnt desc ;
This will produce a nice report that shows tables that you will want to reorganize:
Owner Table PCTFREE PCTUSED avg row Rows Chains Pct
------ --------- ------- ------- ------- --------- --------- ----
SAPR3 ZG_TAB 10 40 80 5,003 1,487 .30
SAPR3 ZMM 10 40 422 18,309 509 .03
SAPR3 Z_Z_TBLS 10 40 43 458 53 .12
SAPR3 USR03 10 40 101 327 46 .14
SAPR3 Z_BURL 10 40 116 1,802 25 .01
SAPR3 ZGO_CITY 10 40 56 1,133 10 .01
6 rows selected.



Cheers,


SpicyOracle.