Oracle 12c has several enhanced features for statistics gathering which add in optimization of PL/SQL execution.    This article describes these enhancements.

 

 

The Optimizer uses statistics to create the best execution plans.  Re-optimization uses the execution of previous statements results to determine if the executed plan was optimal and adjusts plans for future execution in an iterative process.

Three types of re-optimization:

1. Statistics Feedback – also known as cardinality feedback, it compares cardinality of estimated and actual executions. Storing the correct cardinality statistics and creates SQL plane directive for future use.
2. Performance Feedback – Used to improve degree of parallelism for future SQL statements based on previous executions when Automatic Degree of Parallelism (AutoDOP) is enabled in adaptive mode.
3. SQL plan directives – Is additional information used by optimizer to determine correct execution plans and are created on query expressions not SQL statements thus can be applied to multiple SQL statements. Multiple directives can be used for single SQL statement.  SQL directives are purged if not used in 53 days.  You can see directives in views:   DBA_SQL_PLAN_DIRECTIVES and DBA_SQL_PLAN_DIR_OBJECTS.

 

 

 

Partition table statistics – Two sets of statistics are gathered at the partition level and table level, in 12c the table level statistics are aggregated by partition level avoiding full table scan.

 

INCREMENTAL_STALENESS – Preference setting in DBMS_STATS.SET_TABLE_PREFS allows you to determine when statistics on tables are stale by percentage of change with  USE_STALE_PERCENT or locking statistics forcing their use regardless of how much change has occurred with USE_LOCKED_STATS.

 

Incremental statistics improved by EXCHANGE_PARTITION command allows you to exchange statistics from non-partitioned table with those in a partition.  Prior to 12c you had to gather data on all partitions before using incremental global statistics.   Use the DBMS_STATS package preference of INCREMENTAL_LEVEL set to TABLE (the default is PARTITION) to use this EXCHANGE_PARTITION feature.

 

Concurrent Statistics – when global statistics gathering preference CONCURRENT is set, the Job Scheduler and Advance queuing gather states on table/partitions concurrently.  12c has optimized automatic statistics gathering by batching smaller jobs to run together.  When preference CONCURRENT is set to ALL or AUTOMATIC stats a gathered nightly.

 

 

Automatic column group detection – Gathering of statistics on columns used in filter predicates by estimating cardinality.   Determining columns to gather increased stats is determined by workload history.  Three step process determines Auto Column Group detection:

1. Seed column usage – workload history via the DBMS_STATS.SEED_COL_USAGE procedure.
2. Create the column groups – Use the DBMS_STATS.CREATE_EXTENDED_STATS function to id columns groups based on usage information gathered. Once identified extended statistics will be automatically generated.
3. Regather statistics – Regather statistics for all tables identified for column stats extended.

 

 

Larry Catt

OCP