March 2009

March 2009
In each issue of The Specialist newsletter, one member of the Database Specialists team answers a frequently asked question from colleagues, customers, or friends. In this issue, Director of Managed Services Terry Sutton provides a detailed and thoughtful answer to the question of upgrading to Oracle Database 11g. This certainly is database news that you can use. The archive of back issues of the newsletter is available at http://dbspecialists.wpengine.com/backissues.html.

Here’s wishing you good fortune in your Oracle adventure.

THE LIGHTER SIDE
Don’t Scream At Your Disks! 

Do you have high waits for “db file sequential read” in your database? Perhaps you should check to see if anybody has been screaming at your disks? Disks have feelings too!
In this YouTube video, Brendan Gregg from Sun’s Fishworks team shows what happens when you scream at your disks and it’s no joke. Also, check this write-up on CNET. The moral of the story is: Never, ever, scream at your disks!
STUMP THE SPECIALISTS
Upgrading to Oracle Database 11g 

This month’s question came to us from a customer:

“Should we upgrade our databases to 11g?”

Director of Managed Services Terry Sutton thoughtfully responds:
We are frequently asked this question.  Oracle Corporation’s communications understandably promote using the latest version, and tout its new features.  The answer to the question isn’t a simple one, and (like so much else in our world), “it depends”.

One of the traditional answers given by people in the Oracle community (outside of Oracle Corporation) is not to use the first release of any Oracle major version.  There is some logic to this, but it oversimplifies.  As pointed out by Tom Kyte (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:597813300346606714), the major version first releases aren’t necessarily huge changes from the latest release of the previous version, and the second release may even have bigger changes from the first release than the first release had from the previous version.  And, as Jonathan Lewis has pointed out, since Oracle 9, much behavior (including default settings for some important optimizer parameters and other optimizer behavior) can change significantly between smaller releases (such as 9.2.0.3 to 9.2.0.4).

Our preference is to offer a slight variation on the “no first release” rule.  We try to avoid any major version or release upgrade until it has been publicly available for a year or two.  The reasoning behind this view is that every Oracle version/release/patchset is going to have some bugs or unexpected issues, no matter how well Oracle has tested it.  The bugs may or may not be showstoppers.  But if you rush to upgrade your production database to the newest version, you get to be the company which discovers some of these bugs.  If, however, you wait a while, more bugs are likely to be discovered by other users.  Oracle Support learns about these bugs and creates bug fixes.  By the time you come across the bug, it’s easy to find information on the bugs and issues, and there may already be a solution.

Another consideration is the concept of “if it ain’t broke, why fix it?”  If your database is serving your application well now, and you don’t have need of the new features of 11g (which are described in Arup Nanda’s series on the Oracle Technology Network– http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/index.html), then why rush to a new version?  Or why move to a new version at all?

