Friday Jul 24, 2015

What to do with optimizer statistics when upgrading to 12c to take advantage of Database In-Memory

Before most customers can take advantage of Database In-Memory they will need to navigate the tricky terrain of a database upgrade. One of the most challenging aspects of an upgrade is figuring out how to minimize performance regressions due to execution plan changes.

And if that wasn’t enough to handle, the introduction of Database In-Memory into a 12c environment has the potential to change even more of the execution plans. So, what should you do?

[Read More]

Tuesday Jul 21, 2015

Oracle Database In-Memory Bundle Patch 10 Released

The latest Bundle Patch for Database In-Memory has been released. The Bundle Patch is 21188742 or 12.1.0.2 Bundle Patch 10 for Engineered Systems and DB In-Memory (July2015)). This Bundle Patch improves the performance of mixed workload environments (OLTP & DW workloads), as well as enhancing the performance of analytic queries with aggregation. More information on the latest Bundle Patch can be found in the MOS note 21188742.8 or in the Mos note: 12.1.0.2 Bundle Patches for Engineered Systems and DB In-Memory (Doc ID 1937782.1).

[Read More]

Friday Jul 17, 2015

Star Schema Challenge - Part 3

In my previous posts on the Star Schema Challenge, I established baseline results for query performance with row store tables (that is, without using Database In-Memory). A 9 dimensional, 500 million row star schema supported 25 users with a median query time of 16.9 seconds. (With a median of 16.9 seconds, there was not much point in increasing the number of users with the row store tables). The same data in a 500 million row de-normalized table supported 25 users with a median query time of 33.5 seconds per query.

Remember all of these tests are being conducted on a 60 core Intel server with commodity disk and 1 TB of DRAM.

Row Store Query

Median query performance (in seconds) of the query workload with 25 users on row store tables.

[Read More]

Friday Jul 10, 2015

Star Schema Challenge - Part 2.1

In Star Schema Challenge – Part 2 I revealed baseline results for my query workload running on a 500 million row star schema with all tables marked NO INMEMORY and In-Memory Aggregation prevented using the NO_VECTOR_TRANSFORM hint. With a median of 16.9 seconds per query (allowing for an average think time between queries of 5 seconds), I decided that 25 users were enough for a baseline.

After my first post in this series someone suggested that the best implementation might be a de-normalized table. I decided to satisfy this user’s curiosity and test a de-normalized table also, starting with NO INMEMORY test. (Vector Transform is not applicable to the de-normalized table because it does not join to another table.)

[Read More]

Friday Jul 03, 2015

Oracle Database In-Memory Bundle Patch 9 Released

The latest Bundle Patch for Database In-Memory has been released. The Bundle Patch is 21053000 or 12.1.0.2 Bundle Patch 9 for Engineered Systems and DB In-Memory (June2015)). More information on the latest Bundle Patch can be found in the MOS note: 12.1.0.2 Bundle Patches for Engineered Systems and DB In-Memory (Doc ID 1937782.1), and for for specific details on Bundle Patch 9 see MOS note 21053000.8.

[Read More]

Friday Jun 26, 2015

Do I really have to drop all of my reporting indexes?

I'm back on the road this month, meeting with customers to discuss their initial impressions and experiences with Oracle Database In-Memory. During one such discussion, I got asked a very peculiar question. The question was, "Do I really have to drop all of my reporting indexes if I use Database In-Memory?"

I have to admit I was a little taken aback by this question. After all, I thought most folks would be delighted to have an opportunity to give up the majority of their indexes, not just because of the space savings and DML performance benefits but also the maintenance nightmare that indexes can sometimes become.

Assuming this was a trick question, I deployed the standard stalling technique of answering a question with a question, “Can you tell me a little more about your situation?”

To which the system architect explained that they were in production with Oracle Database In-Memory on a 2 node RAC cluster running on commodity servers and a crap IO subsystem (his words, not mine). They had a snowflake schema, and had enough memory to accommodate all of their dimension tables but only the last 3 months of data in their two fact tables. Following my guidelines, they had kept their primary key indexes but dropped the rest of their indexes. He assured me that the performance of most of their queries had improved 100X and their ETL jobs were finishing 2X faster without the indexes but there were some queries that accessed more than just the last 3 months worth of data in the fact table and their performance had gotten worse, a lot worse.

It was in that moment that I realized that our guidance on dropping all reporting indexes with Database In-Memory had been based on an assumption that was not always true. The assumption I had been working under was; all of your performance critical data resides in memory or you have a good IO sub-system (engineered system etc.)

[Read More]

Friday Jun 19, 2015

Star Schema Performance Challenge - Part 2

In my previous post, Star Schema Performance Challenge – Part 1, I outlined a challenge to support 100 concurrent users querying a 9 dimensional, 500 million row star schema using a Sun X4-4 (with 60 cores, 1 TB DRAM and commodity storage).  The users, of course, expect great query performance.  The challenging part of this is 100 active users on a 60 core machine.  With far fewer or less active users this might not be so challenging, but 100 users on this size machine is a different story.

In this post I’ll talk more about the workload and share some baseline results.

[Read More]

Monday Jun 15, 2015

SQL Plan Management

