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? Ive 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. Ive 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.
Click for larger image
Click for larger image
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 its definitely possible. After some judicious and extensive SQL statement tracing, I determined exactly how the SPA wizard was submitting these tasks.
Ive 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, Id expect to see an impact on the optimizer cost for statements LDGN 5.1 and LDGN 5.4 because this parameter affects the CBOs decision to use an available index versus a table scan.
Ive listed the code I used to perform the analysis in Listing 3.2. These statements:
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 tasks 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 Im not satisfied with the appearance of the comparison report that the SQL Performance Advisor comparison reporting mechanism generates? For example, what if Id like to have the SQL statements that Ive 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 thats supplied for the execution_params argument of the DBMS_SQLPA.EXECUTE_ANALYSIS_TASK procedure. Ive illustrated this in the immediately prior example by selecting OPTIMIZER_COST as the comparison metric.
Here are the values that Ive tested for this parameter; as of this articles publication date, these arent yet documented in the Oracle 11g online manuals:
On the Horizon: SQL Plan Management
While SQL Performance Analyzers 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:
Achieving these goals is the focus of my next series of articles: Oracle 11gs new SQL Plan Management (SPM) features.
As this article series has shown, Oracle Database 11gs 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 Im hopeful that Ive given you a thorough grounding in the technical aspects of the features Ive discussed in this article, Im also sure that there may be better documentation available since its 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 Ive 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