Search

OakieTags

Who's online

There are currently 0 users and 31 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

The Big Data Job Gap: Where are the Platform Scientists?

Big data continues to live up to its reputation for disruption as it gnaws away at all of the entrenched constituencies – IT silos, vendors, pricing models and now, careers; it’s about to get very personal. On the possibility side, there is a massive skills gap that needs to be filled. Everybody knows there aren't […]

7 Ways Data Virtualization Helps CIOs

IDC study found that Delphix

  1. Pays for itself in 4.3 months
  2. ROI 461% over 5 years
  3. $1 Million storage and hardware savings
  4. $50 Millionannual  savings for companies over 75,000 employees
  5. $78,500 saved per year per 85 employees
  6. $85,000 annua lIT efficiency per 100 employees
  7. 98.6% storage reduction on a 2.42 TB database footprint

Delphix_IDCReport_Infographic

Screen Shot 2014-08-12 at 10.26.54 AM

Screen Shot 2014-08-12 at 10.27.20 AMScreen Shot 2014-08-12 at 10.27.29 AM

A few more 12c articles

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…

Cheers

Tim…

 


A few more 12c articles was first posted on August 28, 2014 at 3:10 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

In-memory Consistency

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 12.1.0.2, of course):


create table t1 nologging
as
select  *
from    all_objects
where   rownum <= 50000
;

insert /*+ append */ into t1 select * from t1;
commit;

insert /*+ append */ into t1 select * from t1;
commit;

insert /*+ append */ into t1 select * from t1;
commit;

begin
        dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size 1');
end;
/

alter table t1
        inmemory priority high memcompress for query low
        inmemory memcompress for query high (object_type)
;

In this case I’ve made the inmemory priority high and I haven’t set any column to “no inmemory” although I have made one column different from the rest (v$_im_column_level doesn’t get populated unless there is some variation across columns). I have to say I couldn’t get very consistent behaviour in terms of when the data finally got into memory with this table creation – possibly something to do with using “alter table” rather than “create table” – but a second “alter table t1 inmemory;” seemed to do the trick if Oracle was playing hard to get.

Once I’d checked that the table was in memory I collected performance figures from v$mystat and v$session_event for the following query:


select
        /* Test Run */
        last_ddl_time
from
        t1
where   t1.created > trunc(sysdate)
and     t1.object_type = 'TABLE'
and     t1.subobject_name is not null
;

Once I was satisfied that the in-memory option was working correctly, I went through the following steps:

  • Session 1: set transaction read only;
  • Session 1: run the query and collect performance figures
  • Session 2: do several small, committed, updates, modifying a total of 30 or 40 random rows
  • Session 2: Flush the buffer cache – so that we can see future block acquisition
  • Session 1: re-run the query and collect performance figures – compare and contrast

The effect of the “set transaction read only;” was to force the session to do some extra work in the second execution of the query to make the data read-consistent back to the start of the “transaction”. The results were as follows (don’t forget that some of the numbers will relate to the action of collecting the performance figures):


First execution
===============
Name                                                                     Value
----                                                                     -----
Requests to/from client                                                      4
opened cursors cumulative                                                    5
user calls                                                                   6
recursive calls                                                              3
session logical reads                                                    6,680
DB time                                                                      1
non-idle wait count                                                          4
consistent gets                                                              3
consistent gets from cache                                                   3
consistent gets pin                                                          3
consistent gets pin (fastpath)                                               3
logical read bytes from cache                                           24,576
calls to kcmgcs                                                              7
calls to get snapshot scn: kcmgss                                            1
table scans (long tables)                                                    1
table scans (IM)                                                             1
IM scan CUs memcompress for query low                                        1
session logical reads - IM                                               6,677
IM scan bytes in-memory                                              5,155,309
IM scan bytes uncompressed                                          45,896,824
IM scan CUs columns theoretical max                                         18
IM scan rows                                                           399,984
IM scan rows optimized                                                 399,984
IM scan CUs split pieces                                                     1
IM scan CUs predicates received                                              3
IM scan CUs predicates applied                                               3
IM scan CUs predicates optimized                                             1
IM scan CUs pruned                                                           1
IM scan segments minmax eligible                                             1
session cursor cache hits                                                    5
workarea executions - optimal                                                1
parse count (total)                                                          4
execute count                                                                5
bytes sent via SQL*Net to client                                         1,150

Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
SQL*Net message to client                             9           0           0.00        .000           0
SQL*Net message from client                           9           0           0.44        .049       8,408

Second Execution
================
Name                                                                     Value
----                                                                     -----
Requests to/from client                                                      4
opened cursors cumulative                                                    5
user calls                                                                   6
recursive calls                                                              3
session logical reads                                                    6,728
DB time                                                                      1
non-idle wait count                                                         35
enqueue requests                                                             2
enqueue releases                                                             2
physical read total IO requests                                             29
physical read total multi block requests                                    24
physical read total bytes                                            6,987,776
cell physical IO interconnect bytes                                  6,987,776
consistent gets                                                             92
consistent gets from cache                                                  92
consistent gets pin                                                         44
consistent gets pin (fastpath)                                               5
consistent gets examination                                                 48
logical read bytes from cache                                          753,664
physical reads                                                             853
physical reads cache                                                       853
physical read IO requests                                                   29
physical read bytes                                                  6,987,776
consistent changes                                                          48
free buffer requested                                                      894
CR blocks created                                                           41
physical reads cache prefetch                                              824
physical reads prefetch warmup                                             713
shared hash latch upgrades - no wait                                        43
calls to kcmgcs                                                              7
calls to get snapshot scn: kcmgss                                            1
file io wait time                                                        3,861
data blocks consistent reads - undo records applied                         48
rollbacks only - consistent read gets                                       41
table scans (long tables)                                                    1
table scans (IM)                                                             1
table scan rows gotten                                                   2,803
table scan blocks gotten                                                    41
IM scan CUs memcompress for query low                                        1
session logical reads - IM                                               6,636
IM scan bytes in-memory                                              5,155,309
IM scan bytes uncompressed                                          45,896,824
IM scan CUs columns theoretical max                                         18
IM scan rows                                                           399,984
IM scan rows optimized                                                 399,984
IM scan rows cache                                                          48
IM scan blocks cache                                                        41
IM scan CUs split pieces                                                     1
IM scan CUs predicates received                                              3
IM scan CUs predicates applied                                               3
IM scan CUs predicates optimized                                             1
IM scan CUs pruned                                                           1
IM scan segments minmax eligible                                             1
session cursor cache hits                                                    5
workarea executions - optimal                                                1
parse count (total)                                                          4
execute count                                                                5
bytes sent via SQL*Net to client                                         1,150
bytes received via SQL*Net from client                                   1,772
SQL*Net roundtrips to/from client                                            4

Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
Disk file operations I/O                              2           0           0.01        .003           0
db file sequential read                               5           0           0.01        .001           0
db file scattered read                               24           0           0.38        .016           0
SQL*Net message to client                            10           0           0.01        .001           0
SQL*Net message from client                          10           0           0.76        .076       8,408

There’s quite a lot of stats which probably aren’t interesting – and there’s one detail that is important but doesn’t appear (at least not clearly) and that’s the fact that the table in question had about 6,800 blocks below its highwater mark.

So, what do the stats tell us? The most obvious change, of course, is that we had to do some physical reads to get a result set: 24 multiblock reads and 5 single block reads (the latter from the undo tablespace). This is echoed in the session stats as 853 “physical reads cache” from 29 “physical read IO requests”. We can then see the specific read-consistency work (in two ways – with a third close approximation):

consistent changes                                                          48
CR blocks created                                                           41

data blocks consistent reads - undo records applied                         48
rollbacks only - consistent read gets                                       41

IM scan rows cache                                                          48
IM scan blocks cache                                                        41

We applied 48 undo change vectors to fix up 41 blocks to the correct point in time and used them to read 48 rows – the last pair of figures won’t necessarily match the first two pairs, but they do give us a measure of how much data we had to acquire from the cache when trying to do an in-memory scan.

The number 41 actually appears a couple more times: it’s “table scan blocks gotten” (which might seem a little odd since we got far more than 41 blocks by multiblock reads – but we only really wanted 41), and it’s also the change (downwards) in “session logical reads – IM”. Even when Oracle does a pure in-memory query it calculates the number of blocks it would have been reading and reports that number as “session logical reads” and “session logical reads – IM” – so there’s another way to get confused about buffer visits and another statistic to cross-check when you’re trying to work out how to calculate “the buffer hit ratio” ;)

After the first read the scattered reads all seemed to be 32 blocks of “intermittent” tablescan – perhaps this is a measure of the number of blocks that are compressed into a single in-memory chunk (for query low), but perhaps it’s a side effect of the “physical reads prefetch warmup” that Oracle may do when the cache has a lot of empty space. I’ll leave it as an exercise to the reader to refine the test (or think of a different test) to determine whether it’s the former or latter; it’s quite important to find this out because if Oracle is tracking change at the “in-memory chunk” rather than at the block level then a small amount of high-precision change to an in-memory table could result in a relatively large amount of “redundant” I/O as a long-running query tried to stay read-consistent.

The ALS Ice Bucket Challenge, the DBA Kevlar Way

I believe you do your best when you follow in what you believe.

Kellyn’s …. Hold the ice…:)



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [The ALS Ice Bucket Challenge, the DBA Kevlar Way], All Right Reserved. 2014.

Oracle performance video library

Here is a series of Oracle performance tuning videos that I put together recently at Delphix. All the presentations are free and open to the public, just have to register.

 

blog_video1

#444444;">Successfully managing a modern organization’s complex data estate requires careful work at multiple layers, from the implementation of organization-wide data policies and platforms to the careful tuning of particular databases. Only with optimization at every level can data achieve its full potential, enabling the organization.
#444444;">In this webinar series, Kyle Hailey demonstrates field-proven best practices to measure and optimize database performance, and to present compelling results to end users.
#444444;">
#444444;">blog_video2blog_video3blog_video4blog_video5

Greening of Storage

By Tim Gorman

