ppt db25 oracle 05

8/12/2019 Ppt Db25 Oracle 05

1/51

PL/SQL Performance tuningConcurrency & RecoveryData transfer utilitiesOracle Day 5

8/12/2019 Ppt Db25 Oracle 05

2/51

2Copyright 2005, Infosys

Technologies LtdER/CORP/CRS/DB25/003

Version No. 2.0

ObjectivesTo understand PL/SQL performance tuningTo understand few tools like DBMS_TRACE used to tune the PL/SQL code.To understand the locking mechanism and types of locking in Oracle.To understand the read consistency model.To understand the concurrency schemes followed by Oracle.To understand different kinds of database failures and procedure to recoverfrom them.

To understand data transfer utilities

8/12/2019 Ppt Db25 Oracle 05

3/51

PL/SQL Performance tuning

8/12/2019 Ppt Db25 Oracle 05

4/51



Version No. 2.0

Tuning PL/SQL Applications

Usually developer efforts are concentrated on tuning the SQLs used by thePL/SQL program for performance improvement.

Being a procedural language, there can be situations where excessive CPUusage arises out of the code in Pl/SQL even though there are no databaseaccesses.

By tuning the applications, one can make sure they continue to deliver therequired response time and throughput.

8/12/2019 Ppt Db25 Oracle 05

5/51



Version No. 2.0

Reasons for PL/SQL Performance Problems

Badly written SQL statements

Poor programming practices Misuse of shared memory.

8/12/2019 Ppt Db25 Oracle 05

6/51



Version No. 2.0

Solution to Badly written SQL statements

Analyze the execution plans and performance using EXPLAIN PLANstatement.

Rewrite the SQL statements.

For more info on SQL statements, please refer the RDBMS artifacts.

8/12/2019 Ppt Db25 Oracle 05

7/517Copyright 2005, Infosys


Version No. 2.0

Solutions to Poor programming practices

Here different areas of PL/SQL code are categorized and some of the basicprinciples of optimizing are discussed:

DECLARE what is required by the code Hand crafting the Built-in Functions Inefficient Conditional Control Statements Check the LOOP Statements Implicit Datatype Conversions Inappropriate Declarations for Numeric Datatypes Unnecessary NOT NULL Constraints

8/12/2019 Ppt Db25 Oracle 05



Version No. 2.0

DECLARE what is required by the code After the completion of the code, search for un-used variables if any. Remove

them from the code. Defer the execution till it required.

DECLARE

l_chr_var1 VARCHAR2(15) :=takes_five_miunte(..);

.

BEGIN

IF criteria1 THEN

use_the_defined_variable(l_chr_var1);

ELSE

-- say 90% of the cases follows this

--Normal_code;

END IF;

END;

DECLARE

.

BEGIN

IF criteria1 THEN

DECALRE

l_chr_var1 VARCHAR2(15) :=

takes_five_miunte(..);

BEGINuse_the_defined_variable(l_chr_var1);

END;ELSE

--Normal_code;

END IF;

END;

8/12/2019 Ppt Db25 Oracle 05



Version No. 2.0

Hand crafting the Built-in Functions Built-in functions are more efficient.

Do not hand code ones own versions of built -in functions such as REPLACE,TRANSLATE, SUBSTR, INSTR, RPAD, and LTRIM. .

8/12/2019 Ppt Db25 Oracle 05

10/51

8/12/2019 Ppt Db25 Oracle 05



Version No. 2.0

Check the LOOP statements Minimize the number of iterations inside loop. As soon as the code does the

required job EXIT the loop.

Loop within a loop One common place where there is possibility ofunnecessary code execution.

Make sure that there should not be statements inside the loop that can beexecuted outside the loop.

8/12/2019 Ppt Db25 Oracle 05



Version No. 2.0

Implicit Datatype Conversions Avoiding implicit conversions can improve performance.

DECLARE

vNum NUMBER;

BEGIN

vNum:=vNum+15; -- converted

vNum:=vNum+15.0; -- not converted

...

END;

DECLARE

vChar CHAR(5);

BEGIN

vChar := 25; -- converted

vChar := '25'; -- not converted

...

END;

8/12/2019 Ppt Db25 Oracle 05



Version No. 2.0

Inappropriate Declarations for Numeric Datatypes

When one need to declare an integer variable, use the datatypePLS_INTEGER, which is the most efficient numeric type.

8/12/2019 Ppt Db25 Oracle 05



Version No. 2.0

Unnecessary NOT NULL Constraints

Using the NOT NULL constraint incurs a performance cost.

PROCEDURE calc_m ISm NUMBER NOT NULL := 0;a NUMBER;b NUMBER;

BEGIN...m := a + b;...

END;

PROCEDURE calc_m ISm NUMBER; -- no constrainta NUMBER;b NUMBER;

BEGIN...m := a + b;IF m IS NULL THEN-- enforce constraint programmatically...

END IF;END;

8/12/2019 Ppt Db25 Oracle 05

15/5115

Copyright 2005, InfosysTechnologies Ltd

ER/CORP/CRS/DB25/003Version No. 2.0

Solution to Misuse of shared memory

Sizing the shared memory pool correctly.

Make sure it is large enough to hold all frequently used packages but not solarge that memory is wasted.

8/12/2019 Ppt Db25 Oracle 05

16/5116



Identifying PL/SQL Performance problems

There are few tools/API provided by PL/SQL to identify the PL/SQLperformance problems:

DBMS_PROFILER

DBMS_TRACE

One need DBA-SYS access to use these tools/API

8/12/2019 Ppt Db25 Oracle 05

17/5117



DBMS_PROFILER

The Profiler API is implemented as PL/SQL package DBMS_PROFILER.

Provides services for gathering and saving run-time statistics.

The information is stored in database tables, which one can query later.

For example, one can learn how much time was spent executing each PL/SQLline and subprogram.

8/12/2019 Ppt Db25 Oracle 05

18/5118



DBMS_TRACE

The Trace API is implemented as PL/SQL package DBMS_TRACE.

Provides services for tracing execution by subprogram or exception.

One can see the order in which subprograms get executed.

In a typical session, follow the following steps: Optionally, select specific subprograms for trace data collection. Start by calling the procedure set_plsql_trace in package DBMS_TRACE. Run the application to be traced. Stop by calling the procedure clear_plsql_trace.

8/12/2019 Ppt Db25 Oracle 05

19/51

Concurrency in Oracle

8/12/2019 Ppt Db25 Oracle 05

20/51



Version No. 2.0

Locking mechanism

The smallest unit that can be locked in oracle is the row

For each SQL statement oracle acquires the appropriate lockautomatically .

Programmers cannot acquire locks at the row level explicitly The smallest unit at which programmers can explicitly acquire locks is

the table Locks are released when transaction commits or rolls back Oracle does not acquire locks for reading

8/12/2019 Ppt Db25 Oracle 05

21/51



Version No. 2.0

Types of locks

When Oracle acquires locks at the row level, it automaticallyacquires locks at the table level also

There are 5 different modes of locking at the table level row share row exclusive share row exclusive

share exclusive

8/12/2019 Ppt Db25 Oracle 05

22/51



Version No. 2.0

LOCK TABLELOCK TABLE

LOCK TABLE IN MODE

8/12/2019 Ppt Db25 Oracle 05

23/51



Version No. 2.0

Locking Modes

EXCLUSIVE(X)

No other transaction can lock the table.

SHARE(S)

Allow queries but no updatesOther transactions can acquire S Lock on this tableCan allow SELECT..FOR UPDATE & SELECT statements

8/12/2019 Ppt Db25 Oracle 05

24/51



Version No. 2.0

ROW EXCLUSIVE(RX)

Allow concurrent access for queries, but prevent Share lockingalso by other transactionsPermit Insert, Update, Delete or Select by other transactions/ orlock rows in same table by other transactions

Prevent Share or Exclusive locking of the table by othertransactions

Locking Modes

