Transcript Oracle Clustered File System (OCFS)

Optimizer Statistics
Julian Dyke
Independent Consultant
Web Version
1
© 2010 Julian Dyke
juliandyke.com
Agenda










2
© 2010 Julian Dyke
Statistics Strategies
ANALYZE
DBMS_STATS
ANALYZE versus DBMS_STATS
System Statistics
Automatic Statistics Collection
Statistics History
Manual Statistics
Partition Statistics
Oracle 11g Enhancements
juliandyke.com
Statistics
Strategies
3
© 2010 Julian Dyke
juliandyke.com
Statistics Strategies
Introduction
4

Only gather statistics when object has changed significantly

Consider generating manual statistics for:
 Very large objects
 Temporary tables
 Highly volatile tables

Try to collect statistics during a specified window
 Do not execute SQL statements during statistics collection
 Use DBMS_STATS parallelism
 Review sample sizes for very large objects

In complex databases do not rely on Auto job
 Unpredictable collection behaviour / duration
 Unpredictable execution plan changes

See Metalink Note 44961.1 - Statistics Gathering; Frequency and Strategy
© 2010 Julian Dyke
juliandyke.com
ANALYZE
5
© 2010 Julian Dyke
juliandyke.com
ANALYZE
Introduction

SQL statement

Introduced in Oracle 7.0
Collects object statistics for Cost Based Optimizer

6

Initially collected statistics on tables and indexes
 Subsequently on histograms

Advantages
 Online analyze - Oracle 9.0 and above
 Optionally validates structure of tables, indexes and clusters
 Optionally detects chained / migrated rows

Disadvantages
 Serial statistics collection only
 Limited partition-awareness
© 2010 Julian Dyke
juliandyke.com
ANALYZE
VALIDATE STRUCTURE

Syntax is
ANALYZE INDEX <index_name> VALIDATE STRUCTURE;
7

Populates session-level dynamic performance views
 INDEX_STATS
 INDEX_HISTOGRAMS

Reports index statistics including:
 height
 number of leaf blocks
 number of branch blocks
 number of distinct keys

Reports space released by deletions
 Use to determine when to coalesce or rebuild index

Recommends index compression columns
 Use to determine which indexes should be compressed
© 2010 Julian Dyke
juliandyke.com
ANALYZE
VALIDATE STRUCTURE ONLINE


Introduced in Oracle 9.0.1
Syntax is
ANALYZE INDEX <index_name> VALIDATE STRUCTURE ONLINE;

8
ONLINE option
 only checks structure
 does not require TM (DML) lock
 does not populate INDEX_STATS or INDEX_HISTOGRAM
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
9
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Introduction
10

PL/SQL supported package

Introduced in Oracle 8.1.5
 In Oracle 11.1 there are 107 subroutines

Defined in $ORACLE_HOME/rdbms/admin/dbmsstat.sql

Advantages
 Parallel statistics collection
 Partition-awareness

Disadvantages
 Cannot perform structure validation
 Cannot detect chaining / migration
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Gathering Statistics
11

Statistics can be gathered using the following subroutines:
 GATHER_DATABASE_STATS
 GATHER_SCHEMA_STATS
 GATHER_TABLE_STATS
 GATHER_INDEX_STATS

These routines allow you to specify:
 Sample size
 Granularity (partitioned tables only)
 Histogram policy
 Cursor invalidation policy
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Deleting Statistics
12

Statistics can be deleted using the following subroutines:
 DELETE_DATABASE_STATS
 DELETE_SCHEMA_STATS
 DELETE_TABLE_STATS
 DELETE_INDEX_STATS
 DELETE_COLUMN_STATS

DELETE_TABLE_STATS includes
 CASCADE_PART - optionally delete partition statistics
 CASCADE_COLUMNS - optionally delete column statistics
 CASCADE_INDEXES - optionally delete index statistics

DELETE_INDEX_STATS, DELETE_COLUMN_STATS include:
 CASCADE_PART - optionally delete partition statistics
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Statistics Tables

Used to
 export / import statistics
 store object statistics values PRIOR to GATHER operations

To create a statistics table use CREATE_STATS_TABLE. For example:
dbms_stats.create_stats_table
(
ownname => 'SYSTEM',
tabname => 'STATS1',
tblspace=> 'SYSAUX'
);

13
To drop a statistics table use DROP_STATS_TABLE
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Exporting and Importing Statistics
14

Statistics can be exported to a statistics table using:
 EXPORT_COLUMN_STATS
 EXPORT_DATABASE_STATS
 EXPORT_INDEX_STATS
 EXPORT_SCHEMA_STATS
 EXPORT_TABLE_STATS

Statistics can be imported from a statistics table using:
 IMPORT_COLUMN_STATS
 IMPORT_DATABASE_STATS
 IMPORT_INDEX_STATS
 IMPORT_SCHEMA_STATS
 IMPORT_TABLE_STATS

To transfer the statistics to another database export and import the statistics
table
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
OPTIONS parameter
15

Specifies how to determine which objects to gather statistics for:
 GATHER - gather statistics for all objects
 GATHER AUTO - gather statistics for all objects needing new statistics
 GATHER STALE - gather statistics for all objects having stale statistics
 GATHER EMPTY- gather statistics for all objects having no statistics
 LIST AUTO - list objects needing new statistics
 LIST STALE - list objects having stale statistics
 LIST EMPTY - list objects having stale statistics

The OBJLIST parameter must be specified as an OUT parameter for the LIST
options
 GATHER STALE and GATHER EMPTY can also return a list of objects if
this parameter is specified
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
OPTIONS parameter
16

If GATHER STALE is specified statistics will be gathered for objects where at
least 10% of rows have changed
 Total of INSERTS, UPDATES and DELETES since last statistics collection

In Oracle 10g percentage is fixed at 10%
 In Oracle 11g percentage is user-configurable
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Stale Statistics

In Oracle 10.1 and above table modification statistics:

are enabled if STATISTICS_LEVEL is TYPICAL or ALL
 are disabled if STATISTICS_LEVEL is BASIC

are collected in the SGA

are automatically flushed to SYS.MON_MODS$
 by SMON background process
 every 15 minutes to SYS.MON_MODS$
 if a significant number of rows has changed (possibly 10%)


17
can be manually flushed to SYS.MON_MOD$ using:
 DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
are reported in DBA_TAB_MODIFICATIONS
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Stale Statistics

18
SYS.MON_MODS$ has the following columns:
© 2010 Julian Dyke
Column Name
Data Type
OBJ#
NUMBER
INSERTS
NUMBER
UPDATES
NUMBER
DELETES
NUMBER
TIMESTAMP
DATE
FLAGS
NUMBER
DROP_SEGMENTS
NUMBER
juliandyke.com
DBMS_STATS
Stale Statistics

