Databases Reference
In-Depth Information
Height-based and value-based (frequency) histograms
There are two types of histograms—height-based and value-based:
F
In height-based histograms, the same number of values are placed in each bucket
approximately, so we can have multiple buckets ending at the same
ENDPOINT_
VALUE
. In this situation the
ENDPOINT_NUMBER
represents the cumulative number
of rows.
F
In value-based histograms, also known as frequency histograms, we have a number
of buckets greater than or equal to the number of distinct value in the field, as in our
previous example.
According to Oracle documentation, the histogram will be created
as value-based—also known as frequency histograms— by default,
when the number of distinct values is less than or equal to the
number of histogram buckets specified.
See also
F
The
Introducing
Adaptive
Cursor
Sharing
for
bind
variables
peeking
recipe in this
chapter, for a more detailed description of possible problems related to the use of
bind variables
F
The
Collecting
statistics
recipe in this chapter, to see how to collect and transport
statistics between different databases
Managing stored outlines
In production environments, there is a very simple golden rule about database performance—it's
better to have a database that always performs quite well than a database that performs even
10 times faster but not always; that is, a database whose performance sometimes dips.
Execution plan stability is a must for ensuring performance that persists over time; in
production environments, we need an optimal and predictable performance.
In this recipe, we will see how to create stored outlines to achieve execution plan stability and
how to manage these stored outlines.
Getting ready
To create stored outlines, we need an appropriate grant. Connect to the database as
SYSDBA
,
and grant the user
SH
permission to create stored outlines:
CONNECT / AS SYSDBA
GRANT CREATE ANY OUTLINE TO sh;
