Oracle Performance Tuning Tips and Techniques [Paperback]

Richard J. Niemiec (Author)

Book Description

ISBN-10: 0078824346 | ISBN-13: 978-0078824340 | Publication Date: April 1, 1999 | Edition: 1

This guide provides Oracle DBAs with the know-how to tune and tweak Oracle databases to achieve optimal performance results, using real-world examples. There is information on bottlenecks, undocumented parameters and performance-enhancing tips useful to Oracle DBAs. It provides specific examples to build performance tuning skill sets. The tips and techniques have been compiled directly from Oracle User Groups around the world.

Editorial Reviews

Review

"If you only had one book to buy this year, this is the book to get." -- SELECT Magazine, January 2000

From the Back Cover

Expert Technical Knowledge at the Turn of a Page.

Tune and tailor your Oracle7 or Oracle8 system to perform at its peak using this one-of-a-kind reference, designed for every Oracle database administrator, programmer, and user.

In Oracle Performance Tuning Tips & Techniques, you'll find hundreds of real-world examples and insider secrets compiled from TUSC's standing-room only conference presentations and Oracle User Groups worldwide. From finding and fixing bottlenecks to enhancing performance using PL/SQL, this authoritative guide reveals proven methods for getting the most from your Oracle system throughout its lifecycle.
* Discover the Top 10 Tuning Tips used by industry experts to improve performance
* Maximize tuning with the most important basic, advanced, and undocumented INIT.ORA parameters for Oracle7 and Oracle8
* Use Explain Plan and Tkprof to uncover problem queries that, when corrected, result in peak performance
* Explore the new features in Oracle8 to gain peak performance by uncovering problem queries
* Refine your tuning skills by exploring the new features in Oracle8
* Take advantage of key V$ Views and X$ Tables to enhance performance

Developed through years of consulting expertise, Oracle Performance Tuning Tips & Techniques is packed with time-saving tuning gems designed to ensure top system performance.

Product Details

• Paperback: 894 pages
• Publisher: Wrox Press; 1 edition (April 1, 1999)
• Language: English
• ISBN-10: 0078824346
• ISBN-13: 978-0078824340
• Product Dimensions: 9.1 x 7.4 x 2 inches
• Shipping Weight: 4 pounds
• Average Customer Review: 4.7 out of 5 stars  See all reviews (35 customer reviews)
• Amazon Best Sellers Rank: #2,372,928 in Books (See Top 100 in Books)

More About the Author

Discover books, learn about writers, read author blogs, and more.

Customer Reviews

Most Helpful Customer Reviews

30 of 32 people found the following review helpful:

4.0 out of 5 stars Required reading for Oracle developers..., May 9, 2000

By 

nullptr "nullptr" (Seattle, Washington) - See all my reviews

This review is from: Oracle Performance Tuning Tips and Techniques (Paperback)

My review is focused on this book as it relates to a developer, rather than a DBA. A great deal of this book deals specifically with DBA-related activities which I will pass on reviewing for now.

It is a bit scary to think that I actually wrote queries before reading this book. The tips and techniques in this book provide a great deal of insight into how to tune the database and are truly invaluable.

"Tuning a database" is a rather silly way of describing the content of this book, because there are many aspects of that. You can tune the Oracle software itself (DBA), the structure of the data (Architect/Developer), or the SQL queries (Developer).

The coverage of SQL hints was absolutely invaluable for me -- and the author gives a great deal of information on why they need to be used. I come out of reading this book far more skilled on how the Oracle software actually parses and executes a query. The author gives in-depth coverage of the EXPLAIN PLAN and TRACE utilities, and more importantly he explains how to interpret the results.

One of the areas that the author explained very well was the use of indexes -- not just the basic rules (95/5), but how they should be used when the distribution of data is not linear in the column and how the parser decides to use the index in the rule-based vs. cost-based optimizing engine. Of course, the coverage on hints provides a way to override either engine, which is sometimes critical!

Another portion of the book I found of high value was the very large chapter on driving tables (for table joins). This is a complicated but very important issue (and a common cause of huge performance hangups). The author does a nice job of explaining this concept through a series of examples. Any developer who has never heard the term "driving table" needs to add this book to their shopping cart.

