1 / 44

Real Oracle Real Application Testing

Real Oracle Real Application Testing. What to Expect and Prepare For William Callahan Director, Products and Technology CCC Information Services Robert Kalinka Oracle ATS - Consulting Technical Manager. 30. Why CCC. 50. over. over. years industry experience. scalability. over.

filia
Download Presentation

Real Oracle Real Application Testing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Real Oracle Real Application Testing What to Expect and Prepare For William Callahan Director, Products and Technology CCC Information Services Robert Kalinka Oracle ATS - Consulting Technical Manager

  2. 30 Why CCC 50 over over years industry experience scalability over states covered 150 proven over spent on R&D 300 97% compliance million transactions complete all field and phone support staff CBSAs covered innovative reliable $40M

  3. Background - The CCC model Access to the largest auto claims network in the industry Comprehensive industry analytics and insight arising from 28 years of domain expertise. Single workflow platform eliminates the shortcomings of separate applications and drives efficiency, accuracy and speed. • CCC works with its clients and industry partners to assist them in: • Making well-founded, sound decisions • Gaining access to an extensive auto claims supply network • Achieving optimal staff productivity through use of comprehensive single platform

  4. Oracle Consulting Professionals are Oracle Experts • 1,100+ Oracle technology and government consultants globally • Focused on Oracle applications, technology, and hardware products and solutions • Deep expertise across Federal and State & Local • Certified Oracle Enterprise Architects • Navigate the breadth of Oracle’s portfolio • Proven track record in addressing complex IT issues • World-class Project Management Office with 5 levels of certification More Successful Oracle Deployments than Any Other Provider

  5. Oracle Consulting Core Competencies Migration & Implementations IT Strategy & Architecture • Enterprise Architecture • Technology Roadmaps - Cloud, Big Data • Data Center Optimization • IT Governance • Migrate and Upgrade to the Latest Oracle Technology • Solution Design and Implementation NO ADDITIONAL FUNDS TO ADDRESS PRESSURES NO ADDITIONAL FUNDS TO ADDRESS PRESSURES Product Expertise • Product Rapid Starts • Performance Tuning and Optimization • Risk and Operational Analysis • Problem Remediation • Installation and Configuration

  6. Program Agenda • The lay of the land • Chasing down a RAT • Challenges with RAT • Is that a RAT-atouille you are cookin’?

  7. Abstract Exadata Migration Customer Case Study using Real Application Testing • Discuss how CCC information services, a leading insurance claims management process provider, used Real Application Testing to migrate from 10g single instance to Exadata • What challenges, practical considerations and lessons were learnt with database capture and replay as well as setting up the test system • We will demystify capture and replay reports for in-depth performance analysis • We will also discuss how Real Application Testing helped identify and resolve key performance bottlenecks before Go-live

  8. The Lay of the Land

  9. Architecture CCC Legacy Design

  10. Architecture CCC Target Design

  11. Goals/Features

  12. Chasing Down a RAT

  13. Real Application Testing - Components • SQL Performance Analyzer • Typically performed before Database Replay • Identify and resolve any significant SQL Plan changes • Compare before and after performance stats • Database Replay • Workload is captured from the Production database environment and replayed on the target database environment • Workload capture includes production concurrency

  14. RAT Process Planning Patch Instantiate Capture Analyze Replay Target System – Dataguard Copy OEM AWR Compare RAT Replay Reports Target System – Dataguard Copy Source Capture System And Target Replay System Source Production Workload Environment Targets • Confirm Transactional Dataflow for Testing scenario • Issue GoldenGate Stop event marker • Exex rdbms_db_workload(capture) • Snapshot Standby • Convert database to snapshot standby after GoldenGate is synchronized • Copy RAT Capture Files to target system • Perform Replay • Prepare Database • Modify Database as per test scenario • Instantiate Replay Clients • Start Replay • Monitor Replay to completion • Reports • AWR Compare to CAPTURE performance • OEM System Compare for OS metrics • OSWatcher Logs • CONFIRM Patches are Installed • -Real Application Testing Now Available for Earlier Releases (Doc ID 560977.1) Action-Items Confirm version match according to MOS note above. Be sure to Stop Dataflow to target system before capture Target DB instantiation Complete Target System Replay of Production Workload Reset REPLAY system and REPEAT Replay with MODIFICATIONS

  15. Migration Architecture

  16. Real Application Testing – Design Concerns • GoldenGate replication • Migration requirement for cross-platform • Minimize downtime window at cut-over • Test of actual “migrated” Production data sets prior to go-live • Maintaining Transactional Consistency throughout complex workflow • Legacy Production to future production Exadata via GoldenGate • Future production Exadata to DR Exadata via DataGuard • RAT Replay on DR Exadata environment

  17. Real Application Testing – Questions to answer • Testing RAC impact for high number of Distributed Transactions • Architecture challenges • Oracle SOA Suite 10g and Advance Queuing • Confirm SQL execution profile • All Custom Java Based SQL • Expected a high degree of SQL tuning during migration (15%) • Volume processing • High usage of LOB storage for Digital Image, Print Image and Large XML • Heavy transitional data and persistent data set mix • Verify impact of data sub-setting

  18. Other Questions to Answer • AQ - RAT viability, outcome, discovery • Stateless Transaction Testing suitability • AQ nuance when operating in RAC • SOA - RAT viability, outcome, direction change • Very tight coupling between transaction state and execution • Suitability of Node affinity for DB Services • 90/10 ratio between short and long running BPEL threads • Workload capture alignment

  19. Is that a RAT-atouille you are cookin’?

  20. Test Results - Summary • Migration and Consolidation strategy was very complex • Testing consumed about 30 replay iterations sourced from 10 independent capture times on legacy source • Overall migration plus performance testing timeline was approximately 10 weeks • We were able to iteratively validate each migration dry run • Migration issues were fixed along the way • New application deployments were supported in parallel • Configuration issues were fixed along the way

  21. SQL Performance Analyzer 2 Examples Of High Impact SQLID’s By Executions

  22. SPA Results high volume SQL 2rhynbqqzq9ss SELECT DL_CUST_ID, CUST_NM, DECODE(CORP_OFCE_DL_CUST_ID, :"SYS_B_0", PARNT_DL_CUST_ID, CORP_OFCE_DL_CUST_ID) FROM CUSTOMER_REGISTERED WHERE CUST_OFCE_ID = :1 AND CORP_OFCE_DL_CUST_ID = :2

  23. RAT Replay Results SQL 2rhynbqqzq9ss Summary for 792,640 executions over 2-hour interval

  24. Real Production Results SQL 2rhynbqqzq9ss Current Production (8/2013) Single Execution Statistics are within reason from what SPA predicted. • Execution Time <0.01(s) • CPU Time <0.01(s) • Only 23 more Buffer Gets

  25. SPA Results high volume SQL dmxbcbm0yagau SELECT tlcm_num, tlcm_ctry_cd, tlcm_city_cd, tlcm_area_cd, tlcm_extn, tlcm_str, tlcm_pager_pin, tel.lock_id, tel.dl_cntct_mchnsm_id FROM telecom_number tel WHERE tel.dl_cntct_mchnsm_id =:"SYS_B_0"

  26. RAT Results for dmxbcbm0yagau Summary for 432,985 executions over 2-hour interval

  27. Real Production Results for dmxbcbm0yagau Current Production (8/2013) Single Execution Statistics are within reason from what SPA predicted. • Execution Time <0.01(s) • CPU Time <0.01(s) • Same Buffer Gets

  28. Database Replay 3 examples of Full Production Workload

  29. Database Replay Attempt 1 - Results Observations – Top Activity Between 2000 and 3000 concurrent active sessions in wait Significant Bottlenecks • Concurrency • Scheduler • Other

  30. Database Replay Attempt 1 - Results Observations – DB Replay Source production capture was 55 minutes Replay consumed 75 minutes but was only 10% complete

  31. Database Replay Attempt 1 - Summary • Cancelled • Significantly BAD overall performance • Initial migration attempt was flawed • Target environment configuration inefficiencies • Retry • Many more migration and performance testing iterations later in a galaxy far, far away…

  32. Database Replay Attempt 2 – Changes • Single instance to RAC • Single service to multiple services (Singleton and distributed) • ORA-600 error related to custom code • Discovered during DB Replay • Implemented patches from Oracle Support • Tuning Parallelism on specific table to address performance • Only <2% overall SQL re-write • Secure_Files implementation rather than basic LOBS

  33. Database Replay Attempt 2 - Changes • Sequence Cache tuning • Indexes – to maintain FK performance • Sometimes the FTS is not enough for high volume OLTP • Process counts and other init.ora changes • Adjusted number of Workload Replay Clients

  34. Database Replay Attempt 2 - Results Observations – Top Activity Between 30 and 60 concurrent active sessions in wait • Over 98% reduction Significantly less bottlenecks • Still undesired concurrency

  35. Database Replay Attempt 2 - Results Observations – DB Replay Capture and Replay times were reasonable Less average active sessions in Replay as compared to Capture User Calls were reasonable Approximately 13% less Database Time and Active Sessions consumed

  36. Database Replay Attempt 2 – Results Summary • Validated all changes up to this point • While much better than previous attempts, the observed concurrency waits were still significant • We can do better • Retry • Attempt to further tune overall workload

  37. Database Replay Attempt 3 - Changes • Single instance to RAC tuning • Single Service to Multiple Services (Singleton and distributed) - service RE distribution • Indexes tuning • Made additional invisible until they are confirmed to be unnecessary

  38. Database Replay Results Attempt 3 Observations – Top Activity Between 2 and 20 concurrent active sessions • Over 66% reduction from attempt “2” Significantly less bottlenecks • Still concurrency, but reasonable for the workload

  39. Database Replay Results Attempt 3 Observations – DB Replay Capture and Replay times were reasonable User Calls were reasonable Approximately 75% less Database Time and Average Active Sessions consumed

  40. Overall Summary – Migration and Consolidation • Multiple Migrations from Solaris to Exadata x2-2 • XA and SOA applications • Custom Java applications • AQ

  41. Overall Summary – RAT Iterations • Multiple Iterations per database • Init.ora changes • RAT replay configuration changes • Single instance to RAC service configuration • Environment variables • Migration validation • Performance validation • Patch validation

  42. Overall Summary – Go-Live Successful • CCC still needed to implement post go-live applications changes such as: • Query optimization • Workflow optimization • Reduce connections via revised connection pooling • Reduce CURSOR usage • These post go-live changes resulted in even less overall resource usage.

  43. Overall Summary – Go-Live Successful • CCC was able to handle the highest recorded volume with Exadata x2-2 as of October 2012 • Actual performance metrics matched those seen during RAT iterations • Conclusion: CCC was able to do more with Exadata and prove it with RAT before go-live

  44. Current Production – 1 year later Current Production (8/2013) Significantly increased customer volume as compared to 2012 Exalogic implementation Additional Application Query Tuning Additional Workflow Tuning

More Related