Oracle Performance Survival Guide   A Systematic Approach to Database Optimization   The fast, complete, start-to-finish guide to optimizing Oracle performance   Oracle Performance Survival Guide offers a structured, systematic, start-to-finish methodology for optimizing Oracle performance as efficiently as possible. Leading Oracle expert Guy Harrison shows how to maximize your tuning investment by focusing on causes rather than symptoms, and by quickly identifying the areas that deliver the greatest “bang for the buck.”   Writing for DBAs and developers with all levels of experience, Harrison covers every area of Oracle performance management, from application design through SQL tuning, contention management through memory and physical IO management. He also presents up-to-the-minute guidance for optimizing the performance of the Oracle 11g Release 2.    You’ll start by mastering Oracle structured performance tuning principles and tools, including techniques for tracing and monitoring Oracle execution. Harrison illuminates the interaction between applications and databases, guides you through choosing tuning tools, and introduces upfront design techniques that lead to higher-performance applications. He also presents a collection of downloadable scripts for reporting on all aspects of database performance.   Coverage includes      •    “Tuning by layers,” the most effective, highest-value approach to Oracle performance optimization      •    Making the most of Oracle’s core tools for tracing, monitoring, and diagnosing performance      •    Highly efficient database logical and physical design, indexing, transaction design, and API use      •    SQL and PL/SQL tuning, including the use of parallel SQL techniques      •    Minimizing contention for locks, latches, shared memory, and other database resources      •    Optimizing memory and physical disk IO      •    Tuning Real Application Cluster (RAC) databases   guyharrison.net informit.com/ph

