Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- --Original Message-- -- From: Kip.Bryant@(protected) [mailto:Kip.Bryant@(protected)]=20 Sent: Wednesday, February 09, 2005 12:57 PM To: Goulet, Dick Cc: oracle-l@(protected) Subject: RE: The good old ORA-01555 (See ORA-01555.ora-code.com) 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 (See ORA-01555.ora-code.com) go away but it cut the run time down around 60%.
|Dick Goulet |Senior Oracle DBA |Oracle Certified 8i DBA |-- --Original Message-- -- |From: Kline.Michael [mailto:Michael.Kline@(protected)]=3D20 |Sent: Wednesday, February 09, 2005 8:16 AM |To: oracle-l@(protected) |Subject: The good old ORA-01555 (See ORA-01555.ora-code.com) rollback too small
|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.
|(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
|=3D20
|=3D20
|Michael Kline |Database Administration |SunTrust Technology Center |1030 Wilmer Avenue |Richmond, Virginia 23227 |Outside 804.261.9446 |STNet 643.9446
|Cell 804.744.1545 | <mailto:michael.kline@(protected)> michael.kline@(protected)=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
