Re: Fragmentation

In article <8lrkbr$52m$1_at_nnrp1.deja.com>,   thiko_at_my-deja.com wrote:
> Hi
>
> Does anyone know how to identify fragmentation in tables without using
> the analyze or compute statistics sql? Is there any SQL for this?
>
> Many Thanks
>
> Thiko!
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

If I read the question correctly you are wanting to know if there is fragmentation in the data blocks of a table rather than if there are a number of extents for that table. If this is true then the following query should provide a picture, although somewhat cryptic to the uninitiated, of the data and its location:

select dbms_rowid.rowid_to_restricted(rowid,0) from <...>
order by 1;

Replace <...> with the table you want this information from. This returns the Oracle 7-format rowid from your Oracle 8 table. This will be a decimal-separated list of hexadecimal values for block number, row number and file number, in that order. As an example an Oracle 7 rowid might look like this:

00000003.0000.0004

    ^ ^ ^
block no row file no

A data block should fill before a new block is used resulting in sequential row number values for a given block. Should rows be migrated or deleted the ROWID values will change. Finding non-sequential row numbers in a given block indicates fragmentation in that block:

A full block without fragmentation:

00000003.0000.0004
00000003.0001.0004
00000003.0002.0004
00000003.0003.0004
00000003.0004.0004
00000003.0005.0004

Note that the row number portion of the ROWID is sequential.

A fragmented block:

00000003.0000.0004
00000003.0001.0004
00000003.0003.0004
00000003.0005.0004

Note the missing entries in the row number portion.

The query above will illustrate this, although you might want to spool the output to a file and load it into Excel for easier reading.

Another query you can run to determine if a table is internally fragmented is this one:

select distinct dbms_rowid.rowid_block_number(rowid) "BLOCK NUMBER", count(*)
from <...>
group by dbms_rowid.rowid_block_number(rowid);

This will give a row count per block, and if the counts are not uniform you have internal fragmentation. For example:

BLOCK NUMBER COUNT(*)
------------ --------

           2       44
           3       56
           4       56
           5       23
           6       17
           7       47

Data blocks 2, 5 and 6 are not full, therefore it can be presumed that there is fragmentation in those blocks. Block 7 is the last block of the table and may or may not be fragmented.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.