explain relation between Estim. Costs Vs Estim. #Rows

[Former Member]

Hi Experts,

I am planning for Expensive SQL statements tuning...

But I am not very clear about the exact calculated relation between..

           Estim. Costs Vs Estim. #Rows    as well as Estim. CPU costs  Vs Esti.IO costs

Somehow I managed to get information on Estim. CPU costs  Vs Esti.IO costs with the blog

"http://oradbatips.blogspot.in/2007/10/cpu-cost-vs-io-cost-in-execution-plan.html"

But I am not able to get information about Estim. Costs Vs Estim. #Rows..

Please help me by providing simple and calculated explanation...

Please help me if you came across the below statement:

"

SELECT

/*+

INDEX("BBP_PDVIEW_BIH" "BBP_PDBEI~REF" "BBP_PDBEI ^ REF" "BBP_PDBEI_REF" "BBP_PDBEI_REFX")

*/

*

FROM

"BBP_PDVIEW_BIH"

WHERE

"CLIENT"=:A0 AND "BE_REFOBJ_TYPE"=:A1 AND "BE_REFOBJ"=:A2

Estim. Costs = 19,419 , Estim. #Rows = 1,092,167

Estim. CPU-Costs = 2,697,368,277  Estim. IO-Costs = 19,272

Memory Used KB: 70,042,624"

I have checked that in DB all fields are present except the colored ones.

This is SRM system..

Please explain by taking the given values....

Thanks in advance...

Vijendar

[stefan_koehler · Accepted solution]

Hi Vijendar,

> I am planning for Expensive SQL statements tuning ...

To be honest - please stop right now, if you have no clue about E-Rows or E-Costs and need to google all this stuff. SQL tuning is much more than just reading a SQL execution plan. You usually make it even worse by design (just look at that provided HINTs for example).

However to answer your questions:

• Estim. #Rows = Calculated cardinality of a specific execution plan or execution plan step based on statistics
• Estim. Costs = Numeric value based on an algorithm for calculating the costs of a specific execution plan or execution plan step (including I/O and CPU in newer database releases)

I already shared various examples in my CBO blog series about how the cardinality is estimated in several cases:

• [Oracle] DB Optimizer Part I - Understanding FOR ALL ENTRIES (and its processing by SAP DBSL / Oracl...
• [Oracle] DB Optimizer Part II - Extending execution plans or how-to find the bad one
• [Oracle] DB Optimizer Part III - Extending and tuning of execution plans in practice
• [Oracle] DB Optimizer Part IV - What the heck ... Troubleshooting why hints are not considered
• [Oracle] DB Optimizer Part V - Introduction of dynamic sampling and why it is used in SAP BI environ...
• [Oracle] DB Optimizer Part VI - Effects of disabled bind variable peeking, adaptive cursor sharing a...

Once again this is just what the cost based optimizer thinks - not necessarily what the real world looks like. There are thousands of possibilities why the optimizer is way off (like insufficient (basic) statistics, complex predicates, value distribution and many many more).

Regards

Stefan

P.S.: If you want to know more about algorithms and the used inputs for the calculation - please check out the book "Cost-Based Oracle Fundamentals" by Jonathan Lewis.

[Former Member]

And besides:

I have checked that in DB all fields are present except the colored ones.

BBP_PDVIEW_BIH --> is the table you are selecting data from

BBP_PDBEI~REF / BBP_PDBEI^REF /  BBP_PDBEI_REF / BBP_PDBEI_REFX --> are indexes

This is called a database hint, SAP tries to force Oracle to use the ~REF index. It is ok when only BBP_PDBEI~REF exists. SAP does not seem to know the exact name, so it mentions them all.

Cheers Michael