Presentation 36492 OracleAS Tuning Techniques Donald K. Burleson Col. John Garmany Burleson Oracle Consulting

1. Presentation 36492OracleAS Tuning TechniquesDonald K. BurlesonCol. John GarmanyBurleson Oracle Consulting

4. Oracle Training by Don Burleson

5. www.rampant.cc Oracle Books from $9.95

6. Guidehorse.com

7. John Garmany Retired Army Lt. Col. • West Point Graduate • Masters Degree Information Systems • Graduate Certificate in Software Engineering • Airborne Ranger

8. On-site custom Oracle training • Oracle Tuning & Oracle Support • Remote DBA Support

9. Topics: • OracleAS Tuning Approach • OracleAS Monitoring • Tuning with RAM • Load Balancing

10. Enhancement request for OracleAS: • Add a new global parameter: run_fast = yes

11. OracleAS Architecture Internet Web Cache Web Cache Web Cache Web Cache HTTP Server HTTP Server HTTP Server HTTP Server HTTP Server HTTP Server RAC Server RAC Server RAC Server RAC Server RAC Server Database Files

12. Keys to Success • Parameter Tuning • RAM Cache Tuning • Server Tuning

13. Parameter Tuning • OracleAS parameters - Adjusting the Oracle9iAS configuration parameters for each Oracle9iAS component has influence performance and throughput. • Database parameters – Because most Oracle9iAS systems are disk I/O intensive, adjusting the Oracle database parameters for the Infrastructure database (iasdb) and the back-end database can heavily influence performance.

14. RAM Tuning • Data buffer tuning – Adding RAM to the database db_cache_size on the Oracle Infrastructure and back-end database can greatly reduce disk I/O and improve throughput. • Web cache tuning – Adding RAM to the Oracle9iAS web cache can improve the delivery rates of HTML and XML though the Oracle HTTP Server (OHS).

15. Server tuning • Hardware configuration – Adding RAM of CPU resources to existing servers will improve the throughput on the server • Hardware load balancing – The addition of new servers to the Oracle9iAS farm and relocating Oracle9iAS components. Spare servers can be configured with both Web Cache and App Server, and the appropriate components can be started as-needed. • Server parameter tuning – Adjusting the parameters on your server can have a huge impact on the performance of the OracleAS.

16. Monitoring Techniques • Response Time Monitoring – DCM and OEM • Wait Event Monitoring – Determine the source of Latency for each Component. • Server Resources – Once the farm is tuned, overloads can be addressed with dynamic server allocation.

17. Wait Event Monitoring (for isadb and database) How would you tune this database? % TotalEvent                           Waits  Time (s)  Ela Time------------------------------  -----  --------  --------CPU time                                     30     71.43db file parallel write             95         1     23.53control file sequential read       54         1      2.33log file parallel write            62         0       .95db file sequential read            20         0       .68 How would you tune this database? % TotalEvent                           Waits  Time (s)  Ela Time------------------------------  -----  --------  --------db file sequential read   45        22     41.43db file scattered read             95        14     25.55control file sequential read       54         1      2.33log file parallel write            62         0       .95db file parallel write             20         0       .68

18. OracleAS Monitoring

19. Dynamic Monitoring Service (DMS) • OC4J – Measure Parse Time for Incoming Request and Free RAM in the JVM • Portal – Display Portal Metrics • Servlet – Instrument Servlets to Generate Performance Metrics • OHS – Measure Active HTTP Requests

20. DMS has over 300 metrics dmstool -l |grep completed /appsvr/OC4J:3303:6004/oc4j/default/WEBs/parseRequest.completed /appsvr/OC4J:3303:6004/oc4j/default/WEBs/processRequest.completed /appsvr/OC4J:3303:6004/oc4j/default/WEBs/resolveContext.completed /appsvr/OC4J:3303:6004/oc4j/portal/WEBs/parseRequest.completed /appsvr/OC4J:3303:6004/oc4j/portal/WEBs/processRequest.completed /appsvr/OC4J:3303:6004/oc4j/portal/WEBs/resolveContext.completed /ap/OC4J:3303:6004/oc4j/syndserver/WEBs/parseRequest.completed /ap/OC4J:3303:6004/oc4j/syndserver/WEBs/processRequest.completed /ap/OC4J:3303:6004/oc4j/syndserver/WEBs/resolveContext.completed

