CODEWITHSUNDEEP
oracle-databasesqlperformancesql-execution-plan
Ashish Chandra
Ashish Chandra

Reputation: 381

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

enter image description here

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

Upvotes: 4

Views: 6795

Answers (2)

Jon Heller
Jon Heller

Reputation: 36922

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).

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

Check this

COST: Cost of the operation as estimated by the optimizer’s query approach. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the CPU_COST and IO_COST columns.

CPU_COST: CPU cost of the operation as estimated by the query optimizer’s approach. The value of this column is proportional to the number of machine cycles required for the operation. For statements that use the rule-based approach, this column is null.

You can refer this article to understand the What is the cost column in an explain plan?

Depending on your release and setting for the hidden parameter _optimizer_cost_model (cpu or io), the cost is taken from the cpu_cost and io_cost columns in the plan table (, in turn, estimates from sys.aux_stats$. The "cost" column is not any particular unit of measurement, it is a weighted average of the costs derived from the cost-based decision tree generated when the SQL statement is bring processed. The cost column is essentially an estimate of the run-time for a given operation.

Upvotes: 0

Related Questions