What is the reason of high CPU cost but low IO cost?

Question

I am running a query on a table which only has 283 records. The query is going for Full table scan as no indexed column value is passed in predicate.

Cost is only 12 , but CPU cost is very high - 4,75,189.

What is the reason for high CPU cost even though the table has very less no of records?

What is the difference between Cost & CPU Cost?

PL SQL developer is used as an IDE.

Query -:

SELECT     qmh_client, qmh_ip_timestamp, qmh_plant, qmh_key_tsklst_grp,
           qmh_grp_cntr, qmh_valid_dt, qmh_tdc_desc, qmh_cert_std,
           qmh_tsklst_usage, qmh_statu, qmh_lot_size_from, qmh_lot_size_to,
           qmh_tl_usage_uom, qmh_ctyp, qmh_cp_no, qmh_cp_version, qmh_tdform,
           qmh_ref_tdc, qmh_licn_no, qmh_guege_len, qmh_ip_activity,
           qmh_cp_activity, qmh_ip_sts_code, qmh_cp_sts_code, qmh_ltext_act,
           qmh_ltxt_sts_code, qph_ip_id, qmh_ip_mess, qmh_cp_id, qmh_cp_mess,
           qmh_rfd, qmh_smtp_addr, qmh_crt_time, qmh_crt_date, qmh_crt_by,
           qmh_ip_upd_time, qmh_ip_upd_date, qmh_ip_upd_by, qmh_cp_upd_time,
           qmh_upd_date, qmh_cp_upd_by, qmh_clas_sts_code, qmh_clas_id,
           qmh_clas_mess, qmh_clas_upd_time, qmh_clas_upd_date,
           qmh_clas_upd_by, qmh_prd_ind, qmh_tdc_type, qmh_pi_status
      FROM ipdmdm.t_qm_insp_hdr
     WHERE qmh_pi_status = 'N'
FOR UPDATE

Answer 1

According to the manual CPU_COST and IO_COST are measured in different ways. IO_COST is "proportional to the number of data blocks read by the operation", CPU_COST is "proportional to the number of machine cycles required for the operation".

The difference between the costs should not be too surprising since many database operations require orders of magnitude more CPU cycles than disk reads. A simple test on my PC produced similar results:

create table test1(a char(1000));
insert into test1 select level from dual connect by level <= 283;
begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/
explain plan set statement_id = 'cost test' for select * from test1 for update;
select cpu_cost, io_cost from plan_table where statement_id = 'cost test' and id = 0;

CPU_COST   IO_COST
--------   -------
348672          13

Even though it's called the Cost Based Optimizer, the cost is usually not a helpful metric when evaluating execution plans. The "Operation" and "Rows" columns are much more useful.

Also, if you're interested in explain plans, stop using the IDE's crippled view of them and use the text version that Oracle supplies. Use explain plan for select ... and select * from table(dbms_xplan.display);. PL/SQL Developer is a great tool, but its explain plan window is missing critical information (the Notes section) and has some bugs (it does not include session settings).