Oracle tuning with hints
Oracle ensures that the cost-based SQL optimizer becomes more
sophisticated with each release. With each new release, Oracle
provides an increasing number of methods for changing the execution
plans for SQL statements. While hints are used for tuning as well as
documentation, the most common use for Oracle hints is as a
debugging tool. The hints can be used to determine the optimal
execution plan, and then work backward, adjusting the statistics to
make the vanilla SQL simulate the hinted query.
Using Oracle hints can be very complicated, and Oracle developers
only use hints as a last resort, preferring to alter the statistics
to change the execution plan. Oracle contains more than 124 hints,
and many of them are not found in the Oracle documentation.
Undocumented Hints:
|
bypass_recursive_check
bypass_ujvc
cache_cb
cache_temp_table
civ_gb
collections_get_refs
cube_gb
cursor_sharing_exact
deref_no_rewrite
dml_update
domain_index_no_sort
domain_index_sort
dynamic_sampling
dynamic_sampling
_est_cdn
expand_gset_to_union
force_sample_block
gby_conc_rollup
global_table_hints
hwm_brokered
|
ignore_on_clause
ignore_where_clause
index_rrs
index_ss
index_ss_asc
index_ss_desc
like_expand
local_indexes
mv_merge
nested_table_get_refs
nested_table_set_refs
nested_table_setid
no_expand_gset_to_union
no_fact
no_filtering
no_order_rollups
no_prune_gsets
no_stats_gsets
no_unnest
nocpu_costing |
overflow_nomove
piv_gb
piv_ssf
pq_map
pq_nomap
remote_mapped
restore_as_intervals
save_as_intervals
scn_ascending
skip_ext_optimizer
sqlldr
sys_dl_cursor
sys_parallel_txn
sys_rid_order
tiv_gb
tiv_ssf
unnest
use_ttt_for_gsets |
|
Hints can be used to alter optimizer execution plans. Remember, an
optimizer hint is a directive that is placed inside comments inside
the SQL statement and used in those rare cases where the optimizer
makes an incorrect decision about the execution plan. Since hints
are inside comments, it is important to ensure that the hint name is
spelled correctly and that the hint is appropriate to the query.
For example, the following hint is invalid because
first_rows access and
parallel access are mutually exclusive. That is because parallel
access always assumes a full-table scan and
first_rows favors index
access.
-- An invalid hint
select /*+ first_rows parallel(emp,8)*/
emp_name
from
emp
order by
ename;
Some Oracle professionals will place hints together to reinforce
their wishes. For example, if there is a SMP server with eight or
more CPUs, one may want to use Oracle Parallel Query to speed up legitimate full-table scans.
When using parallel query, one should seldom turn on parallelism at
the table level, alter table
customer parallel 35, because the setting of parallelism for
a table influences the optimizer. This causes the optimizer to see
that the full-table scan is inexpensive. Hence, most Oracle
professionals specify parallel query on a query-by-query basis,
combining the full hint with the parallel hint to ensure a fast
parallel full-table scan:
-- A valid hint
select /*+ full parallel(emp,35)*/
emp_name
from
emp
order by
ename;
Now that the general concept of hints has been introduced, it is an
appropriate to look at one of the most important hints for optimizer
tuning.
The ordered hint
determines the driving table for the query execution and also
specifies the order that tables are joined together. The ordered
hint requests that the tables should be joined in the order that
they are specified in the FROM clause, with the first table in the
FROM clause specifying the driving table. Using the ordered hint can
save a huge amount of parse time and speed SQL execution because the
optimizer is given the best order to join the tables.
The Ion tool
is also excellent for identifying SQL to tune and it can show SQL
execution over time with stunning SQL graphics.
For example, the following query uses the
ordered hint to join the
tables in their specified order in the FROM clause. In this example,
the execution plan is further refined by specifying that the
emp to
dept join use a hash join
and the sal to
bonus join uses a nested
loop join:
select
/*+ ordered use_hash (emp, dept) use_nl (sal, bon) */
from
emp,
dept,
sal,
bon
where . . .
Of course, the ordered
hint is most commonly used in data warehouse queries or in SQL that
joins more than five tables.
SQL execution is dynamic, and tuning a SQL statement for the current
data may not be optimal at a future date. The following section
provides a brief tour of the Oracle10g Automated Workload Repository
(AWR) and how it can be used to perform proactive SQL tuning.
SEE CODE DEPOT FOR FULL SCRIPTS
