CPU dominates response time

When CPU dominates, the session really worked in the database, and it is the work that has to shrink. The excess almost always comes from logical reads, from parsing or from heavy PL/SQL.

Symptoms

In the Diagnosis, the finding CPU dominates response time (CPU_DOMINANTE) appears. The text says how much of the total time is CPU, as a percentage and in seconds, lists the CPU components that were added up, for example CPU: FETCH dbcalls, CPU: EXEC dbcalls, suggests starting with the statements with the most LIO and ends with View profile by class.

The finding appears when the CPU class, the sum of the CPU of all call types, holds most of the total time. The severity is always medium. With it, the dominant component finding is not repeated for CPU.

Cause

The CPU in the profile is the self CPU of each call, such as PARSE, EXEC and FETCH: its c= minus that of its child calls. It is time in which the session ran on the CPU. The most common causes:

• Excessive logical reads (LIO). The plan reads many more blocks from the buffer cache than the rows returned call for: an index with poor selectivity, NESTED LOOPS over many rows, the same table revisited for each row. It shows up as FETCH or EXEC CPU.
• Parse. Hard parses from SQL without binds, or repeated PARSE calls of the same cursor. It shows up as PARSE CPU.
• Heavy PL/SQL. Logic in loops and functions called for each row of a SQL statement. It shows up as EXEC CPU of the PL/SQL block.

CPU queueing on the server does not count here: the time in which the session waited for a free CPU falls into unaccounted-for time within calls.

Resolution

1. See which call type weighs the most. In the profile, compare CPU: FETCH dbcalls, CPU: EXEC dbcalls and CPU: PARSE dbcalls.
2. If it is PARSE, follow many hard parses, one PARSE per execution and literals instead of binds.
3. If it is FETCH or EXEC, find the statement. In Top statements by component and by class, open the group of the CPU class; in SQL statements, compare the LIO column with Rows.
4. In the plan of the statement, find in Where the plan spends time the operation with the most Self LIO and fix the access: a more selective index, another join order or join method, correct statistics (execution plan, wrong row estimate).
5. If the statement is a PL/SQL block, open Recursive SQL executed by this statement in its detail. If the child statements are few and cheap, the CPU belongs to the PL/SQL code itself: to find the line that spends it, use the PL/SQL hierarchical profiler (DBMS_HPROF).

How to confirm

Generate a new trace of the same operation after the change. The Duration (s) of the CPU class and the LIO of the fixed statement should drop, and the Total time with them. The finding disappears when CPU no longer holds most of the total time.

Compare the total time, not just the CPU: a new plan can read fewer blocks from the cache and more from disk, trading CPU for read waits.

In the report

In the sample report, CPU adds up to 3.7% of the total time, without this finding. The links open the sections of the example:

• Diagnosis: the finding and the View profile by class link.
• By wait class, CPU row: the sum of the CPU and, in Top components, the call types.
• Profile, CPU rows: one per call type.
• Top statements by class, CPU group: the statements that spent the most CPU.
• SQL statements by contribution: the LIO and Rows columns.
• Call types: CPU (s), Consistent (CR) and Current (CU) of each call.