Replay multiple workloads concurrently against Pluggable Databases in the Container Database
Estimated Time to Complete Use Case: 10 minutes. Note this lab is optional, if you have completed the other recommended labs, you can go through this exercise.
5. Create a Replay Task
5.1 You should already be logged on to Enterprise Manager. If you are not, please follow the instructions detailed in the “SQL Performance Analyzer Optimizer Statistics” of this workbook.
5.2 First we need to reset the test database for the replay Navigate to the Job library
From the Menu, Enterprise Job Library
Page 30 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.3 Search for Reset jobs.
In Name enter ‘res’, click Go
Select “RESET_TEST_FOR_REPLAY”. Click “Submit”
5.4 Click “Submit”
The job will be executed in the background so you can proceed to Database replay.
5.5 Navigate to the Database Replay.
From the Menu, Enterprise Quality Management Database Replay
Page 31 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.6 Choose Tab “Replay Task.
Click “Create” to create a new Replay Task.
5.7 In the screen, enter details about the new Replay Task:
Name : CONSOLIDATED_REPLAY_02
(Note: If a replay Task with this name already exists on your environment, you can create a replay Task with new name, such as CONSOLIDATED_REPLAY_<Your Initials>)
Select “DW_CAPTURE_5m_B” and “OLTP_CAP_5min_B”
DW_CAPTURE_5m_B and OLTP_CAP_5min_B are pre-created captures that we will use in this exercise Click ‘Submit”
Page 32 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.8 Click on the newly created Replay Task.
5.9 Click ‘Create’ to create a replay.
Enter a name for the Replay Select target database Use to find the test database
Page 33 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
In Target Name enter “test”
Click “Search”
Select “test.oracle.com”
Click “Select”
Page 34 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.10 Each workload is stored in our capture repository and is not currently preprocessed.
Click the icon for “Preprocess Workload”.
5.11 Even if our workloads are stored on the same machine, we need to move them to a separate replay directory that will be used for concurrent replay. Let’s copy our captures to the replay folder.
Check “Copy the workload directories to this host from another host”
Click Next
Page 35 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.12 For “Current Location of The Workload Directory” use Named Credential Select: Credential Name: HOST-ORACLE
For “New Location of the Workload Directory”
Enter: Host: em12.oracle.com ( use Search icon ) Directory: /home/oracle/scripts/dbpack/RAT_REPLAY Select: Credential Name: HOST-ORACLE
Search for the host. Click Select “em12.oracle.com”
Click ‘Select’
Page 36 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.13 Wait until workload has been copied
5.14 Select Directory Object : “RAT_REPLAY”
This directory object is pre-created for the container database.
Click “Next”
Page 37 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.15 In Host Credentials
Select “Named” and choose “HOST-ORACLE”
Click “Next
5.16 Click “Submit”
Page 38 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.17 It is now time to replay our workloads. Note for lab purposes the test system and Container database are appropriately setup for replay.
Click the icon for “Replay Workload”
Page 39 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.18 The workload was already copied when we did the preprocessing, so we are going to use that directory Select “Use an existing directory….”
Click “Next”
5.19 Select Directory Object: RAT_REPLAY Click “Next”
5.20 Click “Next”
Page 40 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.21 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_5min_B Change “SID=test” to Service_name = dw.oracle.com
Click “Test Connection” and make sure you get in the upper left corner.
Page 41 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
Select “Capture Name”: OLTP_CAPTURE_5min_B Change “SID=test” to Service_name = oltp.oracle.com
Click “Test Connection” and make sure you get in the upper left corner.
Click Next
5.22 Click “Add Replay Client Hosts”
Page 42 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.23 Click “Go”
Select : em12.oracle.com Click “Select”
5.24 Click on “No” to configure replay clients
Page 43 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.25 Add information for replay client hosts. Make sure that all values are correct. If you have the opportunity to copy from this document with cut and paste, please do so.
Note! if there are any errors or extra spaces, you might get an error and will have to restart from the beginning of Replay Task flow, so make sure to pay extra attention.
Client Oracle Home: /u01/home/oracle/db12c
Client Replay Directory: /home/oracle/scripts/dbpack/RAT_REPLAY Client TNS_ADMIN Directory: /u01/home/oracle/db12c/network/admin Client Work Directory: /tmp
Click “Apply”
Click “Close”
Page 44 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.26 Click “Next”
5.27 For “Database Host Credentials” Select: HOST-ORACLE For “Storage Host Credentials” Select: HOST-ORACLE Click “Next”
Page 45 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.28 Click “Submit”
5.29 The replay has started and we can now monitor the replay
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
Page 46 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.30 When the replay has finished then we have the ability to generate a various types of reports for performance analysis.
The replay and export of AWR data will take about 10 minutes to finish. We have therefore a canned replay you can use to view replay report.
5.31 To view reports in the canned replay
Click on the “Database Replay” bread crumb
5.32 Click on “RAT_REPLAY_CANNED”
Page 47 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.33 Click on “CANNED_REPLAY_1”
5.34 From the replay home page you have the option to drill down and investigate divergence which is visible in the lower left section.
But we are supposed to look at our replay reports so click on “Reports” tab
Page 48 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.35 Let’s view the Database Replay Report. Click on “View”
Page 49 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.36 This report contains an overview information of individual captures and replays
Page 50 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.37 Let’s view the Replay Compare report. Click on “View”
5.38 This report includes a more detailed analyze of our replays. First sections is an overview
Page 51 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.39 Further down in the report we can see a more detailed analyze with breakdown on individual SQL statement
5.40 Top SQL statements divided by DB Time, CPU Time and Wait Time
Page 52 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
5.41 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”
5.42 This report shows when we had peak workload and which application that generated the workload.
We can see that our peaks are caused by the Data warehouse application and by the characteristics of this workload it looks like we have some heavy reports executed on frequent intervals.
Page 53 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database 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 have given you a good overview how to use the new consolidated replay feature.
Page 54 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
Appendix A. How to start the database Workload?
This lab requires targets to be started by running the script 1-db_lab_start.sh.
If you haven’t already, please make sure to run the 1-db_lab_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
$./1-db_lab_start.sh
Page 55 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
Appendix B. 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 C. DB Performance Overview and Business Cases
Oracle Enterprise Manager Express
Oracle Database 12c introduces Oracle Enterprise Manager Express, or simply EM Express, a web based GUI tool optimized for performance Management. EM Express is extremely light weight and is built inside the database so it doesn’t require any additional install. With only a 20 MB disk footprint, there is no resource usage when it is not invoked or used. You can use Oracle Enterprise Manager Express to manage a single database while Cloud Control 12c can be used to manage many databases and targets.
Oracle Enterprise Manager Database Express includes Performance Hub, a completely new unified interface for performance monitoring. It is the single pane of glass view of database performance with access to ADDM, SQL Tuning, Real-Time SQL Monitoring and ASH Analytics under the same hood. A flexible time picker allows the administrator to seamlessly switch between Real-Time and Historical views of database performance. For Oracle RAC databases, there is an additional RAC tab that allows the database administrator to monitor cluster related performance problems.
Diagnosing a slowly performing system or a sudden degradation in performance is a time consuming task and often the activity where the DBAs spend most of their time. A major component of the challenge is to simply identify what is causing the problem in our increasingly complex business environments. There is a need to sample the current state of all the sessions that are active in our systems, but analyzing this data for transient problems that occur for very short durations is not simple. EM Express allows the administrator to rollup, drilldown, and slice or dice performance data across various performance dimensions that are captured along with the session state. With the ability to create filters on various dimensions, identifying performance issues has never been easier.
Page 56 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
Page 57 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database Management Lab Lab
Appendix D. 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 58 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c
Database 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 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.