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

1
Presentation 36492OracleAS Tuning
TechniquesDonald K. BurlesonCol. John
GarmanyBurleson Oracle Consulting
2
(No Transcript)
3
(No Transcript)
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.55
  control 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/OC4J33036004/oc4j/default/WEBs/parseRequ
  est.completed
• /appsvr/OC4J33036004/oc4j/default/WEBs/processRe
  quest.completed
• /appsvr/OC4J33036004/oc4j/default/WEBs/resolveCo
  ntext.completed
• /appsvr/OC4J33036004/oc4j/portal/WEBs/parseReque
  st.completed
• /appsvr/OC4J33036004/oc4j/portal/WEBs/processReq
  uest.completed
• /appsvr/OC4J33036004/oc4j/portal/WEBs/resolveCon
  text.completed
• /ap/OC4J33036004/oc4j/syndserver/WEBs/parseReque
  st.completed
• /ap/OC4J33036004/oc4j/syndserver/WEBs/processReq
  uest.completed
• /ap/OC4J33036004/oc4j/syndserver/WEBs/resolveCon
  text.completed

21
Collect 100 sets at 60 second intervals
dmstool -i 60 -c 100 \ /appsvr/Apache25346004/Ap
ache/handle.completed \ /appsvr/Apache253460
04/Apache/request.completed \ /appsvr/Apache25
346004/Apache/handle.completed
\ /appsvr/Apache25346004/Apache/request.complete
d gtgt t1.lst Output Listing Sun Jul 13
201943 MDT 2003 /appsvr/Apache25346004/Apache
/handle.completed 240320 ops /appsvr/Apache253
46004/Apache/request.completed 146504
ops /appsvr/Apache25346004/Apache/connection.com
pleted 56908 ops
22
Compute delta in spreadsheet
23
Plot with Chart Wizard
24
DMS can be scripted

• !/bin/ksh
• PATHPATH/home/oracle/oraportal904/bin
• export PATH
• Dump Stats for Later Analysis
• dmstool -dump gtgt dumparch.lst
• Dumping OHS Stats to a File
• dmstool -table ohs_server gtgt ohs.lst

25
Sending OHS stats to a flat file

• Dumping OHS Stats to a File
• dmstool -table ohs_server gtgt ohs.lst
• cat ohs.lstgrep connection.active gt
  con_active.lst
• cat ohs.lstgrep request.active gt
  req_active.lst
• cat ohs.lstgrep busyChildren.value gt
  busy_child.lst
• cat ohs.lstgrep readyChildren.value gt
  readyChild.lst
• cat ohs.lstgrep numChildren.value gt det.lst

26
OHS Server Output

• Sun Jul 13 210145 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
31
(No Transcript)
32
Web Cache Monitoring
33
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
OracleAS Load Balancing
Application Server Tier
Web Cache 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_TIME300while truedo vmstat
  SAMPLE_TIME 2 gt /tmp/msg run vmstat and
  direct the output into the Oracle table . . .
  cat /tmp/msgsed 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_at_iasd
  bltltEOF insert 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

• rootgt 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 5 220228 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) gt
  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 gt 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
Total Response Time
50
OracleAS Case StudyForms 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
51
Extend iasdb for performance monitoring
create table  FormStats (       FORM_ID
VARCHAR2(120),       EVENT      
VARCHAR2(120),       FSERVER  NUMBER,      
DBASE       NUMBER,       NWORK     
NUMBER,       CLIENT      NUMBER,      
DATE        DATE) 
52
Load the table from log data
while (((str in.readLine()) ! null)  if
(str.startsWith("TSE")) Add time
to appropriate tier   if
(str.startsWith(" "))          1 -
Extract Event and Form Name      2 -
Load record into database      3 - Clear
times   
53
With the table, reporting is easy
Select Number of Form Events with Database Access
Time lt 4 seconds SELECT COUNT() FROM
FormStat WHERE (DBASE)/1000) gt 4 AND DATE gt
SYSDATE-1 AND DATE lt SYSDATE Select the Form
with the greatest time spent in the Forms
Server SELECT Form_ID, FSERVER FROM
FormStat Where FSERVER (SELECT MAX(FSERVER)
FROM FormStat)
54
Plotting response time data
55
98 Threshold
Form,Database, Form,Dbase,
Network,Client Network
Form,Dbase Seconds Events
Events Events lt 01
63,318 56.68 91,088 81.54
109,580 98.09 lt 02 79,244 70.93
99,264 88.85 110,798 99.18 lt 03
88,512 79.23 103,028 92.22
111,364 99.68 lt 04 93,640 83.82
104,994 93.98 111,556 99.86 lt 05
96,900 86.74 106,184 95.05
111,630 99.92 lt 06 99,036 88.65
106,950 95.73 111,660 99.95 lt 07
100,740 90.18 107,484 96.21
111,676 99.96 lt 08 101,954 91.26
107,902 96.59 111,682 99.97 lt 09
103,016 92.21 108,240 96.89
111,690 99.98 lt 10 103,778 92.89
108,490 97.11 111,698 99.98 lt 15
106,074 94.95 109,226 97.77
111,708 99.99 lt 20 107,216 95.97
109,604 98.11 111,708 99.99 lt 30
108,432 97.06 110,000 98.46
111,708 99.99 lt 60 109,834 98.32
110,552 98.96 111,710 99.99 Total
Events 111,716 111,716
111,716
56
Top offending Forms

• Top 10 Forms and Events that use the most Average
  Form Server Time
• with a minimum of 10 executions and greater than
  2 seconds for execution.
• 1. Form d\prod\forms\F_END_USER_GENERATED_LETT
  ERS.fmx
• Event CLICK F_END_USER_GENERATED_LETTERS
  BUTTONS SAVE_BTN 1 MOUSE
• Avg Tm 5.00 Seconds. Number of Executions
  62
• 2. Form d\prod\forms\F_PC_PICK_RETURNS.fmx
• Event CLICK F_PC_PICK_RETURNS BUTTONS
  PROCESS 1 MOUSE
• Avg Tm 4.00 Seconds. Number of Executions
  13
• Top 10 Forms and Events that use the most Average
  Database Time
• with a minimum of 2 executions and greater than 5
  seconds for execution.
• 1. Form d\prod\forms\f_pc_case_maint.fmx
• Event CLICK F_DIARY DIARY_TAB_ALLOUT
  DATE_OF_INCIDENT 9
• Avg Tm 472.00 Seconds. Number of Executions
  2

57
Conclusions

• Develop a proactive, time-based performance data
  collection scheme. Real-time OEM and Aggrespy
  metrics are of little use.
• Optimize by adjusting RAM resources parameters.
• Once the system is optimized, server monitoring
  is critical
• Server Load Balancing is Critical to properly
  scale Oracle9iAS

58
Current Books by Don Burleson
My web site dba-oracle.com E-mail me at
don_at_burleson.cc