Oracle Clustered File System (OCFS)
Download
Report
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