TECHNOLOGY: Testing

Performing Through Changes

Measure the impact of changes on SQL workload with SQL performance analyzer.

Here is a not-so-uncommon scenario: a query is running slowly. Upon investigation, you determine the reason to be a full-table scan and a possible solution to be an index to help speed up the query. As part of this performance-improvement process, you may have to make several changes in the database, including using indexes and statistics, converting to different index types, changing optimizer compatibility, and modifying an initialization parameter. And although these changes may improve the original query, they bring with them a potential for other unforeseen issues, and you certainly want to understand the impact of the changes on all SQL statements—the actual SQL statements issued by the applications. How? You could pull each and every query from the shared pool and manually check the impact of the changes you are about to make, but this task is probably not feasible in a database of any size.

In Oracle Database 11g, however, there's a new tool—SQL performance analyzer—that enables you to replay all your SQL statements before and after any number of changes and compare the results to see the changes' impact. You can gauge the overall impact as well as that on individual SQL statements.

This article demonstrates how to use SQL performance analyzer to gauge the overall impact of creating an index on the STORE_ID column in a table called TRANS and then refreshing the statistics on the table and the index. You can download the script to create the sample data for this article at otn.oracle.com/oramag/oracle/08-mar/o28sqlperf.zip .

Using SQL Performance Analyzer

SQL performance analyzer operates on the SQL statements captured in a SQL tuning set, which can be easily created via the Oracle Enterprise Manager Database Control. To create a SQL tuning set from the existing SQL statements in the shared pool, follow these steps:

1. Create and log in to a schema named ACME, and run most of the SQL script included in the otn.oracle.com/oramag/oracle/08-mar/o28sqlperf.zip download file to create the sample table and data as well as execute some SQL statements. (Do not run the code after "to gather stats" at this time.)
2. From the Database Home page (in Oracle Enterprise Manager Database Control), click the Performance tab and the Search SQL link, which displays a screen similar to Figure 1.

Figure 1: SQL tuning set creation

Now run SQL performance analyzer:

1. From the Database Home page, click the Performance tab and then the SQL Performance Analyzer link.
2. SQL performance analyzer offers three ways to perform a replay. For this example, choose Guided Workflow . It shows the series of steps you must follow (see Figure 2).

Figure 2: Guided workflow tasks

3. Click Step 1 to create a SQL performance analyzer task. Give the task a name— ACME_SPA 1—and choose the SQL tuning set you created earlier, ACME_STS1.

4. Now click Step 2 (Replay SQL Tuning Set in Initial Environment), which replays the captured SQL statements in the SQL tuning set. You will be asked to give the replay a name. Because this is the initial, prechange replay, call it BASE.

5. After the BASE replay is completed, perform the change. For this example, create the index on the STORE_ID column in the TRANS table: 

create index in_trans_store_id 
on trans (store_id);

6. Now replay the SQL statements in the ACME_STS1 SQL tuning set, by clicking Step 3 (Replay SQL Tuning Set in Changed Environment) in the workflow. Give this replay the name AFTER_INDEX , to designate the SQL statement replay made after the creation of the index.

7. After the replay is finished, you will have two replay results: the initial one (BASE) and the one run after the change (AFTER_INDEX). Click Step 4 (Compare Step 2 and Step 3) to compare the outcomes of the two replays (before and after the change). By default, the basis of the comparison is the elapsed time, but you have the option to change the basis to Buffer Gets, CPU Time, and so on.

8. Finally, view the report comparing the BASE and AFTER_INDEX replays, by clicking Step 5 (View Trial Comparison Report). This brings up a report, shown in Figure 3, displaying the impact of the changes graphically. 

Figure 3: Comparison report after adding index

You can see that there was a 94 percent improvement between the BASE and AFTER_INDEX replays but also that there was a 3 percent regression (or degradation). The elapsed time (shown on the left) is significantly less for the AFTER_INDEX replay than for the BASE replay. On the right side, you can see how the plans of the SQL statements have improved, regressed, or remained unchanged in terms of elapsed execution time. Figure 3 makes clear that for most of the statements, the plan remained unchanged. The Top 10 SQL Statements... list shows how each SQL statement fared within the SQL tuning set. Each SQL ID is a hyperlink that enables you to see the SQL as well as the corresponding plans.

Obviously, creating and using the index helped improve performance, as the overall impact (improvement) of 91 percent suggests, but you wonder if it would help even more to gather statistics again. Well, you don't have to speculate. You can perform another replay after another change.

1. First perform the change—gather the stats on the table in the usual manner. The following block from the sample data and code download gathers statistics for this example: 

begin 
  dbms_stats.gather_table_stats (
    ownname            => 'ACME',
    tabname             => 'TRANS',
    estimate_percent => 100,
    method_opt        => 'FOR ALL COLUMNS SIZE AUTO',
    cascade              => true);
end; 

2. On the SQL Performance Analyzer main page, click the ACME_SPA1 task; this displays the task page, shown in Figure 4. Here you can add another replay by clicking the Create Replay Trial button. Provide the name AFTER_STATS . After the replay is completed, click the Run Replay Trial Comparison button to produce the comparison report. Click the eyeglass icon to bring up the report, shown in Figure 5.

Figure 4: SQL performance analyzer task screen

Figure 5: Comparison report after gathering stats

3. Examine the report carefully: there is a 49 percent improvement but a 118 percent regression, meaning that the overall impact is negative. You may be tempted to think that gathering stats was not good overall, but that conclusion is far from definitive. The next step is to check what actually caused the regression. Note the Top 10 SQL Statements... list in Figure 5. The very first SQL statement caused most of the regression. 

4. Examine the first SQL statement, by clicking the 95mag7mtvkqk5 SQL ID, which shows a 117.55 percent regression. Figure 6 shows detailed information about the statement, including the fact that the statement was executed only four times (Execution Frequency). For difficult-to-understand problems, you can click the Schedule SQL Tuning Advisor button to get advice on how to reduce the elapsed time. SQL tuning advisor can tell you if the plan's regression is due to stale stats and if refreshing the stats will help.

Figure 6: Details of SQL statement causing regression

Note that in a situation in which only one statement has contributed all of the regression to a replay, you can look at that statement and its execution frequency and perhaps disregard that negative impact. Also, the bottom of the SQL statement screen displays the query plan before (the AFTER_INDEX replay) and after (the AFTER_STATS replay) the change, which will help in making a quick diagnosis.

Conclusion

Remember that there are three options on the SQL Performance Analyzer main page, from which I chose Guided Workflow . I chose it because it provides the best control over tasks and will help you learn how to use the tool. For assessing the impact of two common changes—to optimizer versions and initialization parameters—the SQL performance analyzer tasks are even simpler, and you can accomplish them by using the other two options listed on the SQL Performance Analyzer main page—optimizer upgrade simulation and parameter change. For an initialization parameter change such as changing db_file_multiblock_read_count from 16 to 32, you can put the two values on one screen and SQL performance analyzer will execute the replay for both values automatically and compare the output.

With SQL performance analyzer, you can make a change and see its impact on all the SQL statements issued by the users in the database. The report shows you clearly how each statement fared under the change as well as the overall impact and whether the plans were changed. SQL performance analyzer replays the actual SQL statements—not synthetic ones—issued against the database, making the impact analysis as accurate as it can be.
 

Send us your comments