11g SQL Performance Analyzer Rocks!
Posted by Tyler Muth on January 8, 2008
There are a lot of myths and rules of thumb floating around about Oracle tuning:
- Should I partition this table or not?
- What partitioning scheme should I use?
- What values should I use for OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING?
- Should I regularly rebuild my indexes?
…and the list goes on. Some of the most respected experts in the field, including Tom Kyte and Jonathan Lewis, promote the methodology of “Prove It”, which I support 100%. The problem is that the time required to construct test cases and “proof” can be a bit prohibitive.
Enter SQL Performance Analyzer (SPA). This is one of my favorite 11g new features. SQL Performance Analyzer allows you to capture a SQL Tuning set, play it back multiple times with different options, then get the results of the changes.
Lets start with a quick overview of the process:
- Capture a SQL Tuning set. This is a pretty straight forward step from Database Console. For my example later in this post, I told it to capture all SQL executed as user FLOWS_030000 for the next 2 minutes. I then ran the Application Express builder to generate the SQL.
- Run the SQL Performance Analyzer “Guided Workflow” wizard:
- Replay the SQL Tuning set the First Time.
- Change some parameters or data structures.
- Replay the SQL Tuning set the Second Time.
- Compare the 2 Tuning Sets and store the results
- View the results, including SQL that improved or regressed, and any SQL Plans that changed.
As an example, lets take a look the impact of changing OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING. As I mentioned before, the Tuning Set we’re going to use is just the SQL executed as the user FLOWS_030000, so this is NOT testing the full impact of this change on my database, but you could capture a tuning set for the whole db to test this. I’m not going to walk through all 5 steps of the wizard, because there isn’t much to see. I will say that this whole process, including capturing the SQL Tuning Set, required only 5 minutes.
To get to the SQL Performance Analyzer from the 11g Database Console, click the Performance tab, then click on SQL Performance Analyzer on the lower right, then click Guided Workflow. Here’s a screenshot of the Guided Workflow wizard:
Before starting Step 2, I ran the following code:
alter system set optimizer_index_cost_adj=100 scope=both; alter system set optimizer_index_caching=0 scope=both; alter system flush shared_pool; alter system flush buffer_cache;
Before starting Step 3, I ran the following code:
alter system set optimizer_index_cost_adj=45 scope=both; alter system set optimizer_index_caching=90 scope=both; alter system flush shared_pool; alter system flush buffer_cache;
Below is a screenshot of the results screen. Note that of the 379 SQL statements, 147 had errors. These were due to DML operations in APEX and therefore not an issue here, but this is a great feature to be aware of. Also note that there was a 78% Improvement Impact and a 0% Regression Impact.
Finally, the next two cropped screenshots are from the details of a SQL plan that changed, showing us the old and new plans. I cropped the screen into 2 parts so it would fit on this web page.
As you can see, the new plan uses an index that the old plan did not.
Traditionally, testing these changes against 1 SQL Statement would be relatively simple, but testing the impact on 300+ statements, or for that matter every SQL Statement in the whole database, would be next to impossible. This is one of those features that DBA’s and developers alike should embrace. As a developer, you can test any performance change that you want your DBA to make before you make the request. As a DBA, you can review the impact of any potential changes before you put them in production, drastically reducing the risk.