I Didn’t Know That 2 – What is Wrong with this Quote?

10 12 2010

December 10, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

In the interest of interesting quotes, a quote that I found in the “Oracle Tuning the Definitive Reference Second Edition” book on page 988:

“Production DBA’s spend weekends reorganizing their data structures, returning them back into their original, pristine state, in preparation for the return of the end-users on Monday morning.

Rebuilding high-DML indexes in a schedule can be a DBA best practice under certain conditions:

  • You can schedule a job to rebuild and index (and address errors) in just a few minutes.  Because most DBA’s are salaried professionals, the DBA cost is negligible.
  • During a weekly maintenance window when the server sits idle.  Because hardware depreciates rapidly, regardless of use, the cost of rebuilding indexes is essentially zero.”

What, if anything, is wrong with the above quote?  Please keep in mind that the focus of this blog is on the technical content, and learning from that technical content.  Please stay positive in your responses (before answering, first take a look at page 727 to see if we really need to first determine the candidate indexes for a rebuild).


While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

Other pages found during a Google search of the phrase:



6 responses

10 12 2010
Radoslav Golian

“During a weekly maintenance window when the server sits idle. Because hardware depreciates rapidly, regardless of use, the cost of rebuilding indexes is essentially zero”
This is definitely not true.
1. rebuilding of an index = fullscan of a table + sorting + 2x space of the rebuilt index, so the cost is definitely not zero..
2. rebuilding index may not help, it can acually make the performance worse, because of block spliting.. it can be like putting someone on diet (rebuild it) and then feed him all week (it will get fat again), and than again put him on diet – Tom Kyte has used this nice comparison :). Block spliting imply more redo generation. And slower index updates
There are some valid scenarios for index rebuilding, but in most cases we don’t need to rebuild an index
and there are also other options how to reorganize an index (coalsce, shirnk) which may be more valid in some scenarios.
3. in general, index scans represent only small part of all work.. More work is done by reading the coresponding block from a table.

Richard Foote has very good articles aboud index rebuilds, which are worth to read

10 12 2010
Radoslav Golian

ad 1) + lot of redo and undo generation

10 12 2010
Nigel Thomas

And of course there may not be a weekly maintenance window. What websites work 9-5 in a single timezone, for example?

10 12 2010
Charles Hooper

Excellent comments so far.

Now, if I were reading this as a DBA (just to put a different spin on things), I would want a couple of clarifications before I started shutting down my production databases to perform maintenance (would that then make me a non-production DBA too?). For instance, a Google search for data structure:
“•(computer science) the organization of data (and its storage allocations in a computer)
•Attributive form of data structure
•a way to store and organize data in order to facilitate access and modifications

Then I might perform a Google search of the word pristine:
“•completely free from dirt or contamination; ‘pristine mountain snow’
•immaculately clean and unused; ‘handed her his pristine white handkerchief’
•Unspoiled; still with its original purity; uncorrupted or unsullied; primitive, pertaining to the earliest state of something

So then, if I am reading that right, a production DBA should bring down the production database to return the database tables back to their earliest state so that the tables are ready for end-users on Monday morning. This seems to be a bit wasteful, especially if the process is performed on a scheduled basis.

In the next paragraph the author mentions something about rebuilding indexes being a best practice – but is that really necessary if the DBA is truncating the data structures? 🙂

And what about the issue of DBA time being essentially endless because the DBAs are salary? It was my understanding, at least in the United States, that salary employees’ hours could not be treated as endless commodities for employees who are not a manager. I may not be recalling the law correctly, but I believe that it changed since the year 2000:

11 12 2010
Charles Hooper

I thought that by now someone would have looked at the phrase “high-DML indexes” and wondered just what that means. The bad news here is that a SELECT statement is considered DML, as is a DELETE statement and a INSERT statement. So, the second paragraph could be stating that it is a best practice to rebuild indexes on a schedule, if those indexes are frequently used in execution plans.

Now the question, what to do about those indexes that are at three levels in portions of the B*tree index and four levels in other portions of the same B*tree index as described on page 727, and as possibly pictured on page 828 (http://books.google.com/books?id=hiOhVSO-EFcC&pg=PA828 ). Should the DBA rebuild those before the scheduled time interval? Should the DBA first verify that he/she is working with an Oracle database? Should the DBA ask wasn’t this discussed with the book author in 2003 and 2004?

Sometimes trying to puzzle out the meaning of phrases in the book is a bit of a challenge. I just thought that I would put a bit of a different spin on this section of the book, looking at it from the point of view of someone trying to learn Oracle concepts from the book, encountering the topic of frequent index rebuilds for the first time.

For those reading this blog article, see the excellent technical comments provided by Radoslav and Nigel.

11 12 2010
Charles Hooper

I just noticed something really strange. The two AskTom threads that I linked to in this article appear to be a review of roughly 30% of the book “Oracle Tuning the Definitive Reference Second Edition”, yet much of those comments were written between 2002 and 2004. For example, in the first thread:

We see the following addressed:
* Whether or not indexes should be rebuilt
* Bitmap indexes are a special case
* Using separate tablespaces for tables and indexes for performance reasons
* Whether or not indexes should be rebuilt on a schedule
* High clustering fact a reason for index rebuild
* Is it possible that some leaf nodes are at level 3 while others in the same index are at level 2 or level 4
* del_lf_rows should be less than 20% of the total”, “if index height >= 4
* “second derivative” of an undefined mathematical function “f(x)”,
* Throw more RAM at the problem (full caching 50GB of indexes) as a first step or diagnose the problem
* What could happen when the CPUs in a CPU constrained server are upgraded.
* Using multiple block sizes in a database
* Rebuilding indexes into a 32KB blocksize tablespace
* “Oracle authorized books”
* Space requirements for index rebuilds
* Coalesce versus rebuild
* Meaning of “balance” in an index
* Metalink Doc ID 77574.1
* Metalink Doc ID 122008.1

Now, compare the above list with my review of the book – there are a lot of common topics.

An interesting quote that I found in the thread that seems to apply to several of the quotes that I pulled from the book: “You want to talk technical? Be precise, don’t make up new meanings. Don’t make people guess what you MIGHT have meant, maybe.”

Interesting links in the article:
Richard Foote’s “Rebuilding the Truth” article (now at http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf )

Another interesting quote:
“* Use information that is factual.
* Use information that can be proven.
* Use information that not only comes with ‘do this’ advice — but also comes with ‘but watch out, it doesn’t apply in this case’ sort of information (eg: nothing is 100% true, nothing is 100% false — things are not black and white).
* Use information that the person giving you the information has convinced you with examples, test cases, *numbers* to be accurate.
* Above all, use common sense :)”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: