Skip to Main Content
  • Questions
  • v$bh@oracle vs innodb-buffer-page@mysql

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, wei.

Asked: September 06, 2018 - 2:14 am UTC

Last updated: September 08, 2018 - 10:23 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

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 :)

and Connor said...

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.




Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

wei yao, September 06, 2018 - 6:23 am UTC

Thanks for reply.
And i want to know why there is difference between mysql and oracle,
about query on innodb_buffer_page vs query on v$bh
on performance impact,
it seems query on v$bh have less or much less performance impact compre to query on innodb_buffer_page in mysql,
at least no document pointed out this.
Why? :)
Connor McDonald
September 08, 2018 - 10:23 am UTC

And i want to know why there is difference between mysql and oracle,

Besides being two totally different products you mean ? :-)

Rest assured - pound away at v$bh, or any number of other v$ views and you'll have a performance impact. *All* v$ view access should be done sensibly and measure for impact.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library