Assessing Impact of Change on Workload Using SQL Performance Analyzer

Purpose

This tutorial describes how you can proactively tune your SQL using the SQL Performance Analyzer.

Time to Complete

Approximately 30 minutes

Topics

This tutorial discusses the following:

 Overview
 Prerequisites
 Setting Up the Environment
 Performing a Guided Workflow
 Running the SQL Tuning Advisor
 Comparing Replay Trial Against Tuned SQL
 Cleanup
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so, depending on your Internet connection, may result in a slow response time.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

SQL Performance Analyzer can be used to predict and prevent potential performance problems for any database environment change that affects the structure of SQL execution plans. The changes can include any of the following (but not limited to):

DBAs can use SQL Performance Analyzer to foresee SQL performance changes (execution plans, statistics) induced through above changes, for even the most complex environments. As applications evolve through the development lifecycle, database application developers can test changes to schemas, database objects, rewritten applications for example, to mitigate any potential performance impact.

SQL Performance Analyzer also allows for the comparison of SQL performance statistics.

In this tutorial, you create a task that compares the performance of a SQL workload before and after applying a patch. Any SQL regression as a result of patch application are tuned using the SQL Tuning Advisor to make sure that there is net improvement for the SQL workload on the system.

Back to Topic List

Prerequisites

Before starting this tutorial, you should first complete the following steps:

1.

Install Oracle Database 11g.

2.

Download and unzip the spa.zip into your working directory.

Back to Topic List

Setting Up the Environment

You first need to setup the environment that the SQL Performance Analyzer will use. Perform the following steps:

1.

Open a terminal window and execute the following commands from the directory where the SQL files are contained. The setup_demo.sh script creates the appropriate database objects and loads the data that will be used throughout this tutorial. This script may take a few minutes.

                               
cd /home/oracle/spa/sql_replay_100q
./setup_demo.sh
                            

Move your mouse over this icon to see the image

 

2.

Now you can run the run_workload_50q.sh script to generate some workload against the data you just loaded. Execute the following script:

                               
./run_workload_50q.sh 2
                            

Note: The '2' indicates the number of times the queries in the script will be executed. This script may take a few minutes.

Move your mouse over this icon to see the image

 

3.

You also need to create a SQL Tuning Set for the workload you just ran by running the create_sts.sql script.. Execute the following commands:

                               
cd other_scripts
sqlplus apps/apps
@create_sts
                            

Move your mouse over this icon to see the image

 

Back to Topic List

Performing a Guided Workflow

A Guided Workflow creates a SQL Performance Analyzer Task and executes some custom experiments using manually created replay trials. In this case, you have just installed a new patchset and you want to see whether any of the SQL has regressed. Perform the following steps:

1.

Open a browser and enter the following URL:

                               
http://<hostname>:1158/em
                            

Enter system as the username, specify the password oracle, and then click Login.

Move your mouse over this icon to see the image

 

2.

Click the Software and Support tab.

Move your mouse over this icon to see the image

 

3.

Under Real Application Testing, select SQL Performance Analyzer.

Move your mouse over this icon to see the image

 

4.

Under SQL Performance Analyzer Workflows, select Guided Workflow.

Move your mouse over this icon to see the image

 

5.

The first task you want to perform is to create a SQL Performance Analyzer task based on a SQL Tuning Set. Click the Execute icon for this task.

Move your mouse over this icon to see the image

 

6.

Enter OBE1 for the Name and click the flashlight for the SQL Tuning Set.

Move your mouse over this icon to see the image

 

7.

Select the HR_WORKLOAD SQL Tuning Set and click Select.

Move your mouse over this icon to see the image

 

8.

Click Create to create the SQL Performance Analyzer task.

Move your mouse over this icon to see the image

 

9.

Now you can create the replay the SQL tuning set for the initial environment which will create a baseline from which to compare after you make your changes. Click the Execute icon for the second step in the guided workflow. When you replay the STS, the SQL in the STS is test-executed one at a time against the environment without needing to re-run the application.

Move your mouse over this icon to see the image

 

10.

Enter OBE_BEFORE_CHANGE for the Name, check the checkbox for Trial environment established and click Submit.

Move your mouse over this icon to see the image

 

11.

The job is running. Click Refresh a few times until the job completes. The job may take a minute or two to run.

Move your mouse over this icon to see the image

 

12.

When the green check appears, your job is complete.

Move your mouse over this icon to see the image

 

13.

Now you need to make a change. This is where in our scenario, you will apply a patchset. Switch to your terminal window and execute the the make_changes.sh script:

                                 
cd ..
./make_changes.sh
                              

Move your mouse over this icon to see the image

 

14.

Now you can replay the SQL tuning set with the applied patchset so that you can perform the comparison. Click the Execute icon for the third step in the guided workflow.

Move your mouse over this icon to see the image

 

15.

