BC remote Oracle DBA - Call (800) 766-1884  
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










11g Gathering the SQL Tuning Set

Oracle 11g New Features Tips by Burleson Consulting
July 13, 2008

Oracle 11g SQL New Features Tips

The SQL workload (the STS) can be thought of as a container for conducting and analyzing many SQL statements. The STS is fed to the SPA for real-world execution with before-and-after comparisons of changes to holistic "environmental" conditions, specifically CBO levels or changed init.ora parameters.

Internally, the SPA is stored as a database object that contains one or more SQL statements combined with their execution statistics and context such as particular schema, application module name, list of bind variables, etc. The STS also includes a set of basic execution statistics such as CPU and elapsed times, disk reads and buffer gets, number of executions, etc.

When creating a STS, the SQL statements can be filtered by different patterns such as application module name or execution statistics, such as high disk reads. Once created, STS can be an input source for the SQL Tuning Advisor.
Typically, the following steps are used to define the STS using the dbms_sqltune package.  The steps within the new 11g OEM screen for "guided workflow" are simple and straightforward, and serve as an online interface to the dbms_sqltune.create_sqlset procedure: 

1.  Options - Choose a name for your SQL tuning set (STS).  This encapsulated SQL workload is created using the dbms_sqltune.create_sqlset procedure. For example, the following script can be used to create a STS called SQLSET1:
exec dbms_sqltune.create_sqlset ('MYSET1'); 

2.  Load methods - Here is where you can choose the source for your SQL workload, and to take historical SQL statements from AWR.   

3.  Filter options - You can choose "filtering" conditions, based on your specific tuning needs.  For example, if your database is disk I/O bound, you might choose only SQL statements that have more than 100k disk reads. 

4.  Schedule - This is an interface to the dbms_scheduler package, allowing you to define and schedule a job.  

5.  Review - Here you can see the actual source calls to dbms_sqltune.create_sqlset and the dbms_scheduler.create_job procedure call syntax.

There is an interface to the SQL Performance Analyzer in the enterprise manager in the OEM Advisor Central area, and a number of new to Remote DBA_advisor views have been added in 11g which will display information from the SQL Performance Advisor. 

The technology behind SPA is encapsulated inside a new package called dbms_sqlpa. Here is an overview for the procedures of the dbms_sqlpa package:

  • CANCEL_ANALYSIS_TASK - This procedure cancels the currently executing task analysis of one or more SQL statements.
  • CREATE_ANALYSIS_TASK - This function creates an advisor task to process and analyze one or more SQL statements.
  • DROP_ANALYSIS_TASK - This procedure drops a SQL analysis task.
  • EXECUTE_ANALYSIS_TASK - This function & procedure executes a previously created analysis task.
  • INTERRUPT_ANALYSIS_TASK - This procedure interrupts the currently executing analysis task.
  • REPORT_ANALYSIS_TASK - This function displays the results of an analysis task.
  • RESET_ANALYSIS_TASK - This procedure resets the currently executing analysis task to its initial state.
  • RESUME_ANALYSIS_TASK - This procedure resumes a previously interrupted analysis task that was created to process a SQL tuning set.
  • SET_ANALYSIS_TASK_PARAMETER - This procedure sets the SQL analysis task parameter value.
  • SET_ANALYSIS_DEFAULT_PARAMETER - This procedure sets the SQL analysis task parameter default value.

In sum, the new 11g SQL Performance Analyzer is a great way to test for holistic tuning changes.  Remember, the savvy Oracle Remote DBA will always adjust their Oracle initialization parameters to optimize as much of the workload as possible before diving into the tuning of specific SQL statements.


This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr.

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

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



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.

Hit Counter