Transcription
Real Application TestingDave FosterMaster Principal Sales Consultant
The following is intended to outline our generalproduct direction. It is intended for informationpurposes only, and may not be incorporated into anycontract. It is not a commitment to deliver anymaterial, code, or functionality, and should not berelied upon in making purchasing decisions.The development, release, and timing of anyfeatures or functionality described for Oracle’sproducts remains at the sole discretion of Oracle.
Why Consider Oracle 11g for Upgrades? Oracle Database 9i Release 2 Premium Support ended Jul 30, 2007 1 yr free Extended Support offer ends Jul 30, 2008TodayAugust 2012July 2010R2January 2009Premier SupportR2Jul 2011July 2013January 2012Sustaining SupportExtended SupportJuly 2007August 2015July 2010Jul heet.pdf
Oracle Enterprise ManagerTop--Down Application ationQualityManagementReal UserExperience MonitoringLoad Testing andAccelerators forPackaged Apps &Web ServicesService LevelManagementPackaged ApplicationManagementOracle EE-Business SuiteChange ManagementConfiguration Mgmt forApplicationsApplicationTesting SuiteFunctional Testing andAccelerators forPackaged Apps &Web ServicesTest ManagementSOA Application ManagementReal Application TestingDiagnostic and TuningData MaskingProvisioning, ConfigurationManagementChange Trackingand Synchronization
Real Application Testing: Tools of the TradeSQL Performance AnalyzerDatabase ReplayWhat is it? Predicts SQL performance deviationsbefore end-users can be impacted Replays real database workloadon test systemWhat Purpose? Assess impact of change on SQLresponse time Assess impact of change onworkload throughputHow it works? Executes each SQL, stored in SQLTuning Set, in isolation usingproduction context and thencompares before and after executionplans and run-time statistics Captures workloads and replays itwith production characteristicsincluding concurrency,synchronization &dependenciesWhen to use? Unit testing of SQL with the goal toidentify the set of SQL statementswith improved/regressed performance Comprehensive testing of allsub-systems of the databaseserver using real productionworkloadSQL Performance Analyzer and Database Replay are complementary
Real Application TestingLoad and SQL impact testing solution providinghighest quality testing for the database tier Value Rapid technology adoption Higher testing quality Business BenefitTest Lower cost Lower risk Features Database Replay SQL Performance Analyzer (SPA)DeployChangeRemediateBusiness Agility through SuperiorTesting
Testing TodayProduction – 1,000s of Real Online UsersApplicationServersDatabaseServersPRODUCTION
Testing TodayTest – 1-2 testers trying to be 1,000s of ersPRODUCTIONTEST
Database ReplayReal workload for 1,000s of online users rkloadPRODUCTION
Database ReplayReal workload for 1,000s of online users erversCaptureReplayWorkloadPRODUCTIONTEST
Database ReplayTest your system changes at production DatabaseServersServers9.2.0.8,10.2.0.2 , 11gCaptureReplay11g ONLYWorkloadPRODUCTIONTEST
Real Application Testing withDatabase Replay Replay production workload in test environment Identify, analyze and fix potential instabilities before makingchanges to production Capture Workload in Production Capture full production workload with real load, timing &concurrency characteristicsMove the captured workload to test system Replay Workload in Test Make the desired changes in test systemReplay workload with full production characteristicsHonor commit ordering Analyze & Report ErrorsData divergencePerformance divergenceAnalysis & Reporting
Why DB Replay?150DaysFrom:To:Artificial workloadsProduction workloadsPartial workflowsComplete workflowsMonths of developmentDays of developmentManual intensiveAutomatedHigh riskLow risk10Days
Replay Options Synchronized Replay (Default) Workload is replayed in full synchronized modeSame concurrency and timing as production workloadTransaction commit order is honoredEnsures minimal data divergence Synchronization controls Workload can be replayed in unsynchronized modeUseful for load/stress testingHigh data divergenceParameters for controlling synchronization Commit order synchronization: SYNCHRONIZATION Think time synchronization: THINK TIME SCALE Connect (logon) time synchronization: CONNECT TIME SCALE Request rate preservation: THINK TIME AUTO CORRECT
Analysis & Reporting Error Divergence: For each call error divergence is reported New: Error encountered during replay not seen during captureNot Found: Error encountered during capture not seen duringreplayMutated: Different error produced in replay than during capture Data Divergence Replay: Number of rows returned by each call are compared anddivergences reportedUser: Application level validation scripts Performance Reporting Capture and Replay Report: Provides high-level performanceinformationADDM Report: Provides in-depth performance analysisAWR, ASH Report: Facilitates comparative or skew analysis
Database Replay WorkflowProductionTest (11g)(9.2.0.8, 10gR2, 11g)Clients Replay DriverMid-Tier StorageStorageCaptureProcessReplayAnalysis &Reporting
Step 1: Workload CaptureProduction System All external client requestscaptured in binary files System background and internalactivity excludedClientClientClient File System Minimal overhead Avoids function call when possible Buffered I/OMiddle Tier Independent of client protocolFile 1 Can capture on 9.2.0.8, 10gR2,11g and replay on 11gFile 2 Capture load for interesting timeperiod, e.g., peak workload,month-end processing, etc. File nStorage
Step 2: Process Workload Files Setup test systemTest System Application data should be sameas production system as ofcapture start time Use RMAN, Snapshot Standby,imp/exp, Data Pump, etc. tocreate test system Make change: upgrade db and/orOS, change storage, migrateplatforms, etc. Processing transforms captureddata into replayable formatFile 1File 1File 2File 2 File nFile n Once processed, workload can bereplayed many times For RAC copy all capture files tosingle location for processing oruse shared file system MetadataCapture FilesReplay Files
Step 3: Replay Workload Replays workloadpreserving timing,concurrency anddependencies of thecapture systemReplay Client is a specialprogram that consumesprocessed workload andsends requests to thereplay systemClients interpret capturedcalls into sequence of OCIcalls and submit todatabaseFor high concurrencyworkloads, it may benecessary to start multipleclientsTest SystemReplay ClientsFile 1File 2 File nMetadataReplay Files
Supported Changeswith Real Application TestingChangesUnsupportedApplicationServersChanges Supported Database Upgrades, Patches Schema, Parameters RAC nodes, Interconnect OS Platforms, OS Upgrades CPU, Memory Storage Etc.DatabaseServersRecording ofExternal ClientRequests
Real Application Testing withSQL Performance Analyzer (SPA) Test impact of change on SQL query performanceCapture SQL workload in production including statistics & bind variablesRe-execute SQL queries in test environmentTune regressed SQL and seed SQL plans for productionClientClient ClientProduction TestRe-execute SQL QueriesMiddle TierCapture SQLOracle DB9i, 10g, 11gStorage Use SQL TuningAdvisor to tuneregression10gR2, 11g
Why SQL Performance Analyzer (SPA)? Businesses want systems that are performant andmeet SLA’s SQL performance regressions are #1 cause of poorsystem performance Solution for proactively detecting all SQL regressionsresulting from changes not available DBA’s use ineffective and time-consuming manualscripts to identify problemsSPA identifies all changes in SQL performance beforeimpacting users
SQL Performance Analyzer: OverviewProductionClientTest Middle TierRe-execute SQL CaptureSQL TransportSQLOracle DBStorage If adequate spare cycles available,optionally execute SQL hereMake Changes /Tuning Regressions* No middle & application tier setup required
SQL Performance AnalyzerRestrictions SQL Performance Analyzer does not support thefollowing features in the current release Shared server (Oracle MTS) 9i Parallel Query (SQL Trace Capture) Remote Transactions
SQL Performance Analyzerfor Earlier Releases Help customers smoothly transition to Oracle Database 10g and 11g SQL Test Execution feature of SPA backported to 10gR2 For more details: Metalink Note: 560977.1 – “Real Application Testing for Earlier Releases” Metalink Note: 562899.1 – “Testing Performance Impact of an Oracle 9i toOracle Database 10g Release 2 Upgrade with SQL Performance Analyzer”Upgrade UpgradeFromToRelease / PatchesneededComments10gR210gR2 or11g 11.1.0.6 one-off patchor 11.1.0.7 10gR2 one-off patch Uses STS to capture SQL stats9i10gR110gR2 or11g 11.1.0.6 one-off patchor 11.1.0.7 10gR2 one-off patch Uses SQL*Trace to capture SQL stats
Use Real Application Testing to ManageChange with Confidence and LOWER change riskLOWER testing time without compromising qualityLOWER unplanned outagesIMPROVE application reliabilityIMPROVE system performanceIMPROVE end-user experienceIMPROVE quality of serviceADOPT new technology faster
SQL Performance Analyzer and Database Replay are complementary. Real Application Testing Load and SQL impact testing solution providing highest quality testing for the database tier Test Deploy Value . AWR, ASH Report: Facilitates comparative or skew analysis .
