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.

-bash-3.2$ rlwrap sqlplus / as sysdba
 
SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 12 16:07:31 2014
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
 
 
INSTANCE_NAME    STARTUP_TIME               CURRENT_TIME                  DAYS    SECONDS
---------------- -------------------------- -------------------------- ------- ----------
democ1           12-SEP-2014 09:49          12-SEP-2014 16:07              .26      22675
 
SYS@democ1> select cdb from v$database;
 
CDB
---
YES
 
SYS@democ1> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
 
    PDB_ID PDB_NAME                       STATUS
---------- ------------------------------ ---------
         2 PDB$SEED                       NORMAL
         3 DEMOPDB                        NORMAL
 
SYS@democ1> @parms
Enter value for parameter: inmem
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
inmemory_clause_default                                                                                                   TRUE     FALSE      FALSE
inmemory_force                                     DEFAULT                                                                TRUE     FALSE      FALSE
inmemory_max_populate_servers                      12                                                                     TRUE     FALSE      FALSE
inmemory_query                                     ENABLE                                                                 TRUE     FALSE      FALSE
inmemory_size                                      0                                                                      FALSE    FALSE      TRUE
inmemory_trickle_repopulate_servers_percent        1                                                                      TRUE     FALSE      FALSE
optimizer_inmemory_aware                           TRUE                                                                   TRUE     FALSE      FALSE
 
7 rows selected.
 
SYS@democ1> alter system set inmemory_size=500M;
alter system set inmemory_size=499M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified
 
SYS@democ1> alter system set inmemory_size=500M scope=spfile;
 
System altered.
 
SYS@democ1> startup force
ORACLE instance started.
 
Total System Global Area 8589934592 bytes
Fixed Size                  6877112 bytes
Variable Size            1644167240 bytes
Database Buffers         6257901568 bytes
Redo Buffers              144117760 bytes
In-Memory Area            536870912 bytes
Database mounted.
Database opened.
SYS@democ1> @parms
Enter value for parameter: inmem
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
inmemory_clause_default                                                                                                   TRUE     FALSE      FALSE
inmemory_force                                     DEFAULT                                                                TRUE     FALSE      FALSE
inmemory_max_populate_servers                      12                                                                     TRUE     FALSE      FALSE
inmemory_query                                     ENABLE                                                                 TRUE     FALSE      FALSE
inmemory_size                                      536870912                                                              FALSE    FALSE      TRUE
inmemory_trickle_repopulate_servers_percent        1                                                                      TRUE     FALSE      FALSE
optimizer_inmemory_aware                           TRUE                                                                   TRUE     FALSE      FALSE
 
7 rows selected.

So I logged into a container database that has one PDB (DEMOPDB). I checked to see if there was memory assigned to the column store, and there wasn’t. (inmemory_size was set to the default value of 0). Then I attempted to change the value, which as you can see is not a dynamic component. I had to make the change in the spfile and then restart the entire database including any PDBs.

So we’ve enabled inmemory at the container level. Now let’s check the PDB.

Note: The CDB/PDB is still a little weird for me. I use this little script to set SQL*Plus prompt to keep me straight. connect_pdb.sql

Note 2: Someone else asked about the very simple inmem_segs.sql script so here’s a link to it too: inmem_segs.sql

 
SYS@democ1> @connect_pdb
Enter value for pdb_name: DEMOPDB
 
Session altered.
 
 
 
 
 
SYS@democ1:DEMOPDB> alter system set inmemory_size=0;
alter system set inmemory_size=0
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
 
SYS@democ1> select pdb_id con_id, pdb_name, status, open_mode, restricted from dba_pdbs a, v$pdbs b where a.con_id = b.con_id order by pdb_id;
 
    CON_ID PDB_NAME                       STATUS    OPEN_MODE  RES
---------- ------------------------------ --------- ---------- ---
         2 PDB$SEED                       NORMAL    READ ONLY  NO
         3 DEMOPDB                        NORMAL    MOUNTED
 
