March 2008

March 2008

HUMOR
Database Administrator Builds Database Using Nose

Who says database administrators don’t have a funny bone? Watch this YouTube video of a database administrator building an Oracle database with his nose. We’re sure you’ll enjoy it as much as we did.

TUNING LIMBO: HOW LOW CAN YOU GO?
It Takes Two to TangoiPod Shuffle

Iggy Fernandez of the Database Specialists team contributed this installment of Tuning Limbo from his upcoming book on Oracle 11g database administration. The winner will receive their choice of an iPod Shuffle or a $50 Amazon gift certificate, mailed anywhere in the world.

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:

SELECT DISTINCT t.owner,
t.table_name,
t.tablespace_name
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!

STUMP THE SPECIALISTS
Efficiency Ratios Redux

This month’s question came to us from one of our clients:

“The rollback per transaction percentage in one of my databases is very high, according to the Statspack reports. What should I do about it?”

Terry Sutton of the Database Specialists team responds:

In general, efficiency ratios are not a reliable indicator of database efficiency; Cary Millsap explains this in a paper titled “Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok.” Focus on the “Top Timed Events,” “SQL ordered by Gets,” and “SQL ordered by Reads” sections of the Statspack report instead of efficiency ratios because the biggest performance gains come from focusing on the biggest components of the database workload (the sum of the elapsed times of all the SQL queries processed during the period of concern). If you are using Oracle Database 10g or 11g, ASH (Active Session History), AWR (Automatic Workload Repository), and ADDM (Automatic Database Diagnostic Monitor) are even better tools for the purpose though Enterprise Edition and Diagnostics Pack are required in order to use them.

To answer your specific question, the “rollback per transaction percentage” metric is defined in spcpkg.sql (Statspack installation script) and spcrepins.sql (Statspack report generation script); it is the percentage of rollback operations initiated by users in the total of rollback and commit operations initiated by users. Note that a rollback operation may not find any changes that need to be undone; the number of operations that actually find changes that need to be undone is tracked by the “transaction rollbacks” metric which is also recorded in the Statspack report. Another metric to watch is “rollback changes – undo records applied” which is an even better measure of rollback activity.

Why are there so many rollback operations? Perhaps the application is trying to be database agnostic. Other databases such as Microsoft SQL Server and IBM DB2 lock data when it is read by a user; a rollback operation would therefore be the appropriate way to unlock the data. The more important question is whether you will improve database efficiency by eliminating the rollback operations; as we have said already, focus on the “Top Timed Events,” “SQL ordered by Gets,” and “SQL ordered by Reads” sections of the Statspack report instead of efficiency ratios. You can find a lot of information on interpreting timed events 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.

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

PERFORMANCE TUNING BY EXAMPLE
The Case of the Invalidated Index 

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 was restricted to certain parts of the application only.
  2. The second step was to investigate the problem and collect as much pertinent evidence as possible; this was an internet application and the appropriate investigative technique was the one described in Tracing Individual Users in Connection-pooled Environments with Oracle 10g by Terry Sutton.
  3. The third step was to isolate the cause of the performance problem by analyzing the data collected in the second step; we used the tkprof utility and quickly homed in on the SQL statement that was at the heart of the problem.
  4. The fourth step was to solve the problem by creating a solution that addresses the root cause. The Execution Plan Changes section of the Database Rx Performance Portal told us that the execution plan for the SQL statement had changed; it was no longer using an index. The Alerts and Events sections of the portal listed an unusable index. The database administrator confirmed that maintenance had been performed on the table to reduce its size; the MOVE operation on the table had invalidated the index. The solution would be to rebuild the index.
  5. The fifth step was to implement the solution in a safe and controlled manner with the appropriate amount of testing and “before” and “after” measurements; we postponed the rebuilding activity to the late evening to mitigate locking activity.

In summary, we see that, no matter how simple or complex the performance problem, the steps leading to its solution are always the same. Read the original paper written by Chris Lawson and take the Database Rx Performance Portal for a test drive if you like.

DOING IT RIGHT
Use Historical Information When Adding Space to Databases

In the absence of historical information, the database administrator does not know whether newly added space will suffice for a day, a week, a month, or a year. The Database Rx monitoring agent regularly checks the sizes of all the tablespaces in a database and stores the information in the Database Rx performance portal; you can take the portal for a test drive if you like.

SCRIPT OF THE MONTH
Historical Information in Statspack Tables 

Most of us are content with using the spreport.sql script to generate reports on individual pairs of Statspack snapshots but Tim Gorman never loses the opportunity to remind anyone who is willing to listen that the Statspack tables are a gold mine of historical information. Check out his sp_systime_9i.sql and sp_systime_8i.sql scripts; they display the components of response time for each hour and for each day for which information is available; historical information such as this is invaluable in diagnosing and solving performance problems.

BEST OF THE WEB
Oracle Database 11g: The Top New Features for DBAs and Developers

2003 DBA of the Year and well-known author Arup Nanda offers a twenty-part series on the top new features of Oracle Database 11g; perfect to print and take with you on that long flight. If you’re still using Oracle Database 9i, read his twenty-part series on the top new features of Oracle Database 10g. A new feature a day will keep obsolescence away!

GEMS FROM THE ARCHIVE
Interpreting Wait Events

One of the most important sections of a Statspack report is the Top Timed Events section. You can find a lot of information on interpreting timed events 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.

CONFERENCE ROUNDUP
RMOUG Training Days 2008

RMOUG Training Days was held on February 13 and 18 at the Colorado Convention Center in Denver; Jonathan Lewis’ presentation Playing Russian Roulette with Silver Bullets alone was worth the price of admission. Iggy Fernandez represented Database Specialists on the technical agenda with two presentations: Oracle Annoyances for Geeks: Deadlocks and Livelocks, and Optimal Query Execution Plans: An Impossible Dream?

If you were not one of the lucky few who could attend, you can download most of the whitepapers and slide presentations from the RMOUG website and, if you live in the San Francisco Bay Area, you can attend the spring conference of the Northern California Oracle Users Group where Rich Niemiec will be delivering the same keynote address that he delivered at RMOUG: How Oracle Came to Rule the Database World.

NoCOUG Winter Conference

The Northern California Oracle Users Group held its winter conference on February 19 at the Oracle Conference Center in Redwood Shores. The keynote address was delivered by Juan Loiaza, a twenty-year Oracle veteran who knows more about Oracle’s vision and strategy than most people. Most of the whitepapers and slide presentations can be downloaded from the NoCOUG website.

NoCOUG Spring Conference

The Northern California Oracle Users Group will hold its spring conference, a whole day event, on May 15 at the Crowne Plaza hotel in Foster City. The keynote address How Oracle Came to Rule the Database World will be delivered by Rich Niemiec, author of several books on Oracle and CEO of TUSC; Rich will also deliver a talk on the new features of Oracle 11g. If you work with SQL a lot, 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.

Rich Headrick of the Database Specialists team reports that he was able to able to make dramatic performance improvements in a RAC database system by using the simple suggestions made by a speaker at a past NoCOUG conference. An annual 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.

COLLABORATE ’08

The joint annual conference of the Independent Oracle Users Group (IOUG), the Oracle Applications Users Group (OAUG), and Quest International Users Group will be held from April 13 to April 17 at the Colorado Convention Center in Denver. Iggy Fernandez will represent Database Specialists on the technical agenda with three presentations: Stop Doing Transactions Wrong!, Oracle Annoyances for Geeks: Deadlocks and Livelocks, and Optimal Query Execution Plans: An Impossible Dream? More information on COLLABORATE ’08 can be found on the IOUG website.

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.

Leave a Reply

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