Although the wait per enqueue is high, it is not where
the majority of wait time is being spent. I would be
more concerned about the full table scans as indicated
by the 'db scattered read events'.
If you can capture the sid in v$session_wait for this
event, you can track down the object being full table
scanned and the offending SQL. Perhaps it is normal,
perhaps not. It could be driving longer waits on
sequential reads and potentially enqueues for the ST
enqueue (your dump did not actually reveal anything
waiting on an enqueue, just holding them. again only
147 instances of where a wait actually occurred over a
12 or 24 hour period is pretty low).
- "Baker, Barbara"
<bbaker_at_denvernewspaperagency.com> wrote:
> List:
> We've been fighting problems for several days. I've
> sort of overwhelmed
> myself with data, but I don't know what any of it
> means.
>
> Solaris 2.6, Oracle 8.0.5, MTS
> Users complain of extreme slowness. No errors in
> alert, no trace files
> generated.
> Database is bounced every day. I capture wait
> statistics each day before
> the database goes down. The statistics from
> v$system_event for enqueue
> waits has gone up considerably since the problems
> started last Wednesday.
> But when I look at v$lock (I'm using Steve Adams'
> enqueue_locks.sql
> scripts), nothing pops up.
>
> Any ideas where I should start looking? I would
> appreciate any help.
> (I really believe this is a connectivity
> (networking) issue, but don't know
> how to confirm this)
> Thanks!
> Barb
>
> (accumulted since last night at 11:00 pm)
>
>
> EVENT TOTAL_WAITS
> TOTAL_TIMEOUTS TIME_WAITED
> AVERAGE_WAIT
> --------------------------- -----------
> -------------- -----------
> ------------
> latch free 814316
> 4064 106360
> .130612686
> enqueue 147
> 26 12033
> 81.8571429
> free buffer waits 4
> 0 23
> 5.75
> buffer busy waits 2959
> 0 567
> .19161879
> log file parallel write 68177
> 0 78788
> 1.155639
> log file sync 66683
> 1 77517
> 1.16247019
> db file sequential read 1385334
> 0 144617
> .104391432
> db file scattered read 1113301
> 0 142545
> .12803815
>
>
> (The info captured below is unusual. running this
> repeatedly normally shows
> nothing
> except smon TS resource wait)
>
>
> RESOURCE NSID SID HOLDING WANTING
> SECONDS
> -------------------- ----- ---- ------- -------
> ----------
> RT-1-0 4 LGWR X
> 0
> TM-1949-0 46 46 SX
> 0
> TM-1999-0 423 423 SX
> 4
> 46 46 SX
> 0
> TM-2014-0 46 46 SX
> 0
> TM-2106-0 46 46 SX
> 0
> TM-2218-0 46 46 SX
> 0
> TM-2270-0 423 423 SX
> 4
> TM-2275-0 423 423 SX
> 4
> 46 46 SX
> 0
> TS-1-8388610 6 SMON SX
> 48069
> TX-1114154-43605 46 46 X
> 0
> TX-852064-43554 423 423 X
> 4
>
>
> (Below is also unusual. Running this repeatedly
> normally returns no rows)
>
>
> Sess Ser Wait Wait Time W'd So
> ID No Event State W'd (ms) Far (ms)
> P1 P2
> P3
> ---- ------ ---------- -------- -------- --------
> -------------- ----------
> ----
> 16 19 latch free WAITING 0 0
> 2147519876 59
> 0
> 92 38 latch free WAITED S -1 0
> 2147519876 59
> 0
> HORT TIM
> E
>
> 565 31 latch free WAITING 0 0
> 2147519876 59
> 0
> 636 11604 latch free WAITED S -1 0
> 2147519876 59
> 0
> HORT TIM
> E
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Baker, Barbara
> INET: bbaker_at_denvernewspaperagency.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).
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Pass
INET: wbpass_at_yahoo.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).
Received on Thu May 23 2002 - 19:00:38 CDT
