| 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
Mark,
mark,
Yep, it ain't a walk in the park... as I said. I've been thinking/working on this for a number of years, haven't found a solution yet.
The problem with taking so many snapshots is that I am afraid that they will affect performance.
I don't do easy questions... those I answer myself :) Just ask the instructor of the first Oracle database administration course I ever took (she and I are still friends, running a "mutual admiration society" these days). I took the class after working as a DBA for 6 months. And reading and re-reading the manuals. I didn't ask easy questions then either.
Rachel
>From: "Mark Leith" <mark_at_cool-tools.co.uk>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Evaluation questions
>Date: Wed, 01 Aug 2001 07:10:27 -0800
>
>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# (+)
> and o.owner# = u.user# (+)
> and s.sql_hash_value = t.hash_value
> order by lmode
>
>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).
>
>
>_________________________________________________________________
>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: 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).
-- 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).Received on Wed Aug 01 2001 - 10:14:01 CDT
 |
 |