dblink 不走索引 查询_[Oracle]跨DBLINK的JOIN查询的数据库缓存问题15783452141

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