on 03-05-2013 5:12 PM
We received a couple of ORA-01555: snapshot too old errors in our Archiving process. Does SAP have a recommendation for the Oracle paremeter 'UNDO_RETENTION' or is it up to the customer to keep increasing to accomodate/allow long running archive queries?
Thanks
Kevin
Hello Kevin,
Could you indicate the the long running SQL query? Could you check the execution plan for it?
Btw, what ME version do you use?
Regards,
Alex.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
SELECT SHOP_ORDER_BO, LOCATION, ACTUAL_COMP_DATE,
TRANSFER_USER, ORIGINAL_TRANSFER_KEY, SFC, PREV_SITE, ORIGINAL_STATUS_BO, PRIORITY, QTY, RMA_MAX_TIMES_PROCESSED, CREATED_DATE_TIME,
QTY_MULT_PERFORMED, SN_DONE, LCC_BO, ITEM_BO, IMMEDIATE_ARCHIVE, SITE, QTY_SCRAPPED, QTY_DONE, MODIFIED_DATE_TIME, HANDLE,
TRANSFER_DATETIME, STATUS_BO FROM SFC WHERE SITE = 'XXXX' AND (ACTUAL_COMP_DATE < TO_DATE('2012-12-05 03:45:26','YYYY-MM-DD
HH24:MI:SS') OR IMMEDIATE_ARCHIVE = 'true')Mon Mar 04 23:34:32 2013
Explain Plan: TABLE ACCESS FULL TABLE VMWIP.SFC
Cost: 12,458 Bytes: 3,710,598 Cardinality: 22,353
------
ORA-01555 caused by SQL statement below (SQL ID:
ax5s4p3cs3gmb, Query Duration=4214 sec, SCN: 0x0000.d2a97fa5):
SELECT SHOP_ORDER_BO, LOCATION, ACTUAL_COMP_DATE,
TRANSFER_USER, ORIGINAL_TRANSFER_KEY, SFC, PREV_SITE, ORIGINAL_STATUS_BO,
PRIORITY, QTY, RMA_MAX_TIMES_PROCESSED, CREATED_DATE_TIME, QTY_MULT_PERFORMED,
SN_DONE, LCC_BO, ITEM_BO, IMMEDIATE_ARCHIVE, SITE, QTY_SCRAPPED, QTY_DONE, MODIFIED_DATE_TIME,
HANDLE, TRANSFER_DATETIME, STATUS_BO FROM SFC WHERE SITE = 'XXXX' AND
(ACTUAL_COMP_DATE < TO_DATE('2012-12-05 14:26:59','YYYY-MM-DD HH24:MI:SS')
OR IMMEDIATE_ARCHIVE = 'true')Tue Mar 05 10:36:01 2013
Explain Plan:
Table access full TABLE VMWIP.SFC
Cost: 12,458 Bytes: 5,156,458 Cardinality: 31,063
SAP ME version 5.1.2
Paremeter current set to 900 sec
Hi Kevin,
It boils down to the investigation of the performance problem. You may find the guidance in the corresponding chapter of the Troubleshooting guide here.
In short, I would suggest:
1. Checking indices as per installation scripts.
2. Checking histograms and re-gathering statistics (see Oracle-specific steps here).
3. And only after that would try to adjust something with DB parameters.
Best regards,
Alex.
User | Count |
---|---|
93 | |
11 | |
10 | |
6 | |
5 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.