21. Collect 100 sets at 60 second intervals dmstool -i 60 -c 100 \ /appsvr/Apache:2534:6004/Apache/handle.completed \ /appsvr/Apache:2534:6004/Apache/request.completed \ /appsvr/Apache:2534:6004/Apache/handle.completed \ /appsvr/Apache:2534:6004/Apache/request.completed >> t1.lst Output Listing Sun Jul 13 20:19:43 MDT 2003 /appsvr/Apache:2534:6004/Apache/handle.completed 240320 ops /appsvr/Apache:2534:6004/Apache/request.completed 146504 ops /appsvr/Apache:2534:6004/Apache/connection.completed 56908 ops

22. Compute delta in spreadsheet

23. Plot with Chart Wizard

24. DMS can be scripted: #!/bin/ksh PATH=$PATH:/home/oracle/oraportal904/bin export PATH # Dump Stats for Later Analysis dmstool -dump >> dumparch.lst # Dumping OHS Stats to a File dmstool -table ohs_server >> ohs.lst

25. Sending OHS stats to a flat file: # Dumping OHS Stats to a File dmstool -table ohs_server >> ohs.lst cat ohs.lst|grep connection.active > con_active.lst cat ohs.lst|grep request.active > req_active.lst cat ohs.lst|grep busyChildren.value > busy_child.lst cat ohs.lst|grep readyChildren.value > readyChild.lst cat ohs.lst|grep numChildren.value > det.lst

26. OHS Server Output Sun Jul 13 21:01:45 MDT 2003 ---------- ohs_server ---------- busyChildren.value: 16 ... childStart.count: 24748 ops connection.active: 24 threads ... numChildren.value: 44 ... readyChildren.value: 27 ... request.avg: 15321 usecs request.completed: 150942 ops ...

27. Plotting OHS response time

28. List OHS performance metrics dmstool -table ohs_module -c 1 Name: mod_oc4j.c ... decline.count: 13487 ops handle.active: 0 threads handle.avg: 3 usecs handle.completed: 13487 ops handle.maxTime: 8 usecs handle.minTime: 2 usecs handle.time: 43710 usecs Name: http_core.c ... decline.count: 0 ops handle.active: 0 threads handle.avg: 0 usecs handle.completed: 0 ops handle.maxTime: 0 usecs Hard to parse The output

29. Computing real response time One of the problems with the OHS statistics is that the one-time operations will skew the overall averages in the ohs_response listings. (time – min – max) real_average = ------------------------ (completed – 2) Using the data from the previous mod_oc4j.c listing, we can compute the real response time: (43,710 – 2 – 8) real_average = ------------------------ (13,487 – 2) (43,700) real_average = ------------------ = 3.24 milliseconds (13,485)

30. Using Aggrespy

32. Web Cache Monitoring

33. Web Server Web Server OracleAS Web Cache Trigger Programmatic Web Cache Internet Database

34. Web Cache Tuning • Static and Dynamic Information • Cacheability Rules • Cache Invalidations • Multi-version HTML • Rule for Each Page Component

35. Web Cache statistics: • Requests – This shows the current, average and max transaction per second. The backlog section indicates that the Web Cache is overwhelmed and another Web Cache server should be started. • Errors – This summarized the network, site busy and particle-page errors for the Web Cache. • Misses – This section shows cacheable and non-cacheable misses along with the number of refreshes for the Web Cache. • Compression – The compression sections show the total amount of RAM saved by compression and provides a great gauge of the effectiveness of the Web Cache.

36. OracleAS Load Balancing

37. Software Load Balancing • Web Cache to OHS – Web Cache interrogates OHS statistics and routes to least loaded. • OHS to Database Listener – OHS Distributes load to multiple Listeners • Database Listener – Listeners to Multiple Dispatchers under MTS, that load balance to least loaded RAC Instance.

38. Web Server Web Server Web Server Web Cache Web Cache Instance Instance Instance OracleAS Load Balancing Web Cache Tier Application Server Tier Database Server Tier Database

39. Hardware Load Balancing Internet Blade Server Rack OHS & WC Web Cache Web Cache Web Cache Web Cache OHS & WC OHS & WC HTTP Server HTTP Server HTTP Server HTTP Server HTTP Server HTTP Server OHS & WC Oracle RAC Oracle RAC RAC Server RAC Server RAC Server RAC Server RAC Server Oracle RAC Oracle RAC Database Files Oracle RAC

