Skip to Content

Performance issue on 1 SQL request

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.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Nov 25, 2008 at 07:18 PM

    > 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

    Ok, so you say: the parameters are different, the data is different and the statistics are different.

    I'm surprised that you still expect the plans to be the same... 😊

    > For you info, view ZBW_VIEW_EKPO fetched its info from tables EIKP, LFA1, EKKO and EKPO.

    We will need to see the view definition !

    > Starting on September 15th, a query that used to take 10 minutes started taking over 120 minutes.

    Oh - Sep. 15th - that explains it ... just kiddin'.

    Ok, so it appears to be obvious that from that day on, the execution plan for the query was changed.

    If you're on Oracle 10g you may look it up again and also recall the CBO stats that had been used back then.

    > 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#

    Ok - basically you fetch all rows from this view as MANDT is usually not a selection criteria at all.

    > 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

    Ok, we've no restriction to the data, so Oracle chooses the access methods it thinks are best for large volumes of data - Full table scans and HASH JOINS. The index skip scan is quite odd - maybe this is due to one of the join conditions.

    > 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

    Ok, we see significantly different table sizes here, but at least this second plan leaves out the superfluous Index Skip Scan.

    How to move on from here?

    1. Check whether you've installed all the current patches. Not all bugs that are in the system are hit all the time, so it may very well be that after new CBO stats were calculated you just begin to hit one of it.

    2. Make sure that all parameter recommendations are implemented on the systems. This is crucial for the CBO.

    3. Provide a description of the Indexes and the view definition.

    The easiest would be: perform an Oracle CBO trace and provide a download link to it.

    regards,

    Lars

    Add comment
    10|10000 characters needed characters exceeded

    • SAP has not answered yet, but problem is fixed.

      1. I ran these scriptsw:

      exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');

      exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');

      @?/rdbms/admin/prvtbpw.plb

      @?/rdbms/admin/catdph.sql

      @?/rdbms/admin/catdpb.sql

      @?/rdbms/admin/prvtstat.plb

      2. I rebuilt indexes of necessary tables

      3. changed analysis method to CX in DB21 and recalculated statistics.

      I believe that step 3 fixed the problem.

      thanks Lars.