Checking for Fragmentation (Oracle)

Use

This section describes how to check for fragmentation in an Oracle database. For more information, see Managing Tables and Indexes (Oracle) .

Procedure

You can check for fragmentation using BR*Tools or the database features supplied by Oracle:

• Check for unused space in a table using BRSPACE.

  You can do this interactively or using command line options, as follows:

  • Interactively, start BRSPACE with:

    brspace -f dbshow -c tbinfo -t <table_name>

    For more information, see Command options for BRSPACE .

  • Using command line options to get a report including index statistics, for example:

    brconnect -u / -c -f stats -t all_ind -m +I

    For more information, see Command Options for BRCONNECT .

    Towards the end, the report contains index statistics (charts of 100 indexes, processed using validate structure). The values of the two columns used and data are important. The percentage of data / used is equivalent to the pct_used described below. It should not be below 50%.

• Check for unused space in an index using Oracle database features.

  The steps are as follows:

  1. Determine the index you want to analyze.

  2. Log in to SQL*Plus as user SAP<SID>.

  3. Run the command analyze index <NAME> validate structure

  4. Display statistics with the command select * from index_stats

     The column pct_used returns the average used space in index B-tree blocks. If this is below 50% the index is considered to have excessive unused space.