Gather Statistics

52 Pages·2010·0.31 MB·English

Active
Statistics
Wolfgang Breitling
www.centrexcc.com
Copyright 2008 Centrex Consulting Corporation. Personal use of this material is
permitted. However, permission to reprint/republish this material for advertising
and promotional purposes or for creating new collective works for resale or
redistribution to servers or lists, or to reuse any copyrighted component of this
work in other works must be obtained from Centrex Consulting.
Who am I
Independent consultant since 1996
specializing in Oracle and Peoplesoft setup,
administration, and performance tuning
Member of the Oaktable Network
25+ years in database management
DL/1, IMS, ADABAS, SQL/DS, DB2, Oracle
Oracle since 1993 (7.0.12)
OCP certified DBA - 7, 8, 8i, 9i
Mathematics major from University of Stuttgart
2 © Wolfgang Breitling, Centrex Consulting Corporation UKOUG - December 3, 2008
Focus
(cid:153) Gather Statistics
(cid:153) Gather Partition Statistics
(cid:153) Gather Histograms
(cid:153) Clone Statistics
(cid:153) Modify or RYO Statistics
(cid:153) prepare_column_stats
3 © Wolfgang Breitling, Centrex Consulting Corporation UKOUG - December 3, 2008
Gather_Stats and the Stattab Table
(cid:153)(cid:153) gather_object_stats(…, stattab=> , statid=> )
the current statistics in the dictionary get saved into
stattab under the given statid
new statistics are gathered into the dictionary
(cid:153)(cid:153) gather_system_stats(…, stattab=> , statid=> )
the current statistics in the dictionary, if any, are
unaffected
new statistics are gathered directly into stattab under
the given statid
4 © Wolfgang Breitling, Centrex Consulting Corporation UKOUG - December 3, 2008
Gather_System_Stats Strategy
1. Gather system stats into a stattab table using fixed
times and intervals with distinct statids e.g. schedule
hourly or at certain times each day:
gather_system_stats(interval=>60,
statid=>'S'||to_char(sysdate,'yyyymmddhh24miss'), … );
2. Process this data, e.g. using averages, and use those
values to set system statistics with
set_system_stats. Make sure mreadtm is > sreadtm!
select statid, n3 cpuspeed, n1 sreadtim, n2 mreadtim, n11 mbrc
from stats_table where type = 'S' and c4 = 'CPU_SERIO' order by statid
5 © Wolfgang Breitling, Centrex Consulting Corporation UKOUG - December 3, 2008
dbms_stats defaults
select sname, sval1, spare4 from sys.optstat_hist_control$
SNAME SVAL1 SPARE4
SKIP_TIME
TRACE 0
DEBUG 0
SYS_FLAGS 0
STATS_RETENTION 31
CASCADE DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE NULL
METHOD_OPT FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE DBMS_STATS.AUTO_INVALIDATE
GRANULARITY AUTO
AUTOSTATS_TARGET AUTO
APPROXIMATE_NDV TRUE
PUBLISH TRUE
STALE_PERCENT 10
INCREMENTAL FALSE
INCREMENTA_INTERNAL_CONTROL TRUE
6 © Wolfgang Breitling, Centrex Consulting Corporation UKOUG - December 3, 2008
Words of Caution*
(cid:153) ESTIMATE_PERCENT had a default of 100% on 9i. 10g defaults
to for sample size, […].
DBMS_STATS.AUTO_SAMPLE_SIZE
Small sample sizes are known to produce poor number of
distinct values NDV on columns with sskkeewweedd ddaattaa ((wwhhiicchh aarree
ccoommmmoonn)), thus generate sub-optimal plans. Use an estimate
of 100% if your window maintenance can afford it, eevveenn iiff tthhaatt
mmeeaannss ggaatthheerr ssttaattiissttiiccss lleessss oofftteenn.
(cid:153) METHOD_OPT had a default of "FOR ALL COLUMNS SIZE 1" on
9i, which basically meant . 10g defaults to
NO HISTOGRAMS
, which means decides in which columns a
AUTO DBMS_STATS
histogram mmaayy help to produce a better plan. It is known that
iinn ssoommee ccaasseess,, tthhee eeffffeecctt ooff aa hhiissttooggrraamm iiss aaddvveerrssee ttoo tthhee
ggeenneerraattiioonn ooff aa bbeetttteerr ppllaann.
* Metalink Note 465787.1. Emphasis all mine
7 © Wolfgang Breitling, Centrex Consulting Corporation UKOUG - December 3, 2008
Gather Statistics Performance
(10.2.0.3)
gather_table_stats(…,method_opt=>'for all columns size 1', cascade=>true)
NAME 100 10 1 0.1 0.01 auto
sql execute elapsed time 22:55.4 01:53.9 02:14.5 01:36.5 02:17.1 04:42.0
DB CPU 06:26.0 01:22.9 00:58.3 00:47.1 00:59.1 02:40.3
session logical reads 475,464 384,039 279,303 52,801 65,639 719,550
index fast full scans (full) 0 4 4 4 4 6
table fetch by rowid 161 259 259 259 259 259
table scans (long tables) 1 1 1 1 2 3
sorts (rows) 72,002,374 7,208,584 2,320,365 1,934,490 1,948,871 3,598,961
physical reads direct temp tblsp 139,972 0 0 0 0 0
physical writes 139,972 0 0 0 0 0
workarea executions - onepass 5 0 0 0 0 0
workarea executions - optimal 14 32 31 31 32 33
8 © Wolfgang Breitling, Centrex Consulting Corporation UKOUG - December 3, 2008
Gather Statistics Performance
(11.1.0.6)
gather_table_stats(…,method_opt=>'for all columns size 1', cascade=>true)
NAME 100 10 1 0.1 0.01 auto
sql execute elapsed time 21:57.5 02:05.1 01:42.1 01:57.1 02:06.4 02:00.8
DB CPU 06:40.7 01:07.6 00:40.9 00:42.8 00:43.9 01:00.3
session logical reads 489,324 397,822 293,683 66,384 88,169 395,225
index fast full scans (full) 0 4 4 4 4 4
table fetch by rowid 155 215 215 215 215 313
table scans (long tables) 1 1 1 1 2 1
sorts (rows) 72,000,017 7,204,143 2,258,623 1,982,680 1,965,031 1,855,556
physical reads direct temp tblsp 139,938 0 0 0 0 0|
physical writes 139,938 0 0 0 0 0
workarea executions - onepass 5 0 0 0 0 0
workarea executions - optimal 10 28 27 27 28 50
9 © Wolfgang Breitling, Centrex Consulting Corporation UKOUG - December 3, 2008
Gather Statistics Performance
(10.2.0.3)
gather_table_stats(…,method_opt=>'for all columns size 1', cascade=>false)
NAME 100 10 1 0.1 0.01 auto
sql execute elapsed time 19:43.6 02:18.6 01:20.5 01:14.6 01:57.9 04:15.3
DB CPU 03:34.1 01:18.2 00:48.1 00:35.8 00:51.3 02:34.2
session logical reads 372,870 372,817 273,456 46,930 59,223 716,466
index fast full scans (full) 0 0 0 0 0 2
table fetch by rowid 42 42 42 42 42 42
table scans (long tables) 1 1 1 1 2 3
sorts (rows) 44,002,358 4,406,505 437,617 45,654 57,842 1,660,336
physical reads direct temp tblsp 100,551 0 0 0 0 0
physical writes 100,551 0 0 0 0 0
workarea executions - onepass 1 0 0 0 0 0
workarea executions - optimal 6 7 7 7 8 9
10 © Wolfgang Breitling, Centrex Consulting Corporation UKOUG - December 3, 2008