Multithreading not working with direct path load [message #585850] |
Thu, 30 May 2013 06:39 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Output in the sqlldr log:-
------------------------------------------------------------------------------
Path used: Direct
Insert option in effect for this table: APPEND
Trigger DEV."R_TM_BK_BORROWER" was disabled before the load.
DEV."R_TM_BK_BORROWER" was re-enabled.
The following index(es) on table "YO"."TM_BK_BORROWER" were processed:
index DEV.I_NK_TM_BK_BORR_1 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_2 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_3 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_31 loaded successfully with 1554238 keys
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 1554241
Total logical records rejected: 48
Total logical records discarded: 2
Total stream buffers loaded by SQL*Loader main thread: 7695
Total stream buffers loaded by SQL*Loader load thread: 0
------------------------------------------------------------------------------
So, I still see in the sqlldr log that the stream buffers are laoded by main thread and load thread is still not being used.
SQL*Loader load thread did not offload the SQL*Loader main thread. If the load thread takes care of the current stream buffers, then it allows the main thread to build the next stream buffer while the load thread loads the current stream on the server. We have a 24 CPU server.
I am using the following parameters set to true in the sqlldr:-
parallel=true , multithreading=true , skip_index_maintenance=true in the sqlldr
|
|
|
Re: Multithreading not working with direct path load [message #587441 is a reply to message #585850] |
Fri, 14 June 2013 15:34 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Formatted with tags as and where required:-
Oracle DB version : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
I am using direct path load to load data from a flat file into a table using SQL*Loader. I have also kept it as parallel. However, I do not see multithreading being used at all, based on the log file report.
I am using the following parameters set to true in the sqlldr:-
parallel=true , multithreading=true , skip_index_maintenance=true
Output in the sqlldr log:-
Path used: Direct
Insert option in effect for this table: APPEND
Trigger DEV."R_TM_BK_BORROWER" was disabled before the load.
DEV."R_TM_BK_BORROWER" was re-enabled.
The following index(es) on table "YO"."TM_BK_BORROWER" were processed:
index DEV.I_NK_TM_BK_BORR_1 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_2 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_3 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_31 loaded successfully with 1554238 keys
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 1554241
Total logical records rejected: 48
Total logical records discarded: 2
Total stream buffers loaded by SQL*Loader main thread: 7695
Total stream buffers loaded by SQL*Loader load thread: 0
So, I still see in the sqlldr log that the stream buffers are loaded by main thread and load thread is still not being used.
SQL*Loader load thread did not offload the SQL*Loader main thread. If the load thread takes care of the current stream buffers, then it allows the main thread to build the next stream buffer while the load thread loads the current stream on the server. We have a 24 CPU server.
I am not able to find any clue over google too. Any help is appreciated.
|
|
|
|
|
|
|