Got

New
Ava
Forb
the
Free
Ana
BEW
Upg

Search BC Oracle Sites

Search
Home
E-mail Us
Oracle Articles

Oracle SQL tuning - Tune individual SQL

statements
Oracle Tips by Burleson Consulting

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support

For a complete understanding of SQL tuning steps, try the Advanced

Oracle SQL Tuning, a 3-Day or 5-day on-site Oracle course.

Tuning individual Oracle SQL statements

The acronym SQL stands for Structured Query Language. SQL is an
industry standard database query language that was adopted in the
mid-1980s. It should not be confused with commercial products such
as Microsoft SQL Server or open source products such as MySQL,
both of which use the acronym as part of the title of their products.
Do this before you start individual SQL statement tuning
This broad-brush approach can save thousands of hours of tedious
SQL tuning because you can hundreds of queries at once.
Remember, you MUST do this first, else later changes to the
optimizer parameters or statistics may un-tune your SQL.

Remote Support
Development
Implementation

Consulting Staf
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB
Don Burleson Blog

Remember, you must ALWAYS start with system-level SQL tuning,

else later changes might undo your tuned execution plans:

Optimize the server kernel - You must always tune your

disk and network I/O subsystem (RAID, DASD bandwidth,
network) to optimize the I/O time, network packet size and
dispatching frequency.

Adjusting your optimizer statistics - You must always

collect and store optimizer statistics to allow the optimizer to
learn more about the distribution of your data to take more
intelligent execution plans. Also, histograms can hypercharge
SQL in cases of determining optimal table join order, and
when making access decisions on skewed WHERE clause
predicates.

Adjust optimizer parameters - Optimizer

optimizer_mode,optimizer_index_caching,
optimizer_index_cost_adj.

Optimize your instance - Your choice

of db_block_size, db_cache_size, and OS parameters

(db_file_multiblock_read_count, cpu_count, &c), can

influence SQL performance.

Tune your SQL Access workload with physical indexes

and materialized views - Just as the 10g SQLAccess advisor
recommends missing indexes and missing materialized views,
you should always optimize your SQL workload with
indexes, especially function-based indexes, a Godsend for
SQL tuning.

11g Note: The Oracle 11g SQL Performance Analyzer (SPA), is

primarily designed to speed up the holistic SQL tuning process.
Once you create a workload (called a SQL Tuning Set, or STS),
Oracle will repeatedly execute the workload, using sophisticated
predictive models (using a regression testing approach) to accurately
identify the salient changes to SQL execution plans, based on your
environmental changes. Using SPA, we can predict the impact of
system changes on a workload, and we can forecast changes in
response times for SQL after making any change, like parameter
changes, schema changes, hardware changes, OS changes, or Oracle
upgrades. For details, see the book Oracle 11g New Features.
Once the environment, instance, and objects have been tuned, the
Oracle administrator can focus on what is probably the single most
important aspect of tuning an Oracle database: tuning the individual
SQL statements. In this final article in my series on Oracle tuning, I
will share some general guidelines for tuning individual SQL
statements to improve Oracle performance.
Oracle SQL tuning goals
Oracle SQL tuning is a phenomenally complex subject. Entire books
have been written about the nuances of Oracle SQL tuning; however,
there are some general guidelines that every Oracle DBA follows in
order to improve the performance of their systems. Again, see the
book "Oracle Tuning: The Definitive Reference", for complete
details.
The goals of SQL tuning focus on improving the execution plan to
fetch the rows with the smallest number of database "touches" (LIO
buffer gets and PIO physical reads).

Remove unnecessary large-table full-table scans

Unnecessary full-table scans cause a huge amount of

unnecessary I/O and can drag-down an entire database. The
tuning expert first evaluates the SQL based on the number of
rows returned by the query. The most common tuning remedy
for unnecessary full-table scans is adding indexes. Standard
b-tree indexes can be added to tables, and bitmapped and
function-based indexes can also eliminate full-table scans. In
some cases, an unnecessary full-table scan can be forced to
use an index by adding an index hint to the SQL statement.

Cache small-table full-table scansIn cases where a fulltable scan is the fastest access method, the administrator
should ensure that a dedicated data buffer is available for the
rows. In Oracle8 and beyond, a small table can be cached by
forcing it into the KEEP pool.

Verify optimal index usageOracle sometimes has a choice

of indexes, and the tuning professional must examine each
index and ensure that Oracle is using the proper index.

Materialize your aggregations and summaries for static

tables - One features of the Oracle 10g SQLAccess advisor is
recommendations for new indexes and suggestions for
materialized views. Materialized views pre-join tables and
pre-summarize data, a real silver bullet for data mart reporting
databases where the data is only updated daily. Again, see the
book "Oracle Tuning: The Definitive Reference", for
complete details on SQL tuning with materialized views.

These are the goals of SQL tuning in a nutshell. However, they are
deceptively simple, and to effectively meet them, we need to have a
through understanding of the internals of Oracle SQL. Let's begin
with an overview of the Oracle SQL optimizers.
Oracle SQL optimizers
One of the first things the Oracle DBA looks at is the default
optimizer mode for the database. The Oracle initialization parameters
offer many cost-based optimizer modes as well as the deprecated yet
useful rule-based hint:
The cost-based optimizer uses 'statistics' that are collected from the

table using the 'analyze table' command. Oracle uses these metrics
about the tables in order to intelligently determine the most efficient
way of servicing the SQL query. It is important to recognize that in
many cases, the cost-based optimizer may not make the proper
decision in terms of the speed of the query. The cost-based optimizer
is constantly being improved, but there are still many cases in which
the rule-based optimizer will result in faster Oracle queries.
Prior to Oracle 10g, Oracle's default optimizer mode was called
'choose.' In the choose optimizer mode, Oracle will execute the rulebased optimizer if there are no statistics present for the table; it will
execute the cost-based optimizer if statistics are present. The danger
with using the choose optimizer mode is that problems can occur in
cases where one Oracle table in a complex query has statistics and
the other tables do not.
Starting in Oracle 10g, the default optimizer mode is all_rows,
favoring full-table scans over index access. The all_rows optimizer
mode is designed to minimize computing resources and it favors fulltable scans. Index access (first_rows_n) adds additional I/O
overhead, but they return rows faster, back to the originating query:

Full-table scans touch all data blocks

Hence, many OLTP shops will choose first_rows, first_rows_100 or

first_rows_10, asking Oracle to use indexes to reduce block touches:

Index scans return rows fast by doing additional I/O

Note: Staring in Oracle9i release 2, the Oracle performance tuning
guide says that thefirst_rows optimizer mode has been deprecated
and to use first_rows_n instead.
When only some tables contain CBO statistics, Oracle will use the
cost-based optimization and estimate statistics for the other tables in
the query at runtime. This can cause significant slowdown in the
performance of the individual query.
In sum, the Oracle database administrator will always try changing
the optimizer mode for queries as the very first step in Oracle tuning.
The foremost tenet of Oracle SQL tuning is avoiding the dreaded
full-table scan. One of the hallmarks of an inefficient SQL statement
is the failure of the SQL statement to use all of the indexes that are
present within the Oracle database in order to speed up the query.
Of course, there are times when a full-table scan is appropriate for a
query, such as when you are doing aggregate operations such as a
sum or an average, and the majority of the rows within the Oracle
table must be read to get the query results. The task of the SQL
tuning expert is to evaluate each full-table scan and see if the

performance can be improved by adding an index.

In most Oracle systems, a SQL statement will be retrieving only a
small subset of the rows within the table. The Oracle optimizers are
programmed to check for indexes and to use them whenever possible
to avoid excessive I/O. However, if the formulation of a query is
inefficient, the cost-based optimizer becomes confused about the best
access path to the data, and the cost-based optimizer will sometimes
choose to do a full-table scan against the table. Again, the general
rule is for the Oracle database administrator to interrogate the SQL
and always look for full-table scans.
For the full story, see my book "Oracle Tuning: The Definitive
Reference" for details on choosing the right optimizer mode.
A strategic plan for Oracle SQL tuning
Many people ask where they should start when tuning Oracle SQL.
Tuning Oracle SQL is like fishing. You must first fish in the Oracle
library cache to extract SQL statements and rank the statements by
their amount of activity.
Step 1: Identify high-impact SQL
The SQL statements will be ranked according the number of
executions and will be tuned in this order. The executions column of
the v$sqlarea view and thestats$sql_summary or
the dba_hist_sql_summary table can be used to locate the most
frequently used SQL. Note that we can display SQL statements by:

