Table Access

[Former Member]

Hi,

How do I check in a system whehther a table is fragmanted or it requires a reorg??

There is an ABAP Statment which takes a very long time for fetching the records since two days.

The same statement executed faster since a year till two days back.

There are only 58000 records in the table.

Nimisha .

[stefan_koehler]

Hello Nimisha,

>> How do I check in a system whehther a table is fragmanted or it requires a reorg??

What do you mean with fragmented? Rowchaing, Rowmigration or only unused space?

The unused space can and will be reused... so it is not really necessary to reorg a table, unless you are performing Full Table Scans or need the unused sapce or haven't archived until yet or your clustering factor is too bad, and so on.....

>> There is an ABAP Statment which takes a very long time for fetching the records since two days.

So i think the execution plan changes, because of some new statistics were collected or the table has changed and no statistics are collected. Please check the statistics.

If you have Oracle 10g you can restore the old statistics of yesterday with the procedure DBMS_STATS.RESTORE_TABLE_STATS.

>> There are only 58000 records in the table.

So please check the execution plan...

========================================

@ Chip Dawes:

To use the new "reorg feature" .. you have to enable row movement and your tablespace must use ASSM. You can shrink the table.. but be careful... the rowids will change and the indexes become unusable. So i would be very careful with "row movement" in a SAP environment.

Regards

Stefan

[Former Member]

if I insert 100222333 rows into a table then delete 100000000 rows, I have only 222333 rows but full table scans will still read all data blocks up to the high water mark - all the blocks that ever contained data.

If you are on Oracle10g, you may be able to use the dbms_space.verify_shrink_candidate function to look for candidate for reorging and then use the

alter table table_name shrink space compact;

statement to reorg the table.

Check out the excellent article by Arup Nanda on Segment Management at:

<a href="http://www.oracle.com/technology/pub/articles/10gdba/week15_10gdba.html">http://www.oracle.com/technology/pub/articles/10gdba/week15_10gdba.html</a>

Cheers,

Chip

[Former Member]

Good tip by MHO.

I would approach the issue from another perspective.

Why should reading from a measly 58000 row table take so long?

Did something change?

Can you capture the SLOW SQL statement and post it here for us with the explain plan as well?

[Former Member]

Hi Nimisha

Chances that the table is fragmented are pretty small. However you can calculate if you have a lot of free space in your table, if <b>num_rows * avg_row_len</b> is much smaller then the table size.

There are various ways to get the number of rows and the row lenght. Here is my way:

SQL> select num_rows, avg_row_len, blocks*8192 , num_rows*avg_row_len "SIZE" from dba_tables where table_name = 'MSEG';

  NUM_ROWS AVG_ROW_LEN BLOCKS*8192       SIZE
---------- ----------- ----------- ----------
    534770         628   408780800  335835560

NUM_ROWS is the number of records in the table

AVG_ROW_LEN is the average row length

BLOCKS*8192 is the actual table size in bytes

SIZE is the used space in bytes

This relies on correct statistics! So if SIZE is much smaller then BLOCKS*8192 then a reorg makes sense and can be beneficial for performance too.

But in most cases a sudden performance drop is cause by a different execution plan, like wrong index access.

Best regards, Michael