In Oracle 8i, table modification monitoring can only be enabled manually
using:
ALTER TABLE <table_name> MONITORING;
ALTER TABLE <table_name> NOMONITORING;

In Oracle 9i, table modification monitoring can also be enabled using
DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING ('<owner>',TRUE);
DBMS_STATS.ALTER_DATABASE_TAB_MONITORING (TRUE);
19

Reported in DBA_TABLES.MONITORING

In Oracle 10.1 and above MONITORING clause is deprecated and ignored
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
ESTIMATE_PERCENT Parameter
20

Specified using ESTIMATE_PERCENT parameter in
 GATHER_DATABASE_STATS
 GATHER_DICTIONARY_STATS
 GATHER_INDEX_STATS
 GATHER_SCHEMA_STATS
 GATHER_TABLE_STATS

Valid range is 0.000001 to 100

NULL means COMPUTE

Default (10.2) is DBMS_STATS.AUTO_SAMPLE_SIZE
 Oracle determines sample size automatically

Default can be changed using SET_PARAM procedure
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
GRANULARITY Parameter
21

Only relevant for partitioned objects
 ALL - gather global, partition and subpartition statistics
 AUTO - determine granularity based on partitioning type.
 DEFAULT - gather global and partition-level statistics. Now obsolete. Use
GLOBAL AND PARTITION instead
 GLOBAL - gather global statistics
 GLOBAL AND PARTITION - gather global and partition-level statistics
 PARTITION - gather partition-level statistics
 SUBPARTITION - gather subpartition-level statistics

In Oracle 10.2 default is AUTO (not DEFAULT)
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
NO_INVALIDATE parameter
22

In earlier versions ANALYZE and DBMS_STATS gather procedures always
invalidated cursors referencing affected object

In Oracle 10.1 and above, invalidation can be controlled using
NO_INVALIDATE parameter
 TRUE - do not invalidate dependent cursors
 FALSE - invalidate dependent cursors
 DBMS_STATS.AUTO_INVALIDATE (default) - Oracle determines whether
to invalidate dependent cursors or not.

In Oracle 10.2 the default value can be changed using SET_PARAM procedure

Only relevant for DBMS_STATS gather procedures
 ANALYZE always invalidates analyzed objects
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Data Dictionary Statistics

Oracle recommends that data dictionary statistics are collected in Oracle
9.0.1 and above

Subroutines are:
 GATHER_DICTIONARY_STATS
 DELETE_DICTIONARY_STATS
 EXPORT_DICTONARY_STATS
 RESTORE_DICTIONARY_STATS

In Oracle 9i data dictionary statistics should only be collected using:
dbms_stats.gather_schema_stats (ownname => 'SYS',cascade=> TRUE);

In Oracle 9i data dictionary statistics can be deleted using:
dbms_stats.delete_schema_stats (ownname => 'SYS');
23

See Metalink Note 245051.1

Note ANALYZE should not be used with tables owned by SYS.
See Metalink Note 35272.1
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Fixed Object Statistics

In Oracle 10.1 and above Oracle recommends that statistics are collected on
fixed objects
 These include X$ and V$ views

Subroutines include:
 GATHER_FIXED_OBJECTS_STATS
 DELETE_FIXED_OBJECTS_STATS
 EXPORT_FIXED_OBJECT_STATS
 RESTORE_FIXED_OBJECTS_STATS

For example:
dbms_stats.gather_fixed_objects_stats;
24

Only gather fixed object statistics after the database has been running a
representative workload

See Metalink Note 272479.1
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
METHOD_OPT Parameter

Specifies histogram collection policy

Can be specified for:
 GATHER_DATABASE_STATS,
 GATHER_DICTIONARY_STATS,
 GATHER_SCHEMA_STATS
 GATHER_TABLE_STATS

25
Default is FOR ALL COLUMNS SIZE AUTO
 Uses data from COL_USAGE$ table
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
METHOD_OPT Parameter

