SQL Tuning - Oracle 12c - What is New?

Applies to Oracle 12c.

SQL Tuning has three major modules in Oracle 12c.

- Adaptive SQL Plan Management

- Adaptive Execution Plans

- Optimizer Statistics Management

1. Adaptive SQL Plan Management

Summary of SQL Plan Management (SPM):

SQL Plan Baseline:

SQL Management Base Enhancements:

2. Adaptive Execution Plans

Adaptive Execution Plans:

2.1 Dynamic Adaptive Plan:

Using Dynamic Plans:

Dynamic Plan - Adaptive Process:

Dynamic Adaptive Plan Example:

2.2 Reoptimization Adaptive Plan:

3. Optimizer Statistics Management

3.1 SQL Plan Directives

Creating SQL Plan Directives

Using SQL Plan Directives:

SQL Plan Directives - Example:

3.2 Statistics Gathering Performance Improvements

3.2.1 Online Statistics Gathering:

3.2.2 Concurrent Statistics Gathering:

Concurrent Statistics Gathering - Creating Jobs at Different Levels:

3.2.3 Incremental Statistical Gathering Improvements:

Incremental Global Statistics - Workflow:

3.2.4 Session Private Statistics for Global Temporary Tables: 

3.3 Histogram Enhancements: 

3.3.1 Top Frequency Histograms: 

3.3.2 Hybrid Histograms: 

Hybrid Histogram Example: 

3.4 Enhancements to Extended Statistics: 

3.4.1 Column Group: 

Example:

3.5 Dynamic Sampling Enhancements: 

Automatic Dynamic Sampling - optimizer_dynamic_sampling:

Summary of SQL Tuning in Oracle 12c: 

•Adaptive SQL Plan Management

•Enhancements to the SQL management base

•How to use Dynamic plan to improve query performance

•Reoptimization for adaptive execution plans

•How to use SQL plan directives to generate a better plan

•Statistics Gathering performance improvements

•How to use new histograms

•Enhancements to extended statistics and how to detect useful column groups for a specific workload

•Enhancements to dynamic sampling

Reference / Read More: 

Oracle Database 11g: The Top Features for DBAs and Developers
By Arup Nanda
Adaptive Cursors and SQL Plan Management
http://www.oracle.com/technetwork/articles/sql/11g-sqlplanmanagement-101938.html

SQL Plan Management with Oracle Database 12c
Oracle White Paper, June 2013
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf

SQL Plan Management (Part 4 of 4): User Interfaces and Other Features
By Maria Colgan on Feb 02, 2009
https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_4_of_4_user_interfaces_and_other_features

How do adaptive cursor sharing and SQL Plan Management interact?
By Allison on Feb 11, 2013
https://blogs.oracle.com/optimizer/entry/how_do_adaptive_cursor_sharing