Improve the impdp speed

All posts relating to Oracle database administration.

Moderator: Tim...

Improve the impdp speed

Postby royalyogi » Tue Oct 01, 2013 1:01 pm

my impport is running very slow. Could you please let me know how can we increase speed up of import

OS: Windows Server 2008 R2.
Oracle Version: 11.2.0.2
royalyogi
Senior Member
 
Posts: 166
Joined: Tue Apr 23, 2013 3:59 pm

Re: Improve the impdp speed

Postby Tim... » Tue Oct 01, 2013 1:28 pm

Hi.

First things first. Do you know why it is running slow? have you traced it?

The reason I say this is, if you don't know why it is slow, how can you decide how to improve the performance? For example, let's say I suggest running the import in parallel, but the machine is already maxed out on storage throughput. You will actually make it slower because multiple slaves will be fighting over the limited resources...

Whenever you have a performance problem, your first thought should be quantifying what is causing the performance problem. Only then can you decide the best way to fix it.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Improve the impdp speed

Postby royalyogi » Tue Oct 01, 2013 2:01 pm

Thanks Tim for this update.
Our database is running on windows machine and I have checked the CPU,Memory, server performance is look good.
application team has also stop the application services.

only 10gb data has been imported in one hour using impdp and morever we didn't find any locking,blocking sessions on database.

could you please suggest on this?
royalyogi
Senior Member
 
Posts: 166
Joined: Tue Apr 23, 2013 3:59 pm

Re: Improve the impdp speed

Postby Tim... » Tue Oct 01, 2013 2:17 pm

Hi.

Trace it. See what it is waiting on. :)

Can't know what to suggest until you know what is causing the problem!

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Improve the impdp speed

Postby royalyogi » Sat Oct 05, 2013 3:52 am

Special thanks you Tim.
We have taken export expdp backup from prod database (primary database- Data Guard).
1.) Import impdp is very slow 10GB/Hrs on staging database (Data Guard – MAXIMUM AVAILBILITY)
Since Server configuration, database version and configuration, operating system everything are same as production. No blocking, locking or waiting sessions

2.)import impdp is fast 90GB/Hrs on Test standalone database and this test database is running in NOARCHIVE LOG mode with oracle standard version after that no more difference.

Note: CPU,Memory,network and disk I/O are look normal while importing on both databases.

I am beating my head but not getting anything why that much difference on import.
royalyogi
Senior Member
 
Posts: 166
Joined: Tue Apr 23, 2013 3:59 pm

Re: Improve the impdp speed

Postby Tim... » Sat Oct 05, 2013 10:19 am

Hi.

You are beating your head because you are not doing what I suggested. Trace the session running the export and see what it is actually waiting on. Once you know that, it may be obvious what is going on. Without doing that you mights as well blame it on the magic export pixies!

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Improve the impdp speed

Postby royalyogi » Sun Oct 06, 2013 4:35 am

thanks I will enable the trace parameter and will check the issue
royalyogi
Senior Member
 
Posts: 166
Joined: Tue Apr 23, 2013 3:59 pm

Re: Improve the impdp speed

Postby Tim... » Sun Oct 06, 2013 9:15 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 3 guests

cron