PL SQL performance tuning :

In my previous articles i have given the brief idea of SQL peformance tuning.Now a step forward there are some basic steps for PL SQL performance tuning.It is important for every developer to tune the plsql code.PL SQL performance tuning is very important while adding business logic in oracle.PLSQL engine uses the PLSQL optimizer to rearrange the code for better performance.In this article i will try to give the brief introduction of PL SQL performance tuning and what changes programmer needs to do for tuning the performance of PLSQL code.

Scenarios of PL SQL performance tuning :

There are some specific situations where user needs to do some changes of PL SQL performance tuning.I will try to explain the different situations and how to tackle that situations and do performance tuning.

1.Datatype Conversion :

While using the datatype conversion PL SQL engine uses the implicit conversion of the datatype.User needs to avoid the implicit conversion of datatypes.So avoiding implicit conversion will improve the performance.

Program :

DECLARE
   V_No NUMBER;
   V_Char CHAR(10);
BEGIN
   V_No := V_No + 50;      -- converted implicitly; slow
   V_No := V_No + 50.0;    -- not converted; fast
   V_Char  := 25;          -- converted implicitly; slow
   V_Char  := TO_CHAR(25); -- converted explicitly; still slow
   V_Char  := '25';        -- not converted; fast
END;

In above example user will get how to use the different datatypes in PL SQL. Simple example is when user uses the Number datatype,the PL SQL engine always considers number datatype with one precision so try to use one precision while using simple integer number.Just like that while using character datatype try to avoid the TO_CHAR function and always use single quote.

2.Make use of Efficient SQL statements for PL SQL performance tuning:

The PLSQL code will slow down because of the inefficient use of SQL statements. So make sure that user needs to use the perfect SQL statement.

A.Appropriate Index use:

User needs to create appropriate indexes on specific column of the table. User should create the indexes on columns in where condition of the statement.

1. User needs to create bit map indexes in case of distinct values in the table.

Syntax:

Create bitmap index index_name on tablename(column_name);

2. User needs to create simple index in case of non-distinct values.

Syntax:

Create index index_name on tablename(column_name);

3. User needs to create composite index in case of joins (hash tables).

Syntax:

Create index index_name on tablename(column1,column2);

B.Up to date table stats:

User should gather the stats of tables after indexing the columns or changes in table structure. Kindly use the subprogram of dbms_stats package to gather the stats.

Syntax for gathering stats using parallel 30 :

execute dbms_stats.gather_table_stats(ownname => ‘SIEBEL_ODS’, tabname => ‘Table_name’, cascade => true, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => ‘FOR ALL COLUMNS SIZE AUTO’ , degree=>30);

C.Analyze the execution plan of SQL statement and try to improve performance :

1.Check the explain plan and try to reduce cost using indexes.

2.Check the joins using trace facility.

D.Change the SQL statements whenever necessary :

Try to avoid group by as well as distinct statements in SQL.User needs to rewrite the SQL statements whenever necessary.

 3.Use Latest Features of PLSQL :

User needs to use the latest PLSQL features to improve the performance of PLSQL code.

1.Use of Forall Statement :

 Try to replace the use of insert,update and delete loops to Forall statements.

Example for Forall statement :

CREATE TABLE Employee2 AS SELECT * FROM Employee1;

The above statement will create the Employee2 table same as Employee1.

DECLARE

TYPE List_Number IS VARRAY(20) OF NUMBER;  — Define the List_Num Variable of VARRAY TYPE

departments List_Number := NumList(101, 304, 705);  — department numbers

BEGIN

FORALL i IN departments.FIRST.. departments.LAST        —Defining forall

DELETE FROM Employee2 WHERE department_id = departments(i);

—Delete all data for Department number 101,304,705

COMMIT;

END;

2.Use of Bulk Collect :

Use of bulk collect in case of Select into clause.The Bulk collect statement improves the performance of the PLSQL block. When reading-in lots of related rows, bulk collect can run 10x faster than a conventional loop.  This tuning is possible because Oracle reduces context switches into a single operation.

Example of Bulk Collect :

DECLARE
TYPE test_bulk_collect IS TABLE OF s_srv_req%ROWTYPE;

V_test_bulkcollect test_bulk_collect;

CURSOR C_bulk_collect IS
SELECT *
FROM   OF s_srv_req;
BEGIN
OPEN C_bulk_collect;
LOOP
FETCH C_bulk_collect
BULK COLLECT INTO l_tab LIMIT 10000;     ——-10000 records collected with PLSQL engine (No context switching)

DBMS_OUTPUT.put_line(V_test_bulkcollect.count || ‘ rows’);
EXIT WHEN C_bulk_collect %NOTFOUND;
END LOOP
CLOSE C_bulk_collect;
END;

4.Use of correct function calls :

