Kevin Kempf's Blog

April 23, 2009

Oracle 11i on 11g – Success!

Upgraded our production RDBMS from 10.2.0.3/64 to 11.1.0.7/64 last weekend, and I thought I’d toss out a few first impressions.

The upgrade itself went about as well as any RDBMS upgrade ever goes.  For the first time I had to account for our disaster recovery (Dataguard) environment in New Jersey.  Of course, I had a few issues that I hadn’t seen in testing.

First, dataguard wouldn’t talk to the remote node, no matter what I tried it failed to authenticate and gave some vague message to that effect.  I fixed this by recreating my password files with orapwd ignorecase (my sys password was mixed case).  By the time I got this figured out, I was only about 1,000 logs behind at the phsyical standby… 3 hours to catch up.

Then I had one custom report which just dogged the system by pegging a CPU at 100% for 5-15 minutes depending upon parameters.  This is a result of the 11g optimizer changes; the interim solution was to use alter session set optimizer_features_enable=’10.2.0.4′; and suddenly, the query runs in seconds again.  Feels like _optimizer_extend_jppd_view_types (a new 11g optimizer feature) is the culprit, but I’m working with support to resolve/confirm this.  Someone told me Oracle boasted they would make their optimizer to be infallible.  Must be a future release.

Here’s the really important take-away from this upgrade:  I’ve just explained every significant issue… meaning there was 1 technical and 1 functional glitch in a major RDBMS upgrade.  This is an amazing success, when historically compared to a 9i to 10g upgrade or even a 10.2.0.X to 10.2.0.X+1 upgrade.  I have to admit that Oracle 11g is a solid release (no, they didn’t pay me to say this), has caused no functional 11i issues, and, on the aggregate, is running everything as good as or better than 10.2 did.  My EM SQL Response time (with a reference collection taken back when I was on 10g) proves this.  I suppose this is a result of some combination of thorough Oracle 11i 11g certification, and our own in-house regression test abilities.   I’d recommend the 11g database to go with the Oracle ERP without reservation.

*edit* Support wasn’t able to “fix” the optimizer bug I encountered on the report, and I closed the ticket. In the end, they were perfectly willing to work with me on the issue, but the information gathering requirements would have taken a small army of DBAs a week to collect.

*edit* I have a record of my 11i/11g upgrade path. If there’s interest in seeing this, leave a comment

April 3, 2009

RMAN and Advanced Compression

Filed under: Oracle, RMAN — Tags: — kkempf @ 11:03 am

Since we’d purchased 11g Advanced Compression, I thought I’d compare the new ZLIB compression algorithm to the existing BZIP2 algorithm in RMAN.  As a reminder, Oracle licensing prevents you from using ZLIB unless you’ve purchased Advanced Compression, and the RDBMS compatible init parm must be set to 11.0.0 or higher.  In a nutshell, ZLIB compresses less, uses less CPU, and is faster, while bzip2 compresses better, CPU is noticeable, and it’s slow.  ZLIB seems like a good middle-ground (between no compression and BZIP2), though I have no idea why Oracle decided they need to charge you to use it.

The RMAN syntax is simple:

configure compression algorithm ‘ZLIB’;

configure compression algorithm ‘BZIP2’;

configure compression algorithm clear;

working with CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;

The performance I saw was ZLIB was 15% faster, and 16% larger in output size when I ran a full backup of my EM repository (to disk)

Total Time Input Size Output Size Output Rate
BZIP2 214sec 4.7gb 664.1mb 3.1mb/s
ZLIB 179sec 5.62gb 1.07gb 6.12mb/s

Create a free website or blog at WordPress.com.