11g Gathering the SQL Tuning Set
Oracle 11g New Features Tips by Burleson
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
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
This is an interface to the dbms_scheduler package, allowing
you to define and schedule a job.
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
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.
- 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
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_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.