40. Monitoring Servers with vmstat SAMPLE_TIME=300while truedo vmstat ${SAMPLE_TIME} 2 > /tmp/msg$$# run vmstat and direct the output into the Oracle table . . . cat /tmp/msg$$|sed 1,3d | awk '{ printf("%s %s %s %s %s %s\n", $1, $8, $9, 14, $15, $16) }' | while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU DLE_CPU do $ORACLE_HOME/bin/sqlplus -s perfstat/perfstat@iasdb<<EOFinsert into perfstat.stats\$vmstat values ( sysdate, $SAMPLE_TIME, '$SERVER_NAME', $RUNQUE, $PAGE_IN, $PAGE_OUT, $USER_CPU, $SYSTEM_CPU, $IDLE_CPU, 0 ); EXITEOF donedonerm /tmp/msg$$

41. Monitoring Servers with vmstat root> vmstat 5 5 kthr memory page faults cpu ----- ----------- ------------------------ ------------ ----------- r b avm fre re pi po fr sr cy in sy cs us sy id wa 7 5 220214 141 0 0 0 42 53 0 1724 12381 2206 19 46 28 7 9 5 220933 195 0 0 1 216 290 0 1952 46118 2712 40 55 0 5 13 5 220646 452 0 14 1 33 54 0 2130 86185 3014 38 59 0 3 6 5220228 672 0 0 0 0 0 0 1929 25068 2485 25 49 16 10 Assuming an 8 CPU server: • CPU has enqueues when runqueue (r column) > cpu_count • RAM is paging when scan rate (sr) peaks before page-in (pi)

42. Server exception reports Wed Dec 20                                                             page    1                                 run queue > 2                         May indicate an overloaded CPU.                    When runqueue exceeds the number of CPUs                  on the server, tasks are waiting for service.SERVER_NAME     date       hour      runq pg_in pg_ot  usr  sys  idl       --------------- -------------------- ---- ----- ----- ---- ---- ----       AD-01           00/12/13    17          3     0     0   87    5    8                      Whenever Unix performs a page-in, the RAM memory          on the server has been exhausted and swap pages are being used.SERVER_NAME       date       hour      runq pg_in pg_ot  usr  sys  idl       ----------------- -------------------- ---- ----- ----- ---- ---- ----       AD-01             00/12/13    16          0     5     0    1    1   98       AD-01             00/12/14    09          0     5     0   10    2   88       AD-01             00/12/15    16          0     6     0    0    0  100       AD-01             00/12/19    20          0    29     2    1    2   98       PROD1DB           00/12/13    14          0     3    43    4    4   93       PROD1DB           00/12/19    07          0     2     0    1    3   96       PROD1DB           00/12/19    11          0     3     0    1    3   96      

43. Fix for Server Stress • Overloaded CPU • Offload Task to Another Server • Add CPUs • Add Additional Instances/Servers • Overloaded RAM • Add RAM  Cheap $1k/gig • Reallocate RAM from Other Components

44. RAM Disk Solution • Disk I/O remains the biggest bottleneck • 100 gig RAM costs $100k • 6,000 times faster than disk for Oracle Your app will still run inefficiently, but it runs 6,000 times faster!

45. UNIX server Monitoring rules: • The UNIX vmstat utility provides a wealth of information about the ongoing performance of the Oracle9iAS server. • The vmstat run queue value (r) can indicate a CPU shortage whenever the run queue exceeds the number of CUs on the server. • The vmstat page in values (pi) can indicate a RAM memory shortage. • You can easily define vmstat extension table to hold historical server information and use a UNIX shell script to periodically collect server performance information. • The UNIX server information can be used to generate alert reports and long-term trend reports.

46. Details on Oracle server Monitoring: Oracle9i UNIX Administration Handbook By Don Burleson • In the OW bookstore!

47. OracleAS Case Study

48. Response-time monitoring example with Forms Server: • Database Time • Forms Server Time • Network Time • Client Time

49. Client Time Database Form Server Database Server Client Time Network Time Forms Server Time Total Response Time:

50. OracleAS Case Study:Forms Server Tuning • Using OracleAS form server logs, you can often determine the total end-to-end response time. TSE FSERVER_START 0 0 2507559367308400 TSE DBLOGON_START 0 0 2507559367308400 TSE DBLOGON_END 0 0 2507559461832800 Opened file: /u00/app/oracle/prod/forms/F_LOGIN.fmx TSE FSERVER_END -1 0 2507559939113600 TSE FSERVER_START -1 1344 2507560872293600 TSE DB_START 0 0 2507560872801600 TSE DB_END 0 0 2507560967177800