June 2008

June 2008

THE LIGHTER SIDE
Do Coconuts Really Migrate?

If you are pondering a problem and are angling for advice, the Internet Oracle, an email system created by students of Indiana University, can help. Questions are forwarded randomly to other users, who provide humorous answers to the questions. Here’s how the Oracle answered the question: “Do coconuts really migrate?

P.S. The reference to migrating coconuts is from the movie Monty Python and the Holy Grail.

SQL CHALLENGE
Tuning Limbo: Limbo Lower Now! 

Limbo lower now
Limbo lower now
How low can you go
First you spread your limbo feet
Then you move to limbo beat
Limbo ankolimbonee
Bend back like a limbo tree
Jack be limbo Jack be quick
Jack go under limbo stick
All around the limbo clock
Hey let’s do the limbo rock

Dan Grant from California won the last round of Tuning Limbo iPod Shufflewith a solution that requires the retrieval of only two data blocks but Iggy Fernandez of the Database Specialists team has found a solution that requires only one data block and the limbo continues. Can you match Iggy’s solution? The contest has been extended until August 15, 2008 and the winner will receive their choice of an iPod Shuffle or a $50 Amazon gift certificate, mailed anywhere in the world.

STUMP THE SPECIALISTS
Why is Oracle not using my index?This month’s question came to us from a friend:

“Why is Oracle not using my index?”

Gary Sadler of the Database Specialists team responds:

The reason Oracle is not using your index is because a full-table scan would be faster according to the calculations of the query optimizer. There are several methods to influence the calculations of the query optimizer. Here are some examples.

  1. Check that you have statistics for both table and index. Note that, beginning with Oracle 10g, a default nightly maintenance job automatically updates statistics as necessary.
  2. You can force the query optimizer to use an index using the INDEX hint. Beginning with Oracle 10g, you can specify the columns contained in the index instead of the name of the index.
  3. You can adjust the values of OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING. The Search for Intelligence in the Cost-Based Optimizer by Tim Gorman discusses these settings.

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

SCRIPT OF THE MONTH
SQL Magic Trick

Chip Dawes has invented a view that makes it possible to query the Oracle alert log using SQL commands. You’ve got to see it to believe it. We are in awe.

The view is constructed using external tables and analytic functions.

CONFERENCE ROUNDUP
NoCOUG Summer Conference

The Northern California Oracle Users Group will hold its summer conference, a whole day training event, on August 21 at Chevron in San Ramon. Performance guru Cary Millsap will deliver the keynote address and present his “Grand Unified Theory” of Tuning.

“First there were the v$ and x$ views. And tkprof. Then came bstat/estat, Statspack, ASH, ADDM, OEM, and plenty of third-party tools that look kind of like those tools. There are loads of OS tools too that have been around a long time, like top and sar and strace and pstack and gprof. And then there are the methods: OPIM, and YAPP, and Method R to name three. In this presentation, Cary Millsap gives a brief tour of the moving parts and reveals his own perspective about how all these parts should fit together.”

Conference attendance is free for NoCOUG members and cost $40 for non-members.

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 *