Skip Headers
Oracle® Database SQL Tuning Guide
12c Release 1 (12.1)

Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub

Changes in This Release for Oracle Database SQL Tuning Guide

This preface contains:

Changes in Oracle Database 12c Release 1 (12.1)

Oracle Database SQL Tuning Guide for Oracle Database 12c Release 1 (12.1) has the following changes.

New Features

The following features are new in this release:

  • Adaptive SQL Plan Management (SPM)

    The SPM Evolve Advisor is a task infrastructure that enables you to schedule an evolve task, rerun an evolve task, and generate persistent reports. The new automatic evolve task, SYS_AUTO_SPM_EVOLVE_TASK, runs in the default maintenance window. This task ranks all unaccepted plans and runs the evolve process for them. If the task finds a new plan that performs better than existing plan, the task automatically accepts the plan. You can also run evolution tasks manually using the DBMS_SPM package.

    See "Managing the SPM Evolve Advisor Task".

  • Adaptive query optimization

    Adaptive query optimization is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics. The set of capabilities include:

    • Adaptive plans

      An adaptive plan has built-in options that enable the final plan for a statement to differ from the default plan. During the first execution, before a specific subplan becomes active, the optimizer makes a final decision about which option to use. The optimizer bases its choice on observations made during the execution up to this point. The ability of the optimizer to adapt plans can improve query performance.

      See "Adaptive Plans".

    • Automatic reoptimization

      When using automatic reoptimization, the optimizer monitors the initial execution of a query. If the actual execution statistics vary significantly from the original plan statistics, then the optimizer records the execution statistics and uses them to choose a better plan the next time the statement executes. The database uses information obtained during automatic reoptimization to generate SQL plan directives automatically.

      See "Automatic Reoptimization".

    • SQL plan directives

      In previous releases, the database stored compilation and execution statistics in the shared SQL area, which is nonpersistent. Starting in this release, the database can use a SQL plan directive, which is additional information and instructions that the optimizer can use to generate a more optimal plan. The database stores SQL plan directives persistently in the SYSAUX tablespace. When generating an execution plan, the optimizer can use SQL plan directives to obtain more information about the objects accessed in the plan.

      See "SQL Plan Directives".

    • Dynamic statistics enhancements

      In previous releases, Oracle Database only used dynamic statistics (previously called dynamic sampling) when one or more of the tables in a query did not have optimizer statistics. Starting in this release, the optimizer automatically decides whether dynamic statistics are useful and which dynamic statistics level to use for all SQL statements. Dynamic statistics gathers are persistent and usable by other queries.

      See "Dynamic Statistics".

  • New types of histograms

    This release introduces top frequency and hybrid histograms. If a column contains more than 254 distinct values, and if the top 254 most frequent values occupy more than 99% of the data, then the database creates a top frequency histogram using the top 254 most frequent values. By ignoring the nonpopular values, which are statistically insignificant, the database can produce a better quality histogram for highly popular values. A hybrid histogram is an enhanced height-based histogram that stores the exact frequency of each endpoint in the sample, and ensures that a value is never stored in multiple buckets.

    Also, regular frequency histograms have been enhanced. The optimizer computes frequency histograms during NDV computation based on a full scan of the data rather than a small sample (when AUTO_SAMPLING is used). The enhanced frequency histograms ensure that even highly infrequent values are properly represented with accurate bucket counts within a histogram.

    See Chapter 11, "Histograms."

  • Monitoring database operations

    Real-Time Database Operations Monitoring enables you to monitor long running database tasks such as batch jobs, scheduler jobs, and Extraction, Transformation, and Loading (ETL) jobs as a composite business operation. This feature tracks the progress of SQL and PL/SQL queries associated with the business operation being monitored. As a DBA or developer, you can define business operations for monitoring by explicitly specifying the start and end of the operation or implicitly with tags that identify the operation.

    See "Monitoring Database Operations".

  • Concurrent statistics gathering

    You can concurrently gather optimizer statistics on multiple tables, table partitions, or table subpartitions. By fully utilizing multiprocessor environments, the database can reduce the overall time required to gather statistics. Oracle Scheduler and Advanced Queuing create and manage jobs to gather statistics concurrently. The scheduler decides how many jobs to execute concurrently, and how many to queue based on available system resources and the value of the JOB_QUEUE_PROCESSES initialization parameter.

    See "Gathering Optimizer Statistics Concurrently".

  • Reporting mode for DBMS_STATS statistics gathering functions

    You can run the DBMS_STATS functions in reporting mode. In this mode, the optimizer does not actually gather statistics, but reports objects that would be processed if you were to use a specified statistics gathering function.

    See "Running Statistics Gathering Functions in Reporting Mode".

  • Reports on past statistics gathering operations

    You can use DBMS_STATS functions to report on a specific statistics gathering operation or on operations that occurred during a specified time.

    See "Reporting on Past Statistics Gathering Operations".

  • Automatic column group creation

    With column group statistics, the database gathers optimizer statistics on a group of columns treated as a unit. Starting in this release, Oracle Database automatically determines which column groups are required in a specified workload or SQL tuning set, and then creates the column groups. Thus, for any specified workload, you no longer need to know which columns from each table must be grouped.

    See "Detecting Useful Column Groups for a Specific Workload".

  • Session-private statistics for global temporary tables

    Starting in this release, global temporary tables have a different set of optimizer statistics for each session. Session-specific statistics improve performance and manageability of temporary tables because users no longer need to set statistics for a global temporary table in each session or rely on dynamic statistics. The possibility of errors in cardinality estimates for global temporary tables is lower, ensuring that the optimizer has the necessary information to determine an optimal execution plan.

    See "Session-Specific Statistics for Global Temporary Tables".

  • SQL Test Case Builder enhancements

    SQL Test Case Builder can capture and replay actions and events that enable you to diagnose incidents that depend on certain dynamic and volatile factors. This capability is especially useful for parallel query and automatic memory management.

    See Chapter 17, "Gathering Diagnostic Data with SQL Test Case Builder."

  • Online statistics gathering for bulk loads

    A bulk load is a CREATE TABLE AS SELECT or INSERT INTO ... SELECT operation. In previous releases, you needed to manually gather statistics after a bulk load to avoid the possibility of a suboptimal execution plan caused by stale statistics. Starting in this release, Oracle Database gathers optimizer statistics automatically, which improves both performance and manageability.

    See "Online Statistics Gathering for Bulk Loads".

  • Reuse of synopses after partition maintenance operations

    ALTER TABLE EXCHANGE is a common partition maintenance operation. During a partition exchange, the statistics of the partition and the table are also exchanged. A synopsis is a set of auxiliary statistics gathered on a partitioned table when the INCREMENTAL value is set to true. In previous releases, you could not gather table-level synopses on a table. Thus, you could not gather table-level synopses on a table, exchange the table with a partition, and end up with synopses on the partition. You had to explicitly gather optimizer statistics in incremental mode to create the missing synopses. Starting in this release, you can gather table-level synopses on a table. When you exchange this table with a partition in an incremental mode table, the synopses are also exchanged.

    See "Maintaining Incremental Statistics for Partition Maintenance Operations".

  • Automatic updates of global statistics for tables with stale or locked partition statistics

    Incremental statistics can automatically calculate global statistics for a partitioned table even if the partition or subpartition statistics are stale and locked.

    See "Maintaining Incremental Statistics for Tables with Stale or Locked Partition Statistics".

  • Cube query performance enhancements

    These enhancements minimize CPU and memory consumption and reduce I/O for queries against cubes.

    See Table 9-7, "OPERATION and OPTIONS Values Produced by EXPLAIN PLAN" to learn about the CUBE JOIN operation.

Deprecated Features

The following features are deprecated in this release, and may be desupported in a future release:

Desupported Features

Some features previously described in this document are desupported in Oracle Database 12c. See Oracle Database Upgrade Guide for a list of desupported features.

Other Changes

The following are additional changes in the release:

  • New tuning books

    The Oracle Database 11g Oracle Database Performance Tuning Guide has been divided into two books for Oracle Database 12c:

    • Oracle Database Performance Tuning Guide, which contains only topics that pertain to tuning the database

    • Oracle Database SQL Tuning Guide, which contains only topics that pertain to tuning SQL