In case you hadn’t noticed it, partitioning has finally reached clusters in 12c – specifically 18.104.22.168. 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('22.214.171.124')*/ 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:
A few more 12c articles went live over the last few days…
The DMU and In-Database Archiving are from the OCP syllabus. The Invisible Columns stuff seemed like a natural thing to mention, when discussing the In-Database Archiving.
The 12c journey continues…
A comment on one of my early blogs about the 12c in-memory database option asked how Oracle would deal with read-consistency. I came up with a couple of comments outlining the sort of thing I would look for in a solution, and this note is an outline on how I started to tackle the question – with a couple of the subsequent observations. The data is (nearly) the same as the data I generated for my previous article on the in-memory database (and I’m running 126.96.36.199, of course):