Many Oaktable members are planning to talk about deep technical topics in Oaktable world 2014. Looking at the agenda, I am excited, so many deep topics are planned. I will be talking about in-memory internals on Monday morning at 9AM, 9/29/2014, right after Mogens’ Keynote speech. You can find all details here: Oaktable world 2014. I will post my presentation slides after the presentation.
Start your open world week presentation with mine :). Sorry, no beers planned at that time, it is 9AM, after all!
Thanks for attending my presentation at Oaktable World 2014. You can download the slides : In-memory_internals.pdf.
I recently put some more PL/SQL new features articles live.
I’ve also posted a top-level new features article.
In case you hadn’t noticed it, partitioning has finally reached clusters in 12c – specifically 126.96.36.199. They’re limited to hash clusters with range partitioning, but it may be enough to encourage more people to use the technology. Here’s a simple example of the syntax:
I started looking into In-Memory on RAC this week. Data can be distributed across RAC nodes in a couple of different ways. The default is to spread it across the available nodes in the cluster. So if you had a 2 node cluster, roughly 50% of the data in your table or partition would be loaded into the column store in each of the 2 instances.
Some more 12c articles have trickled out over the last few days.
In preparation for our upcoming 12c In-Memory Webcast @CaryMillsap, @TanelPoder, and I solicited questions from members of the universe at large on the interweb. We got a question about how In-Memory works with the 12c multi-tentant option and it got me thinking so I gave it a quick try. As it turns out, it works about as you would expect. The basic idea is to turn it on for the container DB (which is where the memory is actually allocated (ala the other main shared memory regions) and then decide which PDBs to allow to use it (and if so how much of it to use) or not. First, here are the steps necessary to allocate the memory in the container DB.
I enabled an huge 70G table for inmemory population, I expected the inmemory population to take a while, but the population didn’t complete even after letting it run for a day. Why?
Initial review of the server shows no issues, no resource starvation. This must be a problem with Oracle processes itself. I started digging further, and ASH data shows that in numerous samples the process was seen reading block using single block I/I calls. Also object_id matches with the table I was trying to populate.
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?.
Process stack of the process shows that it is touching SGA pages to pre-page SGA memory pages.
I have been testing the inmemory column store product extensively and the product is performing well for our workload. However, I learnt a bit more about inmemory column store and I will be blogging a few them here. BTW, I will be talking about internals of inmemory in Oaktable world presentation, if you are in the open world 2014, you can come and see my talk: http://www.oraclerealworld.com/oaktable-world/agenda/
This just in from OTN Database Forum – a surprising little bug with “group by elimination” exclusive to 12c.
alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss'; select /* optimizer_features_enable('188.8.131.52')*/ trunc (ts,'DD') ts1, sum(fieldb) fieldb from ( select ts, max(fieldb) fieldb from ( select trunc(sysdate) - 1/24 ts, 1 fieldb from dual union all select trunc(sysdate) - 2/24 ts, 2 fieldb from dual union all select trunc(sysdate) - 3/24 ts, 3 fieldb from dual union all select trunc(sysdate) - 4/24 ts, 4 fieldb from dual union all select trunc(sysdate) - 5/24 ts, 5 fieldb from dual ) group by ts ) group by trunc (ts,'DD') /
You might expect to get one row as the answer – but this is the result I got, with the execution plan pulled from memory: