ORA-01555 - best way to manage rollback segments? 1

[MCubitt]

Hi there,

I get this message:
Code: ORA-01555
Text: snapshot too old: rollback segment number 11 with name "_SYSSMU11$" too small

And from what I can gather, I need to add or make larger the number of rollback segments.

Our rollback segments are in SYSTEM tablespace, which is 1Gb and 92% full.

I can increase the size (not a problem) but what exactly should I do?

Do I increase the size of SYSTEM to, say, 1.5Gb and then how do I increase the size of the rollback segments? Or should I add more?

Thank you,






There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Carp,

A very long winded post which has all the characteristics you mention. A summarised version would probably be useful.

The user (who happens to be my sister!) has now reported that the same transaction for a LOT LESS data took 15 minutes, so the fault could lay in teh application or tuning of our Oracle DB.

As I mentioned before, the fault is with the duration of teh transaction not so much teh ORA message I get as a result of that.

Back to the supplier!

Thanks all for your input.





There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

MCubitt,

The post to which Carp refers you perhaps seems "longer-winded" because of extra "banter" among respondents. A "shorter-winded" distillation of that thread appears (as I mentioned in my post above) at FAQ: "ORA-01555: Snapshot too old: rollback...too small". Why do I get it? How can I stop it? FAQ759-4436. Any less "wind" than is already in the FAQ results in "missing pieces" for the reader, thus leading the reader back to the "incomplete" solution that Oracle offers in the error message: "Throw more storage space at the rollback segment."

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:07 (17Jun04) UTC (aka "GMT" and "Zulu"), 09:07 (17Jun04) Mountain Time)

 

[MCubitt]

As always, the Oracle DB offers more questions than answers.

FYI we are now looking into why the transactions took so long, not why we get the error. That really is the key here. One hour to save data is appauling, especially when there is no real volume. Either badly written app or badly tuned Oracle. I suspect a bit of both




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

MCubitt,

Out of curiosity...what type of DML is occurring during the one hour: INSERTs, UPDATEs, or DELETEs? If UPDATEs or DELETEs are occurring, then I suggest monitoring "blocking locks" during your entire run. That could account for the horrendous delay(s). If you are doing INSERTs, then definitely look to the application design (since Oracle does not produce DML locks that could block (delay) an INSERT.

Let us know what you find.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:44 (17Jun04) UTC (aka "GMT" and "Zulu"), 10:44 (17Jun04) Mountain Time)

 

[MCubitt]

Santa,

Sounds like a plan.. only, "how"? I am unsure how to monitor DML locks.

Thanks


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

MCubitt,

Following is my code to monitor "Blocking Locks":

Rem    Name:    LockBlock.sql
Rem    Author:  Dave Hunt
Rem
Rem    This script shows who is holding a lock that other people are
Rem    waiting for and who the waiters are.
Rem
Rem
prompt
prompt Gathering lock information...
set echo off
set feedback off
set pagesize 0
set linesize 150
ttitle off
col "Object" format a20 
col "Holder" format a25
col "Waiter" format a25
col "Lock Type" format a20
prompt ...Countdown...8...
create table TempAllObjects as select * from All_Objects;
prompt ...7...
create index TempAllObjectsID on TempAllObjects (object_id);
prompt ...6...
create table TempVSession as select * from v$Session;
prompt ...5...
create index TempVSessionSID on TempVSession (sid);
prompt ...4...
create table TempVLock as select * from v$lock;
prompt ...3...
create index TempVLockSID on TempVLock(SID);
prompt ...2...
create index TempVLockID1 on TempVLock(ID1);
prompt ...1...
create index TempVLockType on TempVLock(Type);
prompt GO
prompt
select decode(count(*),0,'There are no blocking locks.',1,'There is 1 blocking lock:',
		'There are '||count(*)||' blocking locks:') 
  from TempAllObjects o, TempVSession sw, TempVLock lw, TempVSession sh, TempVLock lh
 where lh.id1  = o.object_id
  and  lh.id1  = lw.id1
  and  sh.sid  = lh.sid
  and  sw.sid  = lw.sid
  and  sh.lockwait is null
  and  sw.lockwait is not null
  and  lh.type = 'TM'
  and  lw.type = 'TM'
/
set pagesize 35
select distinct o.owner||'.'||o.object_name "Object"
	,sh.osuser||':'||sh.username||'('||sh.sid||')' "Holder"
	,sw.osuser||':'||sw.username||'('||sw.sid||')' "Waiter",
        decode(lh.lmode
		, 0, 'none'
		, 1, 'null'
		, 2, 'row share'
		, 3, 'row exclusive'
		, 4, 'share'
		, 5, 'share row exclusive' 
		, 6, 'exclusive'
		,    'unknown')  "Lock Type"
  from TempAllObjects o, TempVSession sw, TempVLock lw, TempVSession sh, TempVLock lh
 where lh.id1  = o.object_id
  and  lh.id1  = lw.id1
  and  sh.sid  = lh.sid
  and  sw.sid  = lw.sid
  and  sh.lockwait is null
  and  sw.lockwait is not null
  and  lh.type = 'TM'
  and  lw.type = 'TM'
/
drop table TempAllObjects;
drop table TempVSession;
drop table TempVLock;
prompt
set feedback on

Here is my code to monitor locks, even if they are not blocking locks:

