Explain SQL Query execution plan: Oracle

[Former Member]

Dear Masters,

Kindly help me to understand execution plan for an SQL statement. I have following SQL execution plan for a query in system. How should I interpret it. I thank You in advace for your guidance.

SELECT STATEMENT ( Estimated Costs = 1.372.413 , Estimated #Rows = 0 )

5 NESTED LOOPS

( Estim. Costs = 1.372.413 , Estim. #Rows = 3.125 )

Estim. CPU-Costs = 55.798.978.498 Estim. IO-Costs = 1.366.482

2 TABLE ACCESS BY INDEX ROWID MSEG

( Estim. Costs = 1.326.343 , Estim. #Rows = 76.717 )

Estim. CPU-Costs = 55.429.596.575 Estim. IO-Costs = 1.320.451

Filter Predicates

1 INDEX RANGE SCAN MSEG~R

( Estim. Costs = 89.322 , Estim. #Rows = 60.069.500 )

Search Columns: 1

Estim. CPU-Costs = 2.946.739.229 Estim. IO-Costs = 89.009

Access Predicates

4 TABLE ACCESS BY INDEX ROWID MKPF

( Estim. Costs = 1 , Estim. #Rows = 1 )

Estim. CPU-Costs = 4.815 Estim. IO-Costs = 1

Filter Predicates

3 INDEX UNIQUE SCAN MKPF~0

Search Columns: 3

Estim. CPU-Costs = 3.229 Estim. IO-Costs = 0

Access Predicates

[former_member182114 · Accepted solution]

Hi Panjak,

Yeahh, there's a huge unperformatic SQL statment, what I can see from this acces plan is:

1 DBO decided to start the query on index R on MSEG, using only part of the index (only one column) with no good uniqueness, accessing disk IO-Costs for this (60mi records), and expecting many interactions (loops) in memory to filter, see CPU-Costs.

So with the parameters you gave to SQL, they start in a very bad way.

2 After that program will access the MSEG commanded by what was found on First step, also with a huge loading from DB and filtering (another where criteria on MSEG fields, not found on index R), reducing the result set to 76.717 rows.

3/4 With this, program goes direct to primary key index on MKPF with direct access (optimized access) and follow to access database table MKPF.

5 At last will "loop" the result sets from MSEG and MKPF, mixing the tuplas generating the final result set.

Do you want to share your SQL, the parameters you are sending and code which generate it with us?

Regards, Fernando Da Ró

[Former Member · Accepted solution]

Dear Fernando Ros,

Thank You Very much for explaining this in detail. One more query....I could see big numbers in cost estimation. Still what are the points which are decision factor of declaring it expensive. Like average CPU cost or some other indicator...?

Regards

Pankaj

[former_member182114 · Accepted solution]

Hi Pankaj,

To trigger an tunning effort with dev team, the explain plain of an SQL must not be analyzed alone.

You must investigate what is around the SQL statment, the Costs values can give you a hint (CPU and I/O), but how often this is executed also must be checked.

Imagine one "optimized" SQL with 400 of CPU and low I/O, but executed 1.000.000 times in less than one hour?!?!? Should burn a CPU..rsss

BTW: The SQL you wrote isn't a case for tunning, but a complete rewriting of code, I suggest you to start a new thread with the SQL and more details of the execution on ABAP Performance forum.

Regards, Fernando Da Ró

[Former Member · Accepted solution]

These MKPF/MSEG joins are a classic. They are costly because the tables are often very large. In your case there seems to be at least 60mio rows in MSEG...

Very easy to end up with a expensive execution. You should evaluate if you are deleting old entries or if there is archiving being done.

Cheers Michael