“Stolen” CPU on Xen-based virtual machines

in

I’ve written previously about how VMWare ESX manages CPU and how to measure your“real” CPU consumption if you are running an database in such a VM. 

VMware is currently the most popular virtualization platform for Oracle database virtualization, but Oracle’s own Oracle Virtual Machine uses the open source Xen hypervisor, as does Amazon’s Elastic Compute Cloud (EC2): which runs quite a few Oracle databases.    So Oracle databases – and many other interesting workloads – will often be found virtualized inside a Xen-based VM.

I recently discovered that there is an easy way to view CPU overhead inside a Xen VM, at least if you are running a paravirtulized linux kernel 2.6.11 or higher.

Read More

Disabling the Intel X-25 E write cache

in
In my last posting on using SSD with Oracle, I said how impressed I was with the X-25 E SSD write performance.  However, at the OReilly MySQL conference last month, I attended a talk by Vadim Tkachenko and Morgan Tocker from Percona on An Overview of Flash Storage for Databases.  It was a great talk overall, but one important thing I learned is that the X-25 E has a volatile 64MB write cache.  What this means is that the X-25 can report that a block is written to disk when it is still within a RAM buffer within the device.  If the disk failed between the write to RAM and the write to flash then the data could be lost.
Read More

ESX CPU optimization for Oracle Databases

in

In the last post, I talked about managing memory for Oracle databases when running under ESX.  In this post I’ll cover the basics of CPU management.

ESX CPU Scheduling

 

Everyone probably understands that in a ESX server there are likely to be more virtual CPUs than physical CPUs.  For instance, you might have an 8 core ESX server with 16 virtual machines, each of which has a single virtual CPU.  Since there are twice as many virtual CPUs as physical CPUs, not all the virtual CPUs can be active at the same time.  If they all try to gain CPU simultaneously, then some of them will have to wait.

In essence, a virtual CPU (vCPU) can be in one of three states:

Read More

Memory Management for Oracle databases on VMWare ESX

in

The trend towards virtualization of mid-level computing workloads is progressing rapidly.  The economic advantages of server consolidation and the – often exaggerated – reduction in administration overheads seem pretty compelling.  And virtualized servers are quicker to provision and offer significant advantages in terms of backup, duplication and migration.

The virtualization of Oracle databases has proceeded more slowly, due to concerns about performance, scalability and support.  Oracle corporation has given mixed messages about support for virtualized databases,  though they currently appear to have conceded that Oracle databases on VMWare are supported, at least for single instance databases (see ).

Oracle would prefer that we use their Xen-based virtualization platform, but they face an uphill battle to persuade the data centers to move from ESX, which is established as a defacto platform in most sites.

So like it or not, we are probably going to see more databases running on ESX and we’d better understand how to manage ESX virtualized databases.  In this post, I’m going to discuss the issues surrounding memory management in ESX.

Read More

Flash tablespace vs. DB Flash Cache

in

In this post I'm going to report on some performance comparisons I've recently conducted on using SSD flash storage for datafiles vs. using the new Oracle 11GR2 database flash cache.  

It sometimes seems like I’ve been waiting for the the end of the spinning magnetic disk all my professional  life.   The technology is so ancient, so clearly limited and (argh) mechanical.  So the fact that Solid State Disk (SSD) is becoming more an more practical as a database storage medium - and directly supported by Oracle in 11GR2 - is very exciting.  

Using SSD as part of database storage can certainly yield big results, but it's important to understand the performance characteristics of flash SSD and make sure we don't use it inappropriately. 

Read More

Best practices with Python and Oracle

in

This is the third in a series of postings outlining the basics of best practices when coding against Oracle in the various languages.  In Oracle Performance Survival Guide (and in it's predecessor Oracle SQL High Performance Tuning) I emphasise the need to use bind variables and array processing when writing Oracle programs.   The book provides examples of both in PL/SQL and Java, while these postings outline the techniques in other languages. 

Previous postings covered .NET languages and perl. 

Read More

Histograms of histograms

in

 

One of our TOAD customers asked us if we could show a chart of CBO histogram values so that they could observe the skew in column values.   We don't currently, but will in an upcoming version of SQL Optimizer (part of TOAD DBA and Developer suites).  In the meantime, the idea of have an SQL that generated a histogram of a histogram seemed appealing, so here's some SQL to do that. 

Read More

The 11GR2 IGNORE_ROW_ON_DUPKEY_INDEX hint

in

One of the strangest new features in 11GR2 is the new IGNORE_ROW_ON_DUPKEY_INDEX hint.  When this hint is applied to an INSERT statement, any duplicate key values that are inserted will be silently ignored, rather than raising an ORA-0001 statement. 

Why is this strange?  Mainly because unlike almost all other hints, this hint has a semantic effect - it changes the actual behavior - not the optimization of the SQL.  In my opinion, clauses that affect the contents of the database should be contained in official SQL syntax, not embedded in a comment string as a "hint".  The Oracle documentation acknowledges the uniqueness of the hint:

Note:

The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in "Hints" does not apply for these three hints.

 Given all that, I'd be reluctant to use such a hint unless there was a compelling performance advantage.  So, let's see if there's any performance justification for this strange hint. 

Read More

More on the database flash cache

in

I'm eagerly awaiting my high-performance flash SSD (an Intel X-25 E), but in the meantime I've played a bit more with the database flash cache on the cheap hardware setup described in my last post.   Sometimes it can be useful to test new features on slow hardware, since you see phenomenon that don't occur when everything is running full speed.

I originally naively imagined that blocks would be copied into the flash cache by the Oracle server process .  Eg, that if I read from disk, I deposit the block in both the buffer cache or the flash cache.  However,  upon investigation it appears that blocks are moved from the buffer cache to the flash cache by the DBWR as they are about to be flushed from the buffer cache.  

This is of course, a much better approach.  The DBWR can write to the flash cache asynchronously, so that user sessions get the benefit - less time reading from magnetic disk - without having to wait while blocks are inserted into the flash cache. 

 

So the lifecycle of a block looks something like this:

Read More

Using the Oracle 11GR2 database flash cache

in

Oracle just released a patch which allows you to use the database flash cache on Oracle Enterprise Linux even if you don't have exadata storage.  The patch is the obscurely named:

  •    8974084:META BUG FOR FLASH CACHE 11.2PL BUGS TO BACKPORT TO 11.2.0.1 OEL

Once you install the patch you can use any old flash device as a database flash cache.  Below I've documented some initial dabbling on a very old server and a cheap usb flash device.   The results are not representative of the performance you'd get on quality hardware, but are still interesting, I think.

Read More

Performant Oracle programming: perl

in

In the last installment, we looked at the fundamentals of efficient Oracle programming with C#.  In particular, we saw how to use bind variables and how to perform array processing.  Now lets take a look at perl.

I have a strong affection for perl:  I started using perl somewhere around 1992 and did some early hacking to allow the Oracle interface of the day - oraperl - to use stored procedures.  My very first true performance monitoring tools were all written using perl.  Perl has always had good support for Oracle and it's possible to write very efficient and powerful Oracle database utilities in perl.

You can get the perl program that implements the examples in this posting here. 

Read More

Oracle performance programming: .NET

in

In Oracle SQL High Performance tuning, I included an appendix in which I outlined how to achieve good cursor management, bind variables and array processing in the major languages of the day.   I had intended to do the same in Oracle Performance Survival Guide, but I ran out of time and space in the book.  So the examples in the book are generally in Java or PL/SQL only.

I wanted to get up to date on the various languages, some of which (like Python) I haven't used for a while and others (Ruby for instance) I've never used with Oracle.  So I thought I'd kill two birds with one stone by writing a series of blog posts on how to program efficiently in the various languages.

There's lots of best practices in each language, but I think most of us would agree that you at least need to know how to do the following:

  1. Use bind variables appropriately.
  2. Ensure you are performing array fetch when retrieving more than one row
  3. Ensure that you use array insert when doing bulk inserts 

The ways of doing this are different in each language.  In Chapter 6 of Oracle Performance Survival Guide,  I describe these techniques and their performance implications, using Java and sometimes PL/SQL examples.  In this series I'll go through the techniques for other languages. 

Let's start with ODP.NET, which is Oracle's ADO.NET driver for .NET languages such as C#, VB.NET and Powershell (see here for a posting on using Oracle with powershell). 

Read More