Oracle PL/SQL Performance Tuning Tips & Techniques

Oracle PL/SQL Performance Tuning Tips & Techniques

by Michael Rosenblum, Paul Dorsey
Released August 2014
Publisher(s): McGraw Hill Computing
ISBN: 9780071822183

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

Proven PL/SQL Optimization Solutions

In Oracle PL/SQL Performance Tuning Tips & Techniques, Oracle ACE authors with decades of experience building complex production systems for government, industry, and educational organizations present a hands-on approach to enabling optimal results from PL/SQL. The book begins by describing the discovery process required to pinpoint performance problems and then provides measurable and repeatable test cases. In-depth coverage of linking SQL and PL/SQL is followed by deep dives into essential Oracle Database performance tuning tools. Real-world examples and best practices are included throughout this Oracle Press guide.

  • Follow a request-driven nine-step process to identify and address performance problems in web applications
  • Use performance-related database tools, including data dictionary views, logging, tracing, PL/SQL Hierarchical Profiler, PL/Scope, and RUNSTATS
  • Instrument code to pinpoint performance issues using call stack APIs, error stack APIs, and timing markers
  • Embed PL/SQL in SQL and manage user-defined functions
  • Embed SQL in PL/SQL using a set-based approach to handle large volumes of data
  • Properly write and deploy data manipulation language triggers to avoid performance problems
  • Work with advanced datatypes, including LOBs and XML
  • Use caching techniques to avoid redundant operations
  • Effectively use dynamic SQL to reduce the amount of code needed and streamline system management
  • Manage version control and ensure that performance fixes are successfully deployed

Code examples in the book are available for download.

