With the introduction of Oracle 10g, there are many
new optimizer hints available to control optimizer behavior. Take a quick tour
of these powerful new hints.
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 optimisations 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) */ ...
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.
Discuss this in the Builder Forums
Do you have a related question or comment? You can access our Oracle forum from here and post your questions, reply to other users, search for answers and more.
Do you need help with Oracle? Gain advice from Builder AU forums
1
Mukarram Shah - 26/10/06
I am playing with 10g and was wondering if we can use some of the new hints that have come with 10g on a 9i database? I tried to use a 10g hint /*+ NO_USE_NL(tableA) */ on a 9i database, but nothing seems to happen. Is it safe to say that 9i ignores 10g hints when encountering them, or will it throw up an exception or some errors.
» Report offensive content
2
Gary Colbran - 07/06/07
Any hint which is not valid will be ignored. So any 10g hint on a 9i database will be invalid and therefore ignored.
» Report offensive content
3
Mahesh - 14/06/07
If i want to completely change my code base from oracle9i to 10g.Is that code changes are mandatory or not.
» Report offensive content
4
atif - 11/10/08
No
» Report offensive content