I would very much like to give this book a 5 star rating but I just have to take off a star for the poor writing style of the author. The author is an Oracle professional with real-world experience -- which probably helps the book in terms of topics covered, but does not help someone trying to read it. There are some instances where entire paragraphs are literally copied and pasted in different chapters.

Another problem is that he assumes a lot of knowledge in some chapters and almost none in others. Each chapter seems to be written for a different audience level. For example, he discusses in-memory hash tables in the chapter on join types -- which us C++ programmers know very well. However, a typical SQL developer probably does not know how a hash-table works or why it is better than using basic nested loops.

However, this book really does belong on the shelf of every developer who wants to really know Oracle.

15 of 16 people found the following review helpful:

5.0 out of 5 stars Excellent Performance Tuning book, January 8, 2000

By 

Roger Jackson (Melbourne, Australia) - See all my reviews

This review is from: Oracle Performance Tuning Tips and Techniques (Paperback)

This text offers some great tips and advice. If you haven't got time to read the whole book don't worry, chapter 1 provides a summary of the entire text. The book has been very well written and concepts are clearly explained with the aid of diagrams where applicable. The scripts presented throughout the book are quite useful although I have found some syntax errors. Soft copies of the scripts are available from the TUSC website anyhow. Overall this book is well suited to beginners and advanced DBAs. I look forward to reading more books from Rich and the team at TUSC.

13 of 15 people found the following review helpful:

5.0 out of 5 stars A book touching the real world, March 10, 2000

By 

Lewei Shang - See all my reviews

This review is from: Oracle Performance Tuning Tips and Techniques (Paperback)

I like this book not because it is a perfect book but it is a comprehensive discussion of Oracle Performance Tuning in a very deep level.

What I like the most is that the book sets a bridge between the academic theory and the real world. It is the first book I have ever read to tell the real numbers for major init.ora parameters. It suggests you the value of the SHARED_POOL_SIZE, DB_BLOCK_BUFFERS, LOG_BUFFERS and much more other parameters in certain conditions. Most books just say: if you set this parameter too high, you will cause this, if you set it too low, you will have that. But they never say how high is too high and how low is too low. So the readers still don't know how to set them. This is like you tell a new driver who is going to drive on the freeway at his first time: "Don't drive too fast, don't drive too slowly." But he may still don't know how fast is too fast, and how slow is too slow. This book tells you something like: "Don't go faster than 65 miles/hour, don't go slower than 45 miles/hour on the freeway in Wisconsin." It can't be more clear. Even though we still can't just follow the numbers because Oracle databases are much more complicated than driving on the free way. But the numbers give us some ideas about the parameters. We may set or modify ours by considering these suggested values and our specific situations.

I like the discussion on the undocumented init.ora parameters and x$ tables which most books don't touch with. But DBAs should know them.

In many cases the book gives you the two sides of tuning tips and shows the examples. E.g. cost based optimizer is not always better than rule based; CACHE hint is good for small tables, but bad for large tables; 95/5 rule for indexes...

The section `Tuning Using Simple Mathematical Techniques', which shows how a simple quadratic equation can predict the performance, is very interesting. It is my first time to know we can calculate the performance on the scientific basis.

There are a few conflicts with Oracle Documentation. E.g. on P189 and P191, the suggested value for LOG_CHECKPOINT_INTERVAL is 0. However, the Oracle8 Documentation says: "Note that specifying a value of 0 (zero) for the interval might cause interval checkpoints to be initiated very frequently ... Hence, setting the value to 0 is not recommended."

The Chapter 9 is unnecessary repetitious.

It would be better if there were an appendix for the quadratic equations instead of using many pages to discuss them in the text. Because it may be boring for those who have known them well, it may be still not very clear for those who don't know much about them.

On P404, Y0 should be 0 instead of 1 because Y0 is the time needed when 0 rows selected. There should be only one assumption: you need 0 second if you select 0 rows.

Overall it is a very good book for DBAs who are in the real world with couple years tuning experience.