| 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
Chaim,
"loading the right log file involves some trial and error"
that is just what I DON'T have time for when the database has slowed to a crawl and all of upper management is looking over my shoulder wondering why I haven't fixed it yet.
Rachel
>From: Chaim.Katz_at_Completions.Bombardier.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: Wed, 01 Aug 2001 12:15:51 -0800
>
>
>
>
>
>
>
>"Hillman, Alex" <Alex.Hillman_at_usmint.treas.gov> on 08/01/2001 02:16:31 PM
>
>Please respond to ORACLE-L_at_fatcity.com
>
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>cc: (bcc: Chaim Katz/Completions/Bombardier)
>
>
>
>
>All,
>
>I just tried a log miner test (without the logminer dictionary). It wasn't
>so
>bad:
>
>Here is the blocker:
>
> 1 select sid,type,lmode,request,block,round(ctime/60,2) ctime
> 2 from v$lock
> 3 where block != 0
>
> SID Lock Type Mode Held Mode Requested Blocking? Minutes
>Blocked
>---------- --------------- ---------- -------------- ----------
>---------------
> 32 TX 6 0 1
>33.83
>
>Here is the blocker's transaction:
>
> 1 select t.xidusn,t.xidslot,t.xidsqn
> 2 from v$transaction t, v$session s
> 3 where t.addr = s.taddr and
> 4 s.sid=32;
>
> XIDUSN XIDSLOT XIDSQN
>---------- ---------- ----------
> 4 55 44118
>
>
>Load a redo log:
>
> 1 begin
> 2 sys.dbms_logmnr.add_logfile
> 3 ('C:\ORACLE\ORADATA\DEV816\REDO02.LOG',
> 4 sys.dbms_logmnr.new);
> 5 sys.dbms_logmnr.start_logmnr;
> 6 end;
>
>PL/SQL procedure successfully completed.
>
>
> 1 select sql_redo
> 2 from v$logmnr_contents
> 3 where xidusn=4 and
> 4 xidslt=55 and
> 5 xidsqn=44118;
> 6 and rownum < 4;
>
>SQL_REDO
>----------------------------------------------------------------------------------------------------
>set transaction read write;
>delete from "UNKNOWN"."Objn:45095" where "Col[1]" = HEXTORAW('c24a46') and
>"Col[2]" = HEXTORAW('534d
>495448') and "Col[3]" = HEXTORAW('434c45524b') and "Col[4]" =
>HEXTORAW('c25003')
>and "Col[5]" = HEXT
>ORAW('77b40c11010101') and "Col[6]" = HEXTORAW('c209') and "Col[7]" IS NULL
>and
>"Col[8]" = HEXTORAW(
>'c115') and ROWID = 'AAALAnAADAAAJAzAAA';
>Check the table name
> 1 select owner,object_name
> 2 from dba_objects
> 3* where object_id=45095
>
>OWNER OBJECT_NAME
>------------------------------ ------------------------------
>SCOTT EMP
>
>
>Voila, the blocking statement is: DELETE FROM emp. (I limited the output,
>but
>there are actually 14 sql_redo statmements that differ only in the value of
>the
>rowid.) (Loading the right log file involves some trial and error.)
>(Remember
>you saw it here first.)
>
>Chaim
>
>
>
>
>
>
>
>
>
>If you had SQL trace available - you would be able to find SQL for the
>session. One time on this list I heard mention about x$trace - like it has
>all data of the event 10046 trace only not in the trace file but in memory.
>Anumbody has some infp about it?
>
>Alex Hillman
>
>-----Original Message-----
>Sent: Wednesday, August 01, 2001 1:34 PM
>To: Multiple recipients of list ORACLE-L
>
>
>
>
>Chaim,
>
>That could work. Logminer wasn't around when I first had this problem, and
>I
>
>haven't spent time working with it.
>
>From what I know from listening to Joe present on it though, it would be a
>more complicated process than I want. I'd have to be sure that the
>dictionary map was always up to date, and I would have to go back and look
>through all the archived logs as well as the online ones, in case the
>statement had been archived off. It also sounds like it would be intensely
>manual.
>
>Joe -- any thoughts on this?
>
>
>I want something that runs FAST, so I can clear locks quickly. And then go
>on to the fun stuff of killing duhvelopers.
>
>Rachel (today it is hot and humid here, and today the AC in the office
>doesn't work.... killing duhvelopers is looking like more and more fun)
>
>
>
> >From: Chaim.Katz_at_Completions.Bombardier.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: Wed, 01 Aug 2001 07:51:19 -0800
> >
> >
> >
> >
> >
> >
> >
> >
> >"Rachel Carmichael" <carmichr_at_hotmail.com> on 07/31/2001 11:48:02 AM
> >
> >Please respond to ORACLE-L_at_fatcity.com
> >
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >cc: (bcc: Chaim Katz/Completions/Bombardier)
> >
> >
> >
> >
> >Rachel,
> >
> >I don't know how to retreive the locking sql either, but reading this
> >discussion
> >it occurred to me that 1) in v$session (of the blocked session) we have
> >the
> >file#,block#,row# that is being waited on. Maybe with logminer (or
> >something
> >like it) we could find the most recent SQL that affected this block/row?
> >
> >Chaim
> >
> >
> >
> >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:
> > INET: Chaim.Katz_at_Completions.Bombardier.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: Hillman, Alex
> INET: Alex.Hillman_at_usmint.treas.gov
>
>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:
> INET: Chaim.Katz_at_Completions.Bombardier.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).Received on Wed Aug 01 2001 - 15:42:06 CDT
 |
 |