Optimizer Statistics Julian Dyke Independent Consultant Web Version

Agenda u u u u u 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 11 g Enhancements juliandyke. com

Statistics Strategies 3 © 2010 Julian Dyke juliandyke. com

Statistics Strategies Introduction 4 u Only gather statistics when object has changed significantly u Consider generating manual statistics for: u Very large objects u Temporary tables u Highly volatile tables u Try to collect statistics during a specified window u Do not execute SQL statements during statistics collection u Use DBMS_STATS parallelism u Review sample sizes for very large objects u In complex databases do not rely on Auto job u Unpredictable collection behaviour / duration u Unpredictable execution plan changes u See Metalink Note 44961. 1 - Statistics Gathering; Frequency and Strategy © 2010 Julian Dyke juliandyke. com

ANALYZE 5 © 2010 Julian Dyke juliandyke. com

ANALYZE Introduction u SQL statement u Introduced in Oracle 7. 0 Collects object statistics for Cost Based Optimizer u 6 u Initially collected statistics on tables and indexes u Subsequently on histograms u Advantages u Online analyze - Oracle 9. 0 and above u Optionally validates structure of tables, indexes and clusters u Optionally detects chained / migrated rows u Disadvantages u Serial statistics collection only u Limited partition-awareness © 2010 Julian Dyke juliandyke. com

ANALYZE VALIDATE STRUCTURE u Syntax is ANALYZE INDEX <index_name> VALIDATE STRUCTURE; 7 u Populates session-level dynamic performance views u INDEX_STATS u INDEX_HISTOGRAMS u Reports index statistics including: u height u number of leaf blocks u number of branch blocks u number of distinct keys u Reports space released by deletions u Use to determine when to coalesce or rebuild index u Recommends index compression columns u Use to determine which indexes should be compressed © 2010 Julian Dyke juliandyke. com

ANALYZE VALIDATE STRUCTURE ONLINE u u Introduced in Oracle 9. 0. 1 Syntax is ANALYZE INDEX <index_name> VALIDATE STRUCTURE ONLINE; u 8 ONLINE option u only checks structure u does not require TM (DML) lock u 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 u PL/SQL supported package u Introduced in Oracle 8. 1. 5 u In Oracle 11. 1 there are 107 subroutines u Defined in $ORACLE_HOME/rdbms/admin/dbmsstat. sql u Advantages u Parallel statistics collection u Partition-awareness u Disadvantages u Cannot perform structure validation u Cannot detect chaining / migration © 2010 Julian Dyke juliandyke. com

DBMS_STATS Gathering Statistics 11 u Statistics can be gathered using the following subroutines: u GATHER_DATABASE_STATS u GATHER_SCHEMA_STATS u GATHER_TABLE_STATS u GATHER_INDEX_STATS u These routines allow you to specify: u Sample size u Granularity (partitioned tables only) u Histogram policy u Cursor invalidation policy © 2010 Julian Dyke juliandyke. com

DBMS_STATS Deleting Statistics 12 u Statistics can be deleted using the following subroutines: u DELETE_DATABASE_STATS u DELETE_SCHEMA_STATS u DELETE_TABLE_STATS u DELETE_INDEX_STATS u DELETE_COLUMN_STATS u DELETE_TABLE_STATS includes u CASCADE_PART - optionally delete partition statistics u CASCADE_COLUMNS - optionally delete column statistics u CASCADE_INDEXES - optionally delete index statistics u DELETE_INDEX_STATS, DELETE_COLUMN_STATS include: u CASCADE_PART - optionally delete partition statistics © 2010 Julian Dyke juliandyke. com

DBMS_STATS Statistics Tables u Used to u export / import statistics u store object statistics values PRIOR to GATHER operations u To create a statistics table use CREATE_STATS_TABLE. For example: dbms_stats. create_stats_table ( ownname => 'SYSTEM', tabname => 'STATS 1', tblspace=> 'SYSAUX' ); u 13 To drop a statistics table use DROP_STATS_TABLE © 2010 Julian Dyke juliandyke. com

DBMS_STATS Exporting and Importing Statistics 14 u Statistics can be exported to a statistics table using: u EXPORT_COLUMN_STATS u EXPORT_DATABASE_STATS u EXPORT_INDEX_STATS u EXPORT_SCHEMA_STATS u EXPORT_TABLE_STATS u Statistics can be imported from a statistics table using: u IMPORT_COLUMN_STATS u IMPORT_DATABASE_STATS u IMPORT_INDEX_STATS u IMPORT_SCHEMA_STATS u IMPORT_TABLE_STATS u To transfer the statistics to another database export and import the statistics table © 2010 Julian Dyke juliandyke. com

