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

Database Verify Utilily (dbv) in Oracle Database

The Database verify utility is mechanism to check the block corruption of the oracle datafiles at Operating system level. It validate the blocks specified by the dbv utility parameters and generates output accordingly. dbv utility is generally useful
for verifying and validating online as well as offline datafiles. Generally this procedure is not used for online data files as errors may be induced in the online files during the execution of validating the files and successive checks may be needed. The Database utiliy can be executed as below

dbv file=datafile_name blocksize=datafile_blocksize

It is the duty of Database administrator to check the file corruption at frequent basis. Below is the script that generates the script for the checks required for the datafiles in unix enviornment and executes the same at the end of the generation

#!/bin/ksh
# dbv script in unix enviornement
#
SQLPLUS=${ORACLE_HOME}/bin/sqlplus
wlogfile=dbv.${ORACLE_SID}
echo "connecting to oracle database and generating commands"
$SQLPLUS system/manager >>$wlogfile << EOF
set echo off feedback off verify off pages 0 termout off
set linesize 150
spool dbv.cmd
select 'dbv file=' || name || ' blocksize=' || block_size ||' feedback=' || round(blocks*.10,0) from v\$datafile;
spool off
set feedback on verify on pages24 echo on termout on
EOF
echo "command generated succesfully"
echo "verifying datafiles
ksh dbv.cmd
#End

The verification would end up as below

DBVERIFY - Verification complete

Total Pages Examined : 61440
Total Pages Processed (Data) : 995
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1
Total Pages Empty : 60444
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

Summary of the verification

Total Pages Examined : number of pages examined
Total Pages Processed (Data) : number of pages inspected that contained table data
Total Pages Failing (Data) : number of table blocks that have corruption
Total Pages Processed (Index): number of blocks inspected by dbv that contained index data
Total Pages Failing (Index): number of index blocks that are corrupted
Total Pages Processed (Other): number of blocks processed other than table and indexes that are corrupted
Total Pages Empty : number of unused blocks in the file
Total Pages Marked Corrupt : show the number of corrupt blocks during scan if it is non zero there is error in the datafile
Total Pages Influx : number of blocks rechecked due to the blocks in use