Table of contents

  1. Cover
  2. Title Page
  3. Copyright Page
  4. Dedication
  5. Contents
  6. Foreword
  7. Acknowledgments
  8. Introduction
  9. Part I: Core Ideas and Elements of PL/SQL Performance Tuning
    1. 1 The Role of PL/SQL in Contemporary Development
      1. Typical Web Application Process Flow
      2. Web Application Performance Problem Areas
        1. Step 1: Client Machine Performance Problems
        2. Step 2: Client Machine to Application Server Transmission Problems
        3. Step 3: Application Server Performance Problems
        4. Step 4: Application Server to Database Transmission Problems
        5. Step 5: Database Performance Problems
        6. Step 6: Database to Application Server Transmission Problems
        7. Step 7: Application Server Processing Performance Problems
        8. Step 8: Application Server to Client Machine Transmission Problems
        9. Step 9: Client Machine Performance Problems
      3. Finding the Cause of Slowly Performing Web Applications
        1. Using Timers to Gather Data About Performance
        2. Measuring Performance
      4. Solving Web Application Performance Problems
        1. Solving Client Machine Performance Problems (Steps 1 and 9)
        2. Resolving Performance Issues Between the Client Machine and Application Server (Step 2)
        3. Solving Performance Problems in the Application Server (Steps 3 and 7)
        4. Solving Performance Problems in the Client Machine (Step 9)
        5. Lessons Learned
      5. Summary
    2. 2 DBA/Developer Borderline: Tools and Features
      1. Data Dictionary Views
      2. Oracle Logging and Tracing
        1. Logging Basics
        2. Tracing Basics
        3. Logging/Tracing Example
      3. PL/SQL Hierarchical Profiler
      4. RUNSTATS
      5. PL/SQL Environment Settings
        1. PLSQL_OPTIMIZE_LEVEL
        2. PLSQL_CODE_TYPE
        3. PLSQL_WARNINGS
        4. PLSQL_CCFLAGS
      6. PL/Scope
      7. Summary
    3. 3 Code Instrumentation in PL/SQL
      1. Is the Problem Really in the Database?
      2. Application Logging
        1. Built-in Code Navigation APIs
        2. User-Driven Logging
      3. Code Instrumentation Best Practices
        1. Placing Process Markers
        2. Placing Error Markers
      4. Summary
  10. Part II: Linking SQL and PL/SQL
    1. 4 Expanding the SQL Horizons
      1. Stepping Outside the SQL Box
        1. Making Life Simpler by Switching to PL/SQL
        2. Using PL/SQL to Fill Functionality Gaps
      2. Calling Functions Within SQL
        1. Single-Table Problems
        2. Multi-Table Problems
      3. PL/SQL-Related Statistics and Their Impact on Execution Plans
        1. Hardware Costs of PL/SQL Functions
        2. Cardinality of PL/SQL Functions
        3. Selectivity of PL/SQL Functions
      4. Oracle Database 12c–Only Features
        1. PRAGMA UDF Clause
        2. Adding Functions Inside the WITH Clause
      5. Summary
    2. 5 Thinking in Sets
      1. Cursors
      2. Loading Sets from SQL to PL/SQL
        1. Oracle Database 12c: Implicit Pagination vs. Continuous Fetch
        2. Merging Sets Using PL/SQL
      3. “…And Justice FORALL!”
        1. Staying Up to Date with Syntax: Sparse Collections
        2. Direct Inserts
        3. FORALL and Table Triggers
      4. Summary
    3. 6 Pulling the Trigger
      1. DML Triggers
        1. Data Protection: Constraints vs. Triggers
        2. Default Values
        3. Cost of Denormalization
      2. INSTEAD OF Triggers
        1. Basic DML Operations
        2. Dangers of Logical Primary Keys
        3. Handling UPDATE Statements
      3. Summary
  11. Part III: Tuner’s Toolkit
    1. 7 Going Beyond Scalar Datatypes
      1. Managing LOBs
        1. Access to LOBs
        2. Storage Mechanisms
        3. I/O Tuning Considerations
        4. SecureFile-Only Features
      2. Managing XML
        1. Storing XML
        2. Manipulating XML
      3. Summary
    2. 8 Keeping the Cache
      1. Built-in Caching Techniques
        1. Deterministic Functions
        2. Scalar Subquery Caching
        3. PL/SQL Function Result Cache
      2. Manual Caching Techniques
        1. PL/SQL Collections
        2. Oracle Context
      3. Summary
    3. 9 Shooting at a Moving Target
      1. Expanding the Knowledge Base
        1. CLOB Input
        2. Cursor Transformation
        3. PL/SQL Function Result Cache Integration
        4. Support for Complex Datatypes
      2. Digging Deeper
        1. More About Search
        2. IN-LIST Trap
      3. Challenging Dynamic SQL Myths
        1. Myth #1: Dynamic SQL Is Always a Security Risk
        2. Myth #2: Dynamic SQL Is Always Slower Than Regular SQL
        3. Myth #3: Dynamic SQL Always Causes Parsing
        4. Myth #4: DDL Statements Are Only for DBAs
      4. Summary
  12. Part IV: PL/SQL in Daily Life
    1. 10 Tales from the Trenches
      1. Third-Party Wrapped Code
      2. Stateless Issues
      3. Unknown UNDO
      4. The Curse of Recursion
        1. Recursion and Cursors
        2. Recursion and Variables
      5. Summary
    2. 11 Code Management in Real-World Systems
      1. The Problem of Code Management
        1. Versioning “Lite” for DBAs
        2. Homegrown Versioning
      2. Edition-Based Redefinition and Performance Tuning
        1. Understanding Edition-Based Redefinition
        2. Important Changes to EBR Introduced in Oracle Database 12c
      3. System Environment Differences and Performance-Related Code Management
      4. Summary
    3. 12 Extra Tips, Tricks, and Ideas
      1. Back to Basics
        1. VARCHAR2 Memory Allocation
        2. The Cost of Current Date
        3. BINARY Datatypes
      2. Text Manipulation
        1. Checking Text Strings for Valid Characters
        2. Similarity of Words
        3. VARCHAR2 and Views
      3. Increasing Processing Complexity
        1. NOCACHE Optimization
        2. ACCESSIBLE BY Clause
        3. More About Pipelined Functions
      4. Summary
  13. Index

Product information

  • Title: Oracle PL/SQL Performance Tuning Tips & Techniques
  • Author(s): Michael Rosenblum, Paul Dorsey
  • Release date: August 2014
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071822183