[Oracle]跨越 DBLINK 访问表时，数据缓存在何处的Data Buffer 中？

远程查询与缓存机制

最新推荐文章于 2023-12-27 11:22:38 发布

weixin_34146410

最新推荐文章于 2023-12-27 11:22:38 发布

阅读量230

点赞数

文章标签：数据库

本文探讨了通过DBLINK进行远程数据库查询时的数据缓存机制。实验表明，数据查询及缓存发生在远程端，且远程端能够高效地完成计算、缓存及检索任务。文章通过具体SQL指令和查询结果展示了这一过程。

结论是存储在 remote 端，这其实也很好理解。在远端能高效率地计算，当然应当在远端完成缓存和检索。

■ Before query execution via DBLINK:

===========
DBLINK destination (remote side):

conn u2 / u2
create table tab002 as select * from dba_objects;

shu immediate
startup

select o.owner, o.object_name, o.object_type, s.buffer_pool, count (*) buffer
from dba_objects o, v $ bh b, dba_segments s
where b.objd = o.data_object_id
and o.owner = 'U 2'
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;

A record was not selected.

===========
Local side:

conn u3 / u3

select o.owner, o.object_name, o.object_type, s.buffer_pool, count (*) buffer
from dba_objects o, v $ bh b, dba_segments s
where b.objd = o.data_object_id
and o.owner = 'U 2'
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;

A record was not selected.

select o.owner, o.object_name, o.object_type, s.buffer_pool, count (*) buffer
from dba_objects o, v $ bh b, dba_segments s
where b.objd = o.data_object_id
and o.owner = 'U3'
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;

A record was not selected.

■ Query execution via DBLINK:

SQL> select count (*) from tab002 @ dbk;

COUNT (*)
----------
69263

conn u3 / u3

select o.owner, o.object_name, o.object_type, s.buffer_pool, count (*) buffer
from dba_objects o, v $ bh b, dba_segments s
where b.objd = o.data_object_id
and o.owner = 'U 2'
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;

A record was not selected. <<<<<<<<<<< Cache has no related information

select o.owner, o.object_name, o.object_type, s.buffer_pool, count (*) buffer
from dba_objects o, v $ bh b, dba_segments s
where b.objd = o.data_object_id
and o.owner = 'U3'
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;

A record was not selected. <<<<<<<<<<< Cache has no related information

===========
DBLINK destination (remote side):

select o.owner, o.object_name, o.object_type, s.buffer_pool, count (*) buffer
from dba_objects o, v $ bh b, dba_segments s
where b.objd = o.data_object_id
and o.owner = 'U 2'
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
----------
U2
TAB 002
TABLE DEFAULT
2042 ★ ★ <<<<<<<<<<<<<<<<<< data got into the cache.

select o.owner, o.object_name, o.object_type, s.buffer_pool, count (*) buffer
from dba_objects o, v $ bh b, dba_segments s
where b.objd = o.data_object_id
and o.owner = 'U3'
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;

A record was not selected.