Oracle-L: RE: Redos gone crazy--trying to use logminer

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 10 Oct 2003 07:49:28 -0800
Message-ID: <F001.005D2B61.20031010074928@fatcity.com>

internal usually refers to index maintenance ... In my recent action, I did something like to get a better picture ...

SELECT username, seg_owner, seg_name, seg_type

,CASE WHEN stmt LIKE 'UPDATE %' THEN SUBSTR(stmt, 1, INSTR(stmt,'"',1,4))

            WHEN stmt LIKE 'INSERT %' THEN SUBSTR(stmt, 1, INSTR(stmt,'"',1,4)) END  stmt_type 


      ,CASE WHEN stmt LIKE 'UPDATE %' THEN SUBSTR(stmt, INSTR(stmt,'"',1,4)+1) 

            WHEN stmt LIKE 'INSERT %' THEN SUBSTR(stmt, INSTR(stmt,'"',1,4)+1) END stmt_detail 
FROM (

SELECT UPPER(sql_redo) stmt FROM sys.tom_logmnr WHERE ( sql_redo IS NOT NULL

       AND LOWER(sql_redo) NOT LIKE 'comm%' 
       AND LOWER(sql_redo) NOT LIKE 'set%' 
       AND LOWER(sql_redo) NOT LIKE 'rollba%' 
       AND sql_redo NOT LIKE '%PERFSTAT%' 
       AND sql_redo NOT LIKE 'Unsupported%'))

where tom_logmnr is the table I built using CTAS from v$lgmnr_contents

Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
Sent: Thursday, October 09, 2003 8:14 PM To: Multiple recipients of list ORACLE-L

Indeed log miner seems to be my only option for figuring this one out.

Log miner is weird!! I had not used it before. interesting...

I found this query on metalink, and here are the results. Is this particularly bizarre? This is for 5 redo logs, each of which filled up within 15 minutes.

Shouldn't I have something populated for seg_name? Is it particularly screwed up that this field is empty??

Also the count for internal seems high.

SQL> select seg_name, count(*) from v$logmnr_contents group by seg_name;

SEG_NAME                           COUNT(*)
-------------------------------- ----------
                                    1128417

SQL>
SQL> SQL> spool logminer_qry2.lis
SQL> set echo on
SQL> -- breakdown of transactions by table, and type

SQL>
SQL> select seg_name, operation, count(*) 2 from v$logmnr_contents group by seg_name, operation;

SEG_NAME                         OPERATION            
             COUNT(*)
--------------------------------
-------------------------------- ----------
                                 COMMIT               
                  547
                                 DELETE               
                    1
                                 INSERT               
                 2204
                                 INTERNAL             
               563760
                                 START                
                  548
                                 UPDATE               
               561357

6 rows selected.

"Jamadagni, Rajendra" <Rajendra.Jamadagni_at_espn.com> wrote: > log miner should give you what you want ... why not? > On last friday > something happened and in our database which usually > averages about 100x100M > archive logs, it started throwing 41 files between > 2pm-3pm, 248 between > 3pm-4pm, 95 between 4pm-5pm. > > Of course we couldn't analyze all files, but an > analysis og a 10 minute > interval at the beginning of archive franzy shows a > clear set of 5 SQLS that > repeated about 83000 times in 10 minutes. > > Once we gave it to development, they were able to > identify the process which > was using the code in question and it became easier. > > I'd start at-least half hour before the peak time > and do a slow analysis. > > I have also found that instead of selecting from > v$lgmnr_contents, I am more > comfortable with doign a CTAS and then perform > queries at my leisure for a > detailed analysis. > > Go for log miner ... at-least it will tell you what > caused the problem. > HTH > Raj >
> ---- > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly > personal. > QOTD: Any clod can have facts, having an opinion is > an art ! > > > -----Original Message----- > Sent: Thursday, October 09, 2003 1:09 PM > To: Multiple recipients of list ORACLE-L > > > Hi, list. Ya, I'm still alive and kickin'. > > We have this small database that's running a weird > vendor application. (We get all the gems.) It's on > Solaris 5.8, Oracle 8.1.7.2 > > The database suddenly went from kicking out 50 meg > redo logs 2 or 3 times a day to churning them out > every 15 minutes. The entire database is only about > 6 > gigs; we now sometimes generate 2 or 3 gigs of redo > per day. > > Even tho this started when a "small" change was made > by the vendor, the vendor is claiming that (ok, hold > on to your hats) it was not their change!! > > I want to know what's in those redo logs. > > I initially thought about log miner. However, I'm > not > sure log miner will give me what I want. > > I tried these 2 audit commands. I'm not seeing much > from them. Is there another audit command that > might > give me better info? There's only 1 user in the > database, so I only really need to audit 1 user. > > audit all by <myuser> by access; > audit update table, insert table, delete table by > <myuser> by access; > > Is there anything else that will be going to redo > that > I can capture with audit?? > > Thanks for any help. > > Barb > > ********************************************************************This > e-mail message is confidential, intended only for > the named recipient(s) above and may contain > information that is privileged, attorney work > product or exempt from disclosure under applicable > law. If you have received this message in error, or > are not the named recipient(s), please immediately > notify corporate MIS at (860) 766-2000 and delete > this e-mail message from your computer, Thank > you.*********************************************************************2 >

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Barbara Baker
  INET: barbarabbaker_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).


**************************************************************************************
This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.
**************************************************************************************5
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Fri Oct 10 2003 - 10:49:28 CDT