April 2008

April 2008

Ali DBA and the Forty Thousand Books

One day, Ali DBA found a magic lamp. He rubbed the lamp and a beautiful genie appeared. “What is your wish, master?” said the genie. “I want all the knowledge about Oracle,” said Ali DBA.

“Is that your final answer?” asked the genie, startled. “Yes, it is!” said Ali DBA; “I want all the knowledge about Oracle.”

“That’s a very dangerous wish,” said the genie; “you’re playing with fire.” “I enjoy playing with fire,” Ali DBA replied.

The genie walked one hundred feet away. “Are you sure?” she shouted. “I’m sure, I’m sure,” said Ali DBA; “cross my heart and hope to die.”

“So be it,” said the genie sadly. She clapped her hands and forty thousand Oracle books fell on Ali DBA and crushed him to death.

The original version of the joke starts like this: A Microsoft certified software engineer, an Oracle database administrator, and a SAS programmer were lost in a desert when they came upon a large bottle. As they tried to pick it up to look at it, there was a puff of smoke and a magic genie appeared. “I will grant you three wishes,” the genie said. “Since there are three of you, I will grant each of you one wish. But be careful what you wish for.”

Tuning Limbo: How Low Can You Go? 

Dan Grant from California has submitted a solution to Tuning Limbo that requires the retrieval of only two data blocks; do you have a better solution or can you go even lower? The contest ends on May 14, 2008 and the winner will receive their choice of an iPod Shuffle or a $50 Amazon gift certificate, mailed anywhere in the world.iPod Shuffle

Consider two tables, modeled after DBA_TABLES and DBA_INDEXES respectively; the first table contains information about tables and the second table contains information about indexes. The requirement is to print the details of all tables which have at least one bitmap index.

Here is one possible solution:

FROM my_tables t, my_indexes i
WHERE t.owner = i.table_owner
AND t.table_name = i.table_name
AND i.index_type = ‘BITMAP’;

Any solution is only as good as the number of data blocks it needs to read in order to find the data; the fewer the better. That’s why we call it the “tuning limbo.” Refer to the helpful hints and submission instructions on our website and send your solution to askdbspecialists@dbspecialists.com; all options are on the table, including statistics, hints, optimizer settings, and any other tricks that you may have up your sleeve.

The contest ends on May 14, 2008, and the judge’s decision is final.

Got Books? 

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

“Can you recommend an introductory Oracle book for somebody who has a varied IT background but is relatively new to Oracle?”

Iggy Fernandez of the Database Specialists team responds:

I recommend Oracle Database 11g Concepts which can be downloaded for free from the Oracle documentation server. For a hands-on introduction to installing and using Oracle, I recommend Hands-On Oracle Database 10g Express Edition for Windows by Steve Bobrowski; a Linux version is also available.

I am writing Beginning Oracle 11g Database Administration for experienced IT professionals such as system administrators and software developers who want an introduction to Oracle database administration; the book will be published later this year. If you would like to be part of the focus group and provide feedback on the chapters as they are completed, please send your Yahoo ID to ifernandez@dbspecialists.com.

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

The Case of the Dull Database 

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 the problem affected all parts of the application during prime business hours.
  2. The second step was to investigate the problem and collect as much pertinent evidence as possible. Given the nature of the problem, we generated a Statspack report for the most affected period.
  3. The third step was to isolate the cause of the performance problem by analyzing the data collected in the second step. We focused on the “Top 5 Timed Events” section of the Statspack report and immediately saw that “db file sequential read” accounted for more than 80% of the total call time while the CPU took less than 5%. It was obvious that the system was I/O bound.
  4. The fourth step was to solve the problem by creating a solution that addressed the root cause. Five techniques presented themselves: faster disks, application tuning, SQL tuning, data clustering, and data caching. Since there was plenty of memory available, we decided to increase the size of the Oracle cache.
  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 increased the size of the Oracle cache by 1 GB every day and monitored the performance of the system using the Statspack reports until performance reached acceptable levels.

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. The original paper written by Chris Lawson is available for download from our website and a lot of information on interpreting timed events can be found in Interpreting Wait Events to Boost System Performance by Roger Schrag and More Examples of Interpreting Wait Events to Boost System Performance by Roger Schrag and Terry Sutton.

One might well ask why we did not use the AWR and ADDM reports instead of the Statspack report. 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 Oracle8i and 9i to Identify Problems by Ian Jones.

Blast From the Past 

Whenever there is a problem with a database, it is a good idea to find out if the problem has been encountered previously and how it was handled. The Database Rx performance portal stores records of all previous alerts and, when a new alert is received, the database administrator can search for similar alerts, then check the daily reviews and the DBA log for the corresponding time periods. 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.

Different Strokes for Different Folks 

Have you ever wanted to print down the page rather than across the page in SQL*Plus, like this?

DEPTNO         : 10
LOC            : NEW YORK

DEPTNO         : 20
LOC            : DALLAS

Well you can, using the PRINT_TABLE procedure written by ever popular Tom Kyte. Please read the posting all the way to the end because there are multiple versions of the procedure.

The Best Things in Life are Free

James Morle’s wonderful book Scaling Oracle8i: Building Highly Scalable OLTP System Architectures has been released into the public domain; click on the link to download it free of charge. James is one of the founding members of the Oak Table Network whose members include some of the brightest minds in the Oracle community. Even though the book says “8i” on the cover, it is still a very relevant book and nothing beats free. If you insist on paying, you can still buy it at Amazon for $49.99. Here is a typical comment from Amazon shoppers.

Scaling Oracle 8i is an incredibly well written book that clearly explains the concepts and terms DBAs have been using for years. But more importantly, it explains them in a way that highlights their relevance in terms how to design applications that will scale, or how to correct existing applications that don’t run as well as expected.”

Collaborate 08 

The joint annual conference of Independent Oracle Users Group (IOUG), Oracle Applications Users Group (OAUG), and Quest International Users Group was held from April 13 to April 17 at the Colorado Convention Center in Denver. There were 446 presentations in the IOUG section of the event alone. Collaborate 09 will be held from May 3 to May 7 next year in Orlando.

NoCOUG Spring Conference

The Northern California Oracle Users Group will hold its spring conference, a whole day event, on May 15 at Crowne Plaza in Foster City. The keynote address How Oracle Came to Rule the Database World will be delivered by Rich Niemiec, author of several Oracle books and CEO of Chicago-based TUSC; Rich will also deliver a talk on the new features of Oracle 11g. If you write a lot of SQL, you won’t want to miss Dan Tow’s presentations: Natural Data Clustering: Why Nested Loops Win So Often and Getting SQL Right the First Try. The complete agenda can be found on the NoCOUG website.

A calendar-year NoCOUG 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.

David Wolff
CEO, Database Specialists, Inc.

Leave a Reply

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