User needs to use correct function calls in PLSQL blocks. If that function is called in Specific SQL query then user needs to create correct functional based index on that function.

Query :

Select count(row_id) from T_Service_request;

Kindly create index on rowed which is functional based.

Create index FI_T_Service_request on T_Service_request(Count(row_id));

5.Looping Practices :

It is very important to optimize the loops in PLSQL statement. Kindly follow the following practices while working with loops.

1. Initialization outside the loop :

Kindly initialize the variables outside the loop to improve the performance of the PLSQL block.

2. Use of Forall statement :

Kindly use Forall statement in case of processing bulk DMLs.

3. Use of Union,Intersect,Minus,Hierarchical Queries :

Try to use union,intersect,minus as well as Hierarchical queries of ‘connect by’ to improve the performance.

4. Bulk Collect :

Process set of rows with bulk collect statement in spite of using loops.

6.String Function Practices :

1.Use In built String Functions :

Use multiple string functions  rather than use PLSQL block.Oracle has defined set of pre defined optimized string functions like Replace,Instr,Substr,RPAD,LPAD etc.

2.Use of REGEX function :

If user wants to deal with regular expression make use of REGEX functions rather than writing whole code.

7.Use of AND OR Operator for PL SQL performance tuning :

Check whether the AND condition and or condition use in SQL statement. Try to avoid OR condition as it is full scanning the table.

8.Use of PLS and Binary Datatypes :

Try to use the datatypes, which are using machine arithmetic datatypes like PLS, and binary arithmetic datatypes.

PLS_INTEGER :

When user wants to define normal integer; He/She tries to define the datatype as INTEGER or NUMBER. PLS_INTEGER requires less storage than Integer or Number datatype. So try to use PLS_INTEGER in case of INTEGER or Number datatype.

BINARY_INTEGER:

The Binary_Integer is same as PLS_INTEGER but PLS_INTEGER is faster in Oracle 9i and Oracle 8i.

BINARY_DOUBLE / BINARY_FLOAT :

Kindly use BINARY_DOUBLE or BINARY_FLOAT for floating point number declaration in PLSQL.

9.Use of Execute Immediate statement and Cursor Variables:

Kindly use the ‘Execute immediate’ statement and cursor variables while developing the PLSQL blocks. Native dynamic SQL code is more compact and much faster than calling the DBMS_SQL package.User can use ref cursor to process dynamic SQL statements.

Example :

DECLARE

TYPE Employee_Cursor IS REF CURSOR;

Cv_employee  Employee_Cursor;              —-Open Ref Cursor

Name VARCHAR2(10);

Salary   NUMBER := 50000;

table_name VARCHAR2(30) := ’employee’;

BEGIN

OPEN Cv_employee FOR ‘SELECT Name, salary FROM ‘ || table_name ||

‘ WHERE salary > :s’ USING Salary;

CLOSE Cv_employee ;                       —-Close Ref Cursor

END;

10.Declaring Size of Variable :

Lot of programmers does not think about the size of variables declaration. You might need to allocate large VARCHAR2 variables when you are not sure how big an expression result will be.So be generous while declaring the size of variable.Check the functionality of that column and according to that specify the size of the variable.

11.Use of Packages in spite of Subprograms :

Kindly use the packages to encapsulate all the similar functionality subprograms. The whole package is loaded in to same-shared memory IO so Subsequent calls to related subprograms in the package require no disk I/O, and your code executes faster.

12.Use of DBMS_PROFILER for PL SQL performance tuning :

Try to use DBMS_PROFILER which will use to gather and save the runtime stats. The Profiler traces the execution of your program, computing the time spent at each line and in each subprogram. You can use the collected data to improve performance.The Profiler is useful when the same query we need to execute again and again.

Lets take an example,when user needs the same report for specific month again and again.The date range is same so at that time user needs to set profiler for that specific query so that result will come fast.

Accepting profile for Specific Query :

 execute dbms_sqltune.accept_sql_profile(task_name => ‘Query Id’, task_owner => ‘Username’,       replace => TRUE);

13.Use of Different hints in SQL statements :

It is profitable to use the hints like parallel and append in internal sql statements. Parallel and append hints improves the performance of the query but it will take the CPU bandwidth. So take care while using the parallel and append hints in the PLSQL code.

Syntax of Hint :

Select Employee_name from Employee;

16 X faster performance :

Select /*+ parallel(Employee,16)*/ Employee_name from Employee;

Always try to use append link while inserting the bunch of records.

Example :

INSERT /*+ APPEND */ INTO t1 SELECT * FROM S_ORG_EXT;

These are some simple methods to tune the performance of PLSQL code. Hope this article will be helpful to everyone who really want to do the PL SQL performance tuning.There are lot of new techniques and methodologies used to tune the performance of plsql application.