RE: The good old ORA-01555 rollback too small

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Wed, 9 Feb 2005 13:39:29 -0500
Message-ID: <4001DEAF7DF9BD498B58B45051FBEA65021B809D@25exch1.vicorpower.vicr.com>

None noted.=20

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Kip.Bryant_at_Vishay.com [mailto:Kip.Bryant_at_Vishay.com]=20 Sent: Wednesday, February 09, 2005 12:57 PM To: Goulet, Dick
Cc: oracle-l_at_freelists.org
Subject: RE: The good old ORA-01555 rollback too small

Interesting concept. Any problems with lockwaits or other side effects?

Kip Bryant

|Michael,

| You've got it right, someone did an update/delete somewhere
|along the way, committed & left. Suggestion try speeding up the
queries
|that these programs use. Sometimes a specialized index is just the
|trick. Have a job that archives data from PeopleSoft's CM_ACCTG_LINE
|table, 500M rows today. They create a specialized index on the table
at
|the start of the program, great for locking things that get in the way
|up, do their thing, and then drop the index. Not only does it make
the
|ORA-01555 go away but it cut the run time down around 60%.

|ORA-01555: snapshot too old: rollback segment number 3 with name
"RBS02"
|too small
|=3D20

|Production got one of these last night from two huge jobs trying to run
|against a table in the range of 200M rows. They were reading this table
|based on perhaps months and codes to build another table. So perhaps
job
|A was reading "base" to build "job_a_table" based on current month and
a
|set of codes, and job B was reading "base" to build "job_b_table" based
|on a different set of criteria.

|=3D20

|I would normally assume this would have been just fine.

|=3D20

|If not, is there any "locking" that can be done to stop the ORA-01555?

|=3D20

|(I still suspect some where along the line, someone tried to update
|while these jobs were running.)

|=3D20

|This has been running fine in the past. There are some new jobs being
|brought into production.

|=3D20

|Oracle is 8.1.7.4 on HP-UX. Rollback capacity is in the vicinity of 32
|GB and has never ran out yet.

|=3D20

|Cell 804.744.1545
| <mailto:michael.kline_at_suntrust.com> michael.kline_at_suntrust.com=3D20
|************************************************=3D20
|The information transmitted is intended solely=3D20
|for the individual or entity to which it is =3D20
|addressed and may contain confidential and/or=3D20
|privileged material. Any review, retransmission,=3D20
|dissemination or other use of or taking action=3D20
|in reliance upon this information by persons or=3D20
|entities other than the intended recipient is=3D20
|prohibited. If you have received this email in=3D20
|error please contact the sender and delete the=3D20
|material from any computer. [ST:A234]=3D20
|************************************************=3D20

|--
|http://www.freelists.org/webpage/oracle-l
|--
|http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 09 2005 - 13:42:41 CST