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)

TEST SERVER :

  • OEL 6 UEK R4
  • ORACLE 12.1.0.2.6
  • 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 (SELECT PAGE_ADDRESS,
                TO_NUMBER (SUBSTR (PAGE_ADDRESS, 3), 'XXXXXXXXXXXXXXXX')
                   AS page_address_begin,
                  TO_NUMBER (SUBSTR (PAGE_ADDRESS, 3), 'XXXXXXXXXXXXXXXX')
                + 2097151
                   AS page_address_end,
                NUMA_NODE
           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
GROUP BY NUMA_NODE;

 

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s