Oracle SQL High-Performance Tuning - Softcover

Harrison, Guy

Softcover

ISBN 10: 0136142311 ISBN 13: 9780136142317

Publisher: Prentice Hall Ptr, 1997

Synopsis

A guide to using Oracle's parallel query facility for large volume queries focusing on SQL and PL/SQL. Harrison, an Oracle developer and consultant, overviews SQL processing and introduces guidelines to improve applications, including execution plans, work with joins, nulls and ranges search, index building, hierarchical queries, table scans, GROUP BY, updates, and distributed SQL. The accompanying CD-ROM features a set of tools for detecting and correcting SQL bottlenecks. Annotation c. by Book News, Inc., Portland, Or.

About this title

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

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.

Edition details

• Publisher: Prentice Hall Ptr
• Publication date: 1997
• ISBN 10: 0136142311
• ISBN 13: 9780136142317
• Binding: Paperback
• Language: English
• Number of pages: 491