Re: [sqr-users] Unexpectedly Filling Rollback Segments

[Arun Kumar <arunkumar13@yahoo.com>]

Don,
I wouldnt jump to conclude that RBS are the only answer.

I dont know how databases treat RBS but I will not be surprised if the issues 
come back after increasing the RBS size.

Just following your statements...

If you were getting RBS Error for about 10,000 rows... and the table has around 
350,000 rows.

So how big can the RBS be increased to? How come database doesnt have issues 
with other sql?



----- Original Message ----
From: "Simpson, Don I (UK)" <Don.I.Simpson@baesystems.com>
To: This list is for discussion about the SQR database reportinglanguage from 
Hyperion Solutions. <sqr-users@sqrug.org>
Sent: Monday, February 5, 2007 11:39:29 AM
Subject: RE: [sqr-users] Unexpectedly Filling Rollback Segments

Dragon,

Thanks, you've got the answer. I am getting the "snapshot too old" error
and I am running a massive query and updating within it, i.e. I'm
selecting all the records at once (350,000 or so) and then updating each
one after the relevant data is extracted, so even though I'm commiting
each update, because the query is still open, it keeps the rollback
record. It looks like increasing the rollback segment space is the only
answer. 

Thanks for your help,

Regards,

Don


-----Original Message-----
From: sqr-users-bounces+don.i.simpson=baesystems.com@sqrug.org
[mailto:sqr-users-bounces+don.i.simpson=baesystems.com@sqrug.org] On
Behalf Of the dragon
Sent: 05 February 2007 15:57
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Unexpectedly Filling Rollback Segments

               *** WARNING ***

This mail has originated outside your organization, either from an
external partner or the Global Internet. 
     Keep this in mind if you answer this message. 

Increasing rollback segments should only be used as a course of last
resort. 
  Usually, if everything else is working well on your system, and a
particular new process is having problems, then it's poorly written code
which needs to be addressed, not adding resources.

I do not believe that the issue is the roolback segments in this case.
It could be the initial select is getting out of sync because there have
been so many commits since the select was first made, but then I would
expect a "snapshot too old" error.

peace,
clark 'the dragon' willis



PSA: Salary <> Slavery. If you earn a salary, your employer is renting
your services for 40 hours a week, not purchasing your soul. Your time
is the only real finite asset that you have, and once used it can never
be recovered, so don't waste it by giving it away.

I work to live; I don't live to work.

"Time is the coin of your life. It is the only coin you have, and only
you can determine how it will be spent. Be careful lest you let other
people spend it for you." -- Carl Sandburg (1878 - 1967)

It is impossible to defeat an ignorant man in argument. -- William G.
McAdoo

Religion is regarded by the common people as true, by the wise as false,
and by the rulers as useful. -- Seneca

"I distrust those people who know so well what God wants them to do
because I notice it always coincides with their own desires." - Susan B.
Anthony




----Original Message Follows----
From: "Knapp, Richard" <KnappR@umsystem.edu>
Reply-To: "This list is for discussion about the SQR database 
reportinglanguage from Hyperion Solutions." <sqr-users@sqrug.org>
To: "This list is for discussion about the SQR database
reportinglanguage 
fromHyperion Solutions." <sqr-users@sqrug.org>
Subject: RE: [sqr-users] Unexpectedly Filling Rollback Segments
Date: Mon, 5 Feb 2007 09:31:54 -0600
MIME-Version: 1.0
Received: from seldon.sqrug.org ([199.164.165.113]) by 
bay0-mc2-f23.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2444);
Mon, 5 
Feb 2007 07:48:22 -0800
Received: from localhost([127.0.0.1] helo=seldon.sqrug.org ident=list)by

seldon.sqrug.org with esmtp (Exim 3.35 #1 (Debian))id
1HE64q-000370-00for 
<ceprn@hotmail.com>; Mon, 05 Feb 2007 10:48:20 -0500
Received: from um-tsmtpout1.um.umsystem.edu ([209.106.228.23])by 
seldon.sqrug.org with esmtp (Exim 3.35 #1 (Debian))id
1HE5oz-0001c7-00for 
<sqr-users@sqrug.org>; Mon, 05 Feb 2007 10:31:57 -0500
Received: from UM-XMAIL04.um.umsystem.edu ([209.106.228.4]) 
byum-tsmtpout1.um.umsystem.edu with Microsoft SMTPSVC(6.0.3790.1830);
Mon, 5 
Feb 2007 09:31:55 -0600
X-Message-Info: LsUYwwHHNt17xzMWfbewrGe5M1tiPGmOM0jToLg1u7E=
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
X-MS-Has-Attach:
X-MS-TNEF-Correlator:
Thread-Topic: [sqr-users] Unexpectedly Filling Rollback Segments
Thread-Index: AcdJOTGSwSBqwBHpQVuNEe4kiIyXJQAAX//w
References: <3E842A7A776DEC4FB5A8522E64A9A5D401528C09@glkms0009>
X-OriginalArrivalTime: 05 Feb 2007 15:31:55.0730 
(UTC)FILETIME=[C4848B20:01C7493A]
X-BeenThere: sqr-users@sqrug.org
X-Mailman-Version: 2.1.5
Precedence: list
List-Id: "This list is for discussion about the SQR database 
reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
List-Unsubscribe: 
<http://www.sqrug.org/mailman/listinfo/sqr-users>,<mailto:sqr-users-requ
est@sqrug.org?subject=unsubscribe>
List-Post: <mailto:sqr-users@sqrug.org>
List-Help: <mailto:sqr-users-request@sqrug.org?subject=help>
List-Subscribe: 
<http://www.sqrug.org/mailman/listinfo/sqr-users>,<mailto:sqr-users-requ
est@sqrug.org?subject=subscribe>
Errors-To: sqr-users-bounces+ceprn=hotmail.com@sqrug.org
Return-Path: sqr-users-bounces+ceprn=hotmail.com@sqrug.org


Speak to your DBA to see if the rollback segments can be increased in
size.

Richard Knapp
EAS - Data Warehouse Group
University of Missouri
615 Locust Street  #200
Columbia, MO 65201
573-882-8856
knappr@umsystem.edu


-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
Simpson, Don I (UK)
Sent: Monday, February 05, 2007 9:21 AM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: [sqr-users] Unexpectedly Filling Rollback Segments


Dear SQR'rs,

Can anybody help with advice on this one.

I'm having to do a lot of update transactions as part of a project to
extract data. The updates are basically to flag extracted records, so we
can do deltas later on.

I'm using commit on each BEGIN-SQL, which I thought was meant to reduce
the use of rollbacks as each transaction is immediately commited,
therefore no rollback to do, i.e.

BEGIN-SQL
update <table>
set <column_1> = value where <column_2 = <test_value>;
commit;
END-SQL

but, after a number of such transactions, probably around 10,000, I get
a rollback error.

Does the commit technique inside BEGIN-SQL not do what I think it is
meant to do?

Regards,

Don Simpson
BAE Systems




********************************************************************
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.
********************************************************************

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_________________________________________________________________
Search for grocery stores. Find gratitude. Turn a simple search into 
something more. 
http://click4thecause.live.com/search/charity/default.aspx?source=hmemta
gline_gratitude&FORM=WLMTAG


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users







 
____________________________________________________________________________________
Need a quick answer? Get one in minutes from people who know.
Ask your question on www.Answers.yahoo.com

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users