Oracle Database Performance Management Best Practices Workshop. AIOUG Product Management Team Database Manageability

(1)

Oracle Database Performance

Management Best Practices

Workshop

AIOUG

(2)

Page 2 of 65 ORACLE ENTERPRISE MANAGER Cloud Control 12c

Database Performance Management Lab Lab

Oracle DB Performance Management Lab ... 3

A. Configure SPA Quick Check ...6

B. Find: Performance Problem Diagnosis ... 10

C. Fix: Tune Regressed SQL ... 16

D. Validate: Validate Tuning Using SPA Quick Check on Production ... 19

E. Concurrent Replay [Optional] ... 32

Appendix A. How to start EM 12c? ... 62

Appendix B. How to Reset SPA Lab in Section B, C and D? ... 62

(3)

Oracle DB Performance Management Lab

Objective:

The objective of this lab is to provide exercises designed to showcase database performance

management capabilities (toolset), methodology and associated best practices using Oracle

Enterprise Manager Cloud Control 12c.

Students will learn to apply the “Find-Fix-Validate” performance tuning methodology to diagnose

performance bottlenecks, tune SQL statements with poor performance in a safe way directly on

production and make sure that the overall result of the tuning activity doesn’t cause new

regression.

By improving performance we will be able to free enough resources on the server to perform a

server consolidation. We will therefore validate that there are enough capacity on the server after

consolidation.

Functional Coverage:

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

Database Performance management

A. Configure SPA Quick Check

B. Find: Performance Problem Diagnosis

C. Fix: Tune Regressed SQL

D. Validate: Validating Tuning Using SPA Quick Check

(4)

Lab environment setup

Your environment has already been setup for the lab. Below are the

details of the machine, VNC, and EM URLs.

Oracle Enterprise Manager URL:

https://XX.XXX.XX.XXX:7802/em

username: dbuser

password: oracle12

PDB

“prod12 _DW”

Credentials: Select Named Credential use “DB_SYS_GLOBAL

Host Named credential: HOST_ORACLE

VNC Viewer

Use VNC Viewer to connect to the machine allocated to your machine, localhost:1, password is

“oracle12”

Putty (Alternative to VNC Viewer)

Log on to your environment localhost:22 using Putty

Username: oracle

(5)

1. Logon with VNC to your assigned machine (machine_ip-address:1)

Password: oracle12

2. Open a terminal: Right click in VNC window

3. Change directory to scripts, the scripts to start / stop databases are here, note you’re using

prod12db, but since the database is already started you can skip this step

(6)

A. Configure SPA Quick Check

Estimated Time to Complete Use Case: 15 minutes

Business Case

Changes in production should always be validated to make sure that the change doesn’t cause any regression. When refreshing Optimizer Statistics for example, we want to make sure that not just the overall performance becomes better but also that no SQL statement, especially business critical ones have regressed. It doesn’t help us to increase the overall performance by 50% if key order entry application degrades in performance as a result of refreshing statistics.

To make sure that tuning exercises are completed successfully, we need to validate it by executing SQL

statements that cover most of the workload. SPA Quick Check uses a predefined SQL Tuning Set and optimized testing configuration for use in a production environment. We are going to configure SPA Quick check with a pre collected SQL Tuning Set and use the most optimal method for executions.

In this lab we will use an Oracle Database 12c Container database (prod12) with one pluggable database (dw).

1. Configure SPA Quick Check

1.1 From your laptop machine, open Firefox or Chrome browser and Log into Enterprise Manager Cloud

Control. Note you’re not using VNC brower but laptop browser.

1.2 Please use URL: https://localhost:7802/em

(7)

1.4 Click on Target -> Databases.

1.5 Expand the prod12 database and within the Pluggable databases , select the Pluggable Database

(8)

1.6 Select “Performance -> SQL -> SQL Performance Analyzer Setup”

(9)

1.8 Select SQL Tuning Set (STS “PENDING_STATS_WKLD”). Click on to get the complete list of STS. Note

that you may find it has already been selected in your case which is fine.

This is the setup page for SPA Quick Check. It gives 3 options for Trial Method. “Optimal” is the default.

Optimal Trial Mode: This is the recommended mode. It finds SQLs with plan changes first by generating

plans, then test-execute every SQL statement with plan changes to validate performance changes.

