客户问到跨DBLINK，结合本地表和远端表的时候，数据在哪一边 的 Data Buffer 缓存。
测试的结果是：本地表在本地缓存，远端表在远端缓存。
####Testcase-0929-10
本地数据库： 远端数据库：
TABXXX TABYYY
=============
本地数据库：
conn user003/user003
create table TABXXX(id integer, val varchar2(3));
insert into TABXXX values(1,'100');
insert into TABXXX values(2,'200');
insert into TABXXX values(3,'300');
commit;
=============
远端数据库：
conn user002/user002
create table TABYYY(id integer, val varchar2(3));
insert into TABYYY values(2,'200');
insert into TABYYY values(3,'300');
insert into TABYYY values(4,'400');
commit;
=============
本地数据库：
shu immediate
startup
conn user003/user003
create public database link dbk connect to user002 identified by user002 using 'remote';
查询前检查Buffer:
conn / as sysdba
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user002'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user003'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
=============
远端数据库：
查询前检查Buffer:
shu immediate
startup
conn / as sysdba
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user002'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user003'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
=============
本地数据库：
执行查询：
conn user003/user003
select a.id,b.value from TABXXX a, TABYYY@dbk b where a.id=b.id;
查询后检查缓存: <<<<<<<<<<<<<<<<<< 本地表在本地缓存，本地没有远端表的缓存内容
conn / as sysdba
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user002'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
no records found
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user003'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE BUFFER_POOL
--------------------------------------------------------- ---------------------
BUFFERS
----------
user003
TABXXX
TABLE DEFAULT
7
SQL>
=============
远端数据库：
查询后检查缓存: <<<<<<<<<<<<<<<<<< 远端表在远端缓存，远端数据库那边，没有本地表的缓存内容
conn / as sysdba
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user002'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE BUFFER_POOL
--------------------------------------------------------- ---------------------
BUFFERS
----------
user002
TABYYY
TABLE DEFAULT
14
SQL>
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user003'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
测试的结果是：本地表在本地缓存，远端表在远端缓存。
####Testcase-0929-10
本地数据库： 远端数据库：
TABXXX TABYYY
=============
本地数据库：
conn user003/user003
create table TABXXX(id integer, val varchar2(3));
insert into TABXXX values(1,'100');
insert into TABXXX values(2,'200');
insert into TABXXX values(3,'300');
commit;
=============
远端数据库：
conn user002/user002
create table TABYYY(id integer, val varchar2(3));
insert into TABYYY values(2,'200');
insert into TABYYY values(3,'300');
insert into TABYYY values(4,'400');
commit;
=============
本地数据库：
shu immediate
startup
conn user003/user003
create public database link dbk connect to user002 identified by user002 using 'remote';
查询前检查Buffer:
conn / as sysdba
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user002'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user003'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
=============
远端数据库：
查询前检查Buffer:
shu immediate
startup
conn / as sysdba
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user002'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user003'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
=============
本地数据库：
执行查询：
conn user003/user003
select a.id,b.value from TABXXX a, TABYYY@dbk b where a.id=b.id;
查询后检查缓存: <<<<<<<<<<<<<<<<<< 本地表在本地缓存，本地没有远端表的缓存内容
conn / as sysdba
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user002'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
no records found
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user003'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE BUFFER_POOL
--------------------------------------------------------- ---------------------
BUFFERS
----------
user003
TABXXX
TABLE DEFAULT
7
SQL>
=============
远端数据库：
查询后检查缓存: <<<<<<<<<<<<<<<<<< 远端表在远端缓存，远端数据库那边，没有本地表的缓存内容
conn / as sysdba
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user002'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE BUFFER_POOL
--------------------------------------------------------- ---------------------
BUFFERS
----------
user002
TABYYY
TABLE DEFAULT
14
SQL>
select o.owner,o.object_name,o.object_type,s.buffer_pool,count(*) buffers
from dba_objects o,v$bh b,dba_segments s
where b.objd=o.data_object_id
and o.owner = 'user003'
and o.object_name=s.segment_name
and o.object_type=s.segment_type
group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool
order by buffers;
no records found
本文转自健哥的数据花园博客园博客，原文链接：http://www.cnblogs.com/gaojian/p/3_15783452141.html，如需转载请自行联系原作者
