11. 3.1.

2 Tuning an Appl icat ion / Redu cing Load

If your whole application is performing suboptimally, or if you are attempting to

reduce the overall CPU or I/O load on the database server, then identifying
resource-intensive SQL involves the following steps:

1. Determine which period in the day you would like to examine; typically
this is the application's peak processing time.
2. Gather operating system and Oracle statistics at the beginning and end of
that period. The minimum of Oracle statistics gathered should be file I/O
(V$FILESTAT), system statistics (V$SYSSTAT), and SQL statistics
(V$SQLAREA, V$SQL or V$SQLSTATS, V$SQLTEXT, V$SQL_PLAN,
and V$SQL_PLAN_STATISTICS).

See Also:

Chapter 6, "Automatic Performance Diagnostics" for information on how to

use Oracle tools to gather Oracle instance performance data

3. Using the data collected in step two, identify the SQL statements using
the most resources. A good way to identify candidate SQL statements is to
query V$SQLSTATS. V$SQLSTATS contains resource usage information
for all SQL statements in the shared pool. The data in V$SQLSTATS
should be ordered by resource usage. The most common resources are:
o Buffer gets (V$SQLSTATS.BUFFER_GETS, for high CPU using
statements)
o Disk reads (V$SQLSTATS.DISK_READS, for high I/O statements)
o Sorts (V$SQLSTATS.SORTS, for many sorts)

One method to identify which SQL statements are creating the highest load is to
compare the resources used by a SQL statement to the total amount of that
resource used in the period. For BUFFER_GETS, divide each SQL statement's
BUFFER_GETS by the total number of buffer gets during the period. The total
number of buffer gets in the system is available in the V$SYSSTAT table, for
the statistic session logical reads.

Similarly, it is possible to apportion the percentage of disk reads a statement

performs out of the total disk reads performed by the system by dividing
V$SQL_STATS.DISK_READS by the value for the V$SYSSTAT statistic
physical reads. The SQL sections of the Automatic Workload Repository report
include this data, so you do not need to perform the percentage calculations
manually.

11.3.2 Gathering Data o n the SQL Identified

If you are most concerned with CPU, then examine the top SQL statements that
performed the most BUFFER_GETS during that interval. Otherwise, start with
the SQL statement that performed the most DISK_READS.

11.5 De vel oping Effi ci en t S QL S tat em ents

You can improve SQL statement efficiency:

• Verifying Optimizer StatisticsVerifying Optimizer Statistics

• Reviewing the Execution Plan
• Restructuring the SQL Statements
• Restructuring the Indexes
• Modifying or Disabling Triggers and Constraints
• Restructuring the Data
• Maintaining Execution Plans Over Time
• Visiting Data as Few Times as Possible

Restructuring the SQL Statements

1)Compose Predicates Using AND and =

To improve SQL efficiency, use equijoins whenever possible. Statements that

perform equijoins on untransformed column values are the easiest to tune.

2) Avoid Transformed Columns in the WHERE Clause

Use untransformed column values. For example, use:

WHERE a.order_no = b.order_no

rather than:
WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no,
'.') - 1))
= TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.')
- 1))

Do not use SQL functions in predicate clauses or WHERE clauses. Any expression
using a column, such as a function having the column as its argument, causes
the optimizer to ignore the possibility of using an index on that column, even a
unique index, unless there is a function-based index defined that can be used.

Avoid mixed-mode expressions, and beware of implicit type conversions. When

you want to use an index on the VARCHAR2 column charcol, but the WHERE
clause looks like this:

AND charcol = numexpr

where numexpr is an expression of number type (for example, 1,

USERENV('SESSIONID'), numcol, numcol+0,...), Oracle translates that
expression into:

AND TO_NUMBER(charcol) = numexpr

Avoid the following kinds of complex expressions:

• col1 = NVL (:b1,col1)

• NVL (col1,-999) = ….
• TO_DATE(), TO_NUMBER(), and so on

These expressions prevent the optimizer from assigning valid cardinality or

selectivity estimates and can in turn affect the overall plan and the join method.

Add the predicate versus using NVL() technique.

For example:

SELECT employee_num, full_name Name, employee_id

FROM mtl_employees_current_view
WHERE (employee_num = NVL (:b1,employee_num)) AND
(organization_id=:1)
ORDER BY employee_num;

Also:

SELECT employee_num, full_name Name, employee_id

 FROM mtl_employees_current_view
WHERE (employee_num = :b1) AND (organization_id=:1)
ORDER BY employee_num;

When you need to use SQL functions on filters or join predicates, do not use
them on the columns on which you want to have an index; rather, use them on
the opposite side of the predicate, as in the following statement:

TO_CHAR(numcol) = varcol

rather than

varcol = TO_CHAR(numcol)

3) Write Separate SQL Statements for Specific Tasks

SQL is not a procedural language. Using one piece of SQL to do many different
things usually results in a less-than-optimal result for each task. If you want SQL
to accomplish different things, then write various statements, rather than writing
one statement to do different things depending on the parameters you give it.

It is always better to write separate SQL statements for different tasks, but if you
must use one SQL statement, then you can make a very complex statement
slightly less complex by using the UNION ALL operator.

Optimization (determining the execution plan) takes place before the database
knows what values will be substituted into the query. An execution plan cannot,
therefore, depend on what those values are. For example:

SELECT info
FROM tables
WHERE ...
AND somecolumn BETWEEN DECODE(:loval, 'ALL',
somecolumn, :loval)
AND DECODE(:hival, 'ALL', somecolumn, :hival);

Written as shown, the database cannot use an index on the somecolumn

column, because the expression involving that column uses the same column on
both sides of the BETWEEN.

This is not a problem if there is some other highly selective, indexable condition
you can use to access the driving table. Often, however, this is not the case.
Frequently, you might want to use an index on a condition like that shown but
need to know the values of :loval, and so on, in advance. With this
information, you can rule out the ALL case, which should not use the index.

If you want to use the index whenever real values are given for :loval and
:hival (if you expect narrow ranges, even ranges where :loval often equals
:hival), then you can rewrite the example in the following logically equivalent
form:

SELECT /* change this half of UNION ALL if other half

changes */ info
FROM tables
WHERE ...
AND somecolumn BETWEEN :loval AND :hival
AND (:hival != 'ALL' AND :loval != 'ALL')
UNION ALL
SELECT /* Change this half of UNION ALL if other half
changes. */ info
FROM tables
WHERE ...
AND (:hival = 'ALL' OR :loval = 'ALL');

If you run EXPLAIN PLAN on the new query, then you seem to get both a
desirable and an undesirable execution plan. However, the first condition the
database evaluates for either half of the UNION ALL is the combined condition
on whether :hival and :loval are ALL. The database evaluates this
condition before actually getting any rows from the execution plan for that part
of the query.

When the condition comes back false for one part of the UNION ALL query, that
part is not evaluated further. Only the part of the execution plan that is optimum
for the values provided is actually carried out. Because the final conditions on
:hival and :loval are guaranteed to be mutually exclusive, only one half of
the UNION ALL actually returns rows. (The ALL in UNION ALL is logically valid
because of this exclusivity. It allows the plan to be carried out without an
expensive sort to rule out duplicate rows for the two halves of the query.)

5) Use of EXISTS versus IN for Subqueries

In certain circumstances, it is better to use IN rather than EXISTS. In general,

if the selective predicate is in the subquery, then use IN. If the selective
predicate is in the parent query, then use EXISTS.
Sometimes, Oracle can rewrite a subquery when used with an IN clause to take
advantage of selectivity specified in the subquery. This is most beneficial when
the most selective filter appears in the subquery and there are indexes on the
join columns. Conversely, using EXISTS is beneficial when the most selective
filter is in the parent query. This allows the selective predicates in the parent
query to be applied before filtering the rows against the EXISTS criteria.

Note :
You should verify the optimizer cost of the statement with the actual number of
resources used (BUFFER_GETS, DISK_READS, CPU_TIME from
V$SQLSTATS or V$SQLAREA). Situations such as data skew (without the use of
histograms) can adversely affect the optimizer's estimated cost for an operation.

6) Controlling the Access Path and Join Order with Hints

You can influence the optimizer's choices by setting the optimizer approach and
goal, and by gathering representative statistics for the query optimizer.
Sometimes, the application designer, who has more information about a
particular application's data than is available to the optimizer, can choose a more
effective way to execute a SQL statement. You can use hints in SQL statements
to instruct the optimizer about how the statement should be executed.

Hints, such as /*+FULL */ control access paths. For example:

SELECT /*+ FULL(e) */ e.last_name

FROM employees e
WHERE e.job_id = 'CLERK';

Join order can have a significant effect on performance. The main objective of
SQL tuning is to avoid performing unnecessary work to access rows that do not
affect the result. This leads to three general rules:

• Avoid a full-table scan if it is more efficient to get the required rows

through an index.
• Avoid using an index that fetches 10,000 rows from the driving table if
you could instead use another index that fetches 100 rows.
• Choose the join order so as to join fewer rows to tables later in the join
order.
6)Use Caution When Managing Views

Be careful when joining views, when performing outer joins to views, and when
reusing an existing view for a new purpose.

7)Use Caution When Joining Complex Views

8)Do Not Recycle Views

Beware of writing a view for one purpose and then using it for other purposes to
which it might be ill-suited. Querying from a view requires all tables from the
view to be accessed for the data to be returned. Before reusing a view,
determine whether all tables in the view need to be accessed to return the data.