8/12/2019 Ppt Db25 Oracle 05

25/51



Version No. 2.0

ROW SHARE(=SHARE UPDATE)

Allow concurrent access, prevent Exclusive locking by othertransactions

SHARE ROW EXCLUSIVE(SRX)

Allow other users to look at table but prevent sharing and updatesby other transactions

Locking Modes

8/12/2019 Ppt Db25 Oracle 05

26/51



Version No. 2.0

Locking

SQL StatementMode oftable lock

Select .... from table none

insert,delete,update RX

Select .... for update of RS

Lock table in rowshare mode RS

Lock table in rowx mode RX

Lock table in share mode S

Lock table in exclusive mode XLock table in srx mode SRX

Row lock

none

X

X

8/12/2019 Ppt Db25 Oracle 05

27/51



Version No. 2.0

Lock Compatibility

SQL statement Table Lock Compatibility

RS RX S SRX XSELECT..FROM None Y Y Y Y Y

INSERT..INTO RX Y Y N N N

UPDATE RX Y Y N N N

SELECT..FOR..UPD RS Y Y Y N NSHARE MODE S Y N Y N N

SRX MODE SRX Y N N N N

EXCLUSIVE MODE X N N N N N

8/12/2019 Ppt Db25 Oracle 05

28/51



Version No. 2.0

Read Consistency Model

System Change Number (SCN) A unique number which is assigned to each transaction when it

starts

Transaction table An internal data structure which stores information regarding the

active transactions in the system

8/12/2019 Ppt Db25 Oracle 05

29/51

Recovery in Oracle

8/12/2019 Ppt Db25 Oracle 05

30/51



Version No. 2.0

Possible causes of failure

Process failure

User/Server Process Background Process

Database Instance failure

Media failure

8/12/2019 Ppt Db25 Oracle 05

31/51



Version No. 2.0

Process recovery Process recovery

to be done when user/server process failed Failure detected by PMON which rolls back the transaction and

release resources If process is a background process, generally instance cannot

function correctly

8/12/2019 Ppt Db25 Oracle 05

32/51



Version No. 2.0

Instance recovery

Need

Required because DBWR writes data to disk only when bufferis nearly full

Some data which is committed may not be present in database Database may contain uncommitted data also

Performed by SMON when database is restarted

Roll forward using Redo Log to record data not present in data files Rolls back transactions that did not commit/were explicitly rolled

back Release locks held by transactions in process at time of failure

8/12/2019 Ppt Db25 Oracle 05

33/51



Version No. 2.0

Media recovery Initiated by DBA Complete Media recovery

Restore backup data files Start recovery program using SQL *DBA Retrieve archived log files to roll forward and roll back using

generated roll back segments

8/12/2019 Ppt Db25 Oracle 05

34/51

Data Transfer Utilities

8/12/2019 Ppt Db25 Oracle 05

35/51



Version No. 2.0

Objectives

Export utility

Import utility

SQL Loader

8/12/2019 Ppt Db25 Oracle 05

36/51



Version No. 2.0

Export & Import utilities

Complementary utilities which allow one to write data in an ORACLE-binaryformat from the database into operating system files and to read data backfrom those.

EXPORT, IMPORT are used for the following tasks: backup Oracle data in operating system files restore tables that were dropped

save space or reduce fragmentation in the database move data from one owner to another

8/12/2019 Ppt Db25 Oracle 05

37/51



Version No. 2.0

Export Utility - Invoking Export

Interactive dialogue:Simply type exp on command prompt.

Controlled through by passing parameters:One may pass parameters when one export data from the database. Type the following at command prompt

C:\>exp scott/tiger file=empdept.expdat tables=(EMP,DEPT) log=empdept.log

Parameterfile controlled:One may use a parameter file where the parameters are stored. Type the following at command prompt

C:\>exp parfile=

8/12/2019 Ppt Db25 Oracle 05

38/51



Version No. 2.0

Import Utility - Invoking Import

Interactive dialogue:Simply type imp on command prompt.

Controlled through by passing parameters:One may pass parameters when one import data. Type the following at command prompt

C:\>imp < userid/password > tables=(table1,table2)

Parameterfile controlled:One may use a parameter file where the parameters are stored.

Type the following at command prompt

C:\>imp parfile=

8/12/2019 Ppt Db25 Oracle 05

39/51

8/12/2019 Ppt Db25 Oracle 05

40/51

8/12/2019 Ppt Db25 Oracle 05

41/51



Version No. 2.0

The SQL * Loader Control file

Key to any load process. The control file provides the following information toSQL*Loader:

The name and location of the input data file The format of the records in the input data file The name of the table or tables to be loaded The correspondence between the fields in the input record and the columns in

the database tables being loaded Selection criteria defining which records from the input file contain data to be

inserted into the destination database tables. The names and locations of the bad file and the discard file (Explained later)

8/12/2019 Ppt Db25 Oracle 05

42/51



Version No. 2.0

The SQL * Loader Log file

The log file is a record of SQL*Loader's activities during a load session. It containsinformation such as the following:

The names of the control file, log file, bad file, discard file, and data file The values of several command-line parameters A detailed breakdown of the fields and datatypes in the data file that was

loaded Error messages for records that cause errors Messages indicating when records have been discarded A summary of the load that includes the number of logical records read from

the data file, the number of rows rejected because of errors, the number ofrows discarded because of selection criteria, and the elapsed time of the load

8/12/2019 Ppt Db25 Oracle 05

43/51



Version No. 2.0

The SQL * Loader Bad file

Whenever SQL*Loader encounters a database error while trying to load arecord, it writes that record to a file known as the bad file .

Common scenarios are: insert failing because of some type of error. Integrity constraint violations lack of free space in a tablespace, can also cause insert operations to fail.

Bad files are mandatory.

8/12/2019 Ppt Db25 Oracle 05

44/51



Version No. 2.0

The SQL * Loader Discard file

Used to hold records that do not meet selection criteria specified in theSQL*Loader control file.

Discard files are optional.

8/12/2019 Ppt Db25 Oracle 05

45/51



Version No. 2.0

An example on usage of SQL * Loader

The following slides contains a short example showing how SQL*Loader isused.

For this example, we'll be loading a Unix module mark sheet (XLS) of a FPbatch taken from perception server (examination server) into Oracle

8/12/2019 Ppt Db25 Oracle 05

46/51



Version No. 2.0

Creating Data file

Save your Excel spreadsheet data as a Comma-Separated-Variable (*.csv)

file.

Click on the icon below to get the sample CSV file

8/12/2019 Ppt Db25 Oracle 05

47/51



Version No. 2.0

Creating Control file

Using any text editor, create a file (say, e:\workarea\marksheet.ctl) containingthese lines:

LOAD DATAINFILE 'E:\workarea\marksheet.csv'REPLACEINTO TABLE scott.marks

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'TRAILING NULLCOLS(empid INTEGER EXTERNAL,quiz1 INTEGER EXTERNAL,quiz2 INTEGER EXTERNAL,test INTEGER EXTERNAL,total INTEGER EXTERNAL,grade

)

8/12/2019 Ppt Db25 Oracle 05

48/51



Version No. 2.0

Running SQL * Loader

At an MS-DOS prompt (or the Start, Run menu) , execute SQL*Loader as

follows:

C:\>sqlldr scott/tiger@training control=e:\workarea\marksheet.ctl

One can see your loaded data with your favorite:

SQL>Select * from marks;

8/12/2019 Ppt Db25 Oracle 05

49/51



Version No. 2.0

Export-Import vs. SQL * Loader

Export and Import tools are primarily used to maintain ORACLE databaseobjects.

Because of the special binary format, files which had been created by theEXPORT utility can only be read by IMPORT utility.

To load data from other systems into the database one have to use

SQL*LOADER.

8/12/2019 Ppt Db25 Oracle 05

50/51

8/12/2019 Ppt Db25 Oracle 05

51/51

Thank You!

ppt db25 oracle 05

Documents