Problem solve Get help with specific problems with your technologies, process and projects.

Avoiding table fragmentation

This code will help you identify candidates for table reorg.

We often have tables with a large number of deletes that become candidates for a reorg. We have a query to identify...

candidates, in which we can change the percentage of fragmentation to look for. We also have an Oracle procedure to generate the code to rebuild the table. Because we have some very large tables, we often use dbms_stats to store and restore statistics on tables.

The initial query identifies tables that we consider candidates for rebuilding. We consider any table whose percentage of deletes greater than 5% to be a candidate. At the same time we identify the table, the query creates the call to the procedure that will generate the commands to rebuild the table. The procedure reorg_a_table creates the commands to rebuild the table.

Reorg_a_table uses the dbms_stats package to export the statistics on the table, and import them after the table is rebuilt. Alternatively, the user can modify the code to analyze each table. This has been tested on Oracle 8.1.7.4.

select
  'exec
analyzedb.reorg_a_table('||''''||rtrim(t.table_owner)||''''||','||''''||
  rtrim(t.table_name)||''''||');',
  t.table_owner owner,
  t.table_name name,
  a.num_rows,
  sum(t.inserts) ins,
  sum(t.updates) upd,
  sum(t.deletes) del,
  sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,'9999
99.99') per_del,
round(((sum(t.updates)+sum(t.inserts)+sum(t.deletes))/(decode(a.num_rows
,0,1,a.num_rows)) *100.0),2) per_chg
from analyzedb.table_modifications t,
     all_tables a
where t.timestamp >= to_date('&from_date','dd-mon-yyyy') and
      t.table_owner = a.owner and t.table_owner not in ('DB451','PRECISE','PERFSTAT') and
      t.table_name=a.table_name
having (sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0 >=5
group by t.table_owner, t.table_name, a.num_rows
order by num_rows desc, t.table_owner, t.table_name;


procedure       reorg_a_table
( i_owner IN VARCHAR2, i_table_name IN VARCHAR2)
is
parallel_max_servers number;
is_dba number;
i_am varchar2(30);
is_ok number;
w_owner varchar2(30);
w_table_name varchar2(30);
w_num_extents number;
w_ts_extent_size number;
w_num_rows number;
w_parallel varchar2(100);
w_option_num_value number;
w_partitioned varchar2(3);

cursor reorg_cursor (w_owner varchar2, w_table_name varchar2, w_num_extents
number, w_ts_extent_size number, w_parallel varchar2)
is
select 0.5 as order_column,
      'exec dbms_stats.export_table_stats('||''''||w_owner||''''||','||''''||w_table
_name||''''||
      ',NULL'||','||''''||'STAT_TABLE'||''''||','||''''||w_owner||''''||',TRUE
,'||''''||'ANALYZEDB'||
      ''''||');' as script
  from dual
union
select 1 as order_column,
       'alter table '||owner||'.'||table_name||' move parallel
tablespace reorg storage (initial 5m next 5m minextents 
1);' as script
from dd_tables
where owner = w_owner
  and table_name = w_table_name
  and partitioned = 'NO'
  and temporary = 'N'
union
select 1 as order_column,
       'alter table '||dtp.table_owner||'.'||dtp.table_name||' move
partition '
       ||partition_name||' tablespace reorg storage (initial 5m next 5m
minextents 1);' as script
from dd_tab_partitions dtp, dd_tables dt
where dtp.table_owner = w_owner
  and dtp.table_name = w_table_name
  and dtp.table_name = dt.table_name
  and dtp.table_owner =  dt.owner
union
select 2 as order_column,
       'alter table '||owner||'.'||table_name||' move tablespace '
       ||tablespace_name||' storage (initial '||w_ts_extent_size||'k next '
       ||w_ts_extent_size||'k minextents 1) '||w_parallel||';' as script
from dd_tables
where owner = w_owner
  and table_name = w_table_name
  and partitioned = 'NO'
  and temporary = 'N'
union
select 2 as order_column,
       'alter table '||dtp.table_owner||'.'||dtp.table_name
       ||' move partition '||partition_name||' tablespace '
       ||dtp.tablespace_name||' storage(initial '||w_ts_extent_size||'k next '
       ||w_ts_extent_size||'k minextents 1||);' as script
from dd_tab_partitions dtp, dd_tables dt
where dtp.table_owner = w_owner
  and dtp.table_name = w_table_name
  and dtp.table_name = dt.table_name
  and dtp.table_owner =  dt.owner
union
select 3 as order_column,
       'alter table '||w_owner||'.'||w_table_name||' deallocate unused;' as script
from dual
union
select 4 as order_column,
       'alter index '||di.owner||'.'||di.index_name||
       ' rebuild tablespace reorg storage (pctincrease 0 initial 5m next
5m) '||w_parallel||';' as script
from dba_indexes di,
     dba_tablespaces dts
where di.owner = w_owner
  and di.table_name = w_table_name
  and di.tablespace_name = dts.tablespace_name
  AND di.INDEX_TYPE <> 'LOB'
  and di.temporary = 'N'
  and di.partitioned = 'NO'
union
select 5 as order_column,
       'alter index '||di.owner||'.'||di.index_name||' rebuild tablespace '
       ||di.tablespace_name||' storage(pctincrease 0 initial
'||dts.initial_extent/1024||'k next '||dts.initial_extent/1024||'k)
'||w_parallel||';' as script
from dba_indexes di,
     dba_tablespaces dts
where di.owner = w_owner
  and di.table_name = w_table_name
  and di.tablespace_name = dts.tablespace_name
  AND di.INDEX_TYPE <> 'LOB'
  and di.temporary = 'N'
  and di.partitioned = 'NO'
union
select 5 as order_column, 'alter index
'||index_owner||'.'||dip.index_name
       ||' rebuild partition '||dip.partition_name||' tablespace '
       ||dip.tablespace_name||' storage(pctincrease 0 initial
'||dts.initial_extent/1024||'k next '||dts.initial_extent/1024||'k)
'||w_parallel||';' as script
from  dba_ind_partitions dip,
      dba_indexes di,
      dba_tablespaces dts
where index_owner = w_owner
  and dip.index_owner = di.owner
  and dip.index_name = di.index_name
  and di.table_name = w_table_name
  and dip.tablespace_name = dts.tablespace_name
order by order_column;
  reorg_list reorg_cursor%ROWTYPE;

begin

select upper(i_owner), upper(i_table_name)
  into w_owner, w_table_name
  from dual;

select partitioned, num_rows
  into w_partitioned, w_num_rows
  from dba_tables
 where owner = w_owner
   and table_name = w_table_name;

select round(count(1)*.75,0)
   into w_num_extents
   from dba_extents
  where segment_name = w_table_name
    and owner = w_owner
    and segment_type = 'TABLE';

select OPTION_NUM_VALUE
  into w_option_num_value
  from ANALYZEDB.STAT_OPTIONS
where OPTION_NAME ='NOPARALLEL';


if w_num_rows > w_option_num_value
  then
    w_parallel :='PARALLEL (DEGREE DEFAULT)';
  else
    w_parallel :='NOPARALLEL';
end if;

if w_partitioned = 'NO'
  then
    select INITIAL_EXTENT/1024
      into w_ts_extent_size
      from dba_tablespaces ts
     where tablespace_name in (select tablespace_name
                                 from dba_tables t
                                where table_name = w_table_name
                                  and owner = w_owner
                                  and partitioned = 'NO'
                                  and t.tablespace_name = ts.tablespace_name);
  else
    select INITIAL_EXTENT/1024
      into w_ts_extent_size
      from dba_tablespaces ts
     where tablespace_name in (select tablespace_name
                             from dba_tab_partitions
                            where table_name = w_table_name
                              and table_owner = w_owner
                              and tablespace_name = ts.tablespace_name);
end if;



 dbms_output.enable(1000000);
 dbms_output.put_line('rem Table '||w_owner||'.'||w_table_name||' has
'||w_num_rows||' rows.');
 dbms_output.put_line('rem The table has '||w_ts_extent_size||'k extents.');

 open reorg_cursor (w_owner, w_table_name, w_num_extents, w_ts_extent_size, w_parallel);

 loop
   fetch reorg_cursor into reorg_list;
    exit when reorg_cursor%NOTFOUND;
   dbms_output.put_line(reorg_list.script);
 end loop;

 close reorg_cursor;

 dbms_output.put_line('exec dbms_stats.import_table_stats '||''''||w_owner||''''||','||''''||w_table
_name||''''||
 
',NULL'||','||''''||'STAT_TABLE'||''''||','||''''||w_owner||''''||',TRUE
,'||''''||'ANALYZEDB'||''''||
                      ');');

EXCEPTION
  when NO_DATA_FOUND
  then
    dbms_output.put_line('REM REM REM');
    dbms_output.put_line('REM Object  '||w_owner||'.'||w_table_name||'
Does Not Exist.');
    dbms_output.put_line('REM REM REM');
  when OTHERS
  then
    dbms_output.put_line('REM REM REM');
    dbms_output.put_line('REM System Error');
    dbms_output.put_line('REM REM REM');

end;

Author's Note

Regarding analyzedb.table_modifications, it is populated periodically from sys.dba_tab_modifications. sys.dba_tab_modifications is used when you turn monitoring on to keep track of tables with statistics that may be stale. Since we run statistics only at night, before we run statistics we populate analyzedb.table_modifications from sys.dba_tab_modifications.

desc sys.dba_tab_modifications:
TABLE_OWNER  VARCHAR2(30)
TABLE_NAME  VARCHAR2(30)
PARTITION_NAME  VARCHAR2(30)
SUBPARTITION_NAME  VARCHAR2(30)
INSERTS  NUMBER
UPDATES  NUMBER
DELETES  NUMBER
TIMESTAMP  DATE
TRUNCATED  VARCHAR2(3)

desc analyzedb.table_modifications
TIMESTAMP  DATE
TABLE_OWNER  VARCHAR2(30)
TABLE_NAME  VARCHAR2(30)
PARTITION_NAME  VARCHAR2(30)
SUBPARTITION_NAME  VARCHAR2(30)
INSERTS  NUMBER
UPDATES  NUMBER
DELETES  NUMBER
TRUNCATED  VARCHAR2(3)

Reader Feedback

Expert Don Burleson writes:

1. Oracle has an online table reorg utility called dbms_redefinition that will reorg a table while it is accepting updates.

2. Note that this tip is for dictionary-managed tablespaces, which are now obsolete in Oracle10g. Much of the fragmentation issues are non-existent with locally-managed tablespaces.

3. With LMTs, the main reason most DBAs do table reorgs is chained rows, and this is always a prime consideration when doing a reorg.

Reader Feedback

Tom L. writes: I found an errors in the script: analyzedb.table_modifications ( table not found )

This was last published in June 2004

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close