-- Performance Tuning – for
Beginners
-- Using Oracle SQL & PL/SQL
1) Introduction:
Performance
tuning is a major part in a database, as well as database maintenance. As the
transaction data increases performance degrades, PL/SQL enhancements becomes mandatory for a database. Any Online transaction processing(OLTP) systems have both real time and MIS related reports along with huge transactions. So, performance tuner has a major role in OLTP systems.
2)
About Oracle Execution Plan:
An explain plan is a representation of the access path that is taken when a query is executed within Oracle.
3) Gather schema/ Table statistics
exec DBMS_STATS.gather_schema_stats('HR');
exec DBMS_STATS.gather_table_stats('HR','employee');
Let us discuss rules on
performance tuning. See the examples wherever given.
1 : Avoid Using * in SELECT
Clauses
The dynamic SQL column reference
(*) gives you a way to refer to all of the columns of a table. Do not use the *
feature because it is very inefficient -- the * has to be converted to each
column in turn. The SQL parser handles all the field references by obtaining
the names of valid columns from the data dictionary and substitutes them on the
command line, which is time consuming.
2: Reduce the Number of Trips to
the Database
Every
time a SQL statement is executed, ORACLE needs to perform many internal
processing steps; the statement needs to be parsed, indexes evaluated,
variables bound, and data blocks read. The more you can reduce the number of
database accesses, the more overhead you can save.
For
example:
There
are 3 distinct ways of retrieving data about employees who have employee
numbers 0342 or 0291.
Method
1 (Least Efficient) :
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = 0342;
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = 0291;
Method 2 (Most Efficient) :
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = 0342 OR EMP_NO = 0291;
Note: One simple way to increase the
number of rows of data you can fetch with one database access and thus reduce
the number of physical calls needed is to reset the ARRAYSIZE parameter in
SQL*Plus, Suggested value is 200.
3: Use TRUNCATE instead of
DELETE
When rows are removed from a
table, under normal circumstances, the rollback segments are used to hold undo
information; if you do not commit your transaction, Oracle restores the data to
the state it was in before your transaction started.
With TRUNCATE,
no undo information is generated. Once the table is truncated, the data cannot
be recovered back. It is faster and needs fewer resources.
Use
TRUNCATE rather than DELETE for wiping the contents of small or large tables
when you need no undo information generated.
4: Counting Rows from Tables
Contrary
to popular belief, COUNT(*) is faster than COUNT(1). If the rows are being
returned via an index, counting the indexed column – for example, COUNT(EMPNO)
is faster still. Use count(rowid) when
there is no indexed column or primary key column.
5: Minimize Table Lookups in a
Query
To improve performance, minimize
the number of table lookups in queries, particularly if your statements include
sub-query SELECTs or multi-column UPDATEs.
For example:
Least Efficient :
SELECT
TAB_NAME
FROM
TABLES
WHERE TAB_NAME = (SELECT
TAB_NAME
FROM
TAB_COLUMNS
WHERE VERSION = 604)
AND
DB_VER = (SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
Most Efficient :
SELECT
TAB_NAME
FROM
TABLES
WHERE (TAB_NAME,
DB_VER) = (SELECT TAB_NAME, DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
Multi-column UPDATE example:
Least Efficient :
UPDATE EMP
SET
EMP_CAT = (SELECT MAX(CATEGORY)
FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT
MAX(SAL_RANGE)
FROM EMP_CATEGORIES )
WHERE EMP_DEPT = 0020;
Most Efficient :
UPDATE EMP
SET
(EMP_CAT, SAL_RANGE) =
(SELECT MAX(CATEGORY), MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
6
: Use Table
Aliases
Always use table aliases &
prefix all column names by their aliases where there is more than one table
involved in a query. This will reduce parse time & prevent syntax errors
from occurring when ambiguously named columns are added later on.
7: Use EXISTS in Place of
DISTINCT
Avoid joins that require the DISTINCT
qualifier on the SELECT list when you submit queries used to determine
information at the owner end of a one-to-many relationship (e.g. departments
that have many employees).
For example:
Least Efficient :
SELECT
DISTINCT DEPT_NO, DEPT_NAME
FROM
DEPT D, EMP E
WHERE
D.DEPT_NO = E.DEPT_NO
Most Efficient :
SELECT
DEPT_NO, DEPT_NAME
FROM
DEPT D
WHERE EXISTS (SELECT
‘X’
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);
EXISTS is a faster alternative
because the RDBMS kernel realizes that when the sub-query has been satisfied
once, the query can be terminated.
8 : Avoid
Calculations on Indexed Columns
If the indexed column is a part of
a function (in the WHERE clause), the optimizer does not use an index and will
perform a full-table scan instead.
Note : The SQL functions MIN
and MAX are exceptions to this rule and will utilize all available indexes.
For example:
Least Efficient :
SELECT . . .
FROM
DEPT
WHERE SAL * 12 > 25000;
Most Efficient :
SELECT
. . .
FROM
DEPT
WHERE SAL > 25000 / 12;
9 : Exact Position of Tables
& Columns
Exact Position:
When
we run a query, the execution starts from last to first. The table which contains
less number of records should be kept last.
Ex: Table1(1000
records), Table2(100)
Select * from table1,
table2.
This
will not affect the query cost wise but in long term data retrieval will be
faster.
Exact
position of columns:
At
time of joining 2 tables master record should be right hand side of = Symbol.
Method
1 (Least Efficient) :
Select * from master, child
where master.columnname=child.columnname;
Method 2 (Most Efficient) :
Select
* from master, child
where child.columnname=master.columnname;
>>> Here
data retrial will be faster
10:
Null Comparison
We
should try to avoid comparing the NULL values column. Use of this kind of
column will not allow the optimizer to use of Indexes
present on the table.
Method -1 (Least Efficient) :
Select rt.requestid
FROM bookrequests
rt
where
(:iv_request_id is NULL OR rt.requestid = :iv_request_id);
Method -2 (Most Efficient) :
if (:iv_request_id is not null) THEN
Select
rt.requesttestid
FROM
bookrequests rt
where
rt.requesttestid = :iv_request_id);
Elsif (:iv_request_id IS NULL) THEN
Select
rt.requesttestid FROM bookrequests rt
where rt.requestid is null);
End If;
11:
Compatible datatype of columns:
At
time of joining tables a due focus should be on the datatype compatibility of
the columns used in Join conditions or where clauses.
Method 1 (Least Efficient) :
Sql> Select barcode, price From
products Where barcode = 5467;
Method
2 (Most Efficient) :
Sql>
Select barcode, price From products Where barcode = ‘5467’;
Such
conditions should not be used at all, even though if its required this may
require oracle definded datatype conversion functions like To_char ,To_date
etc.
- Rectification
of such kind of errors will result in a lot Cost reduction.
- If DataType Conversion is performed , Should be
used on INPUT Variables not on Table Columns.
12:
Inline views,Replacement of Subquery
- When performing Left outer or Right Outer join ,try to replace it by using Inline Views for small / master tables/ fact tables.
SELECT e.empno,e.ename,d.deptno,d.dname
FROM
(SELECT empno, ename from emp@dblink) e, dept d;
- If
the same sub query is used in multiple locations of a query, it can be replaced
by making use of Functions.
- Functions
will avoid great context Switch and also code will be reusable.
Summary of techniques for each type of subquery:
|
Standard Subquery
|
Anti-join Subquery
|
|
IN
|
EXISTS
|
NOT IN
|
NOT EXISTS
|
Correlated
subquery
|
Redundant Boolean predicates. Can always be replaced with a standard join
|
Automatic Transformation to nested loop join
|
Rewrite as select distinct outer join
|
Rewrite as select distinct outer join
|
Non-correlated subquery
|
Automatic transformation to nested loop join
|
Never appropriate
|
Rewrite as nested loop join with minus operator
|
Never appropriate
|
13
: Use of Function for Left join,
Subquery
Method 1 (Least Efficient) :
select e.empno, e.ename, t.trandate, t.tranamount
from transaction t
inner join emp e on t.createdby = e.empno
order by t.tranamount desc;
Method 2 (Most Efficient) :
select counterno,f_showname(t.createdby) "Ename", t.trandate, t.tranamount
from transaction t
order by t.tranamount desc;
Using co-related sub-query:
Method 1 (Least Efficient) :
Select pbn.billno
from Publicbillno
pbn
where
pbn.locationid = iv_locationid
and pbn.billno
not in
(select
pb.billno
from
Publicbill pb
where
pb.locationid = iv_locationid)
Method 2 (Most Efficient) :
Select pbn.billno
from
Patientbillno pbn
where
pbn.locationid = iv_locationid
and not exists
(select pb.billno
from Publicbill pb
where pb.locationid = iv_locationid
and pb.billno = pbn.billno)
14
: Usuage of terms Exists , DECODE,CASE,LIKE
- Making
use of Clauses like `EXISTS,DECODE,CASE‘ will help to reduce the cost of queries.
- Use
of EXISTS in place of IN. EXISTS will give better performance when the main
query returns less records
- CASE
will be more effective than an IF condition.
- Avoid
LIKE clause, If it is used, it should be more specific to the requirement.
- CASE can be used in SELECT statements and Pl/SQL block where more IF-ELSE used.
14.1) Use DECODE to Reduce
Processing
The DECODE statement
provides a way to avoid having to scan the same rows repetitively or to join
the same table repetitively.
For example:
SELECT COUNT(rowid),
SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020 AND ENAME LIKE ‘SMITH%’;
SELECT
COUNT(rowid), SUM(SAL)
FROM
EMP
WHERE DEPT_NO = 0030
AND
ENAME LIKE ‘SMITH%’;
You can achieve the same
result much more efficiently with DECODE:
SELECT
COUNT(DECODE(DEPT_NO, 0020, ‘X’, NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO, 0030, ‘X’, NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO, 0020, SAL, NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO, 0030, SAL, NULL)) D0030_SAL
FROM EMP
WHERE ENAME LIKE ‘SMITH%’;
Similarly, DECODE can be
used in GROUP BY or ORDER BY clause effectively.
14.2) Use EXISTS in Place of
IN for Base Tables
Many base table queries have to
actually join with another table to satisfy a selection criteria. In such
cases, the EXISTS (or NOT EXISTS) clause is often a better choice for
performance.
Least Efficient :
SELECT
*
FROM
EMP
(Base Table)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB’)
Most Efficient :
SELECT
*
FROM
EMP
WHERE EMPNO > 0
AND EXISTS (SELECT
‘X’
FROM
DEPT
WHERE DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’)
14.3) Use NOT EXISTS in Place
of NOT IN
In sub-query statements such as
the following, the NOT IN clause causes an internal sort/merge. The NOT IN
clause is the all-time slowest test possible as it forces a full read of the
table in the sub-query SELECT. Avoid using NOT IN clause either by
replacing it with Outer Joins or with a NOT EXISTS clause as
shown below:
SELECT . . .
FROM
EMP
WHERE DEPT_NO NOT IN (SELECT
DEPT_NO
FROM
DEPT
WHERE DEPT_CAT =
‘A’);
To improve the performance,
replace this code with:
Method 1 (Efficient) :
SELECT . .
.
FROM
EMP A, DEPT B
WHERE A.DEPT_NO = B.DEPT_NO (+)
AND B.DEPT_NO IS NULL
AND B.DEPT_CAT(+) = 'A'
Method 2 (Most Efficient) :
SELECT . . .
FROM
EMP E
WHERE NOT
EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A’);
14.4)
Use Of Not Exist and Exist
Method 1 (Least Efficient) :
Select count(rowid) From Products Where barcode NOT IN (Select
barcode From Clothing);
Time in Secs : >500secs
Method 2 (Most Efficient) :
Select count(rowid) From Products P Where NOT EXISTS (
Select C.barcode From Clothing C
Where C.barcode = P.barcode);
Method 3 (Least Efficient) :
Time in Secs: 6.1 secs
Select prod_id,qty From Product Where prod_id=167 and item_no IN
(select item_no from items)
Time
in Secs : >120secs
Method 4 (Most Efficient) :
Select prod_id,qty From Product
prd Where prod_id=167 and exists
(select ‘x’ from items itm where b.itemno=a.itemno);
Time
in Secs: 2.0 secs
15
: Avoid NOT
on Indexed Columns
In general, avoid using NOT when
testing indexed columns. The NOT function has the same effect on indexed
columns that functions do. When ORACLE encounters a NOT, it will choose not to
use the index and will perform a full-table scan instead.
Least Efficient : (Here, index
will not be used)
SELECT . . .
FROM
DEPT
WHERE DEPT_CODE <> 0;
Most Efficient : (Here, index
will be used)
SELECT . . .
FROM
DEPT
WHERE DEPT_CODE > 0;
In a few cases, the ORACLE
optimizer will automatically transform NOTs (when they are specified with other
operators) to the corresponding functions:
NOT
> to
<=
NOT
>=
to <
NOT
<
to >=
NOT
<=
to >
16: Avoid
IS NULL and IS NOT NULL on Indexed Columns
Avoid using any column that
contains a null as a part of an index. ORACLE can never use an index to locate
rows via a predicate such as IS NULL or IS NOT NULL.
In a single-column index, if the
column is null, there is no entry within the index. For concatenated index, if
every part of the key is null, no index entry exists. If at least one column of
a concatenated index is non-null, an index entry does exist.
17:
Using Global Temporary Table (GTT)
- When
ever a huge data is being processed in a procedure its always better to use
GTT.
- It
will improve the performance as it will also reduce the overhead of data
truncation as in normal table, The procedure would also contain a delete
statements or else a table truncation.
- GTT
will be allocated separately for multiple sessions accessing the table.
- This
will also allow us to use concept like BULK COLLECT, COLLECTION
Syntax:
Create
global temporary gtt_tablename
(col1 datatype, Col2 datatype, …..
) on
commit preserve rows [ On commit delete rows];
Note: On
commit preserve rows: All data retain in the table till the session closed but
On commit delete rows delete all the rows from the GTT as soon as commit fires
even if the session not closed. GTT indexes are auto managed by oracle. No
water mark created when number of deletion occurs.
Examples:
Method 1 (Least Efficient) :
SELECT NVL(T.BILLID,
T.TRANSACTIONID) AS TRANID
FROM CRM.MAINTRANSACTION T, HR.SHIFTMAINTENANCE SM
WHERE T.SHIFTNO = SM.SHIFTNO AND
TRUNC(TRANDATE) =
'19-JUN-2013' AND T.FLAG = 1
AND T.TRANEVENT NOT IN (28) AND
T.CREATEDBY = 8129
AND T.Locationid=10201
Execution Time : 300 sec
Method 2 (Most Efficient) :
INSERT INTO CRM.GT_MAINTRANSCTION SELECT
* FROM CRM.MAINTRANSCTION
WHERE TRANDATE='19-JUN-2013'
SELECT NVL(T.BILLID,
T.TRANSACTIONID) AS TRANID FROM CRM.GT_MAINTRANSACTION T, HR.SHIFTMAINTENANCE SM
WHERE T.SHIFTNO = SM.SHIFTNO AND
TRUNC(TRANDATE) =
'19-JUN-2013' AND T.FLAG = 1 AND
T.TRANEVENT NOT IN (28) AND
T.CREATEDBY = 8129
AND T.Locationid=10201
Execution Time : 40 sec
18
: Use BULK COLLECT and FORALL
When inserting rows in PL/SQL,
developers often place the insert statement inside a FOR loop. To insert
1,000 rows using the FOR loop, there would be 1,000 context switches between
PL/SQL and the Oracle library cache.
Oracle allows you to do bulk binds
using the PL/SQL FORALL loop, which requires only one context switch.
This is achieved by the FORALL statement passing an entire PL/SQL table to the
SQL engine in a single step. Internal tests show that this process
results in substantial performance increases over traditional methods.
Example:
Method 1 (Least Efficient) :
DECLARE
TYPE
NumTab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;
TYPE
NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
pnums
NumTab; pnames NameTab;
BEGIN
FOR i IN 1..20000 LOOP -- use FOR loop
INSERT INTO parts VALUES (pnums(i),
pnames(i));
END LOOP;
END; Time taken in
sec: 11.0
/
Example:
Method 1 (Most Efficient) :
DECLARE
TYPE NumTab IS TABLE OF NUMBER(5) INDEX BY
BINARY_INTEGER;
TYPE
NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
pnums NumTab;
pnames
NameTab;
BEGIN
FORALL
I in 1 .. 20000 -- use FORALL
INSERT INTO parts VALUES (pnums(i),
pnames(i));
END;
Time Taken in Secs: 0.5 sec
/
Using
Bulk Collect
Example :
Method 1 (Least Efficient) :
Declare
Type bcode is table of
products.barcode%TYPE;
i int;
barc bcode;
cursor cur_seq is
select barcode from products
where rownum<100001;
Begin
i:=0;
for cur_dta in cur_seq loop
barc:=cur_dta(i).barcode;
i:=i+1;
end loop;
End;
Execution Time : 20 sec
Method 2 (Most Efficient) :
declare
Type bcode is table of
products.barcode%TYPE;
i
int;
barc bcode;
begin
select barcode BULK COLLECT into
barc from products where rownum<100001;
end;
Time
taken : 1.41 sec
19
: Use of Index
> Indexes
helps a lot in increasing the Performance of a Query. Its like storing the
address of a perticular Record.
> We
can create Bitmap Index, functional Index, Normal Index based on requirement.
> If
we create a Unique index on a column data should be unique in the column.
> Oracle
also provides a new concept of Invisible Index for performance monitoring of a
specific query without affecting the other query referencing the Table
20: Using table partition
> Table partition is helpfull when a table contain a huge
amount of data.
> It
breaks the Table into smaller segments called partition thus resulting into
faster data retrival.
> Partition
can be Various Type. Range , Hash, List Partition
> For
Partitioned table Query writing will be same as for the Normal Tables.
> We
can also Go Partioned Index
> After
performing the partition managment we need to gather the Statistics
> No
Special Query Writing method for Partiton Tables..
CREATE
TABLE Emp ( Emp_no NUMBER, Emp_sal varchar2(30) )
PARTITION BY RANGE (Emp_no)
( PARTITION emp1 VALUES LESS THAN
(10) TABLESPACE tbsa,
PARTITION emp2 VALUES LESS THAN
(20) TABLESPACE tbsb);
Note:
Use bigger values for range partition ( in lakh) in case of production environment.
CREATE
TABLE Emp (Emp_no NUMBER, Emp_sal
varchar2(30))
PARTITION BY HASH (Emp_no)
PARTITIONS 4 STORE IN (gear1, gear2, gear3, gear4);
CREATE
INDEX local_idx_SRC ON BILLING.TESTLALIT (PATIENTSERVICEREQUISTID) LOCAL;
CREATE
INDEX global_part_idx ON
BILLING.TESTLALIT(PATIENTSERVICEREQUISTID)
GLOBAL
PARTITION BY RANGE(PATIENTSERVICEREQUISTID)
(PARTITION
p1 VALUES LESS THAN(1000),
PARTITION p4 VALUES LESS THAN(10000));
Note : MAXVALUE value can be used
if further you don’t want to add new partition.
Caution: If any partition is filled up
and new partition is not added, then your entire transaction will be stopped.
So be careful when you are adding new partitions. But in Oracle 11g onwards
there is solution to add partitions automatically.
-- add partition manually
Alter table
owner.partition_tablename add PARTITION partitionname values less than (value)
e.g.,
sql> alter table hr.employee
add PARTITION employee _4 values less than (1000000)
-- add partition automatically /
add partition dynamically ( Oracle 11g onwards)
Sql> alter table
owner.partition_tablename set interval (100000);
--e.g.,
alter table hr.employee set
interval (500000);
Click Here to view more about Table partitioning
Click here to read Top 10 best practices for performance tuning
Do's
Ø Use the
cursor FOR loop – Whenever you need to read through every record fetched by a
cursor, use the for loop instead of open cursor, fetch & close cursor steps
Ø Work
with records – Always fetch from an explicit cursor into a record declared with
%ROWTYPE, as opposed to individual variables.
Ø Write
small programming blocks for all the queries/code which is re-used and call
these programs in the procedures / functions instead of re-coding.
Ø Use the
%TYPE or %ROWTYPE declaration attributes to define the data type of those of
variables to avoid data type mismatch. {If those database elements change,
compiled code is discarded. When recompiled, the changes are automatically
applied to the code}.
Ø Use
Bulk Collects, Bulk Fetch if the result for Select Stmt fetches more data
Ø Use
bind variables for comparing known values in the where clause of the queries.
For eg. Where status=1 can be reframed as status=:a where a stores 1.
Ø Coding
a few simple queries in place of a single complex query is a better approach,
because the individual SQL statements are easier to optimize and maintain.
Ø Using
if-else-end if conditions, write multiple queries instead of single query in
which the columns are compared with null or not null values,
Ø Use
UNION ALL instead of UNION if the result set of both the queries are same
Ø Use
‘For all’ Statements for repeated DML statements with an exception of using it
cautiously for update statements
Ø Use
DECODE and CASE - Performing complex aggregations with the “decode” or
"case" functions can minimize the number of times a table has to be
selected
Don’ts
Ø Avoid
the LIKE predicate – Always replace a "like" with an equality, when
appropriate
Ø Avoid
the use of NOT IN or HAVING. Instead, a NOT EXISTS subquery may run faster
Ø Never
mix data types - If a WHERE clause column predicate is numeric, do not to use
quotes. For char index columns, always use quotes. There are mixed data type
predicates: eg. Where cust_nbr = “123”,
Where substr(ssn,7,4)= 1234
Ø Avoid
IN/NOT IN and use EXISTS/NOT EXISTS
Ø Do not
use “SELECT *” under any circumstances inside application code
Ø Do not
use count(*). Replace count(*) with count(rowid)
Ø Avoid
using unnecessary brackets. Unnecessary brackets increase parsing time and
adversely affect query performance
Ø Avoid
usage of != or <> (not equal to) operators, instead use other relational
operators like ‘>’ and ‘<’
Sure it will help. Don't forget to post comments. I hope sure we will post.
