FAGLB03 Performance ISSue

[gajanand_gupta1]

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

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

| Id  | Operation                            | Name        | Rows  | Bytes | Cost (%CPU)| Time    |

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

|  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

[gajanand_gupta1 · Accepted solution]

This got resolved after sap suggested note

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

Gajanand Gupta

[Former Member · Accepted solution]

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