Re: Fragmentation

Hi,

You mention to execute the query and load it into Excel for easier viewing. You could use my utility SQL*XL instead. It will safe you the hassle of importing and formatting it all in Excel. SQL*XL executes SQL from Excel. No need to worry about formats again!

Have a look at it at http://www.oraxcel.com

Regards, Gerrit-Jan Linker

In article <8ls8ak$jlo$1_at_nnrp1.deja.com>,   oratune_at_aol.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Jul 29 2000 - 00:00:00 CDT