Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

inmemory: sa00 process

Posted by Riyaj Shamsudeen on September 11, 2014

After the restart of a 12c inmemory database with 300GB+ SGA, I noticed that an Oracle background process sa00 was consuming a bit of CPU. Documentation suggests that it is SGA Allocator process, however, ipcs -ma command shows that the shared memory segment is already allocated. I was curious, of course, what would that background process will be allocating?.

pstack

Process stack of the process shows that it is touching SGA pages to pre-page SGA memory pages.

$ pstack 21131
#0  0x0000000000d9996e in ksmprepage_memory ()
#1  0x0000000000d99369 in ksm_prepage_sga_seg ()
#2  0x0000000003a5c78b in skgmapply ()
#3  0x0000000000da686a in ksmapply_v2 ()
#4  0x0000000000d9a82c in ksmprepage ()
#5  0x0000000000d99f89 in ksm_sslv_exec_cbk ()
#6  0x0000000000f79810 in ksvrdp ()
#7  0x00000000031013b7 in opirip ()
#8  0x0000000001bb0a08 in opidrv ()
#9  0x00000000026c0f71 in sou2o ()
#10 0x0000000000bbd85e in opimai_real ()
#11 0x00000000026cb6bc in ssthrdmain ()
#12 0x0000000000bbd72c in main ()

$ ps -ef|grep  21131
oracle   21131     1 96 15:00 ?        00:01:50 ora_sa00_XXXXXX

Two notable changes in this area:
1. Incidentally, pre_page_sga initialization parameter was defaulted to a value of FALSE until 11.2. In version 12.1, the parameter value defaults to TRUE.
2. As huge SGA is expected for inmemory databases, a new background process SA00 is also created to touch all SGA memory pages at startup.

As inmemory worker processes will be populating the inmemory column store soon after the startup, touching memory pages at instance startup makes sense, and the feature should improve the performance of inmemory population. At least, Worker processes doesn’t need to suffer from huge amount page faults. (note that this SGA is not using hugepages).

Change to the parameter pre_page_sga also should improve the performance of inmemory scan, as the memory map entries will be setup at the process startup. However, I am not quite clear, how this change will affect the performance of a connection storm, i.e. if there are numerous database connections in a short period of time and disconnects. Isn’t that the reason why the pre_page_sga was defaulted to FALSE? But, I need to test this thoroughly to understand the implications further.

4 Responses to “inmemory: sa00 process”

  1. Hanumantha Rao said

    Hi Riyaj Shamsudeen, Thanks for your post and do you observed any changes with this parameter defaulted to TRUE in 12c? is this for a Good cause? Does it will impact the number sessions connected to DB and not disconnecting/releasing the sessions from DB.

  2. Doc ID 1987975.1: In 12c, this has been improved, the time the process takes to connect to the database is almost the same when PRE_PAGE_SGA is set to TRUE and FALSE. This is because the design for SGA prepaging was changed in 12.1, and it helps in preventing foreground processes to take the hit of allocating and locking a large page into physical memory. Limiting the pre-paging activity to just the first process that allocates a granule to the SGA has the biggest impact. The other (minor) contributors are making pre-paging NUMA aware and taking the actual memory page size into account (e.g. huge pages) instead of reading a byte in every fixed 2K increment to trigger a page in operation.

  3. Paul said

    Hi Riyaj
    I was wondering, since the default is now TRUE for 12c : I hope that the startup process (SA00?) only touch SGA pages until sga_target not sga_max_size, otherwise it would definitively defeat the purpose of setting sga_target lower than sga_max_size.
    Regards

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

 
%d bloggers like this: