Oracle Database Advisors

Most Oracle database users know that Oracle Database provides robust support for tuning tasks and have heard about SQL Tuning Advisor or AWR. In addition, Oracle Database offers a suite of advisors and tools for analyzing various aspects, including access structures, security configurations, upgrade considerations, segment compression and more. These tools lay the foundation for automation within the Oracle Database, aiding DBAs and database developers in their work. Conversely, if you’re seeking automated management, choose the Oracle Autonomous Database (short ADB) offering, which is fast deployed, automated, optimized for all workloads and data types and supports multi-cloud. (For more information on Oracle Autonomous Database please refer to Oracle Autonomous Database). 

As the Oracle database and its offerings evolve, new advisors and automation are continually introduced. When Oracle database 23ai was released in May 2024, I reviewed my postings and decided to update this posting. The updated version also incorporates reviewers’ feedback and reflects the latest updates.

As in prior versions, this post provides a concise, up-to-date overview and descriptions of the most common tools, including links to documentation, websites, and notes. But what are Oracle’s advisors?  Advisors are defined by certain characteristics: most are available without additional installation and can be used immediately, while others can be separately loaded or activated via a console or service. Some are even integrated into tools such as Oracle Enterprise Manager Cloud Control or SQL Developer or are made available via PL/SQL packages, initialization parameters or corresponding v$ views. Understanding how to use these advisors and what advice they offer is crucial.

The following table presents an overview of common advisors as of June 2024. It is not exhaustive but highlights some of the best-known advisors and analyzers. Links offer brief descriptions and pointers to documentation or relevant pages and notes. 
Note: Oracle Database 23ai changes are marked with (*). 

Please use Database Licensing Information User Manual for detailed and up-to-date licensing information for on-premises and cloud environments or the Oracle Database Features and Licensing application. Concerning the management packs offered by Enterprise Manager in support of Oracle Database please use Licensing Information User Manual Enterprise Database Management.

—

To get an idea about the functionality the following list (in alphabetical order) provides a short description of each advisor and the corresponding documentation links.

Automatic Database Diagnostic Advisor (ADDM) diagnoses database performance problems via a regular analysis of the Automatic Workload Repository (AWR) data. ADDM can identify the root cause of performance problems, provide recommendations to fix the issues, and should be the first place to look for improvements in most cases (see  ADDM Data). 

ADDM Spotlight is available in the Oracle Enterprise Manager interface (starting with Oracle Enterprise Manager 13c Release 5 Update 13 (13.5.0.13)) and visualizes the performance problems reported by ADDM that were executed during a period. It highlights the findings with the biggest performance impacts and makes recommendations on how to fix them. In addition, it shows database parameter values as well as changes to these during the analysis period. The ADDM Spotlight information is also shown on the Database home page, including a summary of ADDM findings over the last day and a link to drill down to the ADDM Spotlight page (see documentation here).

Autonomous Health Framework (AHF) addresses availability and performance issues. AHF components include Cluster Health Monitor, Cluster Verification Utility, ORAchk/EXAchk, Quality of Service Management, Hang Manager, Memory Guard, Cluster Health Advisor, and Trace File Analyzer. Oracle releases new versions of Oracle Autonomous Health Framework several times a year. These new releases include new features and bug fixes. Ensure that you get the latest version from My Oracle Support document 2550798.1.

Automatic Indexing automatically creates and drops indexes in a database based on the changes in application workload, thus improving database performance. The automatically managed indexes are known as auto indexes (see Managing Automatic Indexes)

Automatic SQL Plan Management (SPM) detects plan performance regressions and repairs them automatically. When a SQL statement is executed, and the execution plan is new, then after execution the performance of the SQL statement is compared with the lowest cost plan found in the Automatic SQL Tuning Set (ASTS). With Oracle Database 23ai there are two modes of plan performance verifications: background-verification mode and real-time verification mode (*). More information can be found in Configuring Automatic SQL Plan Management.

AutoUpgrade utility identifies issues before upgrades, performs pre- and post-upgrade actions, deploys upgrades, performs post-upgrade actions, and starts the upgraded Oracle Database. Download the most recent version of the AutoUpgrade Utility from My Oracle Support Document 2485457.1, and use autoupgrade.jar to prepare for and deploy your upgrade. It is designed to automate the upgrade process, both before starting upgrades, during upgrade deployments, and post-upgrade checks and configuration migration (see also Database Upgrade Guide).

