QUERY OPTIMIZATION

CONTENTS

Introduction........................................................................................................................5
Optimizer Operations........................................................................................................6
Evaluation of Expressions and Conditions..................................................................6
Transforming and Optimizing Statements..................................................................9
Optimizer Modes.............................................................................................................11
Cost-Based Optimization............................................................................................11
Rule-Base Optimization..............................................................................................11
Choosing an Optimization Mode................................................................................11
Examine RULE-Based versus COST-Based Optimization......................................12
Choosing Access Paths....................................................................................................14
Explanation of the Access Paths.................................................................................15
Path 1: Single Row by Rowid.................................................................................15
Path 2: Single Row by Cluster Join........................................................................15
Path 3: Single Row by Hash Cluster Key with Unique or Primary Key............16
Path 4: Single Row by Unique or Primary Key....................................................17
Path 5: Clustered Join.............................................................................................17
Path 6: Hash Cluster Key........................................................................................18
Path 7: Indexed Cluster Key...................................................................................18
Path 8: Composite Index.........................................................................................19
Path 9: Single-Column Indexes..............................................................................20
Path 10: Bounded Range Search on Indexed Columns........................................21
Path 11: Unbounded Range Search on Indexed Columns...................................22
Path 12: Sort-Merge Join........................................................................................22
Path 13: MAX or MIN of Indexed Column...........................................................23
Path 14: ORDER BY on Indexed Column............................................................24
Path 15: Full Table Scan.........................................................................................25
Choosing Among Access Paths...................................................................................25
Choosing an Access Path with the Cost-Based Optimization Mode...................25
Choosing an Access Path with the Rule-Based Optimization Mode...................26
Access Methods................................................................................................................28
Ways of Table Access..................................................................................................28
Table Access Full.....................................................................................................28
Table Access By ROWID........................................................................................28
Ways of index access....................................................................................................28
Index Unique Scan...................................................................................................28
Index Range Scan....................................................................................................28
Cluster Scans................................................................................................................29
Hash Scans....................................................................................................................29
Optimizing Join Statements............................................................................................29
Sort-Merge Join..........................................................................................................29
Nested Loops................................................................................................................30
Hash Join......................................................................................................................30
Cluster Join..................................................................................................................30
Tips on Optimization :.....................................................................................................31
How does ORACLE make use of an Index...............................................................31
How does ORACLE make use of multiple Indexes..................................................32
Ensure the Leading Column of a Concatenated Index is used................................32
When to avoid use of Indexes.....................................................................................34
Beware of Index Suppression....................................................................................34
Beware of Oracle internal suppression......................................................................36
Avoid using inequalities along with an indexed column..........................................37
If the MAX or MIN function is Used........................................................................37
Improve Subquery Execution.....................................................................................38
Force the Driving Table..............................................................................................39
Joining three or more tables.......................................................................................40
Equality and range predicates....................................................................................41
No clear ranking winner.............................................................................................41
Automatic index suppression......................................................................................42
Reducing the Number of Trips to the Database.......................................................42
Using DECODE to reduce processing........................................................................43
Using WHERE in place of HAVING.........................................................................44
Consider Table Joins in place of EXISTS.................................................................44
Hinting for a better plan.................................................................................................45
Examining Hint Syntax...............................................................................................45
Understanding the Available Hints............................................................................46
Explanation of some the hints...................................................................................47
INDEX Hint..............................................................................................................48
INDEX_ASC Hint....................................................................................................48
INDEX_DESC Hint.................................................................................................49
FULL Hint................................................................................................................50
ROWID Hint............................................................................................................50
FIRST_ROWS Hint.................................................................................................50
ALL_ROWS Hint....................................................................................................51
CHOOSE Hint.........................................................................................................51
RULE Hint...............................................................................................................52
AND_EQUAL Hint.................................................................................................52
APPENDIX A...................................................................................................................53
Using the Explain Plan Feature..................................................................................53
Showing the execution plan for a query....................................................................53
Execution Plan Operations.........................................................................................55
APPENDIX B...................................................................................................................57
Using SQL Trace and TKPROF................................................................................57
Taking care of prerequisites.......................................................................................57
Checking Initialization Parameters...........................................................................57
Finding Your Trace Files............................................................................................58
Enabling the SQL Trace feature................................................................................59
Enabling SQL Trace for Your Session......................................................................59
Enabling SQL Trace for Another Session.................................................................59
Enabling SQL Trace for All Sessions.........................................................................60
 Using the TKPROF command....................................................................................61
Using TKPROF Syntax...............................................................................................61
Executing TKPROF.....................................................................................................62
Explaining Plans..........................................................................................................63
Interpreting TKPROF’s output.................................................................................63
APPENDIX C...................................................................................................................66
Using SQL*Plus Autotrace.........................................................................................66
Introduction

Optimization is the process of choosing the most efficient way to execute a SQL
statement. This is an important step in the processing of any data manipulation language
(DML) statement: SELECT, INSERT, UPDATE, or DELETE. Many different ways to
execute a SQL statement often exist, for example, by varying the order in which tables or
indexes are accessed. The procedure Oracle uses to execute a statement can greatly affect
how quickly the statement executes.

A part of the Oracle kernel, the optimizer examines each SQL statement it encounters in
your application and chooses the optimal execution plan, or access path for the statement.
Execution Plan are sequence of physical steps the RDBMS must take to perform the
operation (e.g. retrieval, update) that you have specified.

While querying the database, you should be aware of the operations Oracle performs to
retrieve and manipulate the data. The better you understand the execution path, the better
you will be able to manipulate and tune the query.

You can discover the execution plan for a statement in at least three ways. One is to issue
an EXPLAIN PLAN statement from SQL*Plus. Another alternative is to use the
SQL*Plus autotrace feature, and still another alternative is to use Oracle’s SQL Trace
feature.

Oracle bases the execution of every SQL statement on an execution plan. An execution
plan is created internally by Oracle during the SQL statement parsing process. The
execution plan tells Oracle what path to follow to process a SQL statement to return the
requested result set. Oracle’s optimizer determines the execution plan based on the
manner in which the SQL statement is written and varies based on the optimizer mode
used.
Optimizer Operations

For any SQL statement processed by Oracle, the optimizer does the following:

Evaluation of Expressions and Conditions

The optimizer fully evaluates expressions whenever possible and translates certain
syntactic constructs into equivalent constructs. The reason for this is either that Oracle
can more quickly evaluate the resulting expression than the original expression, or that
the original expression is merely a syntactic equivalent of the resulting expression.

The following sections discuss how the optimizer evaluates expressions and conditions
that contain:

 Constants
Computation of constants is performed only once, when the statement is
optimized, rather than each time the statement is executed.

Consider these conditions that test for monthly salaries greater than 2000:

sal > 24000/12

sal > 2000

sal*12 > 24000

If a SQL statement contains the first condition, the optimizer simplifies it into the
second condition.

Note that the optimizer does not simplify expressions across comparison
operators. In the examples above, the optimizer does not simplify the third
expression into the second. For this reason, application developers should write
conditions that compare columns with constants whenever possible, rather than
conditions with expressions involving columns.

 LIKE Operator
The optimizer simplifies conditions that use the LIKE comparison operator to
compare an expression with no wildcard characters into an equivalent condition
that uses an equality operator instead. For example, the optimizer simplifies the
first condition below into the second.
 ename LIKE 'SMITH'

ename = 'SMITH'

 IN Operator
The optimizer expands a condition that uses the IN comparison operator to and
equivalent condition that uses equality comparison operators and OR logical
operators.

For example, the optimizer expands the first condition below into the second:

ename IN ('SMITH', 'KING', 'JONES')

ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES'

 ANY Operator
The optimizer expands a condition that uses the ANY comparison operator
followed by a parenthesized list of values into an equivalent condition that uses
equality comparison operators and OR logical operators.

For example, the optimizer expands the first condition below into the second.

sal > ANY (first_sal, second_sal)

sal > first_sal OR sal > second_sal

 ALL Operator
The optimizer expands a condition that uses the ALL comparison operator
followed by a parenthesized list of values into an equivalent condition that uses
equality comparison operators and AND logical operators.

For example, the optimizer expands the first condition below into the second:

sal > ALL (first_sal, second_sal)

sal > first_sal AND sal > second_sal

 BETWEEN Operator
 The optimizer always replaces a condition that uses the BETWEEN comparison
operator with an equivalent condition that uses the >= and <= comparison
operators.
For example, the optimizer replaces the first condition below with the second:

sal BETWEEN 2000 AND 3000

sal >= 2000 AND sal <= 3000

 Transitivity
If two conditions in the WHERE clause involve a common column, the optimizer
can sometimes infer a third condition using the transitivity principle. The
optimizer can then use the inferred condition to optimize the statement. The
inferred condition could potentially make available an index access path that was
not made available by the original conditions.

Imagine a WHERE clause containing two conditions of these forms:

WHERE column1 comp_oper constant

AND column1 = column2

In this case, the optimizer infers the condition:

column2 comp_oper constant

where:

comp_oper is any of the comparison operators =, !=, ^=, <, <>, >, <=, or >=.

constant is any constant expression involving operators, SQL functions, literals,

bind variables, and correlation variables.

Consider this query in which the WHERE clause contains two conditions, each or
which uses the [Link] column:

SELECT *
FROM emp, dept
WHERE [Link] = 20
AND [Link] = [Link];

Using transitivity, the optimizer infers this condition:

[Link] = 20
 If an index exists on the [Link] column, this condition makes available
access paths using that index.

Transforming and Optimizing Statements

SQL is a very flexible query language; there are often many statements you could
formulate to achieve the same goal. Sometimes the optimizer transforms one such
statement into another that achieves the same goal if the second statement can be
executed more efficiently.

 Transforming Complex Statements into Join Statements

