Skip to Content
0
Nov 25, 2008 at 06:14 PM

Performance issue on 1 SQL request

48 Views

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.