Business Case
It is not uncommon that different applications have their peaks during different business hours. To have individual servers for each application will cause many servers to be underutilized for the majority part of the time. Consolidating applications that have peak workload during different hours/days/weeks/months will therefore help us utilize database servers in a better way.
To simplify the exercise we have already performed a capture of the DW pluggable database and will perform a capture of the OLTP database. We will then perform a concurrent replay of both workloads.
NOTE! - Best Practices for Database replay is to perform SQL Performance Analyzer to validate that there are no regression due to the implemented change. Since we have been working with SQL Performance Analyzer in previous exercise then we will skip this part. The DW pluggable database in test has all tuning recommendations implemented from previous exercise and we have also made sure that there are no regressions in the OLTP pluggable database either.
Estimated Time to Complete Use Case: 30 minutes, recommended workflow
5. Capture Workload from Prod Database
5.1. You should already be logged on to Enterprise Manager. If you are not, please follow the instructions detailed in the “Configure SPA Quick Check” of this workbook. You should have shutdown prod12 db and started prod database at the end of the previous lab.
5.2. Navigate to Database Replay : From the menu, Enterprise -> Quality Management -> Database replay
Page 33 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
5.3. We have already performed capture for both DW and OLTP that you can use if short on time. However, we’ll walk you through capture workflow to get familiar with the steps.
Click on Captured Workload Click on Create
5.4. Check both boxes in Capture Prerequisites Click Next
5.5. Click “Add”.
Page 34 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
5.6. Enter:
Capture Name : OLTP_CAPTURE_10m_XX (where XX are your initials) Database Target : prod.oracle.com (use Search icon )
Select prod.oracle.com and click Select
Select Named Database credential: DB-SYS-GLOBAL
Select Named Database Host Credential: HOST-ORACLE (ORACLE)
Enter “Database Capture Storage Root Location, type in: /home/oracle/scripts/dbpack/RAT_CAP Click OK.
Note: Typing the directory name seemed to work, due to security privileges on folders.
Page 35 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
5.7. Click Next.
5.8. By default all user activity is captured but EM monitoring will be present during replay as well so this activity should be excluded. This will be done by default when starting capture from Enterprise Manager.
Click Next.
5.9. Change “Duration” to 10 minutes.
Click Next.
Page 36 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
5.10. Review the information.
Click Submit
5.11. It’s time for us to start the OLTP production workload.
Navigate to Enterprise -> Job -> Library
Page 37 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
5.12. Select job “DB_REPLAY_RUN_WORKLOAD”
Click Submit
5.13. Click Submit
5.14. The capture job has now started and will be running for about 10 minutes. You can review the status from Database replay Home page. The page does not have an auto refresh so please refresh it manually. Note it may take a few minutes to refresh the page.
When Capture status is changed to “Completed” in about 10 minutes, continue to the next step.
Page 38 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
5.15. We are now ready with capture from prod.
5.16. Next step is to replay the workload against test. To do this we need to stop prod.oracle.com and start test.oracle.com. Go to your terminal in VNC
Page 39 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6. Concurrent Replay
6.1 You should already be logged on to Enterprise Manager. If you are not, please follow the instructions detailed in the “Configure SPA Quick Check” of this workbook.
6.2 First we need to reset the test database for the replay 6.3 Go to Enterprise -> Job -> Library
6.4 Select “DB_REPLAY_SETUP”
Click Submit.
Page 40 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.5 Click Submit
6.6 Navigate to Database Replay
From the Menu, Enterprise Quality Management Database replay
Page 41 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.7 Go to Tab replay Task.
Click on Replay Task Click Create
Page 42 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.8 Enter:
Name: Replay_For_Consolidation_XX (where XX should be your initials) Select your created OLTP capture and the pre-created DW_CAPTURE_10m
Select “Copy workload to a new replay directory”
For each source workload in drop down menu make sure to use named credential:
“HOST-ORACLE(ORACLE)”
For Source Workload Directories:
Capture Name: select 'OLTP_CAPTURE_10m_XX' (where XX should be your initials) Select Named Credential “HOST-ORACLE”
For Replay Directory:
Host: em12.oracle.com
Select Named Credential “HOST-ORACLE”
Enter “Consolidated Replay Directory”: /home/oracle/scripts/dbpack/RAT_REPLAY Click Submit.
Note: Typing the directory name seemed to work, due to security privileges on folders.
Page 43 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.9 Click on the newly created replay task
Page 44 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.10 Click ‘Create’ to create a replay.
Enter a name for the Replay Select target database
If “test .oracle.com” is not preselected use following procedure to select the database:
Use to find the test database
In Target Name enter “test”
Click “Search”
Select “test.oracle.com”
Click “Select”
Page 45 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.11 Each workload is stored in our capture repository and is not currently preprocessed.
Click the icon for “Preprocess Workload”.
6.12 Select named Credential “DB-SYS-GLOBAL”
6.13 Check “Use an existing directory with Multiple workload subdirectories on this host””
Click Next
Page 46 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.14 Select Directory Object “RAT_REPLAY”
Click Next
6.15 Select: Credential Name: HOST-ORACLE(ORACLE) Click Next
6.16 Click Submit
Page 47 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.17 A preprocess job has now been created. You can monitor the job by Right click on the job and open in another tab. The job will take 2 – 3 minutes to complete.
When pre-processing has completed Click the icon for “Replay Workload”
6.18 For Database Credential use named credential “DB-SYS-GLOBAL”
For Database Host Credential use named credential “HOST-ORACLE(ORACLE)”
Click “Next”
Page 48 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.19 Select “Use an existing directory object”
Select Directory Object: RAT_REPLAY Click “Next”
6.20 Our replay is against the container database but our different workloads shall be replayed against different pluggable databases. Therefore we need to remap our connections against each pluggable database
Select “Capture Name”: DW_CAPTURE_10m
Change “SID=test” to “service_name=dw_test.oracle.com”
Click “Test Connection” and make sure you get
Page 49 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
Select “Capture Name”: OLTP_CAPTURE_10m_XX (where XX are your initials) Change “SID=test” to “service_name=oltp.oracle.com”
Click “Test Connection” and make sure you get . Click Next
6.21 Click “Add”
Page 50 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.22 Select
Host: em12.oracle.com Host Credential:
Use Named: “HOST-ORACLE(ORACLE)”
Enter the following information: Replay Client Host Configuration:
Client Oracle Home: /u01/home/oracle/db12c
Client Replay Directory: /home/oracle/scripts/dbpack/RAT_REPLAY Client Work Directory: /tmp
Database Credential
Use Named: “DB-SYSTEM-NORMAL”
Click “OK”
Page 51 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.23 Click “Next”
6.24 Click “Start Clients”
6.25 When client have been started click “Next”
Page 52 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.26 Click “Submit”
6.27 The replay has now started. If you want to monitor the replay Click on the “Replay Task:…” breadcrumb
Page 53 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.28 Click on the Replay Name
6.29 During the replay you will be able to see the progress and Replay statistics like number of user calls, and DBtime.
We can follow and compare “User Calls”, “Average Active Sessions” and Database time between capture and replay
Turn on Auto Refresh and set it to “1 Minute”. Replay statistics will only be updated once a minute. This page takes a few minutes to refresh in the beginning.
Page 54 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.30 When the replay has finished then we have the ability to generate a various types of reports for performance analysis. We have also the ability to generate reports from the replay.
There is a section for Replay divergence summary where we can see the overall quality of the replay. In general terms, a replay with less than 5% divergence in total should be seen as a very successful replay.
It is possible to drill down on Replay divergence where we can group errors or drill down to individual calls.
Click on “Reports” tab
If you have time please review the replay report.
You can even produce ASH reports for a deeper analysis.
Page 55 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.31 Let’s view the Database Replay Report. Click on “View”
Page 56 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.32 This report contains an overview information of individual captures and replays
Page 57 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.33 Let’s view the Replay Compare report. Click on “View”
6.34 This report includes a more detailed analysis of our replays. First section is an overview.
Page 58 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.35 Further down in the report we can see a more detailed analysis with breakdown on individual SQL statements.
Page 59 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.36 Top SQL statements divided by DB Time, CPU Time and Wait Time
6.37 We can also use Ash Analytics to generate Active reports. Active reports are HTML reports where we can perform drilldown.
So let’s choose the first report. Select “Total Workload Service and Module by workload” Click “View”
Page 60 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
6.38 This report shows when we had a peak workload and which application generated the workload.
We can see that The Data warehouse application is very well tuned but there is a spike in the OLTP application. As we can see we are actually exceeding the number of CPU’s during this peak and to be able to consolidate these applications we should perform a deeper investigation. But it is out of the scope for this exercise.
You have now learned how to investigate and troubleshoot performance issues by replaying and using the find fix and validate methodology. It should be possible to solve these issues as well.
This is an active report so we can change the display type to load map which will give us another view and a more detailed picture of where the DBtime is spent.
Page 61 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
You have now finished the consolidated replay lab.
We have now seen how you can use Real Application Testing Database Replay to validate a database consolidation using Database 12c pluggable databases. We have also seen the extensive reporting that will help us to find and analyze bottlenecks or peaks during certain workloads. We hope that this has given you a good overview on how to use the new consolidated replay feature.
Page 62 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
Appendix A. How to start EM 12c?
This lab requires targets to be started by running the script em_start.sh.
If you haven’t already, please make sure to run the em_start.sh script now.
Refer steps in the lab cheat sheet.
ssh or VNC to your VM as OS user oracle. The password for this account is oracle12.
Once logged in open a terminal window and change directory to scripts
$cd scripts
$./em_start.sh
Appendix B. How to Reset SPA Lab in Section B, C and D?
Go to Enterprise -> Job -> Library
Select “SPA_STATS_SETUP”
Page 63 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
Click Submit.
Click Submit
Page 64 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
Appendix C. Real Application Testing overview and Business Cases
A. SQL Performance Analyzer Optimizer Statistics
Business Case
When gathering new statistics it is not uncommon that the new statistics cause the optimizer to choose a new query plan. In most cases the new plan will be more efficient but sometimes it causes query regression.
As a DBA it is important to proactively predict how new statistics will change the overall performance in the database. With SQL Performance Analyzer (SPA) you have the ability to execute most of your SQL statements that occur in your database.
We have the ability to gather production SQL statements into SQL Tuning Sets either incrementally from Cursor Cache or AWR history for the period of time that we want to test. For the purpose of the lab, we have one pre-created SQL Tuning Set which is representative of the peak workload of this application. We have also gathered statistics in pending mode for validation. So, let’s see if the new statistics will change the performance for this application.
Workflow overview
In this exercise we will learn to use the SPA guided workflow for Optimizer Statistics validation, how to tune regressed statements and how to implement pending statistics on tables.
This exercise will be done against a pluggable database, DW, in a container database ‘test’.
The condition is that we have a warehouse based on 2 schemas ‘STAT1’ and ‘STAT2’. The warehouse has been populated and used for a couple of month and we have currently not updated the statistics. We want to make sure that when we publish new statistics then we would not be surprised with a performance regression.
Some of the activities that are associated with this exercise are time consuming so we have performed those tasks in advance for lab purposes to save some time. The following task has already been executed in the setup of the environment:
Gather Pending Optimizer Statistics on schema STAT1 and STAT2 Steps that we are going to perform in this exercise:
Run SPA Optimizer Statistics workflow
Compare performance of current statistics and pending statistics
Use SQL Plan base lines to fix plan regressions
Run SPA Optimizer Statistics workflow validate implemented SQL Plan Base lines
Compare performance of current statistics and pending statistics + SQL Plan Baselines implemented
Implement (Publish) new statistics
Page 65 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Performance Management Lab Lab
B. Consolidated Database Replay
Business Case
During consolidation testing phase it is important to know that the server will handle all workloads that shall be consolidated. On this front we have the Consolidated Database Replay feature that will help us to predict the workload behavior. We can test different scenarios to make sure that we are as much prepared as possible. Examples of scenarios to test could be:
Worst case scenario with simultaneous peaks.
Growing user activity
Consolidating more applications
In this use case we are going to consolidate two workloads that have been pre-captured on different databases and we are going to concurrently replay them in a Container database. For the purpose of the lab, we’ve already setup the test system and the Container database appropriately to the point-in-time of captures so that replay can be started.
Lab Overview
Objective:
The objective of this document is to provide high-level guidelines on new features associated with Real Application Testing in Oracle Enterprise Manager Cloud Control 12c.
To perform real-world testing of Oracle databases, by capturing production workloads and replaying them on test systems enables you to perform real-world testing quickly and accurately. This allows enterprises to assess the impact of any planned system change before deploying it on production reliably.
Additionally, with Oracle Database 12c we have the Multitenant capability that will make it possible to consolidate application into different pluggable databases within one container database. This allows us to reduce the footprint of each database and reduce resource usage on the server.
In this lab we will learn how to perform multiple replays against the same database to validate how the new database and the server will handle the workload.