If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hybrid View

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.

Solutions

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

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.

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.