Available now: SQL Tuning Advisor for Oracle Cloud Databases

November 29, 2021 | 4 minute read
Sriram Vrinda
Director of Product Management
Text Size 100%:

This blog assumes the reader has familiarity with using the Oracle Cloud Database Management service, Oracle Databases and Structured Query Language (SQL).

We are excited to share that SQL Tuning Advisor is now available in Oracle Cloud Infrastructure (OCI) Database Management service for Oracle Databases in the Oracle Cloud and on-premises. This feature allows you to tune top SQLs as seen in Active Session History (ASH) Analytics feature of Performance Hub. 

SQL Tuning for maximizing Oracle Database performance 

Tuning usually implies fixing performance problems. However, tuning should be part of the life cycle of an application—through the analysis, design, coding, production, and maintenance stages. 

An important facet of database system performance tuning is the tuning of SQL statements. SQL tuning involves three basic steps:

•    Identifying high load or top SQL statements that are responsible for a large share of the application workload and system resources, by reviewing past SQL execution history from the system.
•    Verifying that the execution plans produced by the query optimizer for the statements perform reasonably.
•    Implementing corrective actions to generate better execution plans for poorly performing SQL statements.

SQL Tuning Advisor Tab
Figure 1:  SQL Tuning Advisor Summary

Automatic SQL Tuning and Oracle query optimizer modes for Oracle Database:

Normal mode includes a reasonable execution plan with strict time constraints

In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan.

Tuning mode has additional analysis checks to create a superior execution plan 

In tuning mode, the optimizer performs additional analysis to check whether the execution plan produced under normal mode can be further improved. The output of the query optimizer is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly superior plan. When called under the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer. The tuning performed by the Automatic Tuning Optimizer is called Automatic SQL Tuning.

Oracle SQL Tuning Advisor provides options to manage the scope and duration of tuning task

The scope of a tuning task can be set to limited or comprehensive. 

If the limited option is chosen, the SQL Tuning Advisor produces recommendations based on statistics checks, access path analysis, and SQL structure analysis. SQL Profile recommendations are not generated.

If the comprehensive option is selected, the SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL Profiling. With the comprehensive option, you can also specify a time limit for the tuning task, which by default is 30 minutes.

Figure 2:  Run SQL Tuning Advisor from Performance Hub

Advice output of Oracle SQL Tuning Advisor are recommendations for improvements

After analyzing the SQL statements, SQL Tuning Advisor publishes recommendations. Specifically, SQL Tuning Advisor produces the following types of output:
•    Advice on optimizing the execution plan
•    The rationale for the proposed optimization
•    Estimated performance benefit
•    SQL statement to implement the advice

Figure 3:  SQL Tuning Advisor recommendations   

 

Identifying and tuning high-load SQL statements takes time and is challenging even for an expert. SQL Tuning Advisor uses the optimizer to tune SQL for you.

When sub optimally performing SQL statements occur on a production database, developers may not want to investigate and tune directly on the production database. The DBA can transport the problematic SQL statements to a test database where the developers can safely analyze and tune them.

SQL Tuning Advisor is available to use with Oracle Cloud Infrastructure Database Management service at an extra cost. The feature is accessible through the Console CLI, and REST API and is already available in all commercial regions, with Government regions coming soon. 

Sign up for an Oracle Cloud Infrastructure trial account and for more information about this functionality and how you can use it, review the documentation and try it out for yourself.

Resources

Database Management Videos

Database Management LiveLabs

Database Management Blogs

Sriram Vrinda

Director of Product Management

Sriram Vrinda is an experienced Product Manager with a demonstrated history of working in the information technology and services industry. Strong product management professional skilled in Oracle Database, Autonomous Databases, MySQL Databases, IT Service Management, Solution Architect, and Pre-sales. He has helped various customers with Oracle solutions specifically around performance, availability, and scalability aspects for about 20 years.


Previous Post

Discover targets in an OCI-based Enterprise Manager instance

Next Post


Announcing the general availability of Oracle Cloud Infrastructure Operations Insights AWR Hub

Sriram Vrinda | 4 min read