Oracle SQL Tuning with Oracle SQLTXPLAIN

Oracle SQL Tuning with Oracle SQLTXPLAIN

by Stelios Charalambides
Released March 2013
Publisher(s): Apress
ISBN: 9781430248095

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

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
      2. SQLTXTRSBY
      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
      3. TRCASPLIT
      4. TRCANLZR
      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
    25. APPENDIX A: Installing SQLTXPLAIN
      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 (11.2.0.1)
      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

    Product information

    • Title: Oracle SQL Tuning with Oracle SQLTXPLAIN
    • Author(s): Stelios Charalambides
    • Release date: March 2013
    • Publisher(s): Apress
    • ISBN: 9781430248095