| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: blowing out rollbacks on select statements?
> Can anyone think of a scenario where a snapshot too old on the remote
rollback
> would occur with so little rollback in use?
Yes, if we're still talking about remote rollback here.
As I recall from reading this earlier today, these are fairly lengthy queries that take awhile to run.
If concurrency at the remote end requires rollback at the remote end due to current DML, that rollback could be reused before your query is finished, hence the ORA-1555.
I would investigate increasing rollbacks at the remote and/or setting optimal to prevent shrinks.
Jared
Doug C
<dcowles_at_i84.n To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
et> cc:
Sent by: Subject: Re: blowing out rollbacks on
select statements?
root_at_fatcity.c
om
11/08/01 02:45
PM
Please respond
to ORACLE-L
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Jared.Still_at_radisys.com 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).Received on Thu Nov 08 2001 - 19:45:18 CST
 |
 |