Oracle Database 11g Release 2 Performance Tuning Tips & Techniques

Oracle Database 11g Release 2 Performance Tuning Tips & Techniques

by Richard Niemiec
Released March 2012
Publisher(s): McGraw Hill Computing
ISBN: 9780071780278

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

Implement Proven Database Optimization Solutions

Systematically identify and eliminate database performance problems with help from Oracle Certified Master Richard Niemiec. Filled with real-world case studies and best practices, Oracle Database 11g Release 2 Performance Tuning Tips & Techniques details the latest monitoring, troubleshooting, and optimization methods. Find out how to find and fix bottlenecks, configure storage devices, execute effective queries, and develop bug-free SQL and PL/SQL code. Testing, reporting, and security enhancements are also covered in this Oracle Press guide.

  • Properly index and partition Oracle Database 11g Release 2
  • Work with Oracle Exadata and Oracle Exalogic Elastic Cloud
  • Efficiently manage disk drives, RAID arrays, and memory
  • Tune queries with Oracle SQL hints and the TRACE utility
  • Troubleshoot databases using V$ views and X$ tables
  • Distribute workload using Oracle Real Application Testing
  • Generate reports using Oracle's Statspack and Automatic Workload Repository tools
  • Use sar, vmstat, and iostat to monitor system statistics

“This is a timely update of Rich’s classic book on Oracle Database performance tuning to cover hot new topics like Oracle Database 11g Release 2 and Oracle Exadata. This is a must-have for DBAs moving to these new products.” --Andrew Mendelsohn, Senior Vice President, Oracle Database Server Technologies

Table of contents

  1. Cover 
  2. Epigraph1
  3. Title
  4. Copyright
  5. Epigraph2
  6. About the Author
  7. Contents 
  8. Acknowledgments
  9. Introduction
  10. 1: Introduction to 11g R1 & R2 New Features (DBA and Developer)
    1. Exadata Is What’s Next!
    2. Advanced Compression
    3. Automatic Diagnostic Repository
    4. Automatic Shared Memory Management (ASMM) Improvements
    5. Automatic Storage Management (ASM) Improvements
      1. ASM Preferred Mirror Read
      2. Rolling Upgrades/Patching
      3. Faster Rebalance
      4. ASM Diskgroup Compatibility
      5. ASMCMD Extensions
    6. Automatic SQL Tuning
    7. Data Guard Enhancements
      1. Snapshot Standby
      2. Active Data Guard
      3. Mixed Platform Support
      4. Advanced Compression for Logical Standby (11gR2)
      5. Transparent Data Encryption Support for Logical Standby
      6. Data Pump Compression Enhancements
      7. Data Pump Encrypted Dump File Sets
      8. Data Pump Legacy Mode
    8. Enhanced Statistics
      1. Enhanced I/O Statistics
      2. Reducing the Gathering of Partitioned Objects Statistics
      3. Pending Statistics
      4. Multicolumn Statistics
      5. Expression Statistics
    9. Flashback Data Archive
    10. Health Monitor
    11. Incident Packaging Service (IPS)
    12. Invisible Indexes
    13. New Partitioning Features
      1. Interval Partitioning
      2. REF Partitioning
      3. System Partitioning
      4. Virtual Column-based Partitioning
      5. Partition Advisor
    14. Read-Only Tables
    15. Real Application Clusters (RAC) One Node and RAC Patching
    16. Real Application Testing
      1. Database Replay
    17. SQL Performance Analyzer (SPA)
    18. Result Cache
    19. RMAN New Features
      1. Optimized Backups
      2. Improved Handling of Long-term Backups
      3. Parallel Backup of Very Large Datafiles
      4. Faster Backup Compression (Improved Compression)
      5. Active Database Duplication
      6. Better Recovery Catalog Management
      7. Archived Log Deletion Policy Enhancements
      8. Data Recovery Advisor
      9. Virtual Private Catalog
      10. Proactive Health Check
      11. Block Recovery (Flashback Logs)
      12. Block Recovery (Physical Standby)
    20. SecureFiles
      1. Compression
      2. Encryption
      3. Deduplication
    21. Enhancements to Streams (Golden Gate Is the Future of Streams)
      1. XStream In
      2. XStream Out
      3. Advanced Compression Support for Streams (11gR2)
    22. Shrink Temporary Tablespace
    23. Transparent Data Encryption (TDE) Improvements
    24. New Background Processes in 11g
    25. Version Comparison Chart
    26. New Features Review
    27. References
  11. 2: Basic Index Principles (Beginner Developer and Beginner DBA)
    1. Basic Index Concepts
    2. Invisible Indexes
    3. Concatenated Indexes
    4. Suppressing Indexes
      1. Using the NOT EQUAL Operators: <>, !=
      2. Using IS NULL or IS NOT NULL
      3. Using LIKE
      4. Using Functions
      5. Comparing Mismatched Data Types
    5. Selectivity
    6. The Clustering Factor
    7. The Binary Height
      1. Additional Details Concerning BLEVEL and Index Height
    8. Using Histograms
    9. Fast Full Scans
    10. Skip-Scans
    11. 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
    12. Fast Index Rebuilding
    13. Rebuilding Indexes Online
    14. Tips Review
    15. References
  12. 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 You Need?
      3. What Are Some of the RAID Levels Available?
      4. The Newer RAID 5
    2. Setup and Maintenance of the Traditional Filesystem
      1. What Is the Cost?
    3. Distributing “Key” Datafiles Across Hardware Disks
      1. Storing Data and Index Files in Separate Locations
      2. Avoiding I/O Disk Contention
      3. Moving Datafiles 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 Initialization 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
      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 Initialization 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 Partitioning
      3. New Partitioning Options in Oracle 11gR2
      4. 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 Reimporting the Table
      4. To Avoid Chaining, Set PCTFREE 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. Fast Recovery Area
    12. Increasing Chances of Recovery: Committing After Each Batch
      1. Isolating Large Transactions to Their Own Rollback Segments
      2. Using the UNDO Tablespace
      3. Monitoring UNDO Space
      4. Killing Problem Sessions
    13. Have Multiple Control Files on Different Disks and Controllers
    14. Other Disk I/O Precautions and Tips
    15. Issues to Consider in the Planning Stages
    16. Tips Review
    17. References
  13. 4: Tuning the Database with Initialization Parameters (DBA)
    1. When Upgrading to Oracle 11gR2
      1. Using SEC_CASE_SENSITIVE_LOGON (new in 11g)
    2. Identifying Crucial Initialization Parameters
    3. Changing the Initialization Parameters Without a Restart
    4. Insight into the Initialization Parameters from Oracle Utilities
    5. Viewing the Initialization Parameters with Enterprise Manager
    6. 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
    7. Setting DB_BLOCK_SIZE to Reflect the Size of Your Data Reads
    8. Setting SGA_MAX_SIZE to 25 to 50 Percent of the Size Allocated to Main Memory
    9. Tuning the SHARED_POOL_SIZE for Optimal Performance
      1. Using Stored Procedures for Optimal Use of the Shared SQL Area
      2. Setting the SHARED_POOL_SIZE High Enough to Fully Use the DB_CACHE_SIZE
      3. Keeping the Data Dictionary Cache Hit Ratio at or above 95 Percent
      4. Keeping the Library Cache Reload Ratio at 0 and the Hit Ratio Above 95 Percent
      5. Using Available Memory to Determine If the SHARED_POOL_SIZE Is Set Correctly
      6. Using the X$KSMSP Table to Get a Detailed Look at the Shared Pool
      7. Points to Remember About Cache Size
      8. Waits Related to Initialization Parameters
    10. Using Oracle Multiple Buffer Pools
      1. Pools Related to DB_CACHE_SIZE and Allocating Memory for Data
      2. Modifying the LRU Algorithm
      3. Pools Related to SHARED_POOL_SIZE and Allocating Memory for Statements
    11. Tuning the PGA_AGGREGATE_TARGET for Optimal Use of Memory
    12. Modifying the Size of Your SGA to Avoid Paging and Swapping
    13. Understanding the Oracle Optimizer
      1. How Optimization Looks at the Data
    14. Creating Enough Dispatchers
      1. Have Enough Open Cursors (OPEN_CURSORS)
      2. Don’t Let Your DDL Statements Fail (DDL Lock Timeout)
    15. Two Important Exadata Initialization Parameters (EXADATA ONLY)
    16. 25 Important Initialization Parameters to Consider
    17. Top 25 Initialization Parameters
      1. Initialization Parameters over the Years
      2. Finding Undocumented Initialization Parameters
    18. Understanding the Typical Server
      1. Modeling a Typical Server
      2. Sizing the Oracle Applications Database
    19. Tips Review
    20. References
  14. 5: Enterprise Manager and Grid Control (DBA and Developer)
    1. The Enterprise Manager (EM) Basics
    2. Starting with All Targets and Other Groupings
    3. SQL Performance Analyzer (SPA)
    4. Automatic Database Diagnostic Monitor (ADDM)
      1. Database Instance Server Tab and Database Administration Tab
      2. Database Instance Server Tab: Tablespaces
      3. Database Instance Server Tab: Instance Level Focus
      4. Database Instance Server Tab: All Initialization Parameters
      5. Database Instance Server Tab: Manage Optimizer Statistics
      6. Database Instance Server Tab: 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. SQL Advisors
    9. Deployments Tab (Patching Options)
    10. Scheduler Central and the Jobs Tab
    11. Reports Tab
    12. Automatic Storage Management Performance
    13. Real Application Testing (Database Replay)
    14. EM for Exadata
    15. Summary
    16. Tips Review
    17. References
  15. 6: Using EXPLAIN and SQL PLAN MANAGMENT (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. Digging into the TKPROF Output
    2. Using DBMS_MONITOR
      1. TRCSESS Multiple Trace Files into One File
    3. Using EXPLAIN PLAN Alone
      1. EXPLAIN PLAN—Read It Top to Bottom or Bottom to Top?
      2. Yet Another EXPLAIN PLAN Output Method: Building the Tree Structure
      3. Another Example Using the Tree Approach
      4. Tracing/Explaining Problem Queries in Developer Products
      5. Important Columns in the PLAN_TABLE Table
      6. Initialization Parameters for Undocumented TRACE
    4. Using Stored Outlines
    5. Using SQL Plan Management (SPM) (11g New Feature)
      1. Converting from Stored Outlines to SQL Plan Management
    6. Tips Review
    7. References
  16. 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 Oracle Demo Sample HR Schema
      2. The FIRST_ROWS Hint
      3. The ALL_ROWS Hint
      4. The FULL Hint
      5. The INDEX Hint
      6. The NO_INDEX Hint
      7. The INDEX_JOIN Hint
      8. The INDEX_COMBINE Hint
      9. The INDEX_ASC Hint
      10. The INDEX_DESC Hint
      11. The INDEX_FFS Hint
      12. The ORDERED Hint
      13. The LEADING Hint
      14. The NO_EXPAND Hint
      15. The DRIVING_SITE Hint
      16. The USE_MERGE Hint
      17. The USE_NL Hint
      18. The USE_HASH Hint
      19. The QB_NAME Hint
      20. The PUSH_SUBQ Hint
      21. The PARALLEL Hint
      22. The NO_PARALLEL Hint
      23. The PARALLEL_INDEX Hint
      24. The PQ_DISTRIBUTE Hint
      25. The APPEND Hint
      26. The NOAPPEND Hint
      27. The CACHE Hint
      28. The NOCACHE Hint
      29. The RESULT_CACHE Hint
      30. The CURSOR_SHARING_EXACT Hint
    7. Some Miscellaneous Hints and Notes
      1. Undocumented Hints
      2. Using Hints with Views
      3. Notes on Hints and Stored Outlines (or SQL Plan Baselines in 11g)
    8. Why Isn’t My Hint Working?
    9. Hints at a Glance
    10. Tips Review
    11. References
  17. 8: Query Tuning: Developer and Beginner DBA
    1. What Queries Do I Tune? Querying V$SQLAREA and V$SQL Views
      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. Oracle 11g 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_SQLTEXT View
      5. Selecting Query EXPLAIN PLAN from the DBA_HIST_SQL_PLAN View
    3. When Should I Use an Index?
    4. What If I Forget the Index?
      1. Creating an Index
      2. Invisible Index
      3. Checking the Index on a Table
      4. Is the Column Properly Indexed?
    5. What If I Create a Bad Index?
    6. Exercising Caution When Dropping an Index
    7. Indexing the Columns Used in the SELECT and WHERE
    8. Using the Fast Full Scan
    9. Making the Query “Magically” Faster
    10. Caching a Table in Memory
    11. Using the New 11g Result Cache
    12. Choosing Among Multiple Indexes (Use the Most Selective)
    13. The Index Merge
    14. Indexes That Can Get Suppressed
    15. Function-Based Indexes
    16. Virtual Columns
    17. The “Curious” OR
    18. Using the EXISTS Function and the Nested Subquery
    19. That Table Is Actually a View!
    20. SQL and Grand Unified Theory
    21. Tuning Changes in Oracle Database 11g
    22. Oracle 11g Automatic SQL Tuning
      1. Ensuring the Tuning User Has Access to the API
      2. Creating the Tuning Task
      3. Making Sure the Task Can Be Seen in the Advisor Log
      4. Executing the SQL Tuning Task
      5. Check Status of the Tuning Task
      6. Displaying the SQL Tuning Advisor Report
      7. Reviewing the Report Output
    23. Tuning SQL Statements Automatically Using SQL Tuning Advisor
      1. Enabling Automatic SQL Tuning Advisor
      2. Configuring Automatic SQL Tuning Advisor
      3. Viewing Automatic SQL Tuning Results
    24. Using SQL Performance Analyzer (SPA)
    25. Tips Review
    26. References
  18. 9: Table Joins and Other Advanced Tuning (Advanced DBA and Developer)
    1. Real Application Testing
      1. Database Replay
    2. SQL Performance Analyzer
      1. Create a SQL Tuning Set
      2. Create an Analysis Task
      3. Execute Analysis Task
      4. Query Performance Analyzer Advisor Tasks
      5. Cancel an Executing SQL Performance Analyzer Analysis Task
      6. Remove SQL Performance Analyzer Analysis Task
      7. Determine Active SQL Tuning Sets
      8. Remove SQL Tuning Set
      9. Drop SQL Tuning Set
    3. Join Methods
      1. Nested Loops Joins
      2. SORT-MERGE Joins
      3. CLUSTER Joins
      4. HASH Joins
      5. INDEX-MERGE Joins
    4. Table Join Initialization Parameters
      1. SORT-MERGE and HASH Join Parameters
    5. A Two-Table Join: Equal-Sized Tables (Cost-Based)
    6. A Two-Table INDEXED Join: Equal-Sized Tables (Cost-Based)
    7. Forcing a Specific Join Method
    8. Eliminating Join Records (Candidate Rows) in Multitable Joins
    9. A Two-Table Join Between a Large and Small Table
    10. Three-Table Joins: Not as Much Fun
    11. Bitmap Join Indexes
      1. Bitmap Indexes
      2. Bitmap Join Index
      3. Best Uses for the Bitmap Join Index
    12. Third-Party Product Tuning
      1. Example 1
      2. Example 2
      3. Example 3
    13. Tuning Distributed Queries
    14. When You Have Everything Tuned
    15. 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
    16. Tuning at the Block Level (Advanced)
      1. Key Sections of a Block Dump
      2. A Brief Look at an Index Block Dump
    17. Tuning Using Simple Mathematical Techniques
      1. Traditional Mathematical Analysis
      2. Seven-Step Methodology
      3. Pattern Interpretation
      4. Mathematical Techniques Conclusions
    18. Join Tuning: Relational vs. Object-Relational Performance
      1. Models Used
      2. Results
      3. Conclusion
    19. Tips Review
    20. References
  19. 10: Using PL/SQL to Enhance Performance (Developer and DBA)
    1. Leverage the PL/SQL Function Result Cache to Improve Performance (New in 11g)
    2. Reference Sequences Directly in PL/SQL Expressions (New in 11g)
    3. Use Named Parameters in SQL Function Calls (New in 11g)
    4. Simplify loops with the CONTINUE Statement (New in 11g)
    5. Leverage Compile-Time Warnings to Catch Programming Mistakes (Improved in 11g)
    6. Using Table Triggers (Improved in 11g)
    7. Increase Performance with Native Compilation (Improved in 11g)
    8. Maximize Performance with the Optimizing Compiler (Improved in 11g)
    9. Use DBMS_APPLICATION_INFO for Real-Time Monitoring
    10. Log Timing Information in a Database Table
    11. Reduce PL/SQL Program Unit Iterations and Iteration Time
    12. Use ROWID for Iterative Processing
    13. 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
    14. Reduce the Calls to SYSDATE
    15. Reduce the Use of the MOD Function
    16. Improve Shared Pool Use by Pinning PL/SQL Objects
      1. Pinning (Caching) PL/SQL Object Statements into Memory
      2. Pinning All Packages
    17. Identify PL/SQL Objects That Need to Be Pinned
    18. Use and Modify DBMS_SHARED_POOL.SIZES
      1. Find Large Objects
    19. Get Detailed Object Information from DBA_OBJECT_SIZE
      1. Get Contiguous Space Currently in the Shared Pool
    20. Find Invalid Objects
    21. Find Disabled Triggers
    22. Use PL/SQL Associative Arrays for Fast Reference Table Lookups
    23. Find and Tune the SQL When Objects Are Used
    24. Consider the Time Component When Working with DATE Data Types
    25. Use PL/SQL to Tune PL/SQL
    26. Understand the Implications of PL/SQL Object Location
    27. Use Rollback Segments to Open Large Cursors
      1. Use Active Transaction Management to Process Large Quantities of Data
    28. Use Temporary Database Tables for Increased Performance
    29. Limit the Use of Dynamic SQL
    30. Use Pipelined Table Functions to Build Complex Result Sets
    31. Leave Those Debugging Commands Alone!
    32. 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 Example
      6. Create a Package Example
      7. Database Trigger Example Using PL/SQL
    33. Tips Review
    34. References
  20. 11: Exadata, Tuning RAC, and Using Parallel Features
    1. Exadata Terminology and the Basics
    2. Exadata Stats
    3. Exadata Storage Expansion Rack Briefly
    4. Exalogic Briefly
    5. Smart Scans
    6. Flash Cache
    7. Storage Indexes
    8. Hybrid Columnar Compression (11.2)
    9. I/O Resource Management (IORM)
    10. Use All Oracle Security Advantages with Exadata
    11. Best Practices
    12. Summary: Exadata = Paradigm Shift!
    13. Oracle Database Appliance
    14. SPARC SuperCluster
    15. Oracle Exalytics Business Intelligence Machine
    16. Other Oracle Hardware to Consider
      1. The Oracle Big Data Appliance
      2. ZFS Storage Servers
      3. Pillar Storage System
      4. StorageTek Modular Library System
    17. The Oracle Public Cloud and the Oracle Social Network
    18. Parallel Databases
    19. Real Application Clusters (RAC)
      1. Oracle RAC Architecture
      2. Internal Workings of the Oracle RAC System
      3. SCN Processing
      4. Summary
    20. RAC Performance Tuning Overview
      1. RAC Cluster Interconnect Performance
      2. Finding RAC Wait Events—Sessions Waiting
      3. RAC Wait Events and Interconnect Statistics
      4. Cluster Interconnect Tuning—Hardware Tier
      5. Tuning RAC Using Enterprise Manager Grid Control
    21. Basic Concepts of Parallel Operations
      1. Parallel DML and DDL Statements and Operations
    22. Parallel DML Statements and Operations
    23. Managing Parallel Server Resources and Parallel Statement Queuing
    24. Parallelism and Partitions
    25. Inter- and Intra-operation Parallelization
      1. Examples of Using Inter- and Intra-operations (PARALLEL and NOPARALLEL Hints)
    26. Creating Table and Index Examples Using Parallel Operations
      1. Real-World Example of Distributing Data for Effective Parallel Operations
    27. Parallel DML Statements and Examples
      1. Parallel DML Restrictions
      2. Parallel DML Statement Examples
    28. Monitoring Parallel Operations via the V$ Views
      1. V$PQ_TQSTAT
      2. V$PQ_SYSSTAT
      3. V$PQ_SESSTAT
    29. Using EXPLAIN PLAN and AUTOTRACE on Parallel Operations
    30. Tuning Parallel Execution and the Initialization Parameters
    31. Parallel Loading
    32. Performance Comparisons and Monitoring Parallel Operations
    33. Optimizing Parallel Operations in RAC
      1. Objectives of Parallel Operations
      2. RAC Parallel Usage Models
      3. INIT.ORA 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
    34. Other Parallel Notes
    35. Oracle Documentation Is Online
    36. Tips Review
    37. References
  21. 12: The V$ Views (Developer and DBA)
    1. Creating and Granting Access to V$ Views
      1. Obtaining a Count and Listing of All V$ Views
    2. Getting a Listing for the X$ Scripts That Make Up the V$ Views
      1. Examining the Underlying Objects That Make Up the DBA_ Views
    3. Using Helpful V$ Scripts
      1. Basic Database Information
      2. Basic Automatic Workload Repository (AWR) Information
      3. Basic Licensing Information
      4. Database Options Installed in Your Database
    4. Summary of Memory Allocated (V$SGA)
      1. Automatic Memory Management (AMM) and MEMORY_TARGET
      2. Detailed Memory Allocated (V$SGASTAT)
    5. Finding spfile.ora/init.ora Settings in V$PARAMETER
    6. Determining Hit Ratio for Data (V$SYSSTAT & V$SYSMETRIC)
    7. Determining Hit Ratio for the Data Dictionary (V$ROWCACHE)
    8. Determining Hit Ratio for the Shared SQL and PL/SQL (V$LIBRARYCACHE)
      1. Using the Result Cache
    9. Identifying PL/SQL Objects That Need to Be Kept (Pinned)
    10. Finding Problem Queries by Monitoring V$SESSION_LONGOPS
    11. Finding Problem Queries by Querying V$SQLAREA
    12. Finding Out What Users Are Doing and Which Resources They Are Using
      1. Finding Out Which Objects a User Is Accessing
      2. Getting Detailed User Information
    13. Using Indexes
    14. Identifying Locking Issues
      1. Killing the Problem Session
      2. Finding Users with Multiple Sessions
    15. Finding Disk I/O Issues
    16. Finding Rollback Segment Contention
    17. Determining Whether Freelists Are Sufficient
    18. Checking for Privileges and Roles
    19. Wait Events V$ Views
    20. Some of the Major V$ View Categories
    21. Tips Review
    22. References
  22. 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. Monitoring Space Allocations in the Shared Pool
    7. Creating 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. Mutex/Latch Waits and/or Sleeps
      8. Miscellaneous
    8. Obtaining Information about Redo Log Files
    9. Setting Initialization Parameters
      1. Case 1
      2. Case 2
      3. Case 3
      4. Case 4
      5. Case 5
    10. Buffer Cache/Data Block Details
      1. Buffer Statuses
      2. Segments Occupying Block Buffers
      3. Hot Data Blocks and the Causes of Latch Contention and Wait Events
    11. Obtaining Database- and Instance-Specific Information
    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 Info
      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
    18. X$ Table Naming Conventions
      1. Future Version Impact
    19. Tips Review
    20. References
  23. 14: Using Statspack and the AWR Report to Tune Waits, Latches, and Mutexes
    1. What’s New in 11gR2 (11.2) Statspack and the AWR Report
    2. Installing Statspack
      1. Security of the PERFSTAT Account
      2. Post-Installation
      3. Gathering Statistics
      4. Running the Statistics Report
    3. 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
    4. Interpreting the Statspack and AWR Report 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 and Mutex Statistics
      15. Tuning and Viewing at the Block Level (Advanced)
      16. Dictionary and Library Cache Statistics
      17. SGA Memory Statistics
      18. Nondefault Initialization Parameters
    5. Top 10 Things to Look for in AWR Report and Statspack Output
      1. Managing the Statspack Data
      2. Upgrading Statspack
      3. Deinstalling Statspack
    6. Quick Notes on the New ADDM Report
    7. Scripts in 11gR2
    8. Tips Review
    9. References
  24. 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. Top 25 “Memory Abusers” Statements Tuned
      2. Top 10 “Memory Abusers” as a Percent of All Statements
      3. Buffer Cache Hit Ratio
      4. Dictionary Cache Hit Ratio
      5. Library Cache Hit Ratio
      6. PGA Memory Sort Ratio
      7. Percentage of Data Buffers Still Free
      8. Using the Result Cache Effectively
      9. Pinning/Caching Objects
    5. Disk Performance Index (DPI)
      1. Top 25 “Disk-Read Abuser” Statements Tuned
      2. Top 10 Disk-Read Abusers as Percentage 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 Questions
      6. DBA Knowledge Rating
    9. Other Items to Consider in Your TPI and System Review
    10. Tips Review
    11. References
  25. 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 sar −p to Report Paging Activities
      2. Using sar −w to Report Swapping and Switching Activities
      3. Using sar −r to Report Free Memory and Free Swap
      4. Using sar −g to Report Paging Activities
      5. Using sar −wpgr to Report on Memory Resources
    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 iostat −d for Disk Drives sd15, sd16, sd17, and sd18
      2. Using iostat −D
      3. Using iostat −x
      4. Combining iostat −x 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. Other Sources to Improve Performance
    14. Tips Review
    15. References
  26. A: Key Initialization Parameters (DBA)
    1. Obsoleted/Desupported Initialization Parameters
    2. Deprecated Initialization Parameters
    3. Top 25 Initialization Parameters
    4. Top 20 Initialization Parameters Not to Forget
    5. Top 13 Undocumented Initialization Parameters (As I See It)
    6. Bonus 11 Undocumented Initialization Parameters
    7. Listing of Documented Initialization Parameters (V$PARAMETER)
    8. Listing of Undocumented Initialization Parameters (X$KSPPI/X$KSPPCV)
    9. Oracle Applications Release 12 Recommendations (Note: 396009.1)
      1. Database Initialization Parameter Sizing
    10. Top 10 Reasons Not to Write a Book
    11. Tips Review
    12. References
  27. B: The V$ Views (DBA and Developer)
    1. Creation of V$ and GV$ Views and X$ Tables
    2. A List of Oracle 11g (11.2.0.1.0) GV$ Views
    3. A List of Oracle 11g (11.2.0.1.0) V$ Views
    4. Oracle 11g Scripts for the X$ Tables Used to Create the V$ Views
  28. C: The X$ Tables (DBA)
    1. Oracle 11gR2 X$ Tables Ordered by Name
    2. Oracle 11g X$ Indexes
    3. Oracle 11g V$ Views Cross-Referenced to the X$ Tables
  29. Index

Product information

  • Title: Oracle Database 11g Release 2 Performance Tuning Tips & Techniques
  • Author(s): Richard Niemiec
  • Release date: March 2012
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071780278