RE : SOS Alert

THANKS so much for your Association in this . My Replies Follow Your Questions Below

> -----Original Message-----
> From: Rod Corderey [SMTP:RodCorderey_at_Lane-Associates.com]
> Sent: Monday, November 27, 2000 2:00 PM
>
> Hi
>
> . What about the application itself?
>

Ans What do you mean by that ? It is a Banking Product Application Running on Even Bigger Databases on SUN O.S. (a Different O.S. though)

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

Qs. How do we find that ? We Looked at the SQL_TRACE & NONE of the Parse , Execute & fetch timings were NOT Large ( Those which were Above 1 sec were tuned )

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

Qs. What does navigational traffic mean ?

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

Ans 20 Bach processess are fired off almost in parallel in the Background(O.S.) for 20 Sets of A/cs

The 1st Firing gives a thruput of about 250 A/cs per minute . Then the Second is fired Off Almost immediately after firing off of the 1st in Parallel
(from Within the SAME Telnet login ) & so on ... Gradually By the 6th firing , the Upper Limit of 500 a/cs per minute is reached
Thereafter Continuing to Fire for MORE Sets in Parallel Does NOT Cause the Thruput to increase
Thus Scaling Does NOT Occue

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

Ans hash_area_size = 0 , hash_join_enabled =FALSE in the init.ora parameeters

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

Ans After Interest Run of 300 A/cs a Commit is issued . We even reduced it to 100 A/cs before Commit but but with Little benefit

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

Ans Log Switching was Happening every 5 minutes . For Testing we increased the online
redo logfile size to 100 M from 15M causing Logswitch to happen every hour without any Success  

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

Ans This is a pro*c Application . In SQL_TRACE file , Actaul Data Values used to appear for number of SQLs (Dynamic Literal SQLs) which have been Converted to using Bind variable (e.g. :b1) now but without any Benefit
>
> . Is the dynamic sql really necessary to the process, without NDS - and
> sometimes
> with it - it will always slow things down a bit?
>

Ans Conversion to Bind Variables gave Little benefit

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

Ans Moved the Datafiles Belonging to a particular table (Containing inserts)

& it's corresponding indexes too to a Different Local Hard Disk with Little Success

> 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 :-)
>

Ans. OK. Will Furnish the Model after getting the Details

> 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
Received on Mon Nov 27 2000 - 04:45:25 CST