It's like one of those silly kid games (when is a remote select like a two
phase commit?)
Seems pretty weird to me. But I tested it. It takes not only a TX lock,
but a DX lock on the
local RBS. I don't get why . . .
RBS Name Used Blocks User Name Lo
---------- ----------- ------------------------------ --
2 1 SCOTT DX
2 1 SCOTT TX
> ----------
> From: Jared.Still_at_radisys.com[SMTP:Jared.Still_at_radisys.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Thursday, November 08, 2001 10:08 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: blowing out rollbacks on select statements?
>
>
> Christian,
>
> I read the note on MetaLink. Is it just me, or does the explanation
> given just not make any sense?
>
> Jared
>
> Doc ID:
> Note:74811.1
>
> 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
>
>
>
>
>
> Christian
>
> Trassens To: Multiple recipients of
> list ORACLE-L <ORACLE-L_at_fatcity.com>
> <ctrassens_at_yah cc:
>
> oo.com> Subject: Re: blowing out
> rollbacks on select statements?
> Sent by:
>
> root_at_fatcity.c
>
> om
>
>
>
>
>
> 11/08/01 08:10
>
> AM
>
> Please respond
>
> to ORACLE-L
>
>
>
>
>
>
>
>
>
> 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
> --
> Author: Christian Trassens
> INET: ctrassens_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).
>
>
>
>
> --
> 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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Baker, Barbara
INET: bakerb_at_rockymountainnews.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 - 16:57:40 CST
