Home » RDBMS Server » Server Utilities » Multithreading not working with direct path load (Oracle, 11gR2, Linux)
icon5.gif  Multithreading not working with direct path load [message #585850] Thu, 30 May 2013 06:39 Go to next message
Lalit Kumar B
Messages: 2362
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2362
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.
Re: Multithreading not working with direct path load [message #587455 is a reply to message #587441] Sat, 15 June 2013 00:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7987
Registered: November 2002
Location: California, USA
Senior Member
I understand your problem, but don't have a solution. I expect everyone else is the same and that's why you are not getting any help. You might try posting on Oracle's Export/Import/SQL Loader & External Tables forum:

https://forums.oracle.com/community/developer/english/oracle_database/export_import_sql_loader_%26_external_tables/content

If you can't find any help there, then you might try asktom.oracle.com or Oracle support.
Re: Multithreading not working with direct path load [message #587466 is a reply to message #587455] Sat, 15 June 2013 06:04 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2362
Registered: May 2013
Location: World Wide on the Web
Senior Member
@Barbara - Thanks.

As of now, due to large backlog, asktom is not open for new questions. So, eagerly waiting for it. Will post it in OTN though. Thanks again for the reply.
Re: Multithreading not working with direct path load [message #590658 is a reply to message #587466] Sun, 21 July 2013 06:43 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2362
Registered: May 2013
Location: World Wide on the Web
Senior Member
Folks, finally Tom Kyte replied to my post. Here is the thread in asktom -

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1612304461350#7035459900346550399

[Updated on: Sun, 21 July 2013 06:44]

Report message to a moderator

Re: Multithreading not working with direct path load [message #590661 is a reply to message #590658] Sun, 21 July 2013 07:57 Go to previous message
Michel Cadot
Messages: 59162
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback and link.

Regards
Michel
Previous Topic: Problems DBLINK
Next Topic: Export table in multiple files
Goto Forum:
  


Current Time: Mon Sep 22 10:10:30 CDT 2014

Total time taken to generate the page: 0.11639 seconds