on 04-07-2012 4:34 PM
Hi All,
How I calculate how many time required for any table re-organization in online???
Please help me.
Thanks,
Manas Sahoo
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.