What is the Meaning of the %CPU Column in an Explain Plan?

A question recently appeared on the OTN forums asking what %CPU means in an explain plan output. I did not see a clear definition of that column in the documentation, so I set up a test case. We will use the test table from this blog article. Let’s try creating an explain plan on Oracle 11.2.0.1 for a query:

EXPLAIN PLAN FOR
SELECT
T1.C1,
T1.C2,
T1.C3
FROM
T1,
(SELECT
C1,
C2
FROM
T1
WHERE
MOD(C1,3)=0) V
WHERE
T1.C1=V.C1(+)
AND V.C1 IS NULL
ORDER BY
T1.C1 DESC;

The above command wrote a couple of rows into the PLAN_TABLE table. At this point, we should probably consult the documentation to understand the columns in the PLAN_TABLE table.

COST: Cost of the operation as estimated by the optimizer’s query approach. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the CPU_COST and IO_COST columns.

IO_COST: I/O cost of the operation as estimated by the query optimizer’s approach. The value of this column is proportional to the number of data blocks read by the operation. For statements that use the rule-based approach, this column is null.

CPU_COST: CPU cost of the operation as estimated by the query optimizer’s approach. The value of this column is proportional to the number of machine cycles required for the operation. For statements that use the rule-based approach, this column is null.

We found a couple of interesting columns in the PLAN_TABLE table, so let’s query the table

In the above, I subtracted the IO_COST column from the COST column to derive the DIFF column. I then divided the value in the DIFF column by the COST column, multiplied the result by 100 to convert the number to a percent, and then rounded up the result to derive the PER_CPU column. The PER_CPU column seems to match the %CPU column in the DBMS_XPLAN output. Let’s try another SQL statement:

DELETE FROM PLAN_TABLE;
EXPLAIN PLAN FOR
SELECT
C1
FROM
T1
WHERE
'A'||C1 LIKE 'A%';

Now let’s run the query against the PLAN_TABLE table to see if we are able to predict the values that will appear in the %CPU column of the DBMS_XPLAN output:

If I understand your question correctly, you are asking if it is possible to determine which SQL statements cause performance problems based on the calculated CPU_COST. I do not think that it is safe to determine which SQL statements will cause performance problem based only on the calculated CPU_COST or IO_COST – those numbers are only estimated values. It is not completely clear what the unit of measure is for the CPU_COST values, other than it is related to CPUSPEED from SYS.AUX_STATS$, which indicates the relative performance of the CPU when performing some sort of Oracle Database task (possibly directly related to the number of consistent gets that may be performed per second).

Mathy,
The %CPU column in explain plans cannot be greater than 100. Your question must be asking about raw values displayed in PLAN_TABLE. In either case, keep in mind that the values are estimates only, and do not supply enough information to tell a person that one approach is better than the other, and the cost value is not usually useful for comparing different explain plans beyond individual steps in the explain plan. The calculated costs in an explain plan are directly affected by various initialization parameters and database statistics – if the wrong information is supplied to Oracle’s optimizer, the cost calculation could be entirely useless not only to a person trying to determine which plan is “best”, but also to the optimizer that is trying to determine which access method or join method is “best”.

A general statement, if the above is seen – the optimizer is possibly predicting to experience fewer wait events with plan B. Take a look at DBMS_XPLAN, and how to use it to retrieve the actual execution plan and statistics to help decide which plan is best – there are a few articles on my blog that describe its usage.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: