Maximizing Performance for Oracle Database 12c using Oracle Enterprise Manager

(1)

Maximizing Performance for Oracle

Database 12c using Oracle Enterprise

Manager

Björn Bolltoft

(2)

Page 2 of 58 ORACLE ENTERPRISE MANAGER Cloud Control 12c

Database Management Lab Lab

Database Performance Management ... 3

A. Performance Hub ...4

B. Real-Time Database Operations Monitoring ... 11

C. Tuning a SQL in a PDB ... 14

Real Application Testing ... 18

D. SQL Performance Analyzer Optimizer Statistics ... 19

(3)

Database Performance Management

Objective:

The objective of this lab to provide exercises designed to

showcase the new database performance management

capabilities in Oracle Enterprise Manager Cloud Control 12c and

Oracle Enterprise Manager Express.

Functional Coverage:

In this lab you will go through new features in the following

functional areas:

Database Performance management

A. Performance Hub

B. Real-Time Database Operations Monitoring

C. Tuning a SQL inside a PDB

Lab environment setup

(4)

A. Performance Hub

Estimated Time to Complete Use Case: 15 minutes

Business Case

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 lightweight and is built inside the database so it doesn’t require any additional install. 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.

In this lab we will use an Oracle Database 12c Container database with few pluggable databases. You will be logging in as a CDBA.

Figure 1: A Container database with PDBs

Understanding the Performance Hub

1.1 Log into EM Express. Please type

https://<<your assigned i/p address>>: 7100/em/login

(5)

1.2 Click on the PDB tab on the Performance region of the database homepage.

Here you will notice the activity by the different open PDBs, such as SALES, HR and FINANCE

(6)

Make sure to slide the time picker on an area of high usage (e.g., CPU, IO or Waits). In the figure above we see the DBA has identified the region with high user I/O. Notice how the corresponding selected time window also changes in the summary section.

Review information on the regions: ‘Active Sessions’, ‘Host’, ‘Memory’ and ‘I/O’. Depending on the time period that you have selected at the top the corresponding periods are also selected in each region below. 1.4 Select the “Activity” tab on the left side of the page. This will open the ASH Analytics view of the

performance page

You can also resize the slider to entirely cover the time period of your interest. Notice the graph at bottom, it is providing more detailed view of the time window you selected. By Default the wait class dimension is selected. On the right hand side of the graph you have a list of wait classes for the time window you selected (blue for user I/O, green for CPU etc). Notice how the color changes if you hover over either the menu or the graph to highlight the particular wait class.

Wait class isn’t the only dimension you can drill into the performance issue by. Let’s say you wanted to identify the SQL that was causing the biggest performance impact. You can do that by clicking the drop down list and changing the top dimension from wait class to SQL ID.

1.8 Select the SQL ID dimension from the list of available dimensions using the dropdown box that is currently

(7)

Hover your mouse on top of the SQL (one at the bottom) and you will be able to see how much activity is consumed by this SQL.

Now using the same list of filters select the PDB dimension. Session Identifiers  PDB

What do you see?

(8)

1.6 Click on the Workload tab next to the Activity tab

1.6.1 Notice the three sections “Workload Profile”, “Sessions” and “Top SQL”.

1.6.2 Click on “Parse Calls” tab in “Workload Profile” region. Notice the hard and soft parse behavior. 1.6.3 Click on “Redo Size” tab in the same region. You can determine the redo generation rate from this graph

1.6.4 Click on “Open Cursors” in “Sessions” region. This can help you to find out the pattern of open cursors in the database at a given point of time.

1.6.5 Observe the “Top SQL” Section. You can find the top SQL by “Database Time” in this section with the sql-id and sql text.

(9)

You can see all the executed SQLduring that time along with different attributes like ‘user’,’Start’,’Ended’ etc. The test next to the @ sign indicates the name of the PDB. Click on any SQL of your choice ( e.g. 6kd5jj7kr8swv)

It will navigate you to show the details of this particular query. You can see the plan, parallelism and activity of the query. Click “Plan” tab in the details section. You can see the plan of this query in graphical mode.

(10)

1.7.2 Click on the ellipsis icon […] image next to SQL Text in the General Section. You can see the query text which got executed.

1.7.3 Click on “Save” button on top right corner of the page. This will help you to save this monitored execution in “.html” format, which you can use it to share or to diagnose the things offline.

1.7.4 Click on “Navigate to SQL Details” on top. This will take you to the SQL Hub. Verify the details on this page for the particular SQL selected. You will find all the details now on this page for this sql, such as the activity , plan statistics, monitored SQL and Plan control.

1.7.5 Select Performance Hub from the Performance Menu at the top.

1.8 Click on the Current ADDM Findings Tab

(11)

B. Real-Time Database Operations Monitoring

Environment Details:

The target database is running a load on the sample schema provided via the Examples (or companion) software accompanying the Oracle database software. There are other schemas created to simulate specific performance scenarios.

Estimated Time to Complete Use Case: 8 minutes

Business Case

Real-Time Database Operations Monitoring, a new feature introduced in Oracle database 12c, allows an administrator to monitor long running database tasks such as batch jobs, ETLs etc as a composite business operation. This feature tracks the progress of SQL and PL/SQL queries associated with the business operation being monitored. Developers and DBAs can define business operations for monitoring by explicitly specifying the start and end of operation or implicitly through the use of tags that identify the operation.

Start the Database Operation.

 Open a VNC window and navigate to your i/p address (use display port 2). Use password oracle12.

 You may also ssh using a terminal window and log in as oracle/oracle12.

 Once Logged in perform the following [oracle@em12 ~]$ cd scripts

[oracle@em12 scripts]$ . ./TEST_DB_ENV

[oracle@em12 scripts]$ cd load/frame/queries/awrv [oracle@em12 awrv]$ pwd

/home/oracle/scripts/load/frame/queries/awrv

Using SQLPlus connect to the sh2 account. [oracle@em12 awrv]$ sqlplus sh2/sh2@sales

Open the file (!vi DBOP.sql) from the SQL prompt and then review the content of the file. At the beginning of the file you will notice how we have tagged the operation with dbms_sql_monitor.begin_operation and ended it with dbms_sql_monitor.end_operation Now execute the file @DBOP.sql

2.1 You should already be logged on to Enterprise Manager Express. If you are not, please follow the instructions detailed in earlier section of this workbook.

(12)

Review the list of currently executing SQLs are visible click on the DBOP_DEMO name. This will open the DBOP named DBOP_DEMO.

Note: You may need to scroll down or select “Database operations” from the type dropdown.

2.3 Review the details of the Database Operations.

(13)

2.4 Click on the Activity tab.

You will see all the activity is filtered by this SQL. By default the Wait Class dimension is displayed.

2.5 Log Out of EM Express.

(14)

C. Tuning a SQL in a PDB

Environment Details:

The target database is running a load on the sample schema provided via the Examples (or companion) software accompanying the Oracle database software. There are other schemas created to simulate specific performance scenarios.

Lab environment setup

Your environment should have a workload running. If EM 12c is not

running please refer to Appendix B for the steps to start EM 12c.

Estimated Time to Complete Use Case: 10 minutes

Business Case

This use case is intended to give an idea of how the pluggable database administrator will tune queries in a PDB. We are running a workload and this flow will help you to identify a Top SQL and then tune it using SQL Tuning Advisor. The PDBA will have no access to the Container and his/her view is only restricted to the queries running in the PDB assigned to this user.

3.1 Log into Enterprise Manager Cloud Control.

3.2 Please note that you will be using a new URL: https://Your_Assign_IP:7777/em

(15)

3.3 Once logged into Enterprise Manager. Select Targets  Databases . You should see only the database test.oracle.com. Click on the expand icon on the left and click on the pluggable database

test.oracle.com_SALES

3.4 You should now see the Database Home page.

(16)

3.6 Select the checkbox by the side of the SQL showing highest Activity. Now Schedule SQL Tuning Advisor by clicking on the Tune SQL button.

(17)

.

3.8 Once the job completes. You should see the recommendations for either creating a profile or an index.

3.9 Implement the SQL Profile recommendation. SQL Profiles are a great way of tuning a SQL without creating any new objects or making any code changes.

3.10

(18)

Real Application Testing

Objective:

The objective of this lab to provide exercises designed to

showcase the new Real Application Testing capabilities in Oracle

Enterprise Manager Cloud Control 12c and Database 12c.

Functional Coverage:

In this lab you will go through features in the following functional

areas:



SQL Performance Analyzer Optimizer Statistics

SQL Performance Analyzer Gather Optimizer Statistics Validation



Consolidated Database Replay

Replay multiple workloads concurrently against Pluggable

Databases in the Container Database



Appendix A

(19)

D. SQL Performance Analyzer Optimizer Statistics

SPA Lab Workflow

Estimated Time to Complete Use Case: 9 minutes, Recommended workflow

4. Execute SPA task using Optimizer statistics workflow 4.1 Login using username and password oracle / oracle12

(20)

4.3 Search for database Test by enter: Name : “test”

Click

Click on “test.oracle.com_DW” pluggable database

(21)

4.5 Click “Optimizer Statistics” link.

4.6 Enter:

Task Name : VALIDATE_OPTIMIZER_STATS_01

SQL Tuning Set : SYSTEM.PENDING_STATS_WKLD ( use Search icon )

(22)

4.7 Mark the “PENDING_STATS_WKLD” Tuning Set and click select.

(23)

4.9 The SPA task is now in progress and it will take a minute or two to complete.

When Last Run Status have changed to Completed: Click on your SPA task name.

4.10 The workload in the Tuning Set has now been executed using both current statistics and the pending statistics.

(24)

4.11 As we can see the majority of our statements had unchanged performance. We have a significant improvement but most important to notice is that we have regression as well. Since this application has used stale statistics for a long period, then it would be good to have new statistics implemented. So let’s see if we can tune these regressed statements. We can now drill down on individual statements to see details on execution plans and execution statistics and compare old and new execution plans. For the purpose of lab, we will use SQL Plan Baselines to remediate the identified regressions. Note one can also use SQL Tuning Advisor to remediate regressions by implementing SQL Profile recommendations

Click on “Create SQL Plan Baseline”

4.12 We can see that there are two queries with regression more than 11 % so these are the queries where we need to create SQL Plan Baselines.

(25)

4.13 The job has now been submitted so let’s validate that our changes have the effects that we planned. This will be done with a new execution of the SPA Optimizer Statistics Workflow

Click the “SQL Performance Analyzer” bread crumb

(26)

4.15 Enter:

Task Name : VALIDATE_OPTIMIZER_STATS_02

SQL Tuning Set : SYSTEM.PENDING_STATS_WKLD ( use Search icon ) Comparison Metric: Buffer Gets

Check “Pending optimizer statistics collected” Click Submit

4.16 The SPA task is now in progress and it will take a minute or two to complete.

(27)

4.17 The workload in the Tuning Set have now been executed using both current statistics and the pending statistics + SQL Plan Baselines that were implemented in previous steps.

Click on the glasses for Comparison Report

4.18 As you can see we have now resolved the regression that was caused by new statistics. So it is safe to publish the collected statistics.

(28)

4.19 We can now change statistics for all tables where we have pending statistics. For the scope of this exercise we will only change statistics for a couple of tables.

In the search option change: ‘Type’: Table,

‘Schema’: STAT1

‘Scope’ : Pending click “Go”

Change Action to Publish one by one publish statistics for tables:

STAT1.OUTLETS_TAB3, MANUFACTURING_TAB1, DISTRIBUTION_DEPT_TAB1, DISTRIBUTION_DEPT_TAB2 and DESIGN_DEPT_TAB2” by mark the select field for the table and click “Go”

Note: You have to publish statistics for each table one at a time. A multi-select button will be available in future releases of EM, an enhancement has been logged for this purpose.

You have now learned how to work with SPA. As you can see there are several Guided Workflows that will help you during your analysis and verify that you can implement new changes in production with

confidence.

(29)

E. Consolidated Database Replay (Optional, time

permitting)

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

(30)

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.

(31)

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

(32)

5.8 Click on the newly created Replay Task.

5.9 Click ‘Create’ to create a replay.

(33)

In Target Name enter “test” Click “Search” Select “test.oracle.com”

(34)

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.

(35)

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

(36)

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.

(37)

5.15 In Host Credentials

Select “Named” and choose “HOST-ORACLE” Click “Next

(38)

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”

(39)

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”

(40)

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

(41)

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

(42)

5.23 Click “Go”

Select : em12.oracle.com Click “Select”

(43)

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”

(44)

5.26 Click “Next”

(45)

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

(46)

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

(47)

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.

(48)

(49)

(50)

5.37 Let’s view the Replay Compare report. Click on “View”

(51)

5.39 Further down in the report we can see a more detailed analyze with breakdown on individual SQL statement

(52)

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.

(53)

You have now finished the consolidated replay lab.

(54)

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

(55)

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.

(56)

(57)

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

(58)

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.

Maximizing Performance for Oracle Database 12c using Oracle Enterprise Manager