ADDM and SQL Tuning Advisor

Get help on SQL tuning from the ultimate authority: the Oracle Database itself! Make a query behave using SQL Profiles and learn how to use ADDM to solve common performance problems quickly and easily.

It has been a quiet day so far: no major problems in the database, no fires to put out. You are almost relaxed; it's a perfect day for catching up on important to-do tasks such as tuning RMAN tuning parameters or multiple block sizes.

Suddenly, a developer appears at your cubicle. His SQL query is taking a long time to run. Could you please, he asks, tune the query ASAP so it will "behave"?

Perhaps you relaxed too soon. Your original agenda was to spend some time making strategic decisions that will make your database better, faster, and more secure—such as ensuring the database is recoverable, enhancing underlying technology, or researching security updates. Instead, you will spend another day focusing on tactical activities such as SQL tuning, leaving little or no time for a strategic agenda.

As a strategic DBA, you want to free yourself from mundane chores and focus more on thought-provoking areas. Wouldn't it be nice to have an assistant DBA do them for you?

With Oracle Database 10g, you have one in the form of Automatic Database Diagnostic Monitor (ADDM), a sort of robotic DBA that tirelessly trolls through database performance statistics to identify bottlenecks, analyze SQL statements, and consequently offer various types of suggestions to improve performance, often in conjunction with other "advisors" such as the SQL Tuning Advisor. In this installment, you will get an overview of how this process works.

Automatic Database Diagnostic Monitor (ADDM)

In Week 6, you learned about Automatic Workload Repository (AWR), which collects detailed performance-related metrics from the database at regular intervals, known as snapshots. After each snapshot is taken, ADDM is invoked to thoroughly analyze the data and metrics deriving from the difference between snapshots, and then recommend necessary actions. As I mentioned earlier, after finding a problem, ADDM might in turn call other advisors (such as the SQL Tuning Advisor) to offer recommendations for improvement.

Instead of explaining this feature in words, let me show you exactly how it works. Suppose you are trying to diagnose an unexplained performance problem. In the example in our introduction, you knew which SQL statements needed to be tuned, or at least that SQL statements were the problem. In real life, however, you probably will not have such helpful information.

To perform a diagnosis in 10g, you would choose the snapshots in the relevant interval for further drill-down analysis. In Enterprise Manager 10g, from the Database home page, you would choose "Advisor Central" and then click on the "ADDM" link, which brings up a page similar to Figure 1.

Figure 1: Creating an ADDM task

In this page, you can create tasks to be analyzed by ADDM. You know that the performance problems occurred around 11PM, so choose the snapshots that fall in that range, indicated by "Period Start" and "Period End" values. You can also click on the camera icons to indicate start and stop snapshot intervals, as shown in red ellipses here. After choosing the interval, press the "OK" button, which brings up a page similar to that shown in Figure 2.

Figure 2: ADDM findings

Here ADDM identifies two critical and related performance problems in the interval: some SQL statements are consuming significant CPU time, leading to a significant database slowdown. Based on the findings, ADDM recommends SQL tuning for these statements as highlighted in the figure.

If you click on the individual findings, ADDM displays more details. For example, clicking on the problem finding brings up a page similar to the one shown in Figure 3.

Figure 3: Details of ADDM findings

Here you can see the specific SQL statement causing this problem. ADDM recommends that you subject this SQL statement to a thorough analysis by SQL Tuning Advisor, as mentioned in the "Action" section. You can immediately run the task by clicking on the button next to it, which will invoke the SQL Tuning Advisor.

In Figure 2, you may have noticed a button named "View Report." In addition to providing the recommendation in individual web pages, ADDM can also create plain-text reports for a quicker one-stop analysis. Listing 1 shows the comprehensive recommendations made in our example plain-text report. Note how the report presents relevant details such as the SQL statement in question, its hash value, and so on. The SQL ID can be used for independent analysis in the SQL Tuning Advisor page in Enterprise Manager or via the command line. ADDM is invoked after every AWR snapshot is collected, so the recommendations based on the adjacent snapshots are available for viewing. Hence you will not have to create an ADDM task as shown above if the scope of analysis is just two adjacent snapshots. If you want to analyze between two snapshots that are not adjacent, you will need to create the ADDM task. Keep in mind that this is not all ADDM can do; there are many more analyses and recommendations available for memory management, segment management, redo/undo, and more, as you saw in previous installment. Because it would be impossible to describe the full spectrum of ADDM functionalities in this single brief article, we'll focus only on SQL Tuning Advisor here. Now let's see how it works.

Access Analysis with SQL Tuning Advisor

In a typical runtime optimization, the optimizer generates a set of possible access paths and chooses the least "expensive" among them based on object statistics. At that moment, however, it does not have the time to address whether the statement can be tuned, the statistics are stale, an index can be created, and so on. In contrast, the SQL Tuning Advisor can perform this "expert system" type of thinking. Essentially, the optimizer can answer the question: "Based on what's available, what's the best way to get results?", whereas SQL Tuning Advisor can answer the question, "Based on what the user wants, what else needs to be done to enhance performance?"