Rows processed: Queries that process a large number of

rows will have high I/O and may also have impact on the
TEMP tablespace.

Buffer gets: High buffer gets may indicate a resourceintensive query.

Disk reads: High disk reads indicate a query that is causing

excessive I/O.

Memory KB: The memory allocation of a SQL statement is

useful for identifying statements that are doing in-memory

table joins.

CPU secs: This identifies the SQL statements that use the
most processor resources.

Sorts: Sorts can be a huge slowdown, especially if they're

being done on a disk in the TEMP tablespace.

Executions: The more frequently executed SQL statements

should be tuned first, since they will have the greatest impact
on overall performance.

Step 2: Determine the execution plan for SQL

As each SQL statement is identified, it will be 'explained' to
determine its existing execution plan. There are a host of third-party
tools on the market that show the execution plan for SQL statements.
The most common way of determining the execution plan for a SQL
statement is to use Oracle's explain plan utility. By using explain
plan, the Oracle DBA can ask Oracle to parse the statement and
display the execution class path without actually executing the SQL
statement.
To see the output of an explain plan, you must first create a 'plan
table.' Oracle provides a script in $ORACLE_HOME/rdbms/admin
called utlxplan.sql. Execute utlxplan.sql and create a public synonym
for the plan_table:
sqlplus > @utlxplan
Table created.
sqlplus > create public synonym plan_table for sys.plan_table;
Synonym created.

Most relational databases use an explain utility that takes the SQL
statement as input, runs the SQL optimizer, and outputs the access
path information into a plan_table, which can then be interrogated to
see the access methods. Listing 1 runs a complex query against a
database.
EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR

SET STATEMENT_ID = 'RUN1'

INTO plan_table
FOR
SELECT
'T'||plansnet.terr_code, 'P'||detplan.pac1
|| detplan.pac2 || detplan.pac3, 'P1', sum(plansnet.ytd_d_ly_tm),
sum(plansnet.ytd_d_ty_tm),
sum(plansnet.jan_d_ly),
sum(plansnet.jan_d_ty),
FROM plansnet, detplan
WHERE
plansnet.mgc = detplan.mktgpm
AND
detplan.pac1 in ('N33','192','195','201','BAI',
'P51','Q27','180','181','183','184','186','188',
'198','204','207','209','211')
GROUP BY 'T'||plansnet.terr_code, 'P'||detplan.pac1 || detplan.pac2 ||
detplan.pac3;

This syntax is piped into the SQL optimizer, which will analyze the
query and store the plan information in a row in the plan table
identified by RUN1. Please note that the query will not execute; it
will only create the internal access information in the plan table. The
plan tables contains the following fields:

operation: The type of access being performed. Usually

table access, table merge, sort, or index operation

options: Modifiers to the operation, specifying a full table, a

range table, or a join

object_name: The name of the table being used by the query

component

Process ID: The identifier for the query component

Parent_ID: The parent of the query component. Note that

several query components may have the same parent.

Now that the plan_table has been created and populated, you may
interrogate it to see your output by running the following query in
Listing 2.
plan.sql - displays contents of the explain plan table
SET PAGES 9999;
SELECT lpad(' ',2*(level-1))||operation operation,
options,
object_name,
position
FROM plan_table
START WITH id=0
AND
statement_id = 'RUN1'

CONNECT BY prior id = parent_id

AND
statement_id = 'RUN1';

