Oracle Statistics Tuning


Oracle uses rule-based or cost-based optimisation.

Rule-Based Optimisation

Rule-based optimisation uses a predefined set of parsing rules used to create query execution plans. Rule-based optimisation is used when statistics for an object are not present. Rule based optimisation can make use of stored outlines in order to preserve previously generated execution plans. Thus these execution plans do not require regeneration by the optimiser on re-execution of the statements involved.

Cost-Based Optimisation

Cost-based optimisation selects the lowest-cost path approach and is used when statistics are created for an object. Note that these statistics must be kept up-to-date. Some new features are only available when utilising cost-based optimisation. The OPTIMZER_MODE parameter must be set to CHOOSE to enable cost-based optimisation.

Statistics can be collected using the ANALYZE command.

ANALYZE table | index | cluster <object name> COMPUTE | ESTIMATE STATISTICS [SAMPLE ROWS | <percentage>];

The cost based approach by default optimises for batch applications in terms of throughput. To optimise for response time in interactive systems. To change the goal of the cost based optimisation to response time execute the following command.

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS | ALL_ROWS;

Parameters

Histograms

Histograms can be stored and used as a map to unevenly distributed data which can assist statisitics in cost based optimisation. Like statistics histograms require maintenance. Do not generate histograms for data columns without extremely skewed distributions of data values. Create histograms on columns used often in WHERE clauses, ie. columns used in indexes. Use DBMS_STATS.GETHER_TABLE_STATS ([table[, table ...]) to generate histograms. Histogram sampling rates can also be varied, defaulted at 75.

Gathering Statistics

Statistics should be generated regularly for all tables, clsuters and indexes. Out-of-date statistics will affect performance. Statistics can be generated as an estimated percentage through random samples of records, an exact computation or a user-defined methodology. Exact computations of all records will give the best results. The DBMS_STATS package includes GATHER_INDEX, TABLE, SCHEMA and DATABASE_STATS procedures. These procedures will perform the same function as the ANALYZE object ... command. Note that indexes should always be analysed first since table analysis will utilise the newly created index statistics. Note that when statisitics are gathered on a table all parsed SQL code and code blocks are invalidated and require reparsing and reloading into memory. Therefore pinned code should really require bouncing the database and re-pinning on completion of statistics gathering in order to not have defragmented memory pinned objects.

Automated Statistics Gathering

Statistics can be gathered automatically for tables only using the MONITORING clause of the CREATE and ALTER TABLE statements. This can only be useful in very large databases. Since indexes are used for statistics gathering on tables small databases will not benefit. Also what occurs to parsed code blocks previosly pinned into memory. Generally large databases are not really amenable to being restarted so automated statisitics gathering is a bit dubious as to it's value. The state of table statistics can also be a controlling factor in terms of stale and non-existent statisitics.

Timed Statistics

Set the TIMED_STATISITICS = TRUE to enable the retention of timed statisitcs in the X$ tables. These timed statistics are available through the X$ overlays, ie. the V$ views.

Selecting Changing Statistics Values

select
	 name
	,value
	,to_char(sysdate,'ssss') "Secs"
	,(value-&value) "Change"
	,(to_char(sysdate,'sssss') - &secs) "Change"
	,(value - &value) / (to_char(sysdate,'sssss') - &secs) "Rate"
from v$sysstat
order by name;

NAME                                     VALUE    Change    Change       Rate
------------------------------ --------------- --------- --------- ----------
CPU used by this session                     0         0     76913        0.0
CPU used when call started                   0         0     76913        0.0
CR blocks created                            0         0     76913        0.0
Cached Commit SCN referenced                 0         0     76913        0.0
Commit SCN cached                            0         0     76913        0.0
DBWR buffers scanned                       270       270     76913        0.0
DBWR checkpoint buffers writte               0         0     76913        0.0
DBWR checkpoints                             0         0     76913        0.0
DBWR forced writes                           0         0     76913        0.0
DBWR free buffers found                    265       265     76913        0.0
DBWR lru scans                               3         3     76913        0.0
DBWR make free requests                      3         3     76913        0.0
DBWR revisited being-written b               0         0     76913        0.0
DBWR summed scan depth                     386       386     76913        0.0
DBWR transaction table writes                0         0     76913        0.0
DBWR undo block writes                       3         3     76913        0.0
DDL statements parallelized                  0         0     76913        0.0
DFO trees parallelized                       0         0     76913        0.0
DML statements parallelized                  0         0     76913        0.0
PX local messages recv'd                     0         0     76913        0.0
PX local messages sent                       0         0     76913        0.0
PX remote messages recv'd                    0         0     76913        0.0
PX remote messages sent                      0         0     76913        0.0
Parallel operations downgraded               0         0     76913        0.0
Parallel operations downgraded               0         0     76913        0.0
Parallel operations downgraded               0         0     76913        0.0
Parallel operations downgraded               0         0     76913        0.0
Parallel operations downgraded               0         0     76913        0.0
Parallel operations not downgr               0         0     76913        0.0
SQL*Net roundtrips to/from cli             136       136     76913        0.0
SQL*Net roundtrips to/from dbl               0         0     76913        0.0
Unnecesary process cleanup for               0         0     76913        0.0
background checkpoints complet               0         0     76913        0.0
background checkpoints started               0         0     76913        0.0
background timeouts                      1,438      1438     76913        0.0
branch node splits                           0         0     76913        0.0
buffer is not pinned count              15,438     15438     76913        0.2
buffer is pinned count                  27,131     27131     76913        0.4
bytes received via SQL*Net fro          15,503     15503     76913        0.2
bytes received via SQL*Net fro               0         0     76913        0.0
bytes sent via SQL*Net to clie          93,839     93839     76913        1.2
bytes sent via SQL*Net to dbli               0         0     76913        0.0
calls to get snapshot scn: kcm             724       724     76913        0.0
calls to kcmgas                             14        14     76913        0.0
calls to kcmgcs                              5         5     76913        0.0
calls to kcmgrs                              0         0     76913        0.0
change write time                            0         0     76913        0.0
cleanouts and rollbacks - cons               0         0     76913        0.0
cleanouts only - consistent re              10        10     76913        0.0
cluster key scan block gets                812       812     76913        0.0
cluster key scans                          443       443     76913        0.0
cold recycle reads                           0         0     76913        0.0
commit cleanout failures: bloc               0         0     76913        0.0
commit cleanout failures: buff               0         0     76913        0.0
commit cleanout failures: call               0         0     76913        0.0
commit cleanout failures: cann               0         0     76913        0.0
commit cleanout failures: hot                0         0     76913        0.0
commit cleanout failures: writ               0         0     76913        0.0
commit cleanouts                             9         9     76913        0.0
commit cleanouts successfully                9         9     76913        0.0
consistent changes                          10        10     76913        0.0
consistent gets                         27,419     27419     76913        0.4
current blocks converted for C              10        10     76913        0.0
cursor authentications                      35        35     76913        0.0
data blocks consistent reads -               0         0     76913        0.0
db block changes                            55        55     76913        0.0
db block gets                              418       418     76913        0.0
deferred (CURRENT) block clean               9         9     76913        0.0
dirty buffers inspected                      1         1     76913        0.0
enqueue conversions                          0         0     76913        0.0
enqueue deadlocks                            0         0     76913        0.0
enqueue releases                           342       342     76913        0.0
enqueue requests                           349       349     76913        0.0
enqueue timeouts                             0         0     76913        0.0
enqueue waits                                0         0     76913        0.0
exchange deadlocks                           0         0     76913        0.0
execute count                              640       640     76913        0.0
free buffer inspected                        2         2     76913        0.0
free buffer requested                    2,435      2435     76913        0.0
global cache convert time                    0         0     76913        0.0
global cache convert timeouts                0         0     76913        0.0
global cache converts                        0         0     76913        0.0
global cache cr block receive                0         0     76913        0.0
global cache cr blocks receive               0         0     76913        0.0
global cache cr requests block               0         0     76913        0.0
global cache cr timeouts                     0         0     76913        0.0
global cache defers                          0         0     76913        0.0
global cache freelist waits                  0         0     76913        0.0
global cache get time                        0         0     76913        0.0
global cache gets                            0         0     76913        0.0
global lock async converts                   0         0     76913        0.0
global lock async gets                       0         0     76913        0.0
global lock convert time                     0         0     76913        0.0
global lock get time                         0         0     76913        0.0
global lock releases                         0         0     76913        0.0
global lock sync converts                    0         0     76913        0.0
global lock sync gets                        0         0     76913        0.0
hot buffers moved to head of L             463       463     76913        0.0
immediate (CR) block cleanout               10        10     76913        0.0
immediate (CURRENT) block clea               0         0     76913        0.0
index fast full scans (direct                0         0     76913        0.0
index fast full scans (full)                 0         0     76913        0.0
index fast full scans (rowid r               0         0     76913        0.0
instance recovery database fre               0         0     76913        0.0
kcmccs called get current scn                0         0     76913        0.0
kcmgss read scn without going                0         0     76913        0.0
kcmgss waited for batching                   0         0     76913        0.0
leaf node splits                             0         0     76913        0.0
logons cumulative                            8         8     76913        0.0
logons current                               7         7     76913        0.0
messages received                           26        26     76913        0.0
messages sent                               26        26     76913        0.0
native hash arithmetic execute               0         0     76913        0.0
native hash arithmetic fail                  0         0     76913        0.0
next scns gotten without going               0         0     76913        0.0
no buffer to keep pinned count               1         1     76913        0.0
no work - consistent read gets          13,076     13076     76913        0.2
opened cursors cumulative                  303       303     76913        0.0
opened cursors current                       1         1     76913        0.0
opens of replaced files                      0         0     76913        0.0
opens requiring cache replacem               0         0     76913        0.0
parse count (hard)                         137       137     76913        0.0
parse count (total)                        398       398     76913        0.0
parse time cpu                               0         0     76913        0.0
parse time elapsed                           0         0     76913        0.0
physical reads                           2,434      2434     76913        0.0
physical reads direct                        0         0     76913        0.0
physical writes                              4         4     76913        0.0
physical writes direct                       0         0     76913        0.0
physical writes non checkpoint               4         4     76913        0.0
pinned buffers inspected                     1         1     76913        0.0
prefetched blocks                        1,180      1180     76913        0.0
prefetched blocks aged out bef               0         0     76913        0.0
process last non-idle time                   0         0     76913        0.0
queries parallelized                         0         0     76913        0.0
recovery array read time                     0         0     76913        0.0
recovery array reads                         0         0     76913        0.0
recovery blocks read                         0         0     76913        0.0
recursive calls                          8,593      8593     76913        0.1
recursive cpu usage                          0         0     76913        0.0
redo blocks written                         23        23     76913        0.0
redo buffer allocation retries               0         0     76913        0.0
redo entries                                34        34     76913        0.0
redo log space requests                      0         0     76913        0.0
redo log space wait time                     0         0     76913        0.0
redo log switch interrupts                   0         0     76913        0.0
redo ordering marks                          0         0     76913        0.0
redo size                                7,468      7468     76913        0.1
redo synch time                              0         0     76913        0.0
redo synch writes                            2         2     76913        0.0
redo wastage                             3,940      3940     76913        0.1
redo write time                              0         0     76913        0.0
redo writer latching time                    0         0     76913        0.0
redo writes                                 14        14     76913        0.0
remote instance undo block wri               0         0     76913        0.0
remote instance undo header wr               0         0     76913        0.0
rollback changes - undo record               0         0     76913        0.0
rollbacks only - consistent re               0         0     76913        0.0
rows fetched via callback                1,245      1245     76913        0.0
serializable aborts                          0         0     76913        0.0
session connect time                         0         0     76913        0.0
session cursor cache count                   0         0     76913        0.0
session cursor cache hits                    0         0     76913        0.0
session logical reads                   27,837     27837     76913        0.4
session pga memory                   1,559,588   1559588     76913       20.3
session pga memory max               1,559,588   1559588     76913       20.3
session stored procedure space               0         0     76913        0.0
session uga memory                     187,976    187976     76913        2.4
session uga memory max                 247,132    247132     76913        3.2
sorts (disk)                                 0         0     76913        0.0
sorts (memory)                             105       105     76913        0.0
sorts (rows)                             1,418      1418     76913        0.0
summed dirty queue length                    0         0     76913        0.0
switch current to new buffer                 0         0     76913        0.0
table fetch by rowid                    13,498     13498     76913        0.2
table fetch continued row                  109       109     76913        0.0
table scan blocks gotten                 2,163      2163     76913        0.0
table scan rows gotten                  40,093     40093     76913        0.5
table scans (cache partitions)               0         0     76913        0.0
table scans (direct read)                    0         0     76913        0.0
table scans (long tables)                   35        35     76913        0.0
table scans (rowid ranges)                   0         0     76913        0.0
table scans (short tables)                  31        31     76913        0.0
total file opens                            49        49     76913        0.0
transaction lock background ge               0         0     76913        0.0
transaction lock background ge               0         0     76913        0.0
transaction lock foreground re               0         0     76913        0.0
transaction lock foreground wa               0         0     76913        0.0
transaction rollbacks                        0         0     76913        0.0
transaction tables consistent                0         0     76913        0.0
transaction tables consistent                0         0     76913        0.0
user calls                                 151       151     76913        0.0
user commits                                 0         0     76913        0.0
user rollbacks                               0         0     76913        0.0
write clones created in backgr               0         0     76913        0.0
write clones created in foregr               0         0     76913        0.0