Oracle Database 10g Performance Tuning Tips & Techniques

Book description

"Offers hundreds of hints, tips, and tricks of the trade that can be useful to any DBA wanting to achieve maximum performance of Oracle applications. No Oracle library would be complete without this book." --Ken (Dr. DBA) Jacobs, Vice President of Product Strategy for Server Technologies, Oracle Corporation

"Rich is the first and last stop for Oracle Database technology and performance tuning. His knowledge is a vital tool that you need to successfully negotiate the waters of Oracle database development." --Mike Frey, Principal Architect, Navteq

Table of contents

  1. Cover Page
  2. Oracle Database 10g Performance Tuning Tips & Techniques
  3. Copyright Page
  4. Dedication Page
  5. Contents
  6. Acknowledgments
  7. Introduction
  8. 1 Oracle Database 10g New Features (DBA and Developer)
    1. Installation Improvements
    2. SYSAUX Tablespace
    3. Automatic Storage Management
    4. Cluster Ready Services (CRS)
    5. Server-Generated Alerts
    6. Automatic Workload Repository (AWR)
    7. Automatic Database Diagnostic Monitor (ADDM)
    8. SQL Tuning Advisor
    9. Automatic Shared Memory Management (ASMM)
    10. Flash Recovery Area
    11. Recycle Bin
    12. Recovery Manager Changes
    13. Transparent Data Encryption (10gR2)
    14. LogMiner Changes
    15. New DBMS_STATS Options
    16. Tracing Enhancements
    18. Default (Permanent) Tablespace
    19. Temporary Tablespace Groups
    20. Rename Tablespaces
    21. Bigfile Tablespaces
    22. Shrinking Segments
    23. Data Pump
    24. Cross-Platform Transportable Tablespaces
    25. Write to External Table
    26. Automatic Undo Retention Tuning
    27. V$SESSION Include New Information
    28. OEM Changes
    29. Grid Control
    30. New Background Processes in 10g
    31. Version Comparison Table
    32. New Features Review
    33. References
  9. 2 Basic Index Principles (Beginner Developer and Beginner DBA)
    1. Basic Index Concepts
    2. Concatenated Indexes
    3. Suppressing Indexes
      1. Using the NOT EQUAL Operators '<>', '!='
      2. Using IS NULL or IS NOT NULL
      3. Using Functions
      4. Comparing Mismatched Data Types
    4. Selectivity
    5. The Clustering Factor
    6. The Binary Height
    7. Using Histograms
    8. Fast Full Scans
    9. Skip-Scans
    10. Types of Indexes
      1. B-Tree Indexes
      2. Bitmap Indexes
      3. Hash Indexes
      4. Index-Organized Tables
      5. Reverse Key Indexes
      6. Function-Based Indexes
      7. Partitioned Indexes
      8. Bitmap Join Indexes
    11. Fast Index Rebuilding
    12. Tips Review
    13. References
  10. 3 Disk Implementation Methodology and ASM (DBA)
    1. Disk Arrays: Not a Choice Anymore
      1. Use Disk Arrays to Improve Performance and Availability
      2. How Many Disks Do We Need?
      3. What Are Some of the RAID Levels Available?
      4. The Newer RAID 5
    2. Setup and Maintenance of the Traditional File System
      1. What Is the Cost?
    3. Distributing "Key" Data Files Across Hardware Disks
      1. Storing Data and Index Files in Separate Locations
      2. Avoiding I/O Disk Contention
      3. Moving Data Files to Balance File I/O
    4. Locally Managed Tablespaces
      1. Creating Tablespaces as Locally Managed
      2. Migrating Dictionary-Managed Tablespaces to Locally Managed
      3. Oracle Bigfile Tablespaces
      4. Oracle Managed Files
    5. ASM Introduction
      1. Communication Across IT Roles
      2. ASM Instances
      3. ASM init.ora Parameters
      4. ASM Installation
      5. ASM Parameters and SGA Sizing
      6. ASM and Privileges
      7. ASM Disks
      8. ASM and Multipathing
      9. ASM DiskGroups
      10. ASM Diskgroups and Databases
      11. ASM Redundancy and Failure Groups
      12. New Space-Related Columns in Oracle Database 10g Release 2
      13. Cluster Synchronization Services
      14. Database Instances and ASM
      15. Database Consolidation and Clustering with ASM
      16. Database Processes to Support ASM
      17. Bigfile and ASM
      18. Database Init.ora Parameters to Support ASM
      19. ASM and Database Deployment Best Practices
      20. ASM Storage Management and Allocation
      21. ASM Rebalance and Redistribution
    6. Avoiding Disk Contention by Using Partitions
      1. Getting More Information about Partitions
      2. Other Types of Partitions
      3. Other Partitioning Options
    7. Index Partitioning
    8. Exporting Partitions
    9. Eliminating Fragmentation
      1. Using the Correct Extent Size
      2. Create a New Tablespace and Move the Segments to It
      3. Exporting and Then Re-Importing the Table
      4. To Avoid Chaining, Set Percents Correctly
      5. Rebuilding the Database
    10. Increasing the Log File Size and LOG_CHECKPOINT_INTERVAL for Speed
      1. Determining If Redo Log File Size Is a Problem
      2. Determining the Size of Your Log Files and Checkpoint Interval
    11. Flash Recovery
    12. Increasing Chances of Recovery: Committing after Each Batch
    13. Using Rollback Segments
      1. Avoiding Contention among Rollback Segments
      2. Monitoring Rollback Segment Waits and Contention
      3. Increasing Rollback Segments
      4. Isolating Large Transactions to Their Own Rollback Segments
      5. The Simpler Approach: UNDO Tablespace
      6. Monitoring Undo Space
      7. Killing Problem Sessions
    14. Don't Sort in the SYSTEM or SYSAUX Tablespaces
    15. Have Multiple Control Files on Different Disks and Controllers
    16. Using Raw Devices to Improve I/O for Write-Intensive Data
      1. Reasons for Using Raw Devices
      2. Drawbacks
    17. Other Disk I/O Precautions and Tips
    18. Issues to Consider in the Planning Stages
    19. Tips Review
    20. References
  11. 4 Tuning the Database with Initialization Parameters (DBA)
    1. Identifying Crucial Initialization Parameters
    2. Changing the Initialization Parameters Without a Restart
    3. Viewing the Initialization Parameters with Enterprise Manager
    4. Increasing Performance by Tuning the DB_CACHE_SIZE
      1. Using V$DB_CACHE_ADVICE in tuning DB_CACHE_SIZE
      2. Keeping the Hit Ratio for the Data Cache Above 95 Percent
      3. Monitoring the V$SQLAREA View to Find Bad Queries
    5. Setting DB_BLOCK_SIZE to Reflect the Size of Your Data Reads
      1. Tuning the SHARED_POOL_SIZE for Optimal Performance
      2. Using Oracle Multiple Buffer Pools
      3. Tuning the PGA_AGGREGATE_TARGET for Optimal Use of Memory
      4. Modifying the Size of Your SGA to Avoid Paging and Swapping
      5. Understanding the Cost-Based Optimizer
      6. Creating Enough Dispatchers
      7. 25 Important Initialization Parameters to Consider
      8. Finding Undocumented Initialization Parameters
      9. Understanding the Typical Server
      10. Modeling a Typical Server
      11. Sizing the Oracle Applications Database
    6. Tips Review
    7. References
  12. 5 Enterprise Manager and Grid Control (DBA and Developer)
    1. The Enterprise Manager (EM) Basics
    2. Starting with All Targets and Other Groupings
    3. Policies (Violations) Tab
    4. Monitoring the Database
      1. Database Administration Tab
      2. Database Administration Tab: Tablespaces
      3. Database Administration Tab: Instance Level
      4. Database Administration Tab: All Initialization Parameters
      5. Database Administration Tab: Manage Optimizer Statistics
      6. Database Administration Tab, Instance Level: Resource Manager (Consumer Groups)
      7. Database Maintenance Tab
      8. Database Topology Tab
      9. Database Performance Tab
    5. Monitoring the Hosts
    6. Monitoring the Application Servers
    7. Monitoring the Web Applications
    8. Deployments Tab (Patching Options)
    9. Jobs Tab
    10. Reports Tab
    11. Automatic Storage Management Performance
    12. Summary
    13. Tips Review
    14. References
  13. 6 Using EXPLAIN and STORED OUTLINES (Developer and DBA)
    1. The Oracle SQL TRACE Utility
      1. Simple Steps for SQL TRACE with a Simple Query
      2. The Sections of a TRACE Output
      3. A More Complex TKPROF Output
      4. Digging into the TKPROF Output
      5. Using DBMS_MONITOR (10g New Feature)
      6. TRCSESS Multiple Trace Files into One File (10g New Feature)
      7. Using EXPLAIN PLAN Alone
      8. EXPLAIN PLAN—Read It Top to Bottom or Bottom to Top?
      9. Reading the EXPLAIN PLAN
      10. Using DBMS_XPLAN
      11. Yet Another EXPLAIN PLAN Output Method: Building the Tree Structure
      12. Another Example Using the Tree Approach
      13. Tracing/Explaining Problem Queries in Developer Products
      14. Important Columns in the PLAN_TABLE Table
      15. Helpful Oracle-Supplied Packages
      16. Initialization Parameters for Undocumented TRACE
      17. Using Stored Outlines
      18. Using Plan Stability (Stored Outlines)
    2. Tips Review
    3. References
  14. 7 Basic Hint Syntax (Developer and DBA)
    1. Top Hints Used
      1. Use Hints Sparingly
      2. Fix the Design First
    2. Available Hints and Groupings
      1. Execution Path
      2. Access Methods
      3. Query Transformation Hints
      4. Join Operations
      5. Parallel Execution
      6. Other Hints
    3. Specifying a Hint
    4. Specifying Multiple Hints
    5. When Using an Alias, Hint the Alias, Not the Table
    6. The Hints
      1. The FIRST_ROWS Hint
      2. The ALL_ROWS Hint
      3. The FULL Hint
      4. The INDEX Hint
      5. The NO_INDEX Hint
      6. The INDEX_ JOIN Hint
      7. The INDEX_COMBINE Hint
      8. The INDEX_ASC Hint
      9. The INDEX_DESC Hint
      10. The INDEX_FFS Hint
      11. The ORDERED Hint
      12. The LEADING Hint
      13. The NO_EXPAND Hint
      14. The DRIVING_SITE Hint
      15. The USE_MERGE Hint
      16. The USE_NL Hint
      17. The USE_HASH Hint
      18. The PUSH_SUBQ Hint
      19. The PARALLEL Hint
      20. The NO_PARALLEL Hint
      21. The APPEND Hint
      22. The NOAPPEND Hint
      23. The CACHE Hint
      24. The NOCACHE Hint
      25. The CLUSTER Hint
      26. The HASH Hint
      28. The QB_NAME Hint
    7. Some Miscellaneous Hints and Notes
      1. Notes on Hints and Stored Outlines
    8. Why Isn't My Hint Working?
    9. Hints at a Glance
    10. Tips Review
    11. References
  15. 8 Query Tuning: Developer and Beginner DBA
    1. What Queries Do I Tune? Querying V$SQLAREA
      1. Selecting from the V$SQLAREA View to Find the Worst Queries
      2. Selecting from the V$SQL View to Find the Worst Queries
    2. New 10g Views for Locating Resource-Intensive Sessions and Queries
      1. Selecting from V$SESSMETRIC to Find Current Resource-Intensive Sessions
      2. Viewing Available AWR Snapshots
      3. Selecting from the DBA_HIST_SQLSTAT View to Find the Worst Queries
      4. Selecting Query Text from the dba_hist_sqlstat View
      5. Selecting Query Explain Plan from the DBA_HIST_SQL_PLAN View
    3. When Should an Index Be Used?
    4. What Happens When I Forget the Index?
    5. Creating an Index
    6. Check the Index on a Table
    7. What If I Create a Bad Index?
    8. Caution Should Be Exercised When Dropping Indexes
    9. Indexing the Columns Used in the SELECT and WHERE
    10. The Fast Full Scan
    11. A "Magically" Faster Query
    12. Caching a Table in Memory
    13. Using Multiple Indexes (Use the Most Selective)
    14. The Index Merge
    15. Indexes That Get Suppressed
    16. Function-Based Indexes
    17. The "Curious" OR
    18. The EXISTS Function
    19. That Table Is Actually a View!
    20. SQL and Grand Unified Theory
    21. Tuning Changes in Oracle Database 10g
    22. 10g Automatic SQL Tuning
      1. Ensure That the Tuning User Has Access to the API
      2. Create the Tuning Task
      3. The Task Can Be Seen in the Advisor Log
      4. Execute the SQL Tuning Task
      5. Check Status of the Tuning Task
      6. Displaying the SQL Tuning Advisor Report
      7. Reviewing the Report Output
    23. Tips Review
    24. References
  16. 9 Table Joins and Other Advanced Tuning (Advanced DBA and Developer)
    1. Join Methods
      1. NESTED LOOPS Joins
      2. SORT-MERGE Joins
      3. CLUSTER Joins
      4. HASH Joins
      5. Index Joins
    2. Table Join Initialization Parameters
      1. SORT-MERGE and HASH Join Parameters
    3. A Two-Table Join: Equal-Sized Tables (Cost-Based)
    4. A Two-Table INDEXED Join: Equal-Sized Tables (Cost-Based)
    5. Forcing a Specific Join Method
    6. Eliminating Join Records (Candidate Rows) in Multitable Joins
    7. A Two-Table Join Between a Large and Small Table
    8. Three-Table Joins: Not as Much Fun (Cost-Based)
    9. Bitmap Join Indexes
      1. Bitmap Indexes
    10. Third-Party Product Tuning
    11. Tuning Distributed Queries
    12. When You Have Everything Tuned
    13. Miscellaneous Tuning Snippets
      1. External Tables
      2. Snapshot Too Old: Developer Coding Issue
      3. Set Event to Dump Every Wait
      4. 14 Hours to 30 Seconds with the Exists Operator
    14. Tuning at the Block Level (Advanced)
      1. Key Sections of a Block Dump
    15. Tuning Using Simple Mathematical Techniques
      1. Traditional Mathematical Analysis
      2. Seven-Step Methodology
      3. Pattern Interpretation
      4. Mathematical Techniques Conclusions
    16. Join Tuning: Relational vs. Object-Relational Performance
      1. Models Used
      2. Results
      3. Conclusion
    17. Tips Review
    18. References
  17. 10 Using PL/SQL to Enhance Performance (Developer and DBA)
    1. Use DBMS_APPLICATION_INFO for Real-Time Monitoring
    2. Use a Custom Replacement of DBMS_ APPLICATION_INFO for Real-Time Monitoring in a RAC Environment
    3. Log Timing Information in a Database Table
    4. Reduce PL/SQL Program Unit Iterations and Iteration Time
    5. Use ROWID for Iterative Processing
    6. Standardize on Data Types, IF Statement Order, and PLS_INTEGER
      1. Ensure the Same Data Types in Comparison Operations
      2. Order IF Conditions Based on the Frequency of the Condition
      3. Use the PLS_INTEGER PL/SQL Data Type for Integer Operations
    7. Reduce the Calls to SYSDATE
    8. Reduce the Use of the MOD Function
    9. Shared Pool and Pinning PL/SQL Objects
      1. Pinning (Caching) PL/SQL Object Statements into Memory
      2. Pinning All Packages
    10. Identifying PL/SQL Objects That Need to Be Pinned
    11. Using and Modifying DBMS_SHARED_POOL.SIZES
      1. Finding Large Objects
    12. Get Detailed Object Information from DBA_OBJECT_SIZE
      1. Getting Contiguous Space Currently in the Shared Pool
    13. Finding Invalid Objects
    14. Finding Disabled Triggers
    15. Use PL/SQL Associative Arrays for Fast Reference Table Lookups
    16. Finding and Tuning the SQL When Objects Are Used
    17. The Time Component When Working with DATE Data Types
    18. Tuning and Testing PL/SQL
    19. PL/SQL Object Location Implications
    20. Use Rollback Segments to Open Large Cursors
      1. Use Active Transaction Management to Process Large Quantities of Data
    21. Use Temporary Database Tables for Increased Performance
    22. Integrate a User Tracking Mechanism to Pinpoint Execution Location
    23. Limit the Use of Dynamic SQL
    24. Use Pipelined Table Functions to Build Complex Result Sets
    25. Leave Those Debugging Commands Alone!
    26. The "Look and Feel" Just for the Beginners
      1. PL/SQL Example
      2. Create a Procedure Example
      3. Execute the Procedure from PL/SQL Example
      4. Create a Function Example
      5. Execute the get_cust_name Function from SQL
      6. A Package Example
      7. Database Trigger Example Using PL/SQL
    27. Tips Review
    28. References
  18. 11 Tuning RAC and Using Parallel Features
    1. Real Application Clusters (RAC)
      1. Parallel Databases
      2. Architecture of Oracle RAC
      3. Internal Workings of the Oracle RAC System
      4. SCN Processing
      5. RAC Performance Tuning Overview
      6. RAC Wait Events and Interconnect Statistics
      7. Cluster Interconnect Tuning–Hardware Tier
      8. Tuning RAC Using Enterprise Manager Grid Control
    2. Basic Concepts of Parallel Operations
    3. Parallel DML and DDL Statements and Operations
    4. Parallel DML Statements and Operations Since Oracle 9i
    5. Parallelism and Partitions
    6. Inter- and Intraoperation Parallelization
      1. Examples of Using Inter- and Intraoperations (PARALLEL and NO_PARALLEL Hints)
    7. Creating Table and Index Examples Using Parallel Operations
      1. Real-World Example of Distributing Data for Effective Parallel Operations
    8. Parallel DML Statements and Examples
      1. Parallel DML Restrictions
      2. Parallel DML Statement Examples
    9. Monitoring Parallel Operations via the V$ Views
      1. V$PQ_TQSTAT
      2. V$PQ_SYSSTAT
      3. V$PQ_SESSTAT
    10. Using EXPLAIN PLAN and AUTOTRACE on Parallel Operations
    11. Tuning Parallel Execution and the Oracle Initialization Parameters
    12. Parallel Loading
    13. Performance Comparisons and Monitoring Parallel Operations
    14. Optimizing Parallel Operations in RAC
      1. Objectives of Parallel Operations
      2. RAC Parallel Usage Models
      3. Initialization Parameters
      4. V$ Views for Viewing Parallel Statistics
      5. Parallel Configuration and Associated Baseline Test
      6. Parallel Query Test Examples
      7. Create Table As
      8. Index Builds
      9. Performance Considerations and Summary
    15. Other Parallel Notes
    16. Oracle Documentation Is Online
    17. Tips Review
    18. References
  19. 12 The V$ Views (Developer and DBA)
    1. V$ View Creation and Access
      1. Obtaining a Count and Listing of All V$ Views
      2. Finding the X$ Tables Used to Create the V$ Views
      3. Finding the Underlying Objects That Make Up the DBA_ views
      4. Using Helpful V$ Scripts
      5. Summary of Memory Allocated (V$SGA)
      6. Detail of Memory Allocated (V$SGASTAT)
      7. Finding Initialization Settings in V$PARAMETER
      8. Determining Hit Ratio for Data (V$SYSSTAT)
      9. Determining Hit Ratio for the Data Dictionary (V$ROWCACHE)
      10. Determining Hit Ratio for the Shared SQL and PL/SQL (V$LIBRARYCACHE)
      11. Identifying PL/SQL Objects That Need to Be Kept (Pinned)
      12. Finding Problem Queries by Querying V$SQLAREA
      13. Finding Out What Users Are Doing and Which Resources They Are Using
      14. Finding Out Which Objects a User Is Accessing
      15. Using Indexes
      16. Identifying Locking Issues
      17. Killing the Problem Session
      18. Finding Users with Multiple Sessions
      19. Finding Disk I/O Issues
      20. Finding Rollback Segment Contention
      21. Determining Whether Freelists Are Sufficient
      22. Checking Privileges and Roles
      23. Wait Events V$ Views
      24. Some of the Major V$ View Categories
    2. Tips Review
    3. References
  20. 13 The X$ Tables (Advanced DBA)
    1. Introducing the X$ Tables
      1. Misconceptions about the X$ Tables
      2. Granting Access to View the X$ Tables
    2. Creating V$ Views and X$ Tables
      1. Obtaining a List of the X$ Tables That Make Up the V$ Views
    3. Obtaining a List of All the X$ Tables
    4. Obtaining a List of All the X$ Indexes
    5. Using Hints with X$ Tables and Indexes
    6. Shared Pool
    7. Queries to Monitor the Shared Pool
      1. ORA-04031 Errors
      2. Large Allocations Causing Contention
      3. Shared Pool Fragmentation
      4. Low Free Memory in Shared and Java Pools
      5. Library Cache Hit Ratio
      6. High Number of Hard Parses
      7. Latch Waits and/or Sleeps
      8. Miscellaneous
    8. Redo
    9. Initialization Parameters
      1. Case 1
      2. Case 2
      3. Case 3
      4. Case 4
      5. Case 5
    10. Buffer Cache/Data Blocks
      1. Buffer Statuses
      2. Segments Occupying Block Buffers
      3. Hot Data Blocks/Latch Contention and Wait Events
    11. Instance/Database
    12. Effective X$ Table Use and Strategy
    13. Related Oracle Internals Topics
      1. Traces
      2. DBMS_TRACE Package
      3. Events
      4. Dumps
      5. ORADEBUG
      6. trcsess Utility
    14. Reading the Trace File
      1. Wait Information and Response Time
      2. Recursive Calls
      3. Module Information
      4. Commit
      5. Unmap
      6. Bind Variables
      7. Errors
    15. Some Common X$ Table Groups
    16. Some Common X$ Table and Non-V$ Fixed View Associations
    17. Common X$ Table Joins
      1. New Oracle 10gR1 X$ Tables
      2. New Oracle 10gR2 X$ Tables
    18. X$ Table Naming Conventions
      1. Future Version Impact
    19. Tips Review
    20. References
  21. 14 Using STATSPACK and the AWR Report to Tune Waits and Latches
    1. What's New in 10gR2 (10.2) STATSPACK
    2. New Features in 10gR2 (10.2) STATSPACK
    3. Installing STATSPACK
      1. Security of the PERFSTAT Account
      2. Post-Installation
      3. Gathering Statistics
      4. Running the Statistics Report
    4. The Automatic Workload Repository (AWR) and the AWR Report
      1. Manually Managing the AWR
      2. AWR Automated Snapshots
      3. AWR Snapshot Reports
      4. Run the AWR Report in Oracle Enterprise Manager Grid Control
    5. Interpreting the STATSPACK Output
      1. The Header Information
      2. The Load Profile
      3. Instance Efficiency
      4. Top Wait Events
      5. Oracle Bugs
      6. The Life of an Oracle Shadow Process
      7. RAC Wait Events and Interconnect Statistics
      8. Top SQL Statements
      9. Instance Activity Statistics
      10. Tablespace and File I/O Statistics
      11. Segment Statistics
      12. Additional Memory Statistics
      13. UNDO Statistics
      14. Latch Statistics
      15. Tuning and Viewing at the Block Level (Advanced)
      16. Dictionary and Library Cache Statistics
      17. SGA Memory Statistics
      18. Non-Default Initialization Parameters
    6. Top 10 Things to Look for in AWR Report and STATSPACK Output
      1. Managing the STATSPACK Data
      2. Upgrading STATSPACK
      3. Deinstalling STATSPACK
    7. Quick Notes on the New ADDM Report
    8. Scripts 10gR2
    9. Tips Review
    10. References
  22. 15 Performing a Quick System Review (DBA)
    1. Total Performance Index (TPI)
    2. Education Performance Index (EPI)
    3. System Performance Index (SPI)
    4. Memory Performance Index (MPI)
      1. Buffer Hit Ratio
      2. Dictionary Cache Hit Ratio
      3. Library Cache Hit Ratio
      4. PGA Memory Sort Ratio
      5. Percent of Data Buffers Still Free
      6. Top 10 "Memory Abusers" as a Percent of All Statements
      7. Top 25 "Memory Abusers" Statements Tuned
      8. Pinning/Caching Objects
    5. Disk Performance Index (DPI)
      1. Top 25 "Disk-Read Abuser" Statements Tuned
      2. Top 10 Disk-Read Abusers as Percent of All Statements
      3. Tables/Indexes Separated
      4. Mission-Critical Table Management
      5. Key Oracle Files Separated
      6. Automatic UNDO Management
      7. Temporary Segment Balance
    6. Total Performance Index (TPI)
    7. Overall System Review Example
      1. Rating System
      2. Example System Review Rating Categories
      3. Items Requiring Immediate Action
      4. Other Items Requiring Action
    8. System Information List
      1. Memory-Related Values
      2. Disk-Related Values
      3. CPU-Related Values
      4. Backup- and Recovery-Related Information
      5. Naming Conventions and/or Standards and Security Information
      6. DBA Knowledge Rating
    9. Other Items to Consider in Your TPI and System Review
    10. Tips Review
    11. References
  23. 16 Monitor the System Using Unix Utilities (DBA)
    1. Unix/Linux Utilities
    2. Using the sar Command to Monitor CPU Usage
      1. sar–u (Check for CPU Bogged Down)
      2. The sar–d Command (Find I/O Problems)
      3. The sar–b Command (Check the Buffer Cache)
      4. The sar–q Command (Check the Run Queue and Swap Queue Lengths)
    3. Using the sar and vmstat Commands to Monitor Paging/Swapping
      1. Using the–p Switch of sar to Report Paging Activities
      2. Using the–w Switch of sar to Report Swapping and Switching Activities
      3. Using the–r Switch of sar to Report Free Memory and Free Swap
      4. Using the–g Switch of sar to Report Paging Activities
      5. Using the–wpgr Switch of sar
    4. Finding the Worst User on the System Using the top Command
      1. Monitoring Tools
    5. Using the uptime Command to Monitor CPU Load
    6. Using the mpstat Command to Identify CPU Bottlenecks
    7. Combining ps with Selected V$ Views
    8. Using the iostat Command to Identify Disk I/O Bottlenecks
      1. Using the–d Switch of iostat for Disk Drives sd15, sd16, sd17, and sd18
      2. Using the–D Switch of iostat
      3. Using the–x Switch of iostat
      4. Combining–x Switch of iostat with Logic in a Shell Script
    9. Using the ipcs Command to Determine Shared Memory
    10. Using the vmstat Command to Monitor System Load
    11. Monitoring Disk Free Space
      1. The df Command
    12. Monitoring Network Performance
      1. Monitoring Using the spray Command
      2. Monitoring Network Performance with nfsstat–c
      3. Monitoring Network Performance with netstat
      4. Displaying Current Values of Tunable Parameters
      5. Modifying the Configuration Information File
      6. Other Factors That Affect Performance
    13. Tips Review
    14. References
  24. A Key Initialization Parameters (DBA)
    1. Desupported Initialization Parameters
    2. Deprecated Initialization Parameters
    3. Top 25 Initialization Parameters
    4. Top 10 Initialization Parameters Not to Forget
    5. Top 13 Undocumented Initialization Parameters (As I See It)
    6. Listing of Documented Initialization Parameters (V$PARAMETER)
    7. Listing of Undocumented Initialization Parameters (x$ksppi/x$ksppcv)
    8. Oracle Applications 11i Recommendations (Note: 216205.1)
    9. Top 10 Reasons Not to Write a Book
    10. Tips Review
    11. References
  25. B The V$ Views (DBA and Developer)
    1. Creation of V$ and GV$ Views and X$ Tables
    2. A List of Oracle 10g ( GV$ and V$ Views
    3. The Oracle 10g ( V$ Views
    4. Oracle 10g Scripts of the x$ Tables Used to Create the V$ Views
  26. C The X$ Tables (DBA)
    1. Oracle 10g X$ Tables Ordered by Name
    2. Oracle 10g X$ Indexes
    3. Oracle 10g V$ Views Cross-Referenced to the X$ Tables
    4. Oracle 10g X$ Tables Not Referenced by a GV$ View
  27. Index

Product information

  • Title: Oracle Database 10g Performance Tuning Tips & Techniques
  • Author(s): Richard Niemiec
  • Release date: July 2007
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071596435