Archiving - ORA-01555 error

[Former Member]

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

[alexander_teslyuk]

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.

[Former Member]

1. ORA-01555 caused by SQL statement below
   (SQL ID: 75h0t260nj66t, Query Duration=3005 sec, SCN: 0x0000.d26b4717):

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

[alexander_teslyuk]

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.