Imp very very slow locally on supersized server Options

[cloporte]

Hello
I'm getting confused i have a nightly import ( about 20 tables ) and it takes up to 5 hours
we have one table of about 800,000 lines and the rest are between 1000 and 200,000
this is very slow when i monitor the import i see a very long amount of wait for the SQLnet from client ,

However this is strange because i run the import on the Database server itself .
if i check the current statement i see it's moving from one to one
for instance i have
SELECT /*+ all_rows ordered */
"A".ROWID, 'REPORT', 'CONTRACT_LVL', 'SYS_C001329497'
FROM "REPORT"."CONTRACT_LVL" "A"
WHERE NOT (LENGTH (bonus_nat) <= 31)

then
SELECT /*+ all_rows ordered */
"A".ROWID, 'REPORT', 'CONTRACT_LVL', 'SYS_C001684584'
FROM "REPORT"."CONTRACT_LVL" "A"
WHERE NOT (LENGTH (outcome_cd) <= 1)

etc and it takes hours

DB is on windows 2003 runnin oracle RDBMS 9.2.0.7

while the import screen show 185000 lines imported .
I also see a lot of consistent gets for this sessions raising at that time .

Would it be better to export import without statistics ?

I need also to mention that the dump file comes from a linux hosted Database don't think it will make the difference for a exp/imp
It's a peoplesoft Database there are a lot of tables more than 15000 and if i take the table mentioned above and i want to check its constraints it takes decade before toad can display them .
I have seen that we have a incredible amount of constraints on those tables it might be the reason .

I just wonder if the system catalog needs to be tuned ?
/* Update */

Don't ask me why but now the huge number of wait is no as "Library cache lock" ...
Kind regards

[burleson]

Hi,

>> when i monitor the import i see a very long amount of wait for the SQLnet from client ,


Good clue!

*******************************

>> this is strange because i run the import on the Database server itself .

>> DB is on windows 2003 runnin

Not really. You are running a a personal computer, so disk contention is inevitable.

They are probably both on the same PC drive, yes?

This is an INSERT statement or a data pump import?

Which are you using, SQL inserts or data pump impdp?

**********************************

>> I have seen that we have a incredible amount of constraints on those tables it might be the reason

Yes, It has FAR FASTER to disable constraints before doing a mass insert.

Read this carefully:

http://www.dba-oracle.com/t_insert_tuning.htm

[cloporte]

Hello

Nono everything is running on the server !

I use the standard Imp not the datapump since it's an oracle 9i .

i have used the constraint=n flag and it is much faster , but will it have any impact ? because anyway i don't recreate the tables i jusy truncate them
and use the ignore=y flag .
Not sure what is the constraint=n since the constraints are defined at the table level , so since i don't recreate the tables they are never dropped .
Thank you

[burleson]

>> Not sure what is the constraint=n since the constraints are defined at the table level , so since i don't recreate the tables they are never dropped .

If the tables & constraints exist already, then using constraints=n will not have any effect.

You will need to manually disable the constraints before running the import . . .

See here for complete details:

http://www.rampant-books.com/book_0801_oracle_utilities.htm

[cloporte]

I could execute the same dmp file 100 times faster by just importing to a new schema but in the same database , that's the proof

it's linked to the other schema ( PS user ) there are way to much objects in the peoplesoft schema but i wonder if i would be able to optimize the system dictionary speed access

for that schema as a simple example if you want to have the constraint definition in toad for the peoplesoft schema and the same table as the new schema i just imported it in

it takes decade while on the new schema it's a couple of seconds so imagine toad just does a bunch of select in the system views ...

[burleson]

Hi,

>> I could execute the same dmp file 100 times faster by just importing to a new schema but in the same database , that's the proof

Proof is the wrong word, it is "evidence"!

For some reason, importing into a pre-created tables takes much longer.

That is because a pre-created already has constraints and indexes enforced.

When you load into an empy schema, the indexes & constraints are built after the rows are added.
****************************

>> it takes decade while on the new schema it's a couple of seconds so imagine toad just does a bunch of select in the system views ...

Why Guess?

Get a STATSPACK or AWR report during the process and you can see exactly why it is slow!

http://www.dba-oracle.com/t_taking_statspack_awr_report.htm

[cloporte]

Read below

[cloporte]

OK i have it now

the problem was that i was truncating the table therefore each import /insert

the system will try to validate each line inserted since we have more than 250 constraints on some tables it was a real nightmare

i have changed my script and instead of truncating the tables i just drop them .

It's much faster

thank you .