Hi,
We have a performance problem. We have 2 systems. PRD and QAS (QAS is a copy of PRD as of September 2nd)
SQL request is identical.
table structures is identical.
indexes are identical.
views are identical
DB stats have all been recalculated on both systems
initSID.ora values are almost identical. only memory related parameters (and SID) are different.
Obviously, data is different
For you info, view ZBW_VIEW_EKPO fetched its info from tables EIKP, LFA1, EKKO and EKPO.
Starting on September 15th, a query that used to take 10 minutes started taking over 120 minutes.
I compared explain plans on both system and they are really different:
SQL request:
SELECT "MANDT" , "EBELN" , "EBELP" , "SAISO" , "SAISJ" , "AEDAT" , "AUREL" , "LOEKZ" , "INCO2" , "ZZTRANSPORT" , "PRODA" , "ZZPRDHA" , "ZZMEM_DATE" , "KDATE" , "ZZHERKL" , "KNUMV" , "KTOKK" FROM "ZBW_VIEW_EKPO" WHERE "MANDT" = :A0#
Explain plan for PRD:
SELECT STATEMENT ( Estimated Costs = 300,452 , Estimated #Rows = 0 )
8 HASH JOIN
( Estim. Costs = 300,451 , Estim. #Rows = 4,592,525 )
Estim. CPU-Costs = 9,619,870,571 Estim. IO-Costs = 299,921
Access Predicates
1 TABLE ACCESS FULL EIKP
( Estim. Costs = 353 , Estim. #Rows = 54,830 )
Estim. CPU-Costs = 49,504,995 Estim. IO-Costs = 350
Filter Predicates
7 HASH JOIN
( Estim. Costs = 300,072 , Estim. #Rows = 4,592,525 )
Estim. CPU-Costs = 9,093,820,218 Estim. IO-Costs = 299,571
Access Predicates
2 TABLE ACCESS FULL LFA1
( Estim. Costs = 63 , Estim. #Rows = 812 )
Estim. CPU-Costs = 7,478,316 Estim. IO-Costs = 63
Filter Predicates
6 HASH JOIN
( Estim. Costs = 299,983 , Estim. #Rows = 4,592,525 )
Estim. CPU-Costs = 8,617,899,244 Estim. IO-Costs = 299,508
Access Predicates
3 TABLE ACCESS FULL EKKO
( Estim. Costs = 2,209 , Estim. #Rows = 271,200 )
Estim. CPU-Costs = 561,938,609 Estim. IO-Costs = 2,178
Filter Predicates
5 TABLE ACCESS BY INDEX ROWID EKPO
( Estim. Costs = 290,522 , Estim. #Rows = 4,592,525 )
Estim. CPU-Costs = 6,913,020,784 Estim. IO-Costs = 290,141
4 INDEX SKIP SCAN EKPO~Z02
( Estim. Costs = 5,144 , Estim. #Rows = 4,592,525 )
Search Columns: 2
Estim. CPU-Costs = 789,224,817 Estim. IO-Costs = 5,101
Access Predicates Filter Predicates
Explain plan for QAS:
SELECT STATEMENT ( Estimated Costs = 263,249 , Estimated #Rows = 13,842,540 )
7 HASH JOIN
( Estim. Costs = 263,249 , Estim. #Rows = 13,842,540 )
Estim. CPU-Costs = 59,041,893,935 Estim. IO-Costs = 260,190
Access Predicates
1 TABLE ACCESS FULL LFA1
( Estim. Costs = 63 , Estim. #Rows = 812 )
Estim. CPU-Costs = 7,478,316 Estim. IO-Costs = 63
Filter Predicates
6 HASH JOIN
( Estim. Costs = 263,113 , Estim. #Rows = 13,842,540 )
Estim. CPU-Costs = 57,640,387,953 Estim. IO-Costs = 260,127
Access Predicates
4 HASH JOIN
( Estim. Costs = 2,127 , Estim. #Rows = 194,660 )
Estim. CPU-Costs = 513,706,489 Estim. IO-Costs = 2,100
Access Predicates
2 TABLE ACCESS FULL EIKP
( Estim. Costs = 351 , Estim. #Rows = 54,830 )
Estim. CPU-Costs = 49,504,995 Estim. IO-Costs = 348
Filter Predicates
3 TABLE ACCESS FULL EKKO
( Estim. Costs = 1,534 , Estim. #Rows = 194,660 )
Estim. CPU-Costs = 401,526,622 Estim. IO-Costs = 1,513
Filter Predicates
5 TABLE ACCESS FULL EKPO
( Estim. Costs = 255,339 , Estim. #Rows = 3,631,800 )
Estim. CPU-Costs = 55,204,047,516 Estim. IO-Costs = 252,479
Filter Predicates
One more bit of information, PRD was copied to TST about a month ago and this one is also slow.
I did almost anything I could think of.