As you might expect, this "thinking" consumes resources such as CPU; hence the SQL Tuning Advisor works on SQL statements during a Tuning Mode, which can be run during off-peak times. This mode is indicated by setting the SCOPE and TIME parameters in the function while creating the tuning task. It's a good practice to run Tuning Mode during a low-activity period in the database so that regular users are relatively unaffected, leaving analysis for later.

The concept is best explained through an example. Take the case of the query that the developer brought to your attention, shown below.

select account_no from accounts where old_account_no = 11

This statement is not difficult to tune but for the sake of easier illustration, assume it is. There are two ways to fire up the advisor: using Enterprise Manager or plain command line.

First, let's see how to use it in command line. We invoke the advisor by calling the supplied package dbms_sqltune.

declare

 l_task_id     varchar2(20);
 l_sql         varchar2(2000);
begin
 l_sql := 'select account_no from accounts where old_account_no = 11';
 dbms_sqltune.drop_tuning_task ('FOLIO_COUNT');
 l_task_id := dbms_sqltune.create_tuning_task (
    sql_text  => l_sql,
    user_name  => 'ARUP',
    scope      => 'COMPREHENSIVE',
    time_limit => 120,
    task_name  => 'FOLIO_COUNT'
 );
 dbms_sqltune.execute_tuning_task ('FOLIO_COUNT');
end;
/

This package creates and executes a tuning task named FOLIO_COUNT. Next, you will need to see the results of the execution of the task (that is, see the recommendations).

set serveroutput on size 999999
set long 999999
select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;

The output is shown is Listing 2. Look at these recommendations carefully; the advisor says you can improve performance by creating an index on the column OLD_ACCOUNT_NO. Even better, the advisor calculated the cost of the query if the index were created, making the potential savings more definable and concrete.

Of course, considering the simplicity of this example, you would have reached the conclusion via manual examination as well. However, imagine how useful this tool would be for more complex queries where a manual examination may not be possible or is impractical.

Intermediate-Level Tuning: Query Restructuring

Suppose the query is a little bit more complex:

select account_no from accounts a
where account_name = 'HARRY'
and sub_account_name not in
( select account_name from accounts
  where account_no = a.old_account_no and status is not null);

The advisor recommends the following:

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The optimizer could not unnest the subquery at line ID 1 of the execution
plan.

Recommendation
--------------
  Consider replacing "NOT IN" with "NOT EXISTS" or ensure that columns used
  on both sides of the "NOT IN" operator are declared "NOT NULL" by adding
  either "NOT NULL" constraints or "IS NOT NULL" predicates.

Rationale
---------
  A "FILTER" operation can be very expensive because it evaluates the
  subquery for each row in the parent query. The subquery, when unnested can
  drastically improve the execution time because the "FILTER" operation is
  converted into a join. Be aware that "NOT IN" and "NOT EXISTS" might
  produce different results for "NULL" values.

This time the advisor did not recommend any structural changes such as indexes, but rather intelligently guessed the right way to tune a query by replacing NOT IN with NOT EXISTS. ecause the two constructs are similar but not identical, the advisor gives the rationale for the change and leaves the decision to the DBA or application developer to decide whether this recommendation is valid for the environment.

Advanced Tuning: SQL Profiles

As you may know, the optimizer decides on a query execution plan by examining the statistics present on the objects referenced in the query and then calculating the least-cost method. If a query involves more than one table, which is typical, the optimizer calculates the least-cost option by examining the statistics of all the referenced objects—but it does not know the relationship among them.

For example, assume that an account with status DELINQUENT will have less than $1,000 as balance. A query that joins the tables ACCOUNTS and BALANCES will report fewer rows if the predicate has a clause filtering for DELINQUENT only. The optimizer does not know this complex relationship—but the advisor does; it "assembles" this relationship from the data and stores it in the form of a SQL Profile. With access to the SQL Profile, the optimizer not only knows the data distribution of tables, but also the data correlations among them. This additional information allows the optimizer to generate a superior execution plan, thereby resulting in a well-tuned query.

SQL Profiles obviate the need for tuning SQL statements by manually adding query hints to the code. Consequently, the SQL Tuning Advisor makes it possible to tune packaged applications without modifying code—a tremendous benefit.

The main point here is that unlike objects statistics, a SQL Profile is mapped to a query, not an object or objects. Another query involving the same two tables—ACCOUNTS and BALANCES—may have a different profile. Using this metadata information on the query, Oracle can improve performance.

If a profile can be created, it is done during the SQL Tuning Advisor session, where the advisor generates the profile and recommends that you "Accept" it. Unless a profile is accepted, it's not tied to a statement. You can accept the profile at any time by issuing a statement such as the following:

begin
 dbms_sqltune.accept_sql_profile (
    task_name   => 'FOLIO_COUNT',
    name        => 'FOLIO_COUNT_PROFILE'
    description => 'Folio Count Profile',
    category    => 'FOLIO_COUNT');
end;

This command ties the profile named FOLIO_COUNT_PROFILE generated earlier by the advisor to the statement associated with the tuning task named FOLIO_COUNT described in the earlier example. (Note that although only the advisor, not the DBA, can create a SQL Profile, only you can decide when to use it.)

You can see created SQL Profiles in the dictionary view DBA_SQL_PROFILES. The column SQL_TEXT shows the SQL statement the profile was assigned to; the column STATUS indicates if the profile is enabled. (Even if it is already tied to a statement, the profile must be enabled in order to affect the execution plan.)

Using ADDM and SQL Tuning Advisor

In addition to the three cases described above, SQL Tuning Advisor also identifies any objects with missing statistics referenced in the query. Thus, the advisor performs four distinct types of tasks:

• Checks if objects have valid, usable statistics for proper optimization
• Attempts to rewrite queries for better performance and suggests rewriting
• Checks the access path to see if performance could be improved by adding additional structures such as indexes and materialized views
• Creates SQL profiles and attaches them to specific queries.

Based on these capabilities, I can think of at least three different scenarios in which ADDM and SQL Tuning Advisor serve as powerful tools:

• Reactive Tuning: Your application suddenly starts to perform poorly. Using ADDM, you can drill the problem down to a SQL statement or a set of statements, as shown in the section on ADDM. Following the recommendation of ADDM, you can launch SQL Tuning Advisor and correct the problem.
• Proactive Tuning: The application behaves acceptably well; however, you want to make sure that all necessary maintenance tasks are performed and know if queries can be tuned even more. You would fire up SQL Tuning Advisor in the standalone mode to identify possible tuning alternatives.
• Development Tuning: While code is being tested in development there are many opportunities to tune the query, as opposed to wthe QA or production phases. You can use the command-line version of the advisor to tune individual SQL statements before they are finalized in development.

Using Enterprise Manager

The previous example was deliberately formulated to illustrate how to use SQL Tuning Advisor in command-line mode, which is very useful for scripting these tasks proactively. In most cases, however, you will need to perform tuning in response to problems reported by an end user. Enterprise Manager 10g comes in handy in those cases. A few weeks ago (Week 13) you were introduced to the revamped Enterprise Manager interface. Here's how you would use it to diagnose and tune SQL: From the Database home page, click on the link "Advisor Central" at the bottom of the screen, which launches the page containing all the advisors. Next, click on "SQL Tuning Advisor" at the top of the screen as shown in Figure 4.

Figure 4: Advisor Central in Enterprise Manager

You have just launched the SQL Tuning Advisor. Choose "Top SQL" from the next page as shown in Figure 5.

Figure 5: SQL Tuning Advisors

This action launches a page similar to the one shown in Figure 6, where a graph containing the various wait classes are traced along a time dimension.

Figure 6: Top SQL Chooser

A gray rectangular area within a red ellipse puts the focus on the graph. Reposition the rectangle by mouse-dragging it to a location where the CPU wait is high (as shown in the figure). The lower part of the page will display the relevant SQL statements in that interval, as shown in Figure 7.

Figure 7: Choosing SQL statements based on activity

As you can see, the SQL statement shown at the top (enclosed by the red ellipse) has the highest activity with maximum CPU consumption. Click on the statement ID to see details of the statement, which will bring up a screen as shown in Figure 8.

Figure 8: SQL details

In the figure, you can see the exact SQL statement that caused the CPU consumption in that time period. You can click on the button "Run SQL Tuning Advisor" (marked in the figure) to run the advisor. This brings up a screen similar to the one shown in Figure 9.

Figure 9: Scheduling SQL Tuning Advisor

In the advisor scheduler, you can determine the type of task and how much analysis should be done. For example, in the above figure, I have chosen "comprehensive" analysis and that the advisor is to be run immediately. After the advisor finishes you can see its recommendation, as shown in Figure 10.

Figure 10: Advisor recommendation

This process I just described is similar to what you have seen in the command-line version; however, the flow is more reflective of a real-life scenario in which you have reacted to a problem, drilled down to its cause, and accepted recommendations about how to fix it.

Conclusion

ADDM is a powerful tool that has the "brains" to analyze performance metrics and offer recommendations based on best practices and accepted methodologies professed by seasoned Oracle professionals, all automatically. This functionality can tell the DBA not only what happened and why, but most important, what to do next.

For more information about ADDM and SQL Tuning Advisor, see the Technical Whitepapers Oracle Database 10g: The Self-Managing Database and The Self-Managing Database: Guided Application & SQL Tuning, Chapter 10 of the Oracle Database 2 Day DBA manual, and Chapters 6 and 13 of the Oracle Database Performance Tuning Guide.

Happy Learning !