RE: RE: Deceptive commit after select from dblink.
From: Sayan Sergeevich Malakshinov <malakshinovss_at_psbank.ru>
Date: Thu, 18 Oct 2012 18:01:55 +0400
Message-ID: <OF6D2DC916.2A74B44E-ON44257A9B.002F3CF1-44257A9B.004D22F2_at_psbank.ru>
Sai,
thanks for good point about active standby and read-only databases. And you are right: after "select from dblink" in x$ktcxb on remote i see row without slot(kxidusn=0,kxidslt=0,kxidsqn=0,BITAND(ktcxbflg,2)=0 ) and regardless of whether the commits/rollbacks, the row does not disappear until disconnect. Even if was commit after any dml(slot created, flag changed), row stil exists without slot again. But i have doubts:
1. So why commit(rollback) is needed after simple query from remote, if there are no differences whether the commits/rollbacks or not? 2. What differences between commit and rollback on read-only db?
Date: Thu, 18 Oct 2012 18:01:55 +0400
Message-ID: <OF6D2DC916.2A74B44E-ON44257A9B.002F3CF1-44257A9B.004D22F2_at_psbank.ru>
Sai,
thanks for good point about active standby and read-only databases. And you are right: after "select from dblink" in x$ktcxb on remote i see row without slot(kxidusn=0,kxidslt=0,kxidsqn=0,BITAND(ktcxbflg,2)=0 ) and regardless of whether the commits/rollbacks, the row does not disappear until disconnect. Even if was commit after any dml(slot created, flag changed), row stil exists without slot again. But i have doubts:
1. So why commit(rollback) is needed after simple query from remote, if there are no differences whether the commits/rollbacks or not? 2. What differences between commit and rollback on read-only db?
PS. Really "read-only rollbacks" have no effect in "user commits"/"user rollbacks" statistics. But the same can be on local only too: http://pastebin.com/NDxdssqn
-- Best regards, Sayan Malakshinov http://orasql.orgReceived on Thu Oct 18 2012 - 16:01:55 CEST
|------------>
| ïÔ: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |Saibabu Devabhaktuni <saibabu_d_at_yahoo.com> | >--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| ëÏÍÕ: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |free <oracle-l_at_freelists.org> | >--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| äÁÔÁ: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |16.10.2012 10:15 | >--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| ôÅÍÁ: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |RE: Deceptive commit after select from dblink. | >--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| ïÔ: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |oracle-l-bounce_at_freelists.org | >--------------------------------------------------------------------------------------------------------------------------------------------------| Sayan, Here is what I guess explains what you observed: Whether we call it distributed transaction or not, a simple query over dblink will require transaction to be created on the local and the remote database, this is needed for transaction recoverabilty if there is any failure on local ore remote database, šbut to make it work for read only standby databases, I suspect Oracle changed remote database to only create transaction in the transaction state array but no transaction will appear in the undo segment transaction table unless if there is any DML. I believe, it is for this reason, a commit on the local database trigger rollback on the remote database first when there were no DML's on the remote database since commit with session having an entry in transaction state object probably require corresponding undo segment entries. By the way, I didn't observe rollback or commit statistic values going up on the remote database session statistics, it is weird that it appeared in the trace file only. Whenever any DML happens on the remote database over dblink, transaction table in the undo segment will have an entry and hence commit or rollback on the local database can have similar effect on the remote database too. Thanks, šSai http://sai-oracle.blogspot.com -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l