Categories

JAVA DATEBASE
Technology Network Community
Oracle Database
Fusion Middleware
Development Tools
Java
Desktop
Server & Storage Systems
Enterprise Management
Berkeley DB Family
Cloud Computing
Big Data
Business Intelligence
Architecture
Migration and Modernization
E-Business Suite
Siebel
PeopleSoft Enterprise
JD Edwards World
Industries
JD Edwards EnterpriseOne
User Productivity Kit Pro (UPK) and Tutor
Governance, Risk & Compliance (GRC)
Master Data Management (MDM)
Oracle CRM On Demand
On Demand: SaaS and Managed Applications
AutoVue Enterprise Visualization
Primavera
ATG
Agile PLM
Endeca Experience Management
Fusion Applications
Archived Forums

 



Tags

General Questions


Execution Plan: Cost (%CPU)


Hi all,  I executed this update statament:   SQL> update oe.CUST_PAYMENT_INFO set CREDIT_CARD_NUMBER='4556988708236903' where CREDIT_CARD_NUMBER='4556988708236902';  524288 rows updated.  And the execution plan is as follow: (please refer the the link bellow)  http://www.flickr.com/photos/64727565@N06/5908120477/  My question is:   What does Cost (%CPU) 1298 (1) mean?  Thank you! Dan.

The CBO is now capable of estimating the number of machine cycles required for an operation, and factors this cost into the execution plan calculation. The CPU costs associated with servicing an Oracle query depend on the current server load (which Oracle cannot see). CPU costs are generally not as important unless the entire Oracle instance is using excessive CPU resources.  Source : http://www.dba-oracle.com/art_builder_cpu_io.htm  http://www.perftuning.com/files/pdf/ExecutionPlanCostFormulas.pdf

Dan wrote: Hi all,  I executed this update statament:   SQL> update oe.CUST_PAYMENT_INFO set CREDIT_CARD_NUMBER='4556988708236903' where CREDIT_CARD_NUMBER='4556988708236902';  524288 rows updated.  And the execution plan is as follow: (please refer the the link bellow)  http://www.flickr.com/photos/64727565@N06/5908120477/  My question is:   What does Cost (%CPU) 1298 (1) mean?  Thank you!  It means you use CPU costing(or system statistics are available).COST is a unit and measure for estimating resource according query.And CBO estimated it then choose lower cost`s plan to executing query.In your case according CPU cost mode CBO estimated cost(CPU) is 1298 based on optimizer parameter,system statistics and  your table statistics.There is not main matter this is update or just select statement.So if you issue  select * from  oe.CUST_PAYMENT_INFO  where CREDIT_CARD_NUMBER='4556988708236902';   In this case cost again will 1298.

CPU costs are generally not as important unless the entire Oracle instance is using excessive CPU resources. That is not right.CPU costing always important and introduced in 9i(as system statistics).So beginning 9i onward CPU costing enabled and this is very important matter.Till 9i the query optimizer can not be differ cost of single block read and multi  block read requests.This problem solved in 9i(using CPU costing and cache statistics,object level statistics).

Thanks Chinar!  Is there any formula to calculate CPU cost?  Regards, Dan!

Dan wrote: Thanks Chinar!  Is there any formula to calculate CPU cost?  Regards, Dan! EXPLAIN PLAN FOR .....

Thanks sb92075!  I found the topic CPU Costing Model at http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/ex_plan.htm#19598  According to the CPU costing model:  +Cost =  (#SRds * sreadtim ++           +#MRds * mreadtim ++           #CPUCycles / cpuspeed ) / sreadtim  where:      #SRDs is the number of single block reads     #MRDs is the number of multi block reads     #CPUCycles is the number of CPU Cycles *)     sreadtim is the single block read time     mreadtim is the multi block read time     cpuspeed is the CPU cycles per second  My question is:  1. How can i find how many Srds, and Mrds? 2. How do i find how much time that db used to read per each Srd and Mrd?  Please help! Dan.

For a reasonable introduction to CPU costing, here's a link to an article I wrote for Oracle Magazine seven years ago.  http://jonathanlewis.files.wordpress.com/2010/11/cpu_costing.pdf  Regards Jonathan Lewis


Related Links

number of db files
Script for daily Incremental Backup with Weekly full back?
Oracle Read Consistency (System Change Number)
character set
Is there any way to see the source of view sys.v$parameter ?
Roles& profile
tables purge
High Water Mark for Undo tablespace..!!
Delete with multiple join
error - while creating index
Tuning
Equivalent of Informix's onstat command
PROBLEM WITH INDEX REBUILDING
RMAN duplicate. How to preserve users accounts on AUX database
Throttling Oracle Session?
Urgent ! Oracle 11g RAC On RedHat Linux 5