Oracle Performance Tuning, 2nd Edition

Oracle Performance Tuning, 2nd Edition

by Mark Gurry, Peter Corrigan
Released November 1996
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596515676

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

The books in O'Reilly's Oracle series are authoritative -- they tell the whole story about complex topics, ranging from performance tuning to the use of packages in PL/SQL to new technologies like Power Objects. And they're independent; they're alternatives for readers who need to know how products and features really work. They're packed with real-world advice and techniques from practitioners in the field, and they come with disks containing code you can use immediately in your own applications. O'Reilly is the alternative for Oracle people who need to solve problems -- and solve them now.Performance tuning is crucial in any modern relational database management system. Too many organizations respond to Oracle performance problems by throwing money at these problems -- by buying larger and more expensive computers or by hiring expert consultants. But there's a lot you can do on your own to increase dramatically the performance of your existing system. Whatever version of Oracle you're running -- from Version 6 to Oracle8, proper tuning can save your organization a huge amount of money in additional equipment, extra memory, and hardware upgrades.The first edition of Oracle Performance Tuning became a classic for programmers, managers, database administrators, system administrators, and anyone who cares about improving the performance of an Oracle system. This second edition contains 400 pages of updated material updating on Oracle features, incorporating advice about disk striping and mirroring, RAID, client-server, distributed databases, MPPS, SMPs, and other architectures. It also includes chapters on parallel server, parallel query, backup and recovery, the Oracle Performance Pack, and more.