To optimize a complex statement, the optimizer chooses one of these alternatives.

 Transform the complex statement into an equivalent join statement and then
optimize the join statement.
 Optimize the complex statement as is.

The optimizer transforms a complex statement into a join statement whenever the
resulting join statement is guaranteed to return exactly the same rows as the
complex statement.

Consider this complex statement that selects all rows from the ACCOUNTS table
whose owners appear in the CUSTOMERS table:

SELECT *
FROM accounts
WHERE custno IN
(SELECT custno FROM customers);

If the CUSTNO column of the CUSTOMERS table is a primary key or has a

UNIQUE constraint, the optimizer can transform the complex query into this join
statement that is guaranteed to return the same data:

SELECT accounts.*
FROM accounts, customers
WHERE [Link] = [Link];

 Optimizing Statements That Access Views

To optimize a statement that accesses a view, the optimizer transforms the
statement into an equivalent statement that accesses the view's base tables, then
optimize the resulting statement. The optimizer merges the view's query into the
referencing query block in the accessing statement. To merge the view's query
into a referencing query block in the accessing statement, the optimizer replaces
the name of the view with the names of its base tables in the query block and adds
the condition of the view's query's WHERE clause to the accessing query block's
WHERE clause.

Consider this view of all employees who work in department 10:

CREATE VIEW emp_10

AS SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE deptno = 10;

Consider this query that accesses the view. The query selects the IDs greater than
7800 of employees who work in department 10:

SELECT empno
FROM emp_10
WHERE empno > 7800;

The optimizer transforms the query into the following query that accesses the
view's base table:

SELECT empno
FROM emp
WHERE deptno = 10
AND empno > 7800;

If there are indexes on the DEPTNO or EMPNO columns, the resulting WHERE
clause makes them available.
Optimizer Modes

 Cost-based
 Rule-based

Cost-Based Optimization

Using the cost-based optimization mode, the optimizer determines which execution plan
is most efficient by considering available access paths and factoring in information based
on statistics for the schema objects (tables or indexes) accessed by the SQL statement.
The cost-based optimization mode also considers hints, which are optimization
suggestions placed in a comment in the statement.

Conceptually, the cost-based mode consists of these steps:

 The optimizer generates a set of potential execution plans for the SQL statement
based on its available access paths and hints.
 The optimizer estimates the cost of each execution plan based on statistics in the
data dictionary for the data distribution and storage characteristics of the tables,
indexes, and partitions accessed by the statement. The statistics can be available
once the table has been analyzed via the ANALYZE command. The cost is an
estimated value proportional to the expected resource use needed to execute the
statement with a particular execution plan. The optimizer calculates the cost of
each possible access method and join order based on the estimated computer
resources, including I/O, CPU time, and memory, that are required to execute the
statement using the plan.
 The optimizer compares the costs of the execution plans and chooses the one with
the smallest cost.

Rule-Base Optimization

Using the rule-based mode, the optimizer chooses an execution plan based on the access
paths available and the ranks of these access paths (shown in Table 1).

Choosing an Optimization Mode

The optimizer's behavior when choosing an optimization mode for a SQL statement is
affected by these factors:

 The OPTIMIZER_MODE initialization parameter

  The statistics in the data dictionary
 The OPTIMIZER_GOAL parameter of the ALTER SESSION command
 The hints (comments) in the SQL statement

The OPTIMIZER_MODE and the OPTIMIZER_GOAL Parameter can have the

following values:

 CHOOSE
The optimizer chooses between a cost-based optimization mode and a rule-based
optimization mode based on whether statistics are available for the cost-based
optimization mode. If the data dictionary contains statistics for at least one of the
accessed tables, the optimizer uses a cost-based optimization mode and optimizes
with a goal of best throughput. If the data dictionary contains no statistics for any
of the accessed tables, the optimizer uses a rule-based optimization mode.

 ALL_ROWS
The optimizer uses a cost-based optimization mode for all SQL statements in the
session regardless of the presence of statistics and optimizes with a goal of best
throughput (minimum resource use to complete the entire statement).

 FIRST_ROWS
The optimizer uses a cost-based optimization mode for all SQL statements in the
session regardless of the presence of statistics and optimizes with a goal of best
response time (minimum resource use to return the first row of the result set).

 RULE
The optimizer chooses a rule-based optimization mode for all SQL statements
issued to the Oracle instance regardless of the presence of statistics.

Examine RULE-Based versus COST-Based Optimization

You can determine the optimization method in your environment by viewing the system
parameter setting in the [Link].

SELECT name,value
FROM v$parameter
WHERE name='optimizer_mode';

NAME VALUE
--------------- ---------------
Optimizer_mode CHOOSE
The following is an example which displays the difference in optimizer mode execution
plan.

Example 1.
The breakdown of the deptid values for the records in s_employee table are shown below.
An index exists on the deptid column.

DEPTID Number of records

-------------- ------------------------
10 10
20 1
30 14

Oracle uses rule-based optimizer as the s_employee table has not been analyzed.

SELECT empid, start_date, title

FROM s_employee
WHERE deptid = 10;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_EMPLOYEE'
2 1 INDEX (RANGE SCAN) OF 'S_EMP_DEPTID' (NON-UNIQUE)

Example 2.
In the following example Oracle uses cost-based optimization as the s_employee table
has been analyzed using

ANALYZE TABLE s_employee COMPUTE STATISTICS

SELECT empid, start_date,title

FROM s_employee
WHERE deptid = 10;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=9 Bytes=126)
1 0 TABLE ACCESS (FULL) OF 'S_EMPLOYEE' (Cost=1 Card=9 Bytes=1
26)
The cost-based optimization logs the statistical information in the data dictionary when
the objects are analyzed. It decided to pass on using the index and perform a full table
scan as the deptid of 10 comprised 40 percent of the data.

Choosing Access Paths

One of the most important choices the optimizer makes when formulating an execution
plan is how to retrieve data from the database. For any row in any table accessed by a
SQL statement, there may be many access paths by which that row can be located and
retrieved. The optimizer chooses one of them.

Table 1: Access Paths

Rank Path
1 Single Row by Rowid
2 Single Row by Cluster Join
3 Single Row by Hash Cluster Key with Unique or Primary Key
4 Single Row by Unique or Primary Key
5 Clustered Join
6 Hash Cluster Key
7 Indexed Cluster Key
8 Composite index
9 Single-Column Indexes
10 Bounded Range Search on Indexed Columns
11 Unbounded Range Search on Indexed Columns
12 Sort merge join
13 MAX or MIN of Indexed Column
14 ORDER BY on Indexed Column
15 Full tables scans
Explanation of the Access Paths

Path 1: Single Row by Rowid

This access path is available only if the statements WHERE clause identifies the selected
rows by rowid

This access path is available in the following statement:

SELECT *
FROM DEPT
WHERE ROWID = 'AAACqoAACAAAAD9AAA';

The Execution Plan for this statement might look like this:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY USER ROWID) OF 'DEPT'

Path 2: Single Row by Cluster Join

This access path is available for statements that join tables stored in the same cluster if
both of these conditions are true:

 The statement's WHERE clause contains conditions that equate each column of
the cluster key in one table with the corresponding column in the other table.
 The statement's WHERE clause also contains a condition that guarantees that the
join returns only one row. Such a condition is likely to be an equality condition on
the column(s) of a unique or primary key.

These conditions must be combined with AND operators.

This access path is available for the following statement in which the EMP and DEPT
tables are clustered on the DEPTNO column and the EMPNO column is the primary key
of the EMP table:

SELECT *
FROM EMP, dept
WHERE [Link] = [Link]
AND [Link] = 7900;

The Execution Plan for this statement might look like this:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
3 2 INDEX (UNIQUE SCAN) OF ‘PK_EMP' (UNIQUE)
4 1 TABLE ACCESS (CLUSTER) OF 'DEPT'

PK_EMP is the name of an index that enforces the primary key.

Path 3: Single Row by Hash Cluster Key with Unique or Primary Key

This access path is available if both of these conditions are true:

 The statement's WHERE clause uses all columns of a hash cluster key in equality
conditions. For composite cluster keys, the equality conditions must be combined
with AND operators.
 The statement is guaranteed to return only one row because the columns that
make up the hash cluster key also make up a unique or primary key.

To execute the statement, Oracle applies the cluster's hash function to the hash cluster
key value specified in the statement to obtain a hash value. Oracle then uses the hash
value to perform a hash scan on the table.

This access path is available in the following statement in which the ORDERS and
LINE_ITEMS tables are stored in a hash cluster, and the ORDERNO column is both the
cluster key and the primary key of the ORDERS table:

SELECT *
FROM orders
WHERE orderno = 65118968;

The Execution Plan for this statement might look like this:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (HASH) OF 'ORDERS'

Path 4: Single Row by Unique or Primary Key

This access path is available if the statement's WHERE clause uses all columns of a
unique or primary key in equality conditions. For composite keys, the equality conditions
must be combined with AND operators. To execute the statement, Oracle performs a
unique scan on the index or the unique or primary key to retrieve a single rowid and then
accesses the table by that rowid.

This access path is available in the following statement in which the EMPNO column is
the primary key of the EMP table:

SELECT *
FROM EMP
WHERE empno = 7900;

The Execution Plan for this statement might look like this:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)

PK_EMP is the name of the index that enforces the primary key.

Path 5: Clustered Join

This access path is available for statements that join tables stored in the same cluster if
the statement's WHERE clause contains conditions that equate each column of the cluster
key in one table with the corresponding column in the other table. For a composite cluster
key, the equality conditions must be combined with AND operators. To execute the
statement, Oracle performs a nested loops operation.

This access path is available in the following statement in which the EMP and DEPT
tables are clustered on the DEPTNO column:
SELECT *
FROM emp, dept
WHERE [Link] = [Link];

The Execution Plan for this statement might look like this:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'DEPT'
3 1 TABLE ACCESS (CLUSTER) OF 'EMP'

Path 6: Hash Cluster Key

This access path is available if the statement's WHERE clause uses all the columns of a
hash cluster key in equality conditions. For a composite cluster key, the equality
conditions must be combined with AND operators. To execute the statement, Oracle
applies the cluster's hash function to the hash cluster key value specified in the statement
to obtain a hash value. Oracle then uses this hash value to perform a hash scan on the
table.

This access path is available for the following statement in which the ORDERS and
LINE_ITEMS tables are stored in a hash cluster and the ORDERNO column is the
cluster key:

SELECT *
FROM line_items
WHERE orderno = 65118968;

The Execution Plan for this statement might look like this:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (HASH) OF 'LINE_ITEMS'

Path 7: Indexed Cluster Key

This access path is available if the statement's WHERE clause uses all the columns of an
indexed cluster key in equality conditions. For a composite cluster key, the equality
conditions must be combined with AND operators. To execute the statement, Oracle
performs a unique scan on the cluster index to retrieve the rowid of one row with the
specified cluster key value. Oracle then uses that rowid to access the table with a cluster
scan. Since all rows with the same cluster key value are stored together, the cluster scan
requires only a single rowid to find them all.

This access path is available in the following statement in which the EMP table is stored
in an indexed cluster and the DEPTNO column is the cluster key:

SELECT * FROM emp

WHERE deptno = 10;

The Execution Plan for this statement might look like this:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (CLUSTER) OF 'EMP'
2 1 INDEX (UNIQUE SCAN) OF 'EMP_DEPT_CLUSTER_INDEX'

EMP_DEPT_CLUSTER_INDEX is the name of the cluster index.

Path 8: Composite Index

This access path is available if the statement's WHERE clause uses all columns of a
composite index in equality conditions combined with AND operators. To execute the
statement, Oracle performs a range scan on the index to retrieve rowids of the selected
rows and then accesses the table by those rowids.

This access path is available in the following statement in which there is a composite
index on the JOB and DEPTNO columns:

SELECT *
FROM emp
WHERE job = 'CLERK'
AND deptno = 30;

The Execution Plan output for this statement might look like this:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF ' JOB_DEPTNO_INDEX ' (NON-UNIQUE)

JOB_DEPTNO_INDEX is the name of the composite index on the JOB and DEPTNO
columns.

Path 9: Single-Column Indexes

This access path is available if the statement's WHERE clause uses the columns of one or
more single-column indexes in equality conditions. For multiple single-column indexes,
the conditions must be combined with AND operators. If the WHERE clause uses the
column of only one index, Oracle executes the statement by performing a range scan on
the index to retrieve the rowids of the selected rows and then accessing the table by these
rowids.

This access path is available in the following statement in which there is an index on the
JOB column of the EMP table:

SELECT *
FROM emp
WHERE job = 'ANALYST';

The Execution Plan for this statement might look like this:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_EMPLOYEE'
2 1 INDEX (RANGE SCAN) OF ' JOB_INDEX ' (NON-UNIQUE)

JOB_INDEX is the index on [Link].

If the WHERE clauses uses columns of many single-column indexes, Oracle executes the
statement by performing a range scan on each index to retrieve the rowids of the rows
that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids
of rows that satisfy all conditions. Oracle then accesses the table using these rowids.

Oracle can merge up to five indexes. If the WHERE clause uses columns of more than
five single-column indexes, Oracle merges five of them, accesses the table by rowid, and
then tests the resulting rows to determine whether they satisfy the
remaining conditions before returning them.

Path 10: Bounded Range Search on Indexed Columns

This access path is available if the statement's WHERE clause contains a condition that
uses either the column of a single-column index or one or more columns that make up a
leading portion of a composite index:

column = expr

column >[=] expr AND column <[=] expr

column BETWEEN expr AND expr

column LIKE 'c%'

Each of these conditions specifies a bounded range of indexed values that are accessed by
the statement. The range is said to be bounded because the conditions specify both its
least value and its greatest value. To execute such a statement, Oracle performs a range
scan on the index and then accesses the table by rowid.

This access path is available in this statement in which there is an index on the SAL
column of the EMP table:

SELECT *
FROM emp
WHERE sal BETWEEN 2000 AND 3000;

The Execution Plan for this statement might look like this:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'SAL_IDX' (NON-UNIQUE)

SAL_IDX is the name of the index on [Link].

Path 11: Unbounded Range Search on Indexed Columns

This access path is available if the statement's WHERE clause contains one of these
conditions that use either the column of a single-column index or one or more columns of
a leading portion of a composite index:

WHERE column >[=] expr

WHERE column <[=] expr

Each of these conditions specifies an unbounded range of index values accessed by the
statement. The range is said to be unbounded because the condition specifies either its
least value or its greatest value, but not both. To execute such a statement, Oracle
performs a range scan on the index and then accesses the table by rowid.

This access path is available in the following statement in which there is an index on the
SAL column of the EMP table:

SELECT *
FROM emp
WHERE sal > 2000;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'SAL_IDX' (NON-UNIQUE)

SAL_IDX is the name of the index on [Link].

Path 12: Sort-Merge Join

This access path is available for statements that join tables that are not stored together in
a cluster if the statement's WHERE clause uses columns from each table in equality
conditions. To execute such a statement, Oracle uses a sort-merge operation. Oracle can
also use a nested loops operation to execute a join statement.

This access path is available for the following statement in which the EMP and DEPT
tables are not stored in the same cluster:

SELECT *
FROM emp, dept
WHERE [Link] = [Link];
The Execution Plan for this statement might look like this:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF ' EMP '
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF ' DEPT '

Path 13: MAX or MIN of Indexed Column

This access path is available for a SELECT statement for which all of these conditions
are true:

 The query uses the MAX or MIN function to select the maximum or minimum
value of either the column of a single-column index or the leading column of a
composite index.
 There are no other expressions in the select list.
 The statement has no WHERE clause or GROUP BY clause.

To execute the query, Oracle performs a range scan of the index to find the maximum or
minimum indexed value. Since only this value is selected, Oracle need not access the
table after scanning the index.

This access path is available for the following statement in which there is an index on the
SAL column of the EMP table:

SELECT MAX(sal) FROM emp;

The Execution Plan for this statement might look like this:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'SAL_IDX' (NON-UNIQUE)
Path 14: ORDER BY on Indexed Column

This access path is available for a SELECT statement for which all of these conditions
are true:

 The query contains an ORDER BY clause that uses either the column of a single-
column index or a leading portion of a composite index. The index cannot be a
cluster index.
 There must be a PRIMARY KEY or NOT NULL integrity constraint that
guarantees that at least one of the indexed columns listed in the ORDER BY
clause contains no nulls.

To execute the query, Oracle performs a range scan of the index to retrieve the rowids of
the selected rows in sorted order. Oracle then accesses the table by these rowids.

This access path is available for the following statement in which there is a primary key
on the EMPNO column of the EMP table:

SELECT *
FROM emp
ORDER BY empno;

The Execution Plan for this statement might look like this:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (FULL SCAN) OF 'PK_EMP' (UNIQUE)

PK_EMP is the name of the index that enforces the primary key. The primary key
ensures that the column does not contain nulls.
Path 15: Full Table Scan

This access path is available for any SQL statement, regardless of its WHERE clause
conditions.

This statement uses a full table scan to access the EMP table:

SELECT *
FROM emp;

The Execution Plan for this statement might look like this:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMP'

Choosing Among Access Paths

This section describes how the optimizer chooses among available access paths when
using the cost-based or rule-based optimization mode.

Choosing an Access Path with the Cost-Based Optimization Mode

With the cost-based optimization mode, the optimizer chooses an access path based on
these factors:
 The available access paths for the statement
 The estimated cost of executing the statement using each access path or
combination of paths

To choose an access path, the optimizer first determines which access paths are available
by examining the conditions in the statement's WHERE clause. The optimizer then
generates a set of possible execution plans using available access paths and estimates the
cost of each plan using the statistics for the index, columns, and tables accessible to the
statement. The optimizer then chooses the execution plan with the lowest estimated cost.

To choose among available access paths, the optimizer considers the Selectivity which is
the percentage of rows in the table that the query selects. A query that selects a small
percentage of a table's rows has good selectivity, while a query that selects a large
percentage of rows has poor selectivity.
The optimizer is more likely to choose an index scan over a full table scan for a query
with good selectivity than for one with poor selectivity. Index scans are usually more
efficient than full table scans for queries that access only a small percentage of a table's
rows, while full table scans are usually faster for queries that access a large percentage.

To determine the selectivity of a query, the optimizer considers these sources of

information:

 The operators used in the WHERE clause

 Unique and primary key columns used in the WHERE clause
 Statistics for the table

The examples below illustrate how the optimizer uses selectivity.

Consider this query, which uses an equality condition in its WHERE clause to select all
employees named Jackson:

SELECT *
FROM emp
WHERE ename = 'JACKSON';

If the ENAME column is a unique or primary key, the optimizer determines that there is
only one employee named Jackson, and the query returns only one row. In this case, the
query is very selective, and the optimizer is most likely to access the table using a unique
scan on the index that enforces the unique or primary key (access path 4).

Choosing an Access Path with the Rule-Based Optimization Mode

With the rule-based optimization mode, the optimizer chooses whether to use an access
path based on these factors:

 The available access paths for the statement

 The ranks of these access paths, as shown in Table 1

To choose an access path, the optimizer first examines the conditions in the statement's
WHERE clause to determine which access paths are available. The optimizer then
chooses the most highly ranked available access path.

