OraFAQ Forum: Server Utilities » Import takes long time

Home » RDBMS Server » Server Utilities » Import takes long time

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Import takes long time [message #414899]

Thu, 23 July 2009 23:58

gajini
Messages: 262
Registered: January 2006

Senior Member

Hi All,

Once in two days We usually refresh development schemas with Production data and it usually completes in
6 hours but the last 2 days it is going for more than 12 hrs and the normal table import which is not having CLOB/BLOB
columns itself taking very long time. What could be the reason for this problem? How to find the root cause of this problem
and resolve this issue? Please guide me in troubleshooting this problem. My oracle version is 10.2.0.3.0 and server is HP-UX.

Thanks..

Report message to a moderator

Re: Import takes long time [message #414909 is a reply to message #414899]

Fri, 24 July 2009 00:24

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Increase buffer size.
Catch io statistics at OS level.
...

Regards
Michel

Report message to a moderator

Re: Import takes long time [message #414920 is a reply to message #414899]

Fri, 24 July 2009 01:01

gajini
Messages: 262
Registered: January 2006

Senior Member

Hi Michel,

The buffer is always remains the same, please IO statistics that I've taken from V$SQL,

Quote:

FIRST_LOAD_TIME- 2009-07-23/11:15:55
DISK_READS-359930
DIRECT_WRITES-0
BUFFER_GETS- 177233492
APPLICATION_WAIT_TIME-0
CONCURRENCY_WAIT_TIME-49708
CLUSTER_WAIT_TIME-0
USER_IO_WAIT_TIME-2358783413
PLSQL_EXEC_TIME-0
JAVA_EXEC_TIME-0
ROWS_PROCESSED-7370496
COMMAND_TYPE-2
CPU_TIME-3797651939
ELAPSED_TIME-40037981522
LAST_LOAD_TIME-2009-07-23/11:15:55
LAST_ACTIVE_TIME-23-Jul-2009 22:46:43

Based on the above statisitics, please guide me how to troubleshoot this problem at the database level and let me know if it is IO wait problem not database problem and whether Server admin have to take care of this.

Thanks...

Report message to a moderator

Re: Import takes long time [message #414930 is a reply to message #414920]

Fri, 24 July 2009 01:32

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

io statistics at OS level.

Quote:

The buffer is always remains the same

But does the memory always the same?
Catch vm statistics at OS LEVEL.

Regards
Michel

[Updated on: Fri, 24 July 2009 01:34]

Report message to a moderator

Re: Import takes long time [message #414931 is a reply to message #414899]

Fri, 24 July 2009 01:37

gajini
Messages: 262
Registered: January 2006

Senior Member

Hi Michel,

Please find the sar statistics,

$ sar 2 5

HP-UX thdb1d02 B.11.23 U 9000/800    07/23/09

23:35:44    %usr    %sys    %wio   %idle
23:35:46      43      28      27       1
23:35:48      40      27      32       1
23:35:50      45      30      22       3
23:35:52      37      30      33       0
23:35:54      31      25      41       2

Average       39      28      31       2

If you need some more statistics, please give me the command I'll ask Server admin to execute and give you the output for the analysis.

Thanks...

[EDITED by LF: applied [pre] tags instead of [font] and [quote] ones]

[Updated on: Fri, 24 July 2009 01:41] by Moderator

Report message to a moderator

Re: Import takes long time [message #414942 is a reply to message #414931]

Fri, 24 July 2009 02:02

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

These are too high level statistics.
You need statistics on the disks you access DURING THE PROBLEM.

Regards
Michel

Report message to a moderator

Re: Import takes long time [message #414957 is a reply to message #414899]

Fri, 24 July 2009 02:37

gajini
Messages: 262
Registered: January 2006

Senior Member

Hi Michel,

Please find vmstat statistics in the attached file.

Thanks...

Attachment: vmstat.txt
(Size: 0.73KB, Downloaded 1224 times)

Report message to a moderator

Previous Topic:	Export from ms access 2007 to oracle
Next Topic:	Gather Statistics

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 08:49:30 CDT 2024