Table of contents

  1. Table of Contents
    1. Why Tuning? 5
    2. Who Tunes? 8
    3. When Do You Tune? 9
    4. A Look at Recent Versions 17
    5. How Much Tuning Is Enough? 24
    6. Problems with Design and Development 27
    7. Problems with System Resources 30
    8. Memory Problems and Tuning 33
    9. Disk I/O Problems and Tuning 35
    10. CPU Problems and Tuning 38
    11. Network Problems and Tuning 40
    12. Managing the Problem of Response Time 44
    13. Managing the Problem of Long-Running Jobs 49
    14. Managing the Workload in Your System 53
    15. Making the Decision to Buy More Equipment 55
    16. Management Checkpoints 57
    17. Performance Hints for Managers 61
    18. Selecting a Common Design Methodology 64
    19. Selecting Your Software 64
    20. Selecting Your Hardware 65
    21. Setting Up Screen and Report Templates 68
    22. Using Modular Programming Techniques 71
    23. Defining System Libraries 71
    24. Enforcing Program Version Control 73
    25. Establishing Documentation Standards 73
    26. Establishing Database Environment Standards 74
    27. Security Standards 76
    28. Performance Standards 77
    29. Common Design Problems 83
    30. Choosing an Architecture 85
    31. Tuning Your Data Model 88
    32. Tuning Indexes 98
    33. Testing the Data Model for Performance 103
    34. Denormalizing a Database 106
    35. Constraints 117
    36. Triggers 119
    37. Packages, Procedures, and Functions 121
    38. Designing a Very Large Database Application 121
    39. Miscellaneous Design Considerations 130
    40. SQL Standards 134
    41. The SQL Optimizer 138
    42. SQL Tuning 148
    43. Common Sense in SQL 163
    44. SQL Performance Tips and Hints 173
    45. Using Indexes to Improve Performance 183
    46. SQL Tuning Alternatives 191
    47. Identifying Poor SQL Statements 201
    48. Adjusting SQL Statements Over Time 203
    49. PL/SQL and SQL 206
    50. What Does PL/SQL Offer? 206
    51. PL/SQL Limitations 213
    52. PL/SQL Coding Standards 218
    53. Tuning PL/SQL 221
    54. Exploiting the Power of PL/SQL 242
    55. Oracle PL/SQL Function Extensions 260
    56. What Is Locking? 269
    57. Releasing Locks 272
    58. Avoiding the Dreaded Deadlock 274
    59. Locking Considerations for Oracle Features 276
    60. Overriding the Default Locking Strategy 284
    61. Internal Lock Contention 289
    62. Lock Detection Scripts 292
    63. Steps in Setting Up a Database 301
    64. Tuning Memory 302
    65. Tuning Disk I/O 309
    66. Creating the Database 317
    67. Creating the Tablespaces 320
    68. Creating Rollback Segments 337
    69. Creating Tables 344
    70. Creating Indexes 351
    71. Creating Views 356
    72. Creating Users 357
    73. INIT.ORA Parameter Summary 358
    74. Creating Very Large Databases 379
    75. MONITOR: Monitoring System Activity Tables 387
    76. SQL_TRACE: Writing a Trace File 388
    77. TKPROF: Interpreting the Trace File 391
    78. EXPLAIN PLAN: Explaining the Optimizer’s Plan 397
    79. ORADBX: Listing Events 406
    80. ANALYZE: Validating and Computing Statistics 408
    81. UTLBSTAT.sql and UTLESTAT.sql: Taking Snapshots 410
    82. Other Oracle Scripts 424
    83. Some Scripts of Our Own 427
    84. Oracle Performance Manager 442
    85. Identifying Untuned Application Code 444
    86. Tuning Memory 453
    87. Tuning Disk I/O 479
    88. Avoiding Contention 497
    89. Introduction to Parallel Server 521
    90. Parallel Server Architecture 523
    91. Parallel Server Locking 525
    92. Parallel Server Design 529
    93. Parallel Server Database 537
    94. Tuning Instances 540
    95. INIT.ORA Parameters 541
    96. Ongoing Tuning 545
    97. Introduction to Parallel Query 551
    98. PQO Design Considerations 556
    99. Constructing Your Database for PQO 560
    100. INIT.ORA Parameters 564
    101. Ongoing Tuning of Query Servers 570
    102. Creating Indexes in Parallel 574
    103. Using PQO to Speed Data Loads 575
    104. Performing Parallel Recovery 576
    105. The DBA’s Responsibility 579
    106. Backing Up the Database 580
    107. Preparing to Recover the Database 600
    108. Recovering the Database 628
    109. Correctly Sizing Tables and Indexes 650
    110. Exploiting Array Processing 653
    111. Optimizing INIT.ORA Parameters 656
    112. Disk Tuning 660
    113. Running Jobs in Parallel 663
    114. DBA Tuning for Long-Running Jobs 665
    115. Creating Overnight Extract Tables 668
    116. Index Operations 668
    117. Using PL/SQL to Speed Up Updates 671
    118. Inline Functions 672
    119. Minimizing the Number of Updates 677
    120. Tuning EXPORT and IMPORT Utilities 677
    121. What Is Client-Server? 684
    122. Network Topology 686
    123. Where Should You Put the Network Hardware? 689
    124. Client-Server Performance Issues 692
    125. Tuning Precompilers for Client-Server 706
    126. Tuning the Network Itself 707
    127. Tuning SQL*Net 708
    128. Client-Server: Adapting It in the Real World 716
    129. About Capacity Planning 722
    130. What Do You Need to Test? 723
    131. Capacity Planning Checklist 727
    132. UNIX-Specific Tuning 745
    133. VMS-Specific Tuning 761
    134. Oracle7.0 Features 773
    135. Oracle7.1 Features 775
    136. Oracle7.2 Features 776
    137. Oracle7.3 Features 777
    138. Questions from Planners and Managers 781
    139. Questions from Analysts and Designers 786
    140. Questions from Programmers 793
    141. Questions from Database Administrators 808
    142. Questions from System Administrators 848
    143. Introduction to Financials Tuning 851
    144. Installing Oracle Financials 853
    145. Database-Level Tuning 857
    146. Upgrading Oracle Financials 859
    147. Concurrent Request Processing 860
    148. Archiving and Purging 865
    149. The GL Optimizer 868
    150. Developer Utilities 868
    151. Financials Tips 869
    152. Resources for Financials Developers 871
    153. Oracle Performance Manager 876
    154. Oracle Lock Manager 879
    155. Oracle Topsessions 880
    156. Oracle Tablespace Manager 882
    157. Oracle Expert and Oracle Trace 884
    158. GUI Tips 899
    159. General Tips 900
    160. Case Study Database 1 903
    161. Case Study Database 2 916
  2. Preface (1/2)
  3. Preface (2/2)
  4. I
  5. Introduction to Oracle Performance Tuning
    1. Why Tuning?
      1. Financial Benefits of Tuning
      2. Human Benefits of Tuning
    2. Who Tunes?
    3. When Do You Tune?
      1. Planning the System
      2. Designing and Analyzing the Application
      3. Developing the Application
      4. Testing and Assuring Quality in the System
      5. Monitoring Performance During Production
    4. A Look at Recent Versions
      1. Oracle7.1 Performance Features
      2. Oracle7.2 Performance Features
      3. Oracle7.3 Performance Features
      4. Oracle8 Performance Improvements
    5. How Much Tuning Is Enough?
  6. What Causes Performance Problems?
    1. Problems with Design and Development
      1. Design
      2. Programs
      3. Database
      4. Systems
    2. Problems with System Resources
    3. Memory Problems and Tuning
    4. Disk I/O Problems and Tuning
    5. CPU Problems and Tuning
    6. Network Problems and Tuning
  7. II
  8. Planning and Managing the Tuning Process
    1. Managing the Problem of Response Time
      1. Planning for Complaints About Response Time
      2. Investigating Complaints About Response Time
      3. Considering a Tuning Service Agreement
        1. A. Inclusions and Exclusions
        2. A.1 The tuning service agreement includes the following:
        3. A.2 The tuning service agreement excludes the following:
        4. B. Requirements
        5. B.1 Achieving necessary throughput to keep the company operating
        6. B.2 User Satisfaction
        7. C. Regular Response Time Reporting
        8. D. Response Time Requirements
        9. D.1 Scenario 1
        10. D.2 Scenario 2
    2. Managing the Problem of Long-Running Jobs
    3. Managing the Workload in Your System
    4. Making the Decision to Buy More Equipment
    5. Management Checkpoints
    6. Performance Hints for Managers
  9. Defining System Standards
    1. Selecting a Common Design Methodology
    2. Selecting Your Software
    3. Selecting Your Hardware
      1. Hardware Type
      2. Hardware Configuration
    4. Setting Up Screen and Report Templates
    5. Using Modular Programming Techniques
    6. Defining System Libraries
    7. Enforcing Program Version Control
    8. Establishing Documentation Standards
    9. Establishing Database Environment Standards
    10. Security Standards
    11. Performance Standards
  10. III
  11. Designing for Performance
    1. Common Design Problems
    2. Choosing an Architecture
      1. Unitary
      2. Client-Server
      3. Parallel Server
      4. Distributed Database
      5. Multithreaded Server
    3. Tuning Your Data Model
      1. Database Definitions: A Quick Review
      2. Normalizing a Database
        1. Defining entities
        2. Defining relationships
        3. Defining attributes
    4. Tuning Indexes
      1. What Is an Index?
      2. An Indexing Checklist
        1. Should I index the primary key of a table?
        2. Should I index the foreign keys of a table?
        3. Should I index composite columns of a table?
      3. How Many Indexes?
      4. Using Indexes to Improve Performance
    5. Testing the Data Model for Performance
      1. Requirements for Tuning the Physical Data Model
      2. Program Performance Checking
      3. Tell-Tale Signs of an Untuned Data Model
    6. Denormalizing a Database
      1. Including Children in the Parent Record
      2. Storing the Most Recent Child Data in the Parent Record
      3. Hard-Coding Static Data
      4. Storing Running Totals
      5. Using System-Assigned Keys as Primary Keys
      6. Combining Reference or Code Tables
      7. Creating Extract Tables
      8. Duplicating a Key Beyond an Immediate Child Record
    7. Constraints
      1. Primary Key Constraints
      2. Unique Key Constraints
      3. Foreign Key Constraints
      4. Check Constraints
    8. Triggers
    9. Packages, Procedures, and Functions
    10. Designing a Very Large Database Application
      1. Improving Extract and Load Performance
        1. Case study
        2. Extract and load design considerations
      2. Improving Query Performance
      3. Parallel Query
      4. Developing a Purging Strategy
    11. Miscellaneous Design Considerations
  12. IV
  13. Tuning SQL
    1. SQL Standards
      1. Why Have SQL Standards?
      2. SQL Statement Structure
      3. Encourage Bind Variables
      4. Using Table Aliases
    2. The SQL Optimizer
      1. Rule-Based Optimizer
      2. Cost-Based Optimizer (1/2)
      3. Cost-Based Optimizer (2/2)
        1. ANALYZE command
        2. What statistics are calculated?
        3. Inner workings of the cost-based optimizer
        4. EXPLAIN PLAN extensions for the cost-based optimizer
    3. SQL Tuning
      1. Some Preliminary SQL Questions
      2. Rule-Based Optimizer Tuning (1/2)
      3. Rule-Based Optimizer Tuning (2/2)
        1. Selecting the most efficient table name sequence
        2. The driving table
        3. Joining three or more tables
        4. Efficient WHERE clause sequencing
      4. Cost-Based Optimizer Tuning (1/2)
      5. Cost-Based Optimizer Tuning (2/2)
        1. Cost-based optimizer hints
        2. When are hints ignored?
      6. Cost-Based Versus Rule-Based Optimizers
    4. Common Sense in SQL
      1. Using Efficient Nonindex WHERE Clause Sequencing
      2. Using ROWID When Possible
      3. Reducing the Number of Trips to the Database
      4. Using Null Values
      5. Using DECODE
        1. Using DECODE to reduce processing
        2. Using DECODE in ORDER BY and GROUP BY clauses
      6. Table Alias Shortcuts
      7. Beware of the WHEREs
    5. SQL Performance Tips and Hints
      1. Combining Simple, Unrelated Database Accesses
      2. Deleting Duplicate Records
      3. Counting Rows from Tables
      4. Using WHERE in Place of HAVING
      5. Tuning Views
      6. Minimizing Table Lookups in a Query
      7. Consider Table Joins in Place of EXISTS
      8. Consider EXISTS in Place of Table Joins
      9. Consider EXISTS in Place of DISTINCT
      10. Consider NOT EXISTS in Place of NOT IN
      11. Consider UNION ALL in Place of UNION
      12. Consider IN or UNION in Place of OR
    6. Using Indexes to Improve Performance
      1. Which Is Faster: Indexed Retrieval or a Full Table Scan?
      2. Explicitly Disabling an Index
      3. Avoiding Calculations on Indexed Columns
      4. Adding Additional Columns to the Indexes
      5. Avoiding NOT on Indexed Columns
      6. Avoiding Null in Indexes
      7. Problems in Casting Index Column Types
    7. SQL Tuning Alternatives
      1. Using PL/SQL
      2. Inline SQL Functions
        1. Many-to-many table joins
        2. Beating the outer-join limitations
        3. Reducing very large table join I/O
      3. Full Table Scans via Parallel Query
      4. Discrete Transactions
      5. Using Null to Flag Record Subsets
      6. Using WHERE Instead of an ORDER BY
        1. Ordering via the WHERE clause
        2. Ordering via inline hints
    8. Identifying Poor SQL Statements
    9. Adjusting SQL Statements Over Time
  14. Tuning PL/SQL
    1. PL/SQL and SQL
    2. What Does PL/SQL Offer?
      1. Full ANSI/ISO-Standard SQL Support
      2. Portability
      3. PL/SQL in Packages, Procedures, and Functions
      4. PL/SQL in Triggers
      5. Client-Server and Server-Server Remote Procedure Support
      6. Enhanced Variable Types, Records, and Constants
      7. Bind Variables
      8. Recursive Subcalls
      9. Error Handling
    3. PL/SQL Limitations
      1. Block Size
      2. Interpreted Language
      3. Poor Debugging Facilities
      4. No Direct Support Of DDL or DCL
      5. No Direct Support of Screen or Printer I/O
      6. Limited LONG Support
      7. Proprietary Language
      8. Annoying PL/SQL Behavior
        1. Problems with duplicates
        2. Problems with GOTO
        3. PL/SQL version inconsistencies
    4. PL/SQL Coding Standards
      1. Conventional Programming Standards
      2. PL/SQL-Specific Programming Standards
    5. Tuning PL/SQL
      1. Exploiting SQL
      2. Using Explicit and Implicit Cursors in PL/SQL
      3. Removing Unnecessary SQL Overheads
      4. Database Triggers (1/2)
      5. Database Triggers (2/2)
        1. Trigger advantages
        2. Trigger disadvantages
        3. Tuning database triggers
      6. Stored Procedures and Functions
        1. Procedure and function advantages
        2. Procedure and function disadvantages
        3. Tuning procedures and functions
      7. Stored Packages
        1. Package advantages
        2. Package disadvantages
        3. Tuning packages
    6. Exploiting the Power of PL/SQL
      1. Expanding the Functionality of Other Tools
      2. Using PL/SQL to Speed Up Updates
      3. Exploiting Stored Packages, Procedures, and Functions (1/3)
      4. Exploiting Stored Packages, Procedures, and Functions (2/3)
      5. Exploiting Stored Packages, Procedures, and Functions (3/3)
        1. Exploiting package variables
        2. Exploiting the package initialization section
        3. Exploiting package variable constants
        4. Avoiding passing large parameters
        5. Using SQL inline functions
        6. Pinning objects into memory
        7. Using dynamic version control
        8. Getting schema flexibility via packages
        9. Using PL/SQL wrappers
      6. Cursor Variables
    7. Oracle PL/SQL Function Extensions
      1. DBMS_OUTPUT: dbmsotpt.sql
      2. DBMS_SHARED_POOL: dbmspool.sql
      3. DBMS_TRANSACTION: dbmsutil.sql
      4. DBMS_SQL: dbmssql.sql
      5. DBMS_PIPE: dbmspipe.sql
      6. DBMS_ALERT: dbmsalrt.sql
      7. DBMS_SESSION: dbsmutil.sql
      8. DBMS_DDL: dbmsutil.sql
      9. DBMS_UTILITY: dbmsutil.sql
      10. DBMS_JOB: dbms_job.sql, catjobq.sql
      11. DBMS_APPLICATION_INFO: dbmsutl.sql
  15. Selecting a Locking Strategy
    1. What Is Locking?
      1. Row-Level Locking
      2. Table-Level Locking
    2. Releasing Locks
      1. SQL*Net Dead Connection Identification
      2. Resource Profiles
    3. Avoiding the Dreaded Deadlock
      1. Handling Deadlocks Explicitly
      2. Establishing a Locking Sequence
    4. Locking Considerations for Oracle Features
      1. Process Priorities
      2. Referential Integrity
      3. Parallel Server Locking
      4. Stored Database Triggers
      5. Mutating Database Triggers
      6. DELETE CASCADE
      7. TRUNCATE Table
      8. Rollback Segment Selection
      9. Distributed Databases
    5. Overriding the Default Locking Strategy
      1. SELECT...FOR UPDATE: Locking a Row Explicitly
      2. LOCK TABLE: Locking a Table Explicitly
      3. Using Pseudo-Code to Lock a Large Table
    6. Internal Lock Contention
      1. Rollback Segment Contention
      2. Rollback Shrinkage Contention
      3. Redo Log Buffer Contention
      4. Latch Spin Count
      5. Free List Contention
    7. Lock Detection Scripts
      1. who.sql
      2. table_locks.sql
      3. rollback_locks.sql
      4. waiters.sql
      5. what.sql
  16. V
  17. Tuning a New Database
    1. Steps in Setting Up a Database
    2. Tuning Memory
      1. Memory Components
        1. Buffer cache
        2. Log buffer
        3. Shared buffer pool
        4. Private SQL and PL/SQL areas
      2. Memory Checklist
      3. Setting INIT.ORA Memory Parameters
      4. Pinning Objects in the Shared Pool
    3. Tuning Disk I/O
      1. Disk I/O Checklist
      2. Setting Disk Storage Parameters
    4. Creating the Database
      1. How Many Data Files Should You Create?
      2. What Should You Name Your Data File?
      3. What Size Should You Make the Data File?
      4. How Many Redo Logs and What Size?
    5. Creating the Tablespaces
      1. System Tablespace
      2. Table Tablespaces
      3. Index Tablespaces
      4. Temporary Tablespace
      5. User Tablespaces
      6. Rollback Segment Tablespace
      7. Products Tablespace
      8. Large Object Tablespaces
      9. LOB Tablespaces and External Files
      10. Number of Database Files per Tablespace
      11. Striping Your Data Files
        1. Operating system striping and mirroring
        2. General RAID recommendations
        3. Oracle striping
      12. Coalescing Tablespaces and Deallocating Unused Space
      13. Setting Default Storage for Tablespaces
        1. Setting default storage parameters
        2. Setting PCTINCREASE
    6. Creating Rollback Segments
      1. How Many Rollback Segments?
      2. Setting Rollback Segment Size
      3. Rollback Checklist
    7. Creating Tables
      1. Specifying the Tablespace
      2. Calculating the Size of the Table
        1. Determining INITIAL
        2. Determining PCTFREE
        3. Determining PCTUSED
        4. Determining INITRANS and MAXTRANS
        5. Determining FREELISTS
      3. Understanding the High-Water Mark
      4. Caching Tables
      5. Reducing Dynamic Extension
    8. Creating Indexes
      1. Specifying the Index Tablespace
      2. Calculating the Size of an Index
      3. Analyzing Tables and Indexes
    9. Creating Views
    10. Creating Users
    11. INIT.ORA Parameter Summary (1/5)
    12. INIT.ORA Parameter Summary (2/5)
    13. INIT.ORA Parameter Summary (3/5)
    14. INIT.ORA Parameter Summary (4/5)
    15. INIT.ORA Parameter Summary (5/5)
    16. Creating Very Large Databases
      1. Loading Data
      2. Tuning After Creation
      3. Purging Data
  18. Diagnostic and Tuning Tools
    1. MONITOR: Monitoring System Activity Tables
    2. SQL_TRACE: Writing a Trace File
      1. Turning on SQL_TRACE for a Running Session
    3. TKPROF: Interpreting the Trace File
      1. Take Care in Using TKPROF
    4. EXPLAIN PLAN: Explaining the Optimizer’s Plan
      1. EXPLAIN PLAN Table Definition
      2. Interpreting EXPLAIN PLAN Output
        1. Primary or UNIQUE key index lookup
        2. Non-UNIQUE index lookup
        3. Index-only lookup
        4. ORDER BY without using an index to order the data
        5. Full table scan
        6. Sort merge
        7. Nested loop
      3. Running EXPLAIN PLAN Against Problem Statements
    5. ORADBX: Listing Events
    6. ANALYZE: Validating and Computing Statistics
    7. UTLBSTAT.sql and UTLESTAT.sql: Taking Snapshots
      1. Statistics Definitions
      2. Tuning the Buffer Cache Hit Ratio
      3. Tuning Buffer Busy Wait Ratio
      4. Tuning the DBWR
      5. Tuning Table Access Method
      6. Tuning I/O Spread
      7. Reducing Contention for Internal Latches
      8. Reducing Rollback-Related Transactions
      9. System Waits
    8. Other Oracle Scripts
      1. ONEIDXS.sql: Testing an Individual Index
      2. CATBLOCK.sql and UTLOCKT.sql: Showing Locking Information
      3. DBMSLOCK.sql: Requesting, Converting, and Releasing Locks
      4. SYNC.sql: Synchronizing Processes
    9. Some Scripts of Our Own
      1. What Version of Oracle?
      2. What Are the INIT.ORA Settings?
      3. Looking Inside the SGA
      4. Identifying Database Extents
      5. Performing Database Table Sizing
        1. Looking for tablespace space shortages
        2. Looking for tablespace fragmentation
        3. Looking at space use by individual tables
        4. Looking at the average number of records per block
        5. Putting it together
      6. Checking Extent Sizes and PCTINCREASE
      7. Looking at Objects That Can’t Throw an Extent
      8. Determining Archive Log Disk Location
      9. Which User Is Using the CPU?
      10. Computing the Hit Ratio
      11. Looking at the Dictionary Cache
      12. Looking at Rollback Segment Usage
      13. Finding Foreign Key Relationships
      14. Listing Columns with Inconsistent Data Types or Lengths
      15. Listing Tables That Are Cached
      16. Listing Invalid Objects
      17. Listing All Triggers
      18. Doing Latch Analysis
      19. Checking the Number of Objects
    10. Oracle Performance Manager
  19. Monitoring and Tuning an Existing Database
    1. Identifying Untuned Application Code
      1. Tuning Using V$SQLAREA
        1. Common SQL problems
        2. Checking the buffer cache
        3. Checking for tables without indexes
        4. Listing tables with many indexes
        5. Checking index columns
        6. Picking the optimizer
      2. Tuning Using SQL_TRACE
      3. Tuning Using ORADBX
      4. Identifying Users Running Untuned SQL
    2. Tuning Memory
      1. System Global Area (SGA)
      2. System-Specific Monitoring and Tuning Tools
      3. Tuning the Shared Pool (1/3)
      4. Tuning the Shared Pool (2/3)
      5. Tuning the Shared Pool (3/3)
        1. Tuning the library cache
        2. Looking at the V$LIBRARYCACHE table
        3. Looking at the V$SQLAREA table
        4. Pinning objects in the shared pool
        5. Splitting the shared pool
        6. Tuning cursor usage
        7. Tuning the dictionary cache
        8. Tuning session data
      6. Tuning the Buffer Cache (1/2)
      7. Tuning the Buffer Cache (2/2)
        1. Looking at SQL*DBA MONITOR I/O
        2. Testing INIT.ORA parameters for the effect of increasing buffer cache
        3. Testing INIT.ORA parameters for the effect of decreasing buffer cache
        4. Using UTLBSTAT/UTLESTAT for testing
        5. Looking at the V$SYSSTAT table
        6. Looking inside the buffer cache
      8. Sharing Executable Images
    3. Tuning Disk I/O
      1. System Monitoring and Tuning Tools for Disk I/O
      2. Using the MONITOR Function to Monitor Disk Activity
      3. Looking at Disk I/Os per Disk File
        1. Investigating full table scans
      4. Reducing Disk I/O by Increasing the Sort Area
      5. Reducing Dynamic Extension (1/2)
      6. Reducing Dynamic Extension (2/2)
        1. Detecting dynamic extension
        2. Dynamic extension on temporary segments
        3. Untuned tablespace settings
        4. Chained and migrated rows
      7. Tuning the Archive Writing Process (ARCH)
    4. Avoiding Contention
      1. Tuning the Database Writer
      2. Tuning Rollback Segments
        1. Large updates
        2. Long-running jobs
      3. Monitoring and Tuning Redo Log Files (1/2)
      4. Monitoring and Tuning Redo Log Files (2/2)
        1. Monitoring for redo buffer space contention
        2. Monitoring for redo log latch contention
      5. Reducing Buffer Cache Latch Contention
      6. Reducing Library Cache Latch Contention
        1. Data block and free list contention
      7. Reducing Multithreaded Server Process Contention
      8. Reducing Locking Problems (1/2)
      9. Reducing Locking Problems (2/2)
        1. Foreign key locking problems
        2. Contention right now
  20. Tuning Parallel Server
    1. Introduction to Parallel Server
    2. Parallel Server Architecture
      1. Tightly Coupled Systems
      2. Loosely Coupled Systems
      3. Massively Parallel Architecture
    3. Parallel Server Locking
      1. PCM Locks
      2. SCN Locks
      3. Table Locks
      4. Transaction Locks
    4. Parallel Server Design
      1. Table Categories
        1. Read-only tables
        2. Tables used for inserts and deletes
      2. Suitable Applications
        1. Batch and OTLP instances
        2. Database partitioned by application
        3. Database partitioned by geographic location
        4. Database partitioned by client
        5. Tables accessed by multiple instances
      3. Other Design Issues
        1. Avoid pinging
        2. Dealing with the same table
        3. Dealing with read consistency
        4. Adding to an existing environment
    5. Parallel Server Database
      1. Data Separation
      2. Data Files
      3. Tablespaces
      4. Tables, Indexes, and Clusters
    6. Tuning Instances
    7. INIT.ORA Parameters
      1. Pinging
      2. False Pinging
      3. Parameter Summary
    8. Ongoing Tuning
      1. CATPARR.sq and Its Views
      2. Using V$SYSSTAT
  21. Tuning Parallel Query
    1. Introduction to Parallel Query
      1. System Architecture
      2. Oracle Features
      3. PQO Architecture and Operation
    2. PQO Design Considerations
      1. Denormalized Summary Tables and/or PQO
      2. Deciding On an Index or PQO
      3. Purging Strategies
      4. Full Table Scans and Indexes
      5. Skew and Striping
    3. Constructing Your Database for PQO
    4. INIT.ORA Parameters
      1. Setting the Degree of Parallelism
    5. Ongoing Tuning of Query Servers
      1. Using the V$PQ_SYSSTAT Table
      2. Using EXPLAIN PLAN to Tune PQO
    6. Creating Indexes in Parallel
    7. Using PQO to Speed Data Loads
    8. Performing Parallel Recovery
  22. Tuning Database Backup and Recovery
    1. The DBA’s Responsibility
    2. Backing Up the Database
      1. EXP: the Export Utility
        1. EXP utility advantages
        2. Export utility disadvantages
        3. Incremental exports
        4. Exporting on SMP/MMP machines
        5. Exporting via compression
        6. Single-task exports for UNIX
        7. Faster export scanning
      2. Image (Cold) Backups
        1. Image backup advantages
        2. Image backup disadvantages
        3. Speeding up image backups
      3. Hot Backups
        1. Hot backup advantages
        2. Hot Backup Disadvantages
        3. Speeding up the hot backup process
      4. Oracle Database Backup Summary
    3. Preparing to Recover the Database
      1. Documenting the Database
      2. DBA Tools and Scripts (1/6)
      3. DBA Tools and Scripts (2/6)
      4. DBA Tools and Scripts (3/6)
      5. DBA Tools and Scripts (4/6)
      6. DBA Tools and Scripts (5/6)
      7. DBA Tools and Scripts (6/6)
        1. Nightly reporting scripts
        2. DBA_maint.sql
        3. Database maintenance scripts
        4. alter_tablespace.sql
    4. Recovering the Database
      1. Hardware Failure Recovery
        1. One spare disk on each production machine
        2. Spare disk capacity on each production machine
        3. Adequate hardware maintenance agreement with vendors
        4. Compatible disaster recovery machine
        5. Never compromise database standards
        6. Publicize disk structure and layouts
      2. Database Corruption Recovery
        1. Human error
        2. Internal object corruption
      3. Recovering Databases Onto a Secondary Machine
        1. What is required?
        2. Partial recovery steps
      4. Desperate Situations Require Desperate Measures
        1. No current control file
        2. Missing archive log(s)
        3. Missing online redo log file(s)
        4. Missing data file(s)
        5. Lack of team experience
        6. Not enough disk space for an alternative recovery
        7. Ask Oracle
      5. Not Enough Time to Back Up Before and After Recovery
        1. Parallel cold backup to disk
        2. Hot backup to disk
  23. VI
  24. Tuning Long-Running Jobs
    1. Correctly Sizing Tables and Indexes
      1. Example
        1. Run 1
        2. Run 2
        3. Run 3
        4. Run 4
      2. Optimizing the PCTFREE and PCTUSED Parameters
    2. Exploiting Array Processing
      1. ARRAYSIZE Parameter
      2. Array SELECT Workarounds
      3. Array UPDATE Workarounds
    3. Optimizing INIT.ORA Parameters
    4. Disk Tuning
      1. Resizing Temporary Tables
      2. Choosing Rollback Segments
      3. Avoiding Row Migration and Chaining
        1. Record chaining
        2. Row migration
    5. Running Jobs in Parallel
    6. DBA Tuning for Long-Running Jobs
      1. Increasing Free Lists
      2. Disabling Archiving
      3. Pinning Objects into the SGA
      4. Disabling Triggers, Constraints, and Referential Integrity
      5. Indexing All Relevant Columns
      6. Table Caching
    7. Creating Overnight Extract Tables
    8. Index Operations
      1. Creating Indexes After Inserts
      2. Parallel Index Creation
      3. Unrecoverable Index Creation
      4. Deferring Primary Key and Unique Constraints
    9. Using PL/SQL to Speed Up Updates
    10. Inline Functions
      1. Speeding Up Queries
      2. Caching Data
    11. Minimizing the Number of Updates
    12. Tuning EXPORT and IMPORT Utilities
      1. Tuning EXPORT
      2. Tuning IMPORT
      3. Alternative Tuning of Exports and Imports
        1. Single-task IMPORT and EXPORT
        2. Import with COMMIT = NO
        3. Huge SORT_AREA_SIZE size
        4. Avoid primary keys and unique constraints
  25. Tuning in the Client-Server Environment
    1. What Is Client-Server?
    2. Network Topology
      1. Star Topology
      2. Ring Topology
      3. Bus Topology
      4. Hybrid Topologies
    3. Where Should You Put the Network Hardware?
      1. Thin, Fat, and Three-Tier Client-Server Applications
      2. Distributed Databases
    4. Client-Server Performance Issues
      1. Reducing Oracle Network Traffic
      2. Tuning ARRAYSIZE
      3. Tuning PL/SQL
      4. Choosing Explicit SELECT Statements
      5. Combining SELECT Statements
      6. SQL*Forms Base Table Views
      7. Referential Integrity and Delete Cascade
      8. Automatic Table Replication/Snapshots
      9. Stored Database Triggers
      10. Stored Database Functions, Procedures, and Packages
      11. Inline Database Functions
      12. Running Long-Running Jobs at the Server End
    5. Tuning Precompilers for Client-Server
    6. Tuning the Network Itself
      1. Enlarging Packet Size
      2. Reducing Network Delays
    7. Tuning SQL*Net
      1. Tuning SQL*Net Version 1
      2. Tuning SQL*Net Version 2
        1. SQL*Net Version 2.0
        2. SQL*Net Version 2.1
        3. SQL*Net Version 2.3
      3. Setting Up SQL*Net Version 2
      4. Setting Up Oracle Name Server
    8. Client-Server: Adapting It in the Real World
      1. A Dedicated “Report” Server
      2. Deferred Transaction Processing
      3. Snapshot Replication Alternatives
      4. Local Cache
  26. Capacity Planning
    1. About Capacity Planning
    2. What Do You Need to Test?
      1. Memory
      2. Disk I/O
      3. CPU
      4. Network
      5. Database
    3. Capacity Planning Checklist
      1. Step 1: Obtaining Background Information
      2. Step 2: Structuring the Database and SGA
      3. Step 3: Tuning the Application in Single-User Mode
      4. Step 4: Tuning the Application in Multiuser Mode
      5. Step 5: Fixing the Bottlenecks You Discover
      6. Step 6: Reporting the Results of the Exercise
  27. Tuning for Specific Systems
    1. UNIX-Specific Tuning
      1. Shared Memory and Semaphore Requirements
      2. Writing to Raw Devices
      3. Latches
        1. Spin locks
        2. Semaphore-based queues
      4. UNIX Commands to Monitor Performance (1/2)
      5. UNIX Commands to Monitor Performance (2/2)
        1. Identifying disk bottlenecks
        2. Identifying memory bottlenecks
        3. Identifying swapping bottlenecks
        4. Identifying CPU bottlenecks
        5. Identifying network bottlenecks
      6. Nonstandard UNIX Functionality
        1. Pinning SGA into memory
        2. Smarter ways to export under UNIX
        3. Single-task Oracle executables
        4. UNIX-specific I/O parameters
        5. _TRACE_FILES_PUBLIC and other undocumented parameters
        6. Keeping all Oracle processes at the same priority
        7. Enlarging your System Global Area
        8. Public domain monitoring tools
    2. VMS-Specific Tuning
      1. VMS Tuning Parameters
      2. VMS Tools to Identify Bottlenecks (1/2)
      3. VMS Tools to Identify Bottlenecks (2/2)
        1. Identifying disk I/O bottlenecks
        2. Identifying memory bottlenecks
        3. Identifying CPU bottlenecks
      4. Other Tuning Hints
  28. VII
  29. Summary of New Features
    1. Oracle7.0 Features
    2. Oracle7.1 Features
    3. Oracle7.2 Features
    4. Oracle7.3 Features
    5. Oracle 8.0 Features
  30. Hot Tuning Tips
    1. Questions from Planners and Managers
    2. Questions from Analysts and Designers (1/2)
    3. Questions from Analysts and Designers (2/2)
    4. Questions from Programmers (1/3)
    5. Questions from Programmers (2/3)
    6. Questions from Programmers (3/3)
    7. Questions from Database Administrators (1/8)
    8. Questions from Database Administrators (2/8)
    9. Questions from Database Administrators (3/8)
    10. Questions from Database Administrators (4/8)
    11. Questions from Database Administrators (5/8)
    12. Questions from Database Administrators (6/8)
    13. Questions from Database Administrators (7/8)
    14. Questions from Database Administrators (8/8)
    15. Questions from System Administrators
  31. Tuning Oracle Financials
    1. Introduction to Financials Tuning
    2. Installing Oracle Financials
      1. Sizing
      2. Character Set
      3. Online Backups
    3. Database-Level Tuning
    4. Upgrading Oracle Financials
    5. Concurrent Request Processing
    6. Archiving and Purging
    7. The GL Optimizer
    8. Developer Utilities
    9. Financials Tips
    10. Resources for Financials Developers
      1. The Oracle Applications Users Group
      2. The Oracle Applications List Server
  32. Oracle Performance Pack
    1. Oracle Performance Manager
    2. Oracle Lock Manager
    3. Oracle Topsessions
    4. Oracle Tablespace Manager
    5. Oracle Expert and Oracle Trace
      1. Oracle Expert (1/2)
      2. Oracle Expert (2/2)
      3. Oracle Trace (1/2)
      4. Oracle Trace (2/2)
  33. Tuning Oracle Forms 4.0 and 4.5
    1. GUI Tips
    2. General Tips
  34. Tuning Case Studies
    1. Case Study Database 1
      1. Observation 1
      2. Observation 2
      3. Observation 3
      4. Observation 4
      5. Observation 5
      6. Observation 6
      7. Observation 7
      8. Observation 8
      9. Observation 9
      10. Observation 10
      11. Observation 11
      12. Observation 12
      13. Observation 13
      14. Observation 14
      15. Observation 15
      16. Observation 16
      17. Observation 17
      18. Observation 18
    2. Case Study Database 2
      1. Observation 1
      2. Observation 2
      3. Observation 3
      4. Observation 4
      5. Observation 5
      6. Observation 6
      7. Observation 7
      8. Observation 8
      9. Observation 9
      10. Observation 10
      11. Observation 11
  35. Dynamic Performance Tables
  36. Index (1/4)
  37. Index (2/4)
  38. Index (3/4)
  39. Index (4/4)

Product information

  • Title: Oracle Performance Tuning, 2nd Edition
  • Author(s): Mark Gurry, Peter Corrigan
  • Release date: November 1996
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596515676