Enter OBE_AFTER_CHANGE for the Name, check the checkbox for Trial environment established and click Submit.

Move your mouse over this icon to see the image

 

16.

The job is running. Click Refresh a few times until the job completes. You are now ready to compare the two replay jobs. Click the Execute icon for the fourth step in the Guided Workflow.

Move your mouse over this icon to see the image

 

17 .

Make sure Trial Name 1 is set to OBE_BEFORE_CHANGE and Trial Name 2 is set to OBE_AFTER_CHANGE. Accept the default Buffer Gets for Comparison Metric and click Submit.

Move your mouse over this icon to see the image

 

18 .

When task four completes, click the Execute icon for step five to view the comparison report.

Move your mouse over this icon to see the image

 

19 .

By applying the patchset, there is an improvement with most of the SQL however you have a slight regression. Click the bar chart for the Regression.

Move your mouse over this icon to see the image

 

20 .

There are four SQL statements that needs to be examined further. Click the breadcrumb to go back to the previous window.

Move your mouse over this icon to see the image

 

21 .

You can now run the SQL Tuning Advisor to tune the SQL to see if you can resolve the regressions found.

Move your mouse over this icon to see the image

 

Back to Topic List

Running the SQL Tuning Advisor

You can run the SQL Tuning Advisor for any SQL that is found to have regressed during the Guided Workflow. Perform the following steps:

1.

From the SQL Performance Analyzer Task Result page, click Schedule SQL Tuning Advisor.

Move your mouse over this icon to see the image

 

2.

Enter OBE_TUNE_REGRESSED_SQL for the Tuning Task Name and click OK.

Move your mouse over this icon to see the image

 

3.

Your tuning task was created successfully. Click the Advisor Central breadcrumb.

Move your mouse over this icon to see the image

 

4.

Click Refresh until the job has completed. This may take a few minutes to complete. When the job has completed, click the OBE_TUNE_REGRESSED_SQL link.

Move your mouse over this icon to see the image

 

5.

You see that a SQL Profile was recommended for each SQL statement. Click Implement All Profiles.

Move your mouse over this icon to see the image

 

6.

To confirm implementation, click Yes.

Move your mouse over this icon to see the image

 

7.

The SQL Profile was created. Click the Advisor Central breadcrumb.

Move your mouse over this icon to see the image

 

Back to Topic List

Comparing Replay Trial Against Tuned SQL

You can now compare the replay trial against the tuned SQL to see if there are still any regressions. Perform the following steps:

1.

Select the SQL Performance Analyzer link.

Move your mouse over this icon to see the image

 

2.

Under SQL Performance Analyzer Tasks, select the OBE1 link.

Move your mouse over this icon to see the image

 

3.

Under Replay Trials, click Create Replay Trial.

Move your mouse over this icon to see the image

 

4.

Enter OBE_AFTER_TUNE_REGRESSED_SQL for the Name, click the Trial environment established checkbox and click Submit.

Move your mouse over this icon to see the image

 

5.

The SQL replay job was created successfully. Click Refresh until the job completes. You can now run a comparison report between the OBE_BEFORE_CHANGE replay and the OBE_AFTER_TUNE_REGRESSED_SQL replay. Click Run Replay Trial Comparison.

Move your mouse over this icon to see the image

 

6.

Select OBE_BEFORE_CHANGE for Trial Name 1 and OBE_AFTER_TUNE_REGRESSED_SQL for Trial Name 2. Select Buffer Gets for the Comparison Metric and click Submit.

Move your mouse over this icon to see the image

 

7.

The comparison was created. Click the eyeglasses for the Comparison Report you just created.

Move your mouse over this icon to see the image

 

8.

Notice that there are no regressions at this point. So using the tuned SQL it is safe to apply the patchset because there will be a 87% performance improvement.

Move your mouse over this icon to see the image

 

Back to Topic List

Cleanup

Perform the following steps to cleanup your environment:

1.

Switch back to your terminal window and execute the reset_demo.sh script.

                               
./reset_demo.sh
                            

 

2.

Flush the shared_pool and buffer cache by executing the following commands

                                 
sqlplus / as sysdba
alter system flush shared_pool;
alter system flush buffer_cache;
                              

 

3.

At this point you can perform this OBE tutorial again. This time, however, you do not need to perform step one in the Setting Up the Environment topic. You can start with step two when you run the workload.

 

Back to Topic List

Summary

In this tutorial, you've learned how to:

 Perform a guided workflow
 Replay a SQL Tuning Set using the initial environment
 Apply a patch and then replay the SQL Tuning Set using the changed environment
 Compare the replays to see any regressions
 Run the SQL Tuning advisor to tune the SQL that is causing a regression
 Compare the replay trial against the tuned SQL to show that no regressions remain

Back to Topic List

 Move your mouse over this icon to hide all screenshot

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document