Re: wait event 'process startup'

Hmm.....my first thought is... Is your application designed such that users logon to the
database, do a small unit of work and then logoff, and then repeat the process? I'm
thinking you'll see process startup waits if the database is undergoing a large amount
of connect/disconnect activity. Looks like you had 0.17 logons/sec, or one a logon
every 5.88 seconds or so. That's about 10 logons/minute, every minute. Based on
the Top 5 Timed events, all that's happening for the entire 30 minutes is logons
(and possibly logoffs). If the unit of work done by each user is very, very small,
then I suppose it's possible that the overwhelming majority of the work is in getting
the user logged in. (Process creation, session state initialization, etc)
If that's the case, first thing to look at is application design. Do users really need
to connect, do a tiny amount of work, and logoff? Can they stay logged in,
and avoid the overhead of reconnecting each time?

Second, if the application design can't be changed, look at shared server/MTS.
Running shared server, and prespawning some server processes may help avoid
some of the process creation overhead.

Caveat: This is a tiny amount of data I looked at, and I know nothing about
your application or how it works. This advice may be worth even less than
you paid for it...;-)

Hope that helps,

-Mark

On Mon, 2004-12-27 at 20:48, rjamya wrote:

> Hi all,
>
> I have this issue notice in one of the databases ... process startup
> is larger than normal.
>
> Here is a snippett ...
>
> STATSPACK report for
>
> DB Name DB Id Instance Inst Num Release Cluster Host
> ------------ ----------- ------------ -------- ----------- ------- ------------
> MIS 1893113387 MIS1 1 9.2.0.4.0 YES elara
>
> Snap Id Snap Time Sessions Curs/Sess Comment
> ------- ------------------ -------- --------- -------------------
> Begin Snap: 2369 27-Dec-04 19:50:01 ####### 1.0
> End Snap: 2383 27-Dec-04 20:20:10 ####### 1.0
> Elapsed: 30.15 (mins)
>
> Cache Sizes (end)
> ~~~~~~~~~~~~~~~~~
> Buffer Cache: 64M Std Block Size: 8K
> Shared Pool Size: 384M Log Buffer: 2,000K
>
> Load Profile
> ~~~~~~~~~~~~ Per Second Per Transaction
> --------------- ---------------
> Redo size: 2,553.73 7,777.27
> Logical reads: 416.71 1,269.08
> Block changes: 10.32 31.43
> Physical reads: 1.11 3.39
> Physical writes: 0.15 0.45
> User calls: 14.26 43.42
> Parses: 5.67 17.27
> Hard parses: 1.01 3.07
> Sorts: 2.24 6.82
> Logons: 0.17 0.53
> Executes: 7.09 21.60
> Transactions: 0.33
>
> % Blocks changed per Read: 2.48 Recursive Call %: 52.42
> Rollback per transaction %: 2.36 Rows per Sort: 121.69
>
> Instance Efficiency Percentages (Target 100%)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 100.00 Redo NoWait %: 100.00
> Buffer Hit %: 99.73 In-memory Sort %: 100.00
> Library Hit %: 93.10 Soft Parse %: 82.22
> Execute to Parse %: 20.06 Latch Hit %: 100.00
> Parse CPU to Parse Elapsd %: 84.29 % Non-Parse CPU: 83.90
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 89.62 89.75
> % SQL with executions>1: 55.50 41.33
> % Memory for SQL w/exec>1: 51.11 38.01
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~ % Total
> Event Waits Time (s) Ela Time
> -------------------------------------------- ------------ ----------- --------
> process startup 1,120 1,080 94.71
> CPU time 37 3.27
> db file sequential read 478 3 .28
> log file sync 534 3 .26
> CGS wait for IPC msg 220,250 3 .26
> -------------------------------------------------------------
> Cluster Statistics for DB: MIS Instance: MIS1 Snaps: 2369 -2383
>
>
> This is a fairly lightweight db, any ideas where to start looking at??
>
> 9204/AIX 5.2ml2/RAC/2 nodes/24cpu/64gb
> TIA
> Raj

--
Mark J. Bobak
mark_at_bobak.net
"Science is the belief in the ignorance of experts."  --Richard P.
Feynman


--
http://www.freelists.org/webpage/oracle-l