Oracle SQL tuning steps http://www.dba-oracle.com/art_sql_tune.

htm

Search BC Oracle Sites

Oracle Tips
Got Questions?
Oracle SQL tuning - Tune individual SQL
Free AWR Report
statements Analysis
Home Restart a Failed Daemon
Process
E-mail Us Oracle Tips by Burleson Consulting
BEWARE of 11gR2
Oracle Articles Upgrade Gotchas!
Oracle 12c has been
released
For a complete understanding of SQL tuning steps, try the Advanced Oracle SQL
Oracle Training
Tuning, a 3-Day or 5-day on-site Oracle course.
Oracle Tips
Oracle Forum
Class Catalog Tuning individual Oracle SQL statements

The acronym SQL stands for Structured Query Language. SQL is an industry
Remote DBA standard database query language that was adopted in the mid-1980s. It should
Oracle Tuning not be confused with commercial products such as Microsoft SQL Server or open
Emergency 911 source products such as MySQL, both of which use the acronym as part of the
RAC Support title of their products.
Apps Support
Analysis Do this before you start individual SQL statement tuning
Design
Implementation This broad-brush approach can save thousands of hours of tedious SQL tuning
Oracle Support 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.
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
Remote Support
Development
Implementation
Remember, you must ALWAYS start with system-level SQL tuning, else later
changes might undo your tuned execution plans:
Consulting Staff Optimize the server kernel - You must always tune your disk and
Consulting Prices network I/O subsystem (RAID, DASD bandwidth, network) to optimize
Help Wanted! the I/O time, network packet size and dispatching frequency.

1 de 12 08/11/2013 11:46 a.m.

Oracle SQL tuning steps http://www.dba-oracle.com/art_sql_tune.htm

Adjusting your optimizer statistics - You must always collect and store
optimizer statistics to allow the optimizer to learn more about the
Oracle Posters distribution of your data to take more intelligent execution plans. Also,
Oracle Books histograms can hypercharge SQL in cases of determining optimal table join
Oracle Scripts order, and when making access decisions on skewed WHERE clause
Ion predicates.
Excel-DB
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 scansUnnecessary

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

2 de 12 08/11/2013 11:46 a.m.

Oracle SQL tuning steps http://www.dba-oracle.com/art_sql_tune.htm

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 full-table 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 rule-based 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 full-table scans. Index access (first_rows_n)
adds additional I/O overhead, but they return rows faster, back to the originating
query:

3 de 12 08/11/2013 11:46 a.m.

Oracle SQL tuning steps http://www.dba-oracle.com/art_sql_tune.htm

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 the first_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.

4 de 12 08/11/2013 11:46 a.m.

Oracle SQL tuning steps http://www.dba-oracle.com/art_sql_tune.htm

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 1Identify 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 the
stats$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 processedQueries that process a large number of rows will have

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

Buffer getsHigh buffer gets may indicate a resource-intensive query.

Disk readsHigh disk reads indicate a query that is causing excessive

I/O.

Memory KBThe memory allocation of a SQL statement is useful for

identifying statements that are doing in-memory table joins.

CPU secsThis identifies the SQL statements that use the most processor
resources.

SortsSorts can be a huge slowdown, especially if theyre being done on

a disk in the TEMP tablespace.

ExecutionsThe more frequently executed SQL statements should be

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

Step 2Determine 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

5 de 12 08/11/2013 11:46 a.m.

Oracle SQL tuning steps http://www.dba-oracle.com/art_sql_tune.htm

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:

operationThe type of access being performed. Usually table access,

table merge, sort, or index operation

optionsModifiers to the operation, specifying a full table, a range table,

or a join

object_nameThe name of the table being used by the query component

Process IDThe identifier for the query component

Parent_IDThe 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
------------------------------ -------------------------------------------------------

6 de 12 08/11/2013 11:46 a.m.

Oracle SQL tuning steps http://www.dba-oracle.com/art_sql_tune.htm

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 3Tune 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.
Troubleshooting tip! 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:

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. Lets 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

7 de 12 08/11/2013 11:46 a.m.

Oracle SQL tuning steps http://www.dba-oracle.com/art_sql_tune.htm

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 resultbut 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
);

Lets 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 doesnt 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

8 de 12 08/11/2013 11:46 a.m.

Oracle SQL tuning steps http://www.dba-oracle.com/art_sql_tune.htm

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 -

In many 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

9 de 12 08/11/2013 11:46 a.m.

Oracle SQL tuning steps http://www.dba-oracle.com/art_sql_tune.htm

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

The 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

The 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.

10 de 12 08/11/2013 11:46 a.m.

Oracle SQL tuning steps http://www.dba-oracle.com/art_sql_tune.htm

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training
reference for use by our DBA performance tuning consulting professionals. Feel
free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support

expert should independently investigate their credentials and experience, and
not rely on advertisements and self-proclaimed expertise. All legitimate Oracle
experts publish their Oracle qualifications.

Errata? Oracle technology is changing and we strive to update our BC Oracle

support information. If you find an error or have a suggestion for improving our
content, we would appreciate your feedback. Just e-mail:
and include the URL for the page.

Burleson Consulting
The Oracle of Database Support

Oracle Pe rformance Tuning

11 de 12 08/11/2013 11:46 a.m.

Oracle SQL tuning steps http://www.dba-oracle.com/art_sql_tune.htm

Re mote DBA Se rv ice s

Copyright 1996 - 2012

All rights reserved.

Oracle is the registered trademark of Oracle Corporation.

12 de 12 08/11/2013 11:46 a.m.