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

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.

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

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.

Click the Software and Support tab.

Under Real Application Testing, select SQL Performance Analyzer.

Under SQL Performance Analyzer Workflows, select Guided Workflow.

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.

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

Select the HR_WORKLOAD SQL Tuning Set and click Select.

Click Create to create the SQL Performance Analyzer task.

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.

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

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

When the green check appears, your job is complete.

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

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.

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

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.

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.

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

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.

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

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

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

Enter OBE_TUNE_REGRESSED_SQL for the Tuning Task Name and click OK.

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

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.

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

To confirm implementation, click Yes.

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

Select the SQL Performance Analyzer link.

Under SQL Performance Analyzer Tasks, select the OBE1 link.

Under Replay Trials, click Create Replay Trial.

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

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.

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.

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

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.

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

./reset_demo.sh

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;

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.