Mapping table blocks to NUMA nodes : Scattered or Clustered ?

Depending on the location of table blocks in the buffer cache performance may be affected (Ex : Tables blocks scattered across all the nodes or clustered on one node). In this blog post i will show quickly how to map table blocks to NUMA nodes based on my previous work done in this blog post.(Mapping ORACLE SGA components to numa nodes using NUMA API)


  • OEL 6 UEK R4
  • HugePages_Total:    4001
  • Hugepagesize:       2048 kB
  • *.pga_aggregate_target=400M
  • *.sga_target=8000M

Using the same FAKE NUMA server as on my previous posts.

Capture 1

NOTE : For  this example and all the previous one i haven’t tested with the parameter “_enable_NUMA_support” set to TRUE as this caused me some problem when using fake numa server.

Use the C program  “sga_page_to_node” to map shared memory segment pages to numa nodes.

./sga_page_to_node 429850628 3992 > page_to_node.txt

Create a big table and load it in the buffer cache.

create table numa_distrib tablespace DB_DATA64 as select rpad('X',2000,'X') as a from dual connect by level < 50000;
alter session set events '10949 trace name context forever, level 1';
select count(*) from numa_distrib;

Using the Data_object_id of the table use this query to map table blocks to NUMA nodes

WITH page_to_node
                   AS page_address_begin,
                + 2097151
                   AS page_address_end,
           FROM ext_tab)
  SELECT NUMA_NODE, COUNT (*) block_per_node
    FROM x$bh b, page_to_node p
   WHERE     b.obj = 56800
         AND TO_NUMBER (BA, 'XXXXXXXXXXXXXXXX') BETWEEN page_address_begin
                                                    AND page_address_end


Note : ext_tab is an external table as created in my previous post

And this is the result :

Capture 0

You can redo the test with “_enable_NUMA_support” set to TRUE and compare the result.

That’s it 😀

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s