What does the COST(%CPU) in explain plan mean?

[Jia Lu]

Hi all

I have an explain plan like below. and I doubt about the COST.
How is the COST be calculated? I mean what does that mean? (CPU
rate?)

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1764098166

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7824 (1)|
00:01:34 |
| 1 | SORT AGGREGATE | | 1 |
| |
| 2 | INDEX FAST FULL SCAN| BIG_TABLE_PK | 6812K| 7824 (1)|
00:01:34 |
------------------------------------------------------------------------------

Thanks alot.

Lu

[Randolf Geist]

On Apr 19, 4:05 am, Jia Lu <roka...@gmail.com> wrote:
> I have an explain plan like below. and I doubt about the COST.
> How is the COST be calculated? I mean what does that mean? (CPU
> rate?)

The cost reported by the optimizer has always been a time estimate. It
just uses an odd unit which is single block reads. In your particular
case you see the overall cost is 7824 single block reads. The TIME
column tells you this cost turned into time by simply multiplying the
cost with the single block read time which is available if you have
System Statistics enabled (default from 10g on). We can deduce from
both the 94 seconds and the cost of 7824 that your average single
block read time according to your system statistics seems to be 12 ms
(94,000 ms / 7824 is quite close to 12 ms).

You can check your SYS.AUX_STATS$ table for the SREADTIM value - if it
is blank then you're running with (default) NOWORKLOAD System
Statistics and the single block read time is derived from IOSEEKTIM,
IOTFRSPEED and your default DB_BLOCK_SIZE.

The 12 ms are the default single block read time with default
NOWORKLOAD System Statistics and a default 8 KB default block size.

The % CPU is the estimated percentage of CPU cost of the calculated
cost, since with System Statistics the optimizer also includes an
estimation of the CPU cost required to execute the statement.

For more information, see e.g.

http://jonathanlewis.wordpress.com/2006/12/11/cost-is-time/

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684

[Jia Lu]

On 4月19日, 午後6:51, Randolf Geist <mah...@web.de> wrote:
Thanks for your answer.
You told me something internal :)

I think I should purse your book to study.

Best
Lu