Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 21, 2007

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

By Jim Czuprynski

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.

Click for larger image

Figure 3.7. Creating Parameter Change Comparison Task SPA_IPC_200

Click for larger image

Figure 3.8. SQL Performance Analyzer Task SPA_IPC_200 Execution Details

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:

Figure 3.9. SQL Performance Analyzer Task SPA_IPC_300 Execution Summary

Figure 3.9.1. SPA Task SPA_IPC_300 Result for Statement 1zu2z1n6b6ytx

Figure 3.9.2. SPA Task SPA_IPC_300 Result for Statement 1zu2z1n6b6ytx

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:

Table 3.1. SQL Performance Analyzer Analysis Dimensions




The estimated total cost to execute the statement, as computed by the cost-based optimizer


The number of times the statement has been executed


The total time it took to execute the statement (i.e. wall time). This is the default setting


The total amount of CPU time it took to execute the statement


The total time it took to parse the statement


The total buffer gets (i.e. buffers read + consistent reads) the statement needed to complete


The number of disk reads the statement performed


The number of disk writes the statement performed


The number of rows the statement processed


The number of fetches the statement performed

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.


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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM