FAGLB03 Performance ISSue

gajanand_gupta1 — Thu, 24 Sep 2015 05:45:43 GMT

Hi Friends

In transaction FAGLB03 we are facing performance issue and this came after the EHP7 upgrade from ECC 6.0.

Please see attached screenshot for the detailed issue and also i have taken ST05 Trace SQL statement and ran on database directly then oracle database gives me results in 2-3 seconds while sap transaction is giving report in 1200 seconds which is not acceptable. Kindly see and help me if you can do.

Oracle Database execution plan directly on database and results in 2-3 sec.

SELECT

"A"."BUKRS","A"."HKONT","A"."AUGDT","A"."AUGBL","A"."ZUONR","A"."BLDAT","A"."WAERS","A"."XBLNR",

"A"."BLART","A"."MONAT","A"."SHKZG","A"."GSBER","A"."MWSKZ","A"."DMBTR","A"."WRBTR","A"."SGTXT",

"A"."AUFNR","A"."WERKS","A"."KOSTL","A"."ZFBDT","A"."XOPVW","A"."VALUT","A"."BDIFF","A"."BDIF2",

"A"."VBUND","A"."PSWSL","A"."WVERW","A"."DMBE2","A"."DMBE3","A"."BDIF3","A"."XRAGL","A"."PROJK",

"A"."XARCH","A"."PSWBT","A"."XNEGP","A"."RFZEI","A"."CCBTC","A"."XREF3","A"."BUPLA","A"."BEWAR",

"A"."IMKEY","A"."DABRZ","A"."GRANT_NBR","A"."FKBER","A"."GEBER","A"."PPRCT","A"."BUZID",

"A"."AUGGJ","A"."UZAWE","A"."SEGMENT","A"."PSEGMENT","A"."PGEBER","A"."PGRANT_NBR","A"."KIDNO",

"B"."RCLNT","B"."RYEAR","B"."DOCNR","B"."RLDNR","B"."RBUKRS","B"."DOCLN","B"."ACTIV","B"."RMVCT",

"B"."RTCUR","B"."RUNIT","B"."AWTYP","B"."RRCTY","B"."RVERS","B"."LOGSYS","B"."RACCT",

"B"."COST_ELEM","B"."PRCTR","B"."RBUSA","B"."KOKRS","B"."PPRCTR","B"."SBUSA","B"."TSL","B"."HSL",

"B"."KSL","B"."OSL","B"."MSL","B"."WSL","B"."DRCRK","B"."POPER","B"."RWCUR","B"."GJAHR",

"B"."BUDAT","B"."BELNR","B"."BUZEI","B"."BSCHL","B"."BSTAT","B"."LINETYPE","B"."XSPLITMOD",

"B"."USNAM","B"."TIMESTAMP"

FROM "SAPDBC"."BSIS" "A" INNER JOIN "SAPDBC"."FAGLFLEXA" "B" ON "B"."RBUKRS"="A"."BUKRS" AND "B"."BELNR"="A"."BELNR" AND

"B"."GJAHR"="A"."GJAHR" AND "B"."BUZEI"="A"."BUZEI" AND "B"."RCLNT"="A"."MANDT"

WHERE "A"."BUKRS"='DBCL' AND "A"."HKONT"='0024501003' AND "A"."XARCH"=' ' AND "A"."MANDT"='100' AND "B"."RACCT"='0024501003'

AND "B"."RLDNR"='0L' AND ("B"."BSTAT"=' ' OR "B"."BSTAT"='L') AND "B"."BUDAT"<='20150831' AND "B"."POPER"=

'005' AND "B"."RYEAR"='2015' AND "B"."RCLNT"='100'

Execution Plan

----------------------------------------------------------

Plan hash value: 910208543

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 116 | 54288 | 95 (2)| 00:00:01 |

| 1 | NESTED LOOPS | | 116 | 54288 | 94 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 116 | 54288 | 94 (0)| 00:00:01 |

|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| FAGLFLEXA | 116 | 22852 | 1 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | FAGLFLEXA~2 | 5 | | 1 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | BSIS~1 | 1 | | 1 (0)| 00:00:01 |

| 6 | TABLE ACCESS BY INDEX ROWID | BSIS | 1 | 271 | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter(("B"."BSTAT"=' ' OR "B"."BSTAT"='L') AND "B"."BUDAT"<='20150831')

4 - access("B"."RCLNT"='100' AND "B"."RLDNR"='0L' AND "B"."RBUKRS"='DBCL' AND

"B"."RACCT"='0024501003' AND "B"."RYEAR"='2015' AND "B"."POPER"='005')

5 - access("A"."MANDT"='100' AND "A"."BUKRS"='DBCL' AND "B"."BELNR"="A"."BELNR" AND

"B"."GJAHR"="A"."GJAHR" AND "B"."BUZEI"="A"."BUZEI" AND "A"."HKONT"='0024501003' AND

"A"."XARCH"=' ')

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

- 1 Sql Plan Directive used for this statement

Statistics

----------------------------------------------------------

35 recursive calls

0 db block gets

56 consistent gets

11 physical reads

0 redo size

7819 bytes sent via SQL*Net to client

551 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

5 rows processed

Executre Transaction faglb03

Put below values and execute

This will show summary in good time.

Now double click on below highlighted value

Click on yes in below screen

Now above screen is taking long time which is not acceptable.

Regards

Gajanand Gupta

Re: FAGLB03 Performance ISSue

gajanand_gupta1 — Fri, 25 Sep 2015 16:12:27 GMT

This got resolved after sap suggested note

1524829 - FAGLL03: Oracle HINT - Access should start with BSIS/BSAS

Gajanand Gupta

Re: FAGLB03 Performance ISSue

Former Member — Mon, 28 Sep 2015 18:56:09 GMT

Is this a published note? I am unable to find details on this note?

Question Re: FAGLB03 Performance ISSue in Additional Q&A

FAGLB03 Performance ISSue

Re: FAGLB03 Performance ISSue

Re: FAGLB03 Performance ISSue