SYS@democ1:DEMOPDB> startup
Pluggable Database opened.
SYS@democ1:DEMOPDB> @parms
Enter value for parameter: inmem
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
inmemory_clause_default                                                                                                   TRUE     FALSE      FALSE
inmemory_force                                     DEFAULT                                                                TRUE     FALSE      FALSE
inmemory_max_populate_servers                      12                                                                     TRUE     FALSE      FALSE
inmemory_query                                     ENABLE                                                                 TRUE     FALSE      FALSE
inmemory_size                                      536870912                                                              FALSE    FALSE      TRUE
inmemory_trickle_repopulate_servers_percent        1                                                                      TRUE     FALSE      FALSE
optimizer_inmemory_aware                           TRUE                                                                   TRUE     FALSE      FALSE
 
7 rows selected.
 
SYS@democ1:DEMOPDB> -- inmemory_size value is inherited from CDB as documented. 
SYS@democ1:DEMOPDB> -- let's check which inmem parameters can be set at PDB level
SYS@democ1:DEMOPDB> 
SYS@democ1:DEMOPDB> select name from v$system_parameter where ispdb_modifiable = 'TRUE' and name like nvl('&name',name);
Enter value for name: inmem%
 
NAME
--------------------------------------------------------------------------------
inmemory_size
inmemory_clause_default
inmemory_force
inmemory_query
 
SYS@democ1:DEMOPDB> -- So inmemory_size can be set
SYS@democ1:DEMOPDB> alter system set inmemory_size = 0;
 
System altered.
 
SYS@democ1:DEMOPDB> alter system set inmemory_size = 10M;
 
System altered.
 
SYS@democ1:DEMOPDB> -- no bounce required, that's cool
SYS@democ1:DEMOPDB> -- let's make sure it works
SYS@democ1:DEMOPDB>
SYS@democ1:DEMOPDB> create table kso.junk as select * from dba_objects;
 
Table created.
 
SYS@democ1:DEMOPDB> alter table kso.junk inmemory;
 
Table altered.
 
SYS@democ1:DEMOPDB> select count(*) from kso.junk;
 
  COUNT(*)
----------
     91025
 
SYS@democ1:DEMOPDB> @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                            JUNK                                     13.0              4.1        3.2                 .0
                                                                             ----------------
sum                                                                                       4.1
 
SYS@democ1:DEMOPDB> -- so it works
SYS@democ1:DEMOPDB> alter system set inmemory_size = 0;
 
System altered.
 
SYS@democ1:DEMOPDB> @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                            JUNK                                     13.0              4.1        3.2                 .0
                                                                             ----------------
sum                                                                                       4.1
 
SYS@democ1:DEMOPDB> -- hmmm - doesn't seem to release memory already allocated, not too surprising
SYS@democ1:DEMOPDB> -- let's make sure nothing can be loaded after setting to 0 though
SYS@democ1:DEMOPDB>
SYS@democ1:DEMOPDB> alter table kso.junk inmemory;
 
Table altered.
 
SYS@democ1:DEMOPDB> -- another quirk worth mentioning - "alter table X inmemory" flushes object from column store if it happens to already be there
SYS@democ1:DEMOPDB>
SYS@democ1:DEMOPDB> @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@democ1:DEMOPDB> select count(*) from kso.junk;
 
  COUNT(*)
----------
     91025
 
SYS@democ1:DEMOPDB> -- should repopulate is memory is available
SYS@democ1:DEMOPDB> @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@democ1:DEMOPDB> @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@democ1:DEMOPDB> @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@democ1:DEMOPDB> alter system set inmemory_size = 10M;
 
System altered.
 
SYS@democ1:DEMOPDB> select count(*) from kso.junk;
 
  COUNT(*)
----------
     91025
 
SYS@democ1:DEMOPDB> @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                            JUNK                                     13.0              4.1        3.2                 .0
                                                                             ----------------
sum                                                                                       4.1
 
SYS@democ1:DEMOPDB> -- let's try to make it bigger than the 500M we allocated at the container level
SYS@democ1:DEMOPDB> alter system set inmemory_size = 1G;
alter system set inmemory_size = 1G
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified
 
SYS@democ1:DEMOPDB> -- No can do

