Logminer Query

[marist89]

What's a reasonable time for a query to return from v$logmnr_contents?

The log is 132M on a Sun 420 w/4x400Mhz.

The query is:
SELECT sql_redo, sql_undo
FROM v$logmnr_contents
WHERE seg_owner = 'FOO'
AND seg_name = 'BAR'
AND operation = 'DELETE'

[gopi]

Hope this provides a somewhat "relative" look:

Sun 3800 w/4 processes
25M logs (and I'm doing an aggregate, you're not). I searched through 5 logs which brought me up to 125M total.

SELECT seg_owner, seg_name, count(*) AS Hits
FROM v$logmnr_contents
WHERE seg_name NOT LIKE '%$'
GROUP BY seg_owner, seg_name;

Took me about 3 minutes

[marist89]

Hmm, I must be doing something wrong. My query has been going over 30 minutes....

[gopi]

My hacking:


-- first one uses "New"
EXECUTE Dbms_Logmnr.Add_Logfile( -
options => Dbms_Logmnr.New, -
logfilename => '/arch/wmsp1/wmsp1_1_88.arc');

-- all others us addfile
EXECUTE Dbms_Logmnr.Add_Logfile( -
options => Dbms_Logmnr.addfile, -
logfilename => '/arch/wmsp1/wmsp1_1_89.arc');

EXECUTE Dbms_Logmnr.Add_Logfile( -
options => Dbms_Logmnr.addfile, -
logfilename => '/arch/wmsp1/wmsp1_1_90.arc');
.
. etc....
.
.
EXECUTE Dbms_Logmnr.Start_Logmnr( -
dictfilename =>'/oracle/admin/scripts/l_dictionary_wmsp1.ora');


-- Specifiy SCN range
EXECUTE Dbms_Logmnr.Start_Logmnr( -
dictfilename =>'/oracle/admin/scripts/l_dictionary_wmsp1.ora', -
startscn => 19628200, -
endscn => 19628277);

[marist89]

I'm consistently reading a entire 132M file in about 35 minutes.