Home » RDBMS Server » Server Utilities » Probl improving conventional path load, direct=false, with number of commits and bindsize/readsize (Windows 7 Pro, x64bit)
Probl improving conventional path load, direct=false, with number of commits and bindsize/readsize [message #500788] Wed, 23 March 2011 09:20 Go to next message
ilharco
Messages: 8
Registered: March 2011
Location: Portugal
Junior Member
Hi guys,

I'm currently doing an academic benchmark, TPC-H, and I have some big tables that I want to load using direct path (which workde great and was very fast) and conventional path.

For that, I used this batch script:

for /F "tokens=1,2" %%A in (table4_records.txt) do (
sqlldr userid='tpch/tpch' control=%%A.ctl rows=%%B bindsize=? readsize=? SILENT=HEADER log=bulkload_logs\sf4\bulk_%%A%1.log
)


The problem is that, no matter what values I give to the bindsize and/or readsize options, it always commit from 65534 to 65534 rows. I already pass the %%B size which is the exactly number of rows per table.

In direct load, I just used the rows and the commit as REALLY done after the hole table was loaded.

I want to do something like that, but with conventional load path - I know that is not faster, but that's the point.

Could you please tell me how can I give the correct parameters so I can:
1- load as much data, at a time, as I can;
2- commit less frequently, preferably at the end of the table's load.

Here are the tables' names and number of rows:

lineitem 23996604 -> is the biggest and has aprox. 3GB on disk
orders 6000000
partsupp 3200000
part 800000
customer 600000
supplier 40000
nation 25
region 5


Re: Probl improving conventional path load, direct=false, with number of commits and bindsize/readsi [message #500809 is a reply to message #500788] Wed, 23 March 2011 12:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quoting docs
Quote:
Conventional path loads only: The ROWS parameter specifies the number of rows in the bind array. The maximum number of rows is 65534


http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/ldr_params.htm#i1004682
Re: Probl improving conventional path load, direct=false, with number of commits and bindsize/readsi [message #500815 is a reply to message #500809] Wed, 23 March 2011 13:43 Go to previous message
ilharco
Messages: 8
Registered: March 2011
Location: Portugal
Junior Member
Thank you.

I really read a lot of sites, but that detail escaped me.
Previous Topic: sql loader
Next Topic: SQLLOADER: views linked to External tables
Goto Forum:
  


Current Time: Sun Nov 29 04:31:40 CST 2020