Hi,
One of our jobs that executes a pl/sql procedure used to run for ~ 50 minutes has been runnin slpow for past 4 months and now it has reached a stage where takes almost 7 hours to complete.
When I checked during the runtime I got below sql which is a part of the job code.
SELECT * FROM OD_AR_INV WHERE CUST_B_ACCT_NBR = :B4 AND SOU_S_NAME = :B3 AND CUR_IND = 'Y' AND R_DUE_DATE_CAL_IND = 'N' AND TRANS_DATE >= :B2 AND T_DATE <= :B1 AND F_P_IND = 'N' FOR UPDATE;
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 120 | 375 | | |
| 1 | FOR UPDATE | | | | | | |
| 2 | FILTER | | | | | | |
| 3 | PARTITION RANGE ITERATOR | | | | | KEY | KEY |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID | OD_AR_INV | 1 | 120 | 375 | KEY | KEY |
| 5 | INDEX RANGE SCAN | OD_AR_INV_IE1 | 15 | | 361 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note: cpu costing is off, PLAN_TABLE' is old version
13 rows selected.
The table OD_AR_INV is partitioned on T_DATE and has composite index OD_AR_INV_IE1 on SOU_S_NAME,CUR_IND,R_DUE_DATE_CAL_IND,CUST_B_ACCT_NBR.
All the index partitions have last_analyzed column dating to 2 days back.
Oracle version is 9.2.0.6.0
Please let me know how I can improve this job execution time.The same job has been running without any issues for many days, not sure what could be causing the slowness suddenly?