Results of trace/tkprof

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

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

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

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

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]

Cheers Folks..

Now have a clearer understanding.