Oracle Query tuning

[Former Member]

Hi SAP Experts,

Here is Oracle query and I am trying to tune this but even after doing some work I am only able to reduce the cost of this query from 55000 to about 37000.

Actually, I am working on tunning project and got stuck in one of SAP standard transaction kks5 (for variance scrap calculation). If any one knows or has any idea to tune this process pl. let me know.

I can tell atleast this thing that SGA is showing perfect results and tried to analyze by using all possible method and found out the query below.

I know there is alot posibility to reduce this cost. Is there any way any expert can suggest me to tune this.

SELECT

T1."MANDT", T1."AUFNR", T2."POSNR", T1."OBJNR", T1."PKOSA", T1."KOKRS", T1."WERKS", T1."AUART",

T1."AUTYP", T1."ERDAT", T1."BUKRS", T1."WAERS", T1."IDAT2", T1."LOEKZ", T1."AWSLS", T1."ABGSL",

T1."FLG_MLTPS", T1."PROCNR", T1."PROTY", T1."GSBER", T3."AUFPL", T3."PRODNET", T3."COLORDPROC",

T3."LEAD_AUFNR", T2."PWERK", T2."PROJN", T2."KDAUF", T2."KDPOS", T2."KZBWS", T2."SOBKZ",

T2."MATNR", T2."LTRMI", T2."ELIKZ", T2."SAFNR", T2."VERID", T2."RTP01", T2."RTP02", T2."RTP03",

T2."OBJNP", T2."FXPRU", T3."MAUFNR", T3."SPLSTAT"

FROM

"AUFK" T1, "AFPO" T2, "AFKO" T3

WHERE

T1."MANDT" = T2."MANDT" AND T1."AUFNR" = T2."AUFNR" AND T1."MANDT" = T3."MANDT" AND

T1."AUFNR" = T3."AUFNR"

Execution Plan

SELECT STATEMENT ( Estimated Costs = 37,912 , Estimated #Rows = 863,168 )

8 MERGE JOIN

( Estim. Costs = 37,912 , Estim. #Rows = 863,168 )

5 MERGE JOIN

( Estim. Costs = 16,807 , Estim. #Rows = 864,897 )

2 TABLE ACCESS BY INDEX ROWID AUFK

( Estim. Costs = 83 , Estim. #Rows = 866,629 )

1 INDEX FULL SCAN AUFK~0

( Estim. Costs = 26 , Estim. #Rows = 866,629 )

4 SORT JOIN

( Estim. Costs = 16,724 , Estim. #Rows = 864,897 )

3 TABLE ACCESS FULL AFPO

( Estim. Costs = 7,357 , Estim. #Rows = 864,897 )

7 SORT JOIN

( Estim. Costs = 21,105 , Estim. #Rows = 864,897 )

6 TABLE ACCESS FULL AFKO

( Estim. Costs = 16,536 , Estim. #Rows = 864,897 )

Thanks and Regards,

Raj

[former_member197561 · Accepted solution]

Hi,

In my opinion it is about index.

The execution of the statement reports that on AUFK index is used and on AFPO and on AFKO no index is used - time can be reduced if appropriate indexes are defined.

TABLE ACCESS BY INDEX ROWID AUFK 

                  ( Estim. Costs = 83 , Estim. #Rows = 866,629 )

TABLE ACCESS FULL AFPO 

                      ( Estim. Costs = 7,357 , Estim. #Rows = 864,897 )

TABLE ACCESS FULL AFKO

                  ( Estim. Costs = 16,536 , Estim. #Rows = 864,897 )

Best regards,

Sylvia

[jihoon_kim]

I don't see any WHERE condition in the SQL statement. There are only JOIN conditions.

So, I think optimizer chose the right access path.

[former_member251078]

Hello Rohit,

Seems to be problem with the statistics. Update the statistics for all the tables and underlying indexes. Then execute this again. Please let me know the explain plan again. Also if you are using oracle 10g. Please make sure all the database parameters are according to SAP Note 830576. Most important parameters are OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. Also make sure all event parameters are set according to this note as well.

Regards

Dileep

[lbreddemann]

>

> Seems to be problem with the statistics.

Wow - how did you see that ?

> Also if you are using oracle 10g. Please make sure all the database parameters are according to SAP Note 830576. Most important parameters are OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. Also make sure all event parameters are set according to this note as well

Ok, that is a good idea, really!

Anyhow, what makes me wonder is why the heck is a SORT/MERGE Join done here? And why is it done twice?

Usually a SORT/MERGE join is the worst option to take if other options like HASH JOIN or NESTED LOOP JOIN are available.

So I wonder why these are not available. Are there usuable indexes in place for the join transitions?

That is: Indexes on (MANDT, AUFNR) ?

Really - check wether there are suitable indexs in place.

The last point has already been mentioned: there is no restriction in the query at all.

Do you really want to join these three big tables even over all available clients (MANDT field) ?

Looks like nonsense to me...

regards,

Lars

BTW: there is a seperate forum for Oracle related issues:

[Former Member]

Hi,

Did you see if the statistics of this table are updated?

Did you define index to fields of AFKO?

Best regards

[Former Member]

Hi,

Yes stats for all the tables are up to date. Also, the indexes for all tables exist.

Thanx,

Rohit

[Former Member]

Hi,

Try to use the rule hint.

select /*+ rule */ <your query>

Regards

[Former Member]

Hi,

Its a standard sap query used to create a database view VKKS0.

So, How can I use rule for this

Thanx and Regads,

Raj

[lbreddemann]

The rule-based-optimizer is desupported nowadays.

regards,

Lars