Sudden change in Oracle Undo Manegement behaviour

[Farid]

Hello,

We are running SAP ECC6 EhP5, Oracle 11.2.0.2, HP-UX 11.31 ia64

UNO_RETENTION = 21600

The size of the tablespace PSAPUNDO  is fixed (AUTOEXTENT=NO)

We have been running Oracle Undo Management features for year, without any problem ... until a few weeks ago.

We noticed a problem at the SAP level, with the sm12 locks not being released, it was a problem with the SAP enqueue server, and we have set the SAP parameter :

enque/deque_wait_answer = TRUE Wait for answer from enqueue server (synchronous)

We haven't done any other change, and certainly not changed any Oracle paramter

It solved our SAP sm12 problem , but now, after the ORacle/SAP restart we are observing a change in the ORacle Undo behaviour :

First :

After the restart, the tablespace PSAPUNDO is now constantly full, and extending the tablespace is of no effect.

I undersatnd that PSAPUNDO being full is not necesarily a problem, but in our case it is always full, we never had this before and the user activity has  not increased

We not frequently have severa loracle errors related to that tablespace : ora-1515,ora 30036, ora 1652 : jobs, dialog and update activities are cancelled

Second the Oracle views related to undo management do not work as they used to :

As you can see in the screenshot, the dba historical view of Undo management is no longer filled after the system restart.

As designed, there used to be one entry, generated every 10 minutes

There is now, one single entry in V$UNDOSTAT whic hshows a very low value for TUNED_AUTORETENTION : 4628 sec

We have checked ORacle paramters change history, absolutely no changes have been done.

We are really perplex :

What could cause the PSAPUNDO tablespace to be constantly full , all of a sudden , could it be the sap parameter enque/deque_wait_answer ?

What could cause the ORacle view V$UNDOSTAT to only keep one single entry ?

The size of the tablespace PSAPUNDO is fixed (AUTOEXTENT=NO), does that mean that the UNDO_RETENTION value is ignored ?

Thanks and Regards

[Former Member · Accepted solution]

Hi,

Could you check the TS usage from BRTOOLS?

I faced such issue on older versions where it was because of bug (PSAPUNDO always shows ~100% utilized) but in real time which is not.

Regards,

Nick Loy

[Former Member]

Hi,

Could you check the TS usage from BRTOOLS?

I faced such issue on older versions where it was because of bug (PSAPUNDO always shows ~100% utilized) but in real time which is not.

Regards,

Nick Loy

[Farid]

Hi Nick,

Brtools also indicate a 100% usage

List of tablespaces for extension

Pos.  Tablespace    Files/AuExt.   Total[KB]   Used[%]    Free[KB]  MaxSize[KB]

  1 - PSAPJRP         133/133    3511427072     95.75   149337216   3534569472

  2 - PSAPJRP700        4/4        26624000     95.43     1217024     49152000

  3 - PSAPJRPUSR        1/1         1433600     97.12       41280      2048000

  4 - PSAPTEMP          3/3        19442664     28.74    13855720     59392000

  5 - PSAPUNDO          3/3        51198464      0.04    51177984     51200000

  6 - SYSAUX            3/3         5529600     91.59      465088      9216000

  7 - SYSTEM            1/1         1228800     92.22       95552      3072000

[Former Member]

Hi Raoul,

It is showing only 0.04% used. 99.6% free space is still available.

Regards

Sudhir Sadhu

[Former Member]

Hi,

It is showing 0.04% utilized, so it's just a wrong indication at DB02 which is due to a known bug.

Try to update your BRTOOLS to latest release and check. Till the time you can consider the BRTOOLS output as the correct one.

Regards,

Nick Loy

[stefan_koehler]

Hi Raoul,

at first please forget the previously mentioned hints about the LOBs - it is absolutely misleading and just wrong. Out-Line stored LOB segments don't rely on UNDO segments/extents and its tablespace storage (except LOB locator), even if you have specified them with RETENTION. For more details - please check out this Oracle White Paper "LOB Performance Guidelines".

In-line LOBs are subject to normal chaining and row migration rules within Oracle. If you store a 3900 byte LOB in a row with 2K block sizethen the row will be chained across two or more blocks. Both REDO and UNDO are written for in-line LOBs as they are part of the normal rowdata. The CHUNK option does not affect in-line LOBs.

