Jonas Mason, Senior Consultant
In my last blog, Diagnostic and Tuning Pack Features: SQL Tuning Sets, I covered why an organization might be a candidate for Diagnostic and Tuning Pack features and what kind of value was gained from using these features. I also covered the basic task of creating and populating a SQL Tuning Set.
In this blog I will cover two SQL Tuning Advisor scenarios, including:
- Ad hoc analysis of SQL Statements in Top Activity
- Analysis of a SQL Tuning Set
I will also discuss the types of recommendations made by the SQL Tuning Advisor in these two scenarios.
SQL Tuning Advisor for SQL Identified in Top Activity
In this case, we bypass the creation of a SQL Tuning set altogether, and execute the SQL Tuning Advisor against SQL statements identified in the Top Activity view of the instance. If the DBA is reviewing real time performance for an instance in “Top Activity,” they can see which SQL statements are contributing the most to consuming db_time and then analyze them in real time.
CPU and I/O resources are consumed when performing SQL Tuning Advisor evaluations, though the impact to other processes is minimal and tolerable on most systems that are not under significant load. If in doubt, schedule appropriately. In this case, we are running Tuning Advisor on only one to ten statements. See figure 1 below for details.
Figure 1: Selecting SQL Tuning Advisor from Instance Top Activity View
In this time frame, the sql_id db3pckcggq8y8 is consuming 78.71% of current activity for all SQL on this instance. This could be due to 1) an expensive statement, and/or 2) the frequency of execution of the statement.
Figure 2: Check Box to Select SQL IDs to Tune with the SQL Tuning Advisor
Once you select the sql_id and hit ‘Go’, as shown in Figure 2 above, you will be brought to the following page, where you can schedule the SQL Tuning Advisor to occur immediately, or later, as shown in Figure 3 below. The default Total Time Limit of 30 minutes and Comprehensive Scope of Analysis, with a time limit of five minutes per statement is usually sufficient.
Figure 3: Schedule SQL Tuning Advisor to Execute Immediately or Later
After submitting the SQL Tuning Advisor job, you will see the following status as the job analyzes the SQL; see Figure 4:
Figure 4: SQL Tuning Advisor Job Progress
The results of the job, if any, will recommend either 1) one or more new indexes, 2) a SQL Profile, or 3) a restructure of the SQL statement based on the inefficiencies noted, as seen in Figure 5 below.
Index Recommendation
Figure 5: View Recommendations of Index, SQL Profiles or SQL Restructure
In this case, one of the recommendations is a new compound index with a functional (UPPER) component on the two columns for a table referenced in the query. This recommendation indicates that the use of the UPPER function in the SQL reviewed prevents the usage of an index that might otherwise exist for this column. Introducing a functional index enables the use of an index in this example. Certainly removing the need for an UPPER function on the column in the SQL statement, and creating a standard index on the column, is a possibility worth considering. At least there is a data point here to be considered by the DBA and Developer. Either the code changes, or we test out what creating a functional index does to performance.
By navigating to the right of this screen, shown more completely below, you can compare the explain plan cost difference between what is currently running and what would change with the implementation of 1) an index, 2) a SQL Profile, or 3) a SQL statement restructure. Figures 6 through 8 below demonstrate the possible cost improvements discovered by the SQL Tuning Advisor.
Figure 6: Compare Possible Explain Plan Improvement
Figure 7: Original Explain Plan Cost without Index
Figure 8: New Explain Plan Cost with Index Recommended by Tuning Advisor
Given the dramatic reduction in I/O cost (6,093 to 4) and CPU cost (440,051,008 to 30,436), the subsequent drop in total cost (6,105 to 4) is significant. A review of possible index overhead should always be considered prior to implementing an index. If significant and discrete DML hits the table repeatedly with high frequency from many sessions, the impact of a new index needs to be considered. If inserts on the table are infrequent, the trade off gained in improved select execution times and resource utilization reduction is going to make this attractive. Also consider the storage consumed by the index. Some environments may be constrained by lack of storage. Rollback in this scenario would involve dropping the newly created index.
SQL Profile Recommendation
A SQL Profile contains additional performance statistics and hints generated from the SQL Tuning Advisor and can be applied to a SQL ID after running the SQL Tuning Advisor. Here, in Figure 9, we see that a SQL Profile can improve performance. The benefit of implementing a SQL Profile is that no DDL is required. Enabling and disabling a SQL Profile can be performed by the DBA easily.
Figure 9: Review SQL Profile Explain Plan
When we select the ‘Compare Explain Plans’ option (not visible to right of ‘New Explain Plan’ option), we can see the difference in explain plan performance with the implementation of the SQL Profile:
Figure 10: View Original Explain Plan
Figure 11: New Explain Plan with SQL Profile Implemented
In this case, the explain plan cost drops from 10,317 to 1,436 with the implementation of the SQL Profile. Since an Index is also recommended to improve performance, and shows a potential benefit that is 99.61% vs. 86.08% better, review the explain plan difference with the Index implemented. Determine whether the less intrusive SQL Profile option is better than introducing an Index for reasons other than performance. Then test what applying both the SQL Profile, and introducing an Index together, do to explain plan costs and actual execution time differences in a QA environment.
Executing SQL Tuning Advisor on a SQL Tuning Set
In last week’s blog, I walked through the basic task of creating a SQL Tuning Set. In this section, I will cover executing the SQL Tuning Advisor against a SQL Tuning Set. If your SQL Tuning set contains thousands of SQL statements, of course you should take advantage of idle periods on your system to schedule such a job. If these SQL Tuning Sets are especially large, and were captured in Production prior to the database being cloned to a DEV or TEST instance, run this analysis in your newly cloned DEV or TEST instance instead. The SQL Tuning Sets are stored within the database, and not in Cloud Control.
Figure 12: Navigate to SQL Tuning Sets to Schedule SQL Tuning Advisor
Figure 13: Specify SQL Tuning Advisor Job Name, Scope and Start Time
In Figure 13, specify the length of time to execute; in this case two hours might not be enough, with 6,704 SQL statements. Schedule the appropriate time to begin execution of this SQL Tuning Advisor job. I typically select ‘Comprehensive’ with the default Time Limit Per Statement of five minutes.
Summary
The SQL Tuning Advisor can save a busy DBA time when confronted with long running SQL statements and busy servers. Prior execution times and load characteristics for SQL statements can be compared to these same statistics after tuning efforts have taken place. With this before and after empirical evidence, the DBA can demonstrate how end users benefited from improved execution times. The DBA should also be able to show a corresponding drop in CPU, IOPS, and Disk Throughput utilization.
In my next blog, I will cover the Access Advisor, which is much like the SQL Tuning Advisor, but performs a deeper dive into possible Access Paths that may help your SQL execute faster.