So as documented, the PDB specific value of inmemory_size is inherited from the CDB if it is not explicitly set at the PDB level. But we can also modify it (without restarting anything) as long as the value doesn’t exceed the overall value allocated at the container level. Note that this can be used to control how much of the column store memory any one PDB can use and that you can under or over allocate the space as you see fit. However, a single PDB can not specify a value that exceeds the actual value set (and allocated) for the entire container DB as was shown by the last attempted change to set it to 1G when only 500M was allocated by the container DB.

Th-th-th-that’s all folks!

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.

SYS@INMEM> -- note that to enable the In-Memory Option we had to set the INMEMORY_SIZE parameter and bounce the database
SYS@INMEM>
SYS@INMEM> @parms
Enter value for parameter: inmemory_size
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
inmemory_size                                      107374182400                                                           FALSE    FALSE      TRUE
 
Elapsed: 00:00:00.08
SYS@INMEM> -- then we had to tell Oracle that we wanted our table to take advantage of the column store
SYS@INMEM>
SYS@INMEM> alter table kso.skew3 inmemory;
 
Table altered.
 
Elapsed: 00:00:00.57
SYS@INMEM> -- we also told Oracle that we wanted our table to be cached in the normal buffer cache
SYS@INMEM>
SYS@INMEM> alter table kso.skew3 cache;
 
Table altered.
 
Elapsed: 00:00:00.01
SYS@INMEM> @inmem_tables
Enter value for owner: KSO
Enter value for table_name: 
 
OWNER                          TABLE_NAME                     CACHE PRIORITY INMEMORY_DISTRI INMEMORY_COMPRESS
------------------------------ ------------------------------ ----- -------- --------------- -----------------
KSO                            SKEW3                              Y NONE     AUTO            FOR QUERY LOW
 
Elapsed: 00:00:00.09
SYS@INMEM> -- note that the table was accessed in both modes to get it loaded in both the standard buffer cache and the column store
SYS@INMEM> -- (I cut the output to keep from clouding the issue)
SYS@INMEM> -- but you can see the blocks in the buffer cache below
SYS@INMEM> 
SYS@INMEM> @buffers_in_use
Enter value for owner: KSO
Enter value for object_name: SKEW3
 
OWNER                          OBJECT_NAME                    OBJECT_TYPE             BUFFERS
------------------------------ ------------------------------ -------------------- ----------
KSO                            SKEW3                          TABLE                   1305951
 
Elapsed: 00:00:02.37
SYS@INMEM> -- and you can see the table is in the column store as well
SYS@INMEM> 
SYS@INMEM> @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                            SKEW3                                10,210.7          7,278.8        1.4                 .0
                                                                             ----------------
sum                                                                                   7,278.8
 
Elapsed: 00:00:00.00
SYS@INMEM> -- now let's test performance
SYS@INMEM> -- First let's try it from the standard buffer cache
SYS@INMEM>
SYS@INMEM> ALTER SESSION set inmemory_query = disable;
 
Session altered.
 
Elapsed: 00:00:00.00
SYS@INMEM> @avgskew3x
 
AVG(PK_COL)
-----------
 16487500.8
 
Elapsed: 00:00:12.45
SYS@INMEM> /
 
AVG(PK_COL)
-----------
 16487500.8
 
Elapsed: 00:00:12.41
SYS@INMEM>
SYS@INMEM> -- So about 12.5 seconds from the buffer cache
SYS@INMEM> @x
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6vy9zstcnq02k, child number 5
-------------------------------------
select avg(pk_col) from kso.skew3 where col1 between 0 and 1000
 
Plan hash value: 2684249835                           <--- Same as TABLE ACCESS INMEMORY FULL version (see below)
 
-----------------------------------------------------
| Id  | Operation                  | Name  | E-Rows |
-----------------------------------------------------
|   0 | SELECT STATEMENT           |       |        |
|   1 |  SORT AGGREGATE            |       |      1 |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW3 |  64007 | <--- Exadata version of full scan (not necessarily off disk)
-----------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage(("COL1"<=1000 AND "COL1">=0))
       filter(("COL1"<=1000 AND "COL1">=0))
 
 
21 rows selected.
 
Elapsed: 00:00:00.10
SYS@INMEM> -- Now let's try it from the column store
SYS@INMEM>
SYS@INMEM> ALTER SESSION set inmemory_query = enable;
 
Session altered.
 
Elapsed: 00:00:00.00
SYS@INMEM> @avgskew3x
 
AVG(PK_COL)
-----------
 16487500.8
 
Elapsed: 00:00:03.03
SYS@INMEM> /
 
AVG(PK_COL)
-----------
 16487500.8
 
Elapsed: 00:00:03.11
SYS@INMEM>
SYS@INMEM> -- So a little over 3 seconds from the column store
SYS@INMEM> @x
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6vy9zstcnq02k, child number 4
-------------------------------------
select avg(pk_col) from kso.skew3 where col1 between 0 and 1000
 
Plan hash value: 2684249835                           <--- Same as the TABLE ACCESS STORAGE FULL version
 
------------------------------------------------------
| Id  | Operation                   | Name  | E-Rows |
------------------------------------------------------
|   0 | SELECT STATEMENT            |       |        |
|   1 |  SORT AGGREGATE             |       |      1 |
|*  2 |   TABLE ACCESS INMEMORY FULL| SKEW3 |  64007 | <--- new access option
------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - inmemory(("COL1"<=1000 AND "COL1">=0))
       filter(("COL1"<=1000 AND "COL1">=0))
 
21 rows selected.
 
Elapsed: 00:00:00.02

So even though the data was accessed from memory on both tests, the column store was about 4X faster. So the speed up in this admittedly very simple example was not due to the fact that the data was accessed from RAM (because both tests accessed data directly from memory).

A couple of points about this example.

1. I hope you’ll take my word for it that no trickery was performed. The data was stored in standard 8K blocks and I didn’t do anything other than what is in the terminal output.

2. Oracle generally tries to prevent full scans of objects from wiping it out large portions of the buffer cache. This is a wise choice in most systems since the sizes of many databases far exceed the amount of RAM available for caching. However, it is possible to override that behavior by declaring that a table or partition should be cached. That’s what the CACHE table attribute does.

3. Note that the plan was actually different, even though the plan_hash_value didn’t reflect the difference. The first statement has TABLE ACCESS STORAGE FULL but the second version has TABLE ACCESS INMEMORY FULL. The reason the plan_hash_value doesn’t change is because only specific parts of the plan are included in the hash value calculation (see this post for more info: SQL Gone Bad – But Plan Not Changed? In this case, the operation (TABLE ACCESS) and not the options (INMEMORY FULL or STORAGE FULL) are used. This could be problematic as it will make it more difficult to identify a rather major decision by the optimizer (i.e. whether to use the In-Memory column store or not). This is analogous to the situation where a plan on Exadata doesn’t change, but on one execution a full table scan results in a smart scan while on a another execution it doesn’t. Performance can be vastly different even though the plan hash value hasn’t changed. I personally think this is a flaw and the behavior should be changed.

4. Of course the main point was that there is a significant difference in the execution time of the query when using the column store vs. using the buffer cache. In this simple case, the main difference was the fact that the query was very selective and the in-memory column store has a built in form of storage indexes (similar to Exadata storage indexes or the new zone maps) which allow Oracle to skip processing on regions of data via a min/max pruning.

Note that I did generate 10046 traces and the output clearly shows that neither test did any disk i/o.

So clearly the column orientation (and the optimizations enabled by it) makes a huge difference. So what is the secret sauce then? It’s the new format (and the improvement in processing that is possible with data arranged in columnar format) that provides the dramatic speed up. It’s by fundamentally changing the way the data is accessed, as opposed to the fact that the data is accessed from memory, that we are able to get the vast improvement in query execution times.

For those that are not bored out of their skulls at this point, here’s a bit of the tkprof output from the buffer cache test.

 
********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          6          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     12.79      12.82          0    1304305          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     12.80      12.83          0    1304311          0           1
 
Misses in library cache during parse: 1
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3       11.12         14.78
  Disk file operations I/O                        2        0.00          0.00
 
 
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          6          0           0
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
 
    1  user  SQL statements in session.
    2  internal SQL statements in session.
    3  SQL statements in session.
********************************************************************************

So as you can see, there was no time spent waiting on disk I/O.

A couple of final comments and then I’ll shut up. I expect this new feature to drive adoption of 12c. Generally we don’t see mass adoption of new Oracle versions until the second major point release (i.e. 11gR2 or 12cR2). But in this case, I believe the new In-Memory Option provides so much benefit that it will drive some customers to early adoption and will provide enough potential payback to make it worth being one of the pioneers. We already have Exadata customers planning to move to 12.1.0.2 at their next patch cycle. So I expect we’ll have several systems in production by the end of the year. I’m looking forward to seeing how it behaves in the real world.

Well that’s about it for now. I hope to do several posts on the In-Memory Option over the next few weeks. So let me know if you have anything specific you’d like to hear about.

Why I like the term “Research Lab”

I love the term “Research Lab” to refer to our collection of Oracle Engineered Systems. It’s because that’s what we do with the equipment. We test it, we try weird experiments with it, we take it apart with a screw driver and we put it back together again. And lot’s of times we break things. But better that we break them in the lab than on a production system at a client site. The lab is a great learning environment for us and our customers. Enkitec has had a pretty solid lab for a while, but it’s about to get a lot bigger.

Here’s an abbreviated list of the what we expect to end up with in the lab in the next few weeks.

  • Lot’s of Exadata’s (V2, X2, X3, X4)
  • A SPARC SuperCluster
  • A few Exalytics’s
  • At least one Exalogic
  • A couple of Big Data Appliances (BDAs)
  • Several Oracle Database Appliances (ODAs)
  • A ZFS Appliance or 2
  • An Oracle Virtual Compute Appliance (OVCA)

So here’s to the new and improved Research Lab. Hopefully a few of the new systems will have the cool built in beer shelf like out current BDA.

BDA Beer

I’ll post some pictures when the new stuff shows up. :)

Exadata Zone Maps

Just a quick post on a new Exadata feature called Zone Maps. They’re similar to storage indexes on Exadata, but with more control (you can define the columns and how the data is refreshed for example). People have complained for years that storage indexes provided no control mechanisms, but now we have a way to exert our God given rights as DBA’s to control yet another aspect of the database. Here’s a link to the 12.1.0.2 documentation which resides in the Data Warehousing Guide: Zone Map Documentation

Zone Maps are restricted to Exadata storage by the way (well probably they work on ZFS and Pillar too). Have a look at the Oracle error messages file:

 
>grep -i "storage type" $ORACLE_HOME/rdbms/mesg/oraus.msg | grep -i "not supported"
 
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:31969, 00000, "ZONEMAP not supported for table stored in tablespace of this storage type"
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:64307, 00000, " Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type" 
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:64309, 00000, " Hybrid Columnar Compression with row-level locking is not supported for tablespaces on this storage type."
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:65425, 00000, "CLUSTERING clause not supported for table stored in tablespace of this storage type"
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:65451, 00000, "Advanced index compression is not supported for tablespaces on this storage type."

So according to the messages file, there are a handful of features that are restricted in this fashion (Zone Maps, HCC, Attribute Clustering and Advanced Index Compression).

As a bit of totally irrelevant history, zone maps were actually included in the 12.1.0.1 release, but the documentation on them was removed. So they worked, but they were undocumented.

Here’s an example on a 12.1.0.1 DB on a non-Exadata platform.

 
SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 13 15:41:46 2014
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
 
 
INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
LAB1211          13-AUG-2014 09:54 13-AUG-2014 15:41     .24      20820
 
SYS@LAB1211> create table kso.junk1 (col1 number, col2 number) clustering by linear order (col1,col2);
create table kso.junk1 (col1 number, col2 number) clustering by linear order (col1,col2)
*
ERROR at line 1:
ORA-65425: CLUSTERING clause not supported for table stored in tablespace of this storage type
 
 
SYS@LAB1211> create table kso.junk1 (col1 number, col2 number);
 
Table created.
 
SYS@LAB1211> create materialized zonemap skew_zonemap on kso.junk1(col1);
create materialized zonemap skew_zonemap on kso.junk1(col1)
                                                          *
ERROR at line 1:
ORA-31969: ZONEMAP not supported for table stored in tablespace of this storage type

Note that both zone maps and attribute clustering were disallowed with the “not supported for table stored in tablespace of this storage type” error message.

By the way, attribute clustering is another interesting new feature of 12g that allows you to declaratively instruct Oracle to store data on disk in a sorted order. This physical ordering can have big benefit for storage indexes or zone maps (or any btree index where clustering factor is important for that matter). Oracle’s new In-Memory column store also has a min/max pruning feature (storage indexes) which means physical ordering on disk is important with that feature as well.

Anyway, here’s a link to the 12.1.0.2 documentation on attribute clustering which also resides in the Data Warehousing Guide: Attribute Clustering Documentation

And here’s another example using 12.1.0.2 on an Exadata.

 
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 13 15:42:18 2014
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
 
 
INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
INMEM            24-JUL-2014 18:35 13-AUG-2014 15:42   19.88    1717600
 
Elapsed: 00:00:00.00
SYS@INMEM> @test_zonemap
SYS@INMEM> create table kso.junk1 (col1 number, col2 number) clustering by linear order (col1,col2);
 
Table created.
 
Elapsed: 00:00:00.01
SYS@INMEM> create materialized zonemap skew_zonemap on kso.junk1(col1);
 
Materialized zonemap created.
 
Elapsed: 00:00:00.15
SYS@INMEM> 
SYS@INMEM> -- so as expected, we're able to create an attribute clustered table and a zone map on Exadata
SYS@INMEM> 
SYS@INMEM> -- Let's try creating a tablespace that is not on Exa storage (even though the DB is on EXA platform)
SYS@INMEM> 
SYS@INMEM> create tablespace KSO_NON_EXA datafile '/home/oracle/KSO_NON_EXA.dbf' size 100M;
 
Tablespace created.
 
Elapsed: 00:00:00.38
SYS@INMEM> @tablespaces
 
TABLESPACE_NAME STATUS    CONTENTS  LOGGING   EXTENT_MGT ALLOC_TYP SPACE_MGT BLOCK_SIZE PREDICA
--------------- --------- --------- --------- ---------- --------- --------- ---------- -------
CLASS_DATA      ONLINE    PERMANENT LOGGING   LOCAL      SYSTEM    AUTO            8192 STORAGE
EXAMPLE         ONLINE    PERMANENT NOLOGGING LOCAL      SYSTEM    AUTO            8192 STORAGE
KSO_NON_EXA     ONLINE    PERMANENT LOGGING   LOCAL      SYSTEM    AUTO            8192 HOST      <=== 
SYSAUX          ONLINE    PERMANENT LOGGING   LOCAL      SYSTEM    AUTO            8192 STORAGE
SYSTEM          ONLINE    PERMANENT LOGGING   LOCAL      SYSTEM    MANUAL          8192 STORAGE
TEMP            ONLINE    TEMPORARY NOLOGGING LOCAL      UNIFORM   MANUAL          8192 STORAGE
UNDOTBS1        ONLINE    UNDO      LOGGING   LOCAL      SYSTEM    MANUAL          8192 STORAGE
USERS           ONLINE    PERMANENT LOGGING   LOCAL      SYSTEM    AUTO            8192 STORAGE
 
8 rows selected.
 
Elapsed: 00:00:00.02
SYS@INMEM> 
SYS@INMEM> -- note that tablespace KSO_NON_EXA is on local disk, not Exadata storage servers, so PREDICATE_EVALUATION is set to HOST.
SYS@INMEM> 
SYS@INMEM> drop table kso.junk1;
 
Table dropped.
 
Elapsed: 00:00:00.01
SYS@INMEM> create table kso.junk1 (col1 number, col2 number) clustering by linear order (col1,col2) tablespace kso_non_exa;
 
