Transcript Slide 1
Look Before You Leap! Oracle 11g Real Application Testing Gavin Soorma, Senior Oracle DBA, HBOSA Change Management and its challenges • Change is mandatory – Legal compliance, security, product support • Change is technology driven to enable business to have a competitive edge • Significant time, effort and money is spent before system changes can be successfully deployed in a production environment • Many issues go undetected until production deployment adversely affecting application availability, user confidence and reputation • Inability of test scripts and simulation tools to test using real production workloads. • Test workloads not accurate representations of peak production workloads. Oracle 11g Real Application Testing • Two Components – Database Replay – SQL Performance Analyzer • Capture live workload on production system • Replay production workload on test system with the same timing, concurrency, dependency and transactional properties of the original workload • Testing cycles for complex applications is reduced from months to few days – faster deployment • Reduce cost of change and risk of change Database Replay With Database Replay, DBA’s and System Administrators can test: • Database upgrades, patches, parameter, schema changes, etc. • Configuration changes such as conversion from a single instance to RAC,ASM, etc. • Storage, network, interconnect changes • Operating system migrations • Hardware migrations • Patches, upgrades, • Database parameter changes **Database Upgrades – 10.2.0.4 to 11g is now supported** SPA – SQL Performance Analyzer • Predict regression in SQL statements performance before end users are affected. • Assess impact of change on SQL response time • Unit testing of single SQL or set of SQL statements • Optimizer version changes, access path changes, database parameter changes, index addition and deletion etc • Database Replay Replay real database workload Assess impact of change on total workload throughput • SPA ‘What if’ analysis and predicts deviations in SQL performance Assess impact of change on SQL response time apex:/u01/oracle/scripts> cat load.sh for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 do echo Running Query $i ... echo sqlplus -s sh/sh <<EOF set timing on @query.sql EOF Done apex:/u01/oracle/scripts> cat query.sql select b.country_id,country_name,sum(quantity_sold),sum(amount_sold) from sales a, customers b,countries c where a.cust_id=b.cust_id and b.country_id =c.country_id and a.cust_id=987 group by b.country_id,country_name / ... ... select b.country_id,country_name,sum(quantity_sold),sum(amount_sold) from sales a, customers b,countries c where a.cust_id=b.cust_id and b.country_id =c.country_id and a.cust_id=1660 group by b.country_id,country_name / SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='SALES'; INDEX_NAME -----------------------------SALES_PROD_BIX SALES_CUST_BIX SALES_TIME_BIX SALES_CHANNEL_BIX SALES_PROMO_BIX SQL> ALTER INDEX SALES_PROD_BIX INVISIBLE; Index altered. SQL> ALTER INDEX SALES_CUST_BIX INVISIBLE; Index altered. SQL> ALTER INDEX SALES_TIME_BIX INVISIBLE; Index altered. SQL> ALTER INDEX SALES_CHANNEL_BIX INVISIBLE; Index altered. SQL> ALTER INDEX SALES_PROMO_BIX INVISIBLE; Index altered. SQL> SHOW PARAMETER USE_INVISIBLE NAME TYPE VALUE ------------------------------------ ----------- -----------------------------optimizer_use_invisible_indexes boolean FALSE Database Replay Workflow • Workload Capture External client requests to the database tracked and stored in binary files called capture files • Workload Processing Before replay, captured workload files have to be processed. Process once – replay many times Transforms captured data into replay files • Workload Replay Replay Clients will run the captured and processed workload files on the target server • Analysis and Reporting Analyze capture and replay and report any errors or divergence in data. Compare AWR reports gathered at both capture and replay and provide comparison reports apex:/u01/oracle/capture> ls -l total 66640 -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba -rw-r----1 oracle dba 1131 1152 1078 904 8648 904 1133 1131 1460 1133 916 1132 1145 17104896 26539 43260 20826 306 236 Jul Jul Jul Jul Jul Jul Jul Jul Jul Jul Jul Jul Jul Jul Jul Jul Jul Jul Jul 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 29 14:02 14:02 14:02 14:02 14:02 14:02 14:02 14:02 14:11 14:02 14:02 14:02 14:02 14:15 14:15 14:11 14:11 14:11 14:02 wcr_4j3pkch00384c.rec wcr_4j3pkch003n6y.rec wcr_4j3pkch003s46.rec wcr_4j3pkcs00245s.rec wcr_4j3pkcs00284k.rec wcr_4j3pkd000202y.rec wcr_4j3pkd0003s50.rec wcr_4j3pkd4003s3h.rec wcr_4j3pkd8003h0c.rec wcr_4j3pkds002h0w.rec wcr_4j3pkds003c6q.rec wcr_4j3pkf8002c14.rec wcr_4j3pkg0003w6k.rec wcr_ca.dmp wcr_ca.log wcr_cr.html wcr_cr.text wcr_fcapture.wmd wcr_scapture.wmd Start The Replay Clients apex:/u01/oracle/capture> wrc replaydir=/u01/oracle/capture userid=system password=oracle Workload Replay Client: Release 11.1.0.6.0 - Production on Tue Jul 29 14:41:23 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Wait for the replay to start (14:41:23) Thanks for attending!! GAVIN SOORMA Senior Oracle DBA Specialist HBOS Australia Contact me at : 0417713124 or [email protected] QUESTIONS ANSWERS