What is meant by estimated costs and estimated rows in SQL explain (ST05)?

[Former Member]

Hi

I was just wondering if someone could explain/clarify exactly what is meant by estimated costs and estimated rows in the 'explain' / execution path functionality of ST05.

For example, we could see a SQL statement was very inefficient accessing a table:

Estimated Costs = 6.006.615 , Estimated #Rows = 0

Does this literally mean that for 6 million costs / reads / effort, 0 results were returned??

Is this a ratio of efficiency?

We built an appropriate index and now we have:

Estimated Costs = 2 , Estimated #Rows = 1

A lot better! The job was taking 40+ hours and being cancelled; now it takes 5 minutes. So a 3 million times improvement sounds realistic...

However, we had another instance where the explain showed:

( Estim. Costs = 195.077 , Estim. #Rows = 538.660 )

and we built an index, and now the explain is:

( Estimated Costs = 41.867 , Estimated #Rows = 538.660 )

What exactly does this mean - as the costs has been reduced, but the rows is the same?

Thanks

Ross

[peter_strauss · Accepted solution]

Hi Ross,

>I was just wondering if someone could explain/clarify exactly what is meant by estimated costs and estimated rows in the >'explain' / execution path functionality of ST05

Take a look at note 766349, point 20.

>An EXPLAIN displays "Estimated Costs" and "Estimated Rows", which

>are simply the CBO's calculation results (refer to Note 7550631).

>Since these results are based upon a number of assumptions (column

>values are distributed equally, statistics), and depend upon the

>database parameter settings, the calculated costs and rows are

>useful only within a margin for error. High "Estimated Costs" and

>"Estimated Rows" are therefore neither a satisfactory nor a

>necessary indication of an expensive SQL statement. Also, the

>calculated costs have no actual effect upon the performance - the

>deciding costs are always the actual ones, in the form of BUFFER

>GETs, DISK READs, or processed rows.

So the costs and rows are values conjured up by the cost optimizer when calculating the access path that is most likely to be efficient. THEY ARE ESTIMATES!!!

>Does this literally mean that for 6 million costs / reads / effort, 0 results were returned??

As per the above, no. The costs and rows are estimated before the rows are fetched so there are no actual results yet.

>What exactly does this mean - as the costs has been reduced, but the rows is the same?

An efficient database access is exactly that; reads only the blocks that contain the rows it needs and nothing else. If the access is inefficient it will spend time accessing blocks that contain no data that is eventually contained in the result set.

This question would be better placed in the Oracle forum...

Regards,

Peter

[Former Member · Accepted solution]

Cheers Manoj & Peter

[Former Member]

[http://help.sap.com/saphelp_erp60_sp/helpdata/EN/d1/801ff1454211d189710000e8322d00/content.htm]

Estimated Cost

Estimates the database expenditure required to execute the statement. The cost-based optimizer estimates this value in terms of the I/O and CPU required by the statement. The larger the Estimated Cost the greater the expenditure.

Estimated # of Rows Returned:

Estimates the number of table rows that the SQL statement will return.

What exactly does this mean - as the costs has been reduced, but the rows is the same?

Since you created the index data retrival was effective/faster but it has to retrive the same number of rows