Always Learning

Jonathan Lewis Seminar in Dallas

I got to hear Oracle performance guru Jonathan Lewis today in an all-day seminar in Dallas. "Writing Optimal SQL" was part 2 of a two-part series here in Dallas, sponsored by Dallas Oracle Users Group (DOUG).

Jonathan started out by reviewing some general principles, such as the importance of design and optimizing for the global situation (many queries) not just a single query. "Making me go fast might make you go slower," he said.

Performance comes from precision in selecting just the blocks and rows you need to select, and from the structure of the data (where it's located).

My favorite part of the seminar was his discussion on coding style for SQL, a topic that is often ignored in performance discussions. His view that "Computers are cheap; humans are expensive" is one that I've been preaching for years in my SQL and PL/SQL classes.  "You have two audiences," I tell my classes, "the compiler, and the poor person who comes after you to maintain your code.  Be kind.  Someday that person will be you." Jonathan's take is that you should write SQL as a close translation of the business purpose of the query, and only fiddle with it if performance isn't acceptable.

He also suggested: "Don't tune the SQL; tune the end users," meaning that if a requirement is unclear or the data creates unexpected results, go back to the person requesting the report and get clarification instead of burning a lot of time over-engineering the solution to fit every possible case.

This talk could easily have been titled, "Unintended Side-Effects of SQL" because Jonathan went through many scenarios in which results differed from what might have been expected, and why it turned out that way.

It was a great session.  Thanks to Speak-Tech  and to DOUG's Mary Elizabeth McNeely, president and programs chair, for bringing Jonathan Lewis to town.

Oracle 11gR2 Podcast by Tom Kyte

Tom Kyte recorded a podcast this month about the new features of Oracle Database 11gR2. The podcast, part of the Oracle Database podcasts series, can be accessed here:

http://www.oracle.com/database/podcasts.html

In it, he describes an exciting new feature: the ability to compile stored procedures, packages, etc. while users are online using the same schema! The feature, called Edition-based Redefinition, refers to the current objects as an "edition".  The developer creates a child edition based upon the current one, then does development against the child edition. When it comes time to switch over, new sessions can begin using the new edition, as existing sessions continue to use the old. An upgrade that used to take an hour of downtime for compilations can appear to users to take no time at all. It still takes an hour to compile, he said, but the users are not locked out of the system while it's happening.

He also talks about the ability to create a table, but defer to allocation of storage until the table is first used. This will make it much faster to install complex software applications that have many optional tables in them. Only the metadata in the data dictionary will be created during the install, and tables never used will therfore not take up space.

It's a short talk, about 10 minutes, but well worth listening to.

Controlling Controlfile Backups

Did you know that you can control the name of your controfile trace backups? I didn't, until Bob Morgan of Stonehill College in Easton, MA pointed out some additional syntax in the ALTER DATABASE command.

I already knew that you could control the filename and location when backing up the controlfile in binary, via:

ALTER DATABASE BACKUP CONTROLFILE TO '<pathame>';

And I knew that you could get a text copy of the commands necessary to rebuild the controlfiles, via:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

The resulting text commands are placed in the user's trace file for the session. To find the proper trace file requires knowing the session number; it's in the filename.  (Though I usually cheat, and go to the directory pointed to by the user_dump_dest parameter, sort by modification time, and my session is near the top.)

What Bob pointed out was the variation:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '<filename>';

This still writes to the backup_dump_dest directory, but names the tracefile with your choice of filename instead of the default one that includes the session number.  Much easier to find.

Thanks, Bob!

Tom Kyte's New Years Resolutions

Anyone who knows me knows I'm a BIG Tom Kyte fan.

Tom's published his list of New Years Resolutions for 2009, and the top of the list is "Practice a restore at least once a month."  You can read the rest of the resolutions here:

http://www.oracle.com/newsletters/information-indepth/database-insider/jan-09/kyte.html

(Thanks to Chen Shapira on Twitter for the pointer to this.)

Oracle Database 11gR1 on VMWare

I've just finished creating the test environment I'm going to use to prepare for the 11g OCP upgrade test. Instead of installing directly on a lab machine as I've done before, I decided to do this one inside a VMWare virtual machine. Here are some notes on what I did, while they're fresh in my mind.

Don't you just love the Internet?  I found an excellent step-by-step install guide by John Smiley, and it really shortened the learning curve. It also contains links to the download sites for Oracle Enterprise Linux 5, which is functionally similar to Red Hat Enterprise Linux 5, and Oracle Database 11gR1.

VMWare layer: 

I created a VM with 1024Mb memory, a single 20GB hard disk, one network adapter bridged. I don't plan to do RAC scenarios with this image, it's just for the basics, so I kept it simple. I can add more virtual hard drives to it later to practice ASM.

Linux layer:

I used Oracle Enterprise Linux 5, downloaded from Oracle.  I followed the install instructions from Oracle-Base, which included details on disk partitioning. OEL5 includes 5 CD images as .iso files, but the install only required the first 3.

On my first attempt, I did as the Oracle notes suggested and did a default install of packages. But this led to problems later with missing libraries needed by various RPMs. So, I started over, selected "Customize Now" instead of "Customize Later," and followed instructions I had used successfully with 10gR2 in terms of which packages to include and not:

• GNOME Desktop Environment
• Editors
• Graphical Internet
• Text-based Internet
• Development Libraries
• Development Tools
• Legacy Software Development
• Server Configuration Tools
• Administration Tools
• Base
• Legacy Software Support
• System Tools
• X Window System

This worked much better.

I then followed the Oracle instructions exactly for preparing the OS for Oracle, in terms of adding several additional RPMs, setting kernel parameters, creating security groups and creating the oracle user.

Oracle installation:

My first try failed due to unzipping all the files on my Windows machine and burning an ISO image. I got permissions errors when trying to start the Oracle Universal Installer. So, I reburned the image with just the ZIP file as downloaded by Oracle, and unzipped it within the Linux environment. The rest of the install was textbook.

Throughout, I used the VMware Workstation 6.5 snapshot feature, taking snapshots of the base Linux machine, Linux machine plus additional packages, Linux machine plus customizations for Oracle, and finally after the successful install.  I connected with sqlplus and was rewarded with the opening banner giving the version as 11.1.0.6.0.

Let the studying begin!  Oh wait, it already has.

New Oracle Certified Associate (OCA) Requirements

New candidates for Oracle's entry level DBA certification must now pass an exam on SQL, in addition to the existing requirement for passing the Database Administration I test. The Oracle Certified Associate (OCA) credential is the first rung in Oracle's 3-step DBA certification ladder. The change went into effect December 1st, 2008.

According to the Oracle University website, any of four exams will meet the requirement:

• 1Z0-001: Introduction to SQL and PL/SQL
• 1Z0-007: Introduction to 9i SQL
• 1Z1-051: Oracle Database 11g SQL Fundamentals I
• 1Z0-047: Oracle Database SQL Expert

Existing OCAs and OCPs do not have to fulfill the additional requirement. Their upgrade paths remain the same.

October 2008 Critical Patch Update released

Oracle released its quarterly security update on October 14.  The advisory can be found on OTN at http://www.oracle.com/technology/deploy/security/alerts.htm .

Note that in 2008, Oracle stopped updating Metalink with the Critical Patch Updates, and now distributes them only via OTN.

As with any patching, no matter how trivial, be sure to apply to your development and test systems first before updating production.

Oracle on VMWare

One of my current projects is building VMWare virtual machines that I can use to practice installing Oracle 11g and third-party products.  I found some great "cheat sheets" at the Oracle Base website:

• Oracle DB 11gR1 RAC Installation on OEL5 Using VMWare.  This one covers setting up Oracle Enterprise Linux 5 on VMWare virtual machines in a configuration designed to be used as a RAC node (e.g., dual network interface cards). It's the whole meal, from soup to nuts. The level of detail is great.
• Oracle Enterprise Linux 5 Installation.  This is a step-by-step, generic setup of OEL5 suitable for adding other software (like Oracle) later. It's not Oracle-specific, but a good overview.

Both of these have lots of screenshots and the little tips that head off disaster.

I do have a couple of qualms, though.

Now that Oracle has released their own virtualization solution, OracleVM, they are not likely to ever support VMWare-based solutions. Part of me says I should be building these VMs on OracleVM. However, Oracle VM requires a separate machine to run, and VMWare doesn't. Plus, VMWare is a clear market leader at this point. Because these machines will be used as learning aids, not production, I don't think it's a big risk.

The other thing is using Oracle Enterprise Linux as the O.S. instead of something more generic. The only reasons I'm going this way are that the cheat sheets use it, and it is a free download.

But clearly, things have come a long way since people were first struggling with Oracle in virtual environments years ago.

July 2008 Critical Patch Update released

Oracle Corporation released its quarterly security update today, the July 2008 Critical Patch Update (CPU).

CPUs are cumulative from the release to which they are applied. The July 2008 CPU contains 45 new fixes across all Oracle products.

As with all upgrades and patches, apply them to your development and test servers first before patching production, no matter how small or routine a change may seem.

First Look at Oracle Database 11g

Here are some notes I took at this month's Dallas Oracle Users Group meeting, at which Linda Smith of Oracle previewed the new features coming in Oracle Database 11g.

The first question she answered is:  When?  The official word is "2nd Half of Calendar Year 2007." Originally, it was closer to July, but now she suspects it will be around September, or timed for Oracle OpenWorld conference in San Francisco, in November.

The focus of the release is clear: Availability. Many of the new features are aimed at keeping the database available 7 x 24 x 365, despite the many database administration tasks that must be performed regularly. Here are some highlights:

• Change Management. "Change is the most common cause of instability," Smith said. So Oracle 11g will include features that permit more realistic testing of new releases and patches before committing them to production. You'll be able to snapshot a physical standby, use it to perform testing, then rollback the snapshot.
• Database Replay. This lets you capture the actual workload from a production system, and use it to stress-test your test environment to be sure the changes work. Furthermore, your test environment does not need to have live application servers and client stations to do this: the replay looks the same.  (Of course, you'll want those pieces in your test infrastructure anyway, to test them. But you won't need them to test the database.)
• Online patching.  This boggles my mind, but supposedly the new release will let you apply patches "hot," without a service outage. This includes the quarterly Critical Patch Updates we've all grown to know and love.
• SQL Performance Analyzer. This captures before and after measurements of your SQL, and after you apply an upgrade, can show you which SQL got better -- and which got worse (regressed). So, during testing of an upgrade, you can tune what needs to be tuned before taking the release to production. There's also an automatic SQL tuning feature that will transparently rewrite any statement that it can get a 3X improvement on.
• Flashback Data Archive. In the past, the Flashback Query feature could only go back as far as the size of your Undo Tablespace permits. In 11g, you can configure a more permanent archive that will enable you to flashback years, if required. This is aimed at auditors and compliance reporting, but I can see some interesting DBA possibilities too.

Obviously there's a bunch more, including new partitioning options, a replacement for the way LOBs are done that looks pretty slick (SecureFiles), improvements to ASM, and developer stuff.

Time to book my trip to San Francisco for September!

UPDATE, 4/25/07:  I forgot to mention that a new version of the free Express Edition is also planned, but will lag the Enterprise Edition ship date by some number of months.