Obtaining more information on the cause of high figures shown in tkprof recursive totals

Question

Friends,

I am currently investigating a performance problem with our application. The problem is that an operation that should be completed almost instantly is taking several minutes.

In case it is of importance the application architecture is Oracle Forms, Oracle Application Server and Oracle Database. All of which are 10.x

The customer has supplied both the "raw" sql trace file and one formatted with tkprof.

Reviewing the tkprof file I can't see any sql statements in the file that take more than 13 seconds to complete. There are two I think system statements in this file.

In the summary section at the bottom of the file I can see that the cpu and elapsed times totals for NON-RECURSIVE STATEMENTS are in the region of 3 and 11 seconds respectively. (Shows some wait time I know but not enough to cover the time reported by the users)

The totals for ALL RECURSIVE STATEMENTS appear to point to the problem because the CPU time is around 350 seconds and the elaspsed time is now nearly 400 seconds. Which is more like the time the users are saying it takes.

In the Oracle documentation this is the only reference I could find on recursive calls which explains more about what they are.

My question therefore is how do I find out more information on recursive statements that are causing the problem?

Thanks in advance.

Answer 1

"The customer has supplied both the "raw" sql trace file and one formatted with tkprof." You can run your own tkprof on the raw trace file which should supply more details.

The main places for recursive calls are 1. When parsing SQL. It needs to determine whether the objects referenced are tables, views, synonyms etc, what the columns and indexes are, privileges, statistics and histograms.... 2. When doing DDL. It needs to update a whole bunch of metadata saying what objects exist etc. And, as a side-effect, it may invalidate existing SQLs causing them to be re-parsed (see 1). This may be what you refer to as "system statements" 3. Calling a built-in process, such as gathering stats.

So in the TKPROF, you can look at the count of parses to executes for the non-recursive statements. Mostly you would want to see very few parses. If you have statements with lots of parses, ask why. I had one situation where a table was TRUNCATED in the middle of a loop which resulted in lots of excessive parsing.