With each new release of Oracle, there are
more tuning controls and opportunities for
improving the execution speed of SQL. In an
article about speeding up SQL execution in
Oracle, I discussed some common SQL
tuning hints and, in another article, I
warned about performance issues when
using SQL hints inside views.
Oracle tuning with hints has always been
complex, and the Oracle Technical Network
has a
great overview into the processes of
using hints to tune Oracle SQL. With the
introduction of 10g database, there
are many new optimizer hints available to
control the optimizer behavior. Let’s take a
quick tour of these powerful new hints:
spread_min_analysis
With this hint, you can omit some of the
compile time optimizations of the rules on
spreadsheets, such as detailed dependency
graph analysis. Other optimizations, such as
creating filters to selectively populate
spreadsheet access structures and limited
rule pruning, are still used.
This hint helps to reduce compilation time
because spreadsheet analysis may be lengthy
if the number of rules is significantly
large, such as more than several hundred.
For example:
SELECT /*+
SPREAD_MIN_ANALYSIS */ ...
spread_no_analysis
With this hint, it's possible to have no
spreadsheet analysis. Rule-pruning and
filter generation are also omitted when this
hint is used. If there is spreadsheet
analysis, the compilation time is minimized.
For example:
SELECT /*+
SPREAD_NO_ANALYSIS */ ...
use_nl_with_index
This hint causes the CBO to join the
specified table to another row source with a
nested loops join. It uses the specified
table as the inner table, but only under
this condition: If no index is specified,
the CBO must be able to use some index with
at least one join predicate as the index
key. If an index is specified, the CBO must
be able to use that index with at least one
join predicate as the index key.
For example:
SELECT /*+
USE_NL_WITH_INDEX (polrec polrind) */ ...
CARDINALITY
This hint specifies the estimated
cardinality returned by a query or portions
of the query. Note that if no table is
specified, the cardinality is the total
number of rows returned by the entire query.
For example:
SELECT /*+ CARDINALITY (
[tablespec] card ) */
SELECTIVITY
This hint specifies the estimated
selectivity of a query or portions of the
query. If only one table is specified, the
selectivity is the fraction of rows of that
table that satisfies all single-table
predicates on the specified table. If a list
of tables is specified, the selectivity is
the fraction of rows of the result of
joining those tables in any order that
satisfies all applicable predicates.
For example:
SELECT /*+ SELECTIVITY (
[tablespec] sel ) */
However, note that if both CARDINALITY and
SELECTIVITY hints are specified on the same
set of tables, both hints will be ignored.
no_use_nl
The no_use_nl hint causes the CBO to exclude
nested loops to join each specified table to
another row source, using the specified
table as the inner table. With this hint,
only the hash join and the sort-merge joins
will be considered for the specified tables.
For example:
SELECT /*+ NO_USE_NL (
employees ) */ ...
no_use_merge
This hint causes the CBO to exclude
sort-merge to join each specified table to
another row source, using the specified
table as the inner table.
For example:
SELECT /*+ NO_USE_MERGE (
employees dept ) */ ...
no_use_hash
This hint causes the CBO to exclude hash
joins to join each specified table to
another row source, using the specified
table as the inner table.
For example:
SELECT /*+ NO_USE_HASH (
employees dept ) */ ...
no_index_ffs
This hint causes the CBO to exclude a fast
full-index scan of the specified indexes on
the specified table.
Syntax: /*+ NO_INDEX_FFS
( tablespec indexspec ) */
no_index_ss
This hint causes the CBO to exclude a skip
scan of the specified indexes on the
specified table.
Syntax: /*+ NO_INDEX_SS (tablespec
indexspec ) */
no_star_transformation
This hint causes the CBO to omit star query
transformation.
Syntax: /*+
NO_STAR_TRANSFORMATION */
index_ss
This hint explicitly chooses an index skip
scan for the specified table. If the
statement uses an index range scan, Oracle
scans the index entries in ascending order
of their indexed values. In a partitioned
index, the results are in ascending order
within each partition.
Syntax: /*+ INDEX_SS (tablespec
indexspec) */
index_ss_asc
This hint explicitly chooses an index skip
scan for the specified table. If the
statement uses an index range scan, Oracle
scans the index entries in ascending order
of their indexed values. In a partitioned
index, the results are in ascending order
within each partition.
Syntax: /*+ INDEX_SS_ASC
(tablespec indexspec) */
index_ss_desc
This hint explicitly chooses an index skip
scan for the specified table. If the
statement uses an index range scan, Oracle
scans the index entries in descending order
of their indexed values. In a partitioned
index, the results are in descending order
within each partition.
Syntax: /*+ INDEX_SS_DESC
(tablespec indexspec) */
cpu_costing
This hint turns CPU costing on for the SQL
statement. This is the default cost model
for the optimizer. The optimizer estimates
the number and type of IO operations and the
number of CPU cycles the database will
perform during execution of the given query.
It uses system statistics to convert the
number of CPU cycles and number of IO(s) to
the estimated query execution time. The
CPU_COST column of the PLAN_TABLE stores the
CPU cost.
Syntax: /*+ CPU_COSTING (tablespec
indexspec) */
no_cpu_costing
This hint turns off CPU costing for the SQL
statement. CBO then uses the IO cost model,
which measures everything in single-block
reads and ignores CPU cost.
Syntax: /*+
NO_CPU_COSTING */
As the Oracle optimizer becomes more
sophisticated, Oracle professionals must
constantly add to their arsenal of tools for
tuning SQL statements. While it is far
beyond the scope of this article to discuss
all of the intricacies of the new Oracle10g
SQL hints, you can get additional
information about Oracle10g in Mike
Ault’s latest book
Oracle Database 10g New Features.
 |
If you like Oracle
tuning, check-out my latest book "Oracle Tuning: The Definitive Reference".
Packed with almost 1,000 pages of
Oracle performance tuning
techniques, it's the foolproof way
to find and correct Oracle
bottlenecks. |
