parsing a 10046 event trace file
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: parsing a 10046 event trace file

  1. #1
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178

    parsing a 10046 event trace file

    Is there any script (PERL?) that goes through the entire trace file of a 10046 event trace and gives me the object name and type the query waited?

    I have 200000 lines in the trace file and it is cumbersome to manually run the query on DBA_SEGMENTS to find each object name.


    WAIT #79: nam='db file sequential read' ela= 6410 p1=69 p2=55175 p3=1
    WAIT #79: nam='db file sequential read' ela= 10123 p1=69 p2=53785 p3=1
    WAIT #79: nam='db file sequential read' ela= 6809 p1=70 p2=45315 p3=1
    WAIT #79: nam='db file sequential read' ela= 8008 p1=70 p2=44967 p3=1
    WAIT #79: nam='db file sequential read' ela= 8098 p1=69 p2=65413 p3=1
    WAIT #79: nam='db file sequential read' ela= 10810 p1=77 p2=183858 p3=1
    WAIT #79: nam='db file sequential read' ela= 7107 p1=70 p2=58215 p3=1
    WAIT #79: nam='db file sequential read' ela= 5288 p1=78 p2=81639 p3=1
    WAIT #79: nam='db file sequential read' ela= 8365 p1=69 p2=79209 p3=1
    WAIT #79: nam='db file sequential read' ela= 8504 p1=69 p2=78834 p3=1
    WAIT #79: nam='db file sequential read' ela= 7483 p1=68 p2=92668 p3=1
    WAIT #79: nam='db file sequential read' ela= 9715 p1=79 p2=92561 p3=1
    WAIT #79: nam='db file sequential read' ela= 8007 p1=69 p2=99014 p3=1
    WAIT #79: nam='db file sequential read' ela= 9276 p1=69 p2=98696 p3=1
    WAIT #79: nam='db file sequential read' ela= 116871 p1=41 p2=214130 p3=1
    WAIT #79: nam='db file sequential read' ela= 12584 p1=41 p2=220265 p3=1
    WAIT #79: nam='db file sequential read' ela= 23042 p1=72 p2=250472 p3=1
    WAIT #79: nam='db file sequential read' ela= 13008 p1=70 p2=100682 p3=1
    WAIT #79: nam='db file sequential read' ela= 6752 p1=70 p2=98923 p3=1
    WAIT #79: nam='db file sequential read' ela= 11643 p1=69 p2=126332 p3=1
    WAIT #79: nam='db file sequential read' ela= 15265 p1=77 p2=151339 p3=1
    WAIT #79: nam='db file sequential read' ela= 9945 p1=75 p2=11971 p3=1
    WAIT #79: nam='db file sequential read' ela= 7662 p1=69 p2=130204 p3=1
    WAIT #79: nam='db file sequential read' ela= 5251 p1=32 p2=192680 p3=1

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    hmmmmm.....tkprof?
    I'm stmontgo and I approve of this message

  3. #3
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178
    No... Tkprof output still have p1, p2, p3..

    I am sure someone out there has a little perl script that can parse the entire script for p1, and p2 and run this query and format the output for waiting object name and type:

    SELECT owner , segment_name , segment_type
    FROM dba_extents
    WHERE file_id = &AFN
    AND &BLOCKNO BETWEEN block_id AND block_id + blocks -1;
    Last edited by newbie5; 04-15-2004 at 05:49 PM.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Sure, that would take about 10 minutes... Why don't you write one and share with the rest of us...
    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."

  5. #5
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Well, no perl script here (and, anyway, 9i tkprof gives all the wait details for us), but when I was restricted to 8i only, I used to do the following :

    Code:
     grep WAIT *.trc  | sort +3 -t: >  loadtrace.dat
     grep PARSE *.trc | sort +3 -t: >> loadtrace.dat
     grep EXEC *.trc  | sort +3 -t: >> loadtrace.dat
     grep FETCH *.trc | sort +3 -t: >> loadtrace.dat
     grep UNMAP *.trc | sort +3 -t: >> loadtrace.dat
     grep STAT *.trc  | sort +3 -t: >> loadtrace.dat
     
    drop table waittrace;
    create table waittrace (fname varchar2(30),
    op varchar2(10),
    opseq varchar2(10),
    waithd varchar2(10),
    wait varchar2(30),
    elah varchar2(5),
    ela number, 
    p1 varchar2(30),
    p2 varchar2(30),
    p3 varchar2(30));
    drop table stattrace;
    create table stattrace(
    fname varchar2(30),
    op varchar2(10),
    opseq varchar2(10),
    id number,
    cnt number,
    pid number,
    pos number,
    obj number,
    operation varchar2(60));
    
    drop table cputrace;
    create table cputrace(
    fname varchar2(30),
    op varchar2(10),
    opseq varchar2(10),
    c number,
    e number,
    p number,
    cr number,
    cu number,
    mis number,
    r number,
    dep number,
    og number,
    tim number(20));
    
    ==> put in loadtrace.ctl the following:
    
    LOAD DATA INFILE 'loadtrace.dat' APPEND
    INTO TABLE waittrace
      WHEN op = 'WAIT'
        FIELDS TERMINATED BY " " 
      trailing nullcols
      (fname  char   terminated by ":",
       op     char   terminated by " ",
       opseq  char   terminated by ":",
       waithd char   terminated by "=",
       wait   char   enclosed by "'",
       elah   char ,
       ela    char,
       p1     char,
       p2     char,
       p3     char
     )
    INTO TABLE cputrace
      WHEN op = 'PARSE'
      trailing nullcols
      (fname POSITION(1) char terminated by ":",
       op    char    terminated by " " ,
       opseq char    terminated by ":" ,
       c             enclosed by "c="   AND ",",
       e             enclosed by "e="   AND ",",
       p             enclosed by "p="   AND ",",
       cr            enclosed by "cr="  AND ",",
       cu            enclosed by "cu="  AND ",",
       mis           enclosed by "mis=" AND ",",
       r             enclosed by "r="   AND ",",
       dep           enclosed by "dep=" AND ",",
       og            enclosed by "og="  AND ",tim=",
       tim terminated by "," 
      )
    INTO TABLE cputrace
      WHEN op = 'EXEC'
      trailing nullcols
      (fname POSITION(1) char        terminated by ":",
       op    char    terminated by " " ,
       opseq         char    terminated by ":" ,
       c             enclosed by "c="   AND ",",
       e             enclosed by "e="   AND ",",
       p             enclosed by "p="   AND ",",
       cr            enclosed by "cr="  AND ",",
       cu            enclosed by "cu="  AND ",",
       mis           enclosed by "mis=" AND ",",
       r             enclosed by "r="   AND ",",
       dep           enclosed by "dep=" AND ",",
       og            enclosed by "og="  AND ",tim=",
       tim terminated by "," 
       ) 
    INTO TABLE cputrace
      WHEN op = 'FETCH'
      trailing nullcols
      (fname POSITION(1) char        terminated by ":",
       op    char    terminated by " " ,
       opseq         char    terminated by ":" ,
       c             enclosed by "c="   AND ",",
       e             enclosed by "e="   AND ",",
       p             enclosed by "p="   AND ",",
       cr            enclosed by "cr="  AND ",",
       cu            enclosed by "cu="  AND ",",
       mis           enclosed by "mis=" AND ",",
       r             enclosed by "r="   AND ",",
       dep           enclosed by "dep=" AND ",",
       og            enclosed by "og="  AND ",tim=",
       tim terminated by "," 
       )
    INTO TABLE cputrace
      WHEN op = 'UNMAP'
      trailing nullcols
      (fname POSITION(1) char        terminated by ":",
       op    char    terminated by " " ,
       opseq         char    terminated by ":" ,
       c             enclosed by "c="   AND ",",
       e             enclosed by "e="   AND ",",
       p             enclosed by "p="   AND ",",
       cr            enclosed by "cr="  AND ",",
       cu            enclosed by "cu="  AND ",",
       mis           enclosed by "mis=" AND ",",
       r             enclosed by "r="   AND ",",
       dep           enclosed by "dep=" AND ",",
       og            enclosed by "og="  AND ",tim=",
       tim terminated by "," 
       ) 
    INTO TABLE stattrace
      WHEN op = 'STAT'
      trailing nullcols
      (fname POSITION(1) char        terminated by ":",
       op            char    terminated by " " ,
       opseq         char    terminated by " " ,
       id            enclosed by "id="  AND " ",
       cnt           enclosed by "cnt=" AND " ",
       pid           enclosed by "pid=" AND " ",
       pos           enclosed by "pos=" AND " ",
       obj           enclosed by "obj=" AND " ",
       operation     enclosed by "op='" AND "'"
       )
    ==> and finally invoke SQL*Loader

    sqlldr userid/passwd control=loadtrace.ctl direct=true

    from here is easy, you can select the tables in any way you like.

    Regards,

    Chiappa

  6. #6
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178
    Thanks JChiappa. I will try something similar.

    Thanks marist89. That is the way to go. But you know about not reinventing the wheel, if it has already been invented.
    Last edited by newbie5; 04-15-2004 at 06:43 PM.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by newbie5
    Thanks JChiappa. I will try this.

    Thanks marist89. That is the way to go. But you know about not reinventing the wheel, if it has already been invented.
    what if you invent a better wheel?

    in 8i we can use trace analyzer

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