Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Performance issues - query running slow with index usage and faster when full table access.

ssspApr 4 2018 — edited Apr 6 2018

Hi All,

SAP ECC application running on Oracle EE 12.1.0.2 on Linux

One of the job running for long time (around 9 hours) in ECQ database (Non Prod) and the same job completing in 2 min in Production (ECP) database. Background of this change was, as per SAP recommendations, below parameters have been changed in both ECQ and ECP databases. After changing these values, the said job started running long time in both ECP and ECQ databases. Immediately rolled back those changes to their original values. After rolling back of these values, Job completed in ECP database (Prod) as per regular timing i.e in 2 minutes, whereas it still running for long time in ECQ (Non Production) database.

parallel_max_servers integer 40

parallel_min_servers integer 0

Looking for Experts advice on this.

Below are more details with execution Plans.

ECP Database (Production)

SELECT T_00."RYEAR",T_00."DOCNR",T_00."RLDNR",T_00."RBUKRS",T_00."DOCLN"

,T_00."ACTIV",T_00."RMVCT",T_00."RTCUR",T_00."RUNIT",T_00."AWTYP",T_00."

RRCTY",T_00."RVERS",T_00."LOGSYS",T_00."RACCT",T_00."COST_ELEM",T_00."RC

NTR",T_00."PRCTR",T_00."RFAREA",T_00."KOKRS",T_00."SCNTR",T_00."PPRCTR",

T_00."SFAREA",T_00."RASSC",T_00."TSL",T_00."HSL",T_00."KSL",T_00."OSL",T

_00."MSL",T_00."WSL",T_00."DRCRK",T_00."POPER",T_00."RWCUR",T_00."GJAHR"

,T_00."BUDAT",T_00."BELNR",T_00."BUZEI",T_00."BSCHL",T_00."BSTAT",T_00."

TIMESTAMP",T_01."BUKRS",T_01."BLART",T_01."BLDAT",T_01."MONAT",T_01."CPU

DT",T_01."XBLNR",T_01."WAERS",T_01."GLVOR",T_01."AWKEY",T_01."FIKRS",T_0

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1."HWAER",T_01."HWAE2",T_01."HWAE3",T_01."AWSYS",T_01."LDGRP",T_01."KURS

F" FROM "FAGLFLEXA" T_00 INNER JOIN "BKPF" T_01 ON

T_01."BUKRS"=T_00."RBUKRS" AND T_01."BELNR"=T_00."BELNR" AND

T_01."GJAHR"=T_00."GJAHR" AND T_01."MANDT"=T_00."RCLNT" WHERE

T_00."RLDNR"=:A0 AND T_00."TIMESTAMP" BETWEEN :A1 AND :A2 AND

T_00."RCLNT"=:A3 AND T_00."TIMESTAMP" BETWEEN :A4 AND :A5 AND

T_01."MANDT"=:A6

Plan hash value: 2141571980

-------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 722K(100)| |

| 1 | FILTER | | | | | |

| 2 | NESTED LOOPS | | 31063 | 9252K| 722K (2)| 00:00:29 |

| 3 | NESTED LOOPS | | 31063 | 9252K| 722K (2)| 00:00:29 |

| 4 | TABLE ACCESS FULL | FAGLFLEXA | 31063 | 6188K| 685K (2)| 00:00:27 |

| 5 | INDEX UNIQUE SCAN | BKPF~0 | 1 | | 1 (0)| 00:00:01 |

| 6 | TABLE ACCESS BY INDEX ROWID| BKPF | 1 | 101 | 2 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

ECQ database (Non Production)

SQL_ID 59j7ushnct74x

--------------------