Effective Oracle by Design

Table of contents

1. Cover Page
2. Effective Oracle by Design
3. Copyright Page
4. Contents
5. ACKNOWLEDGMENTS
6. INTRODUCTION
7. 1 The Right Approach to Building Applications
   1. It’s a Team Effort
      1. DBA and Developer Roles
   2. Read the Documentation
      1. A Guide to the Guides
      2. Road Maps to Reading
   3. Avoid the Black Box Syndrome
      1. Database Independence versus Database Dependence
      2. Dangers of Black Box Syndrome
   4. It’s a Database, Not a Data Dump
      1. Use Primary and Foreign Keys
      2. Test the Overhead of Referential Integrity
      3. Middle Tier Checking Is Not a Panacea
   5. Build a Test Environment
      1. Test Against Representative Data
      2. Don’t Test with a Single User
      3. Don’t Test in a Dust-Free Lab
   6. Design to Perform; Don’t Tune to Perform
      1. Don’t Use Generic Data Models
      2. Design Your Data Model for Efficiency
   7. Define Your Performance Goals from the Start
      1. Work to Clear, Specific Metrics
      2. Collect and Log Metrics over Time
      3. Don’t Do It Because “Everyone Knows You Should”
   8. Benchmark, Benchmark, Benchmark
      1. Small-Time Benchmarking
      2. Big-Time Benchmarking
   9. Instrument the System
      1. Trace from asktom.oracle.com
      2. Instrument for Remote Debugging
      3. Use DBMS_APPLICATION_INFO Everywhere
      4. Use DEBUG.F in PL/SQL
      5. Turn on SQL_TRACE in Your Application
      6. Use Industry-Standard APIs
      7. Build Your Own Routines
      8. Audit Is Not a Four-Letter Word
   10. Question Authority
       1. Beware of Universal “Bests”
       2. Suspect Ratios and Other Myths
   11. Don’t Look for Shortcuts
   12. Keep It Simple
       1. Consider Alternate Approaches
       2. Let the Database Do What It Does Best
   13. Use Supplied Functionality
       1. We Heard Feature X Is Slow
       2. We Heard Feature X Is Complicated
       3. We Don’t Want to
       4. We Didn’t Know
       5. We Want Database Independence
   14. Summary
8. 2 Your Performance Toolkit
   1. SQL*Plus
      1. Set Up SQL*Plus
      2. Customize the SQL*Plus Environment
      3. Read the Documentation!
   2. EXPLAIN PLAN
      1. Setup for EXPLAIN PLAN
      2. Use EXPLAIN PLAN
      3. How to Read a Query Plan
      4. Avoid the EXPLAIN PLAN Trap
      5. Use DBMS_XPLAN and V$SQL_PLAN
   3. AUTOTRACE
      1. Set Up AUTOTRACE
      2. Use AUTOTRACE
      3. Format the AUTOTRACE Output
      4. Understand the AUTOTRACE Output
      5. What Are You Looking for in AUTOTRACE Output?
   4. TKPROF
      1. Enable TKPROF
      2. Run TKPROF
      3. Read a TKPROF Report
      4. TKPROF for the Masses
   5. Runstats
      1. Set Up Runstats
      2. Use Runstats
   6. Statspack
      1. Set Up Statspack
      2. Use Statspack
      3. What People Do Wrong with Statspack
      4. Statspack at a Glance
   7. DBMS_PROFILER
      1. Why You Want to Use the Profiler
      2. Profiler Resources
   8. JDeveloper (and Debugging)
   9. Summary
9. 3 Architectural Decisions
   1. Understand Shared Server vs. Dedicated Server Connections
      1. How Do Dedicated Server Connections Work?
      2. How Do Shared Server Connections Work?
      3. Common Misconceptions about Shared Server Connections
      4. Dedicated Server vs. Shared Server Wrap-Up
   2. Take Advantage of Clustering
      1. How Does RAC Work?
      2. What Are the Benefits of RAC?
      3. Clustering Wrap-Up
   3. Know When to Use Partitioning
      1. Partitioning Concepts
      2. The Partitioning Myth
      3. Why Use Partitioning?
      4. Partitioning Wrap-Up
   4. Know When to Use Parallel Operations
      1. The Parallel Myth
      2. Parallel Administration
      3. Parallel Query
      4. Parallel DML
      5. DIY Parallelism
      6. Parallel Processing Wrap-Up
   5. Summary
