Oracle Database 11g: SQL Performance Analyzer, Part 3 - Page 2

December 21, 2007

Assessing Parameter Changes: Scenario #2

How would a change to the STAR_TRANSFORMATION_ENABLED initialization parameter influence the performance of these same four SQL statements? I’ve prepared a new SQL Performance Analyzer task named SPA_IPC_200 to answer this question. As shown in Figure 3.7 below, I once again selected SQL Tuning Set SYS.STS_SPA_300 as the target for my analysis. I’ve chosen a base value of FALSE (the default value) and a changed value of TRUE for this initialization parameter. The results of this analysis are shown in Figure 3.8 below.

Assessing Parameter Changes: Scenario #3

Is there a way to actually execute a series of SQL*Plus and PL/SQL commands to submit the same type of analysis task without using Enterprise Manager? The good news is that it’s definitely possible. After some judicious and extensive SQL statement tracing, I determined exactly how the SPA wizard was submitting these tasks.

I’ve used these statements to generate my final initialization parameter change scenario: the impact of changing the OPTIMIZER_INDEX_COST_ADJ initialization parameter from its non-default value of 25 to its default value of 100. As mentioned previously, I’d expect to see an impact on the optimizer cost for statements LDGN 5.1 and LDGN 5.4 because this parameter affects the CBO’s decision to use an available index versus a table scan.

I’ve listed the code I used to perform the analysis in Listing 3.2. These statements:

• Create a SQL Performance Analyzer Task named SPA_IPC_300
• Execute a “before” performance impact analysis
• Execute an “after” performance impact analysis
• Create a comparison report between the “before” and “after” performance impact analysis

Oracle 11g uses a simple method to perform the “before” and “after” performance impact analyses: It simply issues an ALTER SESSION command to modify the initialization parameter setting before performing the analysis, and then it executes the SPA analysis task to capture the results. Figure 3.9 below shows the summary page from this SQL Performance Analyzer task’s successful execution, and Figures 3.9.1 through 3.9.3 show the detailed analysis for SQL statement LDGN 5.1:

Customizing SQL Performance Analyzer Reporting Results

What if I’m not satisfied with the appearance of the comparison report that the SQL Performance Advisor comparison reporting mechanism generates? For example, what if I’d like to have the SQL statements that I’ve targeted for analysis to be sorted in a different relative order – say, within optimizer cost instead of elapsed time (the default)? The good news is that I can specify a different reporting dimension for the summary report simply by changing the value that’s supplied for the execution_params argument of the DBMS_SQLPA.EXECUTE_ANALYSIS_TASK procedure. I’ve illustrated this in the immediately prior example by selecting OPTIMIZER_COST as the comparison metric.

Here are the values that I’ve tested for this parameter; as of this article’s publication date, these aren’t yet documented in the Oracle 11g online manuals:

On the Horizon: SQL Plan Management

While SQL Performance Analyzer’s features are admittedly impressive, and while it certainly promises to make short work of the uncertainty any DBA faces when contemplating system changes, database upgrades, and application modifications, it still begs one question: How can these analyses be used effectively to limit poor SQL statement performance?

What I really need to accomplish is twofold:

• Insure that any SQL statements that will unquestionably perform better in the post-change production environment will immediately begin using better execution plans once all changes are complete; and
• Insure that any SQL statements that perform well in the current production environment will continue to use their current execution plan in the post-change production environment.

Achieving these goals is the focus of my next series of articles: Oracle 11g’s new SQL Plan Management (SPM) features.

Conclusion

As this article series has shown, Oracle Database 11g’s new SQL Performance Analyzer offers an impressive tool set for analyzing the impact to the performance of SQL statements without having to construct separate test environments for each test scenario. SQL Performance Analyzer therefore has the potential to reduce significantly the enormous time outlay that Oracle DBAs, QA Analysts, and application developers have had to invest in the past to identify, verify, and isolate SQL statements that may perform poorly after a major database release is implemented, a new version of an existing application is released, or even a seemingly harmless change to an initialization parameter is made.

References and Additional Reading

While I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features I’ve discussed in this article, I’m also sure that there may be better documentation available since it’s been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that I’ve drawn upon the following Oracle Database 11gR1 documentation for the deeper technical details of this article:

B28313-02 Oracle Database 11gR1 Data Warehousing Guide

B28274-01 Oracle Database 11gR1 Performance Tuning Guide

B28279-02 Oracle Database 11gR1 New Features Guide

B28419-02 Oracle Database 11gR1 PL/SQL Packages and Types Reference

» See All Articles by Columnist Jim Czuprynski