Databases Reference
In-Depth Information
that may be submitted to the Oracle Database. And, oh yes, they have been in a process
of continual improvement for over 15 years.
In addition, there are three advantages that the query optimizer has over your discretion
in all cases:
• The optimizer sees the structure of the entire database. Many Oracle Databases
support a variety of applications and users and it's quite possible that your system
shares data with other systems, making the overall structure and composition of
the data somewhat out of your control. In addition, you probably designed and
tested your systems in a limited environment, so your idea of the optimal execution
path may not match the reality of the production environment, especially as it
evolves.
• The optimizer has a dynamically changing view of the database and its data. The
statistics used by the cost-based optimizer can change with each automated col‐
lection. In addition to the changing statistical conditions, the internal workings of
the optimizer are occasionally changed to accommodate changes in the way the
Oracle Database operates. Since Oracle9
i
, the cost-based optimizer takes into ac‐
count the speed of the CPU, and since Oracle Database 10
g
, leverages statistics on
I/O. If you force the selection of a particular query plan with a hint, you might not
benefit from changes in Oracle.
• A bad choice by the optimizer may be a sign that something is amiss in your data‐
base. In the overwhelming majority of cases, the optimizer selects the optimal ex‐
ecution path. What may be seen as a mistake by the query optimizer can, in reality,
be traced to a misconception about the database and its design or to an improper
implementation. A mistake is always an opportunity to learn, and you should always
take advantage of any opportunity to increase your overall understanding of how
Oracle and its optimizer work.
We recommend that you consider using hints only when you have determined them to
be absolutely necessary by thoroughly investigating the causes for an optimization
problem. The hint syntax was included in Oracle syntax as a way to handle exceptional
situations, rather than to allow you to circumvent the query optimizer. If you've found
a performance anomaly and further investigation has led to the discovery that the query
optimizer is choosing an incorrect execution path, then and only then should you assign
a hint to a query. In other words, do not use a hint until you can explain why the optimizer
made a poor choice in the first place.
Even in this situation, we recommend that you keep an eye on the hinted query in a
production environment to make sure that the forced execution path is still working
optimally.