DBMS_STATS OPTIONS parameter 15 u Specifies how to determine which objects to gather statistics for: u GATHER - gather statistics for all objects u GATHER AUTO - gather statistics for all objects needing new statistics u GATHER STALE - gather statistics for all objects having stale statistics u GATHER EMPTY- gather statistics for all objects having no statistics u LIST AUTO - list objects needing new statistics u LIST STALE - list objects having stale statistics u LIST EMPTY - list objects having stale statistics u The OBJLIST parameter must be specified as an OUT parameter for the LIST options u 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 u If GATHER STALE is specified statistics will be gathered for objects where at least 10% of rows have changed u Total of INSERTS, UPDATES and DELETES since last statistics collection u In Oracle 10 g percentage is fixed at 10% u In Oracle 11 g percentage is user-configurable © 2010 Julian Dyke juliandyke. com

DBMS_STATS Stale Statistics u In Oracle 10. 1 and above table modification statistics: u are enabled if STATISTICS_LEVEL is TYPICAL or ALL u are disabled if STATISTICS_LEVEL is BASIC u are collected in the SGA u are automatically flushed to SYS. MON_MODS$ u by SMON background process u every 15 minutes to SYS. MON_MODS$ u if a significant number of rows has changed (possibly 10%) u u 17 can be manually flushed to SYS. MON_MOD$ using: u DBMS_STATS. FLUSH_DATABASE_MONITORING_INFO are reported in DBA_TAB_MODIFICATIONS © 2010 Julian Dyke juliandyke. com

DBMS_STATS Stale Statistics u 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 u In Oracle 8 i, table modification monitoring can only be enabled manually using: ALTER TABLE <table_name> MONITORING; ALTER TABLE <table_name> NOMONITORING; u In Oracle 9 i, table modification monitoring can also be enabled using DBMS_STATS. ALTER_SCHEMA_TAB_MONITORING ('<owner>', TRUE); DBMS_STATS. ALTER_DATABASE_TAB_MONITORING (TRUE); 19 u Reported in DBA_TABLES. MONITORING u In Oracle 10. 1 and above MONITORING clause is deprecated and ignored © 2010 Julian Dyke juliandyke. com

DBMS_STATS ESTIMATE_PERCENT Parameter 20 u Specified using ESTIMATE_PERCENT parameter in u GATHER_DATABASE_STATS u GATHER_DICTIONARY_STATS u GATHER_INDEX_STATS u GATHER_SCHEMA_STATS u GATHER_TABLE_STATS u Valid range is 0. 000001 to 100 u NULL means COMPUTE u Default (10. 2) is DBMS_STATS. AUTO_SAMPLE_SIZE u Oracle determines sample size automatically u Default can be changed using SET_PARAM procedure © 2010 Julian Dyke juliandyke. com

DBMS_STATS GRANULARITY Parameter 21 u Only relevant for partitioned objects u ALL - gather global, partition and subpartition statistics u AUTO - determine granularity based on partitioning type. u DEFAULT - gather global and partition-level statistics. Now obsolete. Use GLOBAL AND PARTITION instead u GLOBAL - gather global statistics u GLOBAL AND PARTITION - gather global and partition-level statistics u PARTITION - gather partition-level statistics u SUBPARTITION - gather subpartition-level statistics u In Oracle 10. 2 default is AUTO (not DEFAULT) © 2010 Julian Dyke juliandyke. com

DBMS_STATS NO_INVALIDATE parameter 22 u In earlier versions ANALYZE and DBMS_STATS gather procedures always invalidated cursors referencing affected object u In Oracle 10. 1 and above, invalidation can be controlled using NO_INVALIDATE parameter u TRUE - do not invalidate dependent cursors u FALSE - invalidate dependent cursors u DBMS_STATS. AUTO_INVALIDATE (default) - Oracle determines whether to invalidate dependent cursors or not. u In Oracle 10. 2 the default value can be changed using SET_PARAM procedure u Only relevant for DBMS_STATS gather procedures u ANALYZE always invalidates analyzed objects © 2010 Julian Dyke juliandyke. com

DBMS_STATS Data Dictionary Statistics u Oracle recommends that data dictionary statistics are collected in Oracle 9. 0. 1 and above u Subroutines are: u GATHER_DICTIONARY_STATS u DELETE_DICTIONARY_STATS u EXPORT_DICTONARY_STATS u RESTORE_DICTIONARY_STATS u In Oracle 9 i data dictionary statistics should only be collected using: dbms_stats. gather_schema_stats (ownname => 'SYS', cascade=> TRUE); u In Oracle 9 i data dictionary statistics can be deleted using: dbms_stats. delete_schema_stats (ownname => 'SYS'); 23 u See Metalink Note 245051. 1 u 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 u In Oracle 10. 1 and above Oracle recommends that statistics are collected on fixed objects u These include X$ and V$ views u Subroutines include: u GATHER_FIXED_OBJECTS_STATS u DELETE_FIXED_OBJECTS_STATS u EXPORT_FIXED_OBJECT_STATS u RESTORE_FIXED_OBJECTS_STATS u For example: dbms_stats. gather_fixed_objects_stats; 24 u Only gather fixed object statistics after the database has been running a representative workload u See Metalink Note 272479. 1 © 2010 Julian Dyke juliandyke. com

DBMS_STATS METHOD_OPT Parameter u Specifies histogram collection policy u Can be specified for: u GATHER_DATABASE_STATS, u GATHER_DICTIONARY_STATS, u GATHER_SCHEMA_STATS u GATHER_TABLE_STATS u 25 Default is FOR ALL COLUMNS SIZE AUTO u Uses data from COL_USAGE$ table © 2010 Julian Dyke juliandyke. com

