SQL Tuning Advisor

Hey, don't think I'm mentally lazy, but the less I have to think about something, the faster it gets done. My unit's "TODO" list rivals "War and Peace". If any tool can help us do ANYTHING faster, I want to use it. There will still be plenty for us to learn. Once my Enterprise Manager blogs are complete, I'll be blogging about our experiences with Oracle BPEL, Collaboration Suite, Application Server and Oracle's Internet Directory (OID). This group of products has a steep learning curve (kind of like MT. Everest) and would present a challenge to any unit given the task of supporting them.

I'll be truthful; I was a lousy developer, that's why I became a DBA. All those "IF-THEN-ELSE-OTHERWISE-GOTO-EXIT" routines drove me nuts. But over the years, I have come to understand the importance of knowing the data (not just the database) and the SQL language that accesses it. I became a strong SQL tuner because my (no, make that OUR) profession requires it. In the near future I'll be showing you how to use all of these new-fangled tools to tune SQL. I'll begin with a blog on overall SQL tuning from a DBA perspective.

SQL Tuning Advisor Overview
OK, so what is this first generation SQL Tuning tool all about? The SQL Tuning Advisor is an intelligent analyzer that we can use to monitor and tune SQL statements. It provides recommendations and includes information justifying why it generated those recommendations. This prevents the advisor from becoming a "black hole" that pumps out advice without supporting information. The recommendations may include collecting statistics on objects, new index creation, restructuring the SQL statement or the creation of a SQL profile to create a more optimal access path.

The SQL Tuning Advisor's SQL rewrite feature uses the Oracle10G cost based optimizer to tune poorly performing SQL. It saves the access path in a new Oracle object called a SQL profile, which is then stored in the database. Each time the poorly performing SQL statement executes, the stored access path is used in its place.

The optimizer's job is to create the most high performance access path in the shortest time possible. It's easy to understand how these two objectives can be viewed as "mutually exclusive." In a perfect world, you would give the optimizer as much time as it needs to create the most optimal access path. In the real world, you need to get the statements executing as quickly as possible. That includes optimization. Since the SQL Tuning Advisor does not have time constraints, the tool can take as much time as necessary to optimize the statement. The SQL Tuning Advisor Home Page provides a drop down menu that allows the administrator to "throttle" the amount of time the utility spends optimizing the statement.

The SQL Tuning Advisor will be especially beneficial to administrators who support third-party applications. Before the advent of stored outlines (predecessor to SQL profiles), once the administrator identified the canned application's poorly performing SQL, the third-party vendor was contacted to change the SQL code and the changed code then implemented in test and finally in production to effect the tuning change. Anyone who has experience with third-party application vendors knows that this is often a time consuming, if not impossible, process. Creating SQL Plans and stored outlines (discussed later) allows the administrator to correct the poorly performing SQL statement immediately without vendor assistance.

SQL Tuning Advisor Demo
The SQL Tuning Advisor can be accessed from many different panels in 10G Enterprise Manager. Take a look at the screen print of the panel that appears when you click on the SQL Tuning Advisor link on the Advisor Central Home Page. You'll notice that the SQL Tuning Advisor can not be called directly from Advisor Central. The utility can only be called from the Top SQL, SQL Tuning Sets, Snapshots and Preserved Snapshot Sets panels.

Top SQL Page
We learned previously that the TOP SQL home page is divided into Spot SQL and Period SQL drilldown panels. The Spot SQL page provides a slider that allows me to choose from 5 minute windows. The Period SQL page also provides a slider but its increments are in 24 hour time periods. I decided to choose the Top SQL Period SQL page for the demo because it would provide me with a larger number of SQL statements to choose from.

Before we continue, take a close look at the page I just mentioned. It should look very close to the Top SQL Period SQL page provided in my blog on the SQL Access Advisor. The two pages are almost mirror images of each other. Which is exactly the way they should be. The only difference is when you navigate to the Top SQL pages from the SQL Tuning Advisor, the panels display a "Run SQL Tuning Advisor" button and when you navigate to them from the SQL Access Advisor they display a "Run SQL Access Advisor" button. Everything else is exactly the same.

After dragging the window to the desired 24 hour time slice, 10G Grid Control displays the SQL for that time period on the lower left side of the panel. The tool allows me to drill down to view the text of the statement and also displays the statement's CPU and Wait times. 10G Grid Control provides us with a couple of different ways to activate the SQL Tuning Advisor from the Top SQL Period SQL page.

• I can select multiple SQL statements by clicking on their associated check boxes (far lower left of screenshot). I then activate the SQL Tuning Advisor by clicking on the "Run SQL Tuning Advisor" button to analyze all of the selected statements at the same time.
• I select the statements the same way as mentioned in the previous bullet. Instead of activating the SQL Tuning Advisor immediately, I store them in a SQL Tuning Set by clicking on the "Create SQL Tuning Set" button. I am then able to use the stored SQL statements as input to the advisors at a later time. For more information on SQL Tuning Sets, please refer to my blog on SQL Tuning Sets.
• I can display the SQL details page by clicking on the text of the SQL statement shown in the SQL Text column. I can then activate the advisor from details page.

