Optimizer in Oracle Database 19c

 

The Query Optimizer in Oracle Database 19c What’s New?

Reporting on Hint Usage

• It is an issue to verify whether hints added to SQL statements are actually used

• To make it easier, there is a new section in the output generated by the DBMS_XPLAN.DISPLAY* functions

• The new section (Hint Report) reports hints into two categories

->Valid (used)

-> Invalid (either unused, unresolved or having syntax error)

• For invalid hints, additional information explaining the issue might be provided

 

Execution Plan Comparison

• When dealing with the performance of SQL statements, it is rather usual to compare execution plans

• The new function DBMS_XPLAN.COMPARE_PLANS implements a logical (i.e. not line by line) comparison of two or more execution plans

• The execution plans can be stored in either a plan table, the cursor cache, the AWR, a STS, a SQL baseline, a SQL profile or an advisor task

• The report can be generated in either text, HTML or XML format

• It does not work in Standard Edition (bug 30488154) • An ORA-13631 is raised

 

Online Statistics Gathering Enhancements

• Up to and including 18c, bulk load operations in empty segments do not gather index statistics

• As of 19c, according to the documentation, this restriction no longer exists

• In practice, because of a bug, it is only partially the case

• Statistics on non-unique indexes are not gathered

 

Online Statistics Gathering Enhancements

• Up to and including 18c, bulk load operations in empty segments do not gather index statistics

• As of 19c, according to the documentation, this restriction no longer exists

• In practice, because of a bug, it is only partially the case

• Statistics on non-unique indexes are not gathered

 

Statistics Maintenance Enhancements

• Up to and including 18c, ALTER TABLE operations that move, merge and coalesce partitions do not maintain global and partition-level statistics

• As of 19c, according to the documentation, this restriction no longer exists

• Histograms are not maintained

• In practice, because of bugs, it is only partially the case

• Most global statistics are not (correctly) maintained

• Partition-level statistics are maintained

 

Real-Time SQL Monitoring for Developers

• All database users, without specific privileges, can generate real-time monitoring reports about SQL statements they executed

• The API to generate the reports is the regular one

SELECT dbms_sql_monitor.report_sql_monitor(sql_id => '...', type => 'active') FROM dual

• There are new (undocumented) views to see which statements were monitored and how they were executed

• V$ALL_SQL_MONITOR

• V$ALL_SQL_PLAN_MONITOR

• V$ALL_SQL_PLAN

• V$ALL_ACTIVE_SESSION_HISTORY

 

Automatic Resolution of Plan Regressions

• Up to and including 18c, by default, the SPM Evolve Advisor only considers SQL statements already loaded in SQL plan baselines

• As of 19c, the advisor can also consider other SQL statements

• Only resource-intensive SQL statements with several execution plans

• New SQL plan baselines can be automatically created

• To control the feature, use DBMS_SPM.SET_EVOLVE_TASK_PARAMETER

• To enable it set ALTERNATE_PLAN_BASELINE to AUTO (default in 19.3)

• To disable it set ALTERNATE_PLAN_BASELINE to EXISTING (default in 19.4+)

• 19c also improves the selection of the execution plans stored into CC/AWR/STS

 

 

High-Frequency SPM Evolve Advisor Task

• By default, the SPM Evolve Advisor runs daily in the maintenance window

• In 19c it can be configured to also run outside of the maintenance window

• To control it, the DBMS_SPM.CONFIGURE procedure supports a new parameter

• AUTO_SPM_EVOLVE_TASK (OFF, ON, AUTO)

• In 19c AUTO is equivalent to ON

• When enabled, it runs every hour for no longer than 30 minutes

 

 

Real-Time Statistics

• They do not replace object statistics, their purpose is to augment them between two gatherings

• They are limited to specific object statistics and to INSERT/UPDATE/MERGE

• Number of rows and blocks at the table level

• Low/high value at the column level • They are gathered and used by default

• The NO_GATHER_OPTIMIZER_STATISTICS hint prevents their gathering

• They are visible in *_TAB_STATISTICS and *_TAB_COL_STATISTICS

• NOTES = STATS_ON_CONVENTIONAL_DML

• Execution plans based on them are marked (V$SQL_PLAN.OTHER_XML)

 

High-Frequency Automatic Statistics Collection

• By default, the automatic statistics collection task runs daily in the maintenance window

• In 19c it can be configured to also run outside of the maintenance window

• To control it, the DBMS_STATS.SET_GLOBAL_PREFS procedure supports three new parameter

• AUTO_TASK_STATUS (OFF, ON)

• AUTO_TASK_INTERVAL (in seconds, default 900, minimum 60)

• AUTO_TASK_MAX_RUN_TIME (in seconds, max/default 3600)

• DBA_AUTO_STAT_EXECUTIONS provides information about the runs

 

 

 

Quarantine for Runaway SQL Statements

• The Resource Manager can be instructed to terminate SQL statements that either run for too long or consume excessive resources

• When terminating a SQL statement, 19c automatically creates a quarantine configuration that prevents the execution plan to run

• Instead, an ORA-56955 (quarantined plan used) is raised

• Quarantine configurations

• Can be managed and manually created with DBMS_SQLQ

• Are visible in DBA_SQL_QUARANTINE

• To be used do not necessarily require the activation of a Resource Manager plan

• Cursors using them are marked (V$SQL.SQL_QUARANTINE) and externalize the number of avoided executions (V$SQL.AVOIDED_EXECUTIONS)

Automatic Indexing

• Brand new, game changer feature

• Expert system that automatically manages performance-related indexes

• Requires no external inputs

• Unsupervised • 24x7

• Automatically adapts to changes

• For identification and validation purposes, it uses an automatically managed SQL tuning set

Automatic Indexing – Handling

• By default, it is disabled

• It can be enabled either in reporting mode or “fully”

dbms_auto_index.configure('AUTO_INDEX_MODE', 'REPORT ONLY')

dbms_auto_index.configure('AUTO_INDEX_MODE', 'IMPLEMENT')

 

• Few parameters exist

-> Schema inclusion/exclusion lists

-> Retention of unused indexes and logs

-> Storage (tablespace, space budget, compression)

• When enabled, it runs every 15 minutes

-> DBA_AUTO_INDEX_EXECUTIONS

-> DBMS_AUTO_INDEX.REPORT_(LAST_) ACTIVITY

Summary

• Licensing limitations make many (interesting) features not generally available

• Small, but useful new features or enhancements

• Finally DBMS_XPLAN reports hint usage

• Online statistics gathering (index statistics + partition maintenance operation)

• Real-time SQL monitoring available to everyone

• Quarantine for runaway SQL statements

• New concepts; real-world experience is needed to critically judge them

• Automatic indexing could be a game changer

• Real-time statistics

• Automatic resolution of plan regressions

• Execution plan comparison

• Minor enhancements

• High-frequency automatic statistics collection and SPM Evolve Advisor task