Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Unanswered: Database performance

    I need to find out the following daily from the trace file

    1. top ten queries that took the longest time
    2. top ten queries that returned the largest data
    3. top ten queries most frequently called

    can anybody help me with the scripts

    --Jaggu

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Use tkprof

  3. #3
    Join Date
    Aug 2003
    Posts
    123
    How to generate from tkprof, is there any ready made script available


    --Jaggu

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    /*** Find top 25 SQL statements ***/
    set serverout on size 1000000
    declare
    top25 number;
    text1 varchar2(4000);
    x number;
    len1 number;

    cursor c1 is
    SELECT buffer_gets, substr(sql_text,1,4000)
    FROM v$sqlarea
    ORDER BY buffer_gets desc;

    BEGIN
    dbms_output.put_line('----------'||' '||'----------------------');
    open c1;
    for i in 1..25 loop
    fetch c1 into top25, text1;
    dbms_output.put_line(rpad(to_char(top25),9)||' '||
    substr(text1,1,66));
    len1 := length(text1);
    x := 66;
    while len1 > x-1 loop
    dbms_output.put_line(''' '||substr(text1,x,66));
    x := x+66;
    END LOOP;
    END LOOP;
    END;
    /

    shows top 25 queries (by buffer gets)


    select upper(substr(sql_text, 1, 150)) sqltext, count(*)
    from v$sqlarea
    group by upper(substr(sql_text, 1, 150))
    having count(*) > 10;

    shows queries and the number of executions that are similiar ...

    Both of these only show current info ... You may have to come up with
    another way of looking at things after executiion (audit) ...


    HTH
    Gregg

  5. #5
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Database performance

    Originally posted by jaggu
    I need to find out the following daily from the trace file

    1. top ten queries that took the longest time
    2. top ten queries that returned the largest data
    3. top ten queries most frequently called

    can anybody help me with the scripts

    --Jaggu
    You can get all this with PERFORMANCE MONITOR. Performance Monitor is part of OEM ( Oracle Enterprise Manager ).

    Another option that I can give is to use STACKPACK but Stackpack is not so easy to use for people with few experience.
    Joel Pérez

  6. #6
    Join Date
    Aug 2003
    Posts
    123
    I have set the sql_trace=True in the init.ora file, this is a development database, Now I can see a lot of *.trc file in the Udump, I need to figure out from the *.trc file every day what are the
    1. top ten queries that took the longest time
    2. top ten queries that returned the largest data
    3. top ten queries most frequently called

    my friend has done some script to find the above, but he his not with our company, and no tracablility, I hope some of you would have done the same excercise, please if any one knows how to extract the above three quires please from the *.trc file please help me, It will be really helpful for me, because I need to give the same by tomorrow, but no clues, I would appreciate, any kind of advice and suggestion , help provided

    looking forward for your replies.

    --Jaggu

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead a person to knowledge, but you can't make him think.

    Are you so lazy that you won't even visit http://tahiti.oracle.com and RTFM which explains how to use TKPROF?

    >How to generate from tkprof, is there any ready made script available
    Nobody is getting paid to do your work for you.

  8. #8
    Join Date
    Aug 2003
    Posts
    123
    I am realy sorry, actualy I am not lazzy, the thing is I am very busy with other work, and if the wheel is invented why to invent again that is the reason we are sharing the knowledge here, I can do the thing what I want but it needs some time and as i said I am busy in other things, I am sorry if you have mistaken me.


    --Jaggu

Posting Permissions

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