Results of trace/tkprof
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Results of trace/tkprof

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725

    One of our applications is running slowly and we believe that it is the application causing it , not the database.
    I have just traced and TKPROF'd a session in one of our Oracle Databases to establish how the code is being interpreted, whether or not it is using its indexes, how long it is taking to process.

    The results seem fine to me, hardly any cpu/elapsed time, explain plan shows that the system is making use of indexes etc.

    I do however have some queries regarding my output :

    Does anybody know :-

    Why some statements are parsed numerous amounts of times.

    Why statements need to be executed a number of times to pull out the desired data.

    Can you also please tell me what the difference between a recursive and non-recursive statement is.

    Thanks in advance

    Suresh

  2. #2
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Solutions

    Dear Suresh, 3rd May 2001 14:10 hrs chennai

    This is for Recursive and Nonrecursive calls a link.

    http://www.dbasupport.com/forums/sho...?threadid=9903

    Reg the number of calls why the reasons are follows.

    While you issue a select ,update,delete or insert the objects information would not been available in Data Dictionary cache.So recursive call will happen.

    And i think you have to seen that in TKPROF file

    it would have given the overall cumulative of recurisve and non-recursive total at the bottom for ALL STATEMENTS executed from the start of the session and till you end the tracing to take a output using TKPROF.

    So you should not think that the PARSES,EXECUTE,FETCH is HIGH for a single statement.

    At the start of the TKPROF file you could have seen the each statement wise it would have been stated the number of PARSES,EXECUTE,FETCH.

    Cheers

    Padmam





    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Dear Padman,

    Thankyou for your reply, I now understand recursive/non recursive - however :

    When TKPROFING, I set the argument sys=n so that system sql statement statisticts would not be shown.

    I can still see that some statements were parsed anumber of times=, 9/10 times - do you know why ?

    also - how can I get the system to use array fetching as I have noticed that a fetch is issued for each row retrieved.

    Thanks in advance

    Suresh



  4. #4
    Join Date
    Apr 2001
    Posts
    118
    One way that I have seen this happen before was when there was code that was written where queries were executed within some sort of LOOP structure. The query would be built, parsed, executed and fetched within the LOOP. A value would be retrieved and then the next iteration in the loop would occur where the query would get rebuilt, parsed, executed and getched. To resolve that I changed the code to use dynamic SQL, moved the parse call outside of the loop and then used bind variables to change the query values within the loop so that all that was needed was to re-execute the query and fetch the new rows.

    I hope that made sense. I have no idea if this applies to what you are seeing because it's a little hard to determine without understanding your application or seeing your queries. But, you asked how this could happen and this is one cause that I have seen.

    HTH,

    Heath

  5. #5
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    sys=no run as user sys

    Hi, 3rd May 2001 19:57 hrs chennai

    i understand your 1 doubt try to execute the same query for
    example

    >alter session set sql_trace=true
    >select * from emp;
    >alter session set sql_trace=false

    now execute as follows the above steps thrice

    >tkprof inputfile.trc outputfile.trc explain=scott/tiger

    see the generated file at UDUMP directory

    >tkprof inputfile.trc outputfile.trc explain=scott/tiger sys=no

    see the generated file at UDUMP directory

    >tkprof inputfile.trc outputfile.trc explain=scott/tiger sys=no

    see the generated file at UDUMP directory

    Now look each time at the output file and tell me what happened for the number of parses ?for each time to execute the tkprof execute a standard SQL statement as mentioned above but first time let it be from a new table and other 2 times the same query.

    Note:SYS=NO==>Ignore recursive SQL ststements run as user SYS.

    Let me know what you understood on the above results.?

    now you can find at last the parse,fetch,execute would have become 0,0,0 for recursive statements.

    II)

    http://www.dbspecialists.com/4dbas/p...e_explain.html

    Read this article and let me have your idea on the same

    Cheers

    padmam



    [Edited by padmam on 05-03-2001 at 10:45 AM]
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  6. #6
    Join Date
    Apr 2001
    Location
    London
    Posts
    725

    Smile Cheers Folks.

    Cheers Folks..

    Now have a clearer understanding.

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