One reason to move to the new version would be because Oracle is desupporting the version you are on (see http://www.oracle.com/support/lifetime-support-policy.html).  Premier support for Oracle 10gR2 ends in July 2010, for 11gR1 in August 2012.  Premier support generally ends five years after a release’s general availability date.  So if 11gR2 is released late this year, it will have premium support until late 2014.  Most companies do not want a version which has been desupported.

But even that is not a hard and fast rule.  If your company is using a packaged application which has been working perfectly well for years, and which you are not making any changes to, you may not care about support.  We have clients running quite happily on Oracle 8i or 9i.  The database meets their needs and they’re not changing anything.

One factor that is critical in upgrading (as Tom Kyte says several times in the posting referenced above) is that you must test, test, test.  And test some more.  Before you even think of putting a new version into production you need to hammer on it, stress it, try to break it, in a test environment.  You need to put loads greater than you’re likely to see in production, and you need to use all of the application code that is used in production.  That is the only way to see if the new version is problematic.  And this is a reason why some companies are slow to adopt a new version.  Testing isn’t free.  It consumes resources of personnel, hardware, and cash.

So our advice can be summarized as follows: Check to see if any of the new features of 11g will significantly help your application and if you have the appropriate license; some new features may only be available with Enterprise Edition or may be extra-cost options (see http://download.oracle.com/docs/cd/B28359_01/license.111/b28287.pdf).  If so, consider upgrading.  If not, wait a while to upgrade (while keeping desupport dates in mind).  And whatever you do, test, test, test.

I hope this answer helps you. Best of luck.
SQL CHALLENGE
Consider the following database table. It contains the probabilities for each face of a die. The die is not necessarily unbiased. Also the faces of the die are not necessarily numbered in the conventional manner-starting with 1.

SQL> describe die
Name            Null?      Type
————— ———- —————
FACE_ID         NOT NULL   NUMBER(2)
FACE_VALUE      NOT NULL   NUMBER(2)
PROBABILITY     NOT NULL   NUMBER(10,10
)

Here are SQL commands that can be used to create the table and populate it with sample data.

CREATE TABLE die (
face_id NUMBER(2) NOT NULL

    CHECK (face_id > 0) PRIMARY KEY,
face_value NUMBER(2) NOT NULL
    CHECK (face_value > 0),
probability NUMBER(10,10) NOT NULL
    CHECK (probability >=0 AND probability <= 1)
);

INSERT INTO die VALUES (1, 1, 1/6 + 1/12);
INSERT INTO die VALUES (2, 3, 1/6 + 1/12);
INSERT INTO die VALUES (3, 4, 1/6 + 1/12);
INSERT INTO die VALUES (4, 5, 1/6 – 1/12);
INSERT INTO die VALUES (5, 6, 1/6 – 1/12);
INSERT INTO die VALUES (6, 8, 1/6 – 1/12);

Consider the problem of computing the probabilities of obtaining various sums in N throws of the die. Here is the solution for N = 2; it requires a two-way Cartesian join. The solution for N = 3 would require a three-way Cartesian join, and so on.

SELECT   SUM,
SUM (probability) AS probability
FROM (SELECT d1.face_value + d2.face_value

                   AS SUM,
d1.probability * d2.probability
                   AS probability
FROM die d1,
die d2)
GROUP BY SUM
ORDER BY SUM;

SUM   PROBABILITY
———- ————-
2  0.0625000000
4  0.1250000000
5  0.1250000000
6  0.1041666667
7  0.1666666667
8  0.1041666667
9  0.1250000000
10  0.0486111111
11  0.0555555555
12  0.0486111111
13  0.0138888889
14  0.0138888889
16  0.0069444444

If the die was unbiased and numbered conventionally-beginning with 1-the following probabilities would be obtained. Notice the symmetry of the results in this case; that is, the probability of 7 – X equals the probability of 7 + X.

       SUM   PROBABILITY
———- ————-
2  0.0277777778
3  0.0555555556
4  0.0833333334
5  0.1111111112
6  0.1388888889
7  0.1666666667
8  0.1388888889
9  0.1111111112
10  0.0833333334
11  0.0555555556
12  0.0277777778

As you can see, it is not hard to solve the problem for particular values of N. The challenge is to compute the probabilities without hard-coding the value of N; that is, N must be left as a bind variable in the SQL solution. PL/SQL should not be used because, unlike SQL, it is a true programming language.

This problem was published in the November 2007 issue of the Journal of the Northern California Oracle User Group (NoCOUG).

Es-Cue-El Challenge

BE IT KNOWN BY THESE PRESENTS that the great Wizard of Odds at Hogwash School of Es-Cue-El needs your help in solving the riddle of the ancient jade icosahedron found in the secret chamber of mystery. A great tournament has been organized and all practitioners of the secret art of Es-Cue-El have been invited to demonstrate their prowess.

The best entry will win the Wooden Pretzel award, instituted in honor of another great wizard, who famously observed  that “some people can perform seeming miracles with straight Es-Cue-El, but the statements end up looking like pretzels created by somebody who is experimenting with hallucinogens.” As if that singular honor is not enough, a magnificent iPod Shuffle will also be bestowed on the champion.

Unsolvable Riddle

An ancient twenty-sided die (icosahedron) was discovered in the secret chamber of mystery at Hogwash School of Es-Cue-El. A mysterious symbol was inscribed on each face of the die. The great Wizard of Odds discovered that each symbol represented a number, and he also discovered that the die was biased-it was more probable that certain numbers would be displayed than others when the die was used in a game of chance. The great wizard then recorded all this information in tabular fashion as described below. 

SQL> describe die
Name            Null?      Type
————— ———- —————
FACE_ID         NOT NULL   NUMBER(2)
FACE_VALUE      NOT NULL   NUMBER(2)
PROBABILITY     NOT NULL   NUMBER(10,10) 
The great wizard now implores you to create an Es-Cue-El spell that displays the probabilities of obtaining various sums when the die is thrown N times in succession.*

May the best wizard win!

* N is a “substitution variable” or “bind variable.”

P.S. The “other great wizard” mentioned above is Steven Feuerstein. Here is his answer to a question that he answered in an interview published in the NoCOUG Journal. 
“SQL is a set-oriented non-procedural language; i.e., it works on sets and does not specify access paths. PL/SQL on the other hand is a record-oriented procedural language, as is very clear from the name. What is the place of a record-oriented procedural language in the relational world?”
“Its place is proven: SQL is not a complete language. Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens. We need more than SQL to build our applications, whether it is the implementation of business rules or application logic. PL/SQL remains the fastest and easiest way to access and manipulate data in an Oracle RDBMS, and I am certain it is going to stay that way for decades.”
P.S. Explicit formulae are available for conventionally numbered unbiased dice; for example, the probability of obtaining a sum of X in two throws of a conventionally numbered unbiased six sided die is (6 – abs(X – 7))/36. A long discussion of such formulas can be found on Wolfram MathWorld.
BULLETIN BOARD
Roger Schrag’s excellent paper on moving oracle databases across platforms without export/import is now required reading for the students of the Master of Science in Information Assurance program at Norwich Univerity.

Iggy Fernandez’s excellent book on Oracle database administration is available for purchase at online sites such as Amazon and in book stores such as Borders. The book is meant for junior Oracle DBAs as well as system administrators and application developers. It’s not really an 11g book because the lessons in the book are equally applicable to Oracle Database 10g and Oracle Database 11g. Here’s what it says on the back cover:
“If you are an IT professional who has been thrust into an Oracle Database administration role without the benefit of formal training, or you just want to understand how Oracle Database works, then I wrote this book for you. It’s the book I wish I’d had when I first started using Oracle Database so many years ago. It’s the book that I would have liked to have given to the many IT colleagues and friends who, over the years, have asked me to teach them the basics of Oracle Database.

Why choose my book when there are so many exhaustive reference manuals for Oracle Database available for free download on the Oracle website? Because my book is not an exhaustive reference manual, but instead is a manageable introduction to key Oracle Database topics including planning, installation, monitoring, troubleshooting, maintenance, and backups, to name just a few. You’ll also be getting the benefit of my experience, and not just the party line. For example, for reasons explained inside, I emphasize the Statspack tool instead of Automatic Workload Repository.

 
In Beginning Oracle Database 11g Administration, you’ll find information that you won’t find in other books on Oracle Database. Here you’ll find not just technical information but guidance on the work practices that are as vital to your success as technical skills. The most important chapter in the book is The Big Picture and the Ten Deliverables. If you take the lessons in this chapter to heart, you can quickly become a better Oracle database administrator than you ever thought possible. I hope this book helps you do exactly that!”

Call Database Specialists when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Arrange a free consultation with a senior Database Specialists team member to find out how we can help increase your uptime, improve performance, minimize risk, and reduce costs. Visit our website for no-cost resources, white papers, conference presentations and handy scripts.

Sincerely,
David Wolff
CEO, Database Specialists, Inc.

dwolff@dbspecialists.com
(415) 344-0500 x48

Leave a Reply

Your email address will not be published. Required fields are marked *