Résumé
In this book, Mark Gurry shares his in-depth knowledge of Oracle's SQL statement optimizers. Mark's knowledge is the result of many hard-fought tuning battles during his many years of providing Oracle tuning services to clients. Mark provides insights into the workings of the rule-based optimizer that go well beyond what the rules tell you. Mark also provides solutions to many common problems that occur with both the rule-based and cost-based optimizers.
In addition to the specific problem/solution scenarios for the optimizers, Mark provides a number of handy SQL tuning tips. He discusses the various optimizer hints, telling you when they can be used to good effect. Finally, Mark discusses the use of the DBMS_STATS package to manage database statistics, and the use of outlines to specify execution plans for SQL statements in third-party applications that you can't otherwise modify.
Contents
- Introduction
- The SQL Optimizers
- Understanding the Rule-Based Optimizer
- Understanding the Cost-Based Optimizer
- Some Common Optimizer Misconceptions
- Which Optimizer to Use?
- Rule-Based Optimizer Problems and Solutions
- Problem 1: Incorrect Driving Table
- Problem 2: Incorrect Index
- Problem 3: Incorrect Driving Index
- Problem 4: Using the ORDER BY Index and notthe WHERE Index
- Cost-Based Optimizer Problems and Solutions
- Problem 1: The Skewness Problem
- Problem 2: Analyzing with Wrong Data
- Problem 3: Mixing the Optimizers in Joins
- Problem 4: Choosing an Inferior Index
- Problem 5: Joining Too Many Tables
- Problem 6: Incorrect INIT.ORA Parameter Settings
- Problems Common to Rule and Cost with Solutions
- Problem 1: Statement Not Written for Indexes
- Problem 2: Indexes Are Missing or Inappropriate
- Problem 3: Use of Single-Column Index Merge
- Problem 4: Misuse of Nested Loop, Sort Merge,or Hash Join
- Problem 5: Misuse of IN, EXISTS, NOT IN, NOT EXISTS,or Table Joins
- Problem 6: Unnecessary Sorts
- Problem 7: Too Many Indexes on a Table
- Problem 8: Use of OR Instead of UNION
- Problem 9: Tables and Indexes with Many Deletes
- Other Problems: Heavy Usage of Views
- Other Problems: Joining Too Many Tables
- Handy SQL Tuning Tips
- Identify Bad SQL
- Identify Long-Running SQL Statements
- Use DECODE for IF/ELSE Selection
- Encourage Bind Variables
- Using SQL Hints
- When Are Hints Ignored?
- Using Hints in Views
- Available Hints
- Using DBMS_STATS to Manage Statistics
- Using DBMS_STATS to Analyze Faster
- Copying Statistics Using DBMS_STATS
- Manipulating Statistics Using DBMS_STATS
- Reverting to Previous Statistics
- Using Outlines for Consistent Execution Plans
- Recording Outlines
- Enabling Outlines
- Managing Outlines
Caractéristiques techniques
| PAPIER | |
| Éditeur(s) | O'Reilly |
| Auteur(s) | Mark Gurry |
| Parution | 11/03/2002 |
| Nb. de pages | 102 |
| Format | 11 x 17,5 |
| Couverture | Broché |
| Poids | 109g |
| Intérieur | Noir et Blanc |
| EAN13 | 9780596002688 |
| ISBN13 | 978-0-596-00268-8 |
Avantages Eyrolles.com
Consultez aussi
- Les meilleures ventes en Graphisme & Photo
- Les meilleures ventes en Informatique
- Les meilleures ventes en Construction
- Les meilleures ventes en Entreprise & Droit
- Les meilleures ventes en Sciences
- Les meilleures ventes en Littérature
- Les meilleures ventes en Arts & Loisirs
- Les meilleures ventes en Vie pratique
- Les meilleures ventes en Voyage et Tourisme
- Les meilleures ventes en BD et Jeunesse
