cancel
Showing results for 
Search instead for 
Did you mean: 

What is the difference between est. cost, CPU cost, and IO cost

Former Member
0 Kudos

When looking at and execution plan there is est. cost, CPU cost, and IO cost.

Can someone explain these and more importantly explain which one is the most

important to tune and the step to take to reduce these cost to a respect level.

I have seen many program hit 10,000 cost and 1 Mio CPU cost and I need to get that down to improve performance.

I am running on 10.2.

Thanks

Mikie

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

CPU cost is the processor cost: calculations, internal processing with the memory... programs can use extensive processor cost as you have already noticed... these are single instructions between the processor and the memory

I/O: input/output; this is referring to the movement of data from/to hard drives or other peripherals.. These are complex operations.

They're both important and you'll have to balance both. For instance a program accessing the database too much (lots of I/O) may run for ages. You can tune this program and lower your I/Os, therefore increasing your CPU time; the quantity of work is still the same!

In general you should have a look and see what can be done if you are experiencing issue or if you want to improve your response time...

hope that helps...

Olivier.

lbreddemann
Active Contributor
0 Kudos

Hi Olivier,

sorry to disagree but:

[...]

> They're both important and you'll have to balance

> both. For instance a program accessing the database

> too much (lots of I/O) may run for ages. You can tune

> this program and lower your I/Os, therefore

> increasing your CPU time; the quantity of work is

> still the same!

is just not right.

The amount of work to do for the database to get the job done (that is to deliver the requested results) may vary A LOT!

That is why there is a optimizer at all.

It's true: I/O cost model the time required to perform necessary read/write operations and CPU cost do the same for handling the data in memory.

With indexes, database features active and database versions the optimizer can choose from selecting different access, filter and sorting mechanisms to answer the db requests.

Usually there's not much you can do to the CPU costs - the database has it's internal rating concerning how "expensive" a sort-merge-join will be or a hash-distinct function.

What you can (and usually should) do, is to lower the main contributor to processing and I/O costs: the volume of data.

Therefore one will try to reduce the number of rows to be processed early in the query process. That can either be done by changing the selection itself or via the indexing scheme. Furthermore physical I/O might be reduced by using specific db features (compression, datablock sizing, bitmap indexes, partitioning etc.).

Anyhow, to come back to the original post: it's important to think of costs as time-to-execute modelled in CPU-activity and I/O-operations.

The goal is to deliver the result at the fastest - so, nobody should bother about the absolute numbers when it comes to costs. The question has to be: what execution time is meant with this?

KR Lars

Former Member
0 Kudos

Hi Lars,

agree with you in general an I didn't want to go so deep...

As you said, the important is the DB to deliver the best result (the result could be a SELECT, INSERT or any other DB operation INDEX recreation...not only read...)

But sorry, isn't that what you gain is lost somewhere? For instance, implementing indexes or any other database feature will impact for instance the write process, or consume more CPU isn't it?

Don't misunderstand me, I know what it is to write code, an application or implement a process in order to have our users happy but let's stay in the SAP environment; it is not the goal for the customer to rewrite standard SAP development in order to gain some points on the values displayed by the CBO, correct?

Of course the Oracle DBA may suggest to partition or create a bitmap index on MSEG table in R/3 in order to speed up their official customs reporting, what's the real life? No-one would make this change in a live running SAP system...

In terms of volume of data unfortunately, our business decide what they need in order to run their processes! So we have to deal with it.

But you are right, my last comments where a bit unprecise and it could be discussed at length; you have to keep an eye on these costs (I didn't write the opposite) and react when perf is affected; agreed. Note that Mikie asked about the difference between CPU and I/O, which as far as my knowledge enables me to answer, is not wrong.

Olivier.

Answers (0)