| 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: Evaluation questions
Rachel,
This is indeed harder than one would expect.
It is simple in the case of userA still working on the locking transaction, which I've put together a script for:
select u.name Owner,
o.name Object,
l.sid SID,
s.username Username,
t.sql_text SQL,
l.type Type,
lmode,
decode(lmode, 1, 'NULL', 2, 'Row Share', 3, 'Row Exclusive', 4,
'Share', 5, 'Share Row', 6, 'Exclusive') mode_desc,
request,
decode(request, 1, 'NULL', 2, 'Row Share', 3, 'Row Exclusive', 4,
'Share', 5, 'Share Row', 6, 'Exclusive') request_desc
from v$lock l,
v$session s,
sys.obj$ o,
sys.user$ u,
v$sqltext t
where l.type in ('RW', 'TM', 'TX', 'UL')
and l.sid = s.sid (+)
and l.id1 = o.obj# (+)
The problem lies in when they move on to another transaction without a commit - and personally if this is the case then I should think the developers deserve a DAMNED good slap around the head - as it makes it extremely difficult to track the offending SQL. There *seems* to be no way of actually doing this from within the v$ tables either..(That humble old me knows of anyway:)
If however you have a snapshot of all of the relevant tables (v$lock, v$session, v$sqltext) at the specific time the lock was placed, then it's as simple as above. All you would have to do is trace back to the time the lock was placed, and match the sid to the one in v$session, and then further on down joining on sql_hash_value & hash_value as above, based on a time stamp.
This is of course possible with our tool - but it means setting up a repository of 3 collections, and you would probably want to fire them off every 1-2 minutes (maybe higher) to guarantee collecting the data. You then pull the data in to a reporting tool (excel for instance) to analyse the data.
This is a first pass attempt - I'm going to come back to it in a couple of days to see what else I can find out / do. In the meantime does anybody else have anything to add to this discussion - I think it would be a great help to a lot of people if we can come up with a workable solution for this!
Cheers
Mark
-----Original Message-----
Carmichael
Sent: Tuesday, July 31, 2001 04:48
To: Multiple recipients of list ORACLE-L
Gary,
I wish it did. I worked with Q Diagnostics for quite a while, and worked directly with the developer (although calling John Beresniewicz a developer is an understatement, he's brilliant!) on answering that problem.
Neither Q nor anything from Platinum (does that tell you how long ago it was?) was able to find the locking sql with any precision. The answer I got back from both of them was "if you figure out how to do it, PLEASE let us know"
Having said that, Q Diagnostics was indeed bliss... I fixed locks before users complained, was able to model and monitor bad SQL and fix it and had lots of good info directly on the desktop.
Rachel
>From: "Gary Weber" <gweber_at_charlesjones.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Evaluation questions
>Date: Tue, 31 Jul 2001 06:11:36 -0800
>
>Rachel,
>
>I believe your second wish from below (locking SQL) has been granted by
>former Savant product called Q Diagnostic Center, currently owned by
>Precise. Drill down to locks, including user and SQL info - its a bliss.
>
>Gary Weber
>Senior DBA
>Charles Jones, LLC
>609-530-1144, ext 5529
>
>-----Original Message-----
>Carmichael
>Sent: Tuesday, July 31, 2001 8:56 AM
>To: Multiple recipients of list ORACLE-L
>
>
>one I want is "can the package tell me when a datafile extends"
>
>also, I want (and have NEVER found) a package that can tell me the locking
>SQL, when the locker has gone on and done other SQL after the lock.
>
>ex.
>
>user1 does:
> select * from table for update where <some where clause>
> update table
> insert into second table
>and does not commit
>
>user2 comes in and tries to update the first table, one of the rows that
>meets user1's where clause
>
>I can tell that user1 is blocking user2 but not the SQL that is doing the
>blocking. That's useful in beating duhvelopers about the head in order to
>get the code fixed.
>
>
>
> >From: "O'Neill, Sean" <Sean.ONeill_at_organon.ie>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: Evaluation questions
> >Date: Tue, 31 Jul 2001 02:25:49 -0800
> >
> >I'm still slogging away at selection process of DB monitor tool. I'm now
> >at
> >stage where I'm compiling a list of specific tasks I'd like to take the
> >contenders through and score them on same.
> >E.G.
> >Can package alert if Oracle DB goes down?
> >Can package alert if Control File extends?
> >
> >I'd appreciate your feedback on what you think the monitor package should
> >be
> >able to do, ya know those things you want to know about before anyone
>else
> >does!. I'm particularly fishing for events that might be a bit more
> >obscure
> >yet still useful to monitor.
> >
> >
> >Sean :)
> >
> >Rookie Data Base Administrator
> >Oracle 7.3.3, 8.0.5, 8.1.7 - NT, W2K
> >[0%] OCP Oracle8i DBA
> >[0%] OCP Oracle9i DBA
> >-------------------------------- ------------
> >Organon (Ireland) Ltd.
> >E-mail: sean.oneill_at_organon.ie [subscribed: Digest Mode]
> >
> >Visit: http://groups.yahoo.com/group/Oracle-OCP-DBA
> >
> >"Nobody loves me but my mother... and she could be jivin' too." - BB
>King
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: O'Neill, Sean
> > INET: Sean.ONeill_at_organon.ie
> >
> >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).
>
>
>_________________________________________________________________
>Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Rachel Carmichael
> INET: carmichr_at_hotmail.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).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Gary Weber
> INET: gweber_at_charlesjones.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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: carmichr_at_hotmail.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: mark_at_cool-tools.co.uk 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 Wed Aug 01 2001 - 09:03:00 CDT
 |
 |