RE: Evaluation questions

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).