Troubleshooting
Problem
Upgrade : Oracle DB migration from 8i to 9i by export and import route
Symptom
Taking around 10+ hours with huge data volume from current Production DB on 8i
to Oracle9i
PART: Distributed Order Management 5.0 SP1
Platform
PRODUCT: <None>
COMPONENT: <None>
OS: All - All
DATABASE:
Oracle - 9.2.0.1
WEB SERVER: All - All
WEB BROWSER: Internet Explorer -
6.0
Cause
Resolving The Problem
EMail In from QA Perf ENG group :
How to improve the speed of the IMPORT,
here are some suggestions to try:
* Try importing in the data without the
indices. Create the indices in later with NOLOGGING.
* Also, make sure
PGA_AGGREGATE_TARGET is set high - e.g., 128M. This could help the sort
process.
* check to see if the TEMP tablespace is large and is implemented
as a true "temporary tempfile" - see PMG ( perf Mgmt guide ).
* Make sure
TEMP file is not on RAID 5 but rather on RAID-10.
* Use a higher BUFFER
(e.g., 64000000) and COMMIT=Y
* check to see how the disk subsystem is
implemented - e.g.,
* is it RAID, if it is, what RAID level
*
how large are the RAID LUNs - how many disks in a stripe, stripe width, etc
* Take STATSPACK snapshots every 15-30 minutes. Find out where Oracle
is spending its time.
* What does STATSPACK say during the IMPORT -
specifically, where are the waits
* if the waits are in the redo logs,
consider moving the redo logs to raw devices
* if the waits are in DBWR
waits (writing), check to see if the disk subsystem is efficient (e.g., is it
RAID-5 (which could be very bad for large IMPORTs))
* Consider running
2 or 3 parallel IMPORTs - take a look at the EXPORT log and see which table
took the longest or was the largest.
* Consider IMPORTing the large tables
in parallel.
* consider EXPORT with DIRECT=Y - this approach makes EXPORT
very fast because Oracle bypasses SQL processing. However, you can only use
DIRECT=Y for tables that don't have LONG columns.
Therefore, you will
have to have a separate EXPORT with DIRECT=N run for the tables with LONG
columns. Fortunately, we don't have that many tables with LONG columns. Please
be warned that we ran into issues in the past with DIRECT=Y especially for LONG
columns. Make sure to test out this approach fully.
Historical Number
PRI49131
Product Synonym
[<p><b>]Fact[</b><p>];
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21532892