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 220.127.116.11.6
- HugePages_Total: 4001
- Hugepagesize: 2048 kB
Using the same FAKE NUMA server as on my previous posts.
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 :
You can redo the test with “_enable_NUMA_support” set to TRUE and compare the result.
That’s it 😀