Oracle SQL Tuning

by Gurry, Mark

• ISBN13: 9780596002688
• ISBN10: 0596002688
• Format: Paperback
• Copyright: 2001-11-01
• Publisher: Oreilly & Associates Inc

Summary

Mark Gurry's in-depth knowledge of Oracle's SQL statement optimizers yields insights into the workings of the rule-based optimizer that take readers well beyond what the rules tell them. This little book is a rich source of handy SQL tuning tips and solutions to many common problems using both rule- based and cost-based optimizers. Oracle SQL Tuning Pocket Reference covers how to use the various optimizer hints to good effect; managing database statistics with the DBMS_STATS package; and using outlines to specify execution plans for SQL statements in third-party applications. Replete with real- world examples drawn from over a decade of tuning work for some of Australia's largest corporations

Author Biography

As a database administrator on a financial IMS project about eight years ago, Mark Gurry was asked to investigate his company's database direction for the next five years. The number of users was up to 950, the cost of maintaining the mainframes was huge, and he'd heard about relational databases and downsizing. After much investigation, he chose Oracle, and has stuck with the system ever since. He has worked as Manager of Computing and Network Services, senior database administrator, senior Oracle technical support, and other jobs. He now has a small consulting company called New Age Consultants. Mark has worked for many large organizations and is currently working for Telecom Australia, the largest computer site in Australia and one of the largest in the world. He has also spoken on tuning at Oracle user group meetings and has given internal tuning courses at several of his larger client sites. He has been a senior team member on award-winning systems that have been developed using Oracle.

Table of Contents

Introduction	p. 1
The SQL Optimizers	p. 4
Understanding the Rule-Based Optimizer	p. 5
Understanding the Cost-Based Optimizer	p. 11
Some Common Optimizer Misconceptions	p. 19
Which Optimizer to Use?	p. 19
Rule-Based Optimizer Problems and Solutions	p. 20
Incorrect Driving Table	p. 21
Incorrect Index	p. 22
Incorrect Driving Index	p. 23
Using the Order By Index and not the Where Index	p. 25
Cost-Based Optimizer Problems and Solutions	p. 26
The Skewness Problem	p. 26
Analyzing with Wrong Data	p. 29
Mixing the Optimizers in Joins	p. 31
Choosing an Inferior Index	p. 33
Joining Too Many Tables	p. 36
Incorrect Init. Ora Parameter Settings	p. 38
Problems Common to Rule and Cost with Solutions	p. 43
Statement Not Written for Indexes	p. 43
Indexes Are Missing or Inappropriate	p. 47
Use of Single-Column Index Merge	p. 50
Misuse of Nested Loop, Sort Merge, or Hash Join	p. 51
Misuse of in, Exists, Not in, Not Exists, or Table Joins	p. 54
Unnecessary Sorts	p. 59
Too Many Indexes on a Table	p. 61
Use of Or Instead of Union	p. 63
Tables and Indexes with Many Deletes	p. 64
Other Problems: Heavy Usage of Views	p. 66
Other Problems: Joining Too Many Tables	p. 67
Handy SQL Tuning Tips	p. 67
Identify Bad SQL	p. 67
Identify Long-Running SQL Statements	p. 68
Use Decode for If/Else Selection	p. 69
Encourage Bind Variables	p. 70
Using SQL Hints	p. 72
When Are Hints Ignored?	p. 73
Using Hints in Views	p. 74
Available Hints	p. 74
Using DBMS_STATS to Manage Statistics	p. 94
Using DBMS_STATS to Analyze Faster	p. 94
Copying Statistics Using DBMS_STATS	p. 95
Manipulating Statistics Using DBMS_STATS	p. 96
Reverting to Previous Statistics	p. 97
Using Outlines for Consistent Execution Plans	p. 98
Recording Outlines	p. 98
Enabling Outlines	p. 100
Managing Outlines	p. 101
Table of Contents provided by Syndetics. All Rights Reserved.