Note that the full table scan is the lowest ranked access path on the list. This means that
the rule-based optimization mode always chooses an access path that uses an index if one
is available, even if a full table scan might execute faster.

The order of the conditions in the WHERE clause does not normally affect the
optimizer's choice among access paths.
Consider this SQL statement, which selects the employee numbers of all employees in
the EMP table with an ENAME value of 'CHUNG' and with a SAL value greater than
2000.

SELECT empno
FROM emp
WHERE ename = 'CHUNG'
AND sal > 2000;

Consider also that the EMP table has these integrity constraints and indexes:

There is a PRIMARY KEY constraint on the EMPNO column that is enforced by the
index PK_EMPNO. There is an index named ENAME_IND on the ENAME column.
There is an index named SAL_IND on the SAL column.

Based on the conditions in the WHERE clause of the SQL statement, the integrity
constraints, and the indexes, these access paths are available:

 A single-column index access path using the ENAME_IND index is made

available by the condition ENAME = 'CHUNG'. This access path has rank 9.
 An unbounded range scan using the SAL_IND index is made available by the
condition SAL > 2000. This access path has rank 11.
 A full table scan is automatically available for all SQL statements. This access
path has rank 15.

Note that the PK_EMPNO index does not make the single row by primary key access
path available because the indexed column does not appear in a condition in the WHERE
clause.

Using the rule-based optimization mode, the optimizer chooses the access path that uses
the ENAME_IND index to execute this statement. The optimizer chooses this path
because it is the most highly ranked path available.
Access Methods

This section describes the basic methods by which Oracle can access data.

Ways of Table Access

 Table Access Full

A full table scan sequentially reads each row of a table. It is used whenever there is
no where clause in the query.

 Table Access By ROWID

To improve performance of table access we can access rows by their ROWID
pseudo-column values. It is used to return the result set quickly.

Ways of index access

Use an index scan over a table scan when accessing less than 5 percent of the rows of a
table.

 Index Unique Scan

A unique scan of an index returns only a single rowid. Oracle performs a unique scan
only in cases in which a single rowid is required, rather than many rowids. For
example, Oracle performs a unique scan if there is a UNIQUE or a PRIMARY KEY
constraint that guarantees that the statement accesses only a single row.

SELECT *
FROM s_employee
WHERE empid = 1;

 Index Range Scan

If you query the database based on a range of values, or if you query using a
nonunique index, then an Index Range Scan is used to query the index.

SELECT *
FROM s_employee
WHERE LNAME LIKE ‘S%’;
Cluster Scans

From a table stored in an indexed cluster, a cluster scan retrieves rows that have the same
cluster key value. In an indexed cluster, all rows with the same cluster key value are
stored in the same data blocks. To perform a cluster scan, Oracle first obtains the rowid
of one of the selected rows by scanning the cluster index. Oracle then locates the rows
based on this rowid.

Hash Scans

Oracle can use a hash scan to locate rows in a hash cluster based on a hash value. In a
hash cluster, all rows with the same hash value are stored in the same data blocks. To
perform a hash scan, Oracle first obtains the hash value by applying a hash function to a
cluster key value specified by the statement. Oracle then scans the data blocks containing
rows with that hash value.

Optimizing Join Statements

The optimizer can use the following operations to join two row sources:

Sort-Merge Join
Oracle can only perform a sort-merge join for an equijoin. To perform a sort-merge join,
Oracle follows these steps:

 Oracle sorts each row source to be joined . The rows are sorted on the values of
the columns used in the join condition.
 Oracle merges the two sources so that each pair of rows, one from each source,
that contain matching values for the columns used in the join condition are
combined and returned as the resulting row source.

Consider the following statement.

SELECT [Link], [Link]

FROM dept a , emp b
WHERE [Link] = [Link];

To execute this statement, Oracle performs these steps:

 Perform full table scans of the EMP and DEPT tables.
 Sort each row source separately.
 Merges both the sources .
 Returns the resulting row source.
Nested Loops
Nested Loops operations join two tables via a looping method. The records from one
table are retrieved, and for each record retrieved, an access is performed of the second
table. The access of the second table is performed via an index-based access.
In order for a Nested Loops join to be used, an index must be available for use with the
query. In the following query, a primary key is created on the on the deptno column of
the emp table.

SELECT [Link], [Link]

FROM dept a , emp b
WHERE [Link] = [Link];

To execute Nested Loops join, the optimizer must first select a driving table for the join.
The driving table is the table that will be read first (usually via a TABLE ACCESS FULL
operation). For each record in the driving table, the second table in the join will be
queried. Since an index is available on the deptno column of the emp table , and no
comparable index is available on the dept table, the dept table will be used as the driving
table for the query. During the Nested Loops execution, a TABLE ACCESS FULL
operation will select all the records from the dept table. The index available on the deptno
column of the emp table will be probed to determine if it contains an entry for the value
in the current record from the dept table. If a match is found , then the ROWID for the
matching emp row will be retrieved from the index, and the row will be selected from the
emp table via a TABLE ACCESS BY ROWID operation.

Hash Join
The Hash Join operation compares two tables in memory. During a Hash Join operation,
the first table is scanned via a TABLE ACCESS FULL and the database applies “hashing
” functions to the data to prepare the table for the join. The values from the second table
are then read (also via a TABLE ACCESS FULL operation), and the hashing function is
used to compare the second table with the first table. The rows that result in matches are
returned to the user.

Cluster Join
Oracle can perform a cluster join only for an equijoin that equates the cluster key
columns of two tables in the same cluster. In a cluster, rows from both tables with the
same cluster key values are stored in the same blocks, so Oracle only accesses those
blocks.

SELECT *
FROM emp, dept
WHERE [Link] = [Link];
The outer table (DEPT) is scanned with a full table scan. The [Link] value is
used to find the matching rows in the inner table (EMP) with a cluster scan.

Tips on Optimization :

How does ORACLE make use of an Index

When a request is made to create an index on a column, the column(s) indexed and the
ROWID are stored in the index object. When a SQL statement is written that uses an
indexed column in the “WHERE CLAUSE”, Oracle searches the index for the record(s)
that match the column criteria. Once found, the ROWID(s) for the matched record(s) are
used to search the base table. The fastest search on a table is by ROWID because it is the
address of the location on disk. If an index contains the entire contents of the information
selected, then the SQL statement is satisfied entirely by the index contents and there is no
need to go to the table and search by ROWID. Example 3 and Example 4 below
illustrates the use of index.

Example 3.
SELECT order_id,customer_id
FROM s_order
WHERE order_id = 100;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORDER'
2 1 INDEX (UNIQUE SCAN) OF 'S_ORDER_ID_PK' (UNIQUE)

A unique index is on the order_id column of the s_order table. We can see that the
s_order table was accessed once the ROWID was retrieved from the index. In the first
line we can see that Optimizer=CHOOSE . CHOOSE means that the method of
optimization used could have been rule-based or cost-based, depending on whether the
s_order table was ever analyzed (if previously analyzed , cost based optimization is used;
otherwise rule-based optimization is used).

Example 4.
SELECT order_id
FROM s_order
WHERE order_id = 100;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (UNIQUE SCAN) OF 'S_ORDER_ID_PK' (UNIQUE)

The index has been used to satisfy the search without accessing the database table.

How does ORACLE make use of multiple Indexes

Oracle can use multiple indexes on a table. The s_employee has one index on the title
column and another index on the start_date column.

Example 5.
SELECT empid,start_date,title
FROM s_employee
WHERE title = 'CLERK'
AND start_date = '01-JAN-01';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_EMPLOYEE'
2 1 AND-EQUAL
3 2 INDEX (RANGE SCAN) OF 'EMP_IDX2' (NON-UNIQUE)
4 2 INDEX (RANGE SCAN) OF 'EMP_IDX3' (NON-UNIQUE)

The AND-EQUAL operation merges the rowids obtained by the scans of the EMP_IDX2
and the EMP_IDX3, resulting in a set of rowids of rows that satisfy the query.

Ensure the Leading Column of a Concatenated Index is used.

For a concatenated index to be used in full or in part, the leading edge of the index must
be present. If any column contained in the index is not present in the predicate clause,
then all columns of the index following that column cannot use the index. In case of a
concatenated index, the leading column should be very frequently used as a limiting
condition against the table and it should be highly selective.

Oracle can use a concatenated index, but only if the first portion of the index is in the
predicate. In the following example Oracle makes use of the concatenated index
'EMP_IDX4' on the title and start_date column
Example 6.
SELECT empid,start_date,title
FROM s_employee
WHERE title = 'CLERK'
AND start_date = '01-JAN-01';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_EMPLOYEE'
2 1 INDEX (RANGE SCAN) OF 'EMP_IDX4' (NON-UNIQUE)

Example 7.
In the following case Oracle does not make use of the index because only the second part
of the two-part index is referenced in the predicate clause.

SELECT empid,start_date,title
FROM s_employee
WHERE start_date = '01-JAN-01';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'S_EMPLOYEE'

For a concatenated index to be used in full or in part, the leading edge of the index must
be present. If any column contained in the index is not present in the predicate clause,
then all columns of the index following that column cannot use the index.

Guidelines to assist in determining appropriate indexes

 Index all primary keys.
 Index all foreign keys.
 In highly transaction-oriented environments , keep indexes to minimum because
every data INSERT, UPDATE or DELETE will require the overhead of indexes
to be updated.
When to avoid use of Indexes

Example 8.
NULL values are not stored in indexes. The following query will not make use of the
index.

SELECT *
FROM s_employee
WHERE lname is NULL;

