Answer:

This is the most well-known, but wrong formula I've read in many Oracle documentations.

parse time cpu includes parse cpu time of both recursive and user statements. recursive cpu usage includes both parse cpu time and non-parse cpu time of recursive statements. That means parse cpu usage of recursive statements is included in both parse time cpu and recursive cpu usage. In other words, it's duplicated and formula above is not correct.

ubTools offers the following formula:

CPU used by this session = parse time cpu + others(exec_and_fetch_time_cpu)

Question:

If there is little or no SQL processing done within PL/SQL, should I also subtract recursive cpu usage from CPU used by this session to get the others cpu component ?

Answer:

NO. A formula should explain all cases. It should not work for just some scenarios only.

Also, both SQL and statements in PL/SQL are associated with a cursor internally in Oracle perspective. In other words, they are not different things in PARSE,EXEC,FETCH calls. If a statement is called by an other statement, it's called recursive statement. So, both an SQL and a PL/SQL can be recursive statements.

• If there is no SQL processing in PL/SQL, it means there is no SQL in parent and child PL/SQLs. There are 2 scenarios for this case:
  • If There is no child PL/SQL in the parent PL/SQL, recursive cpu usage is ZERO. Since it's zero, no need to substruct it from CPU used by this session.
  • If there is child PL/SQL in the parent PL/SQL, PARSE call is done for child PL/SQL in recursive mode. In this case, parse time cpu of recursive statement is already included in recursive cpu usage. So, recursive cpu usage should not be substructed from CPU used by this session.

• If there is little SQL processing in PL/SQL with little parse time cpu, the distortion in the mentioned wrong formula is small. But, recursive cpu usage should still NOT be substructed from CPU used by this session even if the distortion is small. Why should DBAs substruct it if they have more correct formula ? No need.

Recommendation:

The current Reponse Time Performance Analysis(RTA) implementaions are not correct. RTA has not reached its next level, yet. That's why ubTools offered a new technique by Microstate Response-time Performance Profiling (MRPP).

There has been a question on this topic at Tom Kyte's site by referring ubTools:

Question:

 
Tom,

Just wanted to: what exactly is "CPU used by this session". One site(
http://www.ubtools.com/cgi-bin/ib/ikonboard.cgi?act=ST;f=25;t=4
says
<>
CPU used by this session = parse time cpu + recursive cpu usage + others

This is the most well-known, but wrong formula I've read in many Oracle
documentations.

parse time cpu includes parse cpu time of both recursive and user statements.
recursive cpu usage includes both parse cpu time and non-parse cpu of recursive
statements. That means parse cpu usage of recursive statements is included in
both parse time cpu and recursive cpu usage. In other words, it's duplicated and
formula above is not correct.

ubTools offers the following formula:

CPU used by this session = parse time cpu + others(exec_and_fetch_time_cpu)
<>

what is exec_and_fetch_time_cpu ?

Regards

Tom Kyte's answer:

 
I am not so sure they are correct.  unless they are talking about the
description of cpu used by this session (i is not clear to me whether they are
saying "the description is wrong" or "the value reported by the statistic is
wrong"

if the values were wrong, the cpu times reported for most things would exceed
elapsed time by large margins.  so, they should be able to demonstrate that for
us.

(and you would have to ask the author of an article in most cases "what did you
mean by this "exec and fetch time cpu" and how exactly do you think we could
find it)

I think they were saying "the description provided is wrong", but I have an
easier description.

cpu use by this session is cpu used by that session.

Our answer:

We said the current CPU breakdown formula is incorrect, not the description of Oracle statistics.

CPU used by this session is the total CPU usage in session or instance level. And, there are 3 components in CPU usage:

• Parse
• Exec
• Fetch

These components can be seen in SQL_TRACE / EVENT10046 traces. Parse component is available by parse time cpu statistic. Since there is no Oracle statistic for Exec/Fetch components, we call them as others.

We had not mentioned values of the CPU usage statistics in this discussion. If we start talking about the values, it gets started another wrong topic on the values. Here is a brief explanation:

• In busy environments, distortion on CPU measurement is minimal. In, non-busy environments, it may not be minimal. In many cases, there is no big performance problem in non-busy environments. So, the distortion on CPU usage doesn't make sense in many cases.
• The wait measurement includes serious distortions in busy environments.

ubTools says for years that RESPONSE TIME ANALYSIS(RTA) CAN NOT BE IMPLEMENTED IN INSTANCE LEVEL. RTA IS A METHOD FOR SESSION LEVEL.

For the full details with the proven samples, see Microstate Response-time Performance Profiling (MRPP).