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"
<[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
