This forum is currently locked. You can't register or post questions at this time. (read more)

Undo utilization is very high

All posts relating to Oracle database administration.

Moderator: Tim...

sivakumarocp
Advisor
Posts: 260
Joined: Sat Jun 27, 2009 3:53 am
Location: India

Undo utilization is very high

Postby sivakumarocp » Wed Aug 20, 2014 1:11 pm

Hi Tim,

One of my production DB, we are continuously getting UNDO tablespace alert saying its utilization is more.
When I check the undo status for the Undo tablesspace its very low also it has more number of UNEXPIRED undo segments.

when I check the session details which is using undo segments its very very low but not sure why does more number of UNEXPIRED undo present on the Tablespace.

if i reduce the UNDO_RETENTION value will it solve the problem.

Code: Select all

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_tablespace                      string      UNDOTBS1
SQL>


Code: Select all

Tablespace Name   Allocated       Used       Free      Space
                    (in MB)    (in MB)    (in MB)   Avail(%)
---------------- ---------- ---------- ---------- ----------
UNDOTBS1             103424     103392         32        .03


STATUS       SIZE_MB     Used_%
--------- ---------- ----------
ACTIVE          1946          2
EXPIRED        24985         24
UNEXPIRED      75859         73

 Undo Name                     Tablspace   Init / Next Extents     Min / Max Extents       Status              Bytes Extents Shrinks   Wraps   Opt. Size
 ----------------------------- ----------- ----------------------- ----------------------- -------- ---------------- ------- ------- ------- -----------
 SYS.SYSTEM                    SYSTEM      114,688 / 57,344        1 / 32,765              ONLINE            393,216       6       0       2
 PUBLIC._SYSSMU10_3983090395$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        714,211,328      48   2,445  34,697
 PUBLIC._SYSSMU11_982344328$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        503,447,552      53   3,329  47,508
 PUBLIC._SYSSMU12_2769091560$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        581,042,176      75   2,836  40,036
 PUBLIC._SYSSMU13_2268716754$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        214,040,576      22   3,349  45,699
 PUBLIC._SYSSMU14_4082363426$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        753,008,640     114   3,271  51,891
 PUBLIC._SYSSMU15_2041658691$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        222,429,184      24   3,342  48,037
 PUBLIC._SYSSMU16_2990239116$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,969,356,800     144   3,620  51,011
 PUBLIC._SYSSMU17_3325880312$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        190,971,904      63   3,442  47,305
 PUBLIC._SYSSMU18_2915251874$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      2,954,166,272     160   3,277  45,725
 PUBLIC._SYSSMU19_1861331324$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      4,319,215,616     175   3,684  50,822
 PUBLIC._SYSSMU1_1288450692$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        610,402,304      72   4,168  58,319
 PUBLIC._SYSSMU20_3840965054$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      8,978,038,784     438   3,925  51,383
 PUBLIC._SYSSMU21_523813872$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,559,363,584      87   4,528  56,888
 PUBLIC._SYSSMU22_1959425383$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,379,008,512      77   3,926  51,155
 PUBLIC._SYSSMU23_1303476429$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,540,489,216     126   3,177  37,484
 PUBLIC._SYSSMU24_718870782$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      2,637,299,712     190   4,726  61,653
 PUBLIC._SYSSMU25_652312396$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      3,101,294,592     156   1,830  23,218
 PUBLIC._SYSSMU26_2857861751$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      4,561,436,672     280   5,505  71,504
 PUBLIC._SYSSMU28_2668654964$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      2,489,450,496     107   4,700  58,582
 PUBLIC._SYSSMU29_2648625515$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      6,033,637,376     306   5,334  71,560
 PUBLIC._SYSSMU2_4021543382$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        372,375,552      21   3,272  43,676
 PUBLIC._SYSSMU30_2666085032$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,168,244,736      72   4,424  59,645
 PUBLIC._SYSSMU31_3718898524$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        743,571,456      44   1,641  21,322
 PUBLIC._SYSSMU32_2391772968$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      2,212,626,432     115   2,008  25,833
 PUBLIC._SYSSMU33_2972913820$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      2,079,457,280     114   2,192  25,598
 PUBLIC._SYSSMU34_1419791009$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        946,995,200      36   1,376  15,994
 PUBLIC._SYSSMU35_2471118694$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,642,070,016     217       0     216
 PUBLIC._SYSSMU36_1565803671$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        931,266,560      98   1,846  22,639
 PUBLIC._SYSSMU37_3210426486$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      2,914,123,776     261   1,456  17,787
 PUBLIC._SYSSMU38_401633365$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        488,767,488      52   1,483  19,044
 PUBLIC._SYSSMU39_4082867807$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,996,292,096     152   1,322  15,896
 PUBLIC._SYSSMU3_4057585512$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE     14,757,724,160    1680   3,386  45,194
 PUBLIC._SYSSMU40_3488265639$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        585,236,480      68   1,493  18,016
 PUBLIC._SYSSMU41_1857622470$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        658,636,800      34   1,359  16,846
 PUBLIC._SYSSMU43_1023158505$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      4,420,993,024     253     937  14,115
 PUBLIC._SYSSMU44_2186339447$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      2,973,892,608     144     303   3,650
 PUBLIC._SYSSMU45_3185457271$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,777,467,392     119     246   3,117
 PUBLIC._SYSSMU46_3321286961$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        462,553,088      81     925  11,735
 PUBLIC._SYSSMU47_1309988392$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        240,254,976      21     882  12,739
 PUBLIC._SYSSMU48_3873826747$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        349,306,880      36   1,233  21,539
 PUBLIC._SYSSMU4_1763017552$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,471,283,200     135   4,189  58,014
 PUBLIC._SYSSMU50_2695958520$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        459,407,360      32     200   2,755
 PUBLIC._SYSSMU51_3061621425$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        593,625,088      67     838  12,105
 PUBLIC._SYSSMU52_442638424$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        586,285,056     110   1,240  17,967
 PUBLIC._SYSSMU53_2881357497$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,776,418,816     169   1,019  15,264
 PUBLIC._SYSSMU54_1824306645$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,939,996,672     185     848  14,202
 PUBLIC._SYSSMU55_2066530649$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        609,353,728      55     955  12,538
 PUBLIC._SYSSMU56_2938325199$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        813,826,048      37     873  11,476
 PUBLIC._SYSSMU57_2471426637$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      2,087,845,888     162     222   2,766
 PUBLIC._SYSSMU58_3963210890$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      2,593,259,520     198     970  13,267
 PUBLIC._SYSSMU59_4047650276$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        314,703,872      45     945  12,320
 PUBLIC._SYSSMU5_2512967983$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        226,623,488      29   2,940  43,607
 PUBLIC._SYSSMU60_3238781853$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        633,470,976      77   1,001  13,164
 PUBLIC._SYSSMU61_20488456$    UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,423,966,208     228       0     227
 PUBLIC._SYSSMU62_1743134731$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        150,994,944      89       0      88
 PUBLIC._SYSSMU63_1513162156$  UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,583,349,760     241       0     240
 PUBLIC._SYSSMU6_4246406575$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE      1,503,789,056     143   3,190  43,390
 PUBLIC._SYSSMU7_1020338033$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        724,697,088      96   3,364  46,872
 PUBLIC._SYSSMU8_2682556948$   UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        899,809,280      88   3,846  54,673
 PUBLIC._SYSSMU9_832557068$    UNDOTBS1    131,072 / 65,536        2 / 32,765              ONLINE        889,323,520      55   4,272  58,894
