May 2008

May 2008

THE LIGHTER SIDE
Thank You For The Music

Did you know that Oracle is a favorite subject of song writers? There are no less than one hundred and forty seven songs about Oracle. Happy listening.

SQL CHALLENGE
Tuning Limbo: How Low Can You Go? 

Dan Grant from California won the Tuning Limbo with a solution that requires the retrieval of only two data blocks. The contest is now closed and a $50 Amazon gift certificate is winging its way to Dan.

STUMP THE SPECIALISTS
Got More Books?This month’s question came to us from a colleague:

“In your previous issue you recommended a couple of introductory Oracle books? Can you recommend some advanced Oracle books?”

Iggy Fernandez of the Database Specialists team responds:

Mark Twain stated the obvious when he said: “The man who doesn’t read good books has no advantage over the man who can’t read them.” But who has the time to read?

Books to the ceiling, books to the sky.
My piles of books are a mile high.
How I love them!
How I need them!
I’ll have a long beard by the time I read them.

If I had the time, these are the books I would read twice, from cover to cover.

  1. Oracle Database Concepts by Oracle Corporation. If you have time to read only one book, then this is the book you should read. As Leonardo da Vinci said: Those who are in love with practice without science are like the sailor who boards a ship without rudder and compass, who is never certain where he is going. Practice must always be built on sound theory. This book is the rudder and compass for your journey through the world of Oracle. And you can’t quarrel with the price; it’s free.
  2. Effective Oracle By Design by Tom Kyte. Instead of scrambling to improve performance after you have deployed your database and application, why not design them right?  This book was written in the days of Oracle 9i but it is still one of the best Oracle books that money can buy.
  3. Practical Oracle 8i: Building Efficient Databases by Jonathan Lewis: Don’t be misled by the title. Mogens Norgaard reports that when Jonathan Lewis was asked if his book would ever be published in an updated 9i version, his response was to suggest that you put a sticker with a “9” over the “8” on the cover because the advice, methods and techniques described in the book were still very valid with Oracle 9i.
  4. The Art and Science of Oracle Performance Tuning by Chris Lawson: This book was written for Oracle 8i and Oracle 9i but is one of my favorites. It describes a systematic five-step method for solving any performance tuning problem. Oracle Database versions may change and specific tools and techniques 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.
  5. Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning by Richmond Shee et al. Oracle performance tuning is all about analyzing where the database is spending its time and this is the best book you can find on the subject.
  6. SQL Tuning by Dan Tow: Dan Tow has spent a lifetime studying how to write SQL that is both correct as well as efficient and this book summarizes knowledge accumulated over many years.
  7. SQL Programming Style by Joe Celko: This is not the easiest book to read but it will appeal to those who are working towards their black belt in SQL.
  8. Oracle PL/SQL Best Practices by Steven Feuerstein: Steven Feuerstein’s mastery of PL/SQL is positively transcendent and this book is worth its weight in gold.
  9. Database System Concepts by Abraham Silberschatz et al. This book, now in its fifth edition, is a well-known college textbook. Its main focus is the theory of database management systems e.g. relational algebra, relational calculus, normal forms, transaction management, query optimization, etc. but it also has great discussions of the big three database systems i.e. Oracle, DB2, and SQL Server.
  10. Expert Oracle Database 10g Administration by Sam Alapati. A rollicking good read on many topics and a wonderful gift for your favorite database administrator.

I hope this answer helps you. Best of luck to you.

PERFORMANCE TUNING BY EXAMPLE
The Case of the Cartesian Curiosity 

In his most excellent book The Art and Science of Oracle Performance Tuning, Chris Lawson, a past member of the Database Specialists team, outlined a systematic five-step method for solving any performance tuning problem. 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. Every month, The Specialist presents a summary of a case study from the Performance Tuning by Example series of case studies created by the Database Specialists team.

  1. The first step was to define the problem. After patient listening and careful observation it was determined that a large number of changes had been made to the application the previous night. Application response had been erratic since then.
  2. The second step was to investigate the problem and collect as much pertinent evidence as possible. We decided to take Statspack snapshots every ten minutes using the expression TRUNC (SYSDATE, ‘MI’) + ((10 – MOD (TO_NUMBER (TO_CHAR (SYSDATE, ‘MI’)), 10)) / 1440). We asked the users to record the time whenever application response went south. 
  3. The third step was to isolate the cause of the performance problem by analyzing the data collected in the second step. We examined the Statspack report for a period of bad performance identified by the users and found no wait events of any concern. However the “SQL ordered by Gets” section showed a single query that dominated all the others by a tremendous margin. This was the culprit.
  4. The fourth step was to solve the problem by creating a solution that addressed the root cause. We reformatted the culprit query using the Toad tool and it was immediately obvious to us that it was incorrect because there were no join conditions for one of the tables; the absence of join conditions would produce a  “Cartesian join.” We confirmed this by checking the query plan using DBMS_XPLAN.DISPLAY_CURSOR. We then used DBMS_METADATA.GET_DDL to identify the missing join conditions.
  5. The fifth step was to implement the solution in a safe and controlled manner with the appropriate amount of testing and with “before” and “after” measurements. We handed this job to the application developer who followed the established procedures.

One might well ask why we did not use AWR and ADDM reports instead of Statspack reports. The answer is that, even though AWR and ADDM are automatically installed when you create an Oracle database, you do need a license for Enterprise Edition and Diagnostics Pack if you want to generate AWR and ADDM reports. But, contrary to popular belief, Statspack is alive and well and has even been enhanced in Oracle Database 11g. A short introduction to installing and using Statspack can be found in Using Statspack in Oracle 8i and 9i to Identify Problems by Ian Jones. You might also want to read an excellent paper called Getting SQL Right the First Try by Dan Tow.

In summary, we see that, no matter how simple or complex the performance problem might be, the steps leading to its solution are always the same.

Forward    Subscribe    Previous Issue    Archive

DOING IT RIGHT
The Lessons of History

Incident Management is about handling an occurrence of a problem correctly and effficiently. Problem Management is about preventing a reoccurrence of the problem. The Database Rx performance portal stores records of all problem occurrences and is therefore a valuable tool in Problem Management. You can log in as a guest user and take the portal for a test drive if you like; please send your feedback and questions to askdbspecialists@dbspecialists.com.

SCRIPT OF THE MONTH
A Better Mousetrap 

Quick! List the names of your data files, temporary files, redo log files, and control files. Also list the last 20 lines from the alert log.

Do your fingers hurt from typing a lot of SQL commands and Unix commands? You might want to check out the free orastat tool by Bill Border and friends. It slices and dices and does a whole bunch of things that only a database administrator can love.

No warranties are provided but you won’t pay anything to use it either.

BEST OF THE WEB
Power to the People

Hotsos has done a service to the Oracle community by giving away Hotsos SQL Test Harness. Developers typically don’t have the ability to properly test SQL commands because they usually lack all the privileges necessary to view query execution plans, execution statistics, dynamic performance tables, and session traces. Hotsos SQL Test Harness solves these problems and empowers developers.

CONFERENCE ROUNDUP
NoCOUG Spring Conference

The Northern California Oracle Users Group held its spring conference, a whole day event, on May 15 at Crowne Plaza in Foster City. Our choice for best paper is Getting SQL Right the First Try by Dan Tow.

NoCOUG’s summer conference is on August 21 at Chevron in San Ramon. A calendar-year membership costs only $80 and includes free admission to four whole day conferences and four issues of the NoCOUG Journal. Walk-ins are welcome at the conferences; pay just $40 for the day or purchase an annual membership at the registration desk.

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 *