October 2008

October 2008

THE LIGHTER SIDE
Innovative Database Administrators

Three Oracle DBAs and three MySQL DBAs were taking the train to an I.T. conference. The Oracle DBAs each purchased a ticket but the MySQL DBAs, being quite frugal, only purchased one ticket between the three of them. The Oracle DBAs were shocked.

When it was time for the conductor to come around checking tickets, the MySQL DBAs rushed into the restroom and closed the door. The conductor knocked on the door of the restroom to check if anyone was in there. One of the MySQL DBAs held out a ticket from under the door and the conductor punched it and moved on. The Oracle DBAs were amazed at the innovative techniques used by the MySQL DBAs.

On the way back from the conference, the Oracle DBAs decided to adopt the technique of the MySQL DBAs and purchased only one ticket. The MySQL DBAs did not purchase any tickets at all. The Oracle DBAs were even more shocked.

How did the MySQL DBAs travel for free? Click here for the answer. The original joke was about engineers and accountants but let’s give it some new life with a new twist. If you tell it to ten of your friends, we will too.

STUMP THE SPECIALISTS
Help, My Database is Slow!This month’s question came to us from a reader:

“My database is slow. How can I improve performance?”

Iggy Fernandez of the Database Specialists team responds:

Quick, push the “Fast” button. Just kidding, but this is the most difficult question I have ever had to answer, if only because you did not give me any information that might help me diagnose the problem. For example, when is performance slow? Some times or all the time? Who is affected? Some users are all users? What changed?

Perhaps you’re looking for silver bullets such as those described in Oracle Silver Bullets: Real-World Oracle Performance Secrets. But the important question is how you arrived at your chosen solution. A hardware upgrade is a typical silver bullet but you won’t be happy if a hardware upgrade doesn’t fix anything. Collecting optimizer statistics is another typical silver bullet but you won’t be happy if performance worsens after you collect statistics.
I sincerely recommend that you read The Art and Science of Oracle Performance Tuning by Chris Lawson, a past member of the Database Specialists team. He preaches a systematic five-step method for solving performance problems. Pay careful attention to the fifth step: the appropriate level of testing should be conducted. Before and after measurements should also be obtained and you should have a backout plan if things go wrong.
  1. The first step is to define the problem. This requires patient listening, skillful questioning, and even careful observation. “The database is slow” is an example of a poorly defined problem. “The database is slow between 10 a.m. and 11 a.m. every day” is more precise. “This report now takes twice as long as it used to take only a week ago” is another example of a precisely defined problem.
  2. The second step is to investigate the problem and collect as much pertinent evidence as possible. Examples include Statspack reports, “sar” data, and session traces.
  3. The third step is to analyze the data collected in the second step and isolate the cause of the performance problem. This is often the most challenging part of the performance tuning exercise.
  4. The fourth step is to solve the problem by creating a solution that addresses the root cause. Solutions are not always obvious and, therefore, this part of the exercise might require a great deal of ingenuity and creativity.
  5. The fifth step is to implement the solution in a safe and controlled manner. An appropriate level of testing should be conducted. “Before” and “after” measurements should be obtained if possible in order to quantify the performance improvement. You also need a backout plan in case things get worse.

Chris wrote his book in the days of Oracle 9i but the method he teaches is evergreen. Oracle Database versions may change and software tools may change but the five steps always remain the same. A problem may be simple and require only a few minutes of your time or it may be tremendously complex and require weeks of your time but the five steps always remain the same. The original paper written by Chris can be downloaded from the Database Specialists website.

Statspack and AWR reports and histories are a great source of data for your investigation but please remember that AWR requires Diagnostics Pack which is only available with Enterprise Edition. Statspack tables such as STATS$OSSTAT, STATS$SYSSTAT, and STATS$SYSTEM_EVENT (and their AWR counterparts) are a great place to start. Historical execution metrics for SQL statements (including CPU time, elapsed time, number of executions, number of logical read operations, and number of physical read operations) can be found in STATS$SQL_SUMMARY.

Tim Gorman explains how to mine Statspack histories in his presentation titled Performance Diagnostics using STATSPACK data. Start with his sp_systime script for initial high-level analysis of where the database is spending its time on a day-by-day and hour-by-hour basis. This will help you determine which time periods need more analysis. You can then review the Statspack reports for the time periods of interest.
Oracle performance tuning is a vast topic but I hope I have given you some food for thought. Best of luck to you.

SQL CHALLENGE
Capitalism In Action  iPod Shuffle

Refer to the explanation below or read Chapter 1 of Beginning Oracle Database 11g Administration by Iggy Fernandez, Senior Staff Consultant at Database Specialists. Construct a relational algebra expression which identifies the suppliers who supply all the parts supplied by TOOL TIME, INC. at cheaper prices. If you prefer to do so, you may submit an SQL query instead of a relational algebra expression but you must restrict yourself to set operators as in the example below and must not use aggregate functions or subqueries but you may use inline views in the FROM clause. Send your solution to askdbspecialists@dbspecialists.com. The winner will receive their choice of an iPod Shuffle or a $50 Amazon gift certificate, mailed anywhere in the world. The contest ends on November 15 and the judge’s decision is final.