*********                                                                                                    ----------------
Total:                                                                                                        108,316,590,080


Undo Name                   SID Serial ID Status    Oracle Use O/S User   Machine              Session Program       LOGON_TIME
----------------------- ------- --------- --------- ---------- ---------- -------------------- --------------------- ---------------------
_SYSSMU21_523813872$        920     49431 ACTIVE    FP867    admin_fp DNTE120       sqlplus.exe           20-AUG-14 18:18:33
_SYSSMU35_2471118694$      1018     35259 ACTIVE    FP832    admin_fp DNTE120       DataSourceManager.exe 20-AUG-14 18:01:31
_SYSSMU48_3873826747$      1298      9341 ACTIVE    FP898    admin_fp DNTE120       DataSourceManager.exe 20-AUG-14 18:12:29
_SYSSMU37_3210426486$      1402      6017 ACTIVE    FP898    admin_fp DNTE120       sqlplus.exe           20-AUG-14 18:19:39

Regards
Sivakumar.A
www.sivakumardba.com

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Undo utilization is very high

Postby Tim... » Wed Aug 20, 2014 1:36 pm

Hi.

First, but the time you come to check a session, it has probably stopped using the UNDO. Same goes for the UNDO tablespace generally. If you are concerned about how much undo is used by a specific session, you really need to monitor it while it is happening.

