Managing Online Transaction Processing Oracle Database (OLTP)

In an OLTP system, several factors may effect the performance of the oracle database. The administrator should configure database parameters taking into account the numbers of synchronous users logging into the system at time and the type of processes they carry out. The administrator should also have knowledge about the way in which the application manages and create session during logging into the system so that the desired level of parameters can be set. The most important parameters to be taken into account in OLTP system are as below:

processes : maximum operating system user processes that can connect to database.
sessions : This parameter is derived as (1.1 *processes)+5. If the process logged into the database create session out
of it then the derived session using above formula may not be enough for OLTP system. If this is the case, one should increase this parameter as needed.
transactions: This parameter is derived as (1.1 *sessions). It is the maximum number of current transaction that can occur. The greater is this parameter more SGA is allocated and more undo data segment needed.
open_cursors :specifies the maximum number of DML statements a session can have.
parallel_max_servers : specifies the maximum no of parallel execution processes and parallel recovery processes for an instance.

Monitor v$resource_limit view and compare the current utilization, max utilization, initial allocation and limit value for the parameters and modify the parameters as needed.

Using Sqlloader load data from excel to oracle database

Sql Loader is the oracle utility to load data into oracle database from external source datafile specified in specific format. The different types of files comes into being during the data load.

Parameter file : specifies the parameters neccesary to invoke sql loader utility. This may contain the oracle user id and password needs to connect to database, placement of the control file need to parse data and placement of the log files
during the load. Different options may be specified in this file during load such as rows of data after which to commit, disable the archiving option , parallel load option, conventional and direct path load etc.

Control file : specifies the format of the data to be loaded and conditions necessary to load data during the load. The data can be replaced,appended, inserted, truncated in the table. Sql loader does not create table and load into the table. INFILE specifies the source data file whereas DISCARDFILE specifies logs of the rejected rows during the load.The method of field segregation is specified by keyword FIELD TERMINATED BY. Source data may be included in the control file itself using begindata option.

Data file : name of the source data file that contains the data to load as format specified in the control file.

Log file : name of log file to log the result of log during the load.

Discard file : name of file to log the rejected rows during the load.Data can be loaded into the database in two ways:

Direct : This option in parameter file load the data from memory directly into the oracle blocks without generation of the redologs. Direct path loads cannot be used in clustered tables.

Conventional : uses sql insert statement to load data. Generation of redo log, archive files, execution of triggers are the characteristic of conventional path load.

Below is the sample of sql load from the excel file saved in csv format:

Parameter file contents:

userid=username/password
control=c:\myData.ctl
errors=10
log=c:\myData.log
load=1000

Control file contents:

LOAD DATA
INFILE 'c:\myData.csv'
DISCARDFILE 'c:\myData.dsc'
append --REPLACE , APPEND, TRUNCATE
INTO TABLE myData
-- PARTITION (Partition_table_name)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(v_name, v_acct)

Sql loader can be invoked from command prompt as:

sqlldr parfile=c:\myData.par

Check the logfiles during the load