Re: blowing out rollbacks on select statements?

This note refers to the local rollback segment. The error is coming from the remote rollback segment. Also, despite 1 GB of rollback tablespace on that database, this error is occuring when there are only 2 4K blocks in use.

Can anyone think of a scenario where a snapshot too old on the remote rollback would occur with so little rollback in use?

On Thu, 08 Nov 2001 08:10:28 -0800, you wrote:

>Distributed queries use rollback segment. Check this
>note:
>
>Doc ID:
>Note:74811.1
>
>Remote SELECT issues TX LOCK and uses Local Rollback
>
>Type:
>BULLETIN
>
>Status:
>PUBLISHED
>
> Content Type:
>TEXT/PLAIN
>
>Creation Date:
>08-OCT-1999
>
>Last Revision Date:
>09-OCT-2000
>
>
>PURPOSE
>To explain why a distributed query uses local rollback
>segments.
>
>SCOPE & APPLICATION
>For DBA's and Oracle Support Analysts.
>
>RELATED DOCUMENTS
>[BUG:702754]
>[BUG:262277]
>
>When a select from a remote table is issued a TX lock
>out is taken
>out on a local rollback segment. This lock is only
>released
>by issuing a COMMIT, ROLLBACK or logoff.
>
>For example:
>
>select empno,ename from emp_at_remotedb;
>
>This is documented behaviour in [BUG:262277].
>
>The explanation given is:
>
>"When we start a transaction which is to be executed
>remotely, since
>at that time we are not sure what is going to happen
>locally while
>the query is being executed remotely, a TX lock is
>taken out on
>a local rollback segment even if the query is only a
>select."
>
>The reason for this is that a remote select is like a
>two phase commit
>in that it sends out the query over the network and
>waits for it to
>return a successful result. If you check
>v$transaction during the
>transaction, you will see used_ublk show 1 block of
>rollback being used
>during this update and a TX lock against the rollback
>segment.
>
>The following is an example of a remote query using a
>local rollback
>segment:
>
>SQL> SELECT t.xidusn "RBS Name",
> t.used_ublk "Used Blocks",
> s.username "User Name",
> l.type "Lock Type"
> FROM v$transaction t,
> v$session s,
> v$lock l
> WHERE t.ses_addr=s.saddr
> AND s.sid = l.sid
> /
>
>no rows selected
>
>SQL> SELECT * FROM scott.emp;
>
>ENAME SALARY
>---------- ----------
>Mike 1000
>Nick 500
>Pete 400
>Dave 2000
>
>SQL> SELECT t.xidusn, t.used_ublk, s.username, l.type
> 2> FROM v$transaction t, v$session s, v$lock l
> 3 WHERE t.ses_addr=s.saddr AND s.sid = l.sid ;
>
>no rows selected
>
>SQL> SELECT * FROM scott.emp_at_suppaix1_v805;
>
>ENAME SALARY
>---------- ----------
>Mike 1000
>Nick 500
>Pete 400
>Dave 2000
>
>SQL> SELECT t.xidusn, t.used_ublk, s.username, l.type
> 2> FROM v$transaction t, v$session s, v$lock l
> 3> WHERE t.ses_addr=s.saddr AND s.sid = l.sid
> 4 /
>
> XIDUSN USED_UBLK USERNAME
>TYPE
>---------- ---------- --------
>-----
> 3 1 SCOTT
> TX
>
>SQL> COMMIT;
>
>Commit complete.
>
>SQL> SELECT t.xidusn, t.used_ublk
> 2> FROM v$transaction t, v$session s
> 3> WHERE t.ses_addr=s.saddr AND s.username='SYSTEM'
> 4 /
>
>no rows selected
>
>
>
>--- Doug C <dcowles_at_i84.net> wrote:
>> This is Oracle 8.1.7 -
>> Unless I'm getting bad information, a developer has
>> passed me some queries
>> that use a database link in them. They are select
>> queries, albeit complicated
>> ones with lot of outer joins and such. Both db's
>> are 8i but I think the remote
>> db is 8.1.6. Getting snapshot too old : roolback
>> segment number whatever is
>> too small. To me that means, that the select query
>> is going after tables that
>> are in the middle of being udpdated and losing there
>> ability to find a
>> consistent image before they are done. So, either
>> the quries are just plum
>> taking too much information and time and another
>> transaction that I have no
>> control over is busy updating the tables at the same
>> time..
>>
>> Agree?
>>
>> - Doug
>> --
>> Please see the official ORACLE-L FAQ:
>> http://www.orafaq.com
>> --
>> Author: Doug C
>> INET: dcowles_at_i84.net
>>
>> Fat City Network Services -- (858) 538-5051 FAX:
>> (858) 538-5051
>> San Diego, California -- Public Internet
>> access / Mailing Lists
>>
>--------------------------------------------------------------------
>> To REMOVE yourself from this mailing list, send an
>> E-Mail message
>> to: ListGuru_at_fatcity.com (note EXACT spelling of
>> 'ListGuru') and in
>> the message BODY, include a line containing: UNSUB
>> ORACLE-L
>> (or the name of mailing list you want to be removed
>> from). You may
>> also send the HELP command for other information
>> (like subscribing).
>
>
>=====
>ENG. Christian Trassens
>Senior DBA
>ctrassens_at_yahoo.com
>christian_trassens_at_yahoo.es
>Phone : +34-699240979
>+34-649824704
>
>__________________________________________________
>Do You Yahoo!?
>Find a job, post your resume.
>http://careers.yahoo.com
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Doug C
  INET: dcowles_at_i84.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).