Re: SOS Alert

Hi

. What about the application itself?

. You mention that accesses are via indexes but are they the most optimal for the   retrieval direction?

. Is there any purely navigational sql traffic that is using table accesses, if so   why?  

. If you take a single iteration of the calculation process and analyse each of the   steps, is the process taking 1/500 minute or is the total process slowing due   to contention eg intermittant lock waits etc?

. Are there any database configuration parameters out of step with what the   process is requesting. eg small sga, substantial use of hash joins with small   hash_area_size etc

. At what point in the total run process are commits being executed?

  If the largest granularity of commit is not at account level, are there any   issues with redos affecting the performance?

. I'm not sure whether you are inferring that dynamic sql is used or that you mean   hard values were embedded in sql statements which have now been replaced with   variables and assignments.

  If you are using dynamic sql then as you are on 7.3 NDS is not available, so   are you using DBMS_SQL or a C process for executing the dynamic sql.   

. Is the dynamic sql really necessary to the process, without NDS - and sometimes   with it - it will always slow things down a bit?

Other issues might involve the mix of physical distribution of the data set involved in a calculation batch, I am assuming that as it is a bank the data set is fairly large.

sorry this a bit sketchy and questions rather than answers but to offer much more I would need to see the calculation process itself and data model against which it is being executed. Or at least the answers to the questions :-)

I might be being a bit blinkered but I would exhaust the design characteristics before addressing hardware any further.

good luck

Rod

-- 
Rod Corderey

Lane Associates
RodCorderey_at_Lane-Associates.com
http://www.Lane-Associates.com

VIVEK_SHARMA wrote:


> 

> 

> 

> 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 ?

> 

> Please Revert to me for any further info / Clarifications ?

> 

> --

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> --

> Author: VIVEK_SHARMA

>   INET: VIVEK_SHARMA_at_infy.com

> 

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).