Oracle High-Performance SQL Tuning

Book Description

From the official Oracle Press comes a comprehensive guide to tuning SQL statements for optimal execution. This expert resource explains how to view the internal execution plan of any SQL statement and change it to improve the performance of the statement. You'll get details on Oracle's optimizer modes, SQL extensions, the STATSPACK utility, and a wealth of methods for tuning Oracle SQL statements.

Customer Reviews:

Good stuff!.......2007-08-09

Still working my way through it, but have already gleaned several insights from it. This gives me information I had not yet found in Oracle manuals. (I'm an application developer, trying to work productively with database administrators and system administrators.)

Ancient coverage.......2006-02-07

I'm sure that this was a great book in it's day, but it is 5 years old and useful only for firewood. The rule optimizer is not even there anymore. [...]

Good book about the internals of SQL.......2002-11-27

It is a good book for those interested in the internals of sql. The knowledge of the author about the matter is excellent, and his writing style is clear, simple, and funny.

Good book for tuning complex SQL.......2001-11-27

I have a very complex system with lots of subqueries, and I was unable to find good tuning information until I tried the techniques in this book.

The book is simple to understand and explains the internals of complex Oracle SQL operations.

I have been very heppy with the results from my tuning, and I am thankful to find this textbook.

This is a most excellent text.......2001-09-30

I bought a copy of this book in hopes of tuning the SQL in my Oracle8i database and I was not disappointed. When using the techniques in the book, I was able to quickly identify and tune the most resource intensive SQL statements in my system. The book is clear and easy to understand.

Oracle High Performance Tuning for 9i and 10g

Average customer rating:

underwhelmed
Very beginner's book
Excellent book for new DBA's and good review book for the experienced
Vague and primitive
Worth the Investment

Oracle High Performance Tuning for 9i and 10g
Gavin JT Powell
Manufacturer: Digital Press
ProductGroup: Book
Binding: Paperback

Database Management Systems | Databases | Computers & Internet | Subjects | Books

General | Databases | Computers & Internet | Subjects | Books

General | Software | Computers & Internet | Subjects | Books

Look Inside Computer Books | Trip | Specialty Stores | Books

All Amazon Upgrade | Amazon Upgrade | Stores | Books

Computers & Internet | Amazon Upgrade | Stores | Books

All Titles | Qualifying Textbooks - Fall 2007 | Stores | Books

Computers & Internet | Qualifying Textbooks - Fall 2007 | Stores | Books
Similar Items:

ASIN: 1555583059

Book Description

There are three parts to tuning an Oracle database: data modeling, SQL code tuning and physical database configuration.

A data model contains tables and relationships between tables. Tuning a data model involves normalization and de-normalization. Different approaches are required depending on the application, such as OLTP or a Data Warehouse. Inappropriate database design can make SQL code impossible to tune. Poor data modeling can have a most profound effect on database performance since all SQL code is constructed from the data model.

Poorly written SQL code is often a culprit of performance problems and is expensive to rectify. However, tuning of SQL code is generally cheaper than changing the data model. SQL code tends to be contained inside independent blocks within applications or stored procedures.

Physical database tuning involves hardware resource usage, networking and various other Oracle things such as configuration and file distribution. Physical configuration is often a culprit of poor performance where Oracle is installed with defaults, and never altered by an expert.

*Includes all three aspects of Oracle database tuning: data model tuning, SQL & PL/SQL code tuning, physical plus configuration tuning
*Contains experienced guidance and real-world examples using large datasets *Emphasizes development as opposed to operating system perspective

Customer Reviews:

underwhelmed.......2007-05-14

I am sure this guy knows his stuff, but it sure didn't come through very clearly. I would recommend a re-write with a new editor. There didn't seem to be a smooth transistion from introduction to the deep dive. Examples are explanations of checkpoint and latches.

By the way, the Index was horrible.

Maybe it is because he came from a dev environment as opposed to systems viewpoint. Anyway, not impressed with the overall presentation, which is a shame because it looks like he has paid his dues and has something to contribute.

Very beginner's book.......2006-12-29

Before you buying it, please do take a look at the content pages. I believe every oracle dba who has over 1 year experience will be disappointed by those basic topics.
I would say, it might be a good book for trainning java developers, who have to write some embeded SQL codes.

Excellent book for new DBA's and good review book for the experienced.......2006-06-19

This is an excellent book for the new someone new to performance tuning. It gives very clear and simple explanation to the approach to PT. It is a good mix of technical scripts and verbose. The author is not too chatty neither he is boring. He brings out complex ideas in a simple way without too much of mathematical jargons. Definately recommended.

Update
=======

Well with all the tuning books available now... I would say this in not the top of the line performance book. Its good but if I could I would give 3.5 stars out of 5

Vague and primitive.......2006-01-21

I have read this book and regrettably found it primitive and obscure. The author seems to have a bit of Java and development knowledge but not much of knowledge as a designer or DBA. His explanation of various items are rudimentary and shallow. When he talks about Cost based optimizer and the way it is used in Oracle, it is pretty clear to me that the author is very light in understanding the basic concepts. There are also a lot of technical errors in describing various constructs and utilities. I think this book will be useful for newcomers but hardly is a "High Performance Tuning book" as the book's title says. There are much better books around. Any of Tom Kyte's books will leave this book standing.

Worth the Investment.......2005-11-23

In his book "Oracle High Performance Tuning for 9i and 10g", Mr Powell has provided a methodical framework for addressing a complex challenge by covering all three logical aspects - data model, SQL and physical - of tuning in highly readable and practical format.

The difficulty in Oracle tuning stems from the fact that the process typically involves many skill sets - from data modeling to application development to database and network administration. In between, throw in the quintessential queueing theory, hardware advances, models in concrete, domain knowledge, etc. and it can really stretch both the depth and breadth of your team's or your capabilities and interests. Mr Powell was able to address most of these issues through invaluable pointers throughout the book and kept me interested to explore the pages.

I was thus fortunate to find this book during the middle of a database migration project. Using it as a guide, I managed to put the principles (not heavy-duty theories) and working samples (yes! they worked beautifully) into action amid tight timelines. If you put in the time and effort to go through the chapters and examples, you will easily understand why I, who has for sometime stop buying IT books, consider this book as having one of the highest ROI in my library so far.

Where I find useful, but not available, are the sample databases and perhaps the inclusion of graphical query paths in the samples to illustrate the design of SQL re-write.

Overall, the book has offered a balanced, practical treatment of the subject in a concise, intelligent format. But above all and to me most important, it has been a pleasure to read. Thanks for the sharing and keep up the writing, Mr Powell!

Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming (Oracle In-Focus series)

Average customer rating:

sql tuning

Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming (Oracle In-Focus series)
Timothy Hall
Manufacturer: Rampant Techpress
ProductGroup: Book
Binding: Paperback

Relational Databases | Databases | Computers & Internet | Subjects | Books

Database Management Systems | Databases | Computers & Internet | Subjects | Books

General | Databases | Computers & Internet | Subjects | Books

General | Software | Computers & Internet | Subjects | Books

Look Inside Computer Books | Trip | Specialty Stores | Books
Similar Items:

ASIN: 097615739X

Book Description

With tools for gaining as many as 30 times the execution speed, this handbook educates database administrators on how to rewrite SQL into PL/SQL and how to use advanced Oracle bulk array processing techniques to achieve high performance. Ready-to-use tips for saving hardware costs by running applications at peak efficiencies are provided for senior Oracle DBAs and developers, and powerful PL/SQL techniques are outlined.

Customer Reviews:

sql tuning.......2007-09-17

This book sucks; they should call this book useless way to try to improve the performance of oracle. For instance the author simulates a slow sql statement by using example of a state that counts the time for a number of seconds, like of like a sleep command. Doing this for a number for year I never came across a sleep or wait command for slow query. The authors also never say why or go into detail about his suggestion. It is like he does not really know what he is talking about but gives you example of the problem and solution but never really goes into why.

Oracle SQL High-Performance Tuning (2nd Edition)

Average customer rating:

Fantastics Oracle SQL Tuning Book!
Useful, but flawed
There is no other book on SQL Tuning
A must for DBAs and plus for developers, One of the Best
Great tuning book

Oracle SQL High-Performance Tuning (2nd Edition)
Guy Harrison
Manufacturer: Prentice Hall PTR
ProductGroup: Book
Binding: Paperback

Relational Databases | Databases | Computers & Internet | Subjects | Books

Database Management Systems | Databases | Computers & Internet | Subjects | Books

General | Databases | Computers & Internet | Subjects | Books

General | Programming | Computers & Internet | Subjects | Books

General | Software | Computers & Internet | Subjects | Books

Look Inside Computer Books | Trip | Specialty Stores | Books

All Titles | Qualifying Textbooks - Fall 2007 | Stores | Books

Computers & Internet | Qualifying Textbooks - Fall 2007 | Stores | Books

Professional | Qualifying Textbooks - Fall 2007 | Stores | Books
Similar Items:

ASIN: 0130123811

Customer Reviews:

Fantastics Oracle SQL Tuning Book!.......2006-03-01

What a perfect book describing nearly all the method to optimize performance(e.g. index, cluster, explain plan trace, tuning table access and joins, using hints etc) for developers. It provides a clear and detail explanation and somtimes illustration on each optimization methods. The book was written in plain English and hence can be easily understood.

Useful, but flawed.......2003-12-24

Of all the SQL tuning books available on the market, this one provides the most depth. It provides solid, easy to follow examples. The flaw in this book is that it just provides 'techniques' for improving performance. It's basically a book that is useful for trial and error SQL tuning.

When tuning SQL you should think in terms of sets and essentially 'What can I do to make the optimizer do the least work?' This book does not teach you to do that. I've yet to see one book that does. It also only discusses response time. Response time is an ends and is not a means in SQL tuning. By reducing the amount of work Oracle has to do, you improve response time.

There is one serious inaccuracy in this book. I emailed the author about this and he did not respond. The author states that you can improve response time of updates, by wrapping them inside of a PL/SQL cursor. This is not only inaccurate, it's not even close. Not only does it take twice as long in Oracle 8i(slightly less in 9i), but it also increases logical I/Os significantly. How something this inaccurate could be missed in a major publication astounds me. To be fair, the 8i version of Steven Fuersteins PL/SQL book has the same inaccuracy(I have not read the 9i version).

The rest of the book seems accurate. I recommend it, but beware that inaccuracy. I have not tested everything the author has stated, but I have not found any other inaccuracies.

There is no other book on SQL Tuning.......2003-11-11

I have read this book cover to cover and more. I have been using this book since 1998 and I still have to say it is good book.It is one of the Oracle Classics. It is a little outdated. If you look at the cover it clearly states it covers to oracle 8i. If you still have 8i databases this is a good book. if you are using 9i and cost based optimization it is still useful but it will not pick up on some of the newer fetures of the CBO. If you use Rule based optimization(RBO) than it is a good book. I had actually used this book as a teaching guide when I use to teach SQL Tuning for an application software company who uses oracle database. I look forward to when the revised version does ship.

A must for DBAs and plus for developers, One of the Best.......2002-12-16

Oracle performance tuning has become more critical for dba's and developers to master as database are approaching 10 plus terabytes. Some of the newest concepts have really improved performance and speed in which data can be found and retrieved. Some of this concepts include function based indexes, bitmap index, partitioning, parallel queries, and optimization hints. The basic starting permise is to make sure your system has enough memory and hardware processing speed. Two branches of thought have emerged when focusing on performance improvement: improved system tuning and improved Optimizer decisions. The Oracle DBA must become familar with how to tune operating system performance and database performance. Some of the valuable indicators are data cache hit ratios, Disk I/O activity, library cache, buffer hit ratios, and shared pool memory. Various views, scripts, tools, and data tables give the DBA insight into how the database is performing. It is safe to assume the database will grow and performance will vary from time to time. By monitoring and adjusting parameters the Oracle database can achieve relative performance to growth. If performance drops add more hardware or change object schema designs to reflect the data loads. Oracle has offered two amazing database architectures: dedicated server and Multiple Transaction Server. Each architecture has its advantages. From a developer's view performance of the schema objects are important, such as, tables, views, indexes, and PL/SQL. The Optimizer evaluates system statistics to decide how to retrieve data, whether it be by full table scans, index range scans, or join table sorts. Each type of index should be evaluated: single indexes, multiple field indexes, partitioned indexes, bitmap indexes, and function indexes. In some cases, it will make sense to use bitmap indexes for data that is relatively static. In other cases it will make sense to use function based indexes where data search on alpha data is complex. Yet in another case it will make sense to use a partitioned index where the data tables have been spread over multiple partition sectors and datafiles. Sometimes usage of indexes is not need and full tables scans are better. The developer uses the optimizer execution plan to see how the data will be retrieved. At first the execution plan may seem confusing, but understanding Oracle database architecture will make the plan readable. I've personally found that keeping the database statistic current and using oracle hints (ordered,index,and parallel) provided the best return on my performance improvements. Table joins is another critical area of performance tuning to grasp. There are a number of join types: nested loops, sort merge, cluster, hash, and index. The optimizer decides which type of join to use based on statistics and available indexes and constraints. The helpful advice, I can give you for purchasing this book is that there are PL/SQL optimization techniques to consider. One I found helpful was to use Packaged procedure calls from triggers rather than putting the PL/SQL code to execute in the trigger. I recommend adding this book to your library.

Great tuning book.......2001-11-14

I had a nagging and peculiar performance problem with a specific query involving a quirk in the Oracle optimizer which I had been trying to solve on and off for months. With the help of this book, I resolved the problem. It has a level of detail in query optimization way beyond other "highly recommended" books. The explanations are clear, *complete* (completeness was lacking in the other books), and easy and enjoyable to read. I finally have the understanding of these Oracle internals that I have been seeking!

Oracle Tuning Power Scripts: With 100+ High Performance SQL Scripts (Oracl in-Focus)

Average customer rating:

Oracle Tuning Power Scripts: With 100+ High Performance SQL Scripts (Oracl in-Focus)
Harry Conway , and Mike Ault
Manufacturer: Rampant Techpress
ProductGroup: Book
Binding: Paperback

Database Management Systems | Databases | Computers & Internet | Subjects | Books

General | Databases | Computers & Internet | Subjects | Books

General | Software | Computers & Internet | Subjects | Books
Similar Items:

ASIN: 0974448672

Book Description

Targeted at Oracle professionals who need fast and accurate working examples of complex issues, Oracle In-focus books target specific areas of Oracle technology in a concise manner. Plenty of working code is provided without a lot of theory, allowing database managers to solve their problems quickly without reviewing data that they already know. All code scripts are available for instant download from a companion web site.

High Performance Oracle8 SQL Programming and Tuning

Average customer rating:

Dont buy this book
My most prized SQL tuning reference!
Mediocre Effort At Best
This book is the best I have read for serious tuning.
fantastic!

High Performance Oracle8 SQL Programming and Tuning
Pete Cassidy
Manufacturer: Coriolis Group Books
ProductGroup: Book
Binding: Paperback

Database Management Systems | Databases | Computers & Internet | Subjects | Books

General | Databases | Computers & Internet | Subjects | Books

General | Programming | Computers & Internet | Subjects | Books

Customer Reviews:

Dont buy this book.......2000-09-08

If you want to learn SQL tuning this is not hte book. I guess the author has just filled the book. All, I learned in the end is use indexes (OK!!!). Using indexes is only one part of SQL tuning. There are other issues which have not been explored at all. It is a sheer waste of money to buy this book. I would rather recommend other fine books by Oracle Press and Prentice Hall on Tuning. Bottomline, Dont buy this book. I would have given it a Zero Star if one was available. It was a waste of my money.

My most prized SQL tuning reference!.......1999-04-19

I have other tuning references, but this is my favorite. The real-life examples and results have saved me hours of frustration. I especially like the chapter on EXPLAIN PLAN, which contains an excellent explaination of the results. I highly recommend it to other DBA's/Developer's.

Mediocre Effort At Best.......1999-03-11

I've been programming with Oracle for two and a half years now. I felt that a "High Performance" programming book might be able to help me out, so I opted for this book. While some of the suggestions are helpful, I don't find most of them to be all that special. The basic concept of the book is, "use Explain Plan" (ie tune your code) and "Use indexes when you can" - Oooh ! That's insightful!

Much of the book is wasted space with the author showing the various permutations of his explain plan output. Four sentences of programming suggestions, followed by two pages of output, doesn't make for particularly interesting reading in my world.

Granted, the book gives you more, and in better layout than most Oracle texts out there, but I guess I was hoping for more.

This book is the best I have read for serious tuning........1999-01-07

I especially like this book because of the real world examples. Pete has taken the time to run many tuning examples. Other tuning books that I have read have few real examples. The section on Analyzing Tables is excellent!

fantastic!.......1998-12-17

Pete Cassidy has written the most detailed Oracle Tuning book ever. He includes timings and Explain Plans for queries using a 1-million row table. He places all of the new Oracle8 enhancements in the last two chapters. I highly recommend this book to anyone who ever writes Oracle SQL statements. Great job Pete!

Average customer rating:

Oracle High-Performance SQL Tuning
BURLESON
Manufacturer: McGraw-Hill Osborne Media
ProductGroup: Book
Binding: Paperback

Average customer rating:

Oracle SQL High-Performance Tuning (2nd Edition)
Guy Harrison
Manufacturer: NY
ProductGroup: Book
Binding: Paperback

Books:

Books Index

Books Home

Recommended Books