With out-of-line storage, UNDO is written only for the LOB locator and LOBINDEX changes. No UNDO is generated for chunks/pages in the LOBSEGMENT. Consistent Read is achieved by using page versions (seethe RETENTION or PCTVERSION options)

Consistent Reads on LOBs: RETENTION and PCTVERSION 
Consistent Read (CR) on LOBs uses a different mechanism than that used for other data blocks in Oracle. Older versions of the LOB are retained in the LOB segment and CR is used on the LOB index to access these older versions (for in-line LOBs which are stored in the table segment, the regular UNDO mechanism is used). There are two ways to control how long older versions are maintained.


RETENTION is a keyword in the LOB column definition. No value can be specified for RETENTION. The RETENTION value is implicit. If a LOB is created with database compatibility set to 9.2.0.0 or higher, undo_management=TRUE and PCTVERSION is not explicitly specified ,time-based retention is used. The LOB RETENTION value is always equal to the value of the UNDO_RETENTION database instance parameter.



Out-of-row LOB undo is maintained in the LOB segment. So the UNDO tablespace and undo retention is not associated with most LOB ORA-1555 issues. Instead the LOB column is created using either PCT_VERSION or RETENTION to manage how much space within blocks or time transpires before the LOB undo is overwritten

Secondly the V$UNDO_STAT "issue" is a known behavior, if you have disabled "automatic undo tuning" (parameter "_undo_autotune" = FALSE). Maybe you have accidentally activated that parameter by restarting the Oracle instance. Regarding the undo tablespace usage, we need more information about the undo segments/extents and its status.

Regards

Stefan

[Farid]

Thank you Stefan for your thorough answer,

I have joined a document detailing the undo segment status, as well as the PSAPUNDO status

segment status.txt.zip

[former_member188883]

Hi Raoul,

Could you also share parameter values for

_undo_autotune

_highthreshold_undoretention

Regards,

Deepak KOri

[stefan_koehler]

Hi Raoul,

thank you for that output - it looks like i assumed for fixed size undo tablespaces (autoextend = no). The (internal) allocation algorithm is different for such kind of undo tablespaces and works as designed. This is described in MOS note #413732.1 - which is also available right here.

For a fixed size UNDO tablespace (NO AUTOEXTEND), starting with 10.2, we provide max retention given the fixed undo space, which is set to a value based on the UNDO tablespace size. 
This means that even if the undo_retention is set to a number of seconds (900 default), the fixed UNDO tablespace supports a bigger undo_retention time interval (e.g: 36 hours), based on the tablespace size, thing that makes the undo extents to be UNEXPIRED. But this doesn't indicate that there are no available undo extents when a transaction will be run in the database, as the UNEXPIRED undo segments will be reused.

Regards

Stefan

[Farid]

Hello Stefan,

One thing I would like to add, I do not know if it makes any difference,

Tablespace has indeed a fixed size ( AUTOEXTEND:NO) but the undo datafiles have the status "Autoextensibles"

Thanks for the link, so according to Oracle literature , there is no need to need more space or to be concerned by the 100% full.

The problem is that in our case it is causing severe problems : Batch jobs cancelled, SAP update processes hanging, dialog processes timeout

Yesterday , the SAP MRP job has failed causing severe business disruption

We have increased again the size of the tabelspace PSAPUNDO, like we did repeatedly over the past weeks .. without any durable success.

And one thing that really makes me perplex, is why the sudden change of behaviour ? As you can see in my very first screenshot, the PSAPUNDO tablespace was working fine, before the change of SAP parameter enque/deque_wait_answer

I opened a sap customer call to ask if that parameter could have any impact on UNDO activity, but I am struggling with with the SAP support 1st level

[Farid]

Hello Deepak,

Here are the parameters :

NAME

VALUE

DESCRIPTION

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

_highthreshold_undoretention	4294967294	high threshold undo_retention in seconds
_undo_autotune	TRUE	enable auto tuning of undo_retention
undo_retention	21600	undo retention in seconds

Thanks and Regards

[former_member188883]

Hi Raoul,

The parameter

_undo_autotune

TRUE

looks like a culprit in this. As per SAP recommendation this value should be false.

Regards,

Deepak Kori

