Oracle Real Application Testing: Change without Risk with Database - PowerPoint PPT Presentation

Oracle Real Application Testing: Change without Risk with Database Replay Leonidas Galanis, Senior Member of Technical Staff Mughees A. Minhas, Director of Product Management Karl Dias, Director of Development

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

Real Application Testing • Value • Rapid technology adoption Deploy Deploy • Higher testing quality • Business Benefit Test Test Test Change • Lower cost • Lower risk • Features Remediate Remediate • Database Replay • SQL Performance Analyzer (SPA) Solution for the Agile Business

Real Application Testing: Tools of the Trade SQL Performance Analyzer Database Replay What is it? • Predict SQL performance deviations • Replay real database workload on before end-users can be impacted test system What Purpose? • Assess impact of change on SQL • Assess impact of change on response time workload throughput How it works? • Single, isolated execution of SQL with • Replay with production context production context including concurrency, synchronization & dependencies When to use? • Unit testing of SQL with the goal to • Comprehensive testing of all sub- identify the set of SQL statements with systems of the database server improved/regressed performance using real production workload SPA and Database Replay offer complimentary solutions

The Need for Database Replay • Businesses want to adopt new technology that adds value • Extensive testing and validation is expensive in time and cost • Despite expensive testing success rate low • Many issues go undetected • System availability and performance negatively impacted • Cause of low success rate • Current tools provide inadequate testing • Simulate synthetic workload instead of replaying actual production workload • Provide partial workflow coverage Database Replay makes real-world testing possible

Database Replay Overview • Replay actual production database workload in test environment • Identify, analyze and fix potential instabilities before making changes to production • Capture Workload in Production • Capture full production workload with real load, timing & concurrency characteristics • Move the captured workload to test system • Replay Workload in Test • Make the desired changes in test system • Replay workload with full production characteristics • Honor commit ordering • Analyze & Report • Errors Analysis & Reporting • Data divergence • Performance divergence

Supported Changes Client Client Client … Changes Unsupported Middle Tier Changes Supported • Database Upgrades, Patches Recording of • Schema, Parameters External Client • RAC nodes, Interconnect Requests • OS Platforms, OS Upgrades Storage • CPU, Memory • Storage • Etc.

Database Replay Workflow Production (10.2.0.4) Test (11.1) Clients … Replay Driver Mid-Tier … Storage Storage Analysis & Process Replay Capture Reporting

Step 1: Workload Capture Production System • All external client requests captured in binary files Client Client Client • System background and internal … activity excluded File System • Minimal overhead • Avoids function call when possible Middle Tier • Buffered I/O File 1 • Independent of client protocol File 2 • Can capture on 10.2.0.4 and replay on 11g … • Capture load for interesting time File n period, e.g., peak workload, month-end processing, etc. Storage

Workload Capture Data Classes Client Client Client User • Client → RDBMS • SQL and PL/SQL Text • Binds • OCI Interface calls • RDBMS → Client • ROWIDS, Lob locators in out-binds and select lists • Row counts for queries and DML Two-Task • Error Codes Layer • System Data • Sequences • SCN • Timing information RDBMS Capture Probes

Workload Capture Unit • Database calls divided into two classes • Non-Commit actions • Commit actions Non-commit Action Capture Unit Commit Action Capture Unit Call Begin Time : T3 Call Begin Time : T1 Call Type : UPDATE Call Type : SELECT Wait-for SCN : 2345955 Wait-for SCN : 2345898 Statement Text : Statement Text : select * from a where b = :1; update a set b=:1 where b=:2 Binds : Bind 1 : ‘c’, Bind 2 : ‘d’ Binds : Bind 1 : ‘b’ Execution : Cursor Number : 1 Execution : Cursor Number : 1 Commit SCN : 2345956 Fetch : Number of Rows : 10 Call End Time :T2 Call End Time :T4

Capture Overhead • Performance Overhead • Workload dependent • TPCC throughput degradation about 4.5% • Proportional to database call size TPCC LOW Overhead HIGH Insert Intensive Large LOBs Short SQL/DML Long Running SQL • Memory Overhead: Each captured process allocates 64KB in PGA for buffering captured data

Workload Capture on RAC RAC Production System • Shared and local file system supported • Production and test system can have different number of nodes • Shared file system (recommended) Capture Capture Capture Capture • One directory shared across all nodes • Captures entire workload Capture Capture • Local file system Capture Capture • Each node with separate capture directory • Directory name and path must be … same on all nodes • For replay, workload files must be consolidated into single directory RAC Test System

Capture Options • Workload can be filtered to customize what is captured • Inclusion Filters: Specifies which sessions should be captured • Exclusion Filters: Specifies which sessions should NOT be captured • Filter Attributes: Any of the following session attributes can be used for filtering • User • Program • Module • Action • Service • Session ID • Workload capture can be run on- demand or scheduled to run at later time

Step 2: Process Workload Files • Setup test system Test System • Application data should be same as production system as of capture start time • Use RMAN, Snapshot Standby, File 1 imp/exp, Data Pump, etc. to File 1 create test system File 2 • Make change: upgrade db and/or OS, change storage, migrate File 2 … platforms, etc. … File n • Processing transforms captured data into replayable format File n • Once processed, workload can be replayed many times Metadata • For RAC copy all capture files to Replay Files Capture Files single location for processing

Step 3: Replay Workload Test System • Replays workload preserving timing, select * from tab … OCIStmtPrepare() Replay Clients concurrency and Binds : Bind 1 : ‘b’ OCIBindByName() dependencies of the Cursor # : 1 OCIAttrSet() capture system # Rows Fetched : 10 OCIStmtExecute() • Replay Client is a special program that consumes processed workload and sends requests to the File 1 replay system File 2 • Clients interpret captured … calls into sequence of OCI calls and submit to database File n • For high concurrency workloads, it may be necessary to start multiple Metadata clients Replay Files

Workload Replay: Synchronization Capture Replay T1 T1 S1 S1 S2 S2 RDBMS will make S2 is slower T3 wait for S2 to during replay finish S3 T3 T3 S3 T4 S4 T4 T5 S4 S5 T5 S5 Commit Action Non-commit Action

Workload Replay: Physical Locator Remapping Capture • Scenario : Select rowid from emp where Capture ename = ‘Smith’; outgoing rowid … Update emp set sal=2*sal where Capture again rowid=:1 as in-bind • Captured bind value does not help Replay with replay Expect • Update will fail unless remapped outgoing rowid • Rowids automatically remapped and intercept it with valid runtime values • Physical locators auto-remapped include, rowids, LOB locator, Ref cursors Replace captured rowid

Workload Replay: Causes of Data Divergence • Certain user actions not synchronized during replay • Calls to dbms_pipe • Commits within PL/SQL • User locks • External data dependencies maintained in application logic • Use of non-repeatable functions • RANDOM(), SYSDATE() • External interactions via urls, dblinks and BFILEs • In-flight sessions at start of capture

Replay Options • Synchronized Replay (Default) • Workload is replayed in full synchronized mode • Same concurrency and timing as production workload • Transaction commit order is honored • Ensures minimal data divergence • Unsynchronized Replay • Workload can be replayed in unsynchronized mode • Useful for load/stress testing • High data divergence • Three parameters control degree of synchronization • Commit order synchronization • Think time synchronization • Connect (logon) time synchronization

Replay Options • Connection Remapping • Capture-time connection string can be remapped for replay time • Number of Replay Clients • Configurable by user • Calibration mode recommends number of replay clients needed for specific workload • Replay clients are multithreaded clients that can drive multiple workload sessions