SOS Alert

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com>
Date: Mon, 27 Nov 2000 12:11:59 +0530
Message-Id: <10692.122903@fatcity.com>

CASE In a Bank , Interest Calculation Batch Processing Unable to go beyond 500 A/cs per minute

CAUSE of the Problem is UNKNOWN

ORACLE 7.3.4.5 on AIX 4.3.3
DB Server - IBM S80 Model - 12 CPUs , 3 GB RAM APP Server 1 - IBM S80 Model - 6 CPUs , 2 GB RAM APP Servers 2 & 3 - IBM Thin Nodes model - 4 CPUs , 1 GB RAM

Storage Box :-

---

SS Class Storage
RAID 0+1 - (First Striped & then Mirrored) NOTE - 2 Storage Boxes Exist , one being the Mirror of the Other Striping exists across a set 4 Disks (in one Box) with another 4 being it's mirror
(in another Box).
Thus a Volume Group Contains 8 Disks with 4 Disks Stripe Size = 4K  

NOTE - Runs Tried from BOTH the APP Servers 1 OR 2 ,But with the SAME MAX of 500 A/cs
processed per minute

CPU Utilizations on BOTH APP & DB Server = 40 % wio% on BOTH APP & DB Servers = 35 %
No paging happening on Both APP & DB Servers

• Oracle Contention Values Seem Small to us as shown Below or so they seem to us :-

SVRMGR> Rem System wide wait events for non-background processes (PMON, 
SVRMGR> Rem SMON, etc).  Times are in hundreths of seconds.  Each one of 
SVRMGR> Rem these is a context switch which costs CPU time.  By looking at
SVRMGR> Rem the Total Time you can often determine what is the bottleneck 
SVRMGR> Rem that processes are waiting for.  This shows the total time spent
SVRMGR> Rem waiting for a specific event and the average time per wait on 
SVRMGR> Rem that event.
SVRMGR> select 	n1.event "Event Name", 
     2>        	n1.event_count "Count",
     3> 	n1.time_waited "Total Time",
     4> 	round(n1.time_waited/n1.event_count, 2) "Avg Time"
     5>    from stats$event n1
     6>    where n1.event_count > 0
     7>    order by n1.time_waited desc;
Event Name                       Count         Total Time    Avg Time     
-------------------------------- ------------- ------------- -------------
SQL*Net message from client           10856276      31977110          2.95
enqueue                                   1295        374980        289.56
db file sequential read                3614044        303848           .08
write complete waits                      5812        295937         50.92
latch free                             5045060        242170           .05
SQL*Net more data from client            13939        165275         11.86
log file sync                            12794        146409         11.44
buffer busy waits                       100443         92477           .92

• ALL Literal SQLs were Converted to using Bind variables
• ALL Tables Running on Indexes Without Any FULL Scans happening .
• All the Literal SQLs (Dynamic) Converted to using Bind variables (Static Queries)
• event="10181 trace name context forever, level 1000" NOTE - Set nevertheless , None of the SQLs taking Excessive time to parse though
• NO statistics are Analyzed
• 7 tables involved in the interest Calc. inserts to 2 Tables selects , updates in 5 tables

Qs. Are there Any BASIC O.S./Hardware Features to Enhance Performance on IBM AIX Systems ?

Qs. Is Any Other portion of the report.txt required for perusal ? Received on Mon Nov 27 2000 - 00:41:59 CST