I’ve done a test of just the XMLTYPE column and I’m still testing, but,
regardless of the type of import done (constraints/indexes = y or
constraints/indexes=N) it still takes about the same time.
I’m redoing my database structure with respect to redo log size to see if it
helps speed it up, as well as options to just not import that table.
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Tuesday, December 29, 2015 8:13 PM
To: andrew.kerber@xxxxxxxxx; Storey, Robert (DCSO)
Cc: 'Raza Siddiqui'; 'Oracle L'
Subject: RE: Multiple imports simultaneous?
Two things come to mind:
1) Do a test of schema c’s table that has an XMLTYPE column as a single
table export and import.
2) Are you planning to use different TEMP tablespaces on different storage
for each schema on the index building and constraints import? If you’ve got
some SSD even temporarily available for that it might be useful. If you put
only TEMP on SSDs for the build it doesn’t even need to be mirrored, since a
fail just means do it over.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Andrew Kerber
Sent: Tuesday, December 29, 2015 4:53 PM
To: Storey, Robert (DCSO)
Cc: Raza Siddiqui; Oracle L
Subject: Re: Multiple imports simultaneous?
The method you describe seems overly complex. Why not run 3 different exports
simultaneously and then 3 different imports simultaneously? I once wrote a
something like that using 6 streams for the export and 6 for the import, and it
worked very well. It took some thought to break everything up logically without
dependence issues, but once done (it was a process we had to repeat
frequently), I just used a shell script to nohup the export processes, and it
worked very smoothly.
On Tue, Dec 29, 2015 at 3:45 PM, Storey, Robert (DCSO)
<RStorey@xxxxxxxxxxxxxxxxxx<mailto:RStorey@xxxxxxxxxxxxxxxxxx>> wrote:
In this case it would be part of a LUN in a SAN. But, the new hardware I am
going to migrate it to (when it gets here) will have 12 disks. I will create 6
mirror pairs, and then stripe across the pairs. 2 separate disks will hold my
redo. So, data and indexes will go onto the raid10 where logical volumes will
be created just for ease of structure (windows environ).
And my bottleneck has been the table in schema c that has the XMLTYPE as a
column. That one table seems to take 12 hours, regardless of how I approach
it. I’m still working on speeding it up, or eliminating it from the import.
From: Raza Siddiqui
[mailto:raza.siddiqui@xxxxxxxxxx<mailto:raza.siddiqui@xxxxxxxxxx>]
Sent: Tuesday, December 29, 2015 3:42 PM
To: Storey, Robert (DCSO)
Cc: Oracle L
Subject: Re: Multiple imports simultaneous?
Overall # of rows is not huge (in today's terms), or schemas or objects...what
is more important is mapping of your tablespaces to their corresponding files
to physical disks - that would be your possible bottleneck.
Raza
On Dec 29, 2015, at 13:28, "Storey, Robert (DCSO)"
<RStorey@xxxxxxxxxxxxxxxxxx<mailto:RStorey@xxxxxxxxxxxxxxxxxx>> wrote:
Just curious about import.
I’m importing an older 9i system into an 11g system. I am precreating the
tablespaces, etc.
Ultimately, the import only cares about 3 schemas. One that only has my PL/SQL
and the other two have the data. So, Schema A, B, C. Schema B has about 360
tables and about 190 million rows total. Schema D has about 45 tables, but and
about 35 million rows of which 27 are in one table that has an XMLTYPE column.
Importing just the one table in Schema B takes about 12 hours. I’m working on
methods to trim that time.
But, is it possible to do multiple imports at once if using different inbound
schemas.
1) Export the database to create my dump file.
2) ON the target server, make 3 copies of the import file.
a. Do an import of Schema A, rows=n, indexes=n, constraints=n
b. Do an import of Schema B, rows=n, indexes=n, constraints=n
c. Do an import of Schema C, rows=n, indexes=n, constraints=n
3) Do an import for each schema where rows =n, and indexes and constraints
= y.
Theoretically, this should not interfere with each other. I can set the
database to no-archive and increase redo logs so that the waits should be
reduced.
Thoughts?
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
