This preface contains:
Oracle Database SQL Tuning Guide for Oracle Database 12c Release 1 (12.1.0.2) has the following changes.
The following features are new in this release:
In-memory aggregation
This optimization minimizes the join and GROUP BY processing required for each row when joining a single large table to multiple small tables, as in a star schema. VECTOR GROUP BY aggregation uses the infrastructure related to parallel query (PQ) processing, and blends it with CPU-efficient algorithms to maximize the performance and effectiveness of the initial aggregation performed before redistributing fact data.
See "In-Memory Aggregation".
SQL Monitor support for adaptive plans
SQL Monitor supports adaptive plans in the following ways:
Indicates whether a plan is adaptive, and show its current status: resolving or resolved.
Provides a list that enables you to select the current, full, or final plans
See "Adaptive Plans" to learn more about adaptive plans, and "Reporting on Database Operations Using SQL Monitor" to learn more about SQL Monitor.
Oracle Database SQL Tuning Guide for Oracle Database 12c Release 1 (12.1) has the following changes.
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.
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 releases earlier than Oracle Database 12c, 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 releases earlier than Oracle Database 12c, 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 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.
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.
Automatic column group creation
With column group statistics, the database gathers optimizer statistics on a group of columns treated as a unit. Starting in Oracle Database 12c, the 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.
Online statistics gathering for bulk loads
A bulk load is a CREATE TABLE AS SELECT or INSERT INTO ... SELECT operation. In releases earlier than Oracle Database 12c, 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.
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 releases earlier than Oracle Database 12c, 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 7-7 to learn about the CUBE JOIN operation.
The following features are deprecated in this release, and may be desupported in a future release:
Stored outlines
See Managing SQL Plan Baselines for information about alternatives.
The SIMILAR value for the CURSOR_SHARING initialization parameter
This value is deprecated. Use FORCE instead.
Some features previously described in this document are desupported in Oracle Database 12c. See Oracle Database Upgrade Guide for a list of desupported features.
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