O2O Migration - Index builds run slow

[Geoff Wild]

O2O = Oracle to Oracle migration.

Basically, we are rebuilding a 9.X DB in prep for upgrade to 10g.


Background info from DBA:

"Summary:
Consultant from Oracle is back to lead us through another O2O migration. We started the process at 10:40 AM Wednesday morning and managed to complete the data migration portion in approximately 5 hours which is consistent with the first pass last week. Unfortunately, we are experiencing performance issues with the index builds again. We have been working with UNIX throughout the day to troubleshoot the issue, but a root cause has not yet been determined. We will involve EMC on Thursday morning.

Plan for Day 2:
The plan for Thursday is to work with UNIX and EMC in an attempt to determine what is causing the index builds to run so slowly. The index builds continue to run through the evening but as of 9:45 PM Wednesday, we only had 71 out of 681 indexes created. If this problem is resolved early enough in the day on Thursday, we may be able to run through the entire migration again, or at least the index builds. This would assist us with more accurately estimating the total length of time required for the production migration.

Technical Details:
The data migration portion of the O2O process continues to complete within a reasonable amount of time and with good throughput. Within this timeframe, we have been able to migrate approximately 1 TB of data at a rate averaging 200 GB per hour. The index builds are still experiencing difficulty due to slow disk reads. A new temporary sorting area within the database was created on a new filesystem but this only gave us a temporary boost in performance, unfortunately. CPU utilization often degrades to the point where it is approximately 90% idle, and almost all the Oracle parallel processes are sleeping rather than running. As was the case last week, disk writes are not an issue but Oracle seems to not be able to read data fast enough to keep up with writes to disk. If we can resolve the index build issue, it would not be unreasonable to estimate a completed migration within 9 to 10 hours. We will continue to work towards a resolution on this."


More info: The location of the New DB is on a brand new DMX3 with 24 GB of cache. The ldisk layout is approx 130 striped disks (mirrored - not raid 5). This server is a RP4440 with 16GB of ram and 8 cpus. It is the only server on this new frame - at the moment. Unix reads are great:

Write Test

# timex prealloc test.50GB 53687091200

real 6:28.00
user 3.73
sys 2:57.13

# timex dd if=/oracle/IPR/sapdata05/test.50GB of=/dev/null bs=8k
6553600+0 records in
6553600+0 records out

real 6:30.72
user 7.99
sys 2:45.78


On the source server, it is on a DMX1000, shared amongst many servers - and my dd test takes over 30 minutes!

Question is, why are the Oracle Index builds reading sooooo slowwwwww???

I attached a jpg of a screen shot from Oracle.

Rgds...Geoff

[Steve Lewis]

Its a long shot, but the pattern of reads for an index build may be confusing the read-ahead algorithms in the disk array.

I had a similar situation with my xp12k, which caused a double-read within the array for certain operations, like database backups and index builds. It was solved by a firmware upgrade on the array.

How long does an RMAN database backup, or an SQL select of a whole table take?

I will leave Oracle tuning hints to more worthy responders than myself.

[Volker Borowski]

Hi,

can you give us more detail HOW the migration is performed and how big are the biggest tables involved ?
This is obviously a SAP System, so do you go by Oracle EXP/IMP with sepearated data load and index creation or do you use R3load from SAP ?

SAP Standard Tables have parallel degree set to "noparallel" so a standard procedure to do a Full Table Scan on a non indexed table will go with a single task.

Just configuring PQ processes does not automaticly causes oracle to use them.

You'll need to show the DB that it can use PQ by either altering the tables "parallel_degree" attribute or specify "parallel" in the create INDEX Statements.

If you go by oracle export/import initiated by sapdba or brtools, it is most likely, that the CREATE INDEX Scripts do not contain a "parallel" clause. Depending on sapdba/brtools version you can already specify a parallel degree to be used upon export. If you do not, I think the same values (noparallel) are used for import again.

Do not use a too high level of "parallel" if you run several CREATE INDEX Statements at the same time.

With 8 cpu you should be able to use 6-10 PQ processes in addition to (I assume) 2 configured DBWR (?) for the migration.

How are the 16G RAM configured for oracle ?

Volker