Re: What does the COST(%CPU) in explain plan mean?
Date: Mon, 19 Apr 2010 02:51:59 -0700 (PDT)
Message-ID: <7198b1e7-2607-4685-b04d-b1e40a6d347e_at_i25g2000yqm.googlegroups.com>
On Apr 19, 4:05 am, Jia Lu <roka..._at_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 Received on Mon Apr 19 2010 - 04:51:59 CDT