Example 9.
If an IS NOT NULL check is performed on the column. All of the non-NULL values for
the column are stored in the index, however the index search would not be efficient. To
resolve the query ,the optimizer would need to read every value from the index and
access the table for each row returned from the index. In most cases, it would be more
efficient to perform a full table scan than to perform an index scan (with associated Table
Access By ROWID operations). The following query should not make use of an index.

SELECT *
FROM s_employee
WHERE lname IS NOT NULL;

Beware of Index Suppression

Neither rule-based or cost-based optimization can use an index if the column that is used
in the predicate clause is modified in any manner as shown below in Example 10 and
Example 11.

Example 10.
SELECT empid,lname
FROM s_employee
WHERE empid = 1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_EMPLOYEE'
2 1 INDEX (UNIQUE SCAN) OF 'S_EMPID_PK' (UNIQUE)
In the following example rule-based optimization was used and a unique index existing
on the empid column was used.

Example 11.
SELECT empid,lname
FROM s_employee
WHERE substr(empid,1,1) = 1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'S_EMPLOYEE'

A full table scan was performed. The suppression of the index will occur if any function
or modification is performed on the index column in the predicate clause including any
character, numeric or date functions.

Index suppression may be desired if you need to force the optimizer to disregard an index
in certain cases. To suppress an index , the SQL statement must be changed in a manner
to ensure the same result set. To suppress predicate conditions for numeric values , add a
0 to the column . To suppress predicate conditions for character values , add a NULL
string to the column as shown below in Example 12 and Example 13.

Example 12.
The following illustrates the suppression of numeric column.

SELECT empid,lname
FROM s_employee
WHERE empid + 0 = 1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'S_EMPLOYEE'

Example 13.
The following illustrates the suppression of character column.

SELECT empid,lname
FROM s_employee
WHERE lname || ‘ ’ = ‘TOM’;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'S_EMPLOYEE'

Beware of Oracle internal suppression.

If you are comparing mismatched data types in the predicate clause, Oracle is forced to
convert one of the values to perform the comparison as shown below in Example 14 and
Example 15.

Example 14.

The empid column is defined as a number. The following illustrates a query containing
an equality that compares the empid column to a character column.

SELECT empid,lname
FROM s_employee
WHERE empid = '1';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_EMPLOYEE'
2 1 INDEX (UNIQUE SCAN) OF 'S_EMPID_PK' (UNIQUE)

The resulting Explain Plan indicates the index was still used. When Oracle compares a
numeric value to a character value, it converts the character value to a number internally.

Example 15.
In this case the emp_dup_id column is a character column and the query contains an
equality that compares the emp_dup_id column to a numeric column.

SELECT emp_dup_id,lname
FROM emp_test
WHERE emp_dup_id = 1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMP_TEST'
The emp_dup_id column was modified internally by oracle with a TO_NUMBER
function , thus suppressing the use of the index.

Avoid using inequalities along with an indexed column

The optimizer will not make use of an Index in case of inequalities. Avoid using the
NOT IN or ‘!=’ or condition as shown below in Example 16 and Example 17.

Example 16.
SELECT *
FROM s_employee
WHERE empid != 1

Example 17.
SELECT *
FROM s_employee
WHERE lname NOT IN
(SELECT lname
FROM emp_test);

If the MAX or MIN function is Used

If you select the MAX or MIN value of an indexed column, the optimizer may use the
index to quickly find the maximum or minimum value for the column.

Example 18.

SELECT MIN(empid)
FROM s_employee;
Improve Subquery Execution
Using a correlated subquery when a subquery is involved in a SQL statement is generally
more efficient.

When multiple tables are joined in a SQL statement and a table in the join is only used in
the predicate clause and not in the SELECT clause, the statement can be converted to a
subquery to improve performance.

When a subquery is correlated, the outer query executes first, followed by the inner query
executing for each record returned from the outer query. When a subquery is non-
correlated, the inner query executes first (the result set is treated similarly as a set of
values from an IN condition), and then the outer query is executed.

When subqueries are used , using the EXISTS operator versus the IN operator is more
efficient. The EXISTS operator terminates the inner query fetching when one matching
record in the query is found, whereas the IN operator continues until all matching records
are found.

The following Example 19 and Example 20 illustrates the improvements when executing
a correlated subquery versus a non-correlated subquery.

Example 19.
The following query is a non-correlated query.

SELECT lname,fname
FROM s_employee
WHERE lname IN
(SELECT lname from emp_test);

Execution Plan
------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'S_EMPLOYEE'
4 1 SORT (JOIN)
5 4 VIEW
6 5 SORT (UNIQUE)
7 6 TABLE ACCESS (FULL) OF 'EMP_TEST'
Example 20.
The statement is modified to a correlated subquery with an EXISTS in the following
example.

SELECT lname,fname
FROM s_employee a
WHERE EXISTS
(SELECT 'X'
FROM emp_test
WHERE lname = [Link]);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'S_EMPLOYEE'
3 1 INDEX (RANGE SCAN) OF 'S_EMP_TEST_IDX' (NON-UNIQUE)

By using correlated subquery , the increased performance is evident.

Force the Driving Table

The execution plan chosen by Oracle determines a driving table based on the order
of the table names when a join condition is involved and no predicate condition
exists that leads the optimizer to choose one table over the other.

The object of all your SQL query and update statements is to minimize the total
physical number of database blocks that need to be read and /or written.

If you have two tables in the FROM clause of the statement, you will keep the
driving table last in the FROM clause.

Table Tab1 has 10000 rows

Table Tab2 has 1 rows

Example 21.
If Tab2 is selected as the driving Table

SELECT count(*)
FROM Tab1, Tab2
0.96 seconds elapsed
Example 22.
If Tab1 is selected as the driving table

SELECT count(*)
FROM Tab2, Tab1
20.96 seconds elapsed

By specifying the correct driving table, it makes a huge difference in performance.

When Oracle processes multiple tables, it uses an internal sort/merge procedure to join
the two tables. First it scans and sorts the first (driver) table. Next it scans the second
table and merges all of the rows retrieved from the second table with those retrieved from
the first table.

Joining three or more tables

If three tables are being joined , try to select the intersection table as the driving
table.

The intersection table is the table that has the most dependencies on it.

In the Example 23 below emp represents the intersection between location and
category table.

Example 23.
SELECT …
FROM location L, category C, emp E
WHERE [Link] BETWEEN 10 AND 20
AND E.cat_no = C.cat_no
AND [Link] = [Link]

This query is more efficient than the one shown in Example 24

Example 24.
SELECT …
FROM emp E , location L, category C
WHERE E.cat_no = C.cat_no
AND [Link] = [Link]
AND [Link] BETWEEN 10 AND 20
Equality and range predicates
When indexes combine both equality and range predicates over the same table, Oracle
cannot merge these indexes. In such cases it uses only the equality predicate. Each row is
individually validated against the second predicate. In the following Example 25 there is
a non-UNIQUE index over dept_no and non-UNIQUE index over emp_cat.

Example 25.
SELECT emp_name
FROM emp
WHERE dept_no > 20
AND emp_cat = ‘A’

Execution Plan
----------------------------------------------------------
Table Access By Rowid on EMP
Index Range Scan on cat_idx

The emp_cat index is used , and then each row is validated manually.

No clear ranking winner

When there is no clear index “ranking” winner, Oracle will use only one of the indexes.
In such cases, Oracle uses the first index referenced by the WHERE clause in the
statement. In the following example there is a non-UNIQUE index over dept_no and
non-UNIQUE index over emp_cat.

Example 26.
SELECT emp_name
FROM emp
WHERE dept_no > 20
AND emp_cat > ‘A’

Execution Plan
----------------------------------------------------------
Table Access By Rowid on EMP
Index Range Scan on dept_idx
Automatic index suppression
Under certain circumstances, the RDBMS kernel will actually omit particular indexes
from the query plan. Assume that the table has two (or more) available indexes and that
one index is unique and the other index is non-unique. In such cases, Oracle uses the
unique retrieval path and ignores the second option. In the following example, there is a
UNIQUE index over emp_no and non-UNIQUE index over emp_dept.

Example 27.
SELECT emp_name
FROM emp
WHERE emp_no = 10
AND emp_dept = 2;

Execution Plan
----------------------------------------------------------
Table Access By Rowid on EMP
Index Unique Scan on emp_no_idx

The emp_no index is used to fetch the row. The second predicate (emp_dept = 2) is then
manually evaluated (no index used).

Reducing the Number of Trips to the Database

Every time a SQL statement is executed, Oracle performs 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 overheads you can
save.

The example shows three ways for retrieving data about employees having emp_no 10 or
20. Method 1 is least efficient, method 2 is more efficient and method 3 is most efficient.

Method 1.
Two separate database access

SELECT *
FROM emp
WHERE emp_no = 10;

SELECT *
FROM emp
WHERE emp_no = 20;
Method 2.
One cursor and two fetches

DECLARE
CURSOR C(eno number) IS
SELECT *
FRO M emp
WHERE emp_no = eno;
BEGIN
OPEN C(10);
FETCH C INTO …;
.
.
OPEN C(20);
FETCH C INTO …;
CLOSE C;
END;

Method 3.
SQL table join

SELECT a.emp_name,b.emp_name
FROM emp a, emp b
WHERE a.emp_no = 10
AND a.emp_no = 10;

Using 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. Consider the following example.

SELECT COUNT(*),SUM(sal)
FROM emp
WHERE deptno=10
AND hiredate > ‘01-Jan-01’

SELECT COUNT(*),SUM(sal)
FROM emp
WHERE deptno=20
AND hiredate > ‘01-Jan-01’
You can achieve the same result more efficiently using DECODE

SELECT COUNT(DECODE(deptno,10,’X’,NULL)) ,
COUNT(DECODE(deptno,20,’X’,NULL)) ,
SUM(DECODE(deptno,10,sal,NULL)) ,
SUM(DECODE(deptno,20,sal,NULL))
FROM emp
WHERE hiredate > ‘01-Jan-01’;

Using WHERE in place of HAVING

Having clause filters selected rows only after all rows have been fetched. This could
include sorting, summing etc. Restricting rows via the WHERE clause helps to reduce
these overheads.

SELECT deptno,sum(sal)
FROM emp
GROUP BY deptno
HAVING DEPTNO != 40;

Using the WHERE clause would be more efficient

SELECT deptno,sum(sal)
FROM emp
WHERE DEPTNO != 40;

Consider Table Joins in place of EXISTS

Consider joining tables rather then specifying subqueries when the percentage of
successful rows returned from the driving table (i.e. the nos of rows that need to be
validated against the subquery) is high. If we are selecting records from emp table and are
required to filter those records that have department category of 'A' than a table join is
more efficient.

SELECT emp_name
FROM emp e
WHERE EXISTS
(SELECT 'X' from dept where dept_no = [Link] and dept_cat = 'A');
To improve the performance we can use the following query.

SELECT emp_name
FROM dept d ,emp e
WHERE d.dept_no = [Link]
AND d.dept_cat='A';

Hinting for a better plan

Now that you know how Oracle is planning to execute the query, you can think about
ways in which to improve performance.

One way that you can change an execution plan is to add optimizer hints to the query.
These hints take the form of specially formatted comments and tell the optimizer how
you want the query to be executed. They aren’t really hints, either, even though Oracle
refers to them as such. They’re more like commands.

All hints force the optimizer to use cost-based optimization, even if the tables being
referenced are not analyzed.

It’s true that Oracle will ignore hints that are contradictory or that are impossible to carry
out, but you’ll never see Oracle ignore a hint when it can be carried out.

Examining Hint Syntax

When you place a hint in a SQL statement, the comment needs to take a special
form, and it needs to immediately follow the verb. For example:

SELECT /*+hint comment hint hint comment ...*/

Notice the plus (+) sign immediately following the start of the comment. That plus sign
tells Oracle that the comment contains hints. The comment may contain several hints, and
you may have comments interspersed with your hints. You should separate hints from
each other, and from any non-hint text, by at least one space.

Keep the following rules in mind when writing hints:

 The comment with the hints must immediately follow the SELECT, INSERT,
UPDATE or DELETE keyword.
 For Oracle to recognize the hints, the comment must start with /*+. As an
alternative, you can use --+to start a comment containing hints. If you do this, the
 comment continues until the end of the line is reached. In practice, almost
everyone uses /*+...*/.
 Several hints allow you to reference table names and/or index names. Table and
index names are always enclosed within parentheses.
 If you alias a table in your query, you must use that alias in any hints that refer to
the table.
 If you supply contradictory hints, Oracle will ignore at least one of them.
 If your hints refer to indexes that don’t exist, Oracle will ignore them.
 If you want to control the execution of a subquery, you must place a hint in that
subquery. Hints in the main query refer to the main query. Hints in a subquery
refer to the subquery.
 If you write a hint incorrectly, Oracle will ignore it. Oracle never returns error
messages for badly written hints. Hints are embedded within comments, and to
comply with ANSI standards, error messages can’t be returned for comments.

Understanding the Available Hints

Table 2 - HINTS

Hint Description
ALL_ROWS Produces an execution plan that optimizes overall
resource usage.
AND_EQUAL(table_name Specifies to access a table by scanning two or more
Index_ name indexes and merging the results. You must specify at
index name ...) least two index names with this hint.
CHOOSE Specifies to use the cost-based optimizer if statistics
exist for any of the tables involved.
CLUSTER(table_name ) Specifies to do a cluster scan to access the specified
table. This hint is valid only for clustered tables.
DRIVING_SITE (table_name Specifies which database to use as the driving site when
). joining tables from two different databases. The driving
site will be the database in which the named table
resides
FIRST_ROWS Produces an execution plan that optimizes for a quick
Initial response.
FULL(table_name ) Specifies to access the named table by reading all
The rows.
HASH(table_name ) Specifies to do a hash scan of the named table. This hint
is valid only for hash-clustered tables.
INDEX(table_name Specifies to access the named table through an index.
[index_name...]) You may optionally specify a list of indexes from which
To choose.
INDEX_ASC(table_name Specifies an index to scan, the same as INDEX , but also
[index_name...]) specifies to scan the index in ascending order.
INDEX_COMBINE Specifies to access the table using a combination of two
(table_ name indexes. You may optionally supply a list of indexes
[index_name...]) from which to choose.
INDEX_DESC(table_name Specifies an index to scan, the same as INDEX , but also
[index_name...]) specifies to scan the index in descending order.
ORDERED Specifies to join tables from left to right, in the same
order in which they are listed in the FROM clause of the
query.
ROWID(table_name ) Specifies to access the named table using ROWIDs.
RULE Specifies to use the rule-based optimizer.
USE_CONCAT Forces combined OR conditions in the WHERE clause
of a query to be transformed into a compound query
using the UNION ALL set operator.
USE_HASH(table_name ) Specifies to use a hash join whenever the named table is
Joined to any other table.
USE_MERGE(table_name ) Specifies to use a merge join whenever the named table
Is joined to any other table.
USE_NL(table_name ) Specifies to use a nested loop when joining the named
Table to any other table. The other table will always be
The driving table.

Explanation of some the hints

In order to explain the usage of INDEX , INDEX_ASC and INDEX_DESC hints we will
make use of the following example shown below.

Example 28

SELECT *
FROM HINT_DEPT ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'HINT_DEPT'

In the above case, a FULL TABLE scan has been performed on the HINT_DEPT table.
INDEX Hint
In the above query in Example 28 is modified to make use of the INDEX hint.

Example 29.

SELECT /*+ INDEX (HINT_DEPT DEPT_PK) */ *

FROM HINT_DEPT ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=82 Bytes=22
14)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HINT_DEPT' (Cost=826 Car

d=82 Bytes=2214)

2 1 INDEX (FULL SCAN) OF 'DEPT_PK' (UNIQUE) (Cost=26 Card=82

)

In the above case the optimizer makes use of the index on deptno column to access the
hint_dept table.

INDEX_ASC Hint
In the above query in Example 28 is modified to make use of the INDEX_ASC hint.

Example 30

SELECT /*+ INDEX_ASC (HINT_DEPT DEPT_PK) */ *

FROM HINT_DEPT ;

DEPTNO DNAME LOC

--------- ---------- ----------
10 MARKETING MUMBAI
20 COMPUTERS CHENNAI
30 FINANCE DELHI
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=82 Bytes=22
14)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HINT_DEPT' (Cost=826 Car

d=82 Bytes=2214)

2 1 INDEX (FULL SCAN) OF 'DEPT_PK' (UNIQUE) (Cost=26 Card=82

)

In the above case the optimizer to make use of an ascending indexed table scan for the
hint_dept table.

INDEX_DESC Hint
In the above query in Example 28 is modified to make use of the INDEX_DESC hint.

Example 30.

SELECT /*+ INDEX_DESC (HINT_DEPT DEPT_PK) */ *

FROM HINT_DEPT ;

DEPTNO DNAME LOC

--------- ---------- ----------
30 FINANCE DELHI
20 COMPUTERS CHENNAI
10 MARKETING MUMBAI

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=82 Bytes=16
40)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HINT_DEPT' (Cost=826 Car

d=82 Bytes=1640)

2 1 INDEX (FULL SCAN DESCENDING) OF 'DEPT_PK' (UNIQUE) (Cost

=26 Card=82)

In the above case the optimizer to make use of an descending indexed table scan for the
hint_dept table.
FULL Hint
Forces the optimizer to make use of a full table scan for the specified table

Example 31.

SELECT /*+ FULL(HINT_DEPT) */ DEPTNO

FROM HINT_DEPT
WHERE DEPTNO = 10;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=13)
1 0 TABLE ACCESS (FULL) OF 'HINT_DEPT' (Cost=1 Card=1 Bytes=13
)

ROWID Hint
Forces the optimizer to make use of a table scan by ROWID for the hint_dept table.

Example 32.

SELECT /*+ ROWID(HINT_DEPT) */ DEPTNO

FROM HINT_DEPT ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=13)
1 0 INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE) (Cost=1 Card=1 B
ytes=13)

FIRST_ROWS Hint
Optimizes for best response time at a higher cost. Will always choose an index over a
Full table scan.

Example 33.

SELECT /*+ FIRST_ROWS(HINT_DEPT) */ DEPTNO

FROM HINT_DEPT ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=26 Card=82
Bytes=1066)

1 0 INDEX (FULL SCAN) OF 'DEPT_PK' (UNIQUE) (Cost=26 Card=82 B

ytes=1066)

ALL_ROWS Hint
Optimizes for best throughput to execute all rows. Cost is very less.

Example 34.

SELECT /*+ ALL_ROWS(HINT_DEPT) */ DEPTNO

FROM HINT_DEPT ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1 Card=82 By
tes=1066)

1 0 TABLE ACCESS (FULL) OF 'HINT_DEPT' (Cost=1 Card=82 Bytes=1

066)

CHOOSE Hint
Uses the Cost-based optimizer if statistics are available for atleast one table, otherwise
uses the Rule-based optimizer.

Example 35.

SELECT /*+ CHOOSE */ DEPTNO

FROM HINT_DEPT ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: CHOOSE
1 0 TABLE ACCESS (FULL) OF 'HINT_DEPT'
RULE Hint
Uses the Rule-based optimizer

Example 36.

SELECT /*+ RULE */ DEPTNO

FROM HINT_DEPT ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'HINT_DEPT'

AND_EQUAL Hint
The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that
merges the scans on the indexes on dname and loc column.

Example 37.

SELECT /*+ AND_EQUAL (HINT_DEPT DNAME_IDX LOC_IDX ) */ dname,loc

FROM HINT_DEPT
WHERE DNAME = 'FINANCE' AND LOC ='DELHI';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=14)
1 0 AND-EQUAL
2 1 INDEX (RANGE SCAN) OF 'DNAME_IDX' (NON-UNIQUE) (Cost=1 C
ard=1)

3 1 INDEX (RANGE SCAN) OF 'LOC_IDX' (NON-UNIQUE) (Cost=1 Car

d=1)
 APPENDIX A

Using the Explain Plan Feature

Oracle’s Explain Plan feature allows you to discover the execution plan for a SQL
statement. You do this by using a SQL statement, named EXPLAIN PLAN , to which
you append the query of interest. Consider this example:

EXPLAIN PLAN
SET STATEMENT_ID =‘test2 ’
FOR
SELECT [Link],[Link]
FROM emp e,dept d
WHERE [Link]=[Link];

After issuing this statement, you can query a special table known as the plan_table to
find the execution plan that Oracle intends to use for the query.

To create a plan_table use the Oracle-supplied script named [Link]. which is

available in $ORACLE_HOME/rdbms/admin directory.

Showing the execution plan for a query

The records in the plan table are related to each other hierarchically. Each record
has an ID column and a PARENT_ID column. You execute a given SQL statement
by performing a series of operations. Each operation, in turn, may consist of one
or more operations. The most deeply nested operations are executed first. They,
in turn, feed results to their parents. This process continues until only one result
is left —the result of the query —which is returned to you. Using the Plan Table Query
The three most significant columns in the plan table are named OPERATION ,
OPTIONS , and OBJECT_NAME . For each step, these tell you which operation is
going to be performed and which object is the target of that operation.

You can use the following SQL query to display an execution plan once it has
been generated:

SELECT parent_id, id,

 LPad(' ', 2*(Level-1)) || Level || '.' || Nvl(Position, 0) || ' ' ||
Operation || ' ' || Options || ' ' || Object_Name || ' ' ||
Object_Type || ' ' || Decode(id, 0, Statement_Id ||' Cost = ' ||
Position)
"Query Plan"
FROM Plan_Table
START WITH id = 0 And Statement_Id = 'test2'
CONNECT BY PRIOR Id = Parent_Id
AND Statement_Id = 'test2';

You use the CONNECT BY clause to link each operation with its parent. The LPAD
business is for indention so that if an operation has child rows, those rows will be
indented underneath the parent.

The output for the Execution Plan is shown below.

PARENT_ID ID Query Plan

---------------- --------- --------------------------------------------------
0 1.0 SELECT STATEMENT test2 Cost =
0 1 2.1 NESTED LOOPS
1 2 3.1 TABLE ACCESS FULL EMP
1 3 3.2 TABLE ACCESS BY INDEX ROWID DEPT
3 4 4.1 INDEX UNIQUE SCAN P_KEY22 UNIQUE
Execution Plan Operations

Table 3 - Execution Plan Operations

Operation Description

AND-EQUAL Operation accepting multiple sets of

rowids, returning the intersection of the
sets, eliminating duplicates. Used for the
Single-column indexes access path.
CONNECT BY Retrieves rows in hierarchical order for a
query containing a CONNECT BY
clause.
CONCATENATION Operation accepting multiple sets of rows
returning the union-all of the sets.
COUNT Operation counting the number of rows
selected from a table.
FILTER Operation accepting a set of rows,
eliminates some of them, and returns the
rest.
FIRST ROW Retrieval on only the first row selected by a
query.
FOR UPDATE Operation retrieving and locking the rows
selected by a query containing a FOR
UPDATE clause.
HASH JOIN Operation joining two sets of rows and
returning the result.
INDEX UNIQUE Retrieval of a single rowid from an index.
INDEX RANGE SCAN Retrieval of one or more rowids from an
index. Indexed values are scanned in
ascending order.
INTERSECTION Operation accepting two sets of rows and
returning the intersection of the sets,
eliminating duplicates.
MERGE JOIN Operation accepting two sets of rows, each
sorted by a specific value, combining each
row from one set with the matching rows
from the other, and returning the result.
MERGE JOIN OUTER Merge join operation to perform an outer
join statement.
MINUS Operation accepting two sets of rows and
returning rows appearing in the first set but
not in the second, eliminating duplicates.
NESTED LOOPS Operation accepting two sets of rows, an
 outer set and an inner set. Oracle compares
each row of the outer set with each row of
the inner set, returning rows that satisfy a
Condition.
REMOTE Retrieval of data from a remote database.
SEQUENCE Operation involving accessing values of a
sequence.
SORT AGGREGATE Retrieval of a single row that is the result
of applying a group function to a group of
selected rows.
SORT UNIQUE Operation sorting a set of rows to eliminate
duplicates.
SORT GROUP BY Operation sorting a set of rows into groups
for a query with a GROUP BY clause.
SORT JOIN Operation sorting a set of rows before a
merge-join.
SORT ORDER BY Operation sorting a set of rows for a query
with an ORDER BY clause.
TABLE ACCESS FULL Retrieval of all rows from a table.
TABLE ACCESS CLUSTER Retrieval of rows from a table based on a
value of an indexed cluster key
TABLE ACCESS HASH Retrieval of rows from table based on hash
cluster key value.
TABLE ACCESS BY ROWID Retrieval of a row from a table based on its
rowid.
UNION Operation accepting two sets of rows and
returns the union of the sets, eliminating
duplicates.

APPENDIX B
Using SQL Trace and TKPROF

Oracle includes a facility known as SQL Trace that is extremely useful for diagnosing
performance problems on running systems. It logs information to an operating system file
for all the queries executed by a specific session, or by all sessions. Later, you can review
that information, find out which queries are consuming the most CPU or generating the
most I/O, and take some corrective action.

SQL Trace returns the following information for each SQL statement:
 A count of the times that the statement was executed
 The total CPU and elapsed time used by the statement
 The total CPU and elapsed times for the parse, execute, and fetch phases of the
statement’s execution
 The total number of physical reads triggered by the statement
 The total number of logical reads triggered by the statement
 The total number of rows processed by the statement

The information on physical I/O and CPU time is most helpful when it comes to
identifying statements that are causing performance problems.

Taking care of prerequisites

Before you can use SQL Trace, you need to take care of some prerequisites. Three
initialization parameters affect how SQL Trace operates. One of them points to the
location in which Oracle writes the trace files. This is important because you need to
know where to find a trace file after you generate it. You also need to know how Oracle
names these files.

Checking Initialization Parameters

The three initialization parameters that affect SQL tracing are the following:

 TIMED_STATISTICS —Controls whether Oracle tracks CPU and elapsed time for
each statement. Always set this parameter to TRUE . The overhead for that is
minimal, and the value of the timing information is well worth it.

 MAX_DUMP_FILE_SIZE —Controls the maximum size of the trace file generated

by the SQL Trace facility. These files get very large very fast, so Oracle allows you to
limit their size.

 USER_DUMP_DEST —Points to the directory in which trace files are created.

This parameter is important when you want to find trace files.
You can control the TIMED_STATISTICS parameter at the session level rather than at
the database level. To turn timed statistics on for your session, issue this command:

ALTER SESSION SET TIMED_STATISTICS =TRUE;

You can’t set the maximum file size at the session level. You must set it in the parameter
file for the instance. You can specify the maximum file size in either bytes or operating-
system blocks. If you specify the size using a raw number, Oracle interprets it to mean
blocks. If you supply a suffix such as K or M, Oracle interprets it as kilobytes or
megabytes. For example:

MAX_DUMP_FILE_SIZE=100 100 blocks

MAX_DUMP_FILE_SIZE=100K 100KB
MAX_DUMP_FILE_SIZE=100M 100MB

If you are tracing a long process and the size of the trace file reaches the limit specified
by MAX_DUMP_FILE_SIZE, then Oracle will silently stop tracing. The process will
continue to run.

The USER_DUMP_DEST parameter points you to the directory where the trace files are
created. Knowing its value is critical to the task of finding the trace files that you
generate.

If you change any of these parameters in the database parameter file, you will need to
stop and restart the instance for those changes to take effect.

Finding Your Trace Files

To find your trace files, you need to know the following: which directory they were
written to, and their names. The USER_DUMP_DEST initialization parameter points to
the directory. You can check the value of this parameter by looking in the database
parameter file or by issuing this query:

SELECT value
FROM v$parameter
WHERE name =‘user_dump_dest ’;

If you’re not the DBA, you may not have access to the v$parameter view.

Finding the directory is the easy part. Figuring out which trace file is yours is a bit more
difficult. Oracle generates trace file names automatically, and the names are based on
numbers that aren’t always easy to trace back to a session. A typical trace file name
would be [Link] .

So how do you find your trace file? One way is by looking at the timestamp. Write down
the time of day when you enable tracing, and also the time at which you finish. Later,
look for a trace file with a modification date close to the finish time. If you’re not sure of
your finish time, look at files with modification dates later than your starting time.
Hopefully, there won’t be so many people using the trace facility simultaneously that this
becomes a difficult task. If you have multiple trace files all created around the same time,
you may have to look at the SQL statements inside each file to identify the one that you
generated.

Enabling the SQL Trace feature

You can turn on the SQL Trace feature in three ways:
 Issue an ALTER SESSION command.
 Make a call to DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION .
 Set SQL_TRACE=TRUE in the database parameter file.

The method that you choose depends on whether you want to enable tracing for your
session, for someone else’s session, or for all sessions connected to the database.

Enabling SQL Trace for Your Session

