Oracle SQL High-Performance Tuning

( 1 )

Overview

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. As databases grow, and ad hoc queries to data warehouses increase, ...
See more details below
Available through our Marketplace sellers.
Other sellers (Multimedia Set)
  • All (13) from $1.99   
  • New (3) from $26.40   
  • Used (10) from $1.99   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$26.40
Seller since 2008

Feedback rating:

(169)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New
0136142311 BRAND NEW NEVER USED IN STOCK 125,000+ HAPPY CUSTOMERS SHIP EVERY DAY WITH FREE TRACKING NUMBER

Ships from: fallbrook, CA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
$60.00
Seller since 2014

Feedback rating:

(181)

Condition: New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
$60.00
Seller since 2014

Feedback rating:

(181)

Condition: New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

Overview

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. 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.


This SQL code optimization guide overviews SQL, focusing on the theory and practice of SQL processes, tuning and optimization. Discusses principles of indexing and clustering, and shows you how to use tools to trace SQL processes. It guides you through practical tuning and optimization of table access, joins, subqueries, sorting, grouping, and data manipulation statements. Analyzing pros/cons of SQL substitution with PL/SQL and the use of parallel SQL, the book presents tuning case studies. Good data model and application design are emphasized. CD-ROM contains trial product versions, freeware tuning toolkit and monitoring utility. Users must be familiar with SQL and ORACLE RDBMS.

Read More Show Less

Editorial Reviews

From Barnes & Noble

Fatbrain Review

This SQL code optimization guide overviews SQL, focusing on the theory and practice of SQL processes, tuning and optimization. Discusses principles of indexing and clustering, and shows you how to use tools to trace SQL processes. It guides you through practical tuning and optimization of table access, joins, subqueries, sorting, grouping, and data manipulation statements. Analyzing pros/cons of SQL substitution with PL/SQL and the use of parallel SQL, the book presents tuning case studies. Good data model and application design are emphasized. CD-ROM contains trial product versions, freeware tuning toolkit and monitoring utility. Users must be familiar with SQL and ORACLE RDBMS.
Booknews
A guide for Oracle developers and database administrators, covering every aspect of Oracle 8/8i SQL and PL/SQL tuning, with specific recommendations, detailed case studies, and real benchmarks. Coverage includes designing applications to maximize efficiency, building indexes, identifying and resolving Oracle Server bottlenecks, fixing poorly performed SQL code, and leveraging Oracle's parallel query facility. Harrison is author of several books on Oracle. Annotation c. Book News, Inc., Portland, OR (booknews.com)
Read More Show Less

Product Details

  • ISBN-13: 9780136142317
  • Publisher: Prentice Hall Professional Technical Reference
  • Publication date: 5/12/1997
  • Edition description: Book and CD
  • Edition number: 1
  • Pages: 491
  • Product dimensions: 7.01 (w) x 9.22 (h) x 1.45 (d)

Meet the Author

GUY HARRISON has worked with Oracle databases for more than a decade, has conducted many Oracle training seminars, and is author of several books on Oracle, including Oracle Desk Reference (Prentice Hall PTR). Currently a product architect at Quest Software, Harrison has conducted many Oracle training seminars and has authored several articles for the Oracle Technical Journal. He resides in Australia.
Read More Show Less

Read an Excerpt

PREFACE:

Introduction

This book is about tuning Oracle databases and applications with an emphasis on the tuning of SQL statements. Tuning SQL is not the only way to tune an application: The design of an application will often dictate its performance limits, and tuning the physical layout of an Oracle database can be critical to reaching those limits. However, tuning SQL is usually the most cost-effective way of improving the performance of an existing application, while other measures—such as changing database parameters or altering disk layouts—will usually be ineffective unless the application's SQL is properly tuned.

It is common for the performance of an Oracle application to appear to be acceptable during development only to degrade abruptly when the application encounters production data volumes or transaction rates. While this may result from a number of causes, inefficient SQL that 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 nonprocedural nature tends to obscure performance-related issues. As a result, its 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 monitor carefully and tune SQL performance, and the tools and techniques needed to tune SQL are not sufficiently well known.

Another factor that has increased the significance of well-tuned SQL is the emergence of data warehouses or On-Line Analytical Processing (OLAP) systems. These databases are often extremelylarge and are subject to a great deal of ad hoc query activity. If the SQL that 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 underperform, 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 input/output (I/O), disks and so on. At the end of the process, you can (if you are lucky) expect a 10 to 20 % improvement in performance.

During these tuning exercises it is usually that apparent the SQL contained within the application 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 there is a dilemma: 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 improve substantially 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 application design and server tuning. By following the guidelines in this book, SQL programmers should be able to write SQL that 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 that performs to its peak potential.

The need for this book

With the Oracle server documentation set consisting of more than a dozen manuals—including a tuning guide—and a number 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 that is aimed not at the administrators of the Oracle databases, but at those writing the access routines (that is, 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. Unfortunately, most tuning texts spend most of their time focusing on database and I/O subsystem tuning.

Who should use this book

This is not a book for Oracle DBAs, although DBAs should find many things of interest here. Rather, this is a book for anyone who needs to write SQL that has a performance requirement.

People who need to write high-performance SQL are as follows:

  • Developers of Oracle-based applications. These developers will typically need to embed SQL statements within the code of the development tool (such as C++, Java, or Visual Basic). Alternately, the SQL may be contained within stored procedures that 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 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

Few people read a book of this type from beginning to end. Depending on your background, you may wish to skip sections that review database theory and jump right into the details of SQL tuning.

However, apart from the "Review of SQL" and the "Beyond SQL Tuning" sections, most readers should attempt to read or at least review the majority of this book.

The book has the following major sections:

Part I: Introduction to SQL Tuning

This section contains a review of the importance of SQL tuning, an overview of the tuning process and a review of SQL. The chapters in Part I are as follows:

  • Chapter 1: Introduction to SQL Tuning
  • Chapter 2: SQL Tuning Quick Start
  • Chapter 3: Review of SQL

Part II: SQL Tuning theory

Chapters in Part II introduce a number of important topics, such as the role of the query optimizers, indexing and hashing concepts, SQL parsing, basic data retrieval strategies, and tools for explaining and tuning SQL execution. Although Part II is heavy on theory, its difficult to tune SQL successfully without at least a broad understanding of these topics. All readers are therefore encouraged to read this section.

The chapters in Part II are as follows:

  • Chapter 4: SQL Processing Internals
  • Chapter 5: The Optimizer
  • Chapter 6: Indexing and Clustering
  • Chapter 7: Tracing and Explaining SQL

Part III: SQL Tuning in Practice

Chapters in Part III contain tuning guidelines for specific SQL statement types and circumstances. While it will be useful to read Part III from start to finish, it may also be used as a reference. You may wish to consult the relevant portions of this section as appropriate tuning requirements arise. Chapters in Part III are as follows:

  • Chapter 8: Tuning Table Access
  • Chapter 9: Tuning Joins and Subqueries
  • Chapter 10: Sorts, Aggregates, and SET Operations
  • Chapter 11: Parallel SQL
  • Chapter 12: Optimizing DML
  • Chapter 13: VLDB and Warehousing
  • Chapter 14: Using and Tuning PL/SQL
  • Chapter 15: Using and Tuning Oracle Java
  • Chapter 16: Oracle Object Types
  • Chapter 17: Miscellaneous Topics

Part IV: Beyond SQL Tuning

At the beginning of the application life cycle, effective database and application design can define the constraints that will ultimately determine the limits on your SQL's performance. For a well-designed application with tuned SQL, the configuration of your database—disk layouts, SGA configuration, etc.—may be the key to getting further gains in performance. Chapters in Part IV discuss these "beyond SQL" issues:

  • Chapter 18: Application Design Issues
  • Chapter 19: Oracle Server Design
  • Chapter 20: Oracle Server Tuning

Appendices

The appendices contain details of configuring client programs and the Oracle server for specific circumstances, a reference guide, and a guide to further reading and other resources.

The Sample Database

Whenever possible, any SQL tuning principle in this book will be illustrated with an example SQL statement. Usually, these SQL statements will be based on the sample database shown in Figure P-1. This database is not intended to illustrate good or bad data modeling principles but to be a basis for illustrating a wide range of SQL statements. You can find an export of one of the variations of this database at the book's website.

The sample database implements a simple and familiar business schema containing Customers, Employees, Products, and Sales. In addition, the database contains the results from an imaginary marketing survey in the Subjects and Scores table.

Many different physical implementations of this logical schema were implemented during the development of the book. For instance, the Sales table was subjected to a variety of partitioning schemes, the Customer table was represented in one example as an Index-Organized table, while Subject and Score data were represented in a variety of ways including nested tables, Varrays, and object tables.

Many of the examples contained in this book are accompanied with a graphical illustration of the performance gains that can be achieved by various optimizations. These performance measurements were collected on a range of computer hardware, ranging from a high-end UNIX host to a Pentium laptop. Performance measurements are shown in either elapsed times or logical database I/Os ("block reads"), whichever was most appropriate to the optimization being performed.

About Quest Software

Since writing the first edition of this book, I have had the pleasure to work with Quest Software in the development of software that assists in the tuning of SQL and Oracle databases. In particular, the SQLab Xpert SQL tuning tool implements many of the tuning principles outlined in this book. I have also been heavily involved in the development of Quest's Spotlight on Oracle product, which implements the database tuning philosophies outlined in the final chapters of this book. Because I'm so familiar with Quest products and believe that they make substantial contributions to SQL tuning, I often refer to them in the text of the book. However, be warned that I'm a Quest employee and hardly unbiased when it comes to Quest products.

Read More Show Less

Table of Contents

Preface
1 Introduction to SQL Tuning 1
2 Review of SQL 17
3 SQL Processing 35
4 Principles of Indexing and Clustering 65
5 Tracing Oracle SQL 83
6 Tuning Table Access 111
7 Optimizing Joins and Subqueries 157
8 Optimizing Sorting and Grouping 197
9 Optimizing Data Manipulation Statements 217
10 Using and Tuning PL/SQL 233
11 Miscellaneous Topics 259
12 Parallel SQL 287
13 SQL Tuning Case Studies 313
14 Data Model and Application Design 335
15 Building a High-Performance Oracle Database Server 361
16 Tuning the Database Server 393
App. A: Reference 431
App. B: Glossary 441
App. C Configuring Development Tools 447
App. D Oracle Server Configuration 459
App. E Differences Between Oracle Versions 471
App. F Bibliography and Resources 475
App. G: Contents of the CD-ROM 481
Index 487
Read More Show Less

Preface

Introduction

This book is about tuning Oracle databases and applications with an emphasis on the tuning of SQL statements. Tuning SQL is not the only way to tune an application: The design of an application will often dictate its performance limits, and tuning the physical layout of an Oracle database can be critical to reaching those limits. However, tuning SQL is usually the most cost-effective way of improving the performance of an existing application, while other measures—such as changing database parameters or altering disk layouts—will usually be ineffective unless the application's SQL is properly tuned.

It is common for the performance of an Oracle application to appear to be acceptable during development only to degrade abruptly when the application encounters production data volumes or transaction rates. While this may result from a number of causes, inefficient SQL that 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 nonprocedural nature tends to obscure performance-related issues. As a result, its 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 monitor carefully and tune SQL performance, and the tools and techniques needed to tune SQL are not sufficiently well known.

Another factor that has increased the significance of well-tuned SQL is the emergence of data warehouses or On-Line Analytical Processing (OLAP) systems. These databases are often extremely large and are subject to a greatdeal of ad hoc query activity. If the SQL that 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 underperform, 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 input/output (I/O), disks and so on. At the end of the process, you can (if you are lucky) expect a 10 to 20 % improvement in performance.

During these tuning exercises it is usually that apparent the SQL contained within the application 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 there is a dilemma: 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 improve substantially 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 application design and server tuning. By following the guidelines in this book, SQL programmers should be able to write SQL that 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 that performs to its peak potential.

The need for this book

With the Oracle server documentation set consisting of more than a dozen manuals—including a tuning guide—and a number 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 that is aimed not at the administrators of the Oracle databases, but at those writing the access routines (that is, 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. Unfortunately, most tuning texts spend most of their time focusing on database and I/O subsystem tuning.

Who should use this book

This is not a book for Oracle DBAs, although DBAs should find many things of interest here. Rather, this is a book for anyone who needs to write SQL that has a performance requirement.

People who need to write high-performance SQL are as follows:

  • Developers of Oracle-based applications. These developers will typically need to embed SQL statements within the code of the development tool (such as C++, Java, or Visual Basic). Alternately, the SQL may be contained within stored procedures that 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 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

Few people read a book of this type from beginning to end. Depending on your background, you may wish to skip sections that review database theory and jump right into the details of SQL tuning.

However, apart from the "Review of SQL" and the "Beyond SQL Tuning" sections, most readers should attempt to read or at least review the majority of this book.

The book has the following major sections:

Part I: Introduction to SQL Tuning

This section contains a review of the importance of SQL tuning, an overview of the tuning process and a review of SQL. The chapters in Part I are as follows:

  • Chapter 1: Introduction to SQL Tuning
  • Chapter 2: SQL Tuning Quick Start
  • Chapter 3: Review of SQL
Part II: SQL Tuning theory

Chapters in Part II introduce a number of important topics, such as the role of the query optimizers, SQL parsing, basic data retrieval strategies, and tools for explaining and tuning SQL execution. Although Part II is heavy on theory, its difficult to tune SQL successfully without at least a broad understanding of these topics. All readers are therefore encouraged to read this section.

The chapters in Part II are as follows:

  • Chapter 4: SQL Processing Internals
  • Chapter 5: The Optimizer
  • Chapter 6: Indexing and Clustering
  • Chapter 7: Tracing and Explaining SQL
Part III: SQL Tuning in Practice

Chapters in Part III contain tuning guidelines for specific SQL statement types and circumstances. While it will be useful to read Part III from start to finish, it may also be used as a reference. You may wish to consult the relevant portions of this section as appropriate tuning requirements arise. Chapters in Part III are as follows:

  • Chapter 8: Tuning Table Access
  • Chapter 9: Tuning Joins and Subqueries
  • Chapter 10: Sorts, Aggregates, and SET Operations
  • Chapter 11: Parallel SQL
  • Chapter 12: Optimizing DML
  • Chapter 13: VLDB and Warehousing
  • Chapter 14: Using and Tuning PL/SQL
  • Chapter 15: Using and Tuning Oracle Java
  • Chapter 16: Oracle Object Types
  • Chapter 17: Miscellaneous Topics
Part IV: Beyond SQL Tuning

At the beginning of the application life cycle, effective database and application design can define the constraints that will ultimately determine the limits on your SQL's performance. For a well-designed application with tuned SQL, the configuration of your database—disk layouts, SGA configura performance. Chapters in Part IV discuss these "beyond SQL" issues:

  • Chapter 18: Application Design Issues
  • Chapter 19: Oracle Server Design
  • Chapter 20: Oracle Server Tuning
Appendices

The appendices contain details of configuring client programs and the Oracle server for specific circumstances, a reference guide, and a guide to further reading and other resources.

The Sample Database

Whenever possible, any SQL tuning principle in this book will be illustrated with an example SQL statement. Usually, these SQL statements will be based on the sample database shown in Figure P-1. This database is not intended to illustrate good or bad data modeling principles but to be a basis for illustrating a wide range of SQL statements. You can find an export of one of the variations of this database at the book's website.

The sample database implements a simple and familiar business schema containing Customers, Employees, Products, and Sales. In addition, the database contains the results from an imaginary marketing survey in the Subjects and Scores table.

Many different physical implementations of this logical schema were implemented during the development of the book. For instance, the Sales table was subjected to a variety of partitioning schemes, the Customer table was represented in one example as an Index-Organized table, while Subject and Score data were represented in a variety of ways including nested tables, Varrays, and object tables.

Many of the examples contained in this book are accompanied with a graphical illustration of the performance gains that can be achieved by various optimizations. These performance measurements were c on a range of computer hardware, ranging from a high-end UNIX host to a Pentium laptop. Performance measurements are shown in either elapsed times or logical database I/Os ("block reads"), whichever was most appropriate to the optimization being performed.

About Quest Software

Since writing the first edition of this book, I have had the pleasure to work with Quest Software in the development of software that assists in the tuning of SQL and Oracle databases. In particular, the SQLab Xpert SQL tuning tool implements many of the tuning principles outlined in this book. I have also been heavily involved in the development of Quest's Spotlight on Oracle product, which implements the database tuning philosophies outlined in the final chapters of this book. Because I'm so familiar with Quest products and believe that they make substantial contributions to SQL tuning, I often refer to them in the text of the book. However, be warned that I'm a Quest employee and hardly unbiased when it comes to Quest products.

Read More Show Less

Introduction

Introduction

This book is about tuning Oracle databases and applications with an emphasis on the tuning of SQL statements. Tuning SQL is not the only way to tune an application: The design of an application will often dictate its performance limits, and tuning the physical layout of an Oracle database can be critical to reaching those limits. However, tuning SQL is usually the most cost-effective way of improving the performance of an existing application, while other measures—such as changing database parameters or altering disk layouts—will usually be ineffective unless the application's SQL is properly tuned.

It is common for the performance of an Oracle application to appear to be acceptable during development only to degrade abruptly when the application encounters production data volumes or transaction rates. While this may result from a number of causes, inefficient SQL that 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 nonprocedural nature tends to obscure performance-related issues. As a result, its 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 monitor carefully and tune SQL performance, and the tools and techniques needed to tune SQL are not sufficiently well known.

Another factor that has increased the significance of well-tuned SQL is the emergence of data warehouses or On-Line Analytical Processing (OLAP) systems. These databases are often extremely large andare subject to a great deal of ad hoc query activity. If the SQL that 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 underperform, 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 input/output (I/O), disks and so on. At the end of the process, you can (if you are lucky) expect a 10 to 20 % improvement in performance.

During these tuning exercises it is usually that apparent the SQL contained within the application 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 there is a dilemma: 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 improve substantially 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 application design and server tuning. By following the guidelines in this book, SQL programmers should be able to write SQL that 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 that performs to its peak potential.

The need for this book

With the Oracle server documentation set consisting of more than a dozen manuals—including a tuning guide—and a number 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 that is aimed not at the administrators of the Oracle databases, but at those writing the access routines (that is, 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. Unfortunately, most tuning texts spend most of their time focusing on database and I/O subsystem tuning.

Who should use this book

This is not a book for Oracle DBAs, although DBAs should find many things of interest here. Rather, this is a book for anyone who needs to write SQL that has a performance requirement.

People who need to write high-performance SQL are as follows:

  • Developers of Oracle-based applications. These developers will typically need to embed SQL statements within the code of the development tool (such as C++, Java, or Visual Basic). Alternately, the SQL may be contained within stored procedures that 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 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

Few people read a book of this type from beginning to end. Depending on your background, you may wish to skip sections that review database theory and jump right into the details of SQL tuning.

However, apart from the "Review of SQL" and the "Beyond SQL Tuning" sections, most readers should attempt to read or at least review the majority of this book.

The book has the following major sections:

Part I: Introduction to SQL Tuning

This section contains a review of the importance of SQL tuning, an overview of the tuning process and a review of SQL. The chapters in Part I are as follows:

  • Chapter 1: Introduction to SQL Tuning
  • Chapter 2: SQL Tuning Quick Start
  • Chapter 3: Review of SQL

Part II: SQL Tuning theory

Chapters in Part II important topics, such as the role of the query optimizers, indexing and hashing concepts, SQL parsing, basic data retrieval strategies, and tools for explaining and tuning SQL execution. Although Part II is heavy on theory, its difficult to tune SQL successfully without at least a broad understanding of these topics. All readers are therefore encouraged to read this section.

The chapters in Part II are as follows:

  • Chapter 4: SQL Processing Internals
  • Chapter 5: The Optimizer
  • Chapter 6: Indexing and Clustering
  • Chapter 7: Tracing and Explaining SQL

Part III: SQL Tuning in Practice

Chapters in Part III contain tuning guidelines for specific SQL statement types and circumstances. While it will be useful to read Part III from start to finish, it may also be used as a reference. You may wish to consult the relevant portions of this section as appropriate tuning requirements arise. Chapters in Part III are as follows:

  • Chapter 8: Tuning Table Access
  • Chapter 9: Tuning Joins and Subqueries
  • Chapter 10: Sorts, Aggregates, and SET Operations
  • Chapter 11: Parallel SQL
  • Chapter 12: Optimizing DML
  • Chapter 13: VLDB and Warehousing
  • Chapter 14: Using and Tuning PL/SQL
  • Chapter 15: Using and Tuning Oracle Java
  • Chapter 16: Oracle Object Types
  • Chapter 17: Miscellaneous Topics

Part IV: Beyond SQL Tuning

At the beginning of the application life cycle, effective database and application design can define the constraints that will ultimately de performance. For a well-designed application with tuned SQL, the configuration of your database—disk layouts, SGA configuration, etc.—may be the key to getting further gains in performance. Chapters in Part IV discuss these "beyond SQL" issues:

  • Chapter 18: Application Design Issues
  • Chapter 19: Oracle Server Design
  • Chapter 20: Oracle Server Tuning

Appendices

The appendices contain details of configuring client programs and the Oracle server for specific circumstances, a reference guide, and a guide to further reading and other resources.

The Sample Database

Whenever possible, any SQL tuning principle in this book will be illustrated with an example SQL statement. Usually, these SQL statements will be based on the sample database shown in Figure P-1. This database is not intended to illustrate good or bad data modeling principles but to be a basis for illustrating a wide range of SQL statements. You can find an export of one of the variations of this database at the book's website.

The sample database implements a simple and familiar business schema containing Customers, Employees, Products, and Sales. In addition, the database contains the results from an imaginary marketing survey in the Subjects and Scores table.

Many different physical implementations of this logical schema were implemented during the development of the book. For instance, the Sales table was subjected to a variety of partitioning schemes, the Customer table was represented in one example as an Index-Organized table, while Subject and Score data were represented in a variety of ways including nested tables, Varrays, an tables.

Many of the examples contained in this book are accompanied with a graphical illustration of the performance gains that can be achieved by various optimizations. These performance measurements were collected on a range of computer hardware, ranging from a high-end UNIX host to a Pentium laptop. Performance measurements are shown in either elapsed times or logical database I/Os ("block reads"), whichever was most appropriate to the optimization being performed.

About Quest Software

Since writing the first edition of this book, I have had the pleasure to work with Quest Software in the development of software that assists in the tuning of SQL and Oracle databases. In particular, the SQLab Xpert SQL tuning tool implements many of the tuning principles outlined in this book. I have also been heavily involved in the development of Quest's Spotlight on Oracle product, which implements the database tuning philosophies outlined in the final chapters of this book. Because I'm so familiar with Quest products and believe that they make substantial contributions to SQL tuning, I often refer to them in the text of the book. However, be warned that I'm a Quest employee and hardly unbiased when it comes to Quest products.

Read More Show Less

Customer Reviews

Average Rating 4
( 1 )
Rating Distribution

5 Star

(0)

4 Star

(1)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 Customer Reviews
  • Anonymous

    Posted November 6, 2000

    Good book, Bad Index

    This is one of the best books I have read for Oracle performance tuning. As an MCSD, Oracle performance tuning is often quite a bit more difficult when compared to SQL Server for me. This book does a great job of outlining real-world tips and techniques for optimizing your queries for Oracle. On the downside, the entire index is only 3 pages long and quite incomplete. Despite the sparse index, the book is organized very well and the information is easy to find by browsing the chapter headings.

    Was this review helpful? Yes  No   Report this review
Sort by: Showing 1 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)