less05_DB_Replay by aashu4uiit

VIEWS: 55 PAGES: 43

									Database Replay

Copyright © 2007, Oracle. All rights reserved.

Objectives

After completing this lesson, you should be able to: • Identify the benefits of using Database Replay • List the steps involved in Database Replay • Use Enterprise Manager to record and replay workloads

5-2

Copyright © 2007, Oracle. All rights reserved.

Why Use Database Replay?
• • • • System changes (such as hardware and software upgrades) are a fact of life. Customers want to identify the full impact of changes before going live. Extensive testing and validation can be expensive in time and money. Despite expensive testing, success rates are low:
– Many issues go undetected. – Changes can negatively affect system availability and performance.

•

Cause of low success rate:
– Inability to properly test with real-world production workloads, with many issues going undetected.

•

The Database Replay feature makes it possible to do real-world testing.
Copyright © 2007, Oracle. All rights reserved.

5-3

Database Replay
• • • Re-create actual production database workload in test environment. Identify and analyze potential instabilities before making changes to production. Capture workload in production:
– Capture full production workload with real load & concurrency – Move the captured workload to test system

•

Replay workload in test:
– Make the desired changes in test system – Replay workload with production load & concurrency – Honor commit ordering

•

Analyze and report:
– Errors – Data divergence – Performance divergence

5-4

Copyright © 2007, Oracle. All rights reserved.

System Architecture: Capture

Capture directory Shadow Shadow Shadow Shadow Shadow capture file Shadow capture file Recording infrastructure Database stack Shadow capture file Shadow capture file
Background Background

Database backup Production database

5-5

Copyright © 2007, Oracle. All rights reserved.

System Architecture: Processing the Workload

Capture directory

Process capture files

Shadow capture file Shadow capture file Shadow capture file Shadow capture file

Database stack

Background

Background

Database backup Production database

Process capture

5-6

Copyright © 2007, Oracle. All rights reserved.

System Architecture: Replay
Replay system
Replay client Replay client

Capture directory

Process capture files

Shadow …

Shadow

Shadow …

Shadow

Shadow capture file Shadow capture file Shadow capture file Shadow capture file

Database stack

Background

Background

Database backup

Test system with changes

Test database

5-7

Copyright © 2007, Oracle. All rights reserved.

The Big Picture
Pre-change production system
Clients/app servers

Post-change test system Capture directory Process capture files Shadow capture file Shadow capture file Shadow capture file Shadow capture file
Test system with changes

Replay system

Production system

Production database

Database backup Can use Snapshot Standby as test system

Database restore

5-8

Copyright © 2007, Oracle. All rights reserved.

Pre-Change Production System
Changes not supported
Clients/app servers

Supported changes • Database upgrades, patches • Schema, parameters • RAC nodes, interconnect • OS platforms, OS upgrades • CPU, memory • Storage

Production system

Production database

5-9

Copyright © 2007, Oracle. All rights reserved.

Supported Workloads

• Supported
– – – – – – All SQL (DML, DDL, PL/SQL) with practically all types of binds Full LOB functionality (cursor-based and direct OCI) Local transactions Logins and logoffs Session switching Limited PL/SQL RPCs

• Limitations
– – – – – –
5 - 10

Direct path load, import/export OCI-based object navigation (ADTs) and REF binds Streams, non-PL/SQL-based AQ Distributed txns, remote describe/commit operations Flashback (Database and Query) Shared Server
Copyright © 2007, Oracle. All rights reserved.

Capture Considerations
Planning • Adequate disk space for captured workload (binary files) • Database restart:
– Only way to guarantee authentic replay
— —

Startup restrict Capture will un-restrict

–

May not be necessary depending on the workload Physical restore (scn/time provided) Logical restore of application data Flashback/snapshot-standby

•

A way to restore database for replay purposes:
– – –

• •

Filters can be specified to capture subset of the workload. SYSDBA or SYSOPER privileges and appropriate OS privileges

Overhead • Performance overhead for TPCC is 4.5% • Memory overhead : 64 KB per session • Disk space
5 - 11 Copyright © 2007, Oracle. All rights reserved.

Replay Considerations

• Preprocess captured workload
– One-time action – On same DB version as replay – Can be performed anywhere (production, test system, or other system) if versions match

• Restore database, and then perform the change:
– – – – – Upgrade Schema changes OS change Hardware change Add instance

5 - 12

Copyright © 2007, Oracle. All rights reserved.

Replay Considerations

• Manage external interactions
– Remap connection strings to be used for the workload:
—

—

One-to-one: For simple instance-to-instance remapping Many-to-one: Use of load balancer (e.g., single node to RAC)

– Modify DB Links and directory objects that point to production systems

• Set up one or more replay clients
– Multithreaded clients that can each drive multiple workload sessions

5 - 13

Copyright © 2007, Oracle. All rights reserved.

Replay Options
• Synchronized replay:
– Ensures minimal data divergence – Commit-based synchronization

•

Unsynchronized replay:
– Useful for load/stress testing – Original commit ordering not honored – High data divergence

•

Think time options:
– Auto (default) – Adjust think time to maintain the captured request rate:
— — — —

0%: No think time (highest possible request rate) <100%: Higher request rate 100%: Exact think time >100%: Lower request rate

•

Login time options
– Percentage (default is 100%)

5 - 14

Copyright © 2007, Oracle. All rights reserved.

Replay Analysis

• Data divergence
– Number of rows compared for each call (queries, DML)

• Error divergence
– New errors – Mutated errors – Errors that have disappeared

• Performance
– – – – Capture and Replay report ADDM report ASH report for skew analysis AWR report

5 - 15

Copyright © 2007, Oracle. All rights reserved.

Database Replay Workflow in Enterprise Manager

1. 2. 3. 4. 5. 6. 7. 8.

Capture the workload on a database. (Task 1) Optionally export the AWR data. (Task 1) Restore the replay database on a test system. Make changes to the test system as required. Copy the workload to the test system. Preprocess the captured workload. (Task 2) Configure the test system for the replay. Replay the workload on the restored database. (Task 3)

5 - 17

Copyright © 2007, Oracle. All rights reserved.

Capturing Workload with Enterprise Manager

5 - 18

Copyright © 2007, Oracle. All rights reserved.

Using the Capture Wizard

5 - 19

Copyright © 2007, Oracle. All rights reserved.

Using the Capture Wizard

5 - 20

Copyright © 2007, Oracle. All rights reserved.

Using the Capture Wizard

5 - 21

Copyright © 2007, Oracle. All rights reserved.

Using the Capture Wizard

5 - 22

Copyright © 2007, Oracle. All rights reserved.

Using the Capture Wizard

5 - 23

Copyright © 2007, Oracle. All rights reserved.

Using the Capture Wizard

5 - 24

Copyright © 2007, Oracle. All rights reserved.

Viewing Workload Capture History

5 - 25

Copyright © 2007, Oracle. All rights reserved.

Processing Captured Workload

5 - 26

Copyright © 2007, Oracle. All rights reserved.

Using the Preprocess Captured Workload Wizard

5 - 27

Copyright © 2007, Oracle. All rights reserved.

Using the Replay Workload Wizard

5 - 28

Copyright © 2007, Oracle. All rights reserved.

Using the Replay Workload Wizard

5 - 29

Copyright © 2007, Oracle. All rights reserved.

Using the Replay Workload Wizard

5 - 30

Copyright © 2007, Oracle. All rights reserved.

Using the Replay Workload Wizard

5 - 31

Copyright © 2007, Oracle. All rights reserved.

Using the Replay Workload Wizard

5 - 32

Copyright © 2007, Oracle. All rights reserved.

Using the Replay Workload Wizard
$ wrc REPLAYDIR=/home/oracle/solutions/dbreplay USERID=system PASSWORD=oracle Workload Replay Client: Release 11.1.0.6.0 - Production on Tue … $ wrc REPLAYDIR=/home/oracle/solutions/dbreplay USERID=system PASSWORD=oracle Copyright (c) 1982, 2007, Oracle. All rights reserved. Workload Replay Client: Release 11.1.0.6.0 - Production on Tue … Wait for the replay to start (21:47:01) Copyright (c) 1982, 2007, Oracle. All rights reserved. Wait for the replay to start (21:47:01)

5 - 33

Copyright © 2007, Oracle. All rights reserved.

Using the Replay Workload Wizard

$ wrc REPLAYDIR=/home/oracle/solutions/dbreplay USERID=system PASSWORD=oracle Workload Replay Client: Release 11.1.0.6.0 - Production on Tue … Copyright (c) 1982, 2007, Oracle. All rights reserved. Wait for the replay to start (21:47:01) Replay started (21:48:14)

5 - 34

Copyright © 2007, Oracle. All rights reserved.

Viewing Workload Replay Statistics

5 - 35

Copyright © 2007, Oracle. All rights reserved.

Viewing Workload Replay Statistics