Have you ever upgraded something to a new version, like your phone or your laptop, and found that while most of it worked great there were just a few things that were worse or even broken? You might have decided to restore back to the previous version because the problem(s) were just too annoying or catastrophic to ignore. Well this was one of the motivations behind the release of SQL Plan Management back in Oracle Database 11g. SQL Plan Management (SPM) provides a framework for completely transparent, controlled execution plan evolution. The main idea being that your application should see no performance regressions due to execution plan changes when you upgrade or change something in your database environment.

[Read More]

Wednesday Jun 03, 2015

Oracle Database In-Memory Bundle Patch 8 Released

As most of you know, we are releasing regular bundle patches for Oracle Database In-Memory. These bundle patches are available periodically through My Oracle Support.

The latest bundle patch to be released was bundle patch 8 or 12.1.0.2.8 (DATABASE PATCH FOR ENGINEERED SYSTEMS AND DB IN-MEMORY 12.1.0.2.8). More information on the latest bundle patch can be found in the MOS note, Doc ID. 20879108.8 or 1937782.1.

The bundle patches are cumulative, which means bundle patch 8 includes all fixes from bundle patch 1 to 7 in addition to new content. Don't let the name of the bundle patch fool you. You can apply this bundle patch on non-engineered systems running Oracle Database In-Memory.

Going forward, we will keep you posted when a new bundle patch is released, so you will always know what is the latest bundle patch. We recommend that you always run with the latest bundle patch.

Friday May 22, 2015

Memory Usage with Oracle Database In-Memory

We often get asked the same two questions at conferences and customer presentations. That is  can't I just cache my tables in the buffer cache or put them in the keep pool, and how much memory do I need to allocate to the IM column store? While not directly related, I'm going to try and answer both questions in this post. First let's tackle the issue of what "in-memory" mean.

[Read More]

Monday May 18, 2015

Star Schema Performance Challenge - Part 1

Over the last 20 years or so I’ve had the opportunity to help build a variety business intelligence solutions for large organizations. It will be no surprise that one of the core requirements is fast query response times. This requirement for fast response times seems pretty obvious – faster response timeslead to greater end user satisfaction. The performance of a database can also determine what types of applications are practical. Breakthroughs in performance often lead to innovating new applications that can change how people work. A more efficient application is usually less expense to operate since it might not require as large of a server.

Oracle Database In-Memory and the availability of more powerful servers with many cores and plenty of memory really is a game changer, when it comes to improving query response times. With Database In-Memory far fewer CPU cycles are used to scan, filter, join and aggregate data. With modern servers, there are plenty of CPU cycles available for processing and  DRAM is relatively inexpensive. So, Database In-Memory can do more with less and, since Database In-Memory is a feature of the Oracle Database that works with all of the others performance enhancing features of the Oracle Database, it has become yet another tool in my big tool box of performance improving techniques.

[Read More]

Friday May 08, 2015

Getting started with Oracle Database In-Memory Part V - Controlling Access

I’m finally going to make good on a promise I made way back in part 3 of our getting started with In-Memory series, to explain how you could control which queries use the In-Memory column store (IM column store) and which don't.

As with all new query performances enhancing features in the Oracle Database, a number of initialization parameters and hints have been introduce that enable you to control when and how the IM column store will be used. This post provides information on the initialization parameter, while the details on the Optimizer hint that control the use of the IM column store can be found on the Optimizer blog.

[Read More]

Thursday Apr 30, 2015

When to use Oracle Database In-Memory?

At this stage you've probably heard a lot about how Oracle Database In-Memory is an unprecedented breakthrough in Oracle database performance, offering incredible performance gains for a wide range of workloads. What you might not know is when and where it would be best to take advantage of this exciting new technology.

So, we've put together a new whitepaper to share some of the high-level use cases, and explain the scenarios under which Database In-Memory provides a performance benefit. The purpose of this paper is to give you some general guidelines so that you can determine whether your use case is a good match.

[Read More]

Tuesday Apr 28, 2015

Discussing In-Memory Databases at The HIVE event in Palo Alto on May 6, 2015

If you happen to be in the San Francisco Bay Area on May 6th, you might be interested in checking out a panel discussion on In-Memory Databases brought to you by The HIVE. The discussion is intended to explore use cases for In-Memory Database technologies and the impact of technologies and trends such as flash memory and cloud computing.

The panel will be made up of industry experts from all of the leading In-Memory Database providers including Oracle, IBM, Aerospike, MemSQL and Pivotal. The session will be moderated by Nik Rouda and my boss, Tim Shetler will be representing Oracle. Noticeably absent are SAP (The SAP Sapphire conference is taking place) and Microsoft. Dozens of other smaller vendors no doubt would like to take part, if you buy into their definition of "In-Memory Database". 

So what does it take to qualify as an "In-Memory database"?

[Read More]

Friday Apr 24, 2015

In-Memory Priority

I'd like to thank everyone who attended our two Hands On Lab sessions at Collaborate last week. We had a great time presenting them and we received some really good feedback. One of the questions that came up, and gets asked periodically, is how does In-Memory priority really work?

Many times people have the misconception that the In-Memory priority attribute affects more than just the order of population but that’s not the case. The only thing the priority affects is the order of population at database startup. We discussed population briefly some time ago, but I think it's worth a quick review.

[Read More]
About

The Oracle Database In-Memory blog is written by the Oracle product management team and sheds light on all things In-Memory.

Search

Archives
« August 2015
SunMonTueWedThuFriSat
      
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
     
Today