Logminer Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Logminer Query

  1. #1
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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'
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  2. #2
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Hmm, I must be doing something wrong. My query has been going over 30 minutes....
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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);

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I'm consistently reading a entire 132M file in about 35 minutes.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width