Listing 3 shows the output from the plan table shown in Listing 1.
This is the execution plan for the statement and shows the steps and
the order in which they will be executed.
SQL> @list_explain_plan
OPERATION
------------------------------------------------------------------------------------OPTIONS
OBJECT_NAME
POSITION
------------------------------ ------------------------------------------------------SELECT STATEMENT
SORT
GROUP BY
1
CONCATENATION
1
NESTED LOOPS
1
TABLE ACCESS FULL
PLANSNET
1
TABLE ACCESS BY ROWID
DETPLAN
2
INDEX RANGE SCAN
DETPLAN_INDEX5
1
NESTED LOOPS

From this output, we can see the dreaded TABLE ACCESS FULL on
the PLANSNET table. To diagnose the reason for this full-table scan,
we return to the SQL and look for any plansnet columns in the
WHERE clause. There, we see that the plansnet column called 'mgc'
is being used as a join column in the query, indicating that an index is
necessary on plansnet.mgc to alleviate the full-table scan.
While the plan table is useful for determining the access path to the
data, it does not tell the entire story. The configuration of the data is
also a consideration. The SQL optimizer is aware of the number of
rows in each table (the cardinality) and the presence of indexes on
fields, but it is not aware of data distribution factors such as the
number of expected rows returned from each query component.
Step 3: Tune the SQL statement
For those SQL statements that possess a sub-optimal execution plan,
the SQL will be tuned by one of the following methods:

Adding SQL 'hints' to modify the execution plan

Re-write SQL with Global Temporary Tables

Rewriting the SQL in PL/SQL. For certain queries this can

result in more than a 20x performance improvement. The

SQL would be replaced with a call to a PL/SQL package that

contained a stored procedure to perform the query.
Using hints to tune Oracle SQL
Among the most common tools for tuning SQL statements are hints.
A hint is a directive that is added to the SQL statement to modify the
access path for a SQL query.
For testing, you can quickly test the effect of another
optimizer parameter value at the query level without using an 'alter
session' command, using the new opt_param SQL hint:
Troubleshooting tip!

select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .

select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .

Oracle publishes many dozens of SQL hints, and hints become

increasingly more complicated through the various releases of Oracle
and on into Oracle.
Note: Hints are only used for de-bugging and you should adjust your
optimizer statistics to make the CBO replicate the hinted SQL. Let's
look at the most common hints to improve tuning:

Mode hints: first_rows_10, first_rows_100

Oracle leading and ordered hints Also see how

to tune table join

order with histograms

Dynamic sampling: dynamic_sampling

Oracle SQL undocumented tuning hints - Guru's only

The cardinality hint

Self-order the table joins - If you find that Oracle is joining the
tables together in a sub-optimal order, you can use the ORDERED
hint to force the tables to be joined in the order that they appear in the
FROM clause. See

 Try a first_rows_n hint. Oracle has two cost-based optimizer

modes,first_rows_n and all_rows. The first_rows mode will execute
to begin returning rows as soon as possible, whereas the all_rows
mode is designed to optimize the resources on the entire query before
returning rows.
SELECT /*+ first_rows */

A case study in SQL tuning

One of the historic problems with SQL involves formulating SQL
queries. Simple queries can be written in many different ways, each
variant of the query producing the same result, but with widely
different access methods and query speeds.
For example, a simple query such as 'What students received an A
last semester'' can be written in three ways, as shown in below, each
returning an identical result.
A standard join:
SELECT *
FROM STUDENT, REGISTRATION
WHERE
STUDENT.student_id = REGISTRATION.student_id
AND
REGISTRATION.grade = 'A';
A nested query:
SELECT *
FROM STUDENT
WHERE
student_id =
(SELECT student_id
FROM REGISTRATION
WHERE
grade = 'A'
);
A correlated subquery:
SELECT *
FROM STUDENT
WHERE
0 <
(SELECT count(*)
FROM REGISTRATION
WHERE
grade = 'A'
AND
student_id = STUDENT.student_id
);

Let's wind up with a review of the basic components of a SQL query