$ wrc REPLAYDIR=/home/oracle/solutions/dbreplay USERID=system PASSWORD=oracle Workload Replay Client: Release 11.1.0.6.0 - Production on Tue … Copyright (c) 1982, 2007, Oracle. All rights reserved. Wait for the replay to start (21:47:01) Replay started (21:48:14) Replay finished (21:51:21) $

5 - 36

Copyright © 2007, Oracle. All rights reserved.

Viewing Workload Replay Statistics

5 - 37

Copyright © 2007, Oracle. All rights reserved.

Packages and Procedures

•

DBMS_WORKLOAD_CAPTURE
– – – – – – – – – START_CAPTURE FINISH_CAPTURE ADD_FILTER DELETE_FILTER DELETE_CAPTURE_INFO GET_CAPTURE_INFO() EXPORT_AWR IMPORT_AWR() REPORT()

•

DBMS_WORKLOAD_REPLAY
– – – – – – – – – – – PROCESS_CAPTURE INITIALIZE_REPLAY PREPARE_REPLAY START_REPLAY CANCEL_REPLAY DELETE_REPLAY_INFO REMAP_CONNECTION EXPORT_AWR IMPORT_AWR GET_REPLAY_INFO REPORT

5 - 38

Copyright © 2007, Oracle. All rights reserved.

Data Dictionary Views: Database Replay

• DBA_WORKLOAD_CAPTURES: Lists all the workload captures performed in the database • DBA_WORKLOAD_FILTERS: Lists all the workload filters defined in the database • DBA_WORKLOAD_REPLAYS: Lists all the workload replays that have been performed in the database • DBA_WORKLOAD_REPLAY_DIVERGENCE: Is used to monitor workload divergence • DBA_WORKLOAD_CONNECTION_MAP: Is used to review all connection strings that are used by workload replays • V$WORKLOAD_REPLAY_THREAD: Monitors the status of external replay clients
5 - 39 Copyright © 2007, Oracle. All rights reserved.

Database Replay: PL/SQL Example
exec DBMS_WORKLOAD_CAPTURE.ADD_FILTER(fname => 'sessfilt',fattribute => USER ,fvalue => 'JFV'); exec DBMS_WORKLOAD_CAPTURE.START_CAPTURE(name => 'june_peak',dir => 'jun07');

Execute your workload
exec DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(); exec DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(capture_dir => 'jun07'); wrc userid=system password=oracle replaydir=/dbreplay

exec DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(replay_name => 'j_r' ,replay_dir => 'jun07');

5 - 40

Copyright © 2007, Oracle. All rights reserved.

Database Replay: PL/SQL Example
exec DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(connection_id => 101,replay_connection => 'edlin44:3434/bjava21'); exec DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(synchronization => TRUE,think_time_scale=> 2); exec DBMS_WORKLOAD_REPLAY.START_REPLAY ();
DECLARE cap_id NUMBER; rep_id NUMBER; rep_rpt CLOB; BEGIN cap_id := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(dir => 'jun07'); /* Get the latest replay for that capture */ SELECT max(id) INTO rep_id FROM dba_workload_replays WHERE capture_id = cap_id; rep_rpt := DBMS_WORKLOAD_REPLAY.REPORT(replay_id => rep_id, format => DBMS_WORKLOAD_REPLAY.TYPE_TEXT); END;

5 - 41

Copyright © 2007, Oracle. All rights reserved.

Calibrating Replay Clients
$ wrc system/oracle@orcl mode=calibrate replaydir=/dbreplay Workload Replay Client: Release 11.1.0.5.0 - Beta on Tue … Copyright (c) 1982, 2007, Oracle. All rights reserved. Report for Workload in: /dbreplay ----------------------Recommendation: Consider using at least 1 clients divided among 1 CPU(s). Workload Characteristics: - max concurrency: 4 sessions - total number of sessions: 11 Assumptions: - 1 client process per 50 concurrent sessions - 4 client process per CPU - think time scale = 100 - connect time scale = 100 - synchronization = TRUE $

5 - 42

Copyright © 2007, Oracle. All rights reserved.

Summary

In this lesson, you should have learned how to: • Identify the benefits of using Database Replay • List the steps involved in Database Replay • Use Enterprise Manager to record and replay workloads

5 - 43

Copyright © 2007, Oracle. All rights reserved.

Practice 5: Overview

This practice covers using Database Replay with Enterprise Manager in the following scenarios:
– Replaying in synchronous mode without changes – Replaying in synchronous mode after changes are applied – Replaying in nonsynchronous mode without changes

5 - 44

Copyright © 2007, Oracle. All rights reserved.


								
To top