Bigfile Tablespace Shrink Analysis determines the potential of a shrink operation by running it in analyze mode first. The result of this analysis contains useful information including a list of unsupported objects, a list of movable objects, the total size of movable objects in the tablespace, and the suggested target size for the tablespace. After analyzing a tablespace you can shrink it. More information can be found in the Database Administrator’s Guide.

Cloud Premigration Advisor Tool (CPAT) analyzes database metadata in an Oracle Database and provides information to assist you in moving data to Oracle Autonomous Database in Oracle Cloud. Cloud Premigration Advisor Tool (CPAT) helps to plan successful migrations to Oracle Databases in the Oracle Cloud or on-premises. It analyzes the compatibility of the source database with your database target and chosen migration method and suggests a course of action for potential incompatibilities. It does not perform the actual migration. You use that information as part of your migration plan. More information can be found in the Utilities guide.

Compression Advisor helps to choose the right segment compression. It calculates the compression rate per table or partition for different compression algorithms in advance. It can even calculate EHCC (Exadata Hybrid Columnar Compression) and the compression of tables for the in-memory column store. On the other hand, it can also be used to diagnose the compression type that has been set. Information about the PL/SQL package DBMS_COMPRESSION can be found in the documentation.

Features and Licensing application provides information about Oracle Database features and their licensing requirements. Use the Features report to view database features introduced since Oracle Database 11g Release 2 (11.2). You can easily search, filter, and compare feature availability across Oracle Database releases. You can also view licensing information for each feature. Use the Licensing report to view the information available in Oracle Database Licensing Information User Manual, organized so you can easily search, filter, and compare license requirements across Oracle Database offerings. For each license, you an view relevant features on the Features report.

JSON Pre-Migration checks whether existing data stored as JSON text can be migrated to the JSON data type. The PL/SQL procedure DBMS_JSON.json_type_convertible_check checks whether a given column of textual JSON data can be converted to JSON data type. The result of checking is stored in the table named by the third parameter of the procedure. This table is created if it does not yet exist. More information can be found here. 

LOB Deduplication ratio function estimates the storage space that you can save by enabling the deduplication feature for an existing SecureFile LOB and returns the deduplication ratio.The deduplication ratio is estimated for the number of rows in the LOB column that you specify. More information can be found in the documentation of DBMS_LOB.

Oracle Database In-Memory Feature Support provides two advisors: In-Memory Eligibility Test (from 19.20) and the In-Memory Advisor (in 23ai). Both are supported by built-in PL/SQL Packages. The In-Memory Eligibility Test makes it very simple to determine whether there is a significant amount of analytic workload. For the ones that do, you can then consider running the Oracle Database In-Memory Advisor (in 23ai). The In-Memory Advisor differentiates estimates of analytic processing performance improvement factors based on data from AWR, ASH, and heat map statistics. More information can be found in the Database In-Memory Guide.

Oracle Database Security Assessment Tool (DBSAT) can assess the security configuration of Oracle Databases and profile the security and compliance posture of databases by evaluating the current state, including configuration, discovering sensitive data, and more. Information about download, documentation, livelabs etc. can be found here. With Oracle Enterprise Manager (OEM) version 13.5, this tool is integrated into OEM, in the Compliance Standards Library, and can inspect any databases registered to the Enterprise Manager, generate reports, and detect possible security drifts of the inspected databases.

Oracle Data Safe is an integrated cloud service that delivers capabilities to protect sensitive and regulated data in Oracle Cloud databases, among others. Functions include assessments for general security evaluation, user configuration evaluation, sensitive data discovery, data masking and activity checking. Oracle Data Safe is a unified control centre for managing database security in the Oracle Cloud, both for your Oracle Cloud Databases as well as for your Oracle OnPremises Databases. For more information, see Data Safe documentation. 

Oracle Database Dictionary Check is a read-only and lightweight PL/SQL package procedure that helps you identify Oracle Database dictionary inconsistencies that are manifested in unexpected entries in the Oracle Database dictionary tables or invalid references between dictionary tables. It can assist you in identifying such inconsistencies and in some cases provides guided remediation to resolve the problem and avoid such database failures. More information on that can be found in the documentation of DBMS_DICTIONARY_CHECK.

