Performance tuning sql loader .ctl files

For performance tuning for sql loader .ctl files
=================================================


increase the "bind array" for SQL*Loader tool.

There are some more steps we need to follow to get well tuned result.

Initially we need to estimate the RBS requirement (Rollback Segment).
1) determine size of a single row.

2) determine size of a bind array

memory= No.of Rows * rowsoze
bindArraysize=min(memory,bindsize)

3) estimate the size of RBS size required.

rollbacksegment size=1.3*bindarraysize.

When estimating the size of rollback segment size ,I recommend to add 30%
for overhead to the bindarray size.

Since SQL *Loader cant select the RBS for its own, we need to
make offline other rollback segments offline that are small as per the
calculation we have made.

another tips are

1)drop the indexes of the table you are loading data, then recreate
the same after loading.
(please dont drop indexes associated with primary and unique key constraints).

2)Use larger redolog files.

3)fixed width input data file faster than .csv file.

NB: Please perform all these steps only with the help of a DBA.

==============================================================

Entry made to the .ctl file is

OPTIONS (READSIZE=20971520, BINDSIZE=20971520, ROWS=20000)
==============================================================

Count(*) in Oracle Equals to countif() function in MS Excel

The count(*) function in Oracle and countif() function in MS Excel are similar in funcionality.

Oracle, Excel are respective trademark of the respective owners:
Oracle Corporation and Microsoft Corporation.

Every time a database is started, a system global area (SGA) is allocated and Oracle
background processes are started. The combination of the background processes and
memory buffers is called an ORACLE instance.

Database and Instance

The physical database structures of an Oracle database, including data files(grouped into tablespaces) ,redo log files, and control files. An Oracle user(login id) and the objects the user owns (tables ,indexes, stored procedures, triggers, packages, cursors etc) collectively known as a schema, an instance is the combination of background processes and memory buffers. Will get into detail of an Oracle instance in next session.

Rise of Phoenix

myself a telecom engineer by profession with a passion to work on Oracle DBMS products and having hands on Oracle DBA experience for around 8 years, I would like to introduce the basic,intermediate and advanced concepts of Oracle,Telecom,Excel ,SOA and Solution Design,Architect concepts that I have gained through years, By simple language. If you have any Oracle /Telecom related queries , Please feel free to post your doubts here.