If you’re logging on through SQL*Plus to test a few SQL statements, you can turn
tracing on by using the ALTER SESSION commands. The following two commands turn
tracing on and then off:

ALTER SESSION SET SQL_TRACE =TRUE;

ALTER SESSION SET SQL_TRACE =FALSE;

In between these two commands, you should execute the SQL queries that you are
interested in tracing.

Enabling SQL Trace for Another Session

Most often, you will want to enable tracing for some other session besides your own. You
may have a batch process that is taking a long time to run or an online program that is
responding slowly. In that case, you will need to follow these steps to trace the SQL
statements being executed:

 Start the batch process or online program that you are interested in tracing.
 Start another session using SQL*Plus.
 Issue a SELECT statement against the V$SESSION view to determine the SID and
serial number of the session created in step 1 by the program that you want to
trace.
 Issue a call to DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to turn tracing on
for that session.
 Collect the information that you need.
 Issue a call to DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to turn tracing
off.

The DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procedure requires that you

identify the specific session that you want to trace by supplying both the session identifier
(SID) and the serial #. You can get this information from the V$SESSION view, which
tells you who is logged on to the database. Here’s an example of the query to use:

SELECT username,sid,serial#
FROM v$session;
USERNAME SID SERIAL#
------------------------------------------------
...
SEAPARK 9 658
SYSTEM 11 1134

Once you have the SID and serial # of the session that you want to trace, you can enable
and disable tracing, as shown in this example:

EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (9,658,TRUE);

PL/SQL procedure successfully completed.

EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (9,658,FALSE);

PL/SQL procedure successfully completed.

In this example, tracing was turned on for the SEAPARK user. The first call to DBMS_
SYSTEM.SQL_TRACE_IN_SESSION included a value of TRUE to enable tracing. The
second call included a value of FALSE to stop it.

Enabling SQL Trace for All Sessions

You can enable tracing for all sessions connecting to a database by placing the following
entry in the database parameter file and then stopping and then restarting the database:

SQL_TRACE =TRUE

Use care when you enable tracing on a database-wide basis like this. Some performance
overhead is involved. Make sure that you really need database-wide statistics, and
whatever you do, remember to turn it off later by setting

SQL_TRACE =FALSE.
Using the TKPROF command
The raw trace files generated by Oracle aren’t very readable. To view the results of a
trace, you must run the TKPROF utility against the trace file that you generated. The
TKPROF utility will read the trace file and create a new file containing the information in
human readable form.

The TKPROF utility allows you to sort the queries in the output file based on a number
of different parameters. For example, you can choose to have the queries that consumed
the most CPU sorted to the front of the file. With large files, sorting the results makes it
easier for you to quickly identify those queries most in need of work.

The TKPROF utility also provides you with the option of issuing an EXPLAIN PLAN
for each statement in the trace file. The result here is that the output file will contain
execution plans, in addition to the statistics, for each statement.

Using TKPROF Syntax

The TKPROF utility is a command-line utility. You run it from the command prompt,
and you pass information to TKPROF using a series of command-line parameters.
The syntax looks like this:

tkprof tracefile outputfile

[explain=username /password ]
[table=[schema .]tablename ]
[print=integer ]
[aggregate={yes|no}]
[insert=filename ]
[sys={yes|no}]
[sort=option [,option ...]]

The following list describes each of the elements in this syntax:

 tracefile —Specifies the name of the trace file to be formatted.

 outputfile —Specifies the name of the output file that you want to create.
This file will contain the formatted trace output.

 explain=username /password —Causes TKPROF to issue an EXPLAIN PLAN

for each SQL statement in the trace file. To do this, TKPROF will connect
using the username and password that you specify.
 table=[schema .]tablename —Specifies an alternate plan table to use. When
you use the explain option, TKPROF expects to find a plan table named
PLAN_TABLE . If your plan table is named differently, use this option.
 print=integer —Causes TKPROF to generate output only for the first integer
SQL statements found in the trace file.
 aggregate=yes|no—Controls whether TKPROF reports multiple executions
of the same SQL statement in summary form. The default is yes. If you
say no, your output file could be quite large, as each execution of each
statement will be listed separately.
 .insert=filename —Causes TKPROF to generate a file containing INSERT
statements. These INSERT statements will contain the trace information and can be
used to save the trace data in a database table.
 sys={yes|no}—Controls whether recursive SQL statements and SQL statements
executed by the user SYS are included in the output file. The default value is yes.
 record=filename —Creates a SQL script in the specified file that contains all the user-
issued SQL statements in the trace file. You can use this file to recreate and replay the
events that were traced.
 sort=option [,option...]—Sorts the trace output on the options that you specify.

Executing TKPROF
The simplest way to execute TKPROF is to specify an input and an output file
name, like this:

tkprof [Link] [Link]

TKPROF:Release [Link].0 -Production on Tue Sep 14 09:56:03 1999

(c)Copyright 1999 Oracle [Link] rights reserved.

If your trace file contains a lot of SQL statements, you will want to sort the results.
You’ll likely find it beneficial to sort by the amount of CPU consumed when executing a
statement. Consider this example:

tkprof [Link] [Link] sort=execpu

TKPROF:Release [Link].0 -Production on Tue Sep 14 09:57:41 1999

(c)Copyright 1999 Oracle [Link] rights reserved.

It’s usually wise to generate execution plans for each SQL statement in the file. If you
look at the execution plan for a poorly performing statement, it can help you better
understand the problem.

Explaining Plans
TKPROF’s explain option allows you to generate execution plans for the SQL statements
in the trace file. When you use the explain option, you must supply TKPROF with a
username and password. TKPROF uses it to log on and explain the plans. The user that
you specify must have access to the tables referenced by the queries in the trace file. The
user should also own a plan table named PLAN_TABLE. However, if no plan table
currently exists, and if the user has the CREATE TABLE privilege, TKPROF will create
one temporarily. Here’s an example of the explain option being used:

tkprof [Link] [Link] sort=execpu explain=seapark/seapark

TKPROF:Release [Link].0 -Production on Tue Sep 14 10:05:33 1999

(c)Copyright 1999 Oracle [Link] rights reserved.

You’ll notice that when you use the explain option, TKPROF’s execution time is much
longer than otherwise. This is due to the overhead involved in connecting to the database
and issuing EXPLAIN PLAN statements for each query.

Interpreting TKPROF’s output

When you look at the file containing the TKPROF output, you’ll see a section like
the one shown for each SQL statement.

TKPROF output for a SQL statement

SELECT LNAME,FNAME
FROM S_EMPLOYEE
WHERE lname in
(SELECT lname FROM EMP_TEST);

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 16 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 6 16 11

Misses in library cache during parse: 1

Optimizer goal: CHOOSE
Parsing user id: 26 (SCOTT)

Rows Row Source Operation

------- ---------------------------------------------------
11 FILTER
37 TABLE ACCESS FULL S_EMPLOYEE
 3 TABLE ACCESS FULL EMP_TEST

Rows Execution Plan

------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
11 FILTER
37 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'S_EMPLOYEE'
3 TABLE ACCESS (FULL) OF 'EMP_TEST'

The major sections that you see here include the following:
 The SQL statement itself
 Statistics related to the execution of that SQL statement
 Information about the execution plan used for the statement

The statistics are often the key to diagnosing performance problems with specific SQL
statements. To interpret this output, you need to understand what those statistics are
telling you.

Understanding TKPROF’s statistics

The first set of statistics that TKPROF displays for a statement is in tabular form.
One row exists for each phase of SQL statement processing, plus a summary row
at the bottom. The three phases of statement processing are:

 The parse phase.

In this phase, Oracle takes a human-readable SQL statement and translates it into an
execution plan that it can understand. This is where syntax is checked, object
security is checked, and so forth.

 The execution phase.

Most of the work occurs in this phase, especially for INSERT, UPDATE, and
DELETE statements. With SELECT statements, the execution phase is where Oracle
identifies all the rows that are to be returned. Any sorting, grouping, or summarizing
takes place here.

 The fetch phase.

This phase applies only to SELECT statements, and it is where Oracle sends the
selected data to the application. The columns that you see in the tabular statistics
represent various counts and timings.

If the TIMED_STATISTICS parameter is not TRUE , you will see zeros for all
the timings.
The column descriptions are as follows:

 COUNT
Tells you the number of times that a SQL statement was parsed or executed. For
SELECT statements, this tells you the number of fetches that were made to retrieve
the data.

 CPU
Tells you the amount of CPU time spent in each phase.

 ELAPSED
Tells you the elapsed time spent in each phase.

 DISK
Tells you the number of database blocks that were physically read from disk in each
phase.

 QUERY
Tells you the number of buffers that were retrieved in consistent mode (usually for
queries) in each phase.

 CURRENT
Tells you the number of buffers that were retrieved in current mode in each phase.

Following the tabular statistics, TKPROF reports the number of library cache misses,
the optimizer goal, and the numeric user ID of the user who parsed the statement.

APPENDIX C

Using SQL*Plus Autotrace

If you’re using SQL*Plus release 3.3 or higher, you can take advantage of the autotrace
feature, to have queries explained automatically.
You turn autotrace on using the SET command, and you issue the query as you normally
would. SQL*Plus will execute the query and display the execution plan and Trace
statistics following the results.

To turn on AUTOTRACE, the following command can be executed in SQL*Plus

SET AUTOTRACE ON

Using the autotrace feature

SQL> set autotrace on

SQL> select * from dual;

D
-
X

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
0 physical reads
0 redo size
365 bytes sent via SQL*Net to client
456 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
The Explain Plan reveals the execution plan Oracle has chosen and the statistics reveal
the resources required to complete execution of the SQL statement. The main statistics to
review are the physical reads (disk reads), db block gets, consistent gets (memory reads),
and the recursive calls (internal oracle recursive calls that are costly).