BINDSIZE, ROWS and READSIZE

BINDSIZE, ROWS and READSIZE parameters affects the performance of rows

Try to keep grip on the frequency of the commit. Commits records frequency  is high then performance become poor as commits perform in small interval of time resources will occupied to write on the data file. Same if commits perform in large interval of time it degrades performance again .redo generates in conventional path load will become large .Our objective is specifies the parameters with proper values.

Some time it’s difficult task to decide what value have to put for the BINDSIZE ,READSIZE, and ROWS .how SQL Loader behaves with these parameters. There is practical example to understands all above facts

Have table in which have to load data

COMP_ID     VARCHAR2(20)

 COMP_NAME   VARCHAR2(100)

 COMP_ADD    VARCHAR2(200)

 CITY     VARCHAR2(30)

 PHONE_NO   VARCHAR2(30)

·         BINDSIZE specifies the size of the bind array n bytes.

·         READSIZE is use at the time of the read data .

·         The maximum  value for BINDSIZE is 20971520.

Now I have to find how SQL loader commits if we didn’t mansion the

Rows 

specified value for readsize(20000000) less than bindsize(20000000)

Commit point reached - logical record count 64

Commit point reached - logical record count 128

It commits at every 64 rows there is no effect of the bindsize and readsize if we use default value of rows.

·         Now I try to find the what will happen if rows define. I specifies the value of rows=200000 bindsize=20000000  readsize=20000000

Commit point reached - logical record count 65936

Commit point reached - logical record count 131872

It overwrite the parameter rows=200000 because row limit in bytes (maximum row length * rows) exceeds the limit of the bindsize, in that case oracle decide the rows commit values depending on the bindsize.

There is one more example now we decrease the value of the bindsize but still same rows as above example now bindsize=10000000  readsize=10000000

Commit point reached - logical record count 7678

Commit point reached - logical record count 15358

You can check log file  to more details

Space allocated for bind array:                9999230 bytes(7633 rows)

Read   buffer bytes:10000000

This time the commit value is decrease.Don’t worry rows also play a role in commit let’s check in next example

Specifies the parameters  bindsize=20000000 readsize=20000000  rows=200

Commit point reached - logical record count 200

Commit point reached - logical record count 400

Space allocated for bind array:                 262000 bytes(200 rows)

Read   buffer bytes:20000000

·         If we only specifies the rows parameter then , if the number of rows in the limit of default bindsize (256000) then commits take place on the number of rows given otherwise it commits calculating the number of rows depend upon the default bindsize.

sqlldr scott/tiger control=multiplefile.ctl log=multiplefile.log  rows=3000

Commit point reached - logical record count 195

sqlldr scott/tiger control=multiplefile.ctl log=multiplefile.log  rows=30

Commit point reached - logical record count 30

·         The point to think what’s the best way to parameterized the

these parameters. I try consolidate by parameterized with values

And find some results

My system is in ideal condition .no other process is running

Now  I am going go load a 46M file to a table having five columns.

Here table truncated first taking time 15-18 sec and the load data

[oracle@mediamath-server sqllder]$  sqlldr scott/tiger control=multiplefile.ctl log=multiplefile.log bindsize=20000000  readsize=20000000

Commit point reached - logical record count 64

1:49

sqlldr scott/tiger control=multiplefile.ctl log=multiplefile.log bindsize=20000000 readsize=20000000  rows=200000

Commit point reached - logical record count 15359

1:25

sqlldr scott/tiger control=multiplefile.ctl log=multiplefile.log bindsize=10000000 readsize=10000000  rows=5000

Commit point reached - logical record count 5000

1:05

From above example we can conclude that if we commits records frequency  is high then (every 64 rows) loading data taking more time performance is poor . same with we commit rows at large interval is also degrade performance hence always put the rows commit proper frequency .

One more thing always try to specific rows parameters otherwise all are depends on bindsize. If you have no idea about maximum row length and you only specifies the bind size it can be increase your difficulties

And as in above example if you use all default parameters (not specifies any one of them) then performance is in most poor stage.