|Oracle® Database SQL Tuning Guide
12c Release 1 (12.1)
|PDF · Mobi · ePub|
This preface contains:
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
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:
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".
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.
SQL plan directives
In previous releases, the database stored compilation and execution statistics in the shared SQL area, which is nonpersistent. Starting in Oracle Database 12c Release 1 (12.1), 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.
Dynamic statistics enhancements
In previous releases, dynamic statistics (previously called dynamic sampling) was only used 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 unpopular 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.
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.
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.
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 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.
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.
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 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.
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.
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.
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.
The following features are deprecated in this release, and may be desupported in a future release:
Some features previously described in this document are desupported in Oracle Database 12c Release 1. 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 Release 1:
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