Cluster insert single row taking very long time
Date: Tue, 10 Jun 2014 11:17:27 +1000
Message-ID: <CAFeFPA9Csr2Lu-5u4_V18A=2v56LPXEmiq1QU6i2WpcNw_zNzQ_at_mail.gmail.com>
Hi All,
We are still trying to get our database of tru64 to AIX and have hit
several bugs in the process.
Source: Oracle 9i on tru64
Target: Oracle 10.2.0.5 on AIX 6.1 (higher oracle not supported by legacy
app)
We have used the following method
Upgrade to 10G on tru64
Rman convert the tablespaces across (TTS)
This buggers up the Cluster index (bug) and since the majority of the database (25TB) is clustered tables this was pretty significant showstopper.
We than decided to export the data recreate the cluster and import the data back to fix up the pointer issues.
This worked and several rounds of testing for end to end process have passed until a few days prior to go live cut-over weekend. We hit a roadblock again.
All of a sudden we get into the situation where a single new row insert (cluster key does not yet exist) into any of the cluster tables could take upto 2 hours to complete and the majority of time is spend on db file sequential reads (scanning looking for a block to place new record). This behavior is unacceptable and also not consistent. We understand that inserts into a cluster table are always going to be a bit more expensive than inserts into normal table, but this is taking it a bit too far. We have logged a case with oracle and it is currently with development but I am hoping that someone has already seen something similar and has any handy hints or tips.
Jack van Zanen
This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 10 2014 - 03:17:27 CEST