How about the delayed block cleanout effect where
inactive entries in the rollback segment transaction
header have been overwritten but the block header has
not been cleaned and still shows the said transaction
as active .. ora-1555 right?
Deepak
- Jared.Still_at_radisys.com 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
> --
> 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).
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deepak Thapliyal
INET: deepakthapliyal_at_yahoo.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 - 20:17:56 CST
