Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Imp very very slow locally on supersized server
cloporte
post Apr 27 2012, 03:52 AM
Post #1


Member
**

Group: Members
Posts: 16
Joined: 27-April 12
Member No.: 47,144



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
Go to the top of the page
 
+Quote Post
burleson
post Apr 27 2012, 06:12 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,434
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
cloporte
post Apr 27 2012, 06:35 AM
Post #3


Member
**

Group: Members
Posts: 16
Joined: 27-April 12
Member No.: 47,144



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
Go to the top of the page
 
+Quote Post
burleson
post Apr 27 2012, 06:46 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,434
Joined: 26-January 04
Member No.: 13



>> 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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
cloporte
post Apr 27 2012, 08:04 AM
Post #5


Member
**

Group: Members
Posts: 16
Joined: 27-April 12
Member No.: 47,144



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 ...
Go to the top of the page
 
+Quote Post
burleson
post Apr 27 2012, 08:08 AM
Post #6


Advanced Member
***

Group: Members
Posts: 11,434
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
cloporte
post Apr 27 2012, 08:16 AM
Post #7


Member
**

Group: Members
Posts: 16
Joined: 27-April 12
Member No.: 47,144



Read below
Go to the top of the page
 
+Quote Post
cloporte
post Apr 27 2012, 11:07 AM
Post #8


Member
**

Group: Members
Posts: 16
Joined: 27-April 12
Member No.: 47,144



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 .
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 2nd September 2014 - 04:13 PM