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.