Using SQL Performance Analyzer
By Ahmed Baraka
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Objectives
In this lecture, you will learn how to perform the following:
• Use SQL Performance Analyzer
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Performance Analyzer
Production Testing
Database Database
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Performance Analyzer
Production Testing
Database Database
Capture
SQL
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Performance Analyzer
Production Testing
Database Database
Capture
SQL
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Performance Analyzer
Production Testing
Database Database
Capture Execute
SQL SQL
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Performance Analyzer
Production Testing
Database Database
Capture Execute Make
SQL SQL Changes
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Performance Analyzer
Production Testing
Database Database
Capture Execute Make Execute
SQL SQL Changes SQL
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Performance Analyzer
Production Testing
Database Database
Capture Execute Make Execute Compare
SQL SQL Changes SQL Performance
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Performance Analyzer
Production Testing
Database Database
Capture Execute Make Execute Compare Fix
SQL SQL Changes SQL Performance Issues
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Performance Analyzer
Production Testing
Database Database
Capture Execute Make Execute Compare Fix
SQL SQL Changes SQL Performance Issues
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
About SQL Performance Analyzer
• Is used to assess impact of changes on SQL response time
• Is part of the Real Application Testing (separate licenses)
• Interfaces: OEM or DBMS_SQLPA
• SQL statements are assessed separately
• Can be executed on a testing database (recommended) or on a
production database
• It has the option to produce the execution plans only (with bind values)
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Using SQL Performance Analyzer
1. Create a tuning task:
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
SQLSET_NAME => 'WRKLD_STS', TASK_NAME => 'MYSPA')
2. Execute the task to collect the before-change data:
- For explain plan only, set the EXECUTION_TYPE to 'EXPLAIN PLAN'
exec DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => :tname,
EXECUTION_TYPE => 'TEST EXECUTE', EXECUTION_NAME => 'before');
3. Generate the before-changes report:
select DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME => :tname,
TYPE=>'TEXT',SECTION=>'SUMMARY') from dual;
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Using SQL Performance Analyzer
4. Perform the changes
5. Execute the after-changes task:
exec DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => :tname,
EXECUTION_TYPE => 'TEST EXECUTE', EXECUTION_NAME => 'after');
6. Generate the after-changes report:
select DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME => :tname,
TYPE=>'TEXT',SECTION=>'SUMMARY') from dual;
7. Compare between the two executions SQL performance data:
exec DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => :tname,
EXECUTION_TYPE => 'COMPARE PERFORMANCE');
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Using SQL Performance Analyzer
8. Generate the comparison report:
select DBMS_SQLPA.REPORT_ANALYSIS_TASK(TASK_NAME => :tname,
TYPE=>'HTML',SECTION=>'ALL') from dual;
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Comparing Between Two Specific Executions
• To define which task executions to compare:
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
TASK_NAME => :v_task,
EXECUTION_TYPE => 'COMPARE PERFORMANCE',
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST(
'execution_name1',
'before',
'execution_name2',
'after')
);
END;
/
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Setting the Comparison Metric
• To change the comparison metric (by default it is ‘elapsed_time’):
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
TASK_NAME => :v_task,
EXECUTION_TYPE => 'COMPARE PERFORMANCE',
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST(
'execution_name1',
'before',
'execution_name2',
'after',
'comparison_metric',
'buffer_gets'
)
);
END;
/
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Comparison Report Sample
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
SQL Performance Analyzer: Dictionary Views
View Description
DBA_ADVISOR_TASKS Contains details about the analysis tasks
DBA_ADVISOR_FINDINGS Contains analysis findings. Four types of findings:
performance regression, symptoms, errors, and
informative.
DBA_ADVISOR_EXECUTIONS Contains metadata information about task executions
DBA_ADVISOR_SQLPLANS Contains list of SQL execution plans
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
Summary
In this lecture, you should have learnt how to perform the following:
• Use SQL Performance Analyzer
Oracle© Database 12c SQL Tuning - a course by Ahmed Baraka
