Expert Oracle SQL

Expert Oracle SQL

Released July 2014
Publisher(s): Apress
ISBN: 9781430259770

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

Expert Oracle SQL: Optimization, Deployment, and Statistics is about optimizing individual SQL statements, especially on production database systems. This Oracle-specific book begins by assuming you have already identified a particular SQL statement and are considering taking steps to improve its performance. The book describes a systematic process by which to diagnose a problem statement, identify a fix, and to implement that fix safely in a production system. You'll learn not only to improve performance when it is too slow, but also to stabilize performance when it is too variable. You'll learn about system statistics and how the Cost-Based Optimizer uses them to determine a suitable execution plan for a given statement. That knowledge provides the foundation from which to identify the root cause, and to stabilize and improve performance.

Next after identifying a problem and the underlying root cause is to put in place a solution. Expert Oracle SQL: Optimization, Deployment, and Statistics explains how to apply various remedies such as changing the SQL statement, adding hints, changing the physical design, and more, and how they can be brought to bear on fixing a problem once and for all.

Rolling a change out smoothly is an important topic when dealing with tuning and optimization. Expert Oracle SQL: Optimization, Deployment, and Statistics does not let you down in this critical area. The book contains two chapters of good information from an experienced professional on how to safely deploy changes into production so that your systems remaining running and available throughout the deployment process.

  • Describes a systematic approach to tuning Oracle SQL
  • Explains how things are supposed to work, what can go wrong, and how to fix it
  • Takes you through the steps needed to stabilize performance in your production systems

What you'll learn

  • Determine the root cause of poor performance in a SQL statement
  • Decipher each line of an execution plan and how it relates to its source SQL statement
  • Know the various options and transformations available for the Cost-Based Optimizer
  • Interpret the diagnostic data from Active Session History, SQL Performance Monitor, and from other sources
  • Apply optimizer hints, SQL profiles, and SQL baselines when they are best used, and avoid them otherwise
  • Manage object statistics used by the optimizer to ensure efficient execution plans