DBMS_STATS METHOD_OPT Parameter u Syntax is: FOR ALL [ INDEXED | HIDDEN ] COLUMNS [ size_clause ] FOR COLUMNS [ [ size_clause ] column|attribute [size_clause] [, column|attribute [size_clause]. . ] u size_clause is: SIZE { integer | REPEAT | AUTO | SKEWONLY } u 26 where: u integer - number of histogram buckets (1. . 254) u REPEAT - only collect histograms for columns that already have histograms u AUTO - determine which columns need histograms automatically u SKEWONLY - determine which columns need histograms based on data distribution © 2010 Julian Dyke juliandyke. com

DBMS_STATS Column Usage Monitoring u u 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 u Updated when statements are parsed u Reset when statistics are gathered for table © 2010 Julian Dyke juliandyke. com

DBMS_STATS Histograms Summary 28 u Do not collect histograms if not necessary u Expensive to collect u Check accuracy of histograms u Watch for columns with high proportion of NULL values u Watch for highly skewed columns u Height-balanced histograms u Limited use if column is not highly skewed u Object oriented designs rarely work with histograms u Using histograms introduces bind variable peeking issues © 2010 Julian Dyke juliandyke. com

DBMS_STATS Locking Statistics u Statistics can be locked for schemas, tables, and partitions u LOCK_SCHEMA_STATS / UNLOCK_SCHEMA_STATS u LOCK_TABLE_STATS / UNLOCK_TABLE_STATS u LOCK_PARTITION_STATS / UNLOCK_PARTITION_STATS dbms_stats. lock_table_stats ( ownname => 'USER 1', tabname => 'T 1', stattype => 'ALL' ); 29 u Possible values for STATTYPE are NULL, DATA, CACHE and ALL u By default GATHER procedures will not overwrite locked statistics u Specify FORCE => TRUE to overwrite u Statistics locking reported in STATTYPE_LOCKED column in u DBA_TAB_STATISTICS u DBA_IND_STATISTICS © 2010 Julian Dyke juliandyke. com

DBMS_STATS Default Parameters u u u 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 AUTOSTATS_TARGET can be u ALL collect statistics for all objects in the database u ORACLE collect statistics for all Oracle-owned objects u AUTO determine which objects need new statistics © 2010 Julian Dyke juliandyke. com

DBMS_STATS Default Parameters u To set default parameters use SET_PARAM: dbms_stats. set_param (pname => 'CASCADE', pval => TRUE); u u Take care with NULL values For example: dbms_stats. set_param ('ESTIMATE_PERCENT', 'NULL'); u Sets the default to COMPUTE statistics dbms_stats. set_param ('ESTIMATE_PERCENT', NULL); u u 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; u 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 u u u 33 ANALYZE updates the following columns in DBA_TAB_STATISTICS u EMPTY_BLOCKS u AVG_SPACE DBMS_STATS sets these columns to 0 Tables u AVG_ROW_LEN differs - e. g. : ANALYZE 43 DBMS_STATS 39 u Indexes u No obvious differences between ANALYZE and DBMS_STATS u Columns u AVG_COL_LEN differs - e. g. : © 2010 Julian Dyke ANALYZE 4 DBMS_STATS 5 juliandyke. com

ANALYZE versus DBMS_STATS Differences 34 u ANALYZE <table> gathers statistics for related indexes u Prior to Oracle 10. 1 DBMS_STATS requires CASCADE => TRUE u For empty indexes u ANALYZE sets BLEVEL to 0 u DBMS_STATS sets BLEVEL > 0 © 2010 Julian Dyke juliandyke. com

ANALYZE versus DBMS_STATS Summary 35 u Use ANALYZE to u Collect index statistics without updating data dictionary u Identify candidates for index coalesce/rebuild u Identify candidates for index compression u Identify tables containing excessive chained rows u Use DBMS_STATS u Gather statistics on tables / indexes u Do not mix ANALYZE / DBMS_STATS © 2010 Julian Dyke juliandyke. com

System Statistics 36 © 2010 Julian Dyke juliandyke. com

System Statistics Introduction 37 u System statistics were introduced in Oracle 9. 0. 1 u Subroutines are: u GATHER_SYSTEM_STATS u DELETE_SYSTEM_STATS u GET_SYSTEM_STATS u SET_SYSTEM_STATS u EXPORT_SYSTEM_STATS u IMPORT_SYSTEM_STATS u RESTORE_SYSTEM_STATS u System statistics are stored in SYS. AUX_STATS$ © 2010 Julian Dyke juliandyke. com

System Statistics Workload versus No Workload Statistics 38 u Oracle 10. 1 and above supports: u noworkload statistics - default values supplied during database creation u workload statistics - collected from representative workload u Workload statistics include: u CPUSPEED - CPU speed u SREADTIM - Single block read time in milliseconds u MREADTIM - Multi block read time in milliseconds u MBRC - Multi block read count u MAXTHR - Maximum I/O system throughput (parallel execution only) u SLAVETHR - Average slave I/O throughput (parallel execution only) u No workload statistics include: u CPUSPEEDNW - CPU speed u IOSEEKTIM - IO Seek Time u IOTFRSPEED - IO Transfer Speed © 2010 Julian Dyke juliandyke. com

System Statistics Example u Create a statistics table using: dbms_stats. create_stat_table ('SYS', 'OLTP_STATS'); u Gather system statistics for a typical period using: dbms_stats. gather_system_stats ( gathering_mode => 'INTERVAL', interval => 60, stattab => OLTP_STATS', statid => 'OLTP' ); u -- 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 u Enable system statistics for single instance databases u Usually improve execution plans u Consider carefully before enabling system statistics in RAC databases u System statistics are database-specific u Watch for asymmetric nodes u Hardware differences u Service configuration u System statistics are: u Difficult to monitor u Very difficult to update / remove u If exporting object statistics to another system for testing: u Remember to export system statistics © 2010 Julian Dyke juliandyke. com

Automatic Statistics Collection 41 © 2010 Julian Dyke juliandyke. com

Automatic Statistics Collection Introduction u Oracle 10. 1 and above u Statistics collected during Maintenance Window u Monday - Friday 22: 00 to 06: 00 u Saturday / Sunday All day u Note that weekend window effectively starts at 06: 00 on Saturday morning u Scheduler job u GATHER_STATS_JOB Scheduler job class u AUTO_TASKS_JOB_CLASS Scheduler windows u WEEKNIGHT_WINDOW u WEEKEND_WINDOW Scheduler window group u MAINTENANCE_WINDOW_GROUP u u u 42 © 2010 Julian Dyke juliandyke. com

Statistics History 43 © 2010 Julian Dyke juliandyke. com

Statistics History Introduction u In Oracle 10. 1 and above, existing statistics are stored in the data dictionary when new statistics are collected u Statistics can be restored using: u RESTORE_DATABASE_STATS u RESTORE_DICTIONARY_STATS u RESTORE_FIXED_OBJECTS_STATS u RESTORE_SCHEMA_STATS u RESTORE_SYSTEM_STATS u RESTORE_TABLE_STATS u Statistics history for tables only is reported in DBA_TAB_STATS_HISTORY SELECT stats_update_time FROM dba_tab_stats_history WHERE owner = 'USER 1' AND table_name = 'T 1'; 12 -FEB-09 04. 36. 32. 997000 PM +00: 00 44 © 2010 Julian Dyke juliandyke. com

Statistics History Optimizer Statistics Operations u 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 -FEB 09 06. 00. 03 07 -FEB-09 06. 01. 52 gather_database_stats(auto) 09 -FEB 09 10. 03 09 -FEB-09 10. 03. 37 gather_database_stats(auto) 10 -FEB 09 10. 03 10 -FEB-09 10. 02. 01 45 u DBA_OPTSTAT_OPERATIONS includes: u gather_database_stats(auto) u gather_schema_stats u DBA_OPTSTAT_OPERATIONS does not include: u gather_table_stats u gather_index_stats © 2010 Julian Dyke juliandyke. com

Statistics History Data Dictionary Tables 46 u Historic statistics are stored in tables created by u $ORACLE_HOME/rdbms/admin/catost. sql u Tables created are: u WRI$_OPTSTAT_TAB_HISTORY u WRI$_OPTSTAT_IND_HISTORY u WRI$_OPTSTAT_HISTHEAD_HISTORY u WRI$_OPTSTAT_HISTGRM_HISTORY u WRI$_OPTSTAT_AUX_HISTORY u WRI$_OPTSTAT_OPR u OPTSTAT_HIST_CONTROL$ © 2010 Julian Dyke juliandyke. com

Statistics History Retention Period u To check statistics history retention period use: SELECT dbms_stats. get_stats_history_retention FROM dual; u Default is 31 days u 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 u 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 u Statistics history rows contain previous values for optimizer statistics u Statistics history does not include current value u SAVTIME column is time row was written u ANALYZETIME column is time statistics were analyzed u SAVTIME != ANALYZETIME u ANALYZE does not update statistics history u [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 u Table statistics can be set manually using SET_TABLE_STATS u Values can be specified for: u NUMROWS - number of rows u NUMBLKS - number of blocks u 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 u Index statistics can be set manually using SET_INDEX_STATS u Values can be specified for: u NUMROWS - number of rows u NUMBLKS - number of blocks u NUMDIST - number of distinct values u AVGLBLK - average leaf blocks per key u AVGDBLK - average data blocks per key u CLSTFCT - clustering factor u INDLEVEL - index level (height) u Use ANALYZE INDEX VALIDATE STRUCTURE to determine values © 2010 Julian Dyke juliandyke. com

Setting Column Statistics Introduction 56 u Column statistics can be set manually using SET_COLUMN_STATS u Values can be specified for: u DISTCNT - number of distinct values u DENSITY - 1 / (number of distinct values) u NULLCNT - number of NULL values u Low and high values u Histograms u Note that DISTCNT usage differs for columns with histograms © 2010 Julian Dyke juliandyke. com

Setting Column Statistics Data Types u u 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 VARCHAR 2(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 VARCHAR 2, p_table_name VARCHAR 2) 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 c 1 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 c 1; LOOP FETCH c 1 INTO l_row; EXIT WHEN c 1%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 = 'COL 1' THEN l_numvals(1) : = get_min_col 1 (p_owner, p_table_name); l_numvals(2) : = get_max_col 2 (p_owner, p_table_name); DBMS_STATS. PREPARE_COLUMN_VALUES (l_statrec, l_numvals); ELSIF l_row. column_name = 'COL 2' 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 = 'COL 3' THEN l_charvals(1) : = get_min_col 3 (p_owner, p_table_name); l_charvals(2) : = get_max_col 3 (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) u 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 c 1; 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 u u 64 Partition statistics are reported by: u DBA_TABLES u DBA_TAB_PARTITIONS u DBA_TAB_SUBPARTITIONS Indexes u DBA_INDEXES u DBA_IND_PARTITIONS u DBA_IND_SUBPARTITIONS Columns u DBA_TAB_COL_STATISTICS u DBA_PART_COL_STATISTICS u DBA_SUBPART_COL_STATISTICS Histograms u DBA_TAB_HISTOGRAMS u DBA_PART_HISTOGRAMS u 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) u Create a range partitioned table containing data for last four seasons CREATE TABLE car 3 PARTITION BY RANGE (season_key) ( PARTITION p 2005 VALUES LESS THAN ('2006'), PARTITION p 2006 VALUES LESS THAN ('2007'), PARTITION p 2007 VALUES LESS THAN ('2008'), PARTITION p 2008 VALUES LESS THAN ('2009') ) AS SELECT * FROM car WHERE season_key >= '2005'; CREATE INDEX car 3_i 1 ON car 3(season_key, race_key, position) LOCAL; dbms_stats. gather_table_stats ( ownname => 'GP', tabname => 'CAR 3', estimate_percent => NULL, cascade => TRUE ); 65 © 2010 Julian Dyke juliandyke. com

Partition Statistics Example (2 of 12) u Table Statistics SELECT num_rows, blocks, avg_row_len FROM dba_tables WHERE owner = 'GP' AND table_name = 'CAR 3'; 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 = 'CAR 3'; 66 PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN P 2005 380 6 36 P 2006 396 6 38 P 2007 374 6 37 P 2008 368 6 37 © 2010 Julian Dyke juliandyke. com

Partition Statistics Example (3 of 12) u Index Statistics SELECT blevel, leaf_blocks, distinct_keys, clustering_factor, num_rows FROM dba_indexes WHERE owner = 'GP' AND index_name = 'CAR 3_I 1'; 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 = 'CAR 3_I 1'; 67 PARTITION_NAME BLEVEL LEAF_BLOCKS DIST KEYS CLUFAC NUM_ROWS P 2005 1 2 380 3 380 P 2006 1 2 396 3 396 P 2007 1 2 374 5 374 P 2008 1 2 368 4 368 © 2010 Julian Dyke juliandyke. com

Partition Statistics Example (4 of 12) u Add a new partition for 2009 season ALTER TABLE car 3 ADD PARTITION p 2009 VALUES LESS THAN ('2010'); u Gather statistics again dbms_stats. gather_table_stats ( ownname => 'GP', tabname => 'CAR 3', estimate_percent => NULL, cascade => TRUE ); 68 © 2010 Julian Dyke juliandyke. com

Partition Statistics Example (5 of 12) u Table Statistics SELECT num_rows, blocks, avg_row_len FROM dba_tables WHERE owner = 'GP' AND table_name = 'CAR 3'; 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 = 'CAR 3'; 69 PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN P 2005 380 6 36 P 2006 396 6 38 P 2007 374 6 37 P 2008 368 6 37 P 2009 0 0 0 © 2010 Julian Dyke juliandyke. com

Partition Statistics Example (6 of 12) u Index Statistics SELECT blevel, leaf_blocks, distinct_keys, clustering_factor, num_rows FROM dba_indexes WHERE owner = 'GP' AND index_name = 'CAR 3_I 1'; 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 = 'CAR 3_I 1'; 70 PARTITION_NAME BLEVEL LEAF_BLOCKS DIST KEYS CLUFAC NUM_ROWS P 2005 1 2 380 3 380 P 2006 1 2 396 3 396 P 2007 1 2 374 5 374 P 2008 1 2 368 4 368 P 2009 0 0 0 © 2010 Julian Dyke juliandyke. com

Partition Statistics Example (7 of 12) u Copy table statistics from 2008 to 2009 partitions dbms_stats. copy_table_stats ( ownname => 'GP', tabname => 'CAR 3', srcpartname => 'P 2008', dstpartname => 'P 2009' ); 71 © 2010 Julian Dyke juliandyke. com

Partition Statistics Example (8 of 12) u Table Statistics SELECT num_rows, blocks, avg_row_len FROM dba_tables WHERE owner = 'GP' AND table_name = 'CAR 3'; 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 = 'CAR 3'; 72 PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN P 2005 380 6 36 P 2006 396 6 38 P 2007 374 6 37 P 2008 368 6 37 P 2009 368 6 37 © 2010 Julian Dyke juliandyke. com

Partition Statistics Example (9 of 12) u Index Statistics SELECT blevel, leaf_blocks, distinct_keys, clustering_factor, num_rows FROM dba_indexes WHERE owner = 'GP' AND index_name = 'CAR 3_I 1'; 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 = 'CAR 3_I 1'; 73 PARTITION_NAME BLEVEL LEAF_BLOCKS DIST KEYS CLUFAC NUM_ROWS P 2005 1 2 380 3 380 P 2006 1 2 396 3 396 P 2007 1 2 374 5 374 P 2008 1 2 368 4 368 P 2009 1 2 368 4 368 © 2010 Julian Dyke juliandyke. com

Partition Statistics Example (10 of 12) u Copy rows from 2008 to 2009. For example: INSERT INTO car 3 SELECT '2009', race_key, driver_key, team_key, engine_key, position, laps_completed, classification_key, notes, driver_points, team_points FROM gp. car 3 WHERE season_key = '2008'; u 74 Statistics are unchanged © 2010 Julian Dyke juliandyke. com

Partition Statistics Example (11 of 12) u Execution plans for statement against 2008 and 2009 partitions: SELECT SUM(team_points) FROM gp. car 3 WHERE season_key = '2008'; 0 1 2 3 SELECT STATEMENT 0 SORT AGGREGATE 1 PARTITION RANGE (SINGLE) 2 TABLE ACCESS (FULL) OF 'CAR 3' SELECT SUM(team_points) FROM gp. car 3 WHERE season_key = '2009'; 0 1 2 3 4 u 75 0 1 2 3 SELECT STATEMENT SORT AGGREGATE PARTITION RANGE (SINGLE) TABLE ACCESS (BY INDEX ROWID) OF 'CAR 3' INDEX (RANGE SCAN) OF 'CAR 3_I 1' Plans are different even though statistics and data are theoretically identical © 2010 Julian Dyke juliandyke. com

Partition Statistics Example (12 of 12) u 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 = 'CAR 3' AND column_name = 'SEASON_KEY'; PARTITION_NAME u u 76 LOW_VALUE HIGH_VALUE P 2005 0 x 32303035 (2005) P 2006 0 x 32303036 (2006) P 2007 0 x 32303037 (2007) P 2008 0 x 32303038 (2008) P 2009 0 x 32303038 (2008) Caused by bug 5643297 - only affects 10. 2. 0. 4 Fixed in patch 7381308 u includes fixes for COPY_TABLE_STATS and APPROX_GLOBAL © 2010 Julian Dyke juliandyke. com

Partition Statistics Summary 77 u Range-partitioning u Only collect statistics for partitions undergoing change u Specify partition names and GRANULARITY parameter to restrict partitions that are analyzed u Consider setting statistics manually for new partitions u COPY_STATS has limited functionality u Watch for changing high values u Use DBMS_STATS NO_INVALIDATE option u ANALYZE invalidates all cursors referencing table being analyzed u Can result in hard-parse rates approaching 100% © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements 78 © 2010 Julian Dyke juliandyke. com

Statistics Preferences 79 © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements Statistics Preferences 80 u In Oracle 10 g preferences are: u set using SET_PARAM u reported by GET_PARAM u stored in SYS. OPTSTAT_HIST_CONTROL$ u In Oracle 11. 1 and above preferences are: u set using: u SET_TABLE_PREFS u SET_SCHEMA_PREFS u SET_DATABASE_PREFS u SET_GLOBAL_PREFS u reported by u GET_PREFS u DBA_TAB_STAT_PREFS u stored in SYS. OPTSTAT_USER_PREFS$ u In Oracle 11. 1 and above global preferences can be reset using u RESET_GLOBAL_PREF_DEFAULTS © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements Statistics Preferences 81 u Allow automatic statistics collection to be customized for individual objects u For example heavily skewed data distributions u Can be set at table, schema, database and global level u In Oracle 10 g preferences can be set for: u CASCADE u DEGREE u ESTIMATE_PERCENT u METHOD_OPT u NO_INVALIDATE u GRANULARITY u In Oracle 11 g preferences can also be set for: u PUBLISH - if TRUE publish statistics; if FALSE store as pending u STALE_PERCENT - threshold level at which statistics considered stale u INCREMENTAL - if TRUE collect incremental global partition statistics © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements Statistics Preferences 82 u Delete preferences using: u DELETE_TABLE_PREFS u DELETE_SCHEMA_PREFS u DELETE_DATABASE_PREFS u Export and import preferences using: u EXPORT_TABLE_PREFS / IMPORT_TABLE_PREFS u EXPORT_SCHEMA_PREFS / IMPORT_SCHEMA_PREFS u EXPORT_DATABASE_PREFS / IMPORT_DATABASE_PREFS © 2010 Julian Dyke juliandyke. com

Pending Statistics 83 © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements Pending Statistics 84 u In Oracle 11. 1 and above statistics can be u Published - current statistics used by optimizer u Pending - private statistics not yet published u In Oracle 11. 1 statistics for a set of objects can be collected as an atomic transaction u Published at same time u Avoids inconsistencies u PUBLISH mode can be set at the following levels u Global u Database u Schema u Table © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements Pending Statistics u To defer publication of all statistics collected by USER 1 use: dbms_stats. set_schema_prefs ('USER 1', 'PUBLISH', 'FALSE'); u To make pending statistics temporarily available to the optimizer use: ALTER SESSION optimizer_use_pending_statistics = TRUE; u To make pending statistics permanently available to the optimizer use: dbms_stats. publish_pending_statistics ( ownname => 'USER 1', tabname => NULL ); 85 u Statistics can be published at database, schema or table level u To delete pending statistics use DELETE_PENDING_STATS u To export pending statistics use EXPORT_PENDING_STATS u There is no corresponding import procedure © 2010 Julian Dyke juliandyke. com

Extended Statistics 86 © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements Extended Statistics 87 u Extended statistics attempt to reflect true selectivity of data u There are two types: u multi-column statistics u expression statistics u Extended statistics use the following procedures: u CREATE_EXTENDED_STATS u DROP_EXTENDED_STATS u SHOW_EXTENDED_STATS_NAME © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements Multi-Column Statistics u In Oracle 11 g the following statistics can be gathered on multiple columns in a table (column groups): u u u 88 Number of distinct values Density Number of nulls Frequency histograms Multicolumn statistics u Appear to work for frequency histograms u <= 254 combinations of values u Do not appear to work for height-based histograms u > 254 combinations of values © 2010 Julian Dyke juliandyke. com

Oracle 11 g 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: 01 Incorrect Cardinality 89 © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements Multi-Column Statistics DECLARE l_extension_name VARCHAR 2(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 11 g 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: 01 Correct Cardinality 91 © 2010 Julian Dyke juliandyke. com

Oracle 11 g 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#5 P 210 Z$AO 1 ON 9 ("TEAM_KEY", "ENGINE_KEY") © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements Multi-Column Statistics SELECT c. intcol#, 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#5 P 210 Z$AO 1 ON 9 © 2010 Julian Dyke New Virtual Column in Table for Multi-Column Statistics juliandyke. com

Oracle 11 g Enhancements Expression Statistics CREATE OR REPLACE FUNCTION points (season_key VARCHAR 2, 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 11 g 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: 01 Incorrect Cardinality 95 © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements Expression Statistics DECLARE l_extension_name VARCHAR 2(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 11 g 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: 01 Correct Cardinality 97 © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements Incremental Statistics 98 u For partitioned tables statistics can be collected: u For sub-partitions u For partitions u Globally u Global statistics should reflect underlying partitioned statistics u Prior to Oracle 11. 1 calculation of global statistics required full table scan of each partition in table u For many applications using range partitioning only the most recent partition is subject to change u Older partitions contain historical data © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements Incremental Statistics u For example: CREATE TABLE car 4 PARTITION BY RANGE (season_key) ( PARTITION p 2006 VALUES LESS THAN ('2005'), PARTITION p 2007 VALUES LESS THAN ('2006'), PARTITION p 2008 VALUES LESS THAN ('2007'), PARTITION p 2009 VALUES LESS THAN ('2008') ) AS SELECT * FROM car WHERE season_key >= '2005'; Create table with partitions for four years data CREATE INDEX i_car 4_1 ON carp(season_key, race_key, position) LOCAL; INSERT INTO car 4 SELECT * FROM car WHERE season_key BETWEEN 2006 AND 2009; 99 © 2010 Julian Dyke Insert data juliandyke. com

Oracle 11 g Enhancements Incremental Statistics u Gather statistics DBMS_STATS. GATHER_TABLE_STATS ( ownname => 'GP', tabname => 'CAR 4' ); u 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 11 g Enhancements Incremental Statistics u Update data in one partition: UPDATE car 4 SET team_points = driver_points WHERE season_key = '2009'; u Gather table statistics again: DBMS_STATS. GATHER_TABLE_STATS ( ownname => 'GP', tabname => 'CAR 4' ); u 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 11 g Enhancements Incremental Statistics 102 u In Oracle 11. 1 and above statistics can be gathered incrementally for partitioned tables u To gather incremental statistics on a specific table u INCREMENTAL and PUBLISH preferences for table must be TRUE u ESTIMATE_PERCENT must be AUTO_SAMPLE_SIZE u GRANULARITY must be AUTO u For each partition a synopsis is created u Contains data about distinct values for each column in partition u Stored in u SYS. WRI$_OPTSTAT_SYNOPSIS_HEAD$ u SYS. WRI$_OPTSTAT_SYNOPSIS$ u If a partition has not been modified synopsis can be used to calculate global statistics u Synopsis must be generated for all partitions first time statistics are gathered after incremental statistics are enabled © 2010 Julian Dyke juliandyke. com

Oracle 11 g Enhancements Incremental Statistics u Set INCREMENTAL preference to TRUE for table: BEGIN DBMS_STATS. SET_TABLE_PREFS ( ownname => 'GP', tabname => 'CAR 4', pname => 'INCREMENTAL', pvalue => 'TRUE' ); END; / u Gather table statistics again to generate synopsis for each partition DBMS_STATS. GATHER_TABLE_STATS ( ownname => 'GP', tabname => 'CAR 4' ); 103 © 2010 Julian Dyke All partitions will be scanned first time to create synopsis juliandyke. com

Oracle 11 g Enhancements Incremental Statistics u Update data in one partition: UPDATE car 4 SET team_points = driver_points WHERE season_key = '2009'; u Gather table statistics again: DBMS_STATS. GATHER_TABLE_STATS ( ownname => 'GP', tabname => 'CARP' ); u u 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