on 01-05-2016 1:34 PM
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:
EKPO | MANDT | = | EKET | MANDT |
EKPO | EBELN | = | EKET | EBELN |
EKPO | EBELP | = | EKET | EBELP |
EKPO | EBELN | = | EKKO | EBELN |
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
Hi Mario ,
did you also verify all the parameters as mentioned in SAP note 1888485 particular for 12c?
Best regards,
James
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.