ttitle "Lock Listing"
set linesize 150
set echo off
col oruser format a16 heading "Oracle Username"
col osuser format a13 heading "O/S Username"
col obj format a20 heading "Locked Object"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a19
col rs heading "RBS|Name" format a4
col unix heading "Unix|Process" format a9
col computer heading "Machine name|of Locker" format a20
set linesize 120
select	owner||'.'||object_name obj
	,oracle_username||' ('||s.status||')' oruser
	,os_user_name osuser
	,machine computer
	,l.process unix
	,''''||s.sid||','||s.serial#||'''' ss
	,r.name rs
	,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from	v$locked_object l
	,dba_objects o
	,v$session s
	,v$transaction t
	,v$rollname r
where l.object_id = o.object_id
  and s.sid=l.session_id
  and s.taddr=t.addr
  and t.xidusn=r.usn
order by osuser, ss, obj
/
ttitle off
set linesize 132

Let me know if these scripts turn up anything.

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:08 (17Jun04) UTC (aka "GMT" and "Zulu"), 15:08 (17Jun04) Mountain Time)

 

[MCubitt]

Santa,

Thanks for those.

I ran the scripts before the user even stated the task and got some unrelated locked object.

I am waiting for the user to run the routine. By the way, for info, they are "registering invoices". So they are matching invoices with purchase orders. In one case there were two POs against one invoice and one PO had 1 line, one had 2 lines. It took over an hour!







There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Okay, here we go. I logged into a DB which was recently copied from "live" and in use by no one else at the moment.

I ran lockblock several times, each time nothing was reported.

However, lockall showed much more...

SQL> @lockall

Fri Jun 18 page 1
Lock Listing

Machine name Unix RBS
Locked Object Oracle Username O/S Username of Locker Process SID/Ser# Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
IFSAPP.INTERNAL_HOLD IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
_VOUCHER_ROW_TAB SMU4
$

IFSAPP.INTERNAL_HOLD IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
_VOUCHER_TAB SMU4
$

Fri Jun 18 page 2
Lock Listing

Machine name Unix RBS
Locked Object Oracle Username O/S Username of Locker Process SID/Ser# Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------

IFSAPP.INTERNAL_POST IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
INGS_ACCRUL_TAB SMU4
$

IFSAPP.INVOICE_HISTO IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
RY_TAB SMU4

Fri Jun 18 page 3
Lock Listing

Machine name Unix RBS
Locked Object Oracle Username O/S Username of Locker Process SID/Ser# Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
$

IFSAPP.INVOICE_ITEM_ IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
TAB SMU4
$

IFSAPP.INVOICE_TAB IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52

Fri Jun 18 page 4
Lock Listing

Machine name Unix RBS
Locked Object Oracle Username O/S Username of Locker Process SID/Ser# Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
SMU4
$

IFSAPP.INV_ACCOUNTIN IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
G_ROW_TAB SMU4
$


Fri Jun 18 page 5
Lock Listing

Machine name Unix RBS
Locked Object Oracle Username O/S Username of Locker Process SID/Ser# Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
IFSAPP.LEDGER_ITEM_T IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
AB SMU4
$

IFSAPP.MAN_SUPP_INVO IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
ICE_ORDER_TAB SMU4
$

Fri Jun 18 page 6
Lock Listing

Machine name Unix RBS
Locked Object Oracle Username O/S Username of Locker Process SID/Ser# Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------

IFSAPP.PAYMENT_PLAN_ IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
TAB SMU4
$

IFSAPP.POSTING_PROPO IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
SAL_AUTH_TAB SMU4

Fri Jun 18 page 7
Lock Listing

Machine name Unix RBS
Locked Object Oracle Username O/S Username of Locker Process SID/Ser# Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
$

IFSAPP.POSTING_PROPO IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
SAL_HEAD_TAB SMU4
$

IFSAPP.POSTING_PROPO IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52

Fri Jun 18 page 8
Lock Listing

Machine name Unix RBS
Locked Object Oracle Username O/S Username of Locker Process SID/Ser# Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
SAL_TAB SMU4
$

IFSAPP.TAX_ITEM_TAB IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
SMU4
$


Fri Jun 18 page 9
Lock Listing

Machine name Unix RBS
Locked Object Oracle Username O/S Username of Locker Process SID/Ser# Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
IFSAPP.VOUCHER_NO_SE IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
RIAL_TAB SMU4
$

IFSAPP.VOUCHER_ROW_T IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
AB SMU4
$

Fri Jun 18 page 10
Lock Listing

Machine name Unix RBS
Locked Object Oracle Username O/S Username of Locker Process SID/Ser# Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------

IFSAPP.VOUCHER_TAB IFSAPP (ACTIVE) cubittm KEYMED\5R2RH0J 1740:1648 '20,6514' _SYS 2004/06/18 10:38:52
SMU4
$


17 rows selected.



A lot of locks!

The job took about 5 minutes to run, rather than the 1 hour and error.

Considering the entire physical structure of LIVE was copied to TEST, it does suggest locking issues could be the problem, doesn't it?




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

MCubitt,

I concur...Whenever something that should take moments/seconds takes many minutes/hours, the first thing I look at is locks, and I usually find issues there. If the problem is "intra-application" in batch mode, it is usually a design issue; if the problem is "others" locking rows with which your batch job deals, then you can reduce the problem by deferring the batch until "off hours".

Let us know what you discover,

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:49 (18Jun04) UTC (aka "GMT" and "Zulu"), 11:49 (18Jun04) Mountain Time)

 

[MCubitt]

Santa,

I await our ERP system vendor's reaction to the suggestion of lock contention. Could be a while...


There's no need for sarcastic replies, we've not all been this sad for that long!