SAP Online re-organization time

[Former Member]

Hi All,

How I calculate how many time required for any table re-organization in online???

Please help me.

Thanks,

Manas Sahoo

[volker_borowski2 · Accepted solution]

Hi,

make sure your table has current stats, then check the number ob blocks the table has and

how many indexes and how many blocks these have as well.

10K block reads / per sec on single threaded array reads are a good rule-of-thumb value.

You need to read the table once to copy the data (may be divided by #PQ procs), assume the double up to tripple time for write.

You will then read the new table (which may be smaller due to compression or cleand up fragmentation) as often, as you have indexes on the table. New indexes will be written to temp and then copied back to their target which will again do a read of the temp area as 10K blocks/sec and with a write time times two.

So if you go single proc wise for a 3 GB table (~ 400k blocks), it will take about 40 secs to read the table. You can verify this in sqlplus with

set timing on

select /*+ full(T) */ count(*) from SAPSR3.TABLENAME T;

If this matches your system you have the first keyvalue for calculating, otherwise, if you are slower or faster, adjust.

Assume the double to tripple value for writing, so the reorg will need 40 secs to read the sample table and ~ 100 secs to write the new table, bringing the sum up to 140 now.

Our table has a primary key that needs to be re-created which has (rotating assumtion wheel now ...) 800M = 100k blocks. So we will need to read the table (40 secs) and write the index to temp (100k/10k*2,5 = 25 sec) which results in 65 total secs for the PK and 205 secs total time up to now.

This should give you an idea how to proceed. If there are more indexes, you need to add theses as well. If you can use parallel DDL for your reorg, you might be faster. To measure:

set timing on

select /*+ full(T) parallel(T,4) */ count(*) from SAPSR3.TABLENAME T;

If you did archiving on the table before, the reorged objects (tabel and indexes) will be smaller, so you can assume less blocks for the target objects.

there are many many other values, that may make this sort of calculation completely useless, but I found the 10k blockreads / sec some quite stable value on average todays hardware.

Volker