Syntax is:
FOR ALL [ INDEXED | HIDDEN ] COLUMNS [ size_clause ]
FOR COLUMNS [ [ size_clause ] column|attribute [size_clause]
[, column|attribute [size_clause]..]

size_clause is:
SIZE { integer | REPEAT | AUTO | SKEWONLY }

26
where:
 integer - number of histogram buckets (1..254)
 REPEAT - only collect histograms for columns that already have
histograms
 AUTO - determine which columns need histograms automatically
 SKEWONLY - determine which columns need histograms based on data
distribution
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Column Usage Monitoring


27
Column usage is recorded in SYS.COL_USAGE$
Column Name
Data Type
OBJ#
NUMBER
INTCOL#
NUMBER
EQUALITY_PREDS
NUMBER
EQUIJOIN_PREDS
NUMBER
NONEQUIJOIN_PREDS
NUMBER
RANGE_PREDS
NUMBER
LIKE_PREDS
NUMBER
NULL_PREDS
NUMBER
TIMESTAMP
DATE
COL_USAGE$ is
 Updated when statements are parsed
 Reset when statistics are gathered for table
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Histograms Summary
28

Do not collect histograms if not necessary
 Expensive to collect

Check accuracy of histograms
 Watch for columns with high proportion of NULL values
 Watch for highly skewed columns

Height-balanced histograms
 Limited use if column is not highly skewed

Object oriented designs rarely work with histograms

Using histograms introduces bind variable peeking issues
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Locking Statistics

Statistics can be locked for schemas, tables, and partitions
 LOCK_SCHEMA_STATS / UNLOCK_SCHEMA_STATS
 LOCK_TABLE_STATS / UNLOCK_TABLE_STATS
 LOCK_PARTITION_STATS / UNLOCK_PARTITION_STATS
dbms_stats.lock_table_stats
(
ownname => 'USER1',
tabname => 'T1',
stattype => 'ALL'
);
29

Possible values for STATTYPE are NULL, DATA, CACHE and ALL

By default GATHER procedures will not overwrite locked statistics
 Specify FORCE => TRUE to overwrite

Statistics locking reported in STATTYPE_LOCKED column in
 DBA_TAB_STATISTICS
 DBA_IND_STATISTICS
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Default Parameters



30
In Oracle 10.1 and above default parameters can be specified
System-wide defaults can be set for
Column Name
Factory Default
CASCADE
DBMS_STATS.AUTO_CASCADE
DEGREE
NULL
ESTIMATE_PERCENT
DBMS_STATS.AUTO_SAMPLE_SIZE
METHOD_OPT
FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE
DBMS_STATS.AUTO_INVALIDATE
GRANULARITY
AUTO
AUTOSTATS_TARGET
AUTO
AUTOSTATS_TARGET can be
 ALL
collect statistics for all objects in the database
 ORACLE
collect statistics for all Oracle-owned objects
 AUTO
determine which objects need new statistics
© 2010 Julian Dyke
juliandyke.com
DBMS_STATS
Default Parameters

To set default parameters use SET_PARAM:
dbms_stats.set_param (pname => 'CASCADE',pval => TRUE);


Take care with NULL values
For example:
dbms_stats.set_param ('ESTIMATE_PERCENT','NULL');

Sets the default to COMPUTE statistics
dbms_stats.set_param ('ESTIMATE_PERCENT',NULL);


Restores factory default (DBMS_STATS.AUTO_SAMPLE_SIZE)
To check default parameters use GET_PARAM e.g.:
SELECT dbms_stats.get_param (pname => 'CASCADE')
FROM dual;

To restore all factory default parameters use:
dbms_stats.reset_param_defaults;
31
© 2010 Julian Dyke
juliandyke.com
ANALYZE
versus
DBMS_STATS
32
© 2010 Julian Dyke
juliandyke.com
ANALYZE versus DBMS_STATS
Differences



33
ANALYZE updates the following columns in DBA_TAB_STATISTICS
 EMPTY_BLOCKS
 AVG_SPACE
DBMS_STATS sets these columns to 0
Tables
 AVG_ROW_LEN differs - e.g.:
ANALYZE
43
DBMS_STATS
39

Indexes
 No obvious differences between ANALYZE and DBMS_STATS

Columns
 AVG_COL_LEN differs - e.g.:
© 2010 Julian Dyke
ANALYZE
4
DBMS_STATS
5
juliandyke.com
ANALYZE versus DBMS_STATS
Differences
34

ANALYZE <table> gathers statistics for related indexes
 Prior to Oracle 10.1 DBMS_STATS requires CASCADE => TRUE

For empty indexes
 ANALYZE sets BLEVEL to 0
 DBMS_STATS sets BLEVEL > 0
© 2010 Julian Dyke
juliandyke.com
ANALYZE versus DBMS_STATS
Summary
35

Use ANALYZE to
 Collect index statistics without updating data dictionary
 Identify candidates for index coalesce/rebuild
 Identify candidates for index compression
 Identify tables containing excessive chained rows

Use DBMS_STATS
 Gather statistics on tables / indexes

Do not mix ANALYZE / DBMS_STATS
© 2010 Julian Dyke
juliandyke.com
System
Statistics
36
© 2010 Julian Dyke
juliandyke.com
System Statistics
Introduction
37

System statistics were introduced in Oracle 9.0.1

Subroutines are:
 GATHER_SYSTEM_STATS
 DELETE_SYSTEM_STATS
 GET_SYSTEM_STATS
 SET_SYSTEM_STATS
 EXPORT_SYSTEM_STATS
 IMPORT_SYSTEM_STATS
 RESTORE_SYSTEM_STATS

System statistics are stored in SYS.AUX_STATS$
© 2010 Julian Dyke
juliandyke.com
System Statistics
Workload versus No Workload Statistics
38

Oracle 10.1 and above supports:
 noworkload statistics - default values supplied during database creation
 workload statistics - collected from representative workload

Workload statistics include:
 CPUSPEED - CPU speed
 SREADTIM - Single block read time in milliseconds
 MREADTIM - Multi block read time in milliseconds
 MBRC - Multi block read count
 MAXTHR - Maximum I/O system throughput (parallel execution only)
 SLAVETHR - Average slave I/O throughput (parallel execution only)

No workload statistics include:
 CPUSPEEDNW - CPU speed
 IOSEEKTIM - IO Seek Time
 IOTFRSPEED - IO Transfer Speed
© 2010 Julian Dyke
juliandyke.com
System Statistics
Example

Create a statistics table using:
dbms_stats.create_stat_table ('SYS','OLTP_STATS');

Gather system statistics for a typical period using:
dbms_stats.gather_system_stats
(
gathering_mode => 'INTERVAL',
interval => 60,
stattab => OLTP_STATS',
statid => 'OLTP'
);

-- 60 seconds
Import system statistics into AUX_STATS$ using:
dbms_stats.import_system_stats
(
stattab => OLTP_STATS',
statid => 'OLTP',
statown => 'SYS'
);
39
© 2010 Julian Dyke
juliandyke.com
System Statistics
Summary
40

Enable system statistics for single instance databases
 Usually improve execution plans

Consider carefully before enabling system statistics in RAC databases
 System statistics are database-specific
 Watch for asymmetric nodes
 Hardware differences
 Service configuration

System statistics are:
 Difficult to monitor
 Very difficult to update / remove

If exporting object statistics to another system for testing:
 Remember to export system statistics
© 2010 Julian Dyke
juliandyke.com
Automatic
Statistics
Collection
41
© 2010 Julian Dyke
juliandyke.com
Automatic Statistics Collection
Introduction

Oracle 10.1 and above

Statistics collected during Maintenance Window
 Monday - Friday
22:00 to 06:00
 Saturday / Sunday
All day

Note that weekend window effectively starts at 06:00 on Saturday morning

Scheduler job
 GATHER_STATS_JOB
Scheduler job class
 AUTO_TASKS_JOB_CLASS
Scheduler windows
 WEEKNIGHT_WINDOW
 WEEKEND_WINDOW
Scheduler window group
 MAINTENANCE_WINDOW_GROUP



42
© 2010 Julian Dyke
juliandyke.com
Statistics
History
43
© 2010 Julian Dyke
juliandyke.com
Statistics History
Introduction

In Oracle 10.1 and above, existing statistics are stored in the data dictionary
when new statistics are collected

Statistics can be restored using:
 RESTORE_DATABASE_STATS
 RESTORE_DICTIONARY_STATS
 RESTORE_FIXED_OBJECTS_STATS
 RESTORE_SCHEMA_STATS
 RESTORE_SYSTEM_STATS
 RESTORE_TABLE_STATS

Statistics history for tables only is reported in DBA_TAB_STATS_HISTORY
SELECT stats_update_time
FROM dba_tab_stats_history
WHERE owner = 'USER1'
AND table_name = 'T1';
12-FEB-09 04.36.32.997000 PM +00:00
44
© 2010 Julian Dyke
juliandyke.com
Statistics History
Optimizer Statistics Operations

Statistics gathering operations are reported in DBA_OPTSTAT_OPERATIONS
SELECT operation, target, start_time, end_time
FROM dba_optstat_operations
ORDER BY start_time;
gather_database_stats(auto) 07-FEB09 06.00.03 07-FEB-09 06.01.52
gather_database_stats(auto) 09-FEB09 10.00.03 09-FEB-09 10.03.37
gather_database_stats(auto) 10-FEB09 10.00.03 10-FEB-09 10.02.01
45

DBA_OPTSTAT_OPERATIONS includes:
 gather_database_stats(auto)
 gather_schema_stats

DBA_OPTSTAT_OPERATIONS does not include:
 gather_table_stats
 gather_index_stats
© 2010 Julian Dyke
juliandyke.com
Statistics History
Data Dictionary Tables
46

Historic statistics are stored in tables created by
 $ORACLE_HOME/rdbms/admin/catost.sql

Tables created are:
 WRI$_OPTSTAT_TAB_HISTORY
 WRI$_OPTSTAT_IND_HISTORY
 WRI$_OPTSTAT_HISTHEAD_HISTORY
 WRI$_OPTSTAT_HISTGRM_HISTORY
 WRI$_OPTSTAT_AUX_HISTORY
 WRI$_OPTSTAT_OPR
 OPTSTAT_HIST_CONTROL$
© 2010 Julian Dyke
juliandyke.com
Statistics History
Retention Period

To check statistics history retention period use:
SELECT dbms_stats.get_stats_history_retention
FROM dual;

Default is 31 days

To check earliest historic statistics use:
SELECT dbms_stats.get_stats_history_availability
FROM dual;
12-JAN-09 11.17.50.176000000 PM +00:00

To set statistics history retention period to 90 days:
dbms_stats.alter_stats_history_retention
(
retention => 90
);
47
© 2010 Julian Dyke
juliandyke.com
Statistics History
Summary
48

Statistics history rows contain previous values for optimizer statistics
 Statistics history does not include current value
 SAVTIME column is time row was written
 ANALYZETIME column is time statistics were analyzed
 SAVTIME != ANALYZETIME

ANALYZE does not update statistics history

[UN]LOCK_TABLE_STATS do update statistics history
© 2010 Julian Dyke
juliandyke.com
Manual
Statistics
49
© 2010 Julian Dyke
juliandyke.com
Setting Table Statistics
Introduction
50

Table statistics can be set manually using SET_TABLE_STATS

Values can be specified for:
 NUMROWS - number of rows
 NUMBLKS - number of blocks
 AVGRLEN - average row length
© 2010 Julian Dyke
juliandyke.com
Setting Table Statistics
Example (1 of 4)
DECLARE
l_numrows NUMBER;
l_numblks NUMBER;
l_avgrlen NUMBER;
l_flags NUMBER;
l_total_blocks NUMBER;
l_total_bytes NUMBER;
l_unused_blocks NUMBER;
l_unused_bytes NUMBER;
l_last_used_extent_file_id NUMBER;
l_last_used_extent_block_id NUMBER;
l_last_used_block NUMBER;
51
© 2010 Julian Dyke
juliandyke.com
Setting Table Statistics
Example (2 of 4)
BEGIN
dbms_stats.get_table_stats
(
ownname => 'GP',
tabname => 'CAR',
numrows => l_numrows,
numblks => l_numblks,
avgrlen => l_avgrlen,
);
l_numrows := 0;
l_numblks := 0;
SELECT COUNT(*) INTO l_numrows FROM gp.car;
52
© 2010 Julian Dyke
juliandyke.com
Setting Table Statistics
Example (3 of 4)
dbms_space.unused_space
(
segment_owner => 'GP',
segment_name => 'CAR',
segment_type => 'TABLE',
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
last_used_extent_file_id => l_last_used_extent_file_id,
last_used_extent_block_id => l_last_used_extent_block_id,
last_used_block => l_last_used_block
);
l_numblks := l_total_blocks;
53
© 2010 Julian Dyke
juliandyke.com
Setting Table Statistics
Example (4 of 4)
dbms_stats.set_table_stats
(
ownname => 'GP',
tabname => 'CAR',
numrows => l_numrows,
numblks => l_numblks,
avgrlen => l_avgrlen
);
END;
54
© 2010 Julian Dyke
juliandyke.com
Setting Index Statistics
Introduction
55

Index statistics can be set manually using SET_INDEX_STATS

Values can be specified for:
 NUMROWS - number of rows
 NUMBLKS - number of blocks
 NUMDIST - number of distinct values
 AVGLBLK - average leaf blocks per key
 AVGDBLK - average data blocks per key
 CLSTFCT - clustering factor
 INDLEVEL - index level (height)

Use ANALYZE INDEX VALIDATE STRUCTURE to determine values
© 2010 Julian Dyke
juliandyke.com
Setting Column Statistics
Introduction
56

Column statistics can be set manually using SET_COLUMN_STATS

Values can be specified for:
 DISTCNT - number of distinct values
 DENSITY - 1 / (number of distinct values)
 NULLCNT - number of NULL values
 Low and high values
 Histograms

Note that DISTCNT usage differs for columns with histograms
© 2010 Julian Dyke
juliandyke.com
Setting Column Statistics
Data Types


Requires additional data types declared in DBMS_STATS package
See $ORACLE_HOME/rdbms/admin/dbmsstat.sql
TYPE numarray IS VARRAY(256) OF NUMBER;
TYPE chararray IS VARRAY(256) OF VARCHAR2(4000);
TYPE statrec IS RECORD
(
EPC
NUMBER,
MINVAL
RAW(2000),
MAXVAL
RAW(2000),
BKVALS
NUMARRAY,
NOVALS
NUMARRAY,
CHVALS
CHARARRAY,
EAVS
NUMBER
)
57
© 2010 Julian Dyke
juliandyke.com
Setting Column Statistics
Example (1 of 3)
PROCEDURE copy_col_stats
(p_owner VARCHAR2,p_table_name VARCHAR2) IS
l_row DBA_TAB_COLUMNS%ROWTYPE;
l_statrec dbms_stats.statrec;
l_numvals dbms_stats.numarray := dbms_stats.numarray();
l_charvals dbms_stats.chararray := dbms_stats.chararray();
l_datevals dbms_stats.datearray := dbms_stats.datearray();
CURSOR c1 RETURN DBA_TAB_COLUMNS%ROWTYPE IS
SELECT *
FROM dba_tab_columns
WHERE owner = p_owner
AND table_name = p_table_name;
BEGIN
l_numvals.extend(2);
l_charvals.extend(2);
l_datevals.extend(2);
58
OPEN c1;
LOOP
FETCH c1 INTO l_row;
EXIT WHEN c1%NOTFOUND;
© 2010 Julian Dyke
juliandyke.com
Setting Column Statistics
Example (2 of 3)
IF l_row.num_buckets IS NOT NULL THEN
l_statrec.epc := 2;
l_statrec.bkvals := NULL;
l_numvals(1) := NULL;
l_numvals(2) := NULL;
l_charvals(1) := NULL;
l_charvals(2) := NULL;
l_datevals(1) := NULL;
l_datevals(2) := NULL;
IF l_row.column_name = 'COL1' THEN
l_numvals(1) := get_min_col1 (p_owner,p_table_name);
l_numvals(2) := get_max_col2 (p_owner,p_table_name);
DBMS_STATS.PREPARE_COLUMN_VALUES (l_statrec,l_numvals);
ELSIF l_row.column_name = 'COL2' THEN
l_datevals(1) := TO_DATE (v_target_date,'YYYYMMDD');
l_datevals(2) := TO_DATE (v_target_date,'YYYYMMDD');
DBMS_STATS.PREPARE_COLUMN_VALUES (l_statrec,l_datevals);
ELSIF l_row.column_name = 'COL3' THEN
l_charvals(1) := get_min_col3 (p_owner,p_table_name);
l_charvals(2) := get_max_col3 (p_owner,p_table_name);
DBMS_STATS.PREPARE_COLUMN_VALUES (l_statrec,l_charvals);
END IF;
59
© 2010 Julian Dyke
juliandyke.com
Setting Column Statistics
Example (3 of 3)

Setting minimum and maximum values (without histograms)
DBMS_STATS.SET_COLUMN_STATS
(
ownname => p_owner,
tabname => p_table_name,
colname => l_row.column_name,
distcnt => l_row.num_distinct,
density => l_row.density,
nullcnt => l_row.num_nulls,
srec => l_statrec,
avgclen => l_row.avg_col_len
);
END IF;
END LOOP;
CLOSE c1;
END;
60
© 2010 Julian Dyke
juliandyke.com
Setting Column Statistics - Histograms
Example (1 of 2)
DECLARE
l_statrec dbms_stats.statrec;
l_charvals dbms_stats.chararray := dbms_stats.chararray ();
l_bkvals dbms_stats.numarray := dbms_stats.numarray ();
BEGIN
l_charvals.extend (11);
l_bkvals.extend (11);
l_charvals(1) := 'MSCH';
l_charvals(2) := 'APRO';
l_charvals(3) := 'ASEN';
l_charvals(4) := 'NMAN';
l_charvals(5) := 'JSTE';
l_charvals(6) := 'NLAU';
l_charvals(7) := 'JCLA';
l_charvals(8) := 'NPIQ';
l_charvals(9) := 'FALO';
l_charvals(10) := 'DHIL';
l_charvals(11) := 'MHAK';
61
© 2010 Julian Dyke
l_bkvals(1) := 91;
l_bkvals(2) := 51;
l_bkvals(3) := 41;
l_bkvals(4) := 31;
l_bkvals(5) := 27;
l_bkvals(6) := 25;
l_bkvals(7) := 25;
l_bkvals(8) := 23;
l_bkvals(9) := 22;
l_bkvals(10) := 22;
l_bkvals(11) := 20;
juliandyke.com
Setting Column Statistics - Histograms
Example (2 of 2)
l_statrec.epc := 11;
l_statrec.bkvals := l_bkvals;
l_statrec.eavs := 0;
DBMS_STATS.PREPARE_COLUMN_VALUES (l_statrec,l_charvals);
DBMS_STATS.SET_COLUMN_STATS
(
ownname => 'GP',
tabname => 'CAR',
colname => 'DRIVER_KEY',
distcnt => 11,
density => 0.00210084, -- 1 / 476 rows
nullcnt => 0,
srec => l_statrec,
avgclen => 4
);
END;
/
62
© 2010 Julian Dyke
juliandyke.com
Partition
Statistics
63
© 2010 Julian Dyke
juliandyke.com
Partition Statistics
Introduction




64
Partition statistics are reported by:
 DBA_TABLES
 DBA_TAB_PARTITIONS
 DBA_TAB_SUBPARTITIONS
Indexes
 DBA_INDEXES
 DBA_IND_PARTITIONS
 DBA_IND_SUBPARTITIONS
Columns
 DBA_TAB_COL_STATISTICS
 DBA_PART_COL_STATISTICS
 DBA_SUBPART_COL_STATISTICS
Histograms
 DBA_TAB_HISTOGRAMS
 DBA_PART_HISTOGRAMS
 DBA_SUBPART_HISTOGRAMS
© 2010 Julian Dyke
DBA_TAB_STATISTICS
(Oracle 10.1 and above)
DBA_IND_STATISTICS
(Oracle 10.1 and above)
juliandyke.com
Partition Statistics
Example (1 of 12)

Create a range partitioned table containing data for last four seasons
CREATE TABLE car3
PARTITION BY RANGE (season_key)
(
PARTITION p2005 VALUES LESS THAN
PARTITION p2006 VALUES LESS THAN
PARTITION p2007 VALUES LESS THAN
PARTITION p2008 VALUES LESS THAN
)
AS SELECT * FROM car
WHERE season_key >= '2005';
('2006'),
('2007'),
('2008'),
('2009')
CREATE INDEX car3_i1 ON car3(season_key,race_key,position) LOCAL;
dbms_stats.gather_table_stats
(
ownname => 'GP',
tabname => 'CAR3',
estimate_percent => NULL,
cascade => TRUE
);
65
© 2010 Julian Dyke
juliandyke.com
Partition Statistics
Example (2 of 12)

Table Statistics
SELECT num_rows,blocks,avg_row_len
FROM dba_tables
WHERE owner = 'GP' AND table_name = 'CAR3';
NUM_ROWS
BLOCKS
AVG_ROW_LEN
1518
24
37
SELECT partition_name,num_rows,blocks,avg_row_len
FROM dba_tab_partitions
WHERE table_owner = 'GP' AND table_name = 'CAR3';
66
PARTITION_NAME
NUM_ROWS
BLOCKS
AVG_ROW_LEN
P2005
380
6
36
P2006
396
6
38
P2007
374
6
37
P2008
368
6
37
© 2010 Julian Dyke
juliandyke.com
Partition Statistics
Example (3 of 12)

Index Statistics
SELECT blevel, leaf_blocks, distinct_keys, clustering_factor, num_rows
FROM dba_indexes
WHERE owner = 'GP' AND index_name = 'CAR3_I1';
BLEVEL
LEAF_BLOCKS
DIST KEYS
CLUFAC
NUM_ROWS
1
8
1518
15
1518
SELECT partition_name,
blevel, leaf_blocks, distinct_keys, clustering_factor, num_rows
FROM dba_ind_partitions
WHERE index_owner = 'GP' AND index_name = 'CAR3_I1';
67
PARTITION_NAME
BLEVEL
LEAF_BLOCKS
DIST KEYS
CLUFAC
NUM_ROWS
P2005
1
2
380
3
380
P2006
1
2
396
3
396
P2007
1
2
374
5
374
P2008
1
2
368
4
368
© 2010 Julian Dyke
juliandyke.com
Partition Statistics
Example (4 of 12)

Add a new partition for 2009 season
ALTER TABLE car3
ADD PARTITION p2009 VALUES LESS THAN ('2010');

Gather statistics again
dbms_stats.gather_table_stats
(
ownname => 'GP',
tabname => 'CAR3',
estimate_percent => NULL,
cascade => TRUE
);
68
© 2010 Julian Dyke
juliandyke.com
Partition Statistics
Example (5 of 12)

Table Statistics
SELECT num_rows,blocks,avg_row_len
FROM dba_tables
WHERE owner = 'GP' AND table_name = 'CAR3';
NUM_ROWS
BLOCKS
AVG_ROW_LEN
1518
24
37
SELECT partition_name,num_rows,blocks,avg_row_len
FROM dba_tab_partitions
WHERE table_owner = 'GP' AND table_name = 'CAR3';
69
PARTITION_NAME
NUM_ROWS
BLOCKS
AVG_ROW_LEN
P2005
380
6
36
P2006
396
6
38
P2007
374
6
37
P2008
368
6
37
P2009
0
0
0
© 2010 Julian Dyke
juliandyke.com
Partition Statistics
Example (6 of 12)

Index Statistics
SELECT blevel, leaf_blocks, distinct_keys, clustering_factor, num_rows
FROM dba_indexes
WHERE owner = 'GP' AND index_name = 'CAR3_I1';
BLEVEL
LEAF_BLOCKS
DIST KEYS
CLUFAC
NUM_ROWS
1
8
1518
15
1518
SELECT partition_name,
blevel, leaf_blocks, distinct_keys, clustering_factor, num_rows
FROM dba_ind_partitions
WHERE index_owner = 'GP' AND index_name = 'CAR3_I1';
70
PARTITION_NAME
BLEVEL
LEAF_BLOCKS
DIST KEYS
CLUFAC
NUM_ROWS
P2005
1
2
380
3
380
P2006
1
2
396
3
396
P2007
1
2
374
5
374
P2008
1
2
368
4
368
P2009
0
0
0
0
0
© 2010 Julian Dyke
juliandyke.com
Partition Statistics
Example (7 of 12)

Copy table statistics from 2008 to 2009 partitions
dbms_stats.copy_table_stats
(
ownname => 'GP',
tabname => 'CAR3',
srcpartname => 'P2008',
dstpartname => 'P2009'
);
71
© 2010 Julian Dyke
juliandyke.com
Partition Statistics
Example (8 of 12)

Table Statistics
SELECT num_rows,blocks,avg_row_len
FROM dba_tables
WHERE owner = 'GP' AND table_name = 'CAR3';
NUM_ROWS
BLOCKS
AVG_ROW_LEN
1518
24
37
SELECT partition_name,num_rows,blocks,avg_row_len
FROM dba_tab_partitions
WHERE table_owner = 'GP' AND table_name = 'CAR3';
72
PARTITION_NAME
NUM_ROWS
BLOCKS
AVG_ROW_LEN
P2005
380
6
36
P2006
396
6
38
P2007
374
6
37
P2008
368
6
37
P2009
368
6
37
© 2010 Julian Dyke
juliandyke.com
Partition Statistics
Example (9 of 12)

Index Statistics
SELECT blevel, leaf_blocks, distinct_keys, clustering_factor, num_rows
FROM dba_indexes
WHERE owner = 'GP' AND index_name = 'CAR3_I1';
BLEVEL
LEAF_BLOCKS
DIST KEYS
CLUFAC
NUM_ROWS
1
8
1518
15
1518
SELECT partition_name,
blevel, leaf_blocks, distinct_keys, clustering_factor, num_rows
FROM dba_ind_partitions
WHERE index_owner = 'GP' AND index_name = 'CAR3_I1';
73
PARTITION_NAME
BLEVEL
LEAF_BLOCKS
DIST KEYS
CLUFAC
NUM_ROWS
P2005
1
2
380
3
380
P2006
1
2
396
3
396
P2007
1
2
374
5
374
P2008
1
2
368
4
368
P2009
1
2
368
4
368
© 2010 Julian Dyke
juliandyke.com
Partition Statistics
Example (10 of 12)

Copy rows from 2008 to 2009. For example:
INSERT INTO car3
SELECT
'2009',
race_key,
driver_key,
team_key,
engine_key,
position,
laps_completed,
classification_key,
notes,
driver_points,
team_points
FROM gp.car3
WHERE season_key = '2008';

74
Statistics are unchanged
© 2010 Julian Dyke
juliandyke.com
Partition Statistics
Example (11 of 12)

Execution plans for statement against 2008 and 2009 partitions:
SELECT SUM(team_points) FROM gp.car3
WHERE season_key = '2008';
0
1
2
3
SELECT STATEMENT
0
SORT AGGREGATE
1
PARTITION RANGE (SINGLE)
2
TABLE ACCESS (FULL) OF 'CAR3'
SELECT SUM(team_points) FROM gp.car3
WHERE season_key = '2009';
0
1
2
3
4

75
0
1
2
3
SELECT STATEMENT
SORT AGGREGATE
PARTITION RANGE (SINGLE)
TABLE ACCESS (BY INDEX ROWID) OF 'CAR3'
INDEX (RANGE SCAN) OF 'CAR3_I1'
Plans are different even though statistics and data are theoretically identical
© 2010 Julian Dyke
juliandyke.com
Partition Statistics
Example (12 of 12)

In Oracle 10.2.0.4 column values are not updated by COPY_TABLE_STATS
SELECT partition_name,low_value,high_value
FROM dba_part_col_statistics
WHERE owner = 'GP'
AND table_name = 'CAR3'
AND column_name = 'SEASON_KEY';
PARTITION_NAME


76
LOW_VALUE
HIGH_VALUE
P2005
0x32303035 (2005)
0x32303035 (2005)
P2006
0x32303036 (2006)
0x32303036 (2006)
P2007
0x32303037 (2007)
0x32303037 (2007)
P2008
0x32303038 (2008)
0x32303038 (2008)
P2009
0x32303038 (2008)
0x32303038 (2008)
Caused by bug 5643297 - only affects 10.2.0.4
Fixed in patch 7381308
 includes fixes for COPY_TABLE_STATS and APPROX_GLOBAL
© 2010 Julian Dyke
juliandyke.com
Partition Statistics
Summary
77

Range-partitioning
 Only collect statistics for partitions undergoing change
 Specify partition names and GRANULARITY parameter to restrict
partitions that are analyzed

Consider setting statistics manually for new partitions
 COPY_STATS has limited functionality
 Watch for changing high values

Use DBMS_STATS NO_INVALIDATE option
 ANALYZE invalidates all cursors referencing table being analyzed
 Can result in hard-parse rates approaching 100%
© 2010 Julian Dyke
juliandyke.com
Oracle 11g
Enhancements
78
© 2010 Julian Dyke
juliandyke.com
Statistics
Preferences
79
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Statistics Preferences
80

In Oracle 10g preferences are:
 set using SET_PARAM
 reported by GET_PARAM
 stored in SYS.OPTSTAT_HIST_CONTROL$

In Oracle 11.1 and above preferences are:
 set using:
 SET_TABLE_PREFS
 SET_SCHEMA_PREFS
 SET_DATABASE_PREFS
 SET_GLOBAL_PREFS
 reported by
 GET_PREFS
 DBA_TAB_STAT_PREFS
 stored in SYS.OPTSTAT_USER_PREFS$

In Oracle 11.1 and above global preferences can be reset using
 RESET_GLOBAL_PREF_DEFAULTS
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Statistics Preferences
81

Allow automatic statistics collection to be customized for individual objects
 For example heavily skewed data distributions

Can be set at table, schema, database and global level

In Oracle 10g preferences can be set for:
 CASCADE
 DEGREE
 ESTIMATE_PERCENT
 METHOD_OPT
 NO_INVALIDATE
 GRANULARITY

In Oracle 11g preferences can also be set for:
 PUBLISH - if TRUE publish statistics; if FALSE store as pending
 STALE_PERCENT - threshold level at which statistics considered stale
 INCREMENTAL - if TRUE collect incremental global partition statistics
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Statistics Preferences
82

Delete preferences using:
 DELETE_TABLE_PREFS
 DELETE_SCHEMA_PREFS
 DELETE_DATABASE_PREFS

Export and import preferences using:
 EXPORT_TABLE_PREFS / IMPORT_TABLE_PREFS
 EXPORT_SCHEMA_PREFS / IMPORT_SCHEMA_PREFS
 EXPORT_DATABASE_PREFS / IMPORT_DATABASE_PREFS
© 2010 Julian Dyke
juliandyke.com
Pending
Statistics
83
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Pending Statistics
84

In Oracle 11.1 and above statistics can be
 Published - current statistics used by optimizer
 Pending - private statistics not yet published

In Oracle 11.1 statistics for a set of objects can be collected as an atomic
transaction
 Published at same time
 Avoids inconsistencies

PUBLISH mode can be set at the following levels
 Global
 Database
 Schema
 Table
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Pending Statistics

To defer publication of all statistics collected by USER1 use:
dbms_stats.set_schema_prefs ('USER1','PUBLISH','FALSE');

To make pending statistics temporarily available to the optimizer use:
ALTER SESSION optimizer_use_pending_statistics = TRUE;

To make pending statistics permanently available to the optimizer use:
dbms_stats.publish_pending_statistics
(
ownname => 'USER1',
tabname => NULL
);
85

Statistics can be published at database, schema or table level

To delete pending statistics use DELETE_PENDING_STATS

To export pending statistics use EXPORT_PENDING_STATS
 There is no corresponding import procedure
© 2010 Julian Dyke
juliandyke.com
Extended
Statistics
86
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Extended Statistics
87

Extended statistics attempt to reflect true selectivity of data

There are two types:
 multi-column statistics
 expression statistics

Extended statistics use the following procedures:
 CREATE_EXTENDED_STATS
 DROP_EXTENDED_STATS
 SHOW_EXTENDED_STATS_NAME
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Multi-Column Statistics

In Oracle 11g the following statistics can be gathered on multiple columns in
a table (column groups):





88
Number of distinct values
Density
Number of nulls
Frequency histograms
Multicolumn statistics
 Appear to work for frequency histograms
 <= 254 combinations of values
 Do not appear to work for height-based histograms
 > 254 combinations of values
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Multi-Column Statistics
SELECT COUNT(*) FROM gp.car
WHERE team_key = 'FER'
AND engine_key = 'FER';
COUNT(*)
1410
Id
Operation
0
SELECT STATEMENT
1
SORT AGGREGATE
2
TABLE ACCESS FULL
Name
CAR
Rows
Bytes
1
13
137
1781
Cost (%CPU)
35
(100)
35
(0)
Time
00:00:01
Incorrect
Cardinality
89
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Multi-Column Statistics
DECLARE
l_extension_name VARCHAR2(30);
BEGIN
l_extension_name := dbms_stats.create_extended_stats
(
ownname => 'GP',
tabname => 'CAR',
extension => '(team_key,engine_key)'
);
END;
BEGIN
dbms_stats.gather_table_stats
(
ownname => 'GP',
tabname => 'CAR',
estimate_percent => NULL,
method_opt => 'FOR ALL COLUMNS SIZE 254 '||
'FOR COLUMNS (TEAM_KEY,ENGINE_KEY) SIZE 254'
);
END;
90
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Multi-Column Statistics
SELECT COUNT(*) FROM gp.car
WHERE team_key = 'FER'
AND engine_key = 'FER';
COUNT(*)
1410
Id
Operation
0
SELECT STATEMENT
1
SORT AGGREGATE
2
TABLE ACCESS FULL
Name
CAR
Rows
Bytes
1
13
1410
18330
Cost (%CPU)
35
(100)
35
(0)
Time
00:00:01
Correct
Cardinality
91
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Multi-Column Statistics
SELECT extension_name,extension
FROM dba_stat_extensions
WHERE owner = 'GP'
AND table_name = 'CAR';
92
Extension Name
Extension
SYS_STUWIHETSRHT#5P210Z$AO1ON9
("TEAM_KEY","ENGINE_KEY")
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Multi-Column Statistics
SELECT c.intcol#,c.col#,c.name
FROM sys.col$ c, sys.obj$ o, sys.user$ u
WHERE c.obj# = o.obj#
AND o.name = 'CAR'
AND o.owner# = u.user#
AND u.name = 'GP';
INTCOL#
93
COL#
NAME
1
1
SEASON_KEY
2
2
RACE_KEY
3
3
DRIVER_KEY
4
4
TEAM_KEY
5
5
ENGINE_KEY
6
6
POSITION
7
7
LAPS_COMPLETED
8
8
CLASSIFICATION_KEY
9
9
NOTES
10
0
SYS_STUWIHETSRHT#5P210Z$AO1ON9
© 2010 Julian Dyke
New Virtual
Column in Table
for Multi-Column
Statistics
juliandyke.com
Oracle 11g Enhancements
Expression Statistics
CREATE OR REPLACE FUNCTION points
(season_key VARCHAR2,race_key NUMBER,position NUMBER)
RETURN NUMBER
DETERMINISTIC IS
l_result NUMBER;
BEGIN
l_result := CASE position
WHEN 1 THEN 10
Note: The real algorithm is MUCH more
WHEN 2 THEN 8
complicated for various reasons
WHEN 3 THEN 6
including:
WHEN 4 THEN 5
• changes to number of points awarded
WHEN 5 THEN 4
WHEN 6 THEN 3
• disqualifications, penalties etc
WHEN 7 THEN 2
• differences between drivers and team
WHEN 8 THEN 1
championship points systems
ELSE 0
END;
RETURN l_result;
END;
94
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Expression Statistics
SELECT COUNT(*) FROM gp.car
WHERE POINTS (season_key,race_key,position) = 10;
COUNT(*)
709
Id
Operation
0
SELECT STATEMENT
1
SORT AGGREGATE
2
TABLE ACCESS FULL
Name
CAR
Rows
Bytes
1
13
177
4248
Cost (%CPU)
38
(100)
38
(0)
Time
00:00:01
Incorrect
Cardinality
95
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Expression Statistics
DECLARE
l_extension_name VARCHAR2(30);
BEGIN
l_extension_name := dbms_stats.create_extended_stats
(
ownname => 'GP',
tabname => 'CAR',
extension => '(points(season_key,race_key,position))'
);
END;
BEGIN
dbms_stats.gather_table_stats
(
ownname => 'GP',
tabname => 'CAR',
estimate_percent => NULL,
method_opt => 'FOR ALL COLUMNS SIZE 254 '||
'FOR COLUMNS (points(season_key,race_key,position)) SIZE 254'
);
END;
96
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Expression Statistics
SELECT COUNT(*) FROM gp.car
WHERE POINTS (season_key,race_key,position) = 10;
COUNT(*)
709
Id
Operation
0
SELECT STATEMENT
1
SORT AGGREGATE
2
TABLE ACCESS FULL
Name
CAR
Rows
Bytes
1
13
709
19143
Cost (%CPU)
38
(100)
38
(0)
Time
00:00:01
Correct
Cardinality
97
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Incremental Statistics
98

For partitioned tables statistics can be collected:
 For sub-partitions
 For partitions
 Globally

Global statistics should reflect underlying partitioned statistics

Prior to Oracle 11.1 calculation of global statistics required full table scan of
each partition in table

For many applications using range partitioning only the most recent partition
is subject to change
 Older partitions contain historical data
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Incremental Statistics

For example:
CREATE TABLE car4
PARTITION BY RANGE (season_key)
(
PARTITION p2006 VALUES LESS THAN
PARTITION p2007 VALUES LESS THAN
PARTITION p2008 VALUES LESS THAN
PARTITION p2009 VALUES LESS THAN
)
AS SELECT * FROM car
WHERE season_key >= '2005';
('2005'),
('2006'),
('2007'),
('2008')
Create table
with
partitions for
four years
data
CREATE INDEX i_car4_1
ON carp(season_key,race_key,position)
LOCAL;
INSERT INTO car4
SELECT * FROM car
WHERE season_key BETWEEN 2006 AND 2009;
99
© 2010 Julian Dyke
Insert data
juliandyke.com
Oracle 11g Enhancements
Incremental Statistics

Gather statistics
DBMS_STATS.GATHER_TABLE_STATS
(
ownname => 'GP',
tabname => 'CAR4'
);

100
Requires full table scan for each partition
© 2010 Julian Dyke
2006
Full Table Scan
2007
Full Table Scan
2008
Full Table Scan
2009
Full Table Scan
juliandyke.com
Oracle 11g Enhancements
Incremental Statistics

Update data in one partition:
UPDATE car4 SET team_points = driver_points
WHERE season_key = '2009';

Gather table statistics again:
DBMS_STATS.GATHER_TABLE_STATS
(
ownname => 'GP',
tabname => 'CAR4'
);

101
Still requires full table scan for each partition
© 2010 Julian Dyke
2006
Full Table Scan
2007
Full Table Scan
2008
Full Table Scan
2009
Full Table Scan
juliandyke.com
Oracle 11g Enhancements
Incremental Statistics
102

In Oracle 11.1 and above statistics can be gathered incrementally for
partitioned tables

To gather incremental statistics on a specific table
 INCREMENTAL and PUBLISH preferences for table must be TRUE
 ESTIMATE_PERCENT must be AUTO_SAMPLE_SIZE
 GRANULARITY must be AUTO

For each partition a synopsis is created
 Contains data about distinct values for each column in partition
 Stored in
 SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$
 SYS.WRI$_OPTSTAT_SYNOPSIS$

If a partition has not been modified synopsis can be used to calculate global
statistics

Synopsis must be generated for all partitions first time statistics are gathered
after incremental statistics are enabled
© 2010 Julian Dyke
juliandyke.com
Oracle 11g Enhancements
Incremental Statistics

Set INCREMENTAL preference to TRUE for table:
BEGIN
DBMS_STATS.SET_TABLE_PREFS
(
ownname => 'GP',
tabname => 'CAR4',
pname => 'INCREMENTAL',
pvalue => 'TRUE'
);
END;
/

Gather table statistics again to generate synopsis for each partition
DBMS_STATS.GATHER_TABLE_STATS
(
ownname => 'GP',
tabname => 'CAR4'
);
103
© 2010 Julian Dyke
All partitions
will be scanned
first time to
create synopsis
juliandyke.com
Oracle 11g Enhancements
Incremental Statistics

Update data in one partition:
UPDATE car4 SET team_points = driver_points
WHERE season_key = '2009';

Gather table statistics again:
DBMS_STATS.GATHER_TABLE_STATS
(
ownname => 'GP',
tabname => 'CARP'
);


Uses synopsis for unmodified partitions
Requires full table scan for modified partition
2006
Synopsis
2007
Synopsis
2008
Synopsis
2009
104
© 2010 Julian Dyke
Full Table Scan
juliandyke.com
Thanks to the following for corrections:


Greg Rahn
Tony Hasler
[email protected]
105
© 2010 Julian Dyke
juliandyke.com