Managing Change with Real Application Testing and

Managing Change with Real Application Testing and Snapshot Standby Barry Hodges Senior Solution Architect, Sales Consulting, Oracle NZ

“The art of progress is to preserve order amid change. ” Alfred North Whitehead British Mathematician and Philosopher, 1861 -1947

IT Challenges

Motivation for change Application Tier Application Upgrades, Object Modifications, etc Database Tier Database Upgrades, Patches, Schema, Parameters, RAC nodes, Interconnect, OS Platforms, OS Upgrades, CPU, Memory, etc Storage Tier SAN Upgrades, ASM, more disk, etc

Database Upgrade Process: Steps 1. Gather information about PROD environment 2. Determine upgrade path & method 3. Prepare B/R strategy, clone and setup Test DB 4. Establish performance baseline before upgrade 5. Develop tests for database & applications 6. Test upgraded database & applications 7. Check performance before & after upgrade 8. Remediate regressions 9. Go Live! Majority of effort spent in developing tests, detecting and remediating regressions

Current Testing Methodology Need to provision the whole stack PRODUCTION BA’s id top n business transactions Simulated load 80/20? TEST Test Scripts need to be constantly rewritten as data changes Simulate load by over throttling? Requires specialised application knowledge What is the correct throughput? Can we trust the simulated load? Can we account for selects?

Database Upgrade Process: Steps 1. Gather information about PROD environment 2. Determine upgrade path & method 3. Prepare B/R strategy, clone and setup Test DB 4. Establish performance baseline before upgrade 5. Develop tests for database & applications 6. Test upgraded database & applications 7. Check performance before & after upgrade 8. Remediate regressions 9. Go Live! Real Application Testing provides realistic testing for Majority of effort spent in developing tests, detecting and remediating regressions real-world systems

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

SQL Performance Analyzer • • • Identify SQL performance regression before end-users are impacted Identify, analyse and fix potential instabilities before making changes to production Capture SQL in Production – – • Re-execute SQL queries in Test – – • Capture SQL workload in production including statistics & binds Move the SQL Tuning Set to test system Make the desired changes in test system Statements can be re-executed as often as is necessary in the test environment Analyze & Report – – Highlight improvements Highlight regressions Highlight highest impact statements Use SQL Tuning Advisor to tune regressed statements SQL

Application Tier Application Upgrades, Some Object Modifications Unsupported Supported Changes Database Tier Patches, Schema, Parameters, RAC nodes, Interconnect, OS Platforms, OS Upgrades, CPU, Memory Storage Tier SAN Upgrades, ASM, more disk, etc Supported Database Upgrades (9 i, 10 g 11 g),

Effort (Days) Current Testing Methodology Install & Setup Understand Application Usage Identify Key Transactions Generate Workload Total Testing Time DB Replay: 2 weeks Load. Runner: 30 weeks Run Test

Real Application Testing Demo Production (wgn 1) Dell OPTIPLEX 755 Intel(R) Dual Core(TM)2 Duo CPU @ 2. 33 GHz 4 GB RAM Oracle Enterprise Linux 5. 1 Oracle 11. 1. 0. 6 Standby (wgn 1 sby)

Real Application Testing Demo Production (wgn 1) Standby (wgn 1 sby) Data Guard

Real Application Testing Demo Production (wgn 1) Standby (wgn 1 sby) DGMGRL> convert database 'sid' to snapshot standby; Data Guard

Real Application Testing Demo Production (wgn 1) Standby (wgn 1 sby) Data Guard Capture SQL

Real Application Testing Demo Production (wgn 1) Standby (wgn 1 sby) Data Guard Prepare

Real Application Testing Demo Production (wgn 1) Standby (wgn 1 sby) Data Guard Replay SQL

Real Application Testing Demo Production (wgn 1) Standby (wgn 1 sby) Data Guard Report & Analyze

Real Application Testing Demo Production (wgn 1) Standby (wgn 1 sby) DGMGRL> convert database 'sid' to physical standby; Data Guard

Value Proposition Oracle DB Replay & SPA Capture with low overhead Test with production context Transfer resolutions to production Yes 3 rd Party SQL Testing Tools No require explicit polling & external storage Yes No not integrated with optimizer Yes Integrated with AWR, ASH, SQL Tuning Advisor & SQL Plan Management üTechnology that can only be built by Oracle No

Value Proposition From: Months of development Days of development Artificial workloads Production workloads Partial workflows Complete workflows Manual intensive Automated Complete Stack Weeks To: Database Tier High risk Low risk Days

Resources • Oracle. com: http: //www. oracle. com/database/real-application-testing. html • Oracle Real Application Testing on OTN: http: //www. oracle. com/technology/products/manageability/databas e/index. html • Oracle Real Application Testing white papers: http: //www. oracle. com/technology/products/database/oracle 11 g/pd f/real-application-testing-whitepaper. pdf http: //www. oracle. com/technology/products/manageability/databas e/pdf/ow 07/db_replay_white_paper_ow 07_1. pdf http: //www. oracle. com/technology/products/manageability/databas e/pdf/ow 07/spa_white_paper_ow 07. pdf