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

February 19, 2010

(Forward to the Next Post in the Series)

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

SELECT
  ID,
  COST,
  IO_COST,
  CPU_COST
FROM
  PLAN_TABLE;

 ID  COST  IO_COST   CPU_COST
--- ----- -------- ----------
  0  1482     1467  364928495
  1  1482     1467  364928495
  2   898      887  257272866
  3   889      887   42272866
  4     0        0       2150

Now let’s display the execution plan:

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 1923834833

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              | 99000 |  1836K|       |  1482   (2)| 00:00:18 |
|   1 |  SORT ORDER BY      |              | 99000 |  1836K|  2736K|  1482   (2)| 00:00:18 |
|   2 |   NESTED LOOPS ANTI |              | 99000 |  1836K|       |   898   (2)| 00:00:11 |
|   3 |    TABLE ACCESS FULL| T1           |   100K|  1367K|       |   889   (1)| 00:00:11 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0018049 |    10 |    50 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."C1"="C1")
       filter(MOD("C1",3)=0)

The %CPU is 2 for ID 0, 1, and 2, and the %CPU is 1 for ID 3.  Let’s return to the query of the PLAN_TABLE table and perform a couple of calculations:

SELECT
  ID,
  COST,
  IO_COST,
  COST-IO_COST DIFF,
  CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU,
  CPU_COST
FROM
  PLAN_TABLE;

 ID  COST  IO_COST  DIFF  PER_CPU   CPU_COST
--- ----- -------- ----- -------- ----------
  0  1482     1467    15        2  364928495
  1  1482     1467    15        2  364928495
  2   898      887    11        2  257272866
  3   889      887     2        1   42272866
  4     0        0     0        0       2150

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:

SELECT
  ID,
  COST,
  IO_COST,
  COST-IO_COST DIFF,
  CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU,
  CPU_COST
FROM
  PLAN_TABLE;

 ID  COST  IO_COST  DIFF  PER_CPU   CPU_COST
--- ----- -------- ----- -------- ----------
  0    54       52     2        4   43331709
  1    54       52     2        4   43331709

The above indicates that the %CPU column should show the number 4 on both rows of the execution plan.

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 2950179127

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)|  Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |  5000 | 25000 |    54   (4)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| SYS_C0018049 |  5000 | 25000 |    54   (4)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter('A'||TO_CHAR("C1") LIKE 'A%')

One of my previous blog articles showed the following execution plan – this was the actual plan displayed by DBMS_XPLAN.DISPLAY_CURSOR after the SQL statement executed:

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |   247 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 10000 |  2236K|   247   (1)| 00:00:03 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 10000 |  2236K|   247   (1)| 00:00:03 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| T1       | 10000 |  2236K|   247   (1)| 00:00:03 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(:Z>=:Z AND :Z<=:Z)
       filter(("C1"<=10000 AND "C1">=1)) 

Is there anything strange about the %CPU column in the above plan?

Incidentally, a query of SYS.AUX_STATS$ shows the following output (values are used to determine the impact of the CPU_COST column that is displayed in the PLAN_TABLE table):

SELECT
  PNAME,
  PVAL1
FROM
  SYS.AUX_STATS$
WHERE
  PNAME IN ('CPUSPEED','CPUSPEEDNW');

PNAME           PVAL1
---------- ----------
CPUSPEEDNW   2031.271
CPUSPEED