Oracle Database 11g: The Top New Features for DBAs and Developers by Arup Nanda

SQL Performance Analyzer

Learn how to accurately predict changes on the actual SQL statements issued against your database.

Download Oracle Database 11g

In a previous installment of this series you learned about Database Replay, that great tool for capturing the real workload in your database and replay them at will. Database Replay is part of what Oracle calls the Real Application Testing option, with the emphasis on the word "real"—the workload replayed is actually what occurred in your database.

In Database Replay, the entire captured workload is replayed against the database. But what if you don't want to do that? For example, you may be interested in understanding how SQL execution plans and therefore SQL performance might be affected by a change, because they can severely affect application performance and availability. In addition, Database Replay replays only what has been captured; not anything else. You may be interested to know the impact of parameter changes on some SQLs that has not been executed in production yet.

This area is where the other important component of the Real Application Testing family—SQL Performance Analyzer (SPA)—shines. SPA allows you to play out some specific SQL or your entire SQL workload against various types of changes such as initialization parameter changes, optimizer statistics refresh, and database upgrades, and then produces a comparison report to help you assess their impact. In this installment, you will learn how to use this tool to answer that important question.

A Sample Problem

Let's go on a test drive. First, let's define the problem you are trying to solve.

The problem is a typical one: Oracle is not using an index, and you want to know why not. To answer that question, I turned to the classic paper by the Oracle guru Tim Gorman, "Searching for Intelligent Life in Oracle's CBO." (You will find versions of this paper in many forms all over the Web.)

One of Tim's suggestions is to change the value of the parameter optimizer_index_cost_adj from the default 100 to something smaller. The paper also gives a formula to calculate what the value should be. Based on that formula, I calculated the value to be 10, in my case. But here comes a difficult question: Is that change going to be beneficial for every SQL statement?

In releases prior to Oracle Database 11g, I have to capture all SQL statements, run them through tracing, and get execution plans—a task not only enormously time consuming but error prone as well. With the new release, I don't need to do that; instead, I have the very simple and effective SQL Performance Analyzer.

First, for the purpose of illustration, run the following queries in the database:

select /* CONTROL_QUERY11 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 33;
select /* CONTROL_QUERY12 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 350;
select /* CONTROL_QUERY13 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 351;
select /* CONTROL_QUERY14 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 534;
select /* CONTROL_QUERY15 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 999;

select /* CONTROL_QUERY21 */ sum(QUANTITY_SOLD) from sales where channel_id = 2;
select /* CONTROL_QUERY22 */ sum(QUANTITY_SOLD) from sales where channel_id = 3;
select /* CONTROL_QUERY23 */ sum(QUANTITY_SOLD) from sales where channel_id = 4;
select /* CONTROL_QUERY24 */ sum(QUANTITY_SOLD) from sales where channel_id = 5;
select /* CONTROL_QUERY25 */ sum(QUANTITY_SOLD) from sales where channel_id = 9;

How to Use It

As usual, the best way to harness the power of this tool is via Oracle Enterprise Manager. (Of course, you can still use command-line options through the provided package dbms_sqlpa but the manageability value added by Enterprise Manager can't be beat.)

Follow the steps shown below.

1. Start Enterprise Manager Database Control and click the Performance tab. Then scroll to the bottom of the page where you will see hyperlinks as shown below.
   

2. Click on Search Sessions, which brings up a screen such as the following:
   

3. Search for the pattern in the SQLs you executed from the cursor cache. Note that the SQLs had a comment in them—CONTROL_QUERYn—where n was 11, 12, etc. Enter the string as a search function. It will bring up all the SQLs executed earlier. On the screen shown above, you will see a radio button titled Save to a new SQL Tuning Set. Select that and enter a SQL Tuning Set named CONTROL1. (Note: for the purposes of examples you are selecting a set number of statements.) No need to put any comments in SQL; all you need to do is create a "SQL Tuning Set" and put all relevant statements in there.
   
   
4. Click on SQL Tuning Sets, which brings up the SQL Tuning Sets page. Now select the STS named CONTROL1. In this page, you can examine the STS and add and drop SQLs from it. Here is a screenshot showing the STS page:
   

5. Now, from the Performance page, click SQL Performance Analyzer. It brings up the main SPA page shown below.
   

6. As you can see, no SPA tasks have been defined yet. You will define one now. In this example you are evaluating the impact of change to the parameter optimizer_index_cost_adj; so click Parameter Change. It brings up the SPA Task Definition page shown below.
   

7. In the page, you will have to enter the necessary information for the SPA task. Give that task a name: STS1, for this example.
   
   
8. Next, you need to enter the SQL Tuning Set name. Click on the flashlight icon next to it and select the STS named CONTROL1.
   
   
9. In the section Parameter Change, you need to enter the parameter you need to change. Click the flashlight icon and select the parameter optimizer_index_cost_adj. The current value is already populated; enter the target value in the box Changed Value.
   
   
10. Next, decide how you will compare these changes: on elapsed time, on CPU time, and so on. For this purpose of this example, choose Buffer Gets.
    
    
11. Finally, schedule this SPA task for execution. Select the radio button Immediate, meaning you want it to run immediately.
    
12. With all the details in place, click Submit. It creates a job that you can monitor independently but you can also be in this page and monitor the status of the SPA task as shown below.
    

13. Click Refresh to see the current status of the task. Note the icon under the Last Run Status column. Here is a description of those icons:
    

14. The icon in the picture means the task is running now. Keep on clicking Refresh. When the icon changes to Completed, shown as a tick mark, the SPA task has completed.
    
    
15. Click on the SPA Task name (STS1) to see the comparison metrics. This brings up a screen as shown below.
    

16. Note the eyeglass icon under the column Comparison Report. This is where you can compare the results of SQL runs before and after the parameter change. Click on that icon.
    
    
17. This is the most exciting part. A screen comes up as shown below:
    

This is exactly what you were waiting for. On the top left corner, you will see the comparison of the two runs of the SQL set before and after the change. Overall, there was a 60% improvement in performance! It's right there, in black and white. In the right hand side of the screen, you will see how many SQL statements changed the execution plans. It seems a majority changed the plans while a small number didn't.

The bottom portion of the screen shows you the SQL ID of the SQL statements analyzed in this task. The little arrows preceding these SQL IDs show whether these SQL statements improved or degraded, and the numbers following the SQL IDs show the percentage of that impact. These numbers tell you exactly the impact of the change on each SQL statement. If you wish, you can examine the SQL by clicking SQL ID. Note the very first SQL, which has the highest impact; if you click on that, you will see a screen similar to the following:

The screen shows a lot of statistics on the execution of the SQL. The bottom part of the screen shows the comparison of the execution plans:

Now you can see how the use of the index forces fewer buffers. But is the picture that rosy? Consider another SQL:

In this case, the gains were modest, just 0.48%, compared to 31.95% in the previous case. For what reason? To find the answer, click on the SQL ID, which brings up a screen as shown below:

Here you can see exactly what changed. The elapsed time actually went up from 0.504 to 1.022 seconds, and all because of CPU time. Why? If you examine the data distribution pattern, you will see the promo_id is distributed as shown below:

SQL> select promo_id, count(1) cnt from sales group by promo_id;
 
   PROMO_ID        CNT
---------- ----------
       534          1
       999     887837
       350      18022
        33       2074
       351      10910
           ----------
sum            918844

As you can see, you wanted to estimate the impact of making a very important change in the database parameter. Using SPA, you did not have to make an estimate—not even a "guess-timate"—on the potential performance impact. Rather, you were able to objectively measure it using the very SQL statements that your application makes against your own database.

Now consider another case: After the change to the parameter, the performance degrades, not improves. Here is a screenshot:

Here the SQL statements are all running worse than before. To fix the problem, you can take advantage of SQL Plan Management (discussed in this installment). SPM provides execution plan stability by allowing you to select good execution plans as your baseline, which are then used by the optimizer for all executions of that SQL. This baselined plan is used until it is disabled or a new baseline plan is created by you. Another way to fix the problem of regressed SQL is by using the SQL Tuning Advisor, which can provide SQL tuning recommendations or suggest external modifications such as creating indexes to improve performance.

Use Cases

For example, when deciding if you want to upgrade the optimizer parameter from say 10.2 to 11.1, you definitely want to see the impact this change will have on your SQL statements. The best tool for the job is SPA. The only difference is, in step 5 above, instead of choosing Database Parameter Changes, choose Optimizer Changes, which brings up a screen as shown below.

In this screen, choose the appropriate source and target optimizer versions and follow the rest of the steps.

Conclusion

When is the best time to use this new tool? The simple answer is: whenever you make any type of change. Unlike with Database Replay—where you can't see the exact SQLs—you can use SPA to get results on specific SQLs or the entire application SQL workload. You can evaluate the pros and cons and arrive at the best possible alternative without compromising your application's performance. No choice is ever black or white; it's the shades of gray that makes arriving at a decision so tough. SPA moves these gray scales toward one of the poles and makes it easier for you to make that decision.