| 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?
The problem I'm having is that I don't think it is being reused. It's only two blocks after all. That's no where near enough to cause a wrap.. is there some other situation? Someone was mentioning delayed block cleanout.. can this be afactor? These aren't long running queries particularly The select is about 1/2 minute and the activity on the remote database a minute or two - not huge batch here really.
On Thu, 08 Nov 2001 17:25:18 -0800, you wrote:
>
>
>> 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
-- 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).Received on Thu Nov 08 2001 - 21:47:49 CST
 |
 |