Tip

Inside the Oracle 11g SQL Performance Advisor, part 2

This is a preview of the forthcoming book Oracle 11g New Features from Rampant TechPress, featuring John Garmany, Steve Karam and Lutz Hartmann.

Go back to "Inside

    Requires Free Membership to View

the Oracle 11g SQL Performance Advisor, part 1."

The SPA treatment

The SQL Performance Analyzer (SPA) allows the DBA to define the SQL Tuning set (the STS) as a source for the test (usually using historical SQL from the AWR tables).

The SPA receives one or more SQL statements as input and provides advice on which tuning conditions have the best execution plans, gives the proof for the advice, shows an estimated performance benefit, and allegedly has a facility to automatically implement changes that are more than three times faster than the "before" condition.

Gathering the SQL Tuning set

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 an 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 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 100,000 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 dba_advisor views have been added in 11g which will display information from the SPA.

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 and 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 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.

Oracle 11g guided workflow screen

The OEM screen for the SPA guided workflow contains a predefined set of steps for holistic SQL workload tuning:

  1. Create SQL Performance Analyzer Task, based on SQL Tuning Set.
  2. Replay SQL Tuning Set in initial environment.
  3. Create replay trial using changed environment.
  4. Create replay trial comparison (using trials from steps 2 and 3).
  5. View trial comparison report.

Using the guided workflow functionality, we can take our SQL tuning set and execute it twice (before and after), saving the SQL execution results (disk reads, buffer gets) using some of the common SQL execution metrics found in the dba_hist_sqlstat table:

DBA_HIST_SQLSTAT Columns:

FETCHES_TOTAL           
END_OF_FETCH_COUNT_TOTAL
SORTS_TOTAL             
EXECUTIONS_TOTAL        
LOADS_TOTAL             
INVALIDATIONS_TOTAL     
PARSE_CALLS_TOTAL       
DISK_READS_TOTAL        
BUFFER_GETS_TOTAL       
ROWS_PROCESSED_TOTAL    
CPU_TIME_TOTAL          
ELAPSED_TIME_TOTAL      

Guided Workflow Items

EXECUTE_ELAPSED_TIME
ELAPSED_TIME
PARSE_TIME
EXECUTE_ELAPSED_TIME
EXECUTE_CPU_TIME
BUFFER_GETS
DISK_READS 
DIRECT_WRITES
OPTIMIZER_COST

Here it's important to note that the guided workflow does not measure these important SQL execution metrics such as sorts and fetches.

Comparing the SPA results

The final step in SPA allows the DBA to quickly isolate suboptimal SQL statements and tune them with the 11g SQLTuning Advisor. When viewing the results, you can use OEM for a visual display of all delta values between the execution run, but most important, you can do a side-by-side comparison of the before-and-after execution plans.

Oracle has always been ahead of the curve in automating well-structured DBA tasks, and the SPA is just the latest incarnation in real-world SQL tuning tools. Tools such as SPA free up the DBA to pursue other important DBA tasks, relieving them of the tedium of individually tuning SQL statements.

This was first published in July 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.