SQL has its roots in “relational algebra.” Relational operators such as Selection, Projection, Union, Difference, and Join produce new tables from old.

Consider the following tables:

CREATE TABLE supplier (
suppliername VARCHAR(32) NOT NULL,
PRIMARY KEY (suppliername)
);

CREATE TABLE part (
partname VARCHAR(32) NOT NULL,
PRIMARY KEY (partname)
);

CREATE TABLE quote (
suppliername VARCHAR(32) NOT NULL,
partname VARCHAR(32) NOT NULL,
quote NUMBER(8,2) NOT NULL,
PRIMARY KEY (suppliername, partname),
FOREIGN KEY (suppliername) REFERENCES supplier,
FOREIGN KEY (partname) REFERENCES part
);

Suppose that we need to identify the suppliers who supply all parts. We can do this in five easy steps. At each step, we use a relational operator and create an intermediate result table.

  1. In the first step, we use the Join operation and form an intermediate result table by concatenating records from the supplier table and the part table. All combinations of suppliername and partname occur in this table.
  2. In the second step, we use the Projection operation and form another intermediate result table by taking only the suppliername and partname columns from the quote table. The result is the list of valid suppliername and partname combinations.
  3. In the third step, we use the Difference operation and form a third intermediate result table by extracting only those rows from the intermediate result table created in the first step that are not to be found in the intermediate result table created in the second step. The result is the list of invalid suppliername and partname combinations. The occurrence of a certain combination of suppliername and partname in this new intermediate table indicates that the supplier in question does not supply the indicated part.
  4. In the fourth step, we use the Projection operation once again and form yet another intermediate result table by taking only the suppliername column from the intermediate result table created in the third step. The result is the list of suppliers who do not supply at least one part.
  5. In the fifth and final step, we use the Difference operation once again and obtain the final result we were seeking by extracting only those rows from the supplier table that do not occur in the intermediate result table created in the fourth step. The result is the list of suppliers who supply all parts.
We can specify the above sequence of steps in a single “relational algebra expression” as shown below.

supplier Minus Projection((part Join supplier) Minus Projection(quote))

The SQL equivalent is as follows. The correspondence with the above relational algebra expression is very obvious. Notice the use of inline views in the FROM clause and its correspondence with the parentheses in the relational algebra expression above.

SELECT suppliername
FROM supplier
MINUS
SELECT suppliername
FROM (SELECT * FROM supplier CROSS JOIN part
MINUS
        SELECT suppliername, partname FROM quote);

 

 

 

 

PET PEEVES
Unloading We Will Go 

Oracle has a mighty fine data loading utility called SQL*Loader which can read data from flat files. Why doesn’t Oracle have a data unloading utility? Arrgh!
Everybody’s favorite answer man, Tom Kyte, has collected four solutions. The one that works the fastest is the Pro*C solution.
An old solution by Gary Dodge lives on in Tim Gorman’s fabulous collection of scripts. Tim Gorman’s own solution, gen_csv.sql, might be all you really need. Check it out for yourself.

BOOK OF THE MONTH CLUB
Secret Oracle – Unleashing the Full Potential of the Oracle DBMS by Leveraging Undocumented Features

In his book review for the fall issue of the NoCOUG Journal, Brian Hitchcock has this to say about his latest find, a self-published book with the long title of Secret Oracle – Unleashing the Full Potential of the Oracle DBMS by Leveraging Undocumented Features.
“How much did I like this book? In the first 30 pages I found so many things that I didn’t know but that I think are worth knowing that I can’t describe them all to you in this review. That’s how good this book is.”

“I also found very few errors. This is interesting because I just finished reading a book from Oracle Press that had many errors, both typos and factual errors. I was surprised that a self-published book would have many fewer errors than a book from a major publisher.”

“Some of the best, most timely, and unique books won’t ever make an appearance in your local bookstore. Also note that when you search Amazon.com, Oracle books from the major publishers will appear first. If you search for ‘Oracle,’ you will find Secret Oracle as the 222nd result, or on the 19th page of search results. You have to realize that these books are out there and you have to go find them.”

CONFERENCE ROUNDUP
NoCOUG Fall Conference

The Northern California Oracle Users Group will hold its fall conference, a whole day training event, on November 13 at CarrAmerica Conference Center in Pleasanton, CA. Everybody’s favorite performance tuning guru, Jonathan Lewis, will deliver the keynote address. The conference agenda is available on the NoCOUG website.

In these tough economic times, local user groups like NoCOUG continue to provide tremendous training value to the Oracle community. Where else can you get four days of training and a quarterly journal for just $80 per year? Conference attendees get a continental breakfast, full lunch, and snacks; who says there’s no such thing as a free lunch? Free giveaways abound, both small and big; the big ticket item given away at the summer conference was a full pass to Oracle OpenWorld 2008.

Non-members are welcome to attend. The fee is $40 for non-members. RSVP here.

PARTING QUOTE
A INTERSECT B = A MINUS ( A MINUS B )

“Those who are in love with practice without knowledge are like the sailor who gets into a ship without rudder or compass and who never can be certain whether he is going. Practice must always be founded on sound theory.”
The Discourse On Painting by Leonardo da Vinci

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 *