This preface describes the most important changes in Oracle Database SQL Tuning Guide.
This preface contains the following topics:
Changes in Oracle Database Release 19c, Version 19.1
Oracle Database SQL Tuning Guide for Oracle Database release 19c, version 19.1 has the following changes.
The following features are new in this release:
Automatic resolution of SQL plan regressions
SQL plan management searches for SQL statements in Automatic Workload Repository (AWR). Prioritizing by highest load, SPM Evolve Advisor looks for alternative plans in all available sources, adding better performing plans to the SQL plan baseline automatically.
Reporting on hint usage
Oracle Database includes a hint usage reporting mechanism that reports whether hints were used during plan generation. In some cases, the report explains why a hint was not used, for example, a syntax error or conflict between hints. You can generate hint usage reports with the standard
See "Reporting on Hints".
Plan comparison function
DBMS_XPLAN.COMPARE_PLANSfunction takes a reference plan and a list of test plans and highlights the differences between them. Users can triage plan reproducibility issues by identifying the source of differences.
See "Comparing Execution Plans".
Oracle Database automatically gathers real-time statistics during conventional DML operations. These statistics augment the standard statistics gathered by
See "Real-Time Statistics".
High-frequency automatic optimizer statistics collection
This lightweight task periodically gathers statistics for stale objects. The default interval is 15 minutes. In contrast to the automated statistics collection job, the high-frequency task does not perform actions such as purging statistics for non-existent objects or invoking Optimizer Statistics Advisor.
Statistics maintenance enhancements
Partition move, merge, and coalesce operations maintain both global and partition-level statistics.
See "Online Statistics Gathering".
Real-time SQL monitoring for developers
Database users who do not have the
SELECT_CATALOG_ROLEcan generate and view SQL Monitor reports for their own SQL statements, including execution plans and performance metrics.
Statistics-based query transformation
In some cases, the database can satisfy a simple aggregation query by accessing table statistics rather than the table itself, thereby significantly reducing query response times.
Quarantine for runaway SQL statements
Oracle Database automatically quarantines SQL statements terminated by Oracle Database Resource Manager (Resource Manager) for breaking resource limits. By putting plans on a “blacklist,” the database prevents resource-hogging statements from executing again.
See "About Quarantined SQL Plans".
An automatic background task monitors system workloads, and then creates and maintains indexes suitable for the statements in the workload. The database validates the performance effects of the indexes and keeps or alters its decisions constantly to maximize performance. For example, the database might create an index on columns used in a statement predicate when the index improves query performance significantly.
See "Automatic Indexing".
Oracle Database Licensing Information User Manual for details on which features are supported for different editions and services
This topic describes additional changes in the release.
The chapter on SQL Test Case Builder is now merged with the diagnosability content in Oracle Database Administrator’s Guide.
Changes in Oracle Database Release 18c, Version 18.1
Oracle Database SQL Tuning Guide for Oracle Database release 18c, version 18.1 has the following changes.
The following features are new in this release:
Private temporary tables
Private temporary tables are temporary database objects that are automatically dropped at the end of a transaction or a session. A private temporary table is stored in memory and is visible only to the session that created it. A private temporary table confines the scope of a temporary table to a session or a transaction, thus providing more flexibility in application coding, leading to easier code maintenance and a better ready-to-use functionality.
Approximate Top-N Query Processing
To obtain “top n” query results much faster than traditional queries, use the
APPROX_COUNTSQL functions with
SQL Tuning Advisor enhancements for Oracle Exadata Database Machine
SQL Tuning Advisor can recommend an Exadata-aware SQL profile. On Oracle Exadata Database Machine, the cost of smart scans depends on the system statistics I/O seek time (
ioseektim), multiblock read count (
mbrc), and I/O transfer speed (
iotfrspeed). The values of these statistics usually differ on Exadata and can thus influence the choice of plan. If system statistics are stale, and if gathering them improves performance, then SQL Tuning Advisor recommends accepting an Exadata-aware SQL profile.
New package for managing SQL tuning sets
You can use
DBMS_SQLTUNEto create, modify, drop, and perform all other SQL tuning set operations.
Scalable sequences alleviate index leaf block contention when loading data into tables that use sequence values as keys.
OPTIMIZER_ADAPTIVE_STATISTICSfrom performance feedback
Unlike in previous releases, setting the
OPTIMIZER_ADAPTIVE_STATISTICSinitialization parameter to
FALSEnow has no effect on performance feedback.