Oracle Database 11g Real Application Testing

1. Oracle Database 11gReal Application Testing

2. What is Real Application Testing? • New database option available with EE only • Includes two new features • Database Replay (DB Replay) • SQL Performance Analyzer (SPA) • SQL Tuning Set (STS) functionality is shared between Real Application Testing option and EM Tuning Pack • Advance functionality requires additional packs • Diagnostic Pack for DB Replay • Tuning Pack for SPA • Price (?)

3. Real Application Testing • Value • Reduces testing cost • Improves testing quality • Business Benefit • Faster technology adoption • Lower risk Deploy Test Change Remediate Solution for the Agile Business

4. Database Replay

5. The Need for Database Replay • System changes like hardware/software upgrades are fact of life • Customers want to identify full impact of change before going live • Extensive testing and validation can be expensive in time and $$$ • Despite expensive testing success rate low • Many issues go undetected • Changes can impact system availability and performance negatively • Cause of low success rate • Inability to properly test with real world production workloads results in many issues to go undetected • Database Replay makes it possible to do real world testing

6. Analysis & Reporting Database Replay • Recreate 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 & concurrency info • 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 & Report • Errors • Data divergence • Performance divergence

7. Client Client Client … Database Replay: Supported Changes Changes Unsupported MiddleTier Changes Supported Database Upgrades, Patches Schema, Parameters RAC nodes, Interconnect OS Platforms, OS Upgrades CPU, Memory Storage Etc. Recording of External Client Requests Storage

8. Comparison of LoadRunner & DB ReplayTesting e-Business Suite 80 24 20 20 5 4 2 5 0 0 Total Testing Time • DB Replay: 2 weeks • LoadRunner: 30 weeks

9. 150 Days 10 Days Why DB Replay? From: To: Production workloads Artificial workloads Complete workflows Partial workflows Days of development Months of development Automated Manual intensive Low risk High risk

10. Database Replay Overview

11. Client Client Client … Step 1: Workload Capture Production System All external client requests captured in binary files System background, internal activity excluded Minimal performance overhead for capture For RAC, shared and local file system supported Specify interesting time period for capture, e.g., peak workload, month-end processing, etc. File System MiddleTier File 1 File 2 … File n Storage

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

13. File 1 File 2 … File n Step 2: Process Workload Files Test System • Setup test system • Logically similar data as production • Use RMAN to physically restore production db from backup • Use Snapshot standby • Use imp/exp, Data Pump, etc. • Processing transforms captured data into replay files and generates necessary meta-data • Must be done on same version of database as replay system • Recommended to process on test system • Once processed, workload can be replayed many times • For RAC, if using local file system, copy all capture files to single location for replay File 1 File 2 … File n Metadata Replay Files Capture Files

14. Replay Client Replay Client Step 3: Replay Workload Replay Driver is a special client program that consumes processed workload and sends requests to the replay system Replay requests preserve timing, concurrency and dependencies seen on the capture system Replay Driver consists of one or more clients. For workloads with high concurrency, it may be necessary to start multiple clients to drive workload Once all replay clients are started, workload replay is then initiated by user Replay Driver File 1 File 2 … File n Metadata Replay Files

15. Replay Options • Synchronized Replay • Workload is replayed in full synchronized mode • Exact 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 (3) parameters provided to control degree of synchronization • Think time synchronization • Commit order synchronization • Connect (logon) time synchronization

16. Replay Options • Unsynchronized Replay Parameters • Think time synchronization • Controls think time between database calls • Auto (Default): Adjusts think time so as to maintain captured request rate • Percentage • 0% No think time, highest possible request rate • <100% Higher request rate • 100% Exact think time • >100% Lower request rate • Commit order synchronization • Controls commit order between transactions • In asynchronous mode, commit order not honored – transactions are committed as soon as commit call is issued • Connect (logon) time synchronization • Controls when sessions are created • 0%: All session are connected immediately • 100% (Default): Sessions connect at same time as in captured system

17. Replay Options • Number of Replay Clients • Configurable by user • Client Calibration Advisor recommends number of replay clients needed for specific workload • Replay clients are multithreaded clients that can drive multiple workload sessions each

18. Analysis & Reporting • Comprehensive reports are provided for analysis purposes • There (3) types of divergences are reported • Data Divergence: Number of rows returned by each call are compared and divergences reported • Error Divergence: For each call error divergence is reported • New: Error encountered during replay not seen during capture • Not Found: Error encountered during capture not seen during replay • Mutated: Different error produced in replay than during capture • Performance Divergence • Capture and Replay Report: Provides high-level performance information • ADDM Report: Provides in-depth performance analysis • AWR, ASH Report: Facilitates comparative or skew analysis

19. Workload Types Supported Supported • All SQL (DML, DDL, PLSQL) with practically all types of binds • Full LOB functionality (Cursor based and direct OCI) • Local transactions • Login/Logoffs • Session switching • Limited PL/SQL RPCs Limitations • 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 • Shared Server

