Re: need help on statspack report, Parse CPU to Parse Elapsd %

From: VC <boston103_at_hotmail.com>
Date: Sun, 07 Mar 2004 06:34:59 GMT
Message-ID: <noz2c.196154$uV3.795903@attbi_s51>

Joe,

"Joe" <yung103_at_netvigator.com> wrote in message news:c2e5m7$du45472_at_imsp212.netvigator.com...
> Thanks a lots for the information of DV and DJ.
> It is the Oracle eBusiness Suite, and Oracle not recommend us to make
> changes to the whole system.
> And in fact we don't have enough manpower to change that.
>
> Any advise for me to reduce the enqueue wait and latch free wait. These 2
> numbers are making me uncomfortable.

Firstly, you need to produce a report for a smaller interval ( 10-15 min ) during a typical load. A five-hour report is hardly useful.

Pls. re-read what I wrote about enqueues. They are not a problem but a symptom of a possible problem with sessions probably locking the same rows(s). If this is the case, you need to fix the application. In order to determine who blocks whom, you can use the following statement:

select (select username from v$session where sid=a.sid) ,

         a.sid,
       ' is blocking ',
        (select username from v$session where sid=b.sid) ,
         b.sid

from v$lock a, v$lock b
where a.block = 1

and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2

If you execute the above repeatedly during a a typical load, you may cacth blocking/blocked sessions and decide what to do about it.

Latches, as I wrote, appear to be a symptom of some SQL statements not using bind variables (library cache latches). If it's a packaged application, you can hardly do much about it.

According to the statspack report you posted, CPU is not a bottleneck:

enqueue                                       10,195,809
latch free                                        6,092,953
CPU                                               1,509,296  <-- that's your
CPU
db file scattered read                           537,596
db file sequential read                          369,621

Again, pls. post data collected during a smaller period of time (10-15) min. in order to see what's going on. Probably the picture with CPU and the rest will be different than what your 5 hour report shows..

VC Received on Sun Mar 07 2004 - 00:34:59 CST