Hi All,
OS - HP-UX (11.31)
DB - 11g Enterprise Edition 11.2.0.4.0 (64bit)
I have been advised by SAP to convert one of our larger tables from HEAP to IOT in order to improve performance.
However, whilst testing on a non-prod copy of the database, I am continually hitting an ORA-1555 Snapshot too old error.
Alert log extract...
Tue Jun 07 20:23:51 2016
ORA-01555 caused by SQL statement below (SQL ID: 1066wvujatmfx, Query Duration=32726 sec, SCN: 0x001b.3df57cfd):
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "SAPJRP"."JEST#$"("MANDT","OBJNR","STAT","INACT","CHGNR") SEL
ECT "JEST"."MANDT","JEST"."OBJNR","JEST"."STAT","JEST"."INACT","JEST"."CHGNR" FROM "SAPJRP"."JEST" "JEST"
Now I thought I understood what this implies ie. the rebuild is attempting to get a read consistent image of the data from when the process began and it has now attempted to read a database block which has changed during that time. The block in the undo tablespace which stored the original value has subsequently been overwritten by another process etc etc. All that I am happy with and have no problem.
My query is how/why the undo block has been overwritten? You can see from the alert log error that the query ran for just over 9Hrs (Duration=32726 sec) yet my database undo_retention parameter is set to 30Hrs (108000 sec). UNDO parameters as follows...
NAME | VALUE
---------------------------------------- | ---------------------
undo_management | AUTO
undo_tablespace | PSAPUNDO
undo_retention | 108000
Again I understand that this is not guaranteed and can only be honored if the current undo tablespace has enough space. My UNDO tablespace is sized as follows...
Tablespace | Files | Max Size (Mb) | Alloc (Mb) | Free (Mb) | Used (Mb) | % Used | % Max
------------------------------ | ---------- | ------------- | ------------ | ------------ | ------------ | ------- | -------
PSAPUNDO | 3 | 90,000 | 75,112 | 11,464 | 63,648 | 84.74 | 70.72
So you can see that it is currently 75Gb with capacity to grow upto 90Gb and yet at the time of the error there is still over 11Gb free!
Why has the old image of the data been lost when the undo_retention has not been reached, the tablespace still has free space available, and it has the option to extend? Furthermore, the filesystem in which the UNDO datafiles exist has plenty of space should an extension have been attempted.
This seems to contradict the 11gR2 Documentation which states...
- For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information. The UNDOTBS1 tablespace that is automatically created by DBCA is auto-extending.
Am I missing something obvious or is my understanding flawed in some way?
As always, thanks in advance for any help.
GT