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).
Received on Thu Oct 09 2003 - 19:14:25 CDT