Table created.
 
Elapsed: 00:00:00.01
SYS@INMEM> select owner, table_name, tablespace_name from dba_tables where table_name like 'JUNK1';
 
OWNER                TABLE_NAME                     TABLESPACE_NAME
-------------------- ------------------------------ ---------------
KSO                  JUNK1                          KSO_NON_EXA
 
Elapsed: 00:00:00.01
SYS@INMEM> 
SYS@INMEM> -- wow - that's a bit of a surprise, clustered table create worked on non-Exa storage
SYS@INMEM> -- maybe the check is done on some other level than the tablespace
SYS@INMEM> 
SYS@INMEM> create materialized zonemap skew_zonemap on kso.junk1(col1);
create materialized zonemap skew_zonemap on kso.junk1(col1)
                                                          *
ERROR at line 1:
ORA-31969: ZONEMAP not supported for table stored in tablespace of this storage type
 
 
Elapsed: 00:00:00.00

So as you can see, attempting to create the zone map on non-Exa storage failed as expected. But I was able to create a clustered table on non-Exa storage, which is a little weird. So while the error message for attribute clustering exists in the messages file, it doesn’t appear that there is a check in the code, at least at the tablespace level. I don’t have a 12.1.0.2 install on a non-Exadata platform at the moment to test it out, but if you do, please let me know.

That’s it for now. I hope to do some more detailed posts on In-Memory, Zone Maps, Attribute Clustering in the near future. As always, your comments are welcomed.

The AVOID_FULL hint

I saw this very odd statement on an SAP system last week.

         SELECT /*+ AVOID_FULL ("/bic/xxx") */ * FROM "/BIC/XXX" WHERE "/BIC/XXX"=:A0

I had never seen that hint before so I thought I’d do a little investigation. First I did a quick check on a test case to see if it worked.

SYS@DEMO1> select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=234657;
 
  COUNT(*)
----------
        32
 
SYS@DEMO1> @x
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  adpsagc1zb5fj, child number 0
-------------------------------------
select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=234657
 
Plan hash value: 1638045392
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |           |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| SKEW_COL1 |    32 |   160 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("COL1"=234657)
 
 
19 rows selected.

So in my first test case it clearly caused the optimizer to avoid a full scan on my table. So I then started wondering how long has this hint been around and so I looked in v$sql_hint, but to my surprise it wasn’t there.

SYS@DEMO1> select name, version, inverse from v$sql_hint where upper(name) like '%AVOID%';
 
no rows selected
 
SYS@DEMO1> select name, version, inverse from v$sql_hint where upper(name) like '%FULL%';
 
NAME                                               VERSION    INVERSE
-------------------------------------------------- ---------- ----------------------------------------------------------------
FULL                                               8.1.0
NATIVE_FULL_OUTER_JOIN                             10.2.0.3   NO_NATIVE_FULL_OUTER_JOIN
NO_NATIVE_FULL_OUTER_JOIN                          10.2.0.3   NATIVE_FULL_OUTER_JOIN
FULL_OUTER_JOIN_TO_OUTER                           11.2.0.3   NO_FULL_OUTER_JOIN_TO_OUTER
NO_FULL_OUTER_JOIN_TO_OUTER                        11.2.0.3   FULL_OUTER_JOIN_TO_OUTER

The AVOID_FULL hint is not present in v$sql_hint. But wait, maybe it’s one of those top secret hidden hints like PARALLEL, which is a valid hint but doesn’t show up in v$sql_hint.

 
SYS@DEMO1> select name, version, inverse from v$sql_hint where name like '%PARALLEL%';
 
NAME                                               VERSION    INVERSE
-------------------------------------------------- ---------- ----------------------------------------------------------------
SYS_PARALLEL_TXN                                   8.1.6
NOPARALLEL                                         8.1.0      SHARED
NO_PARALLEL                                        10.1.0.3   SHARED
PARALLEL_INDEX                                     8.1.0      NO_PARALLEL_INDEX
NO_PARALLEL_INDEX                                  8.1.0      PARALLEL_INDEX
 
