Monitor the Oracle tables and index tablespaces

Here are my thoughts on keeping track on oracle tables and indexes growth.

1. Collecting growth data for Oracle Tables and Indexes

create table perfstat.stats$tab_stats
(
   snap_time       date,
   server_name     varchar2(20),
   db_name         varchar2(9),
   tablespace_name varchar2(40),
   owner           varchar2(40),
   table_name      varchar2(40),
   num_rows        number,
   avg_row_len     number,
   next_extent     number,
   extents         number,
   bytes           number
)
tablespace perfstat
storage (initial 1m next 1m maxextents unlimited)
;

drop table perfstat.stats$idx_stats;


create table perfstat.stats$idx_stats
(
   snap_time         date,
   server_name       varchar2(20),
   db_name           varchar2(9),
   tablespace_name   varchar2(40),
   owner             varchar2(40),
   index_name        varchar2(40),
   clustering_factor number,
   leaf_blocks       number,
   blevel            number,
   next_extent       number,
   extents           number,
   bytes             number
)
tablespace perfstat
storage (initial 1m next 1m maxextents unlimited)
;


drop index
   perfstat.tab_stat_date_idx;

create index
   perfstat.tab_stat_date_idx
on
   perfstat.stats$tab_stats
( snap_time )
tablespace perfstat
storage (initial 1m next 1m maxextents unlimited)
;


drop index
   perfstat.idx_stat_date_idx;
create index
   perfstat.idx_stat_date_idx
on
   perfstat.stats$idx_stats
( snap_time )
tablespace perfstat
storage (initial 1m next 1m maxextents unlimited)
;

The following script can be executed once each week to analyze the table and indexes and collect the table and index data. Note that we must set the oratab file location and pass the proper ORACLE_SID when executing this script:

--****************************************************************
-- Now we grab the index statistics
--****************************************************************
 

-- add analyze and table collection commands here
 

insert into perfstat.stats\$idx_stats
(
   select
      SYSDATE,
      lower('${host}'),
      lower('${ORACLE_SID}'),
      i.tablespace_name,
      i.owner,
      i.index_name,
      i.clustering_factor,
      i.leaf_blocks,
      i.blevel,
      s.next_extent,
      s.extents,
      s.bytes
   from dba_indexes  i,
        dba_segments s,
        dba_tables   t
   where
      i.table_name = t.table_name
   and
      segment_name = index_name
   and
      s.tablespace_name = i.tablespace_name
   and  
      s.owner = i.owner
   and
      i.owner not in ('SYS','SYSTEM')
--   and
--      t.num_rows > 1000
);

Note that this script also has commented out code to restrict the population of rows to tables that contain more than 1,000 rows. This is because the DBA may only be interested in collecting statistics on the most active tables within their database.

The following reports are designed to show the DBA changes within the status of individual objects and the overall space usage for the database as a whole. For example, reports can be run against the stats$tab_stats and stats$idx_stats tables to show the total number of bytes allocated within individual tablespaces within the database.

column old_bytes format 999,999,999
column new_bytes format 999,999,999
column change    format 999,999,999

select
   new.index_name,
   old.bytes                old_bytes,
   new.bytes                new_bytes,
   new.bytes - old.bytes    change
from
   stats$idx_stats old,
   stats$idx_stats new
where
   old.index_name = new.index_name
and
   new.bytes > old.bytes
and
   new.bytes - old.bytes > 10000
and
   to_char(new.snap_time, 'YYYY-MM-DD') =
          (select max(to_char(snap_time,'YYYY-MM-DD')) from stats$idx_stats)
and
   to_char(old.snap_time, 'YYYY-MM-DD') =
           (select max(mydate) from d1)
and
   new.index_name not like 'STATS$%'
order by
   new.bytes-old.bytes desc
;

Note that this report is sequenced so that the tables with the most growth appear at the top of the report.

2. Use Coalesce and deallocate unused space

Oracle notes that the "deallocate unused space" clause is used to to explicitly deallocate unused space at "the end" of a segment and makes that space available for other segments within the tablespace. 

alter table xxx deallocate unused space;

alter index xxx deallocate unused space;

Internally, Oracle deallocates unused space beginning from the end of the objects (allocated space) and moving downwards toward the beginning of the object, continuing down until it reaches the high water mark (HWM).  For indexes, "deallocate unused space" coalesces all leaf blocks within same branch of b-tree, and quickly frees up index leaf blocks for use.

3. If we start using block sizes “the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache.”.
4. Use

1. Bitmap indexes - Bitmap indexes are used where an index column has a relatively small number of distinct values (low cardinality). These are super-fast for read-only databases, but are not suitable for systems with frequent updates.
2. B-tree indexes - This is the standard tree index that Oracle has been using since the earliest releases.
3. Bitmap join indexes - This is an index structure whereby data columns from other tables appear in a multi-column index of a junction table. This is the only create index syntax to employ a SQL-like from clause and where clause

5. Limit the Number of Indexes for Each Table
6. Drop Indexes That Are No Longer Required
7. Estimate Index Size and Set Storage Parameters

4. The maximum size of a single index entry is approximately one-half the data block size.
5. Storage parameters of an index segment created for the index used to enforce a primary key or unique key constraint can be set in either of the following ways:

                                                               i.      In the ENABLE ... USING INDEX clause of the CREATE TABLE or ALTER TABLE statement

                                                             ii.      In the STORAGE clause of the ALTER INDEX statement

8. Consider Parallelizing Index Creation
9. Specify the Tablespace for Each Index
10. Consider Creating Indexes with NOLOGGING
11. Consider Costs and Benefits of Coalescing or Rebuilding Indexes

6. Improper sizing or increased growth can produce index fragmentation. To eliminate or reduce fragmentation, you can rebuild or coalesce the index.

Rebuild Index	Coalesce Index
Quickly moves index to another tablespace	Cannot move index to another tablespace
Higher costs: requires more disk space	Lower costs: does not require more disk space
Creates new tree, shrinks height if applicable	Coalesces leaf blocks within same branch of tree
Enables you to quickly change storage and tablespace parameters without having to drop the original index.	Quickly frees up index leaf blocks for use.