Enqueues are at their MAX value of 200,000 increasing Beyond which failing
with Some Error .
NOTE - Going beyond 500 A/cs per minute Failing Even for Reduced Loads on a
"test" Database
as shown below . Hence the problem does NOT seem to be with enqueue
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 11705094 7907973 .68
db file sequential read 424242 345894 .82
log file sync 2863 83922 29.31
write complete waits 905 71401 78.9
SQL*Net more data from client 3991 36292 9.09
buffer busy waits 1050 26356 25.1
SQL*Net message to client 11705093 8436 0
free buffer waits 60 4718 78.63
enqueue 95 1873 19.72
direct access I/O 116 1249 10.77
log file switch completion 33 1103 33.42
rdbms ipc reply 51 339 6.65
buffer deadlock 52 100 1.92
latch free 3161 50 .02
db file scattered read 288 11 .04
> -----Original Message-----
> From: aaaa wwwwww [SMTP:krisibm_at_lycos.com]
> Sent: Monday, November 27, 2000 3:30 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: SOS Alert
>
> Dear
>
> Going by UR report U have definetely having problem with
> enques
>
> Search for foreign keys and see that they R indexed
>
> Query V$lock and v$sesstat
>
> Rao
> --
>
> On Mon, 27 Nov 2000 00:30:21
> Rod Corderey wrote:
> >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).
> >
> >--
> >Rod Corderey
> >
> >Lane Associates
> >RodCorderey_at_Lane-Associates.com
> >http://www.Lane-Associates.com
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Rod Corderey
> > INET: RodCorderey_at_Lane-Associates.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).
> >
>
>
> Get FREE Email/Voicemail with 15MB at Lycos Communications at
> http://comm.lycos.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: aaaa wwwwww
> INET: krisibm_at_lycos.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
Received on Mon Nov 27 2000 - 05:00:09 CST
