cancel
Showing results for 
Search instead for 
Did you mean: 

Performance issue on 1 SQL request

former_member204746
Active Contributor
0 Kudos

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.

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

> 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

former_member204746
Active Contributor
0 Kudos

thanks for the reply.

Yes, data is not identical. PRD has more data than QAS. I was just surprised about the "INDEX SKIP SCAN EKPO~Z02" on PRD.

1 and 2. Both systems are using the exact same Oracle version (10.2.0.4) with minipatch 8. We are on Windows and using SAP ECC 6.0. So, both use the same CBO calculation.

3. ZBW_VIEW_EKPO definition:

MANDT CLNT 3 Client

EBELN CHAR 10 Purchasing Document Number

EBELP NUMC 5 Item Number of Purchasing Document

SAISO CHAR 4 Season Category

SAISJ CHAR 4 Season Year

AEDAT DATS 8 Purchasing Document Item Change Date

AUREL CHAR 1 Relevant to Allocation Table

LOEKZ CHAR 1 Deletion Indicator in Purchasing Document

INCO2 CHAR 28 Incoterms (Part 2)

ZZTRANSPORT CHAR 8 Article freight group

PRODA DATS 8 Invoice: Date of invoice document for import processing

ZZPRDHA CHAR 18 PO Product hierarchy

ZZMEM_DATE DATS 8 Mem Date used in PO

KDATE DATS 8 End of Validity Period

ZZHERKL CHAR 3 Country of origin of the material

KNUMV CHAR 10 Number of the document condition

KTOKK CHAR 4 Vendor account group

table/join conditions:

EKPO MANDT = EKKO MANDT

EKPO EBELN = EKKO EBELN

LFA1 LIFNR = EKKO LIFNR

LFA1 MANDT = EKKO MANDT

EIKP EXNUM = EKKO EXNUM

EIKP MANDT = EKKO MANDT

Selections/conditions:

EKPO ATTYP EQ '02' AND

LFA1 KTOKK EQ 'ZVII'

You say: 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 would like to know how to recall these stats and test this out.

Thanks.

lbreddemann
Active Contributor
0 Kudos

Hi Eric,

you may want to have a look into the Oracle Documentation [ Restoring Previous Versions of Statistics|http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i41814].

1. Find out for if the old statistics are still available:


select dbms_stats.GET_STATS_HISTORY_AVAILABILITY() from dual;

2. If the statistics are still there (which is likely to be not the case here, as the default retention is set to 31 days) **, you may use


begin
   dbms_stats.restore_table_stats (
     ownname => 'SAP<SID>',
     tabname => '<table_name>',
     as_of_timestamp => to_timestamp ('15.09.2008', 'DD.MM.YYYY'),
     force => true,
     no_invalidate => false
     );
end;

for each of the involved tables.

The procedure also restores the index statistics - so you don't have to care about them.

Anyhow, it would still be a better idea to try to figure out, why the plan changes to a bad one when the statistics are up to date. Maybe a bigger sample size would help here.

I propose you open a support message for this and have the CBO trace analysed.

I know some primary supporters that are capable of that ...

**)

you may double-check this for your system via


select dbms_stats.GET_STATS_HISTORY_RETENTION() from dual;

regards,

Lars

former_member204746
Active Contributor
0 Kudos

Thanks again Lars for your great information and help.

I have opened a case at SAP. I will keep this forum posted with findings.

I will probably copy my QAS stats to my TST system and see how it behaves. (TST is as slow as PRD).

former_member204746
Active Contributor
0 Kudos

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.