O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Oracle SQL Tuning with Oracle SQLTXPLAIN

Book Description

Oracle SQL Tuning with SQLTXPLAIN is a practical guide to SQL tuning the way Oracle's own experts do it, using a freely downloadable tool called SQLTXPLAIN. Using this simple tool you'll learn how to tune even the most complex SQL, and you'll learn to do it quickly, without the huge learning curve usually associated with tuning as a whole.

Firmly based in real world problems, this book helps you reclaim system resources and avoid the most common bottleneck in overall performance, badly tuned SQL. You'll learn how the optimizer works, how to take advantage of its latest features, and when it's better to turn them off.

  • Quickly tune any SQL statement no matter how complex.

  • Build and tune test cases without affecting production.

  • Use the latest tuning features with confidence.

What you'll learn

  • How and why complex SQL goes wrong

  • When statistics are crucial and when they are not

  • How and when the tuning facilities of the database can help or hinder

  • When to tune and when not to tune

  • Which steps in an execution plan are crucial to performance

  • How the optimizer works out the execution plan

Who this book is for

Oracle SQL Tuning with SQLTXPLAIN is ideal for everyone who deals with SQL and SQL tuning. Both developers and DBA's will benefit from learning how to use the SQLTXPLAIN tool along with the problem solving approach described in this book.

Table of Contents

  1. Title Page
  2. Dedication
  3. Contents at a Glance
  4. Contents
  5. About the Author
  6. About the Technical Reviewer
  7. Acknowledgments
  8. Foreword
  9. Introduction
  10. CHAPTER 1: Introduction to SQLTXPLAIN
    1. What Is SQLT?
    2. Getting Started with SQLT
    3. Your First SQLT Report
    4. Join Methods
    5. Summary
  11. CHAPTER 2: The Cost-Based Optimizer Environment
    1. System Statistics
    2. Cost-Based Optimizer Parameters
    3. Siebel Environment Considerations
    4. Hints
    5. History of Changes
    6. Column Statistics.
    7. Out-of-Range Values
    8. Over Estimates and Under Estimates.
    9. The Case of the Mysterious Change
    10. Summary
  12. CHAPTER 3: How Object Statistics Can Make Your Execution Plan Wrong
    1. What Are Statistics?
    2. Object Statistics
    3. Partitions
    4. Stale Statistics
    5. Sampling Size
    6. How to Gather Statistics
    7. Saving and Restoring and Locking Statistics
    8. The Case of the Midnight Truncate
    9. Summary
  13. CHAPTER 4: How Skewness Can Make Your Execution Times Variable
    1. Skewness
    2. Histograms
    3. Bind Variables
    4. The Case of the Variable Execution Time
    5. Summary
  14. CHAPTER 5: Troubleshooting Query Transformations
    1. What Are Query Transformations?
    2. The 10053 Trace File
    3. What Is a Query Transformation?
    4. Why Would We Want to Disable Query Transformations?
    5. Optimizer Parameters
    6. Optimizer Hints
    7. Cost Calculations
    8. Summary
  15. CHAPTER 6: Forcing Execution Plans Through Profiles
    1. What is an SQL Profile?
    2. Where Does SQLT Get Its SQL Profile?
    3. What Can You Do with a SQL Profile?
    4. How Do You Confirm You Are Using an SQL Profile?
    5. How Do You Transfer an SQL Profile from One Database to Another?
    6. Summary
  16. CHAPTER 7: Adaptive Cursor Sharing
    1. Bind Variables and Why We Need Them
    2. The CURSOR_SHARING Parameter
    3. Bind Peeking
    4. Bind Sensitive and Bind Aware Cursors
    5. Does ACS Go Wrong?
    6. Summary
  17. CHAPTER 8: Dynamic Sampling and Cardinality Feedback
    1. Dynamic Sampling?
    2. Cardinality Feedback
    3. The Case of the Identical Twins
    4. Summary
  18. CHAPTER 9: Using SQLTXPLAIN with Data Guard Physical Standby Databases
    1. Data Guard Physical Standby Database
    3. The roxtract Tool
    4. Summary
  19. CHAPTER 10: Comparing Execution Plans
    1. How Do You Use SQLTCOMPARE?
    2. A Practical Example
    3. Summary
  20. CHAPTER 11: Building Good Test Cases
    1. What Can You Do with Test Cases?
    2. Building a Test Case
    3. Exploring the Execution Plan
    4. Other Test Case Utilities
    5. Summary
  21. CHAPTER 12: Using XPLORE to Investigate Unexpected Plan Changes
    1. When Should You Use XPLORE?
    2. How Does XPLORE Work?
    3. An Example XPLORE Session
    4. Summary
  22. CHAPTER 13: Trace Files, TRCANLZR and Modifying SQLT behavior
    1. 10046 Trace
    2. TKPROF
    5. TRCAXTR
    6. Modifying SQLT Behavior
    7. Summary
  23. CHAPTER 14: Running a Health Check
    1. What Is SQL Health Check?
    2. The sqlhc.sql Script
    3. The sqldx.sql Script
    4. The sqlhcxec.sql Script
    5. Summary
  24. CHAPTER 15: The Final Word
    1. Tuning Methodology
    2. Why SQLT Is, Hands Down, the Best Tuning Utility
    3. A Word About Platforms
    4. Other Resources
    5. Summary
    1. A Standard SQLT Installation
    2. How to Change the Licensing Level after a SQLT Installation
    3. A Remote SQLT Installation
    4. Other Ways to Install SQLT
    5. How to Remove SQLT
  26. APPENDIX B: The CBO Parameters (
    1. General Approach to Dealing with Hidden Parameters
    2. More Detailed Descriptions of Some Hidden Parameters
    3. Full List of Parameters
  27. APPENDIX C: Tool Configuration Parameters
  28. Index