Oracle Release Analyzer Diff Utility (ORAdiff) allows you to compare two database releases to each other – with or without patch bundles on top. It displays the differences such as “new tables”, “added parameters”, “changed columns”, “removed privileges” and much more. ORAdiff search can tell you when a parameter was added and which files changed in your Oracle Home. Use this link to start with it.

Database Replay can record (including capture) an entire database workload and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. It can be used either graphically via the Enterprise Manager or PL/SQL packages DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY. For more information, see Part II Database Replay in the Testing Guide.

PL/SQL Hierarchical Profiler identifies performance bottlenecks in PL/SQL applications. The report provides information on the execution times of the parent programs and proportionally on the associated subprograms. SQL and PL/SQL execution times are recorded separately. No special preparation of source code or compile time is required. If you suspect PL/SQL bottlenecks you should consult the PL/SQL Advisor in any case. More information can be found in the Database Development Guide under Using the PL/SQL Hierarchical Profiler.

Segment Advisor finds segments that use too much space. The recommendations include the appropriate commands to free up space. The Segment Advisor runs automatically in the Automatic Maintenance Task, but can also be started independently manually using the DBMS_ADVISOR package. For more information, see The Segment Advisor in the Database Administrator’s Guide.

SQL Access Advisor provides information and recommendations about access structures (missing but also redundant ones) – such as bitmap indexes, function-based indexes, B*tree indexes, partitions, materialized views, etc., see SQL Access Advisor in SQL Tuning Guide.

SQL Performance Analyzer performs a detailed statement analysis of an SQL workload (SQL Tuning Sets) and provides an overview of the performance differences and plans of the individual statements. It can be used either graphically via Enterprise Manager or the DBMS_SQLPA package. Oracle Enterprise Manager Cloud Control also contains the SQL Performance Analyzer Quick Check (SPA Quick Check for short) function. This can be used on some Enterprise Manager pages to check the effects of certain changes on the database load in advance. Information about SPA can be found in the Testing Guide under Introduction to SQL Performance Analyzer.

SQL Repair Advisor analyzes SQL statements with critical errors and tries to fix the problem with a SQL patch, which suggests an alternative plan to the optimizer. The required interface is DBMS_SQLDIAG or Oracle Enterprise Manager Cloud Control. Information about it can be found in the Database Administrator’s Guide under About the SQL Repair Advisor.

SQL Transpiler automatically attempts to convert any PL/SQL function called from a SQL statement into a semantically equivalent SQL expression. Transpiling PL/SQL functions into SQL increases performance for new and existing programs and functions. When a transpiled PL/SQL function is invoked, the per-row cost of executing the transpiled code within SQL is much lower than switching from the SQL runtime to the PL/SQL runtime to execute the original PL/SQL code. If the transpiler cannot convert a PL/SQL function to SQL, the execution of the function falls back to the PL/SQL runtime. Not all PL/SQL constructs are supported by the transpiler. More information on that can be found in SQL Transpiler.

SQL Tuning Advisor is a built-in tools that provides SQL tuning recommendations. It performs an Optimizer analysis run and provides recommendations in the categories: Statistics, SQL Profiles, Access Structures, SQL Plan Management or Statement Formulation. It can run automatically in the Maintenance Task or be used on demand – graphically via Cloud Control or manually with the DBMS_SQLTUNE package. Information about it can be found in the SQL Tuning Guide under About SQL Tuning Advisor.

(Optimizer) Statistics Advisor analyzes the statistics management of the Oracle database. It checks how statistics are generated, validates their quality and checks the status of maintenance tasks. It checks how statistics are generated, validates their quality and checks the status of maintenance tasks. Optimizer Statistics Advisor can be used in automated mode in the Maintenance Task or manually via the DBMS_STATS.CREATE_ADVISOR_TASK function. For more information, see Analyzing Statistics Using Optimizer Statistics Advisor in the SQL Tuning Guide.

Undo Advisor helps to set an appropriate size for UNDO tablespaces (with fixed size) for long-running queries and flashback queries. You can use the Undo Advisor via Oracle Enterprise Manager Database Express (EM Express) or the DBMS_ADVISOR package. For more information, see Sizing a Fixed-Size Undo Tablespace in the Database Administrator’s Guide.