cancel
Showing results for 
Search instead for 
Did you mean: 

Upgrade to Oracle 12.1.0.2 : performance problem

mario_bisonti2
Participant
0 Kudos

Hallo.

I upgraded my SAP Production and Test systems to Oracle 12.1.0.2 from 11.2.0.4

I note that in Production, some reports are very slower than in Test system

Note that oracle parameters are the same except for the parameter regarding the sga size that are larger in Production than Test

I trace an ABAP report and I saw a different plan.
In TEST:

QL Statement

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

SELECT

  *

FROM

  "ZMMODA"

WHERE

  "MANDT"=:A0 AND "MATNR"=:A1 AND "EINDT" BETWEEN :A2 AND :A3 AND "WERKS"=:A4

ORDER BY

  "MANDT","EBELN","EBELP"

Execution Plan

Explain from gv$sql_plan: Address: 000007FF60A43448 Hash_value:  817699050 Child_number:  0 Instance_ID: 1

Sql_id: 825uahssbu67a  Parse Timestamp: 20160105   05:18:33

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

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

  |

  ---   11 SORT ORDER BY

      |    ( Estim. Costs = 368 , Estim. #Rows = 10 )

      |    Estim. CPU-Costs = 29.374.075 Estim. IO-Costs = 366

      |

      ---   10 FILTER

          |    Filter Predicates

          |

          ---   9 HASH JOIN

              |   ( Estim. Costs = 367 , Estim. #Rows = 10 )

              |   Estim. CPU-Costs = 15.281.579 Estim. IO-Costs = 366

              |   Memory Used KB: 194.560

              |   Access Predicates

              |

              |--   6 NESTED LOOPS

              |   |   ( Estim. Costs = 8 , Estim. #Rows = 10 )

              |   |   Estim. CPU-Costs = 64.074 Estim. IO-Costs = 8

              |   |

              |   |--   4 NESTED LOOPS

              |   |   |   ( Estim. Costs = 8 , Estim. #Rows = 10 )

              |   |   |   Estim. CPU-Costs = 64.074 Estim. IO-Costs = 8

              |   |   |

              |   |   |--   2 TABLE ACCESS BY INDEX ROWID EKPO

              |   |   |   |   ( Estim. Costs = 2 , Estim. #Rows = 10 )

              |   |   |   |   Estim. CPU-Costs = 18.381 Estim. IO-Costs = 2

              |   |   |   |

              |   |   |   ------1 INDEX RANGE SCAN EKPO~1

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

              |   |   |           Search Columns: 5

              |   |   |           Estim. CPU-Costs = 4.806 Estim. IO-Costs = 1

              |   |   |           Access Predicates Filter Predicates

              |   |   |

              |   |   ------3 INDEX RANGE SCAN EKET~0

              |   |           Search Columns: 3

              |   |           Estim. CPU-Costs = 3.069 Estim. IO-Costs = 0

              |   |           Access Predicates

              |   |

              |   ------5 TABLE ACCESS BY INDEX ROWID EKET

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

              |           Estim. CPU-Costs = 4.569 Estim. IO-Costs = 1

              |           Filter Predicates

              |

              ---   8 TABLE ACCESS BY INDEX ROWID EKKO

                  |   ( Estim. Costs = 358 , Estim. #Rows = 55.290 )

                  |   Estim. CPU-Costs = 9.087.005 Estim. IO-Costs = 357

                  |

                  ------7 INDEX SKIP SCAN EKKO~W

                          ( Estim. Costs = 55 , Estim. #Rows = 55.290 )

                          Search Columns: 1

                          Estim. CPU-Costs = 2.609.239 Estim. IO-Costs = 55

                          Access Predicates Filter Predicates

instead in Production:

SQL Statement

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

SELECT

  *

FROM

  "ZMMODA"

WHERE

  "MANDT"=:A0 AND "MATNR"=:A1 AND "EINDT" BETWEEN :A2 AND :A3 AND "WERKS"=:A4

ORDER BY

  "MANDT","EBELN","EBELP"

Execution Plan

Explain from gv$sql_plan: Address: 00000005054A74E0 Hash_value:  817699050 Child_number:  0 Instance_ID: 1

Sql_id: 825uahssbu67a  Parse Timestamp: 20160105   14:03:53

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

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

  |

  ---   15 SORT ORDER BY

      |    ( Estim. Costs = 1.184 , Estim. #Rows = 10 )

      |    Estim. CPU-Costs = 201.740.851 Estim. IO-Costs = 1.171

      |

      ---   14 FILTER

          |    Filter Predicates

          |

          ---   13 HASH JOIN

              |    ( Estim. Costs = 1.183 , Estim. #Rows = 10 )

              |    Estim. CPU-Costs = 186.836.041 Estim. IO-Costs = 1.171

              |    Memory Used KB: 201.728

              |    Access Predicates

              |

              |--   6 NESTED LOOPS

              |   |   ( Estim. Costs = 8 , Estim. #Rows = 10 )

              |   |   Estim. CPU-Costs = 65.756 Estim. IO-Costs = 8

              |   |

              |   |--   4 NESTED LOOPS

              |   |   |   ( Estim. Costs = 8 , Estim. #Rows = 10 )

              |   |   |   Estim. CPU-Costs = 65.756 Estim. IO-Costs = 8

              |   |   |

              |   |   |--   2 TABLE ACCESS BY INDEX ROWID EKPO

              |   |   |   |   ( Estim. Costs = 2 , Estim. #Rows = 10 )

              |   |   |   |   Estim. CPU-Costs = 20.063 Estim. IO-Costs = 2

              |   |   |   |

              |   |   |   ------1 INDEX RANGE SCAN EKPO~1

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

              |   |   |           Search Columns: 5

              |   |   |           Estim. CPU-Costs = 4.846 Estim. IO-Costs = 1

              |   |   |           Access Predicates Filter Predicates

              |   |   |

              |   |   ------3 INDEX RANGE SCAN EKET~0

              |   |           Search Columns: 3

              |   |           Estim. CPU-Costs = 3.069 Estim. IO-Costs = 0

              |   |           Access Predicates

              |   |

              |   ------5 TABLE ACCESS BY INDEX ROWID EKET

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

              |           Estim. CPU-Costs = 4.569 Estim. IO-Costs = 1

              |           Filter Predicates

              |

              ---   12 VIEW index$_join$_004

                  |    ( Estim. Costs = 1.174 , Estim. #Rows = 61.760 )

                  |    Estim. CPU-Costs = 179.992.784 Estim. IO-Costs = 1.162

                  |

                  ---   11 HASH JOIN

                      |    Memory Used KB: 16.027.648

                      |    Access Predicates

                      |

                      |--   9 HASH JOIN

                      |   |   Memory Used KB: 13.181.952

                      |   |   Access Predicates

                      |   |

                      |   |-----7 INDEX FAST FULL SCAN EKKO~0

                      |   |       ( Estim. Costs = 205 , Estim. #Rows = 61.760 )

                      |   |       Estim. CPU-Costs = 10.896.674 Estim. IO-Costs = 204

                      |   ------8 INDEX FAST FULL SCAN EKKO~1

                      |           ( Estim. Costs = 360 , Estim. #Rows = 61.760 )

                      |           Estim. CPU-Costs = 11.922.308 Estim. IO-Costs = 360

                      |

                      ------10 INDEX FAST FULL SCAN EKKO~D

                               ( Estim. Costs = 211 , Estim. #Rows = 61.760 )

                               Estim. CPU-Costs = 10.942.963 Estim. IO-Costs = 211

                               Filter Predicates

The problem is on VIEW index$_join$_004 executed in Production, instead in Test it executes

TABLE ACCESS BY INDEX ROWID EKKO

                  |   ( Estim. Costs = 358 , Estim. #Rows = 55.290 )

                  |   Estim. CPU-Costs = 9.087.005 Estim. IO-Costs = 357

                  |

                  ------7 INDEX SKIP SCAN EKKO~W

                          ( Estim. Costs = 55 , Estim. #Rows = 55.290 )

                          Search Columns: 1

                          Estim. CPU-Costs = 2.609.239 Estim. IO-Costs = 55

                          Access Predicates Filter Predicates

that seems faster.

The database statistics post upgrade were calculated with brconnect -u / -c -f stats -t all -f collect -p 4


ZMMODA is a view on the tables EKPO, EKET,EKKO:

EKPOMANDT=EKETMANDT
EKPOEBELN=EKETEBELN
EKPOEBELP=EKETEBELP
EKPOEBELN=EKKOEBELN

I tried even to delete from DB20 and recreate the statistics with no results.

I apllied this _fix_control:

ALTER SYSTEM SET "_FIX_CONTROL"=

'5099019:ON',

'5705630:ON',

'6055658:OFF',

'6120483:OFF',

'6399597:ON',

'6430500:ON',

'6440977:ON',

'6626018:ON',

'6972291:ON',

'7168184:OFF',

'7658097:ON',

'8937971:ON',

'9196440:ON',

'9495669:ON',

'13077335:ON',

'13627489:ON',

'14255600:ON',

'14595273:ON',

'18405517:2',

'20355502:8',

'14846352:OFF'

COMMENT='SAP RECOMMENDED SETTING FOR 12.1.0.2'

  SCOPE=SPFILE;


in particular

'14846352:OFF' mentioned in Oss note "2254070 - 12c: Bad Performance after upgrade from 11.2.0.x to 12.1.0.2 doing a simple join"

but the problem persist.

What could I do ?

Thanks for your help.

Mario Bisonti


Accepted Solutions (1)

Accepted Solutions (1)

stefan_koehler
Active Contributor
0 Kudos

Hi Mario,

> What could I do ?

You need to provide much more data than just the plain execution plan - especially in this case as an index-join (index$_join$_004) is used in only one case which looks like there are different query transformations going on (or different structures between test and prod). In addition you can not make any conclusion about the performance loss just by looking at an "EXPLAIN PLAN" as you don't know if this is the real time-consuming part of the execution plan.

As in the past: Run the SQL in both systems. Capture all the needed diagnostic data with SQLd360 immediately after the run. Provide the ZIP file

Regards

Stefan

Answers (2)

Answers (2)

mario_bisonti2
Participant
0 Kudos

Hallo.

Adding the fiel ETENR of EKET table to the viewn, the report was become faster !

SQL Statement

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

SELECT

  *

FROM

  "ZMMODA2"

WHERE

  "MANDT"=:A0 AND "MATNR"=:A1 AND "EINDT" BETWEEN :A2 AND :A3 AND "WERKS"=:A4

ORDER BY

  "MANDT","EBELN","EBELP"

Execution Plan

Explain from gv$sql_plan: Address: 000000050E9DB3E8 Hash_value:  1297707801 Child_number:  0 Instance_ID: 1

Sql_id: dkdu0pt6pkwst  Parse Timestamp: 20160112   02:25:15

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

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

  |

  ---   11 SORT ORDER BY

      |    ( Estim. Costs = 5 , Estim. #Rows = 2 )

      |    Estim. CPU-Costs = 14.939.275 Estim. IO-Costs = 4

      |

      ---   10 FILTER

          |    Filter Predicates

          |

          ---   9 NESTED LOOPS

              |   ( Estim. Costs = 4 , Estim. #Rows = 2 )

              |   Estim. CPU-Costs = 35.871 Estim. IO-Costs = 4

              |

              |--   7 NESTED LOOPS

              |   |   ( Estim. Costs = 4 , Estim. #Rows = 2 )

              |   |   Estim. CPU-Costs = 35.871 Estim. IO-Costs = 4

              |   |

              |   |--   5 NESTED LOOPS

              |   |   |   ( Estim. Costs = 4 , Estim. #Rows = 2 )

              |   |   |   Estim. CPU-Costs = 29.218 Estim. IO-Costs = 4

              |   |   |

              |   |   |--   2 TABLE ACCESS BY INDEX ROWID EKPO

              |   |   |   |   ( Estim. Costs = 2 , Estim. #Rows = 2 )

              |   |   |   |   Estim. CPU-Costs = 20.079 Estim. IO-Costs = 2

              |   |   |   |

              |   |   |   ------1 INDEX RANGE SCAN EKPO~1

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

              |   |   |           Search Columns: 5

              |   |   |           Estim. CPU-Costs = 4.863 Estim. IO-Costs = 1

              |   |   |           Access Predicates Filter Predicates

              |   |   |

              |   |   ---   4 TABLE ACCESS BY INDEX ROWID EKET

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

              |   |       |   Estim. CPU-Costs = 4.569 Estim. IO-Costs = 1

              |   |       |   Filter Predicates

              |   |       |

              |   |       ------3 INDEX RANGE SCAN EKET~0

              |   |               Search Columns: 3

              |   |               Estim. CPU-Costs = 3.069 Estim. IO-Costs = 0

              |   |               Access Predicates

              |   |

              |   ------6 INDEX UNIQUE SCAN EKKO~0

              |           Search Columns: 2

              |           Estim. CPU-Costs = 1.814 Estim. IO-Costs = 0

              |           Access Predicates

              |

              ------8 TABLE ACCESS BY INDEX ROWID EKKO

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

                      Filter Predicates

Former Member
0 Kudos

This is more for information:

we upgraded ECC EHP4 system to oracle 12c and ME2Xn transactions started dumping intermittently during first run. During second run it did not give any error.

On debugging we found that although sql statements were same, data loaded into internal table pot was not sorted. Hence binary search was failing.

Issue was resolved with SAP note 1534453.

JamesZ
Advisor
Advisor
0 Kudos

Hi Mario ,

did you also verify all the parameters as mentioned in SAP note 1888485 particular for 12c?

Best regards,
James