Second, if you reduce the size of the UNDO_RETENTION you risk getting snapshot too old messages. If you are worries about the size of the undo tablespace being a problem I would suggest you do one of two things:

1) Increase the size of the undo tablespace so it is no longer a problem.
2) Investigate which part of your application is producing the undo and see if it can be tuned/rewritten to produce less undo.

In addition to potential snapshot too old messages, reducing the UNDO_RETENTION will limit the scope of flashback operations.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

sivakumarocp
Advisor
Posts: 260
Joined: Sat Jun 27, 2009 3:53 am
Location: India

Re: Undo utilization is very high

Postby sivakumarocp » Thu Aug 21, 2014 12:23 pm

How do i Identify which part of my application consumes undo.
Regards
Sivakumar.A
www.sivakumardba.com

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Undo utilization is very high

Postby Tim... » Thu Aug 21, 2014 3:03 pm

Hi.

You can track which user is using UNDO using this script.

http://www.oracle-base.com/dba/script.p ... _space.sql

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

sivakumarocp
Advisor
Posts: 260
Joined: Sat Jun 27, 2009 3:53 am
Location: India

Re: Undo utilization is very high

Postby sivakumarocp » Mon Aug 25, 2014 2:22 pm

Hi Tim,

When I used your query it show only few MB's of undo data is getting used by the DB

Code: Select all

SID_SERIAL           USERNAME             PROGRAM                        UNDOSEG                   UNDO
-------------------- -------------------- ------------------------------ ------------------------- --------------------
482,60023            FCCP124              sqlplus.exe                    _SYSSMU23_1303476429$     623816K
1116,39133           FCCPREAD             plsqldev.exe                   _SYSSMU1_1288450692$      8K
1345,33871           FCCP832              sqlplus.exe                    _SYSSMU60_3238781853$     435832K

Tablespace Name                 Allocated       Used       Free      Space
                                  (in MB)    (in MB)    (in MB)   Avail(%)
------------------------------ ---------- ---------- ---------- ----------
UNDOTBS1                           103424   98426.75    4997.25       4.83



But not sure why only few FREE space is available on my undo tablespace!. I am getting confused here.
below is the current undo settings.

Code: Select all

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_tablespace                      string      UNDOTBS1
SQL>


my concern here is how do I reduce the space utilization of my Undo tablespace.
Regards
Sivakumar.A
www.sivakumardba.com

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Undo utilization is very high

Postby Tim... » Mon Aug 25, 2014 5:35 pm

Hi.

The query shows what is actively being used at the moment. If nothing much is being used at the time you issue the query, it won't show much... :)

Remember how undo works. Oracle will keep all undo until the space is needed, then it will remove only enough undo to allow the new operations undo to be stored. It will also try to honour the UNDO_RETENTION. This is standard behaviour and you want it to do this because:

1) Keeping the undo allows you to perform flashback operations.
2) Keeping the undo reduces the chances of "Snapshot too old" messages.
3) During crash recovery, redo is used to rebuild buffers that never got to disk, then undo is used to rollback any un-commited transactions.

You do not want an empty undo tablespace every time a transaction is complete!

If you are not experiencing undo problems, like the undo tablespace becoming massive or snapshot too old messages, you should forget about the contents of the undo tablespace.

I think you might benefit from reading up on undo in the Oracle documentation. It kind-of sounds like you have misunderstood the purpose of it...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 0 guests

cron