LinkedIn

       LinkedIn  Twitter
 

Consulting :: We are experts in Oracle Database Solutions with the experience and knowledge to bring any project to completion, applying best practices. 

Remote DBA Services :: Database Specialists company provides top of the line expertise in remote database administration backed up by over 15 years of experience.

Database Rx :: Our platform remotely track, monitor and manage client's databases. Our clients can access information and receive 24/7 live support anytime, anywhere.

  
 

December 2009    
Database Specialists wishes all its customers and friends a productive and prosperous 2010. Thank you for allowing us to serve you and we hope you enjoy reading our newsletter. Here are some of the most-viewed articles of 2009.
THE LIGHTER SIDE  
How Many Database Progammers?
 
How many database programmers does it take to change a light bulb?
 
Three.
 
One to write the light bulb removal program, one to write the light bulb insertion program, and one to act as a light bulb administrator to make sure nobody else tries to change the light bulb at the same time.
 
How many SAS programmers does it take to change a light bulb?
 
Click here for the answer. Funny!
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, 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) 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.  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.  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 CORNER  
The First International NoCOUG 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.
 
The competition was a great success; nine solutions were found by participants in seven countries and three continents. Alberto Dell'Era wins the contest for his wonderful solution using Discrete Fourier Transforms; the runner-up is André Araujo from Australia, who used binary arithmetic and common table expressions in his solution. The August Order of the Wooden Pretzel was bestowed on Alberto but the real prize was six books of his choice from the Apress catalog. André received a prize of six e-books of his choice.
 
The first solution--by Laurent Schneider from Switzerland--used the CONNECT BY clause to join the table to itself N times and the SYS_CONNECT_BY_PATH and XMLQUERY functions to perform the necessary additions and multiplications. The number of records generated by CONNECT BY grows exponentially and hurts performance.
 
The second solution--by Craig Martin from the USA--used the CONNECT BY clause to join the table to itself N times and logarithms to perform the necessary additions and multiplications. The number of records grows exponentially in this case too.
 
The third solution--by Rob van Wijk from the Netherlands--used the Model clause to generate records. The number of records grows exponentially in this case too.
 
The fourth solution--by Vadim Tropashko from the USA--used recursive common table expressions to generate records.
 
The fifth and sixth solutions--by Alberto Dell'Era from Italy--used advanced mathematical techniques such as convolutions, Discrete Fourier Transforms, and Fast Fourier Transforms. The Fast Fourier Transform method is an efficient way of calculating Discrete Fourier Transforms and was implemented using the Model clause.
 
The seventh solution--by Fabien Contaminard from France--was based on the multinomial probability distribution, an extension of the binomial distribution.
 
The eighth solution--by a blogger named Cd-MaN from Romania--used pipelined table functions and recursion. The solution was demonstrated in a Postgres database but can easily be adapted for use in an Oracle Database. The use of recursion means that this is not a pure SQL solution.
 
The ninth solution--by André Araujo from Australia--used binary arithmetic and common table expressions.
 
Click here for an detailed analysis of the winning solution; there is also a tenth solution.
BULLETIN BOARD  
Database Specialists is mentioned in the April 8, 2009 edition of eWeek in an article titled Sizing Up the Costs of Remote Database Administration Services.  The article discusses the various remote DBA options and compares several providers of these services. Database Specialists is also mentioned in a follow-up article titled Inside the Outsourced World of Remote Database Services.
 

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 

 

Database Rx® Performance Portal Client Log-in
User Name:            Password:
 

 
Receive our FREE White Papers
Email:
For Email Marketing you can trust
 
 

"I find the support from Database Specialists to be outstanding. I have worked with some other outsourced DBAs in the past, and they blow everyone else away."
-IT Director, Semiconductor Firm

  
 

The Database Specialists Blog

Learn more about installing and using Oracle RAC

Gain practical knowledge for using Oracle's wait event interface:

View our popular step-by-step Oracle installation guides:

Learn how to speed up queries using semi-joins and anti-joins.

  
Copyright © 2011 Database Specialists, Inc. All rights reserved. Careers | Terms  |  Privacy  |  Trademark

Website By: WORLDZOO