From
https://docs.oracle.com/cd/E17952_01/mysql-5.6-en/innodb-information-schema-buffer-pool-tables.html warning that
Querying the INNODB_BUFFER_PAGE or INNODB_BUFFER_PAGE_LRU table can can affect performance.
From the source code i can find that some mutex acquire/release maybe the performance bottleneck.
And on oracle document, there is no warnings about query v$bh on product system.
It seems there is no performance affect.
I think query on v$bh also need to visit the whole buffer of buffer_cache in sga,
and the buffers are protected by latch.
Why there is different between oracle and mysql?
Thanks a lot :)
Just about every v$ view in the database is a facade over an underlying memory structure, not a physical table. So for (most of) those views, we need to protect that structure when you query them to avoid chasing memory pointers that change whilst the query is running - you could head off into all sorts of memory areas.
So yes, we protect those structures with latches, for example, I ran this:
select name, gets, misses
from v$latch;
declare
x int;
begin
for i in 1 .. 100 loop
select max(objd) into x from v$bh;
end loop;
end;
/
select name, gets, misses
from v$latch;
and compared before and after figures. In my case, I took over 3million 'cache buffers chains' latches. It's the same reason we have some v$ structures designed particularly for monitoring performance that can hence be queried more aggressively, eg v$sqlstats, v$active_session_history
So with all v$ views, querying should be done in a "sensible" manner where 'sensible' depends on the object in question. For example, v$session is typically a small, relatively fixed size object, so querying it frequently would be much less problematic than querying something like v$sql or v$bh, especially in these modern times of near terabyte sized memory areas for the database.