John,
ADDR Addres
INDX Index
INST_ID Instance ID
KSQSTTYP Enqueue Type
KSQSTGET Number of GETs
KSQSTWAT Number of WAITs
CF Controlfile Transaction
CU Bind Enqueue
SQ Sequence Number Enqueue
ST Space Management Transaction
TM DML Enqueue
TX Transaction
For complete list see Oracle8i Reference, Appendix B, Oracle Enqueue Names.
Alex.
P.S. From the given output, it seems that there is a 'Space Management' problem. Is your TEMPORARY tablespace PERMANENT-type?
-----Original Message-----
From: John Kanagaraj [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 11, 2002 10:44 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Large enqueue waits.
Anjo,
So what does this mean?
select * from x$ksqst where ksqstwat != 0;
ADDR INDX KS KSQSTGET KSQSTWAT
-------- ---------- -- ---------- ----------
4612E15C 69 CF 480 1
4612E1D4 84 CU 11709 5
4612F1B4 592 SQ 3577 9
4612F1CC 595 ST 4600 155
4612F294 620 TM 132223 4
4612F2EC 631 TX 68902 75
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
The manuals for Oracle are here: http://tahiti.oracle.com
The manual for Life is here: http://www.gospelcom.net
** The opinions and statements above are entirely my own and not those of my
employer or clients **
> -----Original Message-----
> From: Anjo Kolk [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, June 11, 2002 6:18 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Large enqueue waits.
>
>
> Identify the enqueue type by:
>
> select * from x$ksqst where ksqstwat != 0;
>
> You have to be sys for that .....
>
>
> Anjo.
>
>
> Jenner Mike wrote:
>
> > Rob, Jared, all,
> > By the time I was able to investigate, the problem
> had disappeared.
> > No blocking locks and nothing unusual in v$session_wait by
> that time. I
> > guess I'll set up an automatic script to run perhaps every
> 10 mins that
> > checks and logs this sort of thing.
> >
> > Unless anyone knows if this is remembered internally..
> >
> > Mike Jenner
> > Database Administrator
> >
> > -----Original Message-----
> > Sent: 10 June 2002 16:23
> > To: Multiple recipients of list ORACLE-L
> >
> > Did you look to see if you had any blocking locks occurring
> at this time?
> >
> > RF
> >
> > Robert G. Freeman - Oracle8i OCP
> > Oracle DBA Technical Lead
> > CSX Midtier Database Administration
> > Author: Oracle9i New Features
> > Mastering Oracle8i
> >
> > Clark Griswold: Eddie, has anyone ever told you that you're
> bad luck?
> > Cousin Eddie: Those were my mother's dying words. But I
> guess if your
> > body's covered in third degree burns, and your foot's
> caught in a bear
> > trap, you tend to start talkin' crazy.
> >
> > -----Original Message-----
> > Sent: Monday, June 10, 2002 10:43 AM
> > To: Multiple recipients of list ORACLE-L
> >
> > Hi to you all,
> >
> > High enqueue locks.
> > This morning before I got to work there were a
> group of users who
> > appeared to be hanging at times when they should have been
> performing
> > updates to certain tables.
> > I tracked it down to what I suspect is high enqueue lock
> times. [details are
> > below. I don't normally see enqueue high in v$session_event or
> > v$system_event].
> > I've used Steve's scripts enqueue_locks.sql and
> enqueue_stats.sql but they
> > don't tell me anything that I can understand :-(
> > By now the sessions that were affected have logged
> out or been
> > terminated, so I can't put a 10046 level 8 trace on them.
> >
> > Can I see what the enqueue waits were referring to?
> >
> > Thanks in advance,
> > Mike.
> >
> > Details of findings:
> >
> > 1 select event, s.username ,
> time_waited/100,total_waits, total_timeouts
> >
> > 2 from v$session_event e, v$session s
> >
> > 3 where s.sid= e.sid
> >
> > 4 and time_waited > 100
> >
> > 5 and event like '%enq%'
> >
> > 6* order by time_waited desc ;
> > Total
> > Event USERNAME TIME_WAITED/100
> Waits Timeout
> > -------------------------- ---------- ---------------
> --------- -------
> > enqueue A 5498.45
> 1787 1787
> >
> > enqueue B 3505.52
> 1140 1140
> >
> > enqueue C 3303.44
> 1078 1071
> >
> > enqueue D 209.89
> 69 69
> >
> > enqueue E 63.29
> 21
> > 21
> > enqueue F 16.17
> 14
> > 4
> > 6 rows selected.
> >
> > and using Steve's resource_waiters script:
> > SQL> @resource_waiters
> > Event name [buffer busy waits] enqueue
> > SID PROGRAM TIME_WAITED AVERAGE_WAIT
> > ---- ------------------------------ ----------- ------------
> > All Disconnected Sessions 7720431 306.021346
> > 78 f45run@scc-corp01 (TNS V1-V2) 20989 20989
> > ARC0 oracle@scc-corp01 (ARC0) 2 .25
> >
> > 1 select * from v$system_event
> > 2 where time_waited > 0
> > 3* order by time_waited desc ;
> > Total Time Waitd
> > Average
> > Event Waits Timeout In Hndrds
> > Time
> > ------------------------------------- --------- ------- -----------
> > -----------
> > SQL*Net message from client ######### 0 3271701695
> > 30.397
> > rdbms ipc message 1875169 819790 422614554
> > 225.374
> > slave wait 2213312 ####### 167923522
> > 75.870
> > pipe get 260819 249480 126637278
> > 485.537
> > pmon timer 277326 277293 85337384
> > 307.715
> > smon timer 2783 2776 85322173
> > 30658.345
> > enqueue 25297 25128 7741422
> > 306.021
> > db file sequential read 48265252 0 5125270
> > .106
> > io done 820132 22440 2847667
> > 3.472
> > db file scattered read 1677976 0 1585987
> > .945
> > log file parallel write 575601 2 1276956
> > 2.218
> > log file sync 347401 1089 922192
> > 2.655
> >
> > Mike Jenner
> > Database Administrator
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Jenner Mike
> > INET: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Freeman, Robert
> > INET: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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).
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Jenner Mike
> > INET: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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).
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Anjo Kolk
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Kanagaraj
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
