| Database Links [message #489977] |
Thu, 20 January 2011 07:46  |
faizul
Messages: 23
Registered: June 2005
|
Junior Member |
|
|
Hi Guru's,
We have a situation where we could have our database split into two and located in single or multiple servers.
Depending on the choices our customers make, they can also install the two databases into a single database (using different schema names).
I know I can access remote databases using database links but what would happen performance ways if these remote databases were actually local database with different schema name (a loopback database link)?
Here's an example
One customer might go with the option:
ServerA.DatabaseA.SchemaA
ServerB.DatabaseB.SchemaB
and another might go with the option:
ServerA.DatabaseA.SchemaA
ServerA.DatabaseA.SchemaB
and another might go with the option:
ServerA.DatabaseA.SchemaA
ServerA.DatabaseB.SchemaB
What performance issues should I be expecting? Ideally I would like to have one set of stored procedures that work with both cases.
What can I do in a loopback case as it needs the trailing identifier?
Thanks
[Updated on: Thu, 20 January 2011 07:48] Report message to a moderator |
|
|
|
| Re: Database Links [message #489980 is a reply to message #489977] |
Thu, 20 January 2011 08:07  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I wouldn't think that performance would be an issue, but data integrity might be. With a loopback database link, Oracle will think there are two databases, and two tables for storing the in-doubt transactions. But in fact, there is only one. I think this can cause problems with the two phase commit.
|
|
|
|
| Re: Database Links [message #489983 is a reply to message #489980] |
Thu, 20 January 2011 08:16 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
And also transaction processing is not the same one, you enter the world of distributed transactions with 2-phase commits and where even SELECT generates transactions.
So yes, there will be an impact in performances, response time and throughput.
Regards
Michel
[Edit: fix typo]
[Updated on: Thu, 20 January 2011 10:53] Report message to a moderator |
|
|
|
|
|
| Re: Database Links [message #490009 is a reply to message #490008] |
Thu, 20 January 2011 10:24 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
I once experimented with using loopback DB links to join tables within the same database together (dont ask) and hit massive performance issues.
These were just selects though.
|
|
|
|
| Re: Database Links [message #490028 is a reply to message #490009] |
Thu, 20 January 2011 11:14 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
This script
drop user jon cascade;
grant dba to jon identified by jon;
conn jon/jon
create database link l1 connect to jon identified by jon using 'orcl';
create database link l2 connect to jon identified by jon using 'orcl';
create table t1 as select * from all_users;
delete from t1@l1;
insert into t1@l2 select * from all_users;
select count(*) from v$transaction;
select count(*) from dba_2pc_pending;
shows one transaction, no pending transactions. Does that mean that Kevin is right: Oracle is clever enough to sort it out? I took the test a step further, and on a two node RAC I created the database links going to different instances, same result.
|
|
|
|
| Re: Database Links [message #490033 is a reply to message #490028] |
Thu, 20 January 2011 11:25 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Correction! In the RAC, I don't get the same result:
RDB1> select inst_id from gv$transaction;
no rows selected
RDB1> delete from t1@l1;
insert into t1@l2 select * from all_users;
38 rows deleted.
RDB1>
38 rows created.
RDB1> select inst_id from gv$transaction;
INST_ID
----------
1
2
RDB1> select count(*) from dba_2pc_pending;
COUNT(*)
----------
0
RDB1> commit;
Commit complete.
RDB1> select inst_id from gv$transaction;
no rows selected
RDB1>
The RAC does see it as two transactions, but still nothing in dba_2pc_pending. Am I misunderstanding that view?
|
|
|
|
|
|
| Re: Database Links [message #490046 is a reply to message #490033] |
Thu, 20 January 2011 12:37 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
dba_2pc_pending is filled only when a 2PC is waiting for recovery not when a distributed transaction is active. DBA_2PC_NEIGHBORS is maybe a better choice for this.
But querying v$dblink (.in_transaction) is even better (although it gives less information).
For instance, I have a db loopback link from MICHEL to SCOTT, you can see the remote access via:
SQL> set autotrace traceonly explain
SQL> select * from emp@mika@scott;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE| | 14 | 1218 | 3 (0)| 00:00:01 | |
| 1 | TABLE ACCESS FULL | EMP | 14 | 1218 | 3 (0)| 00:00:01 | MIKA |
----------------------------------------------------------------------------------------
Note
-----
- fully remote statement
- dynamic sampling used for this statement
See the "SELECT REMOTE", the first note "fully remote statement" and the "Inst" column telling on which site the line is executed.
Now if I query v$dblink I get:
SQL> select logged_on, in_transaction, update_sent from v$dblink
2 where db_link='MIKA.XXX@SCOTT';
LOG IN_ UPD
--- --- ---
YES NO NO
The point there is that Oracle is smart enough to see it is a loopback db link and does not start a transaction has it would do with a remote site but it still treat it as it when optimizing the query.
Now if I make an update:
SQL> update emp@mika@scott set sal=sal+1;
14 rows updated.
SQL> select logged_on, in_transaction, update_sent from v$dblink
2 where db_link='MIKA.XXX@SCOTT';
LOG IN_ UPD
--- --- ---
YES YES YES
SQL> select sys_context('userenv','sid') mysid, sid, username
2 from v$transaction t, v$session s where t.ses_addr = s.saddr;
MYSID SID USERNAME
---------- ---------- ------------------------------
159 159 MICHEL
SQL> select sid from v$session where username='SCOTT';
SID
----------
139
There is only one transaction going-on and my session is the owner of this transaction when there should be 2 transactions going-on in a real remote case one for MICHEL and one for SCOTT.
So we have not a distributed transaction, this is a good point - I was wrong.
But the optimizer still thinks it is a remote or distributed statement and acts as it, this is the bad point.
SQL> set autotrace traceonly explain
SQL> update emp@mika@scott set sal=sal+1;
14 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 1494045816
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |
----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT REMOTE| | 14 | 182 | 3 (0)| 00:00:01 | |
| 1 | UPDATE | EMP | | | | | MIKA |
| 2 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 | MIKA |
----------------------------------------------------------------------------------------
Note
-----
- fully remote statement
- dynamic sampling used for this statement
Note that during this test DBA_2PC_NEIGHBORS was always empty.
Regards
Michel
[Edit: remove domain name]
[Updated on: Thu, 20 January 2011 13:37] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Database Links [message #490070 is a reply to message #490058] |
Thu, 20 January 2011 14:48 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Faizul: I must apologize for your thread being hi-jacked. I guess you can tell that no-one knows for sure what will happen with your database link solution, but it doesn't sound like a good idea.
---
I've tried to globalize Michel's example, with DML through links to the two instances:RDB1> select inst_id from gv$transaction;
no rows selected
RDB1> select logged_on, in_transaction, update_sent from v$dblink;
no rows selected
RDB1> delete from t1@l1 where username='SYS';
1 row deleted.
RDB1> delete from t1@l2 where username='SYSTEM';
1 row deleted.
RDB1> select inst_id from gv$transaction;
INST_ID
----------
1
2
RDB1> select logged_on, in_transaction, update_sent from v$dblink;
LOG IN_ UPD
--- --- ---
YES YES YES
YES YES YES
RDB1> select * from dba_2pc_neighbors;
no rows selected
RDB1> commit;
Commit complete.
RDB1> select inst_id from gv$transaction;
no rows selected
RDB1> select logged_on, in_transaction, update_sent from v$dblink;
LOG IN_ UPD
--- --- ---
YES NO NO
YES NO NO
RDB1>
Was there a two phase commit? If I understand Michel's post correctly, a distributed transaction should show as one transaction in the source database, plus one transaction in the linked database. So in my example with two updates through different links, I would expect to see three transactions, if it is being managed by 2PC. If it is not being managed by 2PC, does that mean that I could end up with half the transaction committed, if one of the instances fails? Because Oracle has realized that it is only one database, but has not realized that it is two instances which commit independently?
I do of course realize that this is of no practical value: you would need to be a complete lunatic (or a DBA stuck in a hotel room in Geel, which is in Belgium) to construct this example.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