Table of contents

  1. Cover
  2. Title
  3. Copyright
  4. Dedication
  5. Contents at a Glance
  6. About IOUG Press
  7. Contents
  8. About the Author
  9. About the Technical Reviewers
  10. Acknowledgments
  11. Foreword
  12. Introduction
  13. Part 1: Basic Concepts
    1. Chapter 1: SQL Features
      1. SQL and Declarative Programming Languages
      2. Statements and SQL_IDs
      3. Cross-Referencing Statement and SQL_ID
      4. Array Interface
      5. Subquery Factoring
        1. The Concept of Subquery Factoring
      6. Joins
        1. Inner Joins and Traditional Join Syntax
        2. Outer Joins and ANSI Join Syntax
      7. Summary
    2. Chapter 2: The Cost-Based Optimizer
      1. The Optimal Execution Plan
      2. The Definition of Cost
      3. The CBO’s Cost-Estimating Algorithm
        1. Calculating Cost
        2. The Quality of the CBO’s Plan Selection
      4. The Optimization Process
        1. Parallelism
        2. Query Transformation
        3. Final State Query Optimization
      5. Summary
    3. Chapter 3: Basic Execution Plan Concepts
      1. Displaying Execution Plans
        1. Displaying the Results of EXPLAIN PLAN
        2. EXPLAIN PLAN May Be Misleading
        3. Displaying Output from the Cursor Cache
        4. Displaying Execution Plans from the AWR
      2. Understanding Operations
        1. What an Operation Does
        2. How Operations Interact
        3. How Long Do Operations Take?
      3. Summary
    4. Chapter 4: The Runtime Engine
      1. Collecting Operation Level Runtime Data
        1. The GATHER_PLAN_STATISTICS Hint
        2. Setting STATISTICS_LEVEL=ALL
        3. Enabling SQL Tracing
      2. Displaying Operational Level Data
        1. Displaying Runtime Engine Statistics with DBMS_XPLAN.DISPLAY_CURSOR
        2. Displaying Runtime Engine Statistics with V$SQL_PLAN_STATISTICS_ALL
      3. Displaying Session Level Statistics with Snapper
      4. The SQL Performance Monitor
      5. Workareas
        1. Operations Needing a Workarea
        2. Allocating Memory to a Workarea
      6. Optimal, One-Pass, and Multipass Operations
      7. Shortcuts
        1. Scalar Subquery Caching
        2. Join Shortcuts
        3. Result and OCI Caches
        4. Function Result Cache
      8. Summary
    5. Chapter 5: Introduction to Tuning
      1. Understanding the Problem
        1. Understanding the Business Problem
        2. Understanding the Technical Problem
        3. Understanding the SQL Statement
        4. Understanding the Data
        5. Understanding the Problem Wrap Up
      2. Analysis
        1. Running the Statement to Completion
        2. Analyzing Elapsed Time
        3. When the Elapsed Times Doesn’t Add Up
        4. When the Time Does Add Up
      3. Fixing the Problem
        1. Check the Statistics
        2. Changing the Code
        3. Adding Hints
        4. Making Physical Changes to the Database
        5. Making Changes to the Environment
        6. Running the SQL Tuning Advisor
        7. Rethink the Requirement
      4. Summary
    6. Chapter 6: Object Statistics and Deployment
      1. The Principle of Performance Management
        1. The Royal Mail Example
        2. The Airport Example
        3. Service Level Agreements in IT
        4. Non-database Deployment Strategies
      2. The Strategic Direction for the CBO
        1. The History of Strategic Features
        2. Implications of the CBO Strategy
        3. Why We Need to Gather Statistics
      3. How Often Do We Need to Change Execution Plans?
        1. Wolfgang Breitling’s Tuning by Cardinality Feedback
        2. The TCF Corollary
      4. Concurrent Execution Plans
        1. Skewed Data and Histograms
        2. Workload Variations
        3. Concurrent Execution Plans Wrap Up
      5. Oracle’s Plan Stability Features
        1. Stored Outlines
        2. SQL Profiles
        3. SQL Plan Baselines
      6. Introducing TSTATS
        1. Acknowledgements
        2. Adjusting Column Statistics
        3. TSTATS in a Nutshell
      7. An Alternative to TSTATS
      8. Deployment Options for Tuned SQL
        1. When Just One SQL Statement Needs to Change
        2. When Multiple SQL Statements Need to Change
      9. Summary
  14. Part 2: Advanced Concepts
    1. Chapter 7: Advanced SQL Concepts
      1. Query Blocks and Subqueries
        1. Terminology
        2. How Query Blocks are Processed
      2. Functions
        1. Aggregate Functions
        2. Analytic Functions
        3. Combining Aggregate and Analytic Functions
        4. Single-row Functions
      3. The MODEL Clause
        1. Spreadsheet Concepts
        2. A Moving Median with the MODEL Clause
        3. Why Not Use PL/SQL?
      4. Summary
    2. Chapter 8: Advanced Execution Plan Concepts
      1. Displaying Additional Execution Plan Sections
        1. DBMS_XPLAN Formatting Options
        2. Running EXPLAIN PLAN for Analysis
        3. Query Blocks and Object Alias
        4. Outline Data
        5. Peeked Binds
        6. Predicate Information
        7. Column Projection
        8. Remote SQL
        9. Adaptive Plans
        10. Result Cache Information
        11. Notes
      2. Understanding Parallel Execution Plans
        1. Operations That Can Be Run in Parallel
        2. Controlling Parallel Execution
        3. Granules of Parallelism
        4. Data Flow Operators
        5. Parallel Query Server Sets and DFO Trees
        6. Table Queues and DFO Ordering
        7. Multiple DFO Trees
        8. Parallel Query Distribution Mechanisms
        9. Why Forcing Parallel Query Doesn’t Force Parallel Query
        10. Further Reading
      3. Understanding Global Hints
        1. Hinting Data Dictionary Views
        2. Applying Hints to Transformed Queries
        3. The NO_MERGE Hint
      4. Summary
    3. Chapter 9: Object Statistics
      1. The Purpose of Object Statistics
      2. Creating Object Statistics
        1. Gathering Object Statistics
        2. Exporting and Importing Statistics
        3. Transferring Statistics
        4. Setting Object Statistics
        5. Creating or Rebuilding Indexes and Tables
        6. Creating Object Statistics Wrap Up
      3. Examining Object Statistics
        1. Examining Object Statistics in the Data Dictionary
        2. Examining Exported Object Statistics
      4. Statistic Descriptions
        1. Table Statistics
        2. Index Statistics
        3. Column Statistics
        4. Statistics Descriptions Wrap-up
      5. Statistics and Partitions
        1. Gathering Statistics on Partitioned Tables
        2. How the CBO Uses Partition-level Statistics
        3. Why We Need Partition-level Statistics
        4. Statistics and Partitions Wrap-up
      6. Restoring Statistics
      7. Locking Statistics
      8. Pending Statistics
      9. A Footnote on Other Inputs to the CBO
        1. Initialization Parameters
        2. System Statistics
        3. Other Data Dictionary Information
      10. Summary
  15. Part 3: The Cost-Based Optimizer
    1. Chapter 10: Access Methods
      1. Access by ROWID
        1. ROWID Concepts
        2. Access by ROWID
      2. B-tree Index Access
        1. INDEX FULL SCAN
        2. INDEX RANGE SCAN
        3. INDEX SKIP SCAN
        4. INDEX UNIQUE SCAN
        5. INDEX FAST FULL SCAN
        6. INDEX SAMPLE FAST FULL SCAN
        7. INDEX JOIN
        8. AND_EQUAL
      3. Bitmap Index Access
      4. Full Table Scans
      5. TABLE and XMLTABLE
      6. Cluster Access
      7. Summary
    2. Chapter 11: Joins
      1. Join Methods
        1. Nested loops
        2. Hash joins
        3. Merge joins
        4. Cartesian joins
      2. Join Orders
        1. Join orders without hash join input swapping
        2. Join orders with hash join input swapping
      3. Semi-joins
        1. Standard semi-joins
        2. Null-accepting semi-joins
      4. Anti-joins
        1. Standard anti-joins
        2. Null-aware anti-joins
      5. Distribution Mechanisms for Parallel Joins
        1. The PQ_DISTRIBUTE hint and parallel joins
        2. Full partition-wise joins
        3. Partial partition-wise joins
        4. Broadcast distribution
        5. Row source replication
        6. Hash distribution
        7. Adaptive parallel joins
        8. Data buffering
        9. Bloom filtering
      6. Summary
    3. Chapter 12: Final State Optimization
      1. Join Order
      2. Join Method
      3. Access Method
      4. IN List Iteration
      5. Summary
    4. Chapter 13: Optimizer Transformations
      1. No-brainer Transformations
        1. Count Transformation
        2. Predicate Move-around
      2. Set and Join Transformations
        1. Join Elimination
        2. Outer Join to Inner Join
        3. Full Outer Join to Outer Join
        4. Semi-Join to Inner Join
        5. Subquery Unnesting
        6. Partial Joins
        7. Join Factorization
        8. Set to Join
      3. Aggregation Transformations
        1. Distinct Aggregation
        2. Distinct Placement
        3. Group by Placement
        4. Group by Pushdown
      4. Subquery Transformations
        1. Simple View Merging
        2. Complex View Merging
        3. Factored Subquery Materialization
        4. Subquery Pushdown
        5. Join Predicate Pushdown
        6. Subquery Decorrelation
        7. Subquery Coalescing
      5. Miscellaneous Transformations
        1. Or Expansion
        2. Materialized View Rewrite
        3. Grouping Sets to Union Expansion
        4. Order by Elimination
        5. Table Expansion
      6. Star Transformation
        1. The Distributed Join Filter Problem
        2. Solving the Distributed Join Filter Problem
      7. In the Future
      8. Summary
  16. Part 4: Optimization
    1. Chapter 14: Why Do Things Go Wrong?
      1. Cardinality Errors
        1. Correlation of Columns
        2. Statistics Feedback and DBMS_STATS.SEED_COL_USAGE Features
        3. Functions
        4. Stale Statistics
        5. Daft Data Types
      2. Caching Effects
      3. Transitive Closure
      4. Unsupported Transformations
      5. Missing Information
      6. Bad Physical Design
      7. Contention
      8. Summary
    2. Chapter 15: Physical Database Design
      1. Adding and Removing Indexes
        1. Removing Indexes
        2. Identifying Required Indexes
      2. Managing Contention
        1. Sequence Contention
        2. The Hot-block Problem
      3. Partitioning
        1. Full Table Scans on Partitions or Subpartitions
        2. Partition-wise Joins
        3. Parallelization and Partitioning
      4. Denormalization
        1. Materialized Views
        2. Manual Aggregation and Join Tables
        3. Bitmap Join Indexes
      5. Compression
        1. Index Compression
        2. Table Compression
      6. LOBs
      7. Summary
    3. Chapter 16: Rewriting Queries
      1. Use of Expressions in Predicates
      2. Equality Versus Inequality Predicates
      3. Implicit Data-Type Conversions
      4. Bind Variables
      5. UNION, UNION ALL, and OR
      6. Issues with General Purpose Views
      7. How to Use Temporary Tables
      8. Avoiding Multiple Similar Subqueries
      9. Summary
    4. Chapter 17: Optimizing Sorts
      1. The Mechanics of Sorting
        1. Memory Limits for Sorts
        2. Disk-based Sorts
      2. Avoiding Sorts
        1. Non-sorting Aggregate Functions
        2. Index Range Scans and Index Full Scans
        3. Avoiding Duplicate Sorts
      3. Sorting Fewer Columns
        1. Taking Advantage of ROWIDs
        2. Solving the Pagination Problem
      4. Sorting Fewer Rows
        1. Additional Predicates with Analytic Functions
        2. Views with Lateral Joins
        3. Avoiding Data Densification
      5. Parallel Sorts
      6. Summary
    5. Chapter 18: Using Hints
      1. Are Hints Supportable?
        1. The PUSH_SUBQ story
        2. The DML error logging story
        3. Documented versus undocumented hints
        4. The MODEL clause corollary
        5. Supportability conclusion
      2. Types of Hints
        1. Edition -based redefinition hints
        2. Hints that cause errors
        3. Runtime engine hints
        4. Optimizer hints that are hints
      3. Production-hinting case studies
        1. The bushy join
        2. Materialization of factored subqueries
        3. Suppressing order by elimination and subquery unnesting
        4. The v$database_block_corruption view
      4. Summary
    6. Chapter 19: Advanced Tuning Techniques
      1. Leveraging an INDEX FAST FULL SCAN
      2. Simulating a Star Transformation
      3. Simulating an INDEX JOIN
      4. Joining Multi-Column Indexes
      5. Using ROWID Ranges for Application-Coded Parallel Execution
      6. Converting an Inner Join to an Outer Join
      7. Summary
  17. Part 5: Managing Statistics with TSTATS
    1. Chapter 20: Managing Statistics with TSTATS
      1. Managing Column Statistics
        1. Time-based columns
        2. Columns with NUM_DISTINCT=1
        3. Skewed column values and range predicates
        4. Correlated columns and expressions
        5. Use of sample data for complex statistical issues
        6. Managing column statistics wrap up
      2. Statistics and Partitions
        1. The DBMS_STATS.COPY_TABLE_STATS myth
        2. Cardinality estimates with global statistics
        3. Costing full table scans of table partitions
      3. Temporary Tables
        1. The pros and cons of dynamic sampling
        2. Fabricating statistics for temporary tables
      4. How to Deploy TSTATS
      5. Summary
  18. Index
  19. General

Product information

  • Title: Expert Oracle SQL
  • Author(s):
  • Release date: July 2014
  • Publisher(s): Apress
  • ISBN: 9781430259770