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
05-03-2001, 06:01 AM
padmam
Solutions
Dear Suresh, 3rd May 2001 14:10 hrs chennai
This is for Recursive and Nonrecursive calls a link.
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
05-03-2001, 09:26 AM
Sureshy
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
05-03-2001, 09:59 AM
Heath
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
05-03-2001, 10:35 AM
padmam
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 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.
