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.
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:
-
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 tasks 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 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:
Table 3.1. SQL Performance
Analyzer Analysis Dimensions
|
Dimension
|
Description
|
OPTIMIZER_COST
|
The estimated total cost
to execute the statement, as computed by the cost-based optimizer
|
EXECUTIONS
|
The number of times the
statement has been executed
|
ELAPSED_TIME
|
The total time it took to
execute the statement (i.e. wall time). This is the default setting
|
CPU_TIME
|
The total amount of CPU
time it took to execute the statement
|
PARSE_TIME
|
The total time it took to parse
the statement
|
BUFFER_GETS
|
The total buffer gets
(i.e. buffers read + consistent reads) the statement needed to
complete
|
DISK_READS
|
The number of disk reads
the statement performed
|
DIRECT_WRITES
|
The number of disk writes
the statement performed
|
ROWS_PROCESSED
|
The number of rows
the statement processed
|
FETCHES
|
The number of fetches
the statement performed
|
|
|
|
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:
-
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 11gs new SQL Plan Management (SPM) features.
Conclusion
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
»
See All Articles by Columnist Jim Czuprynski