Performance & Availability
Understanding
System Statistics
by Jonathan Lewis
Understanding
the optimizer's use of system statistics can make a big difference when
migrating to a new version of the Oracle Database.
What
is the most important component of the Oracle Database engine? My vote goes to
the optimizer. Everything the database does is SQL, and every piece of SQL has
to be translated into something that can work efficiently. Whatever you do with
your data, the optimizer gets involved.
The
Cost-Based Optimizer (CBO) is a subtle and complex piece of code, so when it
first appeared in Oracle7 it wasn't too surprising that a few problems existed.
However, it didn't take many patch releases before it was perfectly viable to
implement a purely cost-based system. I recall setting up several such systems
in 7.1, and at least one major system in 7.2.
Nevertheless,
a surprising number of sites avoided the CBO for quite a long time—the reason
being that many developers and DBAs were still
thinking in a "rule-based" manner, and therefore doing things that
make life difficult for the CBO. Basically, they were "lying" to
their optimizer and then wondering why it made poor decisions.
I
suspect the reason for my early success was that I happened to notice something
that is now well understood: the fact
that the CBO "likes" to do tablescans when
the value for parameter db_file_multiblock_read_count is big, and "prefers" to use
indexes when the value is small.
As
we now know, a few other parameters display similar characteristics — that is,
RBO liked them big but CBO needed them small. And, of course, there was the
little detail that in a '"tie-break" the CBO will work down the list of tables in the from clauses, whereas the RBO
works up that list.
CBO
is very different from RBO — but there are just a few particularly critical
differences that you must know about, because if you don't understand them and
keep treating CBO as you would treat the RBO, you can give yourself a number of
unnecessary problems.
History Repeats Itself
The
CBO keeps evolving. Some releases of Oracle introduce minor enhancements, some
releases introduce major enhancements, and some releases change the paradigm.
Unless
you spot the major enhancements and paradigm shifts, you can end up fighting
the CBO for years, trying to work around remembered weaknesses instead of
playing to new strengths — just as many people did in the shift from RBO to
CBO.
So
are there any recent changes in the CBO that could have as much impact on our
thinking as the change from the RBO? The answer is "yes."
In
Oracle9i Database, Oracle added system statistics (which I rate as a new
paradigm) and automatic workarea sizing (which I rate
as a major enhancement). In Oracle Database 10g, we get various tuning
advisors that optimize your developers' time by helping them produce more
efficient SQL, indexing, materialized views or PL/SQL more quickly. We also get
query profiles, a major enhancement that stores statistical information in the
database for the purpose of helping the optimizer make better decisions. This
information is particularly useful when you have to deal with untouchable
third-party SQL.
Across
both versions we get dynamic_sampling, a very useful
feature introduced in 9i that is particularly beneficial for data
warehouse and decision support systems. But if you haven't paid any attention
to dynamic sampling in 9i, it can become a nuisance if you're running an
OLTP system on 10g because dynamic sampling is effectively enabled by
default, and it's probably an unnecessary overhead.
The
most important of all these changes is the introduction of system statistics.
In fact, I would go so far as to say that one of the key steps in migrating
from Oracle8 to Oracle9i is enabling system statistics and working
through the impact they have on your system. Similarly, system statistics are
really quite critical to getting the most out of the optimizer in 10g,
and the best time to get familiar with system statistics is the moment you
decide to migrate from 8i.
This
feature is so significant that I'll devote the rest of this article to nothing
else, saving dynamic_sampling and profiles as topics
for another day.
System Statistics
Prior
to Oracle9i, the CBO based its calculations on the number of I/O
requests that would be needed to satisfy a query, using various constants to
massage figures for tablescans and throwing in a few
rules to account for things such as caching of small indexes. (See my
DBAzine.com article "Why isn't Oracle using my index?"
[for an introduction to this topic.)
Initially
some of the assumptions built into the optimizer were a little naive, but as
time passed assumptions were refined, algorithms improved, and new features
implemented. However, the side effect of estimating I/O requests became a
persistent limitation.
In
9i, Oracle introduced cpu_costing, a mechanism
that allows the CPU cost of an operation to be included as part of the overall
estimate. This feature is enabled in 9i only if you collect system
statistics; in 10g, it's enabled by default.
So
what does cpu_costing do, and what are system
statistics exactly? Let's start with system statistics, using a couple of calls
to the dbms_stats package to demonstrate. (This
example uses 9.2.0.4, and your account will need to be granted the role gather_system_statistics
for it to work.)
execute dbms_stats.gather_system_stats('Start');
-- some time delay while the database is under a typical
workload
execute dbms_stats.gather_system_stats('Stop');
To
see what you have done, you can query a table (owned by the SYS schema) called aux_stats$. After gathering system statistics, this table
will contain a few critical numbers used by the new optimizer algorithms to
calculate costs. (You have to flush the shared_pool
to invalidate existing execution plans, though.) The following query will show
you the current settings:
select pname, pval1
from sys.aux_stats$
where sname =
'SYSSTATS_MAIN';
The
exact list of results is version dependent (the code is still evolving, some
versions of Oracle gather more statistics than others) but you will probably
see something like this:
PNAME PVAL1
------------------------------
----------
CPUSPEED 564
MAXTHR 13899776
MBRC 6
MREADTIM 10.496
SLAVETHR 182272
SREADTIM 1.468
10g
also introduces a few extra rows with values that are set as the database
starts up:
CPUSPEEDNW 904.86697
IOSEEKTIM 10
IOTFRSPEED 4096
I've
quoted the SQL for convenience; in fact, the approved method for viewing this
information is the get_system_stats
procedure in the dbms_stats package. There is also a set_system_stats procedure if you want to
"adjust" the values without gathering them properly.
Tablescans
There
are two significant changes that apply to the optimizer cost calculations when
system statistics are available. You will note first that sys.aux_stats$ holds values for the following:
Using
this information, Oracle can estimate how long it will take to do a tablescan (or index fast full scan). The arithmetic is
easy: it's just the number of multi-block reads needed to do the scan,
multiplied by the average time to do a multi-block read. Ignoring the minor
changes due to automatic segment space management, we just take the high-water
mark, and work from there:
Time
to completion = mreadtim *
HWM / MBRC.
Rather
then reporting this "time to completion" as the cost of the query,
Oracle restates the time in terms of the equivalent number of single block
reads. To do this, simply divide the time to completion by the average time for
a single-block read.
Cost
= time to completion / sreadtim
Or,
putting the two formulae together and rearranging terms:
Cost
of tablescan = (HWM /
MBRC) * (mreadtim / sreadtim)
From
this example, you can see that the cost of a query is the time to completion of
a query, but expressed in units of single block reads rather than in proper
time units.
When
you start using system statistics, the optimizer automatically starts to be
more "sensible" when choosing between tablescans
and indexed access paths because the cost of the multiblock
reads used for tablescans will include a proper and
appropriate time component.
Historically,
the cost of a tablescan was simply:
Cost
of tablescan = HWM /
(modified db_file_multiblock_read_count).
This
formula made little allowance for the fact that your choice of value for the
parameter db_file_multiblock_read_count
could be unrealistic, nor did it allow for the extra
time that an extremely large db_file_multiblock_read_count
would take compared to a single block read.
This
weakness is largely why Oracle created the optimizer_index_cost_adj parameter in 8.1.6 to
allow you to introduce a factor that was similar in intent to the mreadtim
that you collect in system statistics. (You may have spotted the similarity
between the mreadtim/sreadtim
element in the new cost formula, and the common method for estimating a
sensible optimizer_index_cost_adj.)
But there are some unexpected side effects to using the optimizer_index_cost_adj
parameter that can cause problems and the mechanisms that come into play when you
start using system statistics are much more robust.
It
is still meaningful, by the way, to use optimizer_index_cost_adj as a clue to table
caching effects (specifically, what percentage of single block table reads are
likely to turn into real read requests) even when using system statistics.
There are some indications in 10g, though, that even this clue will
become unnecessary in the not too distant future.
CPU Costs
System
statistics do more than correct for the I/O and time trade-off between single-block
and multi-block reads. They also cater for two further enhancements (or
corrections) to costing: first, Oracle can be even better at balancing tablescans against indexed access paths; second, Oracle can
be smart about rearranging predicate order.
Note
how the statistics include the apparent CPU speed, nominally in MHz. Don't be
alarmed if this is nothing like the actual CPU speed of your system — the
figure is probably just an internal calibration of a baseline operation that
Oracle uses to produce relative CPU costs of other operations. On one machine
running at 2.8GHz, I typically come up with an apparent CPU speed of a few
hundred MHz. (Bear in mind that what you see is the nominal speed of a single
CPU, not the sum of all the CPUs in a multi-CPU system.)
So
why does it help the optimizer to know the (apparent) speed of your CPU?
Consider an example where you have a choice:
Oracle
may decide, based purely on the number and speed of single-block and
multi-block reads, that a tablescan
would be quicker. But how much CPU will this take if the tablescan
requires a test like the following on 10,000 rows:
date_col between to_date('01-Jan-2004')
and to_date('02-Jan-2004);
CPU
operations take time as well, and if the number and nature of the tests that
have to be performed on a tablescan require a lot of
CPU, Oracle factors this cost into the equation and might switch a query from a
CPU-intensive tablescan to an index range scan. You
can see this from the formula in the Oracle9i Database Performance
Tuning Guide and Reference (A96533 p. 9-22):
Cost = (
#SRds *
sreadtim +
#MRds *
mreadtim +
#CPUCycles
/ cpuspeed
) / sreadtim
The
#CPUCycles
value in this equation is visible in the cpu_cost
column of the newer versions of the plan_table used
by the Explain Plan facility. (Here's another small, but important, detail of
optimizer enhancements: always check to see how the explain plan facility has
evolved.)
In
fact, if you use this column in the cost equation, the formula needs a fudge
factor thrown in—cpuspeed is recorded in MHz, and the
other timings are given in milliseconds, so the CPU component of the formula
looks as if it should be adjusted by a factor of 1,000:
#CPUCycles / (cpuspeed * 1000)
Predicate Order
Apart
from the high-level choices, knowledge of CPU operations and the complexity of
predicates can allow Oracle to do things that you might never consider in a
manual tuning exercise. The best demonstration of this principle comes from a
(slightly contrived) worked example. For the purposes of a repeatable test, the
following code ran under 9.2.0.4 and used a locally managed tablespace
with a uniform extent size of 1MB, and manual segment space allocation.
create table t1 as
select
trunc(sysdate-1) +
rownum/1440 d1,
rownum n1,
rpad('x',100) padding
from
all_objects
where
rownum <= 3000
;
alter table t1
add constraint
t1_pk primary key (d1,n1)
using index
(create index t1_pk on t1(d1,n1))
;
execute dbms_stats.gather_table_stats(user,'t1',cascade=>true)
Based
on this data set, here are two virtually identical queries. Which one of them
will be faster? The index hint is there just in case your test database has
some unexpected parameter settings that push the optimizer into doing a tablescan:
select /*+ index(t1) */
padding
from t1
where n1 = 2800
and d1 >= trunc(sysdate)
;
select /*+ index(t1) */
padding
from t1
where d1 >= trunc(sysdate)
and n1 = 2800
;
Notice
that the only difference between the two queries is the order of the
predicates. If you run the queries through autotrace
(set autotrace on), you'll find that they both
produce the same plan and the same number of consistent gets to execute.
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=112)
1
0 TABLE ACCESS (BY INDEX ROWID)
OF 'T1' (Cost=8 Card=1 Bytes=112)
2
1 INDEX (RANGE SCAN) OF
'T1_PK' (NON-UNIQUE) (Cost=7 Card=1562)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
So
when I put each query in turn into a PL/SQL loop that executed it 10,000 times,
why did the first query take 4.34 CPU seconds to run 10,000 times (at 2.8GHz)
and the second query take 13.42 CPU seconds if system statistics were not
enabled? And why, when I enabled system statistics, did both queries run in the
shorter time?
The
answer isn't visible in autotrace, but if you run the
two queries through Oracle's dbms_xplan package to
get the full execution plan, you see the following for the faster query
when system statistics are not enabled:
Id Operation Name Rows
Bytes Cost
----- ---------------------------- ------------
------ ------- -------
0 SELECT STATEMENT 1 110
8
1 TABLE ACCESS BY INDEX ROWID T1 1 110
8
* 2
INDEX RANGE SCAN T1_PK 1562 7
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."D1">=TRUNC(SYSDATE@!) AND "T1"."N1"=2800)
filter("T1"."N1"=2800
AND "T1"."D1">=TRUNC(SYSDATE@!))
Note: cpu costing is off
If
you swap the order of the predicates (still without system statistics) to check
the slower query, you will spot a small change in the Predicate
Information section:
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."D1">=TRUNC(SYSDATE@!) AND "T1"."N1"=2800)
filter("T1"."D1">=TRUNC(SYSDATE@!) AND "T1"."N1"=2800)
However,
when you enable system statistics (specifically it's the cpu_costing
component that counts), then it doesn't matter which way round you write the
predicates in your query—the execution plan shows that the filter()
line operates the numeric check before the date test, and the query runs in the
shorter time.
There
are two important points to address here. First, why is there a significant
difference in the run time, and second, what was Oracle doing when it found the
quicker path automatically?
The
difference in run time depends on the fact that I engineered the data set and
the query to force Oracle to check both columns (d1 and n1) for every index
entry covered by the index range scan, and the specific predicate values
required Oracle to check 1,560 rows. Of those 1,560 rows, every single one will
pass the date test, but only one will pass the numeric test. So, by switching
the order of the predicates manually, I was actually choosing between
executing:
1,560
date tests that pass, followed by 1,560 numeric tests
and
1,560
numeric tests of which one passes, and one subsequent date test.
The
difference in run time is entirely due to the absence of 1,559 date tests
(multiplied by the 10,000 iterations of the loop).
How
did Oracle find the optimal order of predicate evaluation when cpu_costing was enabled? First, as part of its
"traditional" optimization mechanism, the optimizer always works out
the selectivity (fraction of rows that will be returned) for each of the
predicates on a table. Second, Oracle keeps an internal reference list of basic
operations with their costs in the form: "operation X requires N cpu units," and this list is calibrated against the cpuspeed element of system statistics.
Combining
these two sets of figures, the optimizer can work out figures for "number
of tests required for this predicate * CPU cost of test" and "number
of rows for next predicate because of this predicate" as it re-arranges
the order of predicates—all it has to do is minimize the total CPU cost across
all tests.
In
summary, when CPU costing is enabled, a new code path comes into play that may
improve the performance of some of your queries without affecting the visible
execution path, and you will only understand what's going on if you check the
new explain plan output. (This re-arrangement of predicate order can be stopped
by using the hint /*+ ordered_predicates */, but the
hint is deprecated in 10g.)
Conclusion
Although
system statistics haven't yet achieved much popularity, they are not only
important overall but actually critical to certain optimization options in 10g.
System statistics give the optimizer more of the "truth" about how
your system really performs, and therefore allow the optimizer to produce a
better match between estimated and actual query execution time.
System
statistics also include details that allow the optimizer to trade I/O costs
against CPU costs. At a gross level, knowledge of CPU speeds allow even better
decisions on execution paths; at a finer level of detail, Oracle even allows
for re-arranging the order of predicate testing to minimize CPU time after the
path has been decided.
If
you are still running 8i and plan to migrate to Oracle9i, you
should include system statistics as part of your migration plan. If you are
running 9i without using system statistics, you should enable them as
soon as possible. If you are migrating to 10g without first implementing
system statistics on 9i, then make some allowances for investigating
system statistics in your test plans. If you don't, you may spend a lot of time
trying to understand why odd things are happening in those places where you've
put in a workaround for a problem that wouldn't have existed if you had been
doing the right thing in the first place.
Jonathan Lewis (http://www.jlcomp.demon.co.uk) is a freelance consultant with more than 18 years experience in Oracle. He specializes in physical database design and the strategic use of the Oracle database engine, is author of Practical Oracle 8i: Designing Efficient Databases (Addison-Wesley).