Oracle SQL Tuning Secret Tips
This chapter has provided an overview of SQL tuning in
Oracle10g, with a focus on the new 10g SQL tuning features. The
main points of this chapter include:
The goals of SQL tuning involve verifying the best
execution plan for any statements.
The best execution plan is either the plan that
starts returning rows the fastest or the plan that executes the
query with the smallest resource consumption.
Oracle10g now automatically collects and refreshes
schema statistics using the
Histogram collection can now be easily automated,
and some of these databases choose to put histograms on all key
columns to improve the accuracy of table join order.
One common cause of suboptimal SQL is missing
materialized view and indexes, especially function-based
The SQLTuning Advisor and SQLAccess Advisor provide an easy method for
identifying and tuning SQL with suboptimal execution plans.
Oracle provides a wealth of hints to change the
optimizer execution plans.
SQL Profiles are a great
improvement over the stored outlines of the Optimizer Plan
dba_hist tables contain a wealth of historical
information about historical SQL execution statistics.
Time-series analysis of object usage within SQL
can yield important insights into holistic tuning for SQL
The next chapter will introduce the new Automated Session
History (ASH) tables and show how ASH can be used to quickly
identify and tune resource bottlenecks. A silver bullet is
a SQL tuning technique whereby a single action may have a
positive effect on many SQL statements. The next section
will delve into silver bullets as they relate to Oracle SQL
tuning and show some techniques that can improve SQL
Using Function-based Indexes (FBI)
In almost all cases, the use of a built-in function like
to_char, decode, substr,
etc. in an SQL query may cause a full-table scan of the target
table. To avoid this problem, many Oracle DBAs will create
corresponding indexes that make use of function-based indexes.
If a corresponding function-based index matches the built-in
function of the query, Oracle will be able to service the query
with an index range scan thereby avoiding
a potentially expensive full-table scan.
The following is a simple example. Suppose the DBA has
identified a SQL statement with hundreds of full-table scans
against a large table with a built-in function (BIF) in the
WHERE clause of the query. After examining the SQL, it is
simple to see that it is accessing a customer by converting the
customer name to uppercase using the upper BIF.
upper(c.customer_name) = upper(:v1)
c.cust_nbr = o.cust_nbr
Running the explain plan utility confirms the DBA’s suspicion
that the upper BIF is responsible for an unnecessary large-table
BY INDEX ROWID
RANGE SCAN CUST_NBR_IDX
The table access full customer option confirms that this BIF not
using the existing index on the
Since a matching function-based index may change the execution
plan, a function-based index can be added on
It can be risky to add indexes to a table because the execution
plans of many queries may change as a result. This is not a
problem with a function-based index because Oracle will only use
this type of index when the query uses a matching BIF.
(initial 128k next 128k maxextents 2147483645 pctincrease
Now, the SQL can be re-explained to show that the full-table
scan has been replaced by a index range scan on the new function-based index. For this query, the
execution time has been decreased from 45 seconds to less than
BY INDEX ROWID
RANGE SCAN CUST_NBR_IDX
BY INDEX ROWID
This simple example serves to illustrate the foremost SQL tuning
rule for BIFs. Whenever a BIF is used in a SQL statement, a
function-based index must be created.
Using Temporary Tables
The prudent use of temporary tables can dramatically improve
Oracle SQL performance. The following example from the DBA world
can be used to illustrate this concept. In the query that
follows, the goal is to identify all users existing within
Oracle who have not been granted a role. The query could be
formulated as an anti-join with a noncorrelated subquery as
username NOT IN
(select grantee from dba_role_privs);
This query runs in 18 seconds. These anti-joins can often be
replaced with an outer join, but the use of temporary tables
offers another option. Now, the same query is rewritten to
utilize temporary tables by selecting the distinct values from
drop table temp1;
drop table temp2;
username not in
(select grantee from temp2);
With the addition of temporary tables to hold the intermediate
results, this query runs in less than three seconds, a 6x
performance increase. Again, it is not easy to quantify the
reason for this speed increase, since the DBA views do not map
directly to Oracle tables; however, it is clear that temporary
table show promise for improving the execution speed of certain
types of Oracle SQL queries.
Fixing CBO Statistics
A client had just moved their system into production and was
experiencing a serious performance problem. The emergency
support DBA found that the
and there was
only one table with statistics. The DBA was running cost-based
but seemed completely unaware of the necessity to analyze the
schema for CBO statistics.
The trouble began when the DBA wanted to know the average row
length for a table. After using a Google search to determine
that the location of that information was the
column, it was
determined that the values were NULL. The DBA then went to
MetaLink and learned that an
analyze table command
would fill in the avg_row_len
CBO will dynamically estimate statistics for all tables with
missing statistics, and when using
with only one
table analyzed, any SQL that touches the table will be optimized
as a cost-based query. In this case, a multi-step silver bullet
did the trick:
alter table customer delete statistics;
exec dbms_stats (…);
When the system immediately returned to an acceptable
performance level, the DBA realized the importance of providing
complete and timely statistics for the CBO using the
Changing CBO SQL Optimizer Parameters
An emergency involving an Oracle 9.0.2 client from Phoenix who
was experiencing steadily degrading performance involved a large
number of large-table full-table scans which were suspected to
being unnecessary. This suspicious information was found by a
quick look into v$sql_plan
view using the
is found earlier in this chapter.
The top SQL was extracted from
and timed as-is with
an index hint.
it was unclear why the CBO was not choosing the index, the query
with the index hint ran almost 20x faster. After acting fast
and running a script against
discovered that approximately 65 percent of the indexes were
currently inside the data buffer cache.
Based on similar systems, the next step was to lower
to a value of 20 in
hopes of forcing the CBO to lower the relative costs of index
Some parameters can be dynamically altered in database versions
Oracle9i and newer.
alter system set optimizer_index_cost_adj =20 scope = pfile;
As a result of these actions, the execution plans for over 350
SQL statements were changed, and the overall system response
time was cut in half.
Repairing Obsolete CBO Statistics Gathering
A client called and expressed confusion as to why their system
was grinding to a halt. There was a serious degradation in SQL
performance after the implementation of partitioned tablespaces
in a 16-CPU Solaris 64-bit Oracle 9.0.4 system. The changes in
the development and QA instances had been thoroughly tested.
As it turned out, analyze
had been used to gather the CBO statistics. The
gathers partition-wise statistics. There was not time to pull a
deep sample collection, so a
dbms_stats was issued with a ten percent sample size. It
is parallelized with 15 parallel processes to speed-up the
exec dbms_stats.gather_schema_stats( -
ownname => 'SAPR4', -
options => 'GATHER AUTO', -
=> 10, -
method_opt => 'for all columns size repeat', -
degree => 15 -
In less than 30 minutes, the improved CBO statistics tripled the
performance of the entire database.
Removing full-table scans with Oracle Text
One serious SQL performance problem occurs when the SQL LIKE
operator is used to find a string within a large Oracle table
column such as VARCHAR(2000), CLOB, or BLOB:
select stuff from bigtab where text_column like ‘%ipod%’;
select stuff from bigtab where full_name like ‘%JONES’;
Since standard Oracle cannot index into a large column, their
LIKE queries cause full-table scans, and Oracle must examine
every row in the table, even when the result set is very small.
The following problems can be caused by unnecessary full-table
Large-table full-table scans increase the load on
the disk I/O sub-system
Small-table full-table scans(in the data buffer
cause high consistent gets and drive up
The Oracle*Text utility, also called Oracle ConText and Oracle
Intermedia, allows parsing through a large text column and index
on the words within the column. Unlike ordinary b-tree or
bitmap indexes, Oracle context
not updated as content is changed. Since most standard Oracle
databases will use the ctxcat
index with standard relational tables, the DBA must decide on a
As a result, Oracle Text indexes are only useful for removing
full-table scans when the tables are largely read-only and/or
the end-users do not mind not having 100% search recall:
The target table is relatively static (e.g.
nightly batch updates)
The end-users would not mind missing the latest
Oracle Text works with traditional data columns as well as with
MS-Word docs and Adobe PDF files that are stored within Oracle.
Oracle Text has several index types:
CTXCAT Indexes: A CTXCAT index is best for smaller
text fragments that must be indexed along with other standard
relational data (VARCHAR2).
WHERE CATSEARCH(text_column, 'ipod')> 0;
CONTEXT Indexes: The CONTEXT index type is used to
index large amounts of text such as Word, PDF, XML, HTML or
plain text documents.
WHERE CONTAINS(test_column, 'ipod', 1) > 0
CTXRULE Indexes: A CTXRULE index can be used to
build document classification applications.
These types of indexes allow users to replace the old-fashioned
SQL LIKE syntax with CONTAINS or CATSEARCH SQL syntax.
When the query is executed with the new index, the full-table
scan is replaced with a index scan, thereby greatly reducing
execution speed and improving hardware stress:
0 SELECT STATEMENT Optimizer=FIRST_ROWS
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BIGTAB'
3 2 DOMAIN INDEX OF 'TEXT-COLUMN_IDX'
Oracle Text Index Re-synchronization
Since rebuilding an Oracle Text index with CONTEXT, CTXCAT, or
CTXRULE requires a full-table scan and lots of internal parsing,
it is not practical to use triggers for instantaneous index
Updating Oracle Text indexes is easy, and they can be scheduled
using dbms_job or the Oracle10g
Oracle text provides a CTX_DDL package with the
SQL> EXEC CTX_DDL.SYNC_INDEX('text_column_idx');
SQL> EXEC CTX_DDL.OPTIMIZE_INDEX('text_column_idx','FULL');
For example, if a nightly
dbms_scheduler job is created to
index will be refreshed, but the structure will become
suboptimal over time. Oracle recommends that the
be used periodically to rebuild the whole index from scratch.
Index optimization can be performed in three modes: FAST; FULL;
In sum, the Oracle Text indexes are great for removing
unnecessary full-table scans for static Oracle tables, and they
can reduce I/O by several orders of magnitude. This will
greatly improving overall SQL performance.
The following conclusion section includes a summary of the most
important techniques in time-series SQL tuning.