and see how to optimize a query for remote execution.
Tips for writing more efficient SQL
Space doesn't permit me to discuss every detail of Oracle tuning, but
I can share some general rules for writing efficient SQL in Oracle
regardless of the optimizer that is chosen. These rules may seem
simplistic but following them in a diligent manner will generally
relieve more than half of the SQL tuning problems that are
experienced:

Rewrite complex subqueries with temporary tables Oracle created the global temporary table (GTT) and the SQL
WITH operator to help divide-and-conquer complex SQL
sub-queries (especially those with with WHERE clause
subqueries, SELECT clause scalar subqueries and FROM
clause in-line views). Tuning SQL with temporary tables (and
materializations in the WITH clause) can result in amazing
performance improvements.

Use minus instead of EXISTS subqueries - Some say

that using the minus operator instead of NOT IN and NOT
Exists will result in a faster execution plan.

Use SQL analytic functions - The Oracle analytic functions

can do multiple aggregations (e.g. rollup by cube) with a
single pass through the tables, making them very fast for
reporting SQL.

Re-write NOT EXISTS and NOT EXISTS subqueries as

outer joins - Inmany cases of NOT queries (but ONLY where
a column is defined as NULL), you can re-write the
uncorrelated subqueries into outer joins with IS NULL
tests. Note that this is a non-correlated sub-query, but it could
be re-written as an outer join.

select book_key from book

where
book_key NOT IN (select book_key from sales);

Below we combine the outer join with a NULL test in the WHERE

clause without using a sub-query, giving a faster execution plan.

select b.book_key from book b, sales s
where
b.book_key = s.book_key(+)
and
s.book_key IS NULL;

Index your NULL values - If you have SQL that frequently

tests for NULL, consider creating an index on NULL values.
To get around the optimization of SQL queries that choose
NULL column values (i.e. where emp_name IS NULL), we
can create a function-based index using the null value built-in
SQL function to index only on the NULL columns.

Leave column names alone - Never do a calculation on an

indexed column unless you have a matching function-based
index (a.k.a. FBI). Better yet, re-design the schema so that
common where clause predicates do not need transformation
with a BIF:

where salary*5
> :myvalue
where substr(ssn,7,4)
= "1234"
where to_char(mydate,mon) = "january"

Avoid the use of NOT IN or HAVING. Instead, a NOT

EXISTS subquery may run faster (when appropriate).

Avoid the LIKE predicate = Always replace a "like" with an

equality, when appropriate.

Never mix data types - If a WHERE clause column predicate

is numeric, do not to use quotes. For char index columns,
always use quotes. There are mixed data type predicates:

where cust_nbr = "123"

where substr(ssn,7,4) = 1234

Use decode and case - Performing complex aggregations

with the "decode" or "case" functions can minimize the
number of times a table has to be selected.

Don't fear full-table scans - Not all OLTP queries are

optimal when they uses indexes. If your query will return a

large percentage of the table rows, a full-table scan may be

faster than an index scan. This depends on many factors,
including your configuration (values
for db_file_multiblock_read_count,db_block_size), query
parallelism and the number of table/index blocks in the buffer
cache.

Use those aliases - Always use table aliases when referencing

columns.

Also, see these related SQL tuning notes:

Oracle automatic SQL tuning
Oracle 10g AWR SQL Tuning Scripts
Oracle SQL Tuning init.ora parameters
Oracle SQL tuning with column histograms
Oracle SQL undocumented tuning hints
SQL tuning using materialized views
Conclusion
This article should provide you with a good overall background in
Oracle SQL tuning, although there are many details that are too
involved to discuss in one article.

Get the Complete

Oracle SQL Tuning Information
The landmark book "Advanced Oracle SQL Tuning
Definitive Reference" is filled with valuable
information on Oracle SQL Tuning. This book includes
scripts and tools to hypercharge Oracle 11g performance
and you can buy it for 30% off directly from the
publisher.

Get the Complete

Oracle SQL Tuning Information
The landmark book "Advanced Oracle SQL Tuning
Definitive Reference" is filled with valuable
information on Oracle SQL Tuning. This book includes
scripts and tools to hypercharge Oracle 11g performance
and you can buy it for 30% off directly from the

publisher.