Performing Through ChangesBy Arup Nanda
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.)
3. Click Save to a new SQL Tuning Set , and enter ACME_STS1 as the tuning set. Search all the SQL statements issued by the ACME schema, by entering ACME as the parsing schema name and clicking the Search button. The SQL statements will be displayed in the lower part of the screen (as shown at the bottom of Figure 1).
4. Click Save . These statements will be saved to the SQL tuning set called ACME_STS1. (To capture additional SQL statements, click Save to an existing SQL Tuning Set , shown at the top of Figure 1.)
Now run SQL performance analyzer:
1. From the Database Home page, click the Performance tab and then the SQL Performance Analyzer link.
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.
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.
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.
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.
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.
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.
Arup Nanda ( email@example.com ) has been an Oracle DBA for more than 12 years, handling all aspects of database administration, from performance tuning to security and disaster recovery. He was Oracle Magazine 's DBA of the Year in 2003.