#222222;">I recall that the Dec 1999 or Jan 2000 issues of Nat’l Geographic magazine had a “Letters From The Editor” column that speculated, in jest, that the rate at which humans were saving back-issues of Nat’l Geographic magazine, would by the year 2100 result in the total accumulation of yellow magazines outweighing planet Earth.
#222222;" />
#222222;">Anyway, that statement resonated with me, because although I change residences every few years, it has only been recently that I hadn’t packed and carried my decades of accumulated Nat’l Geographic magazines with me.  Now that I’m free of them, I have no idea why I schlepped them with me for so long.  Worse, it cost real money to do so;  movers charge by weight. One mover commented that he was certain that two-thirds of the weight of all my possessions were books and Nat’l Geographic magazines, as he handed me an $8,000 bill for the move.
#222222;" />
#222222;" />#222222;">I now collect books on Kindle.  And I dropped off my boxes of yellow Nat’l Geographic magazines at the Goodwill store, in the middle of a dark and shameful night, almost a decade ago.  I don’t know if it was a particularly “green” decision, but I know that my recent moves have been the easiest since I was an undergraduate.
#222222;" />
#222222;" />#222222;">Likewise in data centers.  If we keep doing business in data centers as we have for the past 30 years, quite soon the planet would tilt off it’s axis due to the sheer weight of data storage hardware.
#222222;" />
#222222;" />#222222;">The advent of virtual machines has had a profound impact on provisioning environments.  Instead of unpacking, racking, wiring, powering, and cooling physical servers, data centers can now create virtual machines by the hundreds by pointing and clicking.  All of these new virtual machines share the previously under-utilized CPU and RAM resources of physical servers, making the ROI on CPU and RAM resources sky high.
#222222;" />
#222222;" />#222222;">So, virtual machine technology has allowed data centers to provision several million virtual servers without having to power and cool several millions of physical servers.  They use the existing physical servers far more efficiently.  That is “green”.
#222222;" />
#222222;" />#222222;">Not so with disk storage.
#222222;" />
#222222;" />#222222;">Each virtual machine still requires a full image of storage.  So, as several million virtual servers have been spun up, each has required a full complement of disk storage, thus driving the already overheated computer storage industry into supernova.
#222222;" />
#222222;" />#222222;">I’ve said it before and say it again:  if you have money to invest, do so in either energy or data storage.  We’re never going to use less of either.
#222222;" />
#222222;" />#222222;">So how does Delphix fit in?
#222222;" />
#222222;" />#222222;">Delphix virtualizes data, just as VMware and their competitors virtualize servers.  Delphix data virtualization makes more efficient use of existing storage, and slows the rate of growth of storage in data centers.

#ff0000;">#000000;">Data virtualization leapfrogs and multiplies the incremental improvements in storage, introducing real agility and increasing the tempo of development operations, ultimately utilizing storage resources more efficiently.
#222222;" />
#222222;" />#222222;">Server virtualization has been a huge initial step, but data virtualization is the final step to fully deliver on the promise of infrastructure virtualization.

#ff0000;">#000000;">Fast, good, cheap. 

#222222;">That is “green”.

 

frog

 

photo by John P Clare

I’ve Got A Ram

Just in case anyone thinks I might lack the passion to make happen what I say I will make happen (end domestic poverty and the war on drugs together with all its bad effects, for starters), here is this link to my youtube channel and a song I sang for the Bel Canto benefit concert (sorry in advance for the poor video focus. The sound should be okay).

https://www.youtube.com/watch?v=5_H5Bq3Kd5E

You can also see me directing the University Chorus of the Upper Valley and plenty of other things on my channel pudge1954.

Enjoy.

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.

OTN APAC Tour 2014

As well as losing the ACED OpenWorld confirmation email, it turns out my website/mailbox move also caused me to lose the email about being accepted on the OTN APAC Tour 2014. I saw a tweet this morning saying that I was on the agenda for the NZOUG event and checked with Francisco to see what was going on. That’s when I found out that yet another important email had gone missing… :)

The good news is I had already agreed the time off work, so everything is good for the tour.

  • Perth, Australia : November 6-7
  • Shanghai, China : November 9
  • Tokyo, Japan : November 11-13
  • Beijing, China : November 14-15
  • Bangkok, Thailand : November 17
  • Auckland, New Zealand : November 19-21

After the OTN Yathra Tour 2014, which was 7 cities in 14 days, I told myself I was too old to do all this tour stuff… So much for that idea… :)

Although it’s a 6 event tour, the first and last events are pretty much like being at home for me. I’ve been to the AUSOUG and NZOUG events a few times, so it will be good to meet up with everyone again… It will be my second time in Beijing, and I’ve seen the traffic in India now, so I’m hoping I will cope better with the roads in China this time. :) Shanghai, Tokyo and Bangkok are all new to me, so it will be interesting to see how I cope with them…

As always before one of these tours, I am a complete mix of excitement, fear and general panic! :)

I look forward to seeing you all there!

Cheers

Tim…


OTN APAC Tour 2014 was first posted on August 26, 2014 at 10:49 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.