[Oracle]跨DBLINK的JOIN查询的数据库缓存问题15783452141-阿里云开发者社区

[Oracle]跨DBLINK的JOIN查询的数据库缓存问题15783452141

2017-12-26 4116

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介：

客户问到跨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;

no records found

本文转自健哥的数据花园博客园博客，原文链接：http://www.cnblogs.com/gaojian/p/3_15783452141.html，如需转载请自行联系原作者

[Oracle]跨DBLINK的JOIN查询的数据库缓存问题15783452141

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像