Table of Contents:
Preface Part I: Methods, Concepts, and Tools Chapter 1. Oracle Performance Tuning: A Methodical Approach A Brief History of Oracle Performance Tuning Moving Beyond a Symptomatic Approach Stage 1: Minimizing the Application Workload Stage 2: Reducing Contention and Bottlenecks Stage 3: Reducing Physical IO Stage 4: Optimizing Disk IO Summary Chapter 2. Oracle Architecture and Concepts The Oracle APIs     Creating the Cursor     Checking for Cached SQL Statements      Parsing the SQL     Associating Bind Variables     Executing the SQL     Fetching Rows     Using Array Fetch     Processing Result Sets     Closing the Cursor     Optimizing Oracle API Calls The Oracle Query Optimizer     Cost Based Optimization     Optimizer Goal     Optimizer Statistics     Bind Variable Peeking and Adaptive Cursor Sharing     Hints     Outlines, Profiles, and Baselines Transactions and Locking Oracle Server Architecture     Instances and Databases     The System Global Area     Data Caching     The Program Global Area     Memory Management Segments and Files     Tables     Indexes     Blocks, Extents, Segments, and Partitions     Tablespaces and Data Files     Undo Segments     Redo Logs and Archive Logs     Flashback Logs Server Processes Background Processes Real Application Clusters Summary Chapter 3. Tools of the Trade Explaining SQL Statements     The Plan Table     Exploiting Cached SQL     DBMS_XPLAN     Interpreting the Execution Plan     Virtual Indexing Tracing Oracle Execution     Tracing from Within Your Session     Identifying Your Trace File     Getting Tracing Status     Invoking Trace in Another Session     Tracing by MODULE, ACTION, or SERVICE     Starting a Trace Using a Login Trigger     Finding the Trace File     Other Specialized Traces Formatting Traces with tkprof     The tkprof Sort Options     Other tkprof Options     Merging Multiple SQL Trace Files     Interpreting Tkprof Output     Execution Plans in tkprof     Wait Statistics and tkprof     Alternatives to tkprof Using AUTOTRACE in SQL*PLUS Monitoring the Oracle Server     The V$ table interface     Wait Interface     The Time Model     Integrating the Time Model and Wait Interface     Oracle Enterprise Manager     Spotlight on Oracle Summary Part II: Application and Database Design Chapter 4. Logical and Physical Database Design Logical Data Modeling     Normalization and Third Normal Form     Data Type Choices     Artificial Keys     Data Warehouse Design Logical to Physical     Mapping Entities or Classes to Tables     Choosing a Table Type     Data Types and Precisions     Optional Attributes and NULL Values     Column Order     Exploiting Oracle Object Types Denormalization     Replicating Column Values to Avoid Joins     Summary Tables     Vertical Partitioning     Implementing Denormalization Star Schema Design     Star Schema Basics     Snowflakes Schemas     Dimension Hierarchies     Aggregations and Materialized Views     Materialized View Best Practices Physical Storage Options     Manual and Automatic Segment Storage Management     Concurrent Inserts and Freelists     PCTFREE and PCTUSED     Compression     LOB Storage Oracle Partitioning     Types of Partitions     Composite Partitions     Choosing a Partitioning Strategy     Enterprise Manager Partitioning Advisor Summary Chapter 5. Indexing and Clustering Overview of Oracle Indexing and Clustering B*-Tree Indexes     Index Selectivity     Unique Indexes     Implicit Indexes     Concatenated Indexes     Index Skip Scans     Guidelines for Concatenated Indexes     Index Merges     Null Values in Indexes     Reverse Key Indexes     Index Compression     Functional Indexes     Foreign Keys and Locking     Indexes and Partitioning Bitmap Indexes     Features of Bitmap Indexes     Drawbacks of Bitmap Indexes     Bitmap Indexes and Cardinality     Bitmap Index Merge     Bitmap Join Indexes Index Overhead     Index Organized Tables     Configuring the Overflow Segment     Periodic Rebuild of Index Only Tables Clustering     Index Clusters     Hash Clusters Nested Tables Choosing the Best Indexing Strategy Summary Chapter 6. Application Design and Implementation SQL Statement Management     Optimizing Parsing     Avoiding Unnecessary SQL Executions The Array Interface     Implementing Array Fetch     Array Insert Transaction Design     Isolation Levels     Transactions and Locks     Row Level Locking in Oracle     Application Locking Strategies Using Stored Procedures to Reduce Network Traffic Summary Part III: SQL and PL/SQL Tuning Chapter 7. Optimizing the Optimizer The Oracle Optimizer     What Is Cost?     Optimizer Goal     Selectivity and Cardinality     Query Transformation     Cost Calculations     Object Statistics     Histograms     Bind Variable Peeking     Adaptive Cursor Sharing     Database Parameters     System Statistics     Collecting Statistics Using DBMS_STAT     DBMS_STATS Procedures and Parameters     Setting DBMS_STATS Defaults     Creating Histograms with METHOD_OPT     Sampling     Partition Statistics     Extended Statistics     Locking Statistics     System Statistics     Exporting and Importing Statistics     Manipulating Statistics Summary Chapter 8. Execution Plan Management Hints     Using Hints to Change the Access Path     Using Hints to Change the Join Order     Errors in Hint Specifications Stored Outlines     Creating an Outline to Stabilize a Plan     Hacking an Outline     SQL Tuning Sets     Manually Creating a Tuning Set     Creating Tuning Sets in     Enterprise Manager SQL Profiles and the SQL Tuning Advisor     Using DBMS_SQLTUNE     Indexing Advice     SQL Tuning in Enterprise Manager     Cross-SQL Tuning with the SQL Access Advisor SQL Baselines     Creating the Baseline     Evolving the Baseline     Automating and Configuring Baselines     Fixed Baselines     Baseline Management in Oracle     Enterprise Manager Summary Chapter 9. Tuning Table Access Single Value Lookups     Choosing Between Table and Index Scan     Bitmap Indexes and Single Value Lookups     Hash Clusters and Single Value Lookups Avoiding “Accidental” Table Scans     NOT EQUALS Conditions     Searching for Nulls     Searching for Values That Are NOT NULL     Creating Indexes on NULLable Columns     Unintentionally Disabling an Index with a Function     Functional Indexes     Functional Indexes and Statistics     Virtual Columns Multicolumn Lookups     Using Concatenated Indexes     Index Merges     Uniqueness and Over-Indexing Searching for Ranges     Unbounded Range Scan     Bounded Range Scans     Range Lookups Using the LIKE Operator Multvalue Single-Column Lookups Optimizing Necessary Full Table Scans     Lowering the High Water Mark     Optimizing PCTFREE and PCTUSED     Reducing the Row Length     Compressing the Table     Making Database IO More Efficient     Using the SAMPLE Option     Parallel Query     The Fast Full Index Scan     Partitioning Summary Chapter 10. Joins and Subqueries Types of Joins Join Methods     Nested Loops Join     Sort-Merge Join     Hash Join Choosing the Right Join Method     Sort-Merge/Hash Versus Nested Loops     Sort-Merge Versus Hash Joins Optimizing Joins     Optimizing Nested Loops Join     Optimizing Sort-Merge and Hash Joins Avoiding Joins     Denormalization     Index Clusters     Materialized Views     Bitmap Join Index Join Order     Special Joins     Outer Joins     Star Joins     Hierarchical Joins Subqueries     Simple Subqueries     Correlated Subqueries     Anti-Join Subqueries     Semi-Join Subqueries Summary Chapter 11. Sorting, Grouping, and Set Operations Sort Operations     Optimal, One-Pass and Multi-Pass Sorts     Measuring Sort Activity     Tracing Sort Activity     Using an Index to Avoid a Sort Grouping and Aggregates     Aggregate Operations     Maximums and Minimums     The “Top N” Query     Counting the Rows in a Table     GROUP BY Operations     HAVING Versus WHERE SET Operations     UNION Versus UNION ALL     INTERSECT     MINUS     SET Operations and Their Alternatives Summary Chapter 12. Using and Tuning PL/SQL Performance Advantages of PL/SQL     A Procedural Approach     Reduction in Network Overhead     Divide and Conquer Massive SQLs Measuring PL/SQL Performance     Measuring PL/SQL Overhead     Using DBMS_PROFILER     The 11g Hierarchical Profiler     Data Access Optimization     Array Processing and BULK COLLECT     Array Processing for INSERT Statements     Bind Variables and Dynamic SQL PL/SQL Code Optimization     Tune the SQL First     PLSQL_OPTIMIZE_LEVEL     LOOP Optimization     “Short Circuiting” Expressions     Order of Expressions in IF and CASE Statements     Recursion     The NOCOPY Clause     Associative Arrays Other Optimizations     Native Compilation     PL/SQL In-Lining     Data Types     Using Java for Computation     Function Caching DML Trigger Performance     UPDATE OF and WHEN Clauses     Before and After Row Triggers Summary Chapter 13. Parallel SQL Understanding Parallel SQL     Parallel Processes and the Degree of Parallelism     Parallel Slave Pool     Parallel Query IO     Parallel Performance Gains Deciding When to Use Parallel Processing     Your Server Computer Has Multiple CPUs     The Data to Be Accessed Is on Multiple Disk Drives     The SQL to Be Parallelized is Long Running or Resource-Intensive     The SQL Performs at Least One Full Table, Index, or Partition Scan     There Is Spare Capacity on Your Host     The SQL is Well Tuned Configuring Parallel Processing     Determining the Degree of Parallelism     Parallel Hints     Parallel Configuration Parameters Monitoring Parallel SQL     Parallel Explain Plans     Tracing Parallel Execution     The V$PQ_TQSTAT View     Other Statistics Optimizing Parallel Performance     Start with a SQL That Is Optimized for Serial Execution     Ensure That the SQL Is a Suitable SQL for Parallel Execution     Ensure That the System Is Suitably Configured for Parallel Execution     Make Sure that All Parts of the Execution Plan Are Parallelized     Ensure That the Requested DOP Is Realistic     Monitor the Actual DOP     Check for Skew in Data and Skew in Workload Between Processes Other Parallel Topics     Parallel Execution in RAC     Parallel Index Lookups     Parallel DML     Parallel DDL Summary Chapter 14. DML Tuning DML Performance Fundamentals     WHERE Clause Optimization     Index Overhead     Trigger Overhead     Referential Integrity INSERT Specific Optimizations     Array Processing     Direct Path Inserts     Multi-Table Insert     Manual Segment Storage Management (MSSM) and Freelists     Parallel DML DELETE Operations     TRUNCATE     Partitions     Create Table as Select UPDATE and MERGE Operations     Correlated UPDATEs     Optimizing MERGE COMMIT Optimization     COMMIT Frequency     Batch and NOWAIT Commit     NOLOGGING Summary Part IV: Minimizing Contention Chapter 15. Lock Contention Lock Types and Modes Waiting for Locks Monitoring and Analyzing Locks     Lock Wait Statistics     Finding the Responsible SQL     Measuring Lock Contention for Specific Transactions     Tracing Lock Activity     Blockers and Waiters Application Locking Strategies When Row Level Locking Fails     Unindexed Foreign Keys     ITL Waits     Bitmap Indexes     Direct Path Inserts System Locks     The High Water Mark (HW) Enqueue     The Space Transaction (ST) Enqueue     The Sequence Cache (SQ) Enqueue     The User Lock (UL) Enqueue     Other System Locks Summary Chapter 16. Latch and Mutex Contention Overview of Latch and Mutex Architecture     Gets, Spins, and Sleeps     Mutexes Measuring and Diagnosing Latch/Mutex Contention     Identifying Individual Latches     Finding SQLs and Segments Associated with Latch Waits Specific Latch/Mutex Scenarios     Library Cache Mutex Waits     Library Cache Pin     Shared Pool Latch     Cache Buffers Chains Latch     Row Cache Objects Latch     Other Latch Scenarios Is Latch Contention Inevitable?     What About Changing _SPIN_COUNT?     Spin Count, Latch Contention, and Throughput     Setting Spin Count for Individual Latches Summary Chapter 17. Shared Memory Contention Buffer Cache Architecture Free Buffer Waits     DBWR Direct and Asynchronous IO     Other Remedies for Free Buffer Waits Recovery Writer (RVWR) Waits     Improving Flashback Log IO     Increasing the Size of the     Flashback Log Buffer Buffer Busy Waits     Measuring Buffer Busy     Traditional Causes of Buffer Busy Waits     Buffer Busy and Hot Blocks Redo Log Buffer Waits Summary Part V: Optimizing Memory Chapter 18. Buffer Cache Tuning Buffer Cache Principles     The LRU List     Table Scan Handling     The CACHE Property     Direct Path IO Buffer Cache Configuration and Tuning     Monitoring the Buffer Cache     The Buffer Cache Hit Rate     Multiple Buffer Caches     Sizing the Buffer Cache Automatic Shared Memory Management (ASMM)     Implementing ASMM     Monitoring Resize Operations     Tuning ASMM     Nondefault Pools     Memory Thrashing Summary Chapter 19. Optimizing PGA Memory IO and PGA Memory PGA Memory Management     PGA_AGGREGATE_TARGET     Session PGA Limits Measuring PGA Usage and Efficiency     Session PGA Utilization     Measuring Temporary IO Wait Time     Measuring Work Area Activity Sizing the PGA with V$PGA_TARGET_ADVICE Over-Riding PGA Aggregate Target Summary Chapter 20. Other Memory Management Topics Optimizing Overall Oracle Memory     IO Wait Times and Memory Optimization     Using Advisories to Distribute PGA/Buffer Cache Memory     Oracle 11G Automatic Memory Management (AMM) Result Set Cache     Enabling and Configuring the Result Set Cache     Result Cache Statistics     Result Cache Dependencies     Result Cache Latches     PL/SQL Function Cache Other Memory Optimizations     Sizing the Shared Pool     Large Pool Sizing     Redo Log Buffer     Locking the SGA Summary Part VI: IO Tuning and Clustering Chapter 21. Disk IO Tuning Fundamentals Disk IO Concepts     Service Time and Throughput     Queuing     Disk Drives: Slow and Getting Slower     Disk Capacity and Data Placement Oracle IO Architecture     Datafile Single Block Read     Multi Block Read     Direct Path Reads     Temporary Direct Path IO     Data File Write IO     Direct Path Writes     Redo Log IO     Archive Log IO     Flashback IO     Control File IO Measuring and Monitoring Oracle IO     IO Wait Times     Monitoring Datafile IO     Calibrating IO Optimizing Datafile IO     Minimizing IO Latency     Maximizing IO Throughput     Striping Strategies     RAID Arrays     Isolating Datafile IO Redo and Archive Optimization     Alternating and Distributing Logs     Redo and Archive Fine-Grained Striping     Just Say NO to RAID5 for Redo!     Redo Log Sizing Flashback Logs Summary Chapter 22. Advanced IO Techniques Automatic Storage Management (ASM)     ASM Architecture     ASM Monitoring     ASM Tuning Solid State Disk (SSD)     Flash-Based SSD     DDR RAM-Based SSD     Hybrid SSD     Using SSD for Oracle Databases The Exadata Storage Server Database Block Size Summary Chapter 23. Optimizing RAC RAC Overview     Global Cache Requests     RAC Tuning Principles     Single Instance Tuning and RAC   Measuring Cluster Overhead Reducing Global Cache Latency     Measuring Global Cache Latency     Examining the Interconnect     Signs of Interconnect Problems Optimizing the Interconnect     Network Hardware and Protocols     Ethernet Jumbo Frames     UDP Buffer Size     LMS Waits Cluster Balance     Assessing Cluster Balance     Cluster Balance and Services     RAC Load Balancing Facilities Minimizing Global Cache Requests     Causes of High Global Cache Request Rates     Measuring Global Cache Request Rates     Techniques for Reducing Global Cache Requests Summary Bibliography