Dallas Oracle Users Group Performance Meetup

I spoke at a one day DOUG meeting yesterday. It was pretty cool. Very small intimate group of about 50. The speakers were Nitin Vengurlekar, Charles Kim, Cary Millsap and myself. All are Ace Directors and either work at Viscosity or Enkitec. As a bonus, Tanel Poder showed up to weigh in on some open discussion. Anyway, I thoroughly enjoyed it. I promised the group I would post my slides and a zip file with some of my scripts that I demoed. So here it is (click on the image to download a zip file with PDF and scripts):

Morphine

OOW 2014 Schedule

Here’s a quick rundown of places I plan to be during the week.

Date/Time Title Description
Sunday 9/28/14 3:30 Expert Oracle Exadata: Then and Now [UGF6626] I’ll be participating in the Exadata Then and Now Panel with Tanel Poder, Andy Colvin, Martin Bach, Karl Arao, Frits Hoogland and anyone else we can drag in. The idea is to get book authors from “Apress Expert Oracle Exadata” version 1 and version 2 (due out by the end of the year) to discuss things that have changed since the book was first published in 2011.
Monday 9/29/14 11:30 Engineered Systems General Session[GEN8922] This is an hour and half long session with a bunch of speakers (me and Keith Lippiatt from Accenture/Enkitec, Juan Loaiza, John Fowler, Ganesh Ramamurthy and Michael Workman from Oracle). This will be a more technical talk than you might expect for a keynote. And I’ve heard some of the speakers are dressing down, so I shouldn’t stick out like a sore thumb. :) Here’s a link to the Keynote Video. (my and Keith’s bit is about 3 minutes in)
Tuesday 9/30/14 12:45 How to Hire World Class Oracle Dudes and Dudettes This is a TED talk on how to find exceptional people and how to get them to join your company. Here’s a link to a video of the talk.
Wednesday 10/1/14 3:00 Hanging Out at Jillian’s I’ll just be hanging out with some of the Enkitec guys.
Thursday 10/2/14 10:45 Oracle Database In-Memory In Action[CON6812] This is a joint presentation with Tanel Poder on the new 12.1.0.2 In-Memory Option.


Hope to see you in San Francisco.

12c In-Memory on RAC

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.

SYS@dw1> alter table kso.skew inmemory;
 
Table altered.
 
SYS@dw1> @gen_ddl
Enter value for object_type: 
Enter value for owner: KSO
Enter value for object_name: SKEW
 
DDL
--------------------------------------------------------------------------------
 
  CREATE TABLE "KSO"."SKEW"
   (    "PK_COL" NUMBER,
        "COL1" NUMBER,
        "COL2" VARCHAR2(30),
        "COL3" DATE,
        "COL4" VARCHAR2(1),
         PRIMARY KEY ("PK_COL")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 INVISIBLE COMPUTE STATISTICS
  STORAGE(INITIAL 865075200 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 1480589312 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  INMEMORY PRIORITY NONE MEMCOMPRESS FOR QUERY LOW
  DISTRIBUTE AUTO NO DUPLICATE                             < --- here's the RAC bit
   CACHE
 
SYS@dw1> @inmem_segs
Enter value for owner: 
Enter value for segment_name: 
 
OWNER                          SEGMENT_NAME                   ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
                                                                             ----------------
sum
 
no rows selected
 
SYS@dw1> select count(*) from kso.skew;
 
  COUNT(*)
----------
  32000004
 
SYS@dw1> @inmem_segs
Enter value for owner: 
Enter value for segment_name: 
 
OWNER                          SEGMENT_NAME                   ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
KSO                            SKEW                                  1,413.0            391.4        1.7              749.4
                                                                             ----------------
sum                                                                                     391.4
SYS@dw1> -- so about half the data is loaded in the local instance column store
SYS@dw1> -- let's see what's in the other instance's cache
SYS@dw1> l
  1  SELECT v.owner, v.segment_name,
  2  v.bytes/(1024*1024) orig_size_megs,
  3  v.inmemory_size/(1024*1024) in_mem_size_megs,
  4  (v.bytes - v.bytes_not_populated) / v.inmemory_size comp_ratio,
  5  v.bytes_not_populated/(1024*1024) megs_not_populated
  6  FROM v$im_segments v
  7  where owner like nvl('&owner',owner)
  8* and segment_name like nvl('&segment_name',segment_name)
SYS@dw1> l6
  6* FROM v$im_segments v
SYS@dw1> c/v$/gv$/
  6* FROM gv$im_segments v
SYS@dw1> /
Enter value for owner: 
Enter value for segment_name: 
 
OWNER                          SEGMENT_NAME                   ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
KSO                            SKEW                                  1,413.0            569.1        1.6              526.6
KSO                            SKEW                                  1,413.0            391.4        1.7              749.4
                                                                             ----------------
sum                                                                                     960.5

Continue reading ‘12c In-Memory on RAC’ »

12c In-Memory in PDB

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.

Continue reading ‘12c In-Memory in PDB’ »

The Next Big Thing

Oracle’s 12.1.0.2 was released a few weeks ago (You can download it from OTN here: Oracle 12.1.0.2 Download). While technically a minor point release, it contains a couple of major features that would normally be rolled out in a more substantial version change like 12cR2 or perhaps V13. Of course the most highly anticipated feature is a new option (Oracle In-Memory Option) that provides a column oriented, in-memory store. Enkitec was in the Beta program, so we’ve been testing it out for quite a while now and we are impressed. Here’s a link to a video of a conversation between myself, Tanel Poder and Cary Millsap about the In-memory Option published prior to the general release. Note: the three of us are also scheduled to do a webcast on the topic on Sep. 17th at 9:00AM CDT. You can sign up here if you are interested: In-Memory Webcast

But back to the topic: What this new option provides is a radical departure from the way Oracle has traditionally managed data access. In the past, all data access was done using row-major format, which is a foundation of the Oracle RDBMS architecture (I’m of course leaving out some esoteric formats such as the hybrid columnar compressed (HCC) format that is available on Exadata). At any rate, this columnar format is a major change in the way data is accessed for Oracle, and while the name of the option indicates that the secret sauce is the fact that the data is accessed from memory, I’m going to argue that the “memory” part is not the most important factor. In my opinion, the column-oriented format is why it’s “The Next Big Thing”.

While accessing data from RAM is definitely faster than reading it off disk, it’s important to note that Oracle has been serving data from memory for decades via the standard buffer cache. In fact, you could describe the Oracle RDBMS as a very sophisticated disk caching mechanism. That’s certainly a vast over simplification, but it’s really not too far from reality. Many Oracle systems spend most of their time accessing data from the buffer cache. Back in the day, DBA’s even invented a metric to describe the effectiveness of the caching. The much maligned “buffer cache hit ratio” was used for that purpose and is still present in the modern day AWR reports. While tuning artificial ratios like this one has long since gone out of fashion, it’s important to note that it is not uncommon to see this ratio in the upper 90′s. (i.e. 99% of blocks being accessed from RAM is common) And in fact, we can pin tables in the buffer cache so that all rows are accessed from memory. So if that’s the case, then we should be able to compare speeds of queries executed on data in memory using both the standard row-major format and the new columnar format. Let’s give it a quick try.

Continue reading ‘The Next Big Thing’ »