TIP 59#: CPU cost vs I/O cost in execution plan

If you are using execution plan for tuning SQL statement, you may notice three distinguished columns: I/O Cost and CPU cost and Cost.
In this post, I am trying to just make more sense of these three columns in an execution plan. Optimizer uses a CPU to I/O ratio to determine how much CPU cost would be equivalent to one I/O cost.
Here is guideline on how to find out this ratio and see if that make sense in your system.

1. Enable event 10053 .


alter session set max_dump_file_size = unlimited;
ALTER SESSION SET EVENTS '10053 trace name context forever,level 1';
EXPLAIN PLAN FOR --SQL STATEMENT--;
exit

2. Find trace file.
Trace file should be in user_dump_dest

3. Go through trace file and find access path for an index full scan or a table full scan
Here is a sample :



In above example :
resc_io is I/O cost ( 559.00 in this sample)
resc_cpu is CPU cost ( 27509992 in this sample)
cost is total cost ( 562.23 in this sample)

CPU to IO ratio can be calculated with the following formula :

cpu_to_io_ratio = resc_cpu / (cost - resc_io)

In this case CPU to IO ratio is 27509992/(562.23-559.00 ) = 8517025.39. This means that each I/O cost equals to 8517025.39 CPU cost.
In healthy system this factor should not be very low. Also the higher this ratio is, the more effective index will be to improve performance.

PS : Those who are interested, please Email me for full documentation on 10053 event and more sample on this subject.



7 comments:

Raj Kumar Kushwaha said...
This comment has been removed by the author.
Raj Kumar Kushwaha said...
This comment has been removed by the author.
Uday said...

Hello Shervin,

I am interested in getting full document on 10053 , can you please email me on uday_moralwar@yahoo.com

Thanks , Uday

Anonymous said...

Hello,

What if IO Cost and Total Cost are same? so it will 0 (zero) at denominator. How the cost can be estimated at that time?

vijendar said...

what is the ideal value...

by calculation igot 18349444.06
is this ok or in which area i need to tune..

Here are actual values:
Estim. Costs = 19,419 , Estim. #Rows = 1,092,167
Estim. CPU-Costs = 2,697,368,277
Estim. IO-Costs = 19,272
Memory Used KB: 70,042,624

Please mail to vijay17sep@gmail.com

Thank you..

vijendar

Bhaskar B said...

please can you the useful docemnet at bhaskar5570@gmail.com

and sql optimzer and tuning related please send if you have any document..

i am very interest on performance tuning.

mail id: bhaskar5570@gmail.com

Thanks
Bhaskar

SK said...

Hi
Please can you send me how is the resc_io and resc_cpu calculated by oracle.
Thanks
SK