61423-0 The complete developer's guide to optimizing Oracle SQL code. Optimizing SQL code is the #1 factor in improving Oracle database performance. Yet most guides to Oracle tuning virtually ignore SQL. Until now. Oracle SQL High-Performance Tuning zeroes in on SQL, showing how to achieve performance gains of 100% or more in many applications. Expert Oracle developer Guy Harrison presents a detailed overview of SQL processing, and then introduces SQL tuning guidelines that improve virtually any application. Learn how to: *Choose execution plans. *Work with joins. *Search for nulls and ranges. *Trace SQL statement execution. *Build indexes that support efficient queries. *Optimize virtually all type of SQL including: views, hierarchical queries, table scans, GROUP BY, updates, subqueries, distributed SQL, and more. As databases grow, and ad hoc queries to data warehouses increase, optimizing SQL becomes even more critical. Harrison offers practical guidance on using Oracle's parallel query facility for large-volume queries, and shows when to use Oracle's PL/SQL instead of standard SQL.The book is replete with examples, showing poorly tuned SQL, how to fix it-and specific performance measurements collected on a wide range of computer hardware, from high-end UNIX SMP hosts to 486 laptops running Personal Oracle. The CD-ROM contains an extraordinary collection of Oracle SQL tuning tools, including: *A complete freeware Tuning Toolkit for generating SQL execution plans in a Windows environment, and for monitoring server load and efficiency. *Trial versions of the SQLab tuning environment, Precise/SQL tuning tools, and SQL Navigator development environment. *Full-function 90-day trial versions of Personal Oracle for Windows 3.1 and Windows 95. If your Oracle applications must deliver supercharged performance, you can't afford to be without Guy Harrison's Oracle SQL High-Performance Tuning.
FREE Two-Day Shipping for students on millions of items. Learn more |
| ||||||||||||||||||
|
There is a newer edition of this item:
|
Book Description
Editorial Reviews
From the Inside Flap
Introduction
Over recent years, the popularity of the Oracle Relational Database Management System (RDBMS) has increased dramatically. Along with this heightened popularity has come an increasing interest in improving the performance of Oracle-based systems. The heightened emphasis on performance can be attributed to a number of factors:
Oracle databases now tend to be substantially larger than they were in the past. Five years ago, average Oracle databases would have been measured in hundreds of megabytes or less. Nowadays, even “small” Oracle databases are measured in gigabytes.
As the average size of the database increased, so has the user population which the database is expected to support. In its infancy, Oracle databases generally supported non-critical, small-scale applications. Today, Oracle databases increasingly support mission-critical, high-performance, high-volume applications.
The expectations of computer users have increased. Response time delays and throughput rates which would have seemed tolerable in the past are no longer acceptable.
It's quite common for the performance of an Oracle application to appear to be acceptable during development only to abruptly degrade when the application encounters production data volumes and transaction rates. While there are a number of reasons why this occurs, inefficient SQL which fails to maintain good performance as data volumes increase is a major factor.
Poorly performing SQL arises in applications for a number of reasons. Although SQL is a relatively easy language to learn, its non-procedural nature tends to obscure performance-related issues. As a result, it's much harder to write efficient SQL than it is to write functionally correct SQL. Additionally, there seems to be insufficient awareness of the need to carefully monitor and tune SQL performance and the tools and techniques needed to tune SQL are not widely known.
Another factor which has increased the importance of well-tuned SQL is the emergence of “data warehouses” and On-Line Analytical Processing (OLAP) systems. These databases are often extremely large and are subject to a great deal of ad hoc query activity. If the SQL which supports these queries is inefficient, then queries may take hours or even days to complete or may fail to complete at all.
When Oracle applications start to misperform, it's typical for performance experts to be called in to perform benchmark tests or tune the Oracle database engine. For the most part, they will tune the operating system, change Oracle configuration parameters, reconfigure I/O and so on. At the end of the process, you can (if you are lucky) expect a 10 to 20 percent improvement in performance.
What is usually apparent during these tuning exercises is that it is the SQL contained within the application that is the most important factor in determining performance. If the SQL can be tuned, then performance increases of 100 percent or more are not uncommon. But a dilemma exists—by the time performance problems are recognized, it is often difficult to make changes to the production SQL. Furthermore, performance experts usually don't have the application knowledge required to understand and tune the SQL, while the developers don't have the necessary understanding of SQL performance tuning.
It follows that the best way to substantially improve the performance of most Oracle applications is to improve the efficiency of the application SQL. To make this happen, developers needed to acquire SQL tuning skills together with a commitment to tuning.
The objective of this book is to provide SQL programmers with the theory and practice of SQL tuning together with hints and guidelines for optimizing specific SQL statement types. We'll see how to diagnose and correct problems with existing SQL and briefly explore performance issues beyond SQL tuning—such as design and server tuning. By following the guidelines in this book, SQL programmers should be able to write SQL which will perform well both in development and in production and will be able to detect and correct inefficiencies in existing SQL. The result will be SQL which performs to its peak potential.
Why this book?
With the Oracle server documentation set consisting of more than a dozen manuals—including a tuning guide—and a couple of independent Oracle tuning texts on the market, is there really a need for this book?
There is a need, and the basis for this need lies in two fundamental imperfections in all alternative tuning guides—they are aimed almost exclusively at Database Administrators (DBAs), and they gloss over the processes of tuning SQL statements. There is a need for a book which is aimed not at the administrators of the Oracle databases, but at those writing the access routines (e.g., the SQL) for the database—such as application developers, users of data warehouses and others whose work involves writing high-performance SQL.
Additionally, while tuning the database engine can help poorly performing applications, nothing can match improving the efficiency of SQL for getting massive performance improvements. The Oracle tuning guide and other performance tuning texts give primary attention to database server tuning.
Who should use this book?
This is not a book for Oracle DBAs (Data Base Administrators), although DBAs should find things of interest here. Rather, this is a book for anyone who needs to write SQL which has a performance requirement.
People who need to write high-performance SQL are:
Developers of Oracle-based applications. These developers will typically need to embed SQL statements within the code of the development tool (such as SQL*Windows, Powerbuilder or Visual Basic). Alternately, the SQL may be contained within stored procedures which they will call from their client tool. These SQL statements will need to be efficient, otherwise the applications concerned will fail to meet reasonable performance requirements.
Those querying data warehouses or decision-support type databases. These databases are typically very large and hence these queries must run efficiently, otherwise they may take an unreasonable time to complete (or not complete at all).
Anyone who writes Oracle SQL statements and cares about their response time or throughput.
How to use this book
Very few people read a book of this type from beginning to end. Depending on your background, you may wish to skip sections which review database theory and jump right into the details of SQL tuning. However, apart from the “Review of SQL” and the “Beyond SQL Tuning” section, most readers should attempt to read or at least review most of this book.
The book has the following major sections:
Introduction
Over recent years, the popularity of the Oracle Relational Database Management System (RDBMS) has increased dramatically. Along with this heightened popularity has come an increasing interest in improving the performance of Oracle-based systems. The heightened emphasis on performance can be attributed to a number of factors:
Oracle databases now tend to be substantially larger than they were in the past. Five years ago, average Oracle databases would have been measured in hundreds of megabytes or less. Nowadays, even “small” Oracle databases are measured in gigabytes.
As the average size of the database increased, so has the user population which the database is expected to support. In its infancy, Oracle databases generally supported non-critical, small-scale applications. Today, Oracle databases increasingly support mission-critical, high-performance, high-volume applications.
The expectations of computer users have increased. Response time delays and throughput rates which would have seemed tolerable in the past are no longer acceptable.
It's quite common for the performance of an Oracle application to appear to be acceptable during development only to abruptly degrade when the application encounters production data volumes and transaction rates. While there are a number of reasons why this occurs, inefficient SQL which fails to maintain good performance as data volumes increase is a major factor.
Poorly performing SQL arises in applications for a number of reasons. Although SQL is a relatively easy language to learn, its non-procedural nature tends to obscure performance-related issues. As a result, it's much harder to write efficient SQL than it is to write functionally correct SQL. Additionally, there seems to be insufficient awareness of the need to carefully monitor and tune SQL performance and the tools and techniques needed to tune SQL are not widely known.
Another factor which has increased the importance of well-tuned SQL is the emergence of “data warehouses” and On-Line Analytical Processing (OLAP) systems. These databases are often extremely large and are subject to a great deal of ad hoc query activity. If the SQL which supports these queries is inefficient, then queries may take hours or even days to complete or may fail to complete at all.
When Oracle applications start to misperform, it's typical for performance experts to be called in to perform benchmark tests or tune the Oracle database engine. For the most part, they will tune the operating system, change Oracle configuration parameters, reconfigure I/O and so on. At the end of the process, you can (if you are lucky) expect a 10 to 20 percent improvement in performance.
What is usually apparent during these tuning exercises is that it is the SQL contained within the application that is the most important factor in determining performance. If the SQL can be tuned, then performance increases of 100 percent or more are not uncommon. But a dilemma exists—by the time performance problems are recognized, it is often difficult to make changes to the production SQL. Furthermore, performance experts usually don't have the application knowledge required to understand and tune the SQL, while the developers don't have the necessary understanding of SQL performance tuning.
It follows that the best way to substantially improve the performance of most Oracle applications is to improve the efficiency of the application SQL. To make this happen, developers needed to acquire SQL tuning skills together with a commitment to tuning.
The objective of this book is to provide SQL programmers with the theory and practice of SQL tuning together with hints and guidelines for optimizing specific SQL statement types. We'll see how to diagnose and correct problems with existing SQL and briefly explore performance issues beyond SQL tuning—such as design and server tuning. By following the guidelines in this book, SQL programmers should be able to write SQL which will perform well both in development and in production and will be able to detect and correct inefficiencies in existing SQL. The result will be SQL which performs to its peak potential.
Why this book?
With the Oracle server documentation set consisting of more than a dozen manuals—including a tuning guide—and a couple of independent Oracle tuning texts on the market, is there really a need for this book?
There is a need, and the basis for this need lies in two fundamental imperfections in all alternative tuning guides—they are aimed almost exclusively at Database Administrators (DBAs), and they gloss over the processes of tuning SQL statements. There is a need for a book which is aimed not at the administrators of the Oracle databases, but at those writing the access routines (e.g., the SQL) for the database—such as application developers, users of data warehouses and others whose work involves writing high-performance SQL.
Additionally, while tuning the database engine can help poorly performing applications, nothing can match improving the efficiency of SQL for getting massive performance improvements. The Oracle tuning guide and other performance tuning texts give primary attention to database server tuning.
Who should use this book?
This is not a book for Oracle DBAs (Data Base Administrators), although DBAs should find things of interest here. Rather, this is a book for anyone who needs to write SQL which has a performance requirement.
People who need to write high-performance SQL are:
Developers of Oracle-based applications. These developers will typically need to embed SQL statements within the code of the development tool (such as SQL*Windows, Powerbuilder or Visual Basic). Alternately, the SQL may be contained within stored procedures which they will call from their client tool. These SQL statements will need to be efficient, otherwise the applications concerned will fail to meet reasonable performance requirements.
Those querying data warehouses or decision-support type databases. These databases are typically very large and hence these queries must run efficiently, otherwise they may take an unreasonable time to complete (or not complete at all).
Anyone who writes Oracle SQL statements and cares about their response time or throughput.
How to use this book
Very few people read a book of this type from beginning to end. Depending on your background, you may wish to skip sections which review database theory and jump right into the details of SQL tuning. However, apart from the “Review of SQL” and the “Beyond SQL Tuning” section, most readers should attempt to read or at least review most of this book.
The book has the following major sections:
Introduction
This section (the preamble to which you are reading now). This section contains a review of the importance of SQL tuning and an overview of the tuning process.
Review of SQL
This section reviews the history and basic functionality of the SQL language and may be useful for those who are relatively new to SQL. The section defines basic SQL concepts which are used later in the book. Those experienced in SQL will probably skip or only skim this section.
SQL Processing and Indexing
This section explains the mechanisms by which Oracle interprets an SQL statement and retrieves or alters the data specified. This section introduces a number of very important topics, such as the role of the query optimizers, indexing and hashing concepts; SQL parsing; and basic data retrieval strategies. Although this section is heavy on theory, it's difficult to successfully tune SQL without at least a broad understanding of these topics. All readers are encouraged to read this section.
Tracing SQL Execution
This section explains how SQL processing can be traced and interpreted. Understanding the tracing and diagnostic utilities is a basic prerequisite for SQL tuning. Unless you feel very familiar with the tkprof tool and the EXPLAIN PLAN statement, you should not skip this chapter.
Tuning SQL
This section contains specific tuning guidelines for specific SQL statement types and circumstances. While it will be useful to read this section from start to finish, this is a part of the book which may be used as a reference. You may wish to consult the relevant portions of this section as appropriate tuning requirements arise. Specific chapters in this section are:
Tuning table access
Tuning joins and subqueries
Sorting and aggregation
Data Manipulation Statements
PL/SQL statements
Parallel SQL
Miscellaneous topics
SQL Tuning Case Studies
This section consists of a number of SQL tuning examples, showing SQL statements and traces from the start of the tuning process to the end. In a practical sense, this section is intended to illustrate the theory, techniques and principles covered in previous sections. As
From the Back Cover
The complete developer's guide to optimizing Oracle SQL code.
Optimizing SQL code is the #1 factor in improving Oracle database performance. Yet most guides to Oracle tuning virtually ignore SQL. Until now. Oracle SQL High-Performance Tuning zeroes in on SQL, showing how to achieve performance gains of 100% or more in many applications.
Expert Oracle developer Guy Harrison presents a detailed overview of SQL processing, and then introduces SQL tuning guidelines that improve virtually any application. Learn how to:
- Choose execution plans.
- Work with joins.
- Search for nulls and ranges.
- Trace SQL statement execution.
- Build indexes that support efficient queries.
- Optimize virtually all type of SQL including: views, hierarchical queries, table scans, GROUP BY, updates, subqueries, distributed SQL, and more.
As databases grow, and ad hoc queries to data warehouses increase, optimizing SQL becomes even more critical. Harrison offers practical guidance on using Oracle's parallel query facility for large-volume queries, and shows when to use Oracle's PL/SQL instead of standard SQL.
The book is replete with examples, showing poorly tuned SQL, how to fix it—and specific performance measurements collected on a wide range of computer hardware, from high-end UNIX SMP hosts to 486 laptops running Personal Oracle.
The CD-ROM contains an extraordinary collection of Oracle SQL tuning tools, including:
- A complete freeware Tuning Toolkit for generating SQL execution plans in a Windows environment, and for monitoring server load and efficiency.
- Trial versions of the SQLab tuning environment, Precise/SQL tuning tools, and SQL Navigator development environment.
- Full-function 90-day trial versions of Personal Oracle for Windows 3.1 and Windows 95.
If your Oracle applications must deliver supercharged performance, you can't afford to be without Guy Harrison's Oracle SQL High-Performance Tuning.
Product Details
Would you like to update product info or give feedback on images?
|
More About the Author
Guy Harrison has worked with Oracle databases as a developer, administrator and performance expert for more than two decades. He is the author of many articles and several books on database technology including Oracle Performance Survival Guide and MySql Stored Procedure Programming .
Guy is currently a Director of Development at Quest Software and is the Chief Architect of Quest's popular Spotlight(tm) product family. He lives in Melbourne, Australia with his wife Jenni and children Chris, Kate, Mike and William.
Guy is currently a Director of Development at Quest Software and is the Chief Architect of Quest's popular Spotlight(tm) product family. He lives in Melbourne, Australia with his wife Jenni and children Chris, Kate, Mike and William.
Customer Reviews
|
Share your thoughts with other customers:
|
||||||||||||||||||||||
|
Most Helpful Customer Reviews
9 of 9 people found the following review helpful:
4.0 out of 5 stars
This IS tuning the database (the other stuff isn't!),
By
This review is from: Oracle SQL High-Performance Tuning (2nd Edition) (Paperback)
If the idea of slow database response scares you, this is a good place to start. You hear "tuning a database" a lot, but most of it comes too late. There are some queries that no disks, no CPU, and no amount of memory can speed up. Guy Harrison (well named) is expert on tuning SQL, and knows how to write about it in a way that makes it easy for you to benefit.Besides giving you lots examples of how SQL and PL/SQL can work well, Guy shows you how to use the tools (explain plan, SQL*Trace, Tkprof) that let you know when you are getting close. Actually, he points out that you need to set performance goals early on, and keep testing to see if you are on track; if you don't do that early, it may be too late when you realize you need help. One of my favorite sections is on tuning joins and sub-queries and the accompanying graph showing 197,664 block gets if you do it wrong (wrong index), and 45 if you do it right (using pl/sql instead of correlated subqueries). The title of this review aside, Guy has good sections on tuning an instance, looking at instance settings and hardware, that can be helpful if you get the application working well and still have slow response times, but most of the chances to have an application that works are available during planning and development, and this book defintely helps there. If you like working with Oracle, and want to know more, you will benefit from this book.
9 of 9 people found the following review helpful:
5.0 out of 5 stars
Got me out of a jam,
By A Customer
This review is from: Oracle SQL High-Performance Tuning with CDROM (Paperback)
Turned right to the page I needed and got started on the solution.What out-dated material? Did they move the SGA? Are we not using SQL anymore? This is an outstanding book by an author with actual experience in the field who's taken the time to produce extensive examples. Not just the trivial examples for syntax diagrams, Mr. Harrison demonstrates the discipline required of tuning, performance testing, data gathering and analysis. My copy has pages 59-61 and is just about error-free. I suppose it may be possible that the word "select" may have been mis-spelled somewhere, but if you get stuck on that, you've probably got bigger problems that need to be dealt with before buying this book. Who is it that writes those negative reviews; a competitive author or just someone with a brain-fart? It skews the results unfairly. Shouldn't stand-out books like these have something more relevant than just a blank value judgement? It was a real jaw-dropper to see "unuseful" in a review about this book after having landed a contract to implement one of the techniques straight off the page. If you get nothing else out of this book, know that tuning SQL will give you orders of magnitude improvement in performance, while fiddle-farting with the init.ora will get you a percentage, in other words, not even a factor. Which would you rather have...10% or 10 times? I do have one complaint...the new cover is too gaudy, if not down-right garish. But then, I bought it for the content, not the cover.
11 of 12 people found the following review helpful:
3.0 out of 5 stars
Useful, but flawed,
By
This review is from: Oracle SQL High-Performance Tuning (2nd Edition) (Paperback)
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.
Share your thoughts with other customers: Create your own review
|
|
What Other Items Do Customers Buy After Viewing This Item?
Suggested Tags from Similar Products(What's this?)Be the first one to add a relevant tag (keyword that's strongly related to this product).
|
Sell a Digital Version of This Book in the Kindle Store
If you are a publisher or author and hold the digital rights to a book, you can sell a digital version of it in our Kindle Store.
Learn more
Customer Discussions
|
This product's forum
Active discussions in related forums
Search Customer Discussions
|
Related forums
|
Listmania!
|
|
