Home » RDBMS Server » Performance Tuning » What is the number of cost?
What is the number of cost? [message #271280] Mon, 01 October 2007 02:28 Go to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear!

Following the Oracle document, the cost means to..

Quote:

The cost represents units of work or resource used. The CBO uses disk I/O, CPU usage, and memory usage as units of work. So, the cost used by the CBO represents an estimate of the number of disk I/Os and the amount of CPU and memory used in performing an operation. The operation can be scanning a table, accessing rows from a table by using an index, joining two tables together, or sorting a row set. The cost of a query plan is the number of work units that are expected to be incurred when the query is executed and its result produced.



And, please view my explain plan:

SQL> set autotrace traceonly explain
SQL> SELECT hdld.ma_kh, tbld.ma_tb, TRIM (tbld.ten_tb) ten_tb,
  2         tbld.diachi diachitb, tbld.loaitb_id, ltb.dichvuvt_id, 1 sys_pageid,

  3         100 sys_rec_per_page
  4    FROM ccs_qbh1.thuebao_lds tbld,
  5         ccs_qbh1.hopdong_lds hdld,
  6         ccs_qbh1.loaihinh_tbs ltb
  7   WHERE tbld.ma_hd = hdld.ma_hd
  8     AND ltb.loaitb_id = tbld.loaitb_id
  9     AND ROWNUM < 101
 10     AND hdld.so_gt = '3'
 11  /

Execution Plan
----------------------------------------------------------
Plan hash value: 4048859822

--------------------------------------------------------------------------------

---------------

| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%C

PU)| Time     |

--------------------------------------------------------------------------------

---------------

|   0 | SELECT STATEMENT               |              |     1 |   154 |     6
(0)| 00:00:01 |

|*  1 |  COUNT STOPKEY                 |              |       |       |
   |          |

|   2 |   NESTED LOOPS                 |              |     1 |   154 |     6
(0)| 00:00:01 |

|   3 |    NESTED LOOPS                |              |     1 |   148 |     5
(0)| 00:00:01 |

|   4 |     TABLE ACCESS BY INDEX ROWID| HOPDONG_LDS  |     1 |    33 |     3
(0)| 00:00:01 |

|*  5 |      INDEX RANGE SCAN          | HDLD_CMT     |     1 |       |     1
(0)| 00:00:01 |

|   6 |     TABLE ACCESS BY INDEX ROWID| THUEBAO_LDS  |     1 |   115 |     2
(0)| 00:00:01 |

|*  7 |      INDEX RANGE SCAN          | TBLD_MAHD    |     1 |       |     1
(0)| 00:00:01 |

|   8 |    TABLE ACCESS BY INDEX ROWID | LOAIHINH_TBS |     1 |     6 |     1
(0)| 00:00:01 |

|*  9 |     INDEX UNIQUE SCAN          | LHTB_PK      |     1 |       |     0
(0)| 00:00:01 |

--------------------------------------------------------------------------------

---------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<101)
   5 - access("HDLD"."SO_GT"='3')
   7 - access("TBLD"."MA_HD"="HDLD"."MA_HD")
   9 - access("LTB"."LOAITB_ID"="TBLD"."LOAITB_ID")


It's my questions: What does the number of cost represent? Why is it the number? Where is the number? I do not really understand.

Would you like to explain or navigate to the related document which can be further clean explanation?

Thank you very much!

[Updated on: Mon, 01 October 2007 02:32]

Report message to a moderator

Re: What is the number of cost? [message #271284 is a reply to message #271280] Mon, 01 October 2007 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is nothing (for you) just internal optimizer number that allows you to compare 2 plans.
It "represents units of work or resource used".

Quote:
Would you like to [...] navigate to the related document

Which document? you don't tell us nor give a link.

Regards
Michel

[Updated on: Mon, 01 October 2007 02:40]

Report message to a moderator

Re: What is the number of cost? [message #271287 is a reply to message #271280] Mon, 01 October 2007 02:47 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you Michael for your reply!

My English is bad, and it's same to my Oracle knowledge.

I mean that "I did not understand the number of cost" and said I that some one who could explain the number or give me any link related it.

Give up my mistake, please!


Re: What is the number of cost? [message #271288 is a reply to message #271287] Mon, 01 October 2007 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is related to nothing (for you).
It is just a number.
Say it is a number of quarks.

Now the most important thing is not what it indicates but how you can use it.
You can use it to compare to 2 execution plans for the same query/need.
The lowest it is, the more efficient is your query (I repeat for the same need).
You can't compare 2 differents queries (for 2 different needs) with it.

Regards
Michel
Re: What is the number of cost? [message #271291 is a reply to message #271288] Mon, 01 October 2007 02:59 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Yeah!

Thank you, Michael! I will try to investigate the most efficient execution plan instead of the numbers.

Regards!
Re: What is the number of cost? [message #271298 is a reply to message #271291] Mon, 01 October 2007 03:34 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can find the most efficient query using the cost number.

Regards
Michel
Previous Topic: Slow update with Bulk collect and Forall
Next Topic: How to Best Performance Param of Oracle in Windows
Goto Forum:
  


Current Time: Thu Dec 08 20:30:53 CST 2016

Total time taken to generate the page: 0.09453 seconds