Home » RDBMS Server » Server Utilities » Import takes long time
Import takes long time [message #414899] Thu, 23 July 2009 23:58 Go to next message
gajini
Messages: 259
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..
Re: Import takes long time [message #414909 is a reply to message #414899] Fri, 24 July 2009 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Increase buffer size.
Catch io statistics at OS level.
...

Regards
Michel
Re: Import takes long time [message #414920 is a reply to message #414899] Fri, 24 July 2009 01:01 Go to previous messageGo to next message
gajini
Messages: 259
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...

Re: Import takes long time [message #414930 is a reply to message #414920] Fri, 24 July 2009 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
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 Go to previous messageGo to next message
gajini
Messages: 259
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
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
Re: Import takes long time [message #414957 is a reply to message #414899] Fri, 24 July 2009 02:37 Go to previous message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi Michel,

Please find vmstat statistics in the attached file.

Thanks...

  • Attachment: vmstat.txt
    (Size: 0.73KB, Downloaded 174 times)
Previous Topic: Export from ms access 2007 to oracle
Next Topic: Gather Statistics
Goto Forum:
  


Current Time: Wed Dec 07 18:54:54 CST 2016

Total time taken to generate the page: 0.11147 seconds