10. 4 Effective Administration
    1. Use SPFILEs to Start Your Database
       1. The Problems with PFILEs
       2. How SPFILEs Work
       3. Convert a Database to Use SPFILE
       4. Save System Parameter Changes
       5. Are PFILEs Obsolete?
       6. Help, My SPFILE Is Broken and I Cannot Start
       7. SPFILE Wrap-Up
    2. Let Oracle Manage Your Datafiles
       1. When Is OMF Useful?
       2. How OMF Works
       3. OMF Wrap-Up
    3. Bulletproof Your Recovery
       1. Backup Guidelines
       2. Backup and Recovery Wrap-Up
    4. Use Locally Managed Tablespaces
       1. Why Are DMTs Obsolete?
       2. Use System-Managed LMTs When You Do Not Know How Big Your Objects Will Become
       3. Use Uniform Extent Sizes When You Know the Ultimate Size of an Object
       4. Some LMT Caveats
       5. LMT and DMT Wrap-Up
    5. Let Oracle Manage Your Segment Space
       1. Understand Freelists and Freelist Groups
       2. How PCTFREE and PCTUSED Control Freelists
       3. The Case for ASSM
       4. ASSM Wrap-Up
    6. Let Oracle Manage Your Rollback Segments
       1. Set UNDO_RETENTION
       2. An UNDO Tablespace Caveat
       3. UNDO Tablespace Wrap-Up
    7. Summary
11. 5 Statement Processing
    1. Understand the Types of SQL Statements
    2. How Are Statements Executed?
       1. Parsing
       2. Optimization and Row-Source Generation
       3. Execution
       4. Statement Execution Wrap-Up
    3. Queries from Start to Finish
       1. A Quick-Return Query
       2. A Slow-Return Query
       3. Consistent Reads
    4. Modification DML from Start to Finish
    5. DDL Processing
    6. Use Bind Variables
       1. What Are the Advantages of Using Bind Variables?
       2. Use Bind Variables with Java and VB
       3. There Are Exceptions to Every Rule
       4. Bind Variable Peeking
    7. Parse as Little as Possible
       1. The Cost of Parsing
       2. Use PL/SQL to Reduce Parses
       3. Move SQL Out of Triggers to Reduce Parsing
       4. Prepare Once; Execute Many
    8. Summary
12. 6 Getting the Most Out of the Cost-Based Optimizer
    1. Why the RBO Is Dead
    2. Make the CBO Do Its Best
       1. Adjust the OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ Parameters
       2. Use SYSTEM Statistics
    3. Optimize the CBO
       1. Set COMPATIBLE for Upgrades
       2. Set DB_FILE_MULTIBLOCK_READ_COUNT to Reduce Full-Scan Costs
       3. Set HASH_JOIN_ENABLED to Control Hash Joins
       4. Set OPTIMIZER_DYNAMIC_SAMPLING to Gather Statistics Dynamically
       5. Set OPTIMIZER_FEATURES_ENABLE to Control Feature Choices
       6. Set OPTIMIZER_MAX_PERMUTATIONS to Control Permutations
       7. Set OPTIMIZER_MODE to Pick a Mode
       8. Rewrite Queries with QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY
       9. Control PGA Memory with BITMAP_MERGE_AREA_ SIZE, SORT_AREA_SIZE, and HASH_AREA_SIZE
       10. Use STAR_TRANSFORMATION_ENABLED for Star Queries
       11. Set Others Parameters that Affect the Optimizer
    4. Use the 10053 Event to Trace CBO Choices
    5. Summary
13. 7 Effective Schema Design
    1. Fundamental Schema Design Principles
       1. Let the Database Enforce Data Integrity
       2. Use the Correct Datatype
       3. Optimize to Your Most Frequently Asked Questions
    2. Overview of Table Types
    3. B*Tree Index Clustered Tables
       1. Create Clusters
       2. Use Clusters
       3. Clusters Wrap-Up
    4. Index-Organized Tables (IOTs)
       1. Use IOTs as a Space-Saving Alternative to Association Tables
       2. Use IOTs to Colocate Randomly Inserted Data
       3. IOTs Wrap-Up
    5. External Tables
       1. Set Up External Tables
       2. Modify External Tables
       3. Use External Tables for Direct-Path Loading
       4. Use External Tables for Parallel Direct-Path Loading
       5. Use External Tables for Merging
       6. Handle Errors with External Tables
    6. Indexing Techniques
       1. Use FBIs—Think Outside the Box
       2. Use Domain Indexes
    7. Compression
       1. Use Index Key Compression
       2. Use Table Compression for Read-Only/Read-Mostly Tables
       3. Compression Wrap-Up
    8. Summary
14. 8 Effective SQL
    1. What You Need to Write Efficient SQL
    2. Understand Access Paths
       1. Full Scans
       2. ROWID Access
       3. Index Scans
       4. Cluster Scans
    3. Understand Joins
       1. Nested Loops
       2. Hash Joins
       3. Sort-Merge Joins
       4. Cartesian Joins
       5. Anti-Joins
       6. Full Outer Joins
    4. Schema Matters (Physical)
    5. Really Know SQL
       1. The ROWNUM Pseudo Column
       2. Scalar Subqueries
       3. Analytics
    6. Don’t Tune a Query
       1. Understand the Question
       2. A Proof-of-Concept Example
    7. Overview of Other SQL Techniques
    8. Summary
15. 9 Effective PL/SQL Programming
    1. Why PL/SQL?
       1. PL/SQL Is the Most Efficient Language for Data Manipulation
       2. PL/SQL Is Portable and Reusable
    2. Write as Little as You Can
       1. Not Doing It Procedurally
       2. Fit Your Code on the Screen
    3. Use Packages
       1. Advantages of Packages
       2. Break the Dependency Chain
       3. Packages Wrap-Up
    4. Use Static SQL
       1. Advantages of Static SQL
       2. Look for Opportunities to Replace Dynamic SQL
       3. Static SQL Wrap-Up
    5. Bulk Processing
       1. Use Bulk Processing When It Has Dramatic Effects
       2. Use Bulk Processing for ETL Operations
       3. Bulk Processing Wrap-Up
    6. Returning Data
       1. Advantages of Ref Cursors
       2. Use Ref Cursors to Return Result Sets
    7. Use %TYPE and %ROWTYPE
       1. Base Record Types on a Table
       2. Base Record Types on a Cursor
       3. Base Datatypes on a Column
    8. Using Invoker Rights
       1. Invoker Rights and Multiple Schemas
       2. Criteria for Invoker Rights Routines
    9. Make Your Lookups Work Efficiently
       1. Single-Row Fetching for Lookups
       2. Bulk Processing for Lookups
       3. Single-Statement Operations for Lookups
       4. Lookup Wrap-Up
    10. Be Careful with Autonomous Transactions
        1. Criteria for Autonomous Transactions
        2. Autonomous Transactions Can Impact Data Integrity
    11. Choose Whether to Use Implicit or Explicit Cursors
        1. Use Implicit Cursors for Single-Row Selections
        2. Use Implicit Cursors for Result Sets with a Limited Number of Rows
        3. Implicit/Explicit Cursors Wrap-Up
    12. Summary
16. 10 So, You Had an Accident
    1. Find Out What’s Different
       1. Start Collecting History Today
       2. Detective Work
    2. Change One Thing at a Time
    3. Have a Sound Reason for Changing that One Thing
       1. Have a Goal
       2. Validate Your Hypothesis
    4. Be Able to Unchange Things
    5. Build a Test Case
       1. Test Case Requirements
       2. Keep Your Test Cases as Small as Possible
    6. Summary
17. A Setting Up and Some Scripts
    1. Setting Up BIG_TABLE
    2. Frequently Used Scripts
       1. PRINT_TABLE
       2. SHOW_SPACE
       3. COLS_AS_ROWS
    3. GEN_DATA
18. Index