20. EM Interface: DB Replay Summary

21. Best Practices • Capture • Provide adequate disk space for captured workload (binary files) • Database restart (Optional): Recommended to minimize divergence during replay • For RAC, use shared file system • Test System Setup • Ensure data in test is identical to production as of capture start time to minimize data divergence during replay • Use RMAN backup/restore or Snapshot Standby feature to setup test system • Reset system clock to same time as production if application logic involves SYSDATE usage • Process Workload • Processing workload has performance overhead and can possibly take a long time • Process workload on test system after rather than production system • Replay • Use Client Calibration Advisor to identify number of replay clients needed to replay workload properly

22. DB Replay Security Model • Any “Non-SYS” user with DBA Role • SYSDBA role is not mandatory • Does not have to be the user whose workload is captured • “Execute” privileges on DBMS_WORLOAD_CAPTURE/REPLAY procedures • DBMS_WORKLOAD_CAPTURE • START_CAPTURE, FINISH_CAPTURE, REPORT(), ADD_FILTER, DELETE_FILTER • DBMS_WORKLOAD_REPLAY • PROCESS_CAPTURE,INITIALIZE_REPLAY, PREPARE_REPLAY(), START_REPLAY(), CANCEL(), REPORT, ADD_FILTER, REMAP_CONNECTION • Capture and Replay user can be different if they have appropriate privileges

23. SQL Performance Analyzer (SPA)

24. Client Client Client … SQL Performance Analyzer (SPA) • Test impact of change on SQL query performance • Capture SQL workload in production including statistics & bind variables • Re-execute SQL queries in test environment Production Test Re-execute SQL Queries MiddleTier Capture SQL Use SQL Tuning Advisor to tune regression … … Oracle DB Storage

25. SPA Benefits • Enables identification of SQL performance regressions before end-users can be impacted • SPA can help with any change that impacts SQL execution plan • DB upgrades • Optimizer statistics refresh • New indexes, Materialized Views, Partitions, etc. • Fix regressed SQL with SQL Tuning Advisor and SQL Plan Baselines • Integrated with query optimizer • Captures SQL workload with low overhead

26. Make Change Tune SQL Initial Environment Post-Change Environment Tuned Environment Execute Production SQL and Gather Stats SQL Tuning Advisor Execute SQL Re-execute SQL Compare Performance SQL Performance Analyzer Workflow

27. Incremental Capture SQL Tuning Set Step 1: Capture SQL Workload • SQL Tuning Set (STS)used to store SQL workload • STS includes: • SQL Text • Bind variables • Execution plans • Execution statistics • Incremental capture used to populate STS from cursor cache over a time period • SQL tuning set’s filtering and ranking capabilities filters out undesirable SQL Cursor Cache Production Database

28. Export/Import SQL Tuning Set SQL Tuning Set Step 2: Move SQL Workload to Test System Cursor Cache Test Database Production Database • Copy SQL tuning set to staging table (“pack”) • Transport staging table to test system (datapump, db link, etc.) • Copy SQL tuning set from staging table (“unpack”)

29. Fetch Next SQL Test Execute Execution Plan & Statistics Save Result SQL Tuning Set Before Change SQL Performance Analyzer Step 3: Execute SQL Before Making Change • “Before change” SQL performance version is the SQL workload performance baseline • SQL Performance = execution plans + execution statistics • Test-Execute SQL in SQL tuning set: • produce execution plans and statistics • execute SQL serially (no concurrency) • every SQL is executed only once • skip DDL/DML effects • Explain plan SQL in SQL tuning set to generate SQL plans only

30. Fetch Next SQL Test Execute Execution Plan & Statistics Save Result Completed SQL Tuning Set Before Change After Change SQL Performance Analyzer Step 4: Execute SQL After Making Change • Manually implement the planned change: • Database upgrade • Implementation of tuning recommendations • Schema changes • Statistics gathering • Database parameter changes, • OS/hardware changes, etc. • Re-execute SQL after change: • Test-Execute SQL in SQL tuning set to generate SQL execution plans and statistics • Explain plan SQL in SQL tuning set to generate SQL plans

31. Completed Completed Before Change Compare SQL Performance Analysis Report After Change Step 5: Compare & Analyze Performance • Compare performance using different metrics • Elapsed Time • Parse Time • Execute Elapsed Time • Execute CPU Time • Buffer Gets • Disk Reads • Disk Writes • Optimizer Cost • SPA Report shows impact of change for each SQL • Improved SQL • Regressed SQL • Unchanged SQL • SQL with Errors • Tune regressed SQL using SQL Tuning Advisor • Analysis results can be used to seed SQL Plan Management repository SQL Performance Analyzer

32. EM Interface: SPA Report

33. SPA Security Model • Based on DBMS_SQLTUNE/SQL Tuning Advisor • Requires “Advisor”, “Administer [Any] SQL Tuning” privileges • Create/Drop/Alter SQL Profile privileges

34. Q & A