Tune this oracle SQL statement

[Former Member]

Hi Oracle Gurus,

Can you please help me tune the below query. I got the query from ST04> Expensive SQLs. This particular query was using us the max resources.

Can you please help me understand and start off tuning.

cheers

Chander

####################################################################

SQL Statement:

SELECT

"VBELN" , "POSNR" , "MATNR" , "WERKS" , "UEPOS" , "PSTYV" , "KWMENG"

FROM

"VBAP"

WHERE

"MANDT" = :A0#

####################################################################

Execution Plan:

Explain from v$sql_plan: Address: 07000002A67A4E88 Hash_value: 376673483 Child_number: 0

SELECT STATEMENT ( Estimated Costs = 76,270 , Estimated #Rows = 0 )

2 TABLE ACCESS BY INDEX ROWID VBAP

( Estim. Costs = 76,270 , Estim. #Rows = 4,338,737 )

1 INDEX RANGE SCAN VBAP~0

( Estim. Costs = 37,905 , Estim. #Rows = 4,338,737 )

Search Columns: 1

#################################################################

Table and Index information for VBAP:

Table VBAP

Last statistics date 04.04.2007

Analyze Method ample 8,677,473 Rows

Number of rows 8,677,473

Number of blocks allocated 1,055,241

Number of empty blocks 6,647

Average space 1,416

Chain count 0

Average row length 807

Partitioned NO

UNIQUE Index VBAP~0

Column Name #Distinct

MANDT 2

VBELN 474,509

POSNR 1,369

####################################################################

Detailed information for Index VBAP~0:

UNIQUE Index VBAP~0

Column Name #Distinct

MANDT 2

VBELN 474,509

POSNR 1,369

Last statistics date 12.04.2007

Analyze Method ample 9,525,710 Rows

Levels of B-Tree 3

Number of leaf blocks 75,803

Number of distinct keys 9,525,710

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 1,449,586

[lbreddemann]

Hi Chander,

beside the fact that it's nearly impossible to tune a statement with the limited information you gave us (what about db parameter setup) you could start at looking at the logic of the statement.

The statement should get you several fields from table VBAP for all rows where MANDT is equal to the value of the bind variable.

From the cbo statistics you added to your post, we know, that the whole table vbap contains 8.6 Mio rows.

In the MANDT field there are two distinct values. So - assuming that bind-variable-peeking and histograms are NOT used here - the cbo might come up with two conclusions:

:A0 contains a value that is NOT one of the two in MANDT of VBAP, which would lead to a result set of 0.

:A0 contains one of the two values. Then - cut off from furhter information - the cbo has to believe that only a half of the rows of the table fit to the condition. Thus it comes up with the estimated rows of 4.3 Mio rows when accessing the table via the index.

When I - again assume - that the two distinct values are NOT equally distributed at all and one of both is for the productive client and the other value is either accedentally inserted or it belongs to a client containing much less data, then the situation is even worse.

In that case the query asks for much more rows to be given back.

So what to do here? Hmm... first shot (ALWAYS!): get your db parameters right! Perhabs it's just that optimizer_index_cost_adj is set too high on your system.

If that does not help... recheck the application logic.

What do you do with that much data?

Perhabs you can add a hint to the query, so that Oracle does not choose the index when reading most of the table data:

SELECT /*+FULL("VBAP")*/ ... 

Best regards,

Lars

[Former Member]

Hi Lars,

That was a good insight to analyze the SQL statement. Thank you for that.

My optimizer_index_cost_adj is set to 10.

Do you have any more to suggest to check?

Regards

Chander

[Former Member]

Hi Chander,

first of all you should check how often the statement was executed? If it runs frequently, you should check why the where clause is that poorly selective, as it is. I could imagine, that you run into a ABAP statement using FOR ALL ENTRIES on an empty internal table. This could produce something you showed us in your original posting. I would recommend to check the ABAP coding instead of trying to tune the SQL. May be there is already a solution in a note.

Best regards

Ralph Ganszky

[Former Member]

Chander

Try this

SELECT /*+ first_rows */

"VBELN" , "POSNR" , "MATNR" , "WERKS" , "UEPOS" , "PSTYV" , "KWMENG"

FROM

"VBAP"

WHERE

"MANDT" = :A0#

Vinod