SYS@DEMO1> -- hmmm there is no PARALLEL hint listed 
SYS@DEMO1> -- but it clearly works
SYS@DEMO1> --
SYS@DEMO1> select count(*) from kso.skew2;
 
  COUNT(*)
----------
  32000004
 
SYS@DEMO1> @x
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  56v09mkbstyaa, child number 0
-------------------------------------
select count(*) from kso.skew2
 
Plan hash value: 4220890033
 
----------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |       | 89256 (100)|          |
|   1 |  SORT AGGREGATE            |       |     1 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| SKEW2 |    32M| 89256   (1)| 00:11:38 |
----------------------------------------------------------------------------
 
 
14 rows selected.
 
SYS@DEMO1> select /*+ parallel 2 */ count(*) from kso.skew2;
 
  COUNT(*)
----------
  32000004
 
SYS@DEMO1> @x
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9rgx66dnd21zj, child number 1
-------------------------------------
select /*+ parallel 2 */ count(*) from kso.skew2
 
Plan hash value: 2117817910
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |       |  3095 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE                |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR               |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE             |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |          |    32M|  3095   (1)| 00:00:25 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| SKEW2    |    32M|  3095   (1)| 00:00:25 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - storage(:Z>=:Z AND :Z<=:Z)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 32 because of degree limit
 
 
27 rows selected.

So the PARALLEL hint is not listed but it clearly is a valid hint (even though the SHARED hint is documented as the inverse of the NOPARALLEL hint). So maybe this AVOID_FULL hint is one of those corner cases. So I did some more testing and found a special case where the hint didn’t work as I expected. When I set parallel_degree_policy to LIMITED and decorated my table with a degree setting of DEFAULT I got this behavior.

SYS@DEMO1> select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=1;
 
  COUNT(*)
----------
   3199971
 
SYS@DEMO1> @x
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  591tybw6c8vth, child number 0
-------------------------------------
select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=1
 
Plan hash value: 578366071
 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |       |       |  1548 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE                |          |     1 |     5 |            |          |        |      |            |
|   2 |   PX COORDINATOR               |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000 |     1 |     5 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE             |          |     1 |     5 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |          |  3023K|    14M|  1548   (1)| 00:00:13 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| SKEW     |  3023K|    14M|  1548   (1)| 00:00:13 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - storage(:Z>=:Z AND :Z<=:Z AND "COL1"=1)
       filter("COL1"=1)
 
 
24 rows selected.

The optimizer picked a full scan, despite the hint to the contrary. My next thought was to try Wolfganging the statement (generating 10053 trace) to see if maybe the hint syntax was slightly off and so it was silently ignored. (I wish there was a setting to throw an error when an invalid hint is specified in a statement by the way, but as far as I know there isn’t such a switch). Anyway, here’s the trace bit.

SYS@DEMO1> @wolfgang
 
VALUE
----------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/demo/DEMO1/trace/DEMO1_ora_16420.trc
 
 
Session altered.
 
SYS@DEMO1> select /*+ avoid_full(a) 2 */ count(*) from kso.skew2 a where col1 is not null;
 
  COUNT(*)
----------
  32000003
 
SYS@DEMO1> !vi /u01/app/oracle/diag/rdbms/demo/DEMO1/trace/DEMO1_ora_16420.trc
 
. . .
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "KSO"."SKEW2" "A" WHERE "A"."COL1" IS NOT NULL
. . .
Dumping Hints
=============
====================== END SQL Statement Dump ======================

Not too helpful, the trace didn’t say whether the hint was valid or not. In fact, it didn’t even mention it at all. Strange. So then I thought I’d go back to the production system and see what the plan looked like there. Sure enough, the hint wasn’t working on the production system either – the plan was a full scan on the table.

So the bottom line is there is this new (to me) hint that has been around for some time (I don’t know how long because it’s not documented) that seems to work sometimes but not all the time (but since it’s not documented I don’t know the syntax – so I may just be messing it up). Anyway, due to this erratic behavior you should definitely use it with care. :)

Happy April Fools Day!

Note: I actually saw this statement in a production system.