> CASE 23,000 Transactions Primarily OLTP in nature fired as part of a
> Benchmark Program using 500 Concurrent users having Corresponding Oracle
> session processes over an SQL*Net
>
> APP Server = 4 CPUs , 4 GB RAM
> DB Server = 4 CPUs , 3 GB RAM
>
> ORACLE 7.3.4.5.0
> Digital Unix 4.0 G
> log_simultaneous_copies = 2*CPU_COUNT = 8
> spin_count = 3500
>
> 1) report.txt :-
> 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 17712253 173478890 9.79
> latch free 34804595 47608331 1.37
> enqueue 1116209 21332017 19.11
> library cache pin 12762 841255 65.92
> SQL*Net more data from client 100049 315101 3.15
> row cache lock 5090 112247 22.05
> log file sync 296670 76643 .26
> library cache load lock 737 46513 63.11
> db file scattered read 241909 25466 .11
> db file sequential read 102217 15063 .15
> buffer busy waits 17792 10519 .59
>
>
> enqueue_resources = 550,000
>
> enque_resources value unable to increase beyond this value . Attemptng
> gives the following
> Error Message (approx.) - Unable to allocate enque_resources for this O/S
>
>
> 2) In order to identify the LATCHNAME on which the waits on latch free
> exist , run the following script repeatedly during the benchmark runs :-
> select a.*,b.*
> from v$session_wait a,v$latchname b
> where a.p2=b.latch#;
>
> RESULT - Most of the waits Occuring on "library cache"
> a few waits on row cache objects & shared pool also exist
>
> Qs. How can this be Addressed ?
>
> shared_pool_size kept to BARE MINIMUM = 130 MB
>
> NOTE We get Error ORA-4031 when shared_pool_size = 100 MB
> 04031, 00000, "unable to allocate %s bytes of shared memory
> (\"%s\",\"%s\",\"%s\")"
>
> shared_pool_reserved_size = 10 MB
Received on Tue Aug 29 2000 - 00:34:32 CDT
