46B Oracle Database Import (Low Performance)

[Former Member]

Dear All,

I have exported a database from AIX and import to Windows 2003.

Our export file is just 37.4 GB but we need to wait more than 18 hours.

SAP Version: 46B

Oracle Version: 10.2.0.4.0

1. Is it normal or not?

2. Is that any solution to improve the performance issue? (oracle turning? export or import parameter changing?)

3. Can i use the Migration Monitor in this case? It will be faster when we use it? What we should be more focus on?

-

---

Some DBMIG Log----

---

INFO 2011-03-17 04:27:47 DBR3LOADEXEC_NT_ORA SyCoprocessCreate:931

Creating coprocess R3load ...

INFO 2011-03-17 04:27:47 DBR3LOADEXEC_NT_ORA R3loadPrepare:0

Total number of processes: 97

0 process(es) finished successfully:

0 process(es) finished with errors:

4 process(es) still running: 0 1 2 3

INFO 2011-03-17 04:27:47 DBR3LOADEXEC_NT_ORA SyCoprocessCreate:931

Creating coprocess R3load ...

INFO 2011-03-17 04:27:47 DBR3LOADEXEC_NT_ORA R3loadPrepare:0

Total number of processes: 97

0 process(es) finished successfully:

0 process(es) finished with errors:

5 process(es) still running: 0 1 2 3 4

-

---

Some DBMIG Log----

---

INFO 2011-03-17 06:03:42 DBR3LOADEXEC_NT_ORA R3loadPrepare:0

Total number of processes: 97

59 process(es) finished successfully: 0 1 3 5 6 7 8 12 13 17 18

19 20 21 26 28 29 30 32 33 35 38 39 40 41 42 43 44 45 46 47 48 49

52 54 55 56 57 58 61 62 63 66 67 68 69 70 75 76 77 78 84 88 89 91

92 93 94 95

0 process(es) finished with errors:

38 process(es) still running: 2 4 9 10 11 14 15 16 22 23 24 25 27

31 34 36 37 50 51 53 59 60 64 65 71 72 73 74 79 80 81 82 83 85 86

87 90 96

-

---

Some DBMIG Log----

---

INFO 2011-03-17 10:30:12 DBR3LOADEXEC_NT_ORA R3loadPrepare:0

Total number of processes: 97

95 process(es) finished successfully: 0 1 2 3 4 5 6 7 8 9 11 12

13 14 15 16 17 18 19 20 21 22 23 24 25 26 28 29 30 31 32 33 34 35

36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96

0 process(es) finished with errors:

2 process(es) still running: 10 27

INFO 2011-03-17 13:30:24 DBR3LOADEXEC_NT_ORA R3loadPrepare:0

Total number of processes: 97

96 process(es) finished successfully: 0 1 2 3 4 5 6 7 8 9 11 12

13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78

79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96

0 process(es) finished with errors:

1 process(es) still running: 10

4. We need to spend more than 6 hours to process the last part. Is it normal or not?

Thank you for your kindly help and patience.

Edited by: HarryLaiHarry on Mar 17, 2011 3:31 PM

[Former Member · Accepted solution]

Hi,

Did you split the pacakges before exporting ?

Did you use the -loadprocedure fast method ?

What are your Oracle parameters for your database.

Mark

[Former Member · Accepted solution]

Thank you for your reply.

1. I have split the top 80 lager table.

2. I have not use -loadprocedure fast method, how to use it?

There are the following Oracle parameters.

1. parameter for Oracle 10.2.x

OPTIMPEEK_USER_BINDS=FALSE

background_dump_dest = D:\oracle\SW1\saptrace\background

compatible = 10.2.0

control_file_record_keep_time=30

core_dump_dest = D:\oracle\SW1\saptrace\background

db_block_size=8192

db_files=254

db_name = SW1

dml_locks=4000

event = "10191 trace name context forever, level 1"

FILESYSTEMIO_OPTIONS = setall

job_queue_processes = 1

log_archive_dest = D:\oracle\SW1\saparch\SW1arch

log_buffer=52428800

log_checkpoint_interval=0

log_checkpoints_to_alert=true

open_cursors=800

optimizer_features_enable = 10.2.0.1

processes=500

recyclebin = off

remote_login_passwordfile='exclusive'

remote_os_authent=true

sessions=600

shared_pool_reserved_size=63905464

sort_area_retained_size=0

sort_area_size=2097152

statistics_level = typical

undo_retention = 43200

undo_tablespace = PSAPUNDO

undo_management = AUTO

user_dump_dest = D:\oracle\SW1\saptrace\usertrace

workarea_size_policy = AUTO

shared_pool_size = 408021893

shared_pool_reserved_size = 40802189

pga_aggregate_target = 408021893

sga_max_size = 612032839

db_cache_size = 306016419

control_files = ( D:\oracle\SW1\sapdata1\cntrl\cntrlSW1.dbf,D:\oracle\SW1\sapdata2\cntrl\cntrlSW1.dbf,D:\oracle\SW1\sapdata3\cntrl\cntrlSW1.dbf )

#MAX_ROLLBACK_SEGMENTS = 400

[markus_doehr2 · Accepted solution]

> Did you use the -loadprocedure fast method ?

-loadprocedure fast is not available for kernels < 6.40

Note 1045847 - ORACLE DIRECT PATH LOAD SUPPORT IN R3LOAD

Markus

[Former Member · Accepted solution]

Hi,

What kernel you on and patch level..

Mark

[Former Member · Accepted solution]

Thanks Markus.. should of checked first..

Mark

[Former Member · Accepted solution]

Your meaning is R3LOAD version?

I have run "r3load - v" in the directory.

The result is :

1. R3load version @(#) $Id: //bas/46D/src/ins/R3load/R3load.c#40 $ SAP

2. Compiled Jul 10 2006 21:57:17

[Former Member · Accepted solution]

Thanks Mark,Markus.

I have use the standard SAP Migration tools not a command mode.

Can i active the -loadprocedure fast method?

Harry

[markus_doehr2 · Accepted solution]

> Can i active the -loadprocedure fast method?

No - that is implemented only with kernel 6.40 and higher (see the note I posted), not with 4.6D.

Markus

[markus_doehr2]

> 4. We need to spend more than 6 hours to process the last part. Is it normal or not?

Whether this is "normal" or not nobody can answer.

What are the packages that take so long? What tables are included and are they big?

Markus

[Former Member]

Thank you for your reply.

SAPAPPL1 is the biggest.(<SAPAPPL1.001 - SAPAPPL1.011> each package 1GB)

We find that the last processor is import this package.

Is not fully utilizing the resources that import a large package by one processor?

Can i parallel import this package or separate some large table into other package?