Every time 10G Grid Control displays a SQL statement's SQL ID or a snippet of its text, it usually shows them as a link that allows us to drill down to the SQL statement's details. Let's click on the SQL text link and navigate to the SQL statement details page. This page shows the text of the SQL statement and its associated execution plan. 10G Grid Control provides a link to tune the statement immediately using the SQL Tuning Advisor. In addition, the page also provides tabs that allow users to drill down into in-depth performance statistics and historical tuning and execution information.

The Schedule Advisor
I'll click on the "Run SQL Tuning Advisor" button to activate the SQL Tuning Advisor on this single statement. 10G Grid Control then displays the Schedule Advisor page. This page allows users to specify the time the advisor will run and select the scope of the analysis performed.

Analysis Scope
You can choose from limited or comprehensive analysis. The limited option will provide recommendations on statistics collection, new index recommendations and SQL statement restructuring but will not recommend SQL profiles. The comprehensive option provides all recommendations including SQL profiles.

Here's the tradeoff. Look at the text included in the comprehensive option. Pay special attention to the words "may take a long time." I would heed that warning if I were you. Do NOT run an in-depth comprehensive analysis on numerous SQL statements during peak utilization periods. Comprehensive analysis does incur a noticeable overhead on the database. Here are my recommendations:

• Only run the comprehensive option during periods of low database activity.
• If you must run the comprehensive option during peak periods, only run it on a few statements at a time.
• You can run the limited option during periods of peak activity but don't run it on dozens and dozens (and dozens) of statements in one execution. Break them up into groups.
• The best option is to create a SQL Tuning Set, transport the set to a test environment and tune the statements at your leisure. That's what test is for.

I chose the comprehensive option and clicked OK to start the analysis process. 10G Grid Control displays the SQL Tuning Advisor Status page. This page is displayed until the advisor has completed its analysis activities.

Why does the SQL tuning process generate overhead? As stated previously in this blog, the SQL Tuning Advisor does not have optimization time constraints. As a result, the tool can take as much time as necessary to optimize each statement. You'll certainly get an educated decision from the optimizer, but you may impact system performance to get it. The big resource consumer is when you select the comprehensive option to tell the SQL Tuning Advisor to recommend SQL profiles.

SQL Profiles
Don't confuse SQL profiles with stored outlines. Stored outlines, which were introduced in Oracle8i, provide administrators with the capability of "freezing" a SQL statement's access path and storing it in the database. Administrators can use the OEM stored outline editor or hints to change the statement's access path for better performance. The access path to the data then remains constant despite data changes, schema changes, and upgrades of the database and/or application software. To learn more about this popular feature, please turn to my DBAZine article titled "Stored Outlines."

SQL profiles differ from stored outlines in a couple of key areas. Although SQL profiles also change a statement's access path, they don't freeze the exact same access path forever. In addition, a SQL Profile's access path is still dependent upon the data. What you are doing is allowing the optimizer to take its time to make the most educated decision that it can.

Recommendations
After the analysis is complete, 10G Grid Control displays a recommendations page. The series of recommendations are displayed in a tabular format. Each row provides a recommendation category, finding, rationale and percentage of benefit.

If an alternate access path is recommended, a set of glasses is displayed which allows users to view the new explain plan. The recommendation page also provides a button that displays the original explain plan to allow administrators to compare the old access path to the new one. Comparing the new and old explain plans is HIGHLY recommended.

IF YOU HIT THE IMPLEMENT BUTTON ON THE RECOMMENDATIONS PAGE, YOU WILL IMPLEMENT THE RECOMMENDATION. In case of the SQL profile, you will implement it immediately without any additional prompts. I learned this the hard way.

How do you drop a profile that is created automatically by the SQL Tuning Advisor? That's the next logical question I would ask. You can use the Oracle supplied DBMS_SQLTUNE procedure to manually create, alter and drop SQL profiles. If you are looking for more information on how to use the DBMS_SQLTUNE procedure to create and administer SQL profiles, please search for note 271196.1 on Oracle's support website Metalink.

So, if you need to drop a SQL Profile which was automatically created using the SQL Tuning Advisor you can select from the DBA_SQL_PROFILES table or navigate to EM's advisor central page to view the advisor's output. You can then use that information to identify the SQL profile and then execute the statement below to drop it.

BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
END;
/

Since our first SQL statement generated only a few recommendations, I chose to analyze another statement that generated this set of recommendations. If you choose to implement the create index recommendation, you will not build the index. Instead, 10G Enterprise Manager will display the SQL Access Advisor home page to allow you to analyze the statement and create the proper index structures.

As with all advisor executions, the SQL Tuning Advisor output is available on the Advisor Central home page until it expires.

I think you'll find that the first release of the SQL Tuning Advisor to be pretty robust. Do I agree with all of its recommendations? NO! But since when do DBAs always agree on everything, or anything for that matter? Like all new features, as this one matures it will provide us with higher quality recommendations at a lower resource consumption cost.

Thanks for reading.