| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: DB freezes ... no indications of any kind
This query was totally customized for our production database.
We run this query every 2 secs
This is querying the main knowN events which causes sometimes the database
to freeze
especially the latch free wait you can customize it as you want.
the format comes out like below
SID EVENT_WITH_OSUSER
P1RAW KSUSSP1 P2 KSUSSP3
----------
----------------------------------------------------------------------------
---- -------- ---------- ---------- ----------
929 ,printstop,TECHSUPP6(LATCH FREE)
DA50D9C8 3662731720 66 0
SID GIVES U THE SID NAME
EVENT_WITH_OSUSER GIVES THE OSUSER AND THE EVENT IT IS WAITING ON
P1RAW GIVES U THE RAW DATA FOR THAT PARTICULAR EVENT THIS FOR EXAMPLE CAN BE
LINKED IN LATCH FREE TO V$LATCH TO GET THE LATCH NAME IT IS WAITING ON AND
WHERE AND WHY ALSO
KSUSSP1 GIVES U THE P1 VALUE OF SESSION_WAIT WHICH USUALLY GIVES THE
FILE_NUMBER AND OTHER THINGS AND P2 GIVES THE BLOCK NUMBER OR OTHER THINGS
AND IF IT IS LATCH FREE IT WILL GIVE THE LATCH NUMBER.
I am not able to get the descriptions immediately.But I will mail it to you
as to what all it points to
As you might be knowing the v$sesssion_wait links to 2 x$tables X$KSUSECST
and x$ksled, The x$ksled is one way the static table which holds the texts
for all the events which are going on in session_Wait and the X$KSUSECST
gives you the actual waits going based on sids
What I have done is take what i want from x$ksled and designed this small
query for my convenience
If you are having 9i client installed u can put this query on performance
manager to the user defined chart and allow it to query every 2mins or how
many ever minutes u want and u can even start recording this whole event so
that u can trace back just before the freeze occured.
We have a very high oltp system and we had hectic problems once we migrated
from 8.0.6 to 8.1.7 and hence I started writing these queries since our
databases started to freeze without notice.
I will let u know more on this query Sorry about less information given
Once u run this query side by side run this another query which will point
to where latch is waiting for and why
I am able to track as to where the latch is getting screwed but the
whycolumn in this query which I am using I am not able to track why it
waiting for This only tracks the shared_pool,library cache and row cache
objects,It gives u the sql address also which might be screwing u up.
Even this I run every 2 secs in the peak time in order to know where we are
getting screwed.
select /*+ USE_NL(b c) */
a.kslltnum,a.addr,e.KSLLWNAM||'['||e.KSLLWLBL||']'"Latch_name_with_label",a.
kslltwhy,a.kslltwhr,c.KSUSESQL,
c.KSUSESQH from x$ksllt a, v$session_Wait b,x$ksuse c,x$kslwsc d,x$ksllw e
where b.p1raw=a.addr
and p2 in(106,105,95)
and b.sid=c.indx and a.kslltwhr=d.indx and d.indx=e.indx
The above gives the results as below
KSLLTNUM ADDR Latch_name_with_label KSLLTWHY KSLLTWHR KSUSESQL KSUSESQH
---------- --------
----------------------------------------------------------------------------
---- ---------- ---------- -------- ----------
95 8000C114 kqreqd: rel enqueue[]
0 801 D76DE55C 2392129741
The below said qeury gives u the buffer busy waits along with its file and
block number from this u can later on query the db_objects to find out which
object is getting screwed.
All these queries are made to run every 2 secs in our main production
environment.
and we put all this into recording in 9i performance manager in order to
track what has happened
This 9I's performance manager has helped me a lot in solving some probs
select s.indx Sid,f.fnnam Filename,s.KSUSSP2 BLOCK,s.ksussp3 Waiting_on from
X$KSUSECST s,x$kccfn f
where f.fnnam is not null and f.fntyp=4
and s.KSUSSOPC=75
and s.KSUSSP1=f.fnfno and
bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksussseq!=0
I will let u know once my head is free.
If you can and quite confident on these queries try running them and start
recording the events so that u can analyze it later on .
or take immediate action when something goes wrong
Please try to install the performance manager of 8i which will ease most of
your work
u dont have to run the query manually this will run based on the time u have
set and will start recording too.
-----Original Message-----
Sent: Tuesday, March 12, 2002 11:53 AM
To: Multiple recipients of list ORACLE-L
And how would I interpret the output of this query?
Thanks Arun
Raj
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
Sent: Tuesday, March 12, 2002 10:14 AM
To: Multiple recipients of list ORACLE-L
If you are able to login and query the session_Wait try using the below said query
select s.INDX
SID,u.KSUUDNAM||','||u.KSUSEUNM||','||u.KSUSEMNM||'('||decode(KSUSSOPC,2,'LA
TCH FREE',12,'ENQUEUE',94,'db file sequential read',
95,'db file scattered read',144,'LIBRARY CACHE PIN',75,'buffer busy waits',
145,'library cache lock',146,'library cache load lock',91,'log file
sync',KSUSSOPC)||')' EVENT_WITH_OSUSER,
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arun Chakrapani INET: ArunC_at_1800FLOWERS.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-LReceived on Tue Mar 12 2002 - 13:24:25 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |