Perfomance Issue on T-Code CJ20N

[Former Member]

Hi All,

We have some problem on CJ20N. It costs 7-8 mins to get in to Project Builder screen. SQL trace shows the slowest selection:

SELECT

  T_00."AUFNR", T_00."AUFPL", T_01."PSPEL" "PROJN", T_01."KTEXT"

FROM

  "AFKO" T_00 INNER JOIN "AUFK" T_01 ON T_01."MANDT"=:A0 AND T_00."AUFNR"=T_01."AUFNR"

WHERE

  T_00."MANDT"=:A1 AND T_00."PRONR"=:A2 AND T_01."AUTYP"=:A3

Index usage of this statement:

SELECT STATEMENT ( Estimated Costs = 3 , Estimated #Rows = 342 )

       5 HASH JOIN

         ( Estim. Costs = 2 , Estim. #Rows = 342 )

         Estim. CPU-Costs = 14.406.898 Estim. IO-Costs = 2

         Access Predicates

           2 TABLE ACCESS BY INDEX ROWID AFKO

             ( Estim. Costs = 1 , Estim. #Rows = 357 )

             Estim. CPU-Costs = 11.666 Estim. IO-Costs = 1

             Filter Predicates

               1 INDEX RANGE SCAN AFKO~0

                 Search Columns: 1

                 Estim. CPU-Costs = 3.249 Estim. IO-Costs = 0

                 Access Predicates

           4 TABLE ACCESS BY INDEX ROWID AUFK

             ( Estim. Costs = 1 , Estim. #Rows = 342 )

             Estim. CPU-Costs = 4.591 Estim. IO-Costs = 1

               3 INDEX RANGE SCAN AUFK~B

                 Search Columns: 2

                 Estim. CPU-Costs = 2.889 Estim. IO-Costs = 0

                 Access Predicates

Our Index info is below for tables AUFK and AFKO:

AUFK:

0          X      Primary index

A                 Cost accounting-/allocation group/order type

B                 Order type/plant

C                 Request type/controlling area

D                 Index for matchcode ID PRPO-D

E                 Production process/product cost collector

AFKO:

0          X      Primary index

1                 Relationship operation - order header

2                 Index for project definition

3                 Index for subnetworks

4

5                 Index MAUFNR

6                 Index LEAD_AUFNR

D                 Index for matchcode ID ORDE_D

F                 Index for matchcode ID ORDE_F

P                 Task list number

There is a OSS Note: 484638 but our oracle version is 11.2.0.3.0 so i don't think that the note is suitable for us. So i don't know what to do next. Please help me.

Thanks in Advance for any clarification.

Erdem

[former_member207186]

Hi Erdem,

You can also check the statistics as per note 1020260 - "Delivery of Oracle statistics (Oracle >= 10g)" especially for tables AUFK and AFKO and also ensure that the statistics are collected as per note 838725 - "Oracle dictionary statistics and system statistics".

Regards,
Bíborka

[stefan_koehler]

Hi Erdem,

please provide the runtime statistics of this query (e.g. with Real Time SQL Monitoring as this query runs so long) and the detailed DBMS_XPLAN output.

Explanation can be found in my blog posts here:

Regards

Stefan