Per-SQL Time Limit: Default is 300 seconds if the statement has not finished by then it will be terminated

and not validated.

Comparison Metric: Choose “Buffer Gets”. This metric will show us how much work that is done for the

statement by measuring logical reads. This metric is stable and will not be impacted if the SQL is executed during peak or non-peak load.

This database is not configured with Resource Consumer Groups but this is a way to make sure that the statement will have limited impact on the server. Be aware that this can cause a statement to be terminated once it exceeds the defined Per-SQL Time Limit.

Save the changes you have made by clicking on “Save”.

(10)

B. Find: Performance Problem Diagnosis

Environment Details:

Database prod12 is a multitenant database and it contains only one PDB for the moment. The plan was to move the manufacturing calculation data warehouse (PDB name DW) first and later on move the order Entry system as well. DW has been in production for several months after the migration to the new Linux server. Performance was acceptable during the first month but has slowly degraded due to a combination of increasing data volume and more users.

Estimated Time to Complete Use Case: 17 minutes

Business Case

You have been asked to tune the database. But under no circumstance is it allowed to cause any performance degradation since it might cause the manufacturing process to stop with a cost of over $1M a day.

In this exercise we are supposed to identify slow SQL statements so we can tune them.

2.1 You should already be logged on to Enterprise Manager. If you are not, please follow the instructions

detailed in earlier section of this workbook.

2.2 Go to Enterprise -> Job -> Library

2.3 Select “RUN_PRODUCTION_WORKLOAD”

(11)

2.4 Click Submit. (the second submit is a confirmation), note there is no need to select em12 target, it is

picked up automatically

(12)

2.5 It is now time to see how the database behaves and if there are any SQL statements that indicates poor

performance

Click on Targets -> Databases

2.6 Select pluggable database prod12.oracle.com_DW in prod12.oracle.com

2.7 Wait for 5-7 minutes till the workload that you submitted ramps up so that the top SQL intended for

(13)

2.8 Select a period of time during the first spike. (If the spike is not as distinct as it is in the picture please

select the first 5 minutes of the workload).

2.9 By default the ASH analytic page is displayed in Activity mode. The load map mode will give us more fine grained information so let’s switch to this mode.

Click “Load Map”

(14)

2.11 Let’s change dimension and see which part of the application is using our resources.

Click on the drop down list with current value “Wait Class, Wait Event” and select “Module, Action”

2.12 Our resources are used by Action STAT. Let’s drill down even further to see which SQL statements are the main contributors.

(15)

2.13 Select “Wait Class, SQL ID”

2.14 We have now identified our queries that use most resources (You may see SQL IDs

99v2f8wz5am4x,

706vj3n6jvxzn and 807g9b91wb9s5)

. So let’s remove our filters and go back to the activity view Click on filter “Action STAT”

(16)

C. Fix: Tune Regressed SQL

In previous section we identified three statements which are the main contributors for resource consumption. Our goal is to improve the performance of these statements without causing any regressions. Tuning can be done in several ways, one option is to send our statements back to our developers and they can run manual tuning on them. But if it is complex statements or if it is an application delivered by an Application vendor then we might get the answer that it will be implemented in the next release.

Instead we are going to use SQL Tuning Advisor to find out if it can help us with some advice.

3.1 As we can see there are a couple of SQL Statements that cause the major part of the activity. You should

see SQL id 99v2f8wz5am4x, 706vj3n6jvxzn and 807g9b91wb9s5. You can hover over the SQL id for each

SQL and see the initial part of the SQL text. Select these Three SQL

Please Note: The number of SQLs to tune can be different, depending upon where your slider window in ASH ANALYTICS is at. Also the SQL ID’s can be different. So the screen shots given below may differ.

(17)

3.2 Review job detail and for easier identification change the name to “SQL_TUNING_BB” where BB is your

initials.

Change “Time Limit per Statement”: 2 (minutes)

Click Submit

3.3 The tuning task will run for 5 to 10 minutes. You can see the progress and the cumulative benefits on the

(18)

3.4 As we can see Tuning advisor has given tuning recommendations for us in the form of SQL profiles,

indexes and new statistics that we should investigate further. Click on Show all results.

(19)

D. Validate: Validate Tuning Using SPA Quick Check

on Production

Estimated Time to Complete Use Case: 22 minutes

Business Case

