Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation







 Oracle DBMS_SQLPA Performance Analysis
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Of the five statements, four of them improved, and one had no change. A section for one of the statements that improved appears as follows.


Figure 8.4:  Results Table


The execution plan section shows that a full table scan was used before and the index was used after the change.


Figure 8.5: Execution Plan Comparisons

Miscellaneous items

All in all, this was not too hard to setup and get running. There may be odd errors along the way when varying the input parameters (ORA-01478 array bind error), so try using a different format with respect to named versus positional parameters.


If there is a need to start over, use the DELETE or DROP_SQLSET procedure in DBMS_SQLTUNE. Try to make the statements as clean as possible such as avoiding extra SELECT statements and making the statement identification easy to find. For example, a table could be aliased with XXX, so that string would be unusual to find in Oracle but easy to filter on.


Enterprise Manager offers a streamlined interface into the SQL Performance Analyzer. Even though the tuning set and report were manually created, they will still be available in OEM. As an example, drilling down to TASK_35 shows a graphical comparison between sets. The two regressed SQL statements were related to internals of executing the two packages.


Figure 8.6: SQL Performance Analyzer Graphical Results


As mentioned in the beginning, SPA can capture differences due to most anything that affects an execution plan. Aside from DDL operations such as index creation or using hints, two other major elements can factor in to changes. One is the compatibility parameter and the other is generic parameter changes.


A change in the optimizer setting is specific enough to not be counted as a parameter change. Although it is a parameter, the setting is intrinsic to how the optimizer works as a whole. An example of a normal parameter change would be changing the OPTIMIZER_INDEX_COST_ADJ or DB_FILE_MULTIBLOCK_READ_COUNT settings.


Now that the API interface is now visible, that is, the actual DBMS_SQLPA and DBMS_SQLTUNE to create the SQL tuning set, the guided workflow in Enterprise Manager will be much easier to follow along.


Figure 8.7:  Workflow

To sum up so far, the SQL Performance Analyzer can be used to help validate or explore changes. It can help overcome a 100% copy of the production environment, and unlike ADRCI, SPA can be back ported to releases prior to 11g. MetaLink note 560977.1, “Real Application Testing Now Available for Earlier Releases,” details the applicable versions.


After applying a patch via the opatch utility, one can capture on the older versions. However, only replay on version 11g and higher.


Finally, since SQL Performance Analyzer falls under Real Application Testing, it must be licensed. Database Replay, SQL Performance Analyzer and SQL Tuning Sets (STS) are licensable. STS can be used if the DBA licensed the Tuning Pack.

Other Utilities

The advisory framework introduced in Oracle 10g opened up quite a few portals into Oracle’s internals. The RDBMS is far more instrumented than it has ever been, and hopefully that trend will continue. If one works in a split, with respect to releases, environment, how many times one wished feature X in 10g were available in 8i? That is not just limited to advisory and diagnostics, either. Flashback tabling to before some SCN is much easier to perform than a tablespace point in time recovery . It can take hours to recover from an errant commit in an older version while the time in a newer one could be a matter of seconds.

r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.


Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software










BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.