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

Product Details

• Amazon Sales Rank: #414134 in Books
• Published on: 2001-07-27
• Original language: English
• Binding: Paperback
• 656 pages

From the Back Cover

Improve Database Performance by Tuning SQL Statements

Tune all types of SQL statements--from a simple SELECT statement to a complex non-correlated subquery--using tips and techniques from SQL expert Don Burleson. Officially authorized by Oracle Corporation, this in-depth resource explains how to take any SQL statement, view the internal execution plan, and change the execution plan 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.

• Understand how SQL tuning fits into the overall Oracle tuning model
• Improve Oracle SQL functionality using extensions such as in-line views and BIFs
• Identify and report on SQL in the library cache
• Tune SQL table access, full-table scans, and parallel query
• Run TKPROF to get a SQL trace report
• Change the execution plans for Oracle SQL statements using Oracle hints
• Use the Oracle8i optimizer plan stability, cost-based optimizer, and rule-based optimizer
• Tune SQL DML statements, SQL subqueries, and data warehouse SQL
• Tune SQL with temporary tables and indexes
• Diagnose and optimize system performance using STATSPACK

About the Author
Don Burleson (Kittrell, NC) has 20 years of experience as a database administrator and has written 9 books on database management including Oracle SAP Administration (O’Reilly), High-Performance Oracle8 Tuning, High-Performance Oracle Data Warehousing, High-Performance Oracle Database Applications (Coriolis) and Oracle8 Tuning, German Edition (Sybex). Don has taught more than 100 University courses and regularly conducts in-house Oracle training.

Excerpt
CHAPTER
13 Tuning with Optimizer Plan Stability

This chapter discusses the use of optimizer plan stability in Oracle8i and shows how you can improve the run-time performance of SQL statements and also provide a easy method to permanently change the execution plans for SQL statements. This chapter will cover the following topics:
n Introduction to stored outlines
n Preparing Oracle for stored outlines
n How to create and modify a stored outline
n Managing a stored outline

Introduction to Stored Outlines
The optimizer plan stability feature of Oracle8 i has been a long time coming. The
earlier databases such as DB2 and IDMS have had the ability to store execution
plans since the 1980s, and the concept of stored SQL outlines has had widespread acceptance in the non-Oracle world for decades. The argument behind optimizer plan stability is that there exists only one optimal execution plan for any SQL statement, and once located, the execution plan should never change, even when the CBO statistics or initialization parameters are changed. Of course, this philosophy is contrary to the basic tenet of Oracle cost-based optimization, which expects SQL statements to change execution plans
when the characteristics of the table and index statistics change, or when a
change is made to an important initialization parameter such as sort_area_size
or db_file_multiblock_read_count.

Regardless of philosophy, creating a stable execution plan for Oracle SQL has
two major benefits:
n Change execution plan without touching SQL source code Many
databases have SQL that is dynamically generated (e.g., SAP) or SQL that
resides in unreachable PC libraries. For these types of applications, stored
outlines allow you to change the execution plan for the SQL without the
need to change the SQL source code.
n Permanent SQL tuning changes Once tuned, optimizer plan stability
allows for SQL statements to always have the same execution plan. There
will be no surprises when a change is made to an important initialization
parameter such as sort_area_sizeor when the CBO statistics change.

Stored outlines are great for tuning SQL in database application suites where the SQL source is not available or cannot be changed. For example, SAP
and PeopleSoft applications products can now have SQL tuning without touching the source code. Now that we see the benefits of using stored outlines, let’s take a look at how optimizer plan stability works. When a SQL statement enters Oracle8 i, the database will perform the following actions (Figure 13-1).
1. Check shared pool Hash the SQL statement and see if an identical
statement is ready to go in the shared pool. If it is found, reexecute the SQL
statement from the shared pool.
2. Check stored outlines If the SQL is not found in the shared pool, check
for a stored outline in DBA_OUTLINES view in the OUTLINE tablespace. If
a stored outline is found, load it into the shared pool and begin execution.
3. Start from scratch If nothing for the SQL statement is found in the shared
pool or stored outlines, parse the SQL, develop an execution plan, and
begin execution.
FIGURE 13-1. Executing a new SQL statement Optimizer plan stability can also aid us in our SQL tuning effort. Oracle provides a package called outln_pkgthat allows you to manage your stored outlines and Oracle also provides an option for the ALTER SYSTEM and ALTER SESSION commands for the automatic collections of stored outlines for all of your SQL statements. However, it is not a good idea to turn-on the option to automatically store outlines unless you are prepared to examine each stored outline and ensure that the best execution plan is being used. If you have already tuned your SQL, you can alter system set_stored_outlines=true and Oracle will define plans for all issued SQL statements in all sessions at the time they are executed; these stored plans will be reused until altered or dropped. If you have not tuned your SQL, you can create individual stored outlines for your session by entering the alter session set_stored_outlines=truecommand.

Just as the shared pool will not reuse a SQL statement unless it is identical to a
previous statement, the effective use of stored outlines depends on the statement being reissued in an identical fashion each time it is used. If even one space is out of place, the stored outline is not reused. Whenever possible, all SQL should be stored in PL/SQL procedures, functions, or packages, and bind variables should always be used to ensure that the SQL is reusable. This allows reuse of the stored image of the SQL as well as reuse of your stored outlines.

Customer Reviews

An excellent & clear book about the sql internals
It is an excellent book for those interested in the internals of sql. The knowledge of the author about the matter is excellent, and his style to write, clear, simple and funny.

Good book for tuning complex SQL
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
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.