SQL Tuning Advisor has now given us advice to create SQL Profiles, create indexes and gather new statistics. We will now implement the advice that will give us most benefits. But since we are not allowed to jeopardize

current performance then we need to validate that our advices are risk free to implement. This will be done with SPA Quick Check

4.1 The largest benefit comes by creating SQL Profiles. New indexes have almost as high a benefit. But indexes will definitely cause performance penalty during insert and updates and can also cause other SQL to regress. We can also see that statistics are stale so we should have a look at statistics gathering later on. This is because

(20)

4.2 Enterprise Manager has now created a SQL Performance Analyzer Task for validating the performance.

Click on the SPA Task

(21)

4.4 There are four trials executed, first and second are only comparing execution plans, third and forth are full

executions of regressed SQL statements (subset of the workload) Let’s see the comparison result from the third and fourth trial. Click on the glasses for the second comparison report.

4.5 As we can see the performance improvement is in line with what the SQL Tuning Advisor recommended.

(22)

4.6 Identify your tuning task and click on the name

4.7 Click on SQL Profile

(23)

4.9 Check “Implement the new profile(s) with forced matching” and click Yes.

4.10 We have now implemented our new profiles. Let’s see if this had any impact on the workload.

Go to Performance -> ASH Analytics

4.11 Can we see any performance improvement?

(24)

4.12 We had a tuning advice to refresh Optimizer statistics. Let’s see if this will improve the performance even further.

4.13 Go to Enterprise -> Job -> Library

Select RESET_LAB_SPA_STATS_VALIDATE

(25)

The Job will take few seconds to finish.

4.15 Click on Targets -> Databases. Select pluggable database prod12.oracle.com_DW in prod12.oracle.com

4.16 Go to Performance -> SQL -> Optimizer Statistics

4.17 This database is configured to gather statistic in Pending mode. This is done to make sure that new statistics doesn’t surprise us with changed plans that causing poor performance.

(26)

(27)

4.18 Select Schema

Check “Validate impact ….” (Important and is at the bottom of the page) Click Next

4.19 Click Add

4.20 Search for “STAT1”

(28)

4.21 Click Next

4.22 Click Next

(29)

4.24 A SPA task has just been created and will start as soon as the statistic gathering has finished.

Click on the job name

4.25 The SPA task will take about a minute.

(30)

4.26 As in previous SPA task we have four trials. The first comparison will show changes in Optimizer cost which indicates new execution plan but the second comparison will show the changes in buffer gets for

statements with new plans.

So let’s bring up the second SPA report by clicking on the glasses.

4.27 We can see that we have 2 improved statements and no regression. The improvement is 86 % on the

workload which is very good. We have now guaranteed that new statistic will improve performance. Lets publish the new statistic

(31)

4.28 Let’s find schema STAT1 tables so we can publish the statistic

Change type to Table Set Schema to STAT1 Change scope to Pending Click GO

Change action to Publish.

Select one table, Click Go beside Action. Repeat this for each table. For purpose of the lab we’ll skip this step of publishing statistics and move on to next section.

At this point we have succeeded with tuning of prod12 database. Our next mission is to consolidate our OLTP database with the Data Warehouse. We can now stop database prod12 and start database prod.

This should be done from the started terminal window in VNC.

(32)

E. Concurrent Replay

[Optional]

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.

(33)

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

(34)

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.

(35)

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.

(36)

5.10. Review the information. Click Submit

(37)

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.

(38)

5.15. We are now ready with capture from prod.

(39)

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”

(40)

6.5 Click Submit

6.6 Navigate to Database Replay

(41)

(42)

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.

(43)

(44)

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”

(45)

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”

(46)

6.14 Select Directory Object “RAT_REPLAY” Click Next

6.15 Select: Credential Name: HOST-ORACLE(ORACLE) Click Next

(47)

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”

(48)

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

(49)

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

(50)

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”

(51)

6.23 Click “Next”

6.24 Click “Start Clients”

(52)

6.26 Click “Submit”

(53)

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

(54)

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

(55)

(56)

(57)

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

(58)

(59)

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.

(60)

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.

(61)

You have now finished the consolidated replay lab.

(62)

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

(63)

Click Submit.

(64)

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

(65)

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.

Oracle Database Performance Management Best Practices Workshop. AIOUG Product Management Team Database Manageability