[stefan_koehler]

Hi Raoul,

now i am confused as your last statements are contradictory with the statements from your initial post.

Initial post: "The size of the tablespace PSAPUNDO is fixed"

Last statement: "Tablespace has indeed a fixed size (AUTOEXTEND:NO) but the undo datafiles have the status "Autoextensibles"

This combination is not possible. As previously posted the used algorithm changes between autoextend and fixed size undo tablespaces (and even changes when maxsize is greater than actual size - for more details check out MOS ID #1112431.1).

Initial post: "We not frequently have several oracle errors related to that tablespace : ora-1515,ora 30036, ora 1652 : jobs, dialog and update activities are cancelled"

Last statement: "The problem is that in our case it is causing severe problems : Batch jobs cancelled, SAP update processes hanging, dialog processes timeout"

Initially you have written that you don't have any of such issues and now you have ... i am a little bit confused

You can do further undo analysis (at the given time) with help of structure x$ktuxe ([K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry table) and PL/SQL procedure SCN_TO_TIMESTAMP and check which undo is commited (and not expired from a given period of time). Be aware that x$ktuxe will run through every undo segment header block (of each undo segment).

> And one thing that really makes me perplex, is why the sudden change of behaviour ?

There is also a known bug #9681444 (MOS ID #1112431.1), but the other way round - high undo retention time after instance restart when load was particular high right before bouncing the instance. However we would need access for further deep analysis - pretty hard to troubleshoot by forum posts.

> but I am struggling with with the SAP support 1st level

Regards

Stefan

[Farid]

Hi Stefan,

Yes I must admit, I can be quite confusing ....

HEre are some clarifications :

Initial post: "The size of the tablespace PSAPUNDO is fixed"

Actually I should have said "The size of the tablespace PSAPUNDO was fixed",

The DBA has changed the status of the datafiles yesterday to see if it would improve things, and I did not noticed it,

the Tablespace is now Autoextensibles and the datafiles have already used all the allocatable space

Initial post: "We not frequently have several oracle errors related to that tablespace : ora-1515,ora 30036, ora 1652 : jobs, dialog and update activities are cancelled"

I meant :

"We now frequently have several oracle errors related to that tablespace : ora-1515,ora 30036, ora 1652 : jobs, dialog and update activities are cancelled"

My excuse : I am French

[stefan_koehler]

Hi Raoul,

thank you for clarification.

Oracle may uses a different undo allocation algorithm (check previous posted blog post) after changing the data file attributes to autoextend on. Be aware if maxsize is currently greater than the actual size then the algorithm for calculating the tuned undo is based on free space again.

Depending on the used algorithm you may want to troubleshoot it further, but currently it is hard to point you in the right direction as we don't know the exact state of your database.

By the way you can also see that Oracle tries to steal unexpired extents "heavily" after restart only and that the transaction count went up very high (not quite sure if that is a result of the V$UNDO_STAT "issue" or if this is real, but you can verify this by generating several AWR reports and comparing the statistics).

Regards

Stefan

[former_member188883]

Hi Raoul,

Sorry to miss out UNO_RETENTION = 21600. This parameter value is on a higher end.

You may reduce the same to 900 and then monitor the system. SAP Default value is 900.

Hope this helps.

Regards,

Deepak Kori

[Farid]

Hi Deepak,

But according to sap note 600141 :

• Recommendation for the Undo retention time:

• 12h as the start value

• As an indicator, the longest running SAP ABAP report can be used, if this is
  known.

• You should not select an undo_retention parameter that is too small,
  otherwise, 'ORA-01555' will appear more regularly than in manual Undo Management
  mode.

[former_member188883]

Hi Raoul,

Yes as per SAP note we should have a higher value for UNDO_RETENTION.

Could you check whether following parameters are set

_undo_autotune

_highthreshold_undoretention

Regards,

Deepak Kori

[former_member188883]

Hi Raoul,

Can you check in DB02 -> Table Analysis , whether there are any large LOB segments ?

Regards,

Deepak Kori

[Farid]

Hello Deepak,

We have indeed a quite large LOBSEGMENT :

SQL> select table_name from dba_lobs where segment_name = 'SYS_LOB0000013311C00007$$';

TABLE_NAME
------------------------------
SOFFCONT1

Thank you