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
- Cover
- Epigraph1
- Title
- Copyright
- Epigraph2
- About the Author
- Contents
- Acknowledgments
- Introduction
-
1: Introduction to 11g R1 & R2 New Features (DBA and Developer)
- Exadata Is What’s Next!
- Advanced Compression
- Automatic Diagnostic Repository
- Automatic Shared Memory Management (ASMM) Improvements
- Automatic Storage Management (ASM) Improvements
- Automatic SQL Tuning
- Data Guard Enhancements
- Enhanced Statistics
- Flashback Data Archive
- Health Monitor
- Incident Packaging Service (IPS)
- Invisible Indexes
- New Partitioning Features
- Read-Only Tables
- Real Application Clusters (RAC) One Node and RAC Patching
- Real Application Testing
- SQL Performance Analyzer (SPA)
- Result Cache
-
RMAN New Features
- Optimized Backups
- Improved Handling of Long-term Backups
- Parallel Backup of Very Large Datafiles
- Faster Backup Compression (Improved Compression)
- Active Database Duplication
- Better Recovery Catalog Management
- Archived Log Deletion Policy Enhancements
- Data Recovery Advisor
- Virtual Private Catalog
- Proactive Health Check
- Block Recovery (Flashback Logs)
- Block Recovery (Physical Standby)
- SecureFiles
- Enhancements to Streams (Golden Gate Is the Future of Streams)
- Shrink Temporary Tablespace
- Transparent Data Encryption (TDE) Improvements
- New Background Processes in 11g
- Version Comparison Chart
- New Features Review
- References
- 2: Basic Index Principles (Beginner Developer and Beginner DBA)
-
3: Disk Implementation Methodology and ASM (DBA)
- Disk Arrays: Not a Choice Anymore
- Setup and Maintenance of the Traditional Filesystem
- Distributing “Key” Datafiles Across Hardware Disks
- Locally Managed Tablespaces
-
ASM Introduction
- Communication Across IT Roles
- ASM Instances
- ASM Initialization Parameters
- ASM Installation
- ASM Parameters and SGA Sizing
- ASM and Privileges
- ASM Disks
- ASM and Multipathing
- ASM Diskgroups
- ASM Diskgroups and Databases
- ASM Redundancy and Failure Groups
- New Space-Related Columns
- Cluster Synchronization Services
- Database Instances and ASM
- Database Consolidation and Clustering with ASM
- Database Processes to Support ASM
- Bigfile and ASM
- Database Initialization Parameters to Support ASM
- ASM and Database Deployment Best Practices
- ASM Storage Management and Allocation
- ASM Rebalance and Redistribution
- Avoiding Disk Contention by Using Partitions
- Index Partitioning
- Exporting Partitions
- Eliminating Fragmentation
- Increasing the Log File Size and LOG_CHECKPOINT_INTERVAL for Speed
- Fast Recovery Area
- Increasing Chances of Recovery: Committing After Each Batch
- Have Multiple Control Files on Different Disks and Controllers
- Other Disk I/O Precautions and Tips
- Issues to Consider in the Planning Stages
- Tips Review
- References
-
4: Tuning the Database with Initialization Parameters (DBA)
- When Upgrading to Oracle 11gR2
- Identifying Crucial Initialization Parameters
- Changing the Initialization Parameters Without a Restart
- Insight into the Initialization Parameters from Oracle Utilities
- Viewing the Initialization Parameters with Enterprise Manager
- Increasing Performance by Tuning the DB_CACHE_SIZE
- Setting DB_BLOCK_SIZE to Reflect the Size of Your Data Reads
- Setting SGA_MAX_SIZE to 25 to 50 Percent of the Size Allocated to Main Memory
-
Tuning the SHARED_POOL_SIZE for Optimal Performance
- Using Stored Procedures for Optimal Use of the Shared SQL Area
- Setting the SHARED_POOL_SIZE High Enough to Fully Use the DB_CACHE_SIZE
- Keeping the Data Dictionary Cache Hit Ratio at or above 95 Percent
- Keeping the Library Cache Reload Ratio at 0 and the Hit Ratio Above 95 Percent
- Using Available Memory to Determine If the SHARED_POOL_SIZE Is Set Correctly
- Using the X$KSMSP Table to Get a Detailed Look at the Shared Pool
- Points to Remember About Cache Size
- Waits Related to Initialization Parameters
- Using Oracle Multiple Buffer Pools
- Tuning the PGA_AGGREGATE_TARGET for Optimal Use of Memory
- Modifying the Size of Your SGA to Avoid Paging and Swapping
- Understanding the Oracle Optimizer
- Creating Enough Dispatchers
- Two Important Exadata Initialization Parameters (EXADATA ONLY)
- 25 Important Initialization Parameters to Consider
- Top 25 Initialization Parameters
- Understanding the Typical Server
- Tips Review
- References
-
5: Enterprise Manager and Grid Control (DBA and Developer)
- The Enterprise Manager (EM) Basics
- Starting with All Targets and Other Groupings
- SQL Performance Analyzer (SPA)
-
Automatic Database Diagnostic Monitor (ADDM)
- Database Instance Server Tab and Database Administration Tab
- Database Instance Server Tab: Tablespaces
- Database Instance Server Tab: Instance Level Focus
- Database Instance Server Tab: All Initialization Parameters
- Database Instance Server Tab: Manage Optimizer Statistics
- Database Instance Server Tab: Resource Manager (Consumer Groups)
- Database Maintenance Tab
- Database Topology Tab
- Database Performance Tab
- Monitoring the Hosts
- Monitoring the Application Servers
- Monitoring the Web Applications
- SQL Advisors
- Deployments Tab (Patching Options)
- Scheduler Central and the Jobs Tab
- Reports Tab
- Automatic Storage Management Performance
- Real Application Testing (Database Replay)
- EM for Exadata
- Summary
- Tips Review
- References
-
6: Using EXPLAIN and SQL PLAN MANAGMENT (Developer and DBA)
- The Oracle SQL TRACE Utility
- Using DBMS_MONITOR
-
Using EXPLAIN PLAN Alone
- EXPLAIN PLAN—Read It Top to Bottom or Bottom to Top?
- Yet Another EXPLAIN PLAN Output Method: Building the Tree Structure
- Another Example Using the Tree Approach
- Tracing/Explaining Problem Queries in Developer Products
- Important Columns in the PLAN_TABLE Table
- Initialization Parameters for Undocumented TRACE
- Using Stored Outlines
- Using SQL Plan Management (SPM) (11g New Feature)
- Tips Review
- References
-
7: Basic Hint Syntax (Developer and DBA)
- Top Hints Used
- Available Hints and Groupings
- Specifying a Hint
- Specifying Multiple Hints
- When Using an Alias, Hint the Alias, Not the Table
-
The Hints
- The Oracle Demo Sample HR Schema
- The FIRST_ROWS Hint
- The ALL_ROWS Hint
- The FULL Hint
- The INDEX Hint
- The NO_INDEX Hint
- The INDEX_JOIN Hint
- The INDEX_COMBINE Hint
- The INDEX_ASC Hint
- The INDEX_DESC Hint
- The INDEX_FFS Hint
- The ORDERED Hint
- The LEADING Hint
- The NO_EXPAND Hint
- The DRIVING_SITE Hint
- The USE_MERGE Hint
- The USE_NL Hint
- The USE_HASH Hint
- The QB_NAME Hint
- The PUSH_SUBQ Hint
- The PARALLEL Hint
- The NO_PARALLEL Hint
- The PARALLEL_INDEX Hint
- The PQ_DISTRIBUTE Hint
- The APPEND Hint
- The NOAPPEND Hint
- The CACHE Hint
- The NOCACHE Hint
- The RESULT_CACHE Hint
- The CURSOR_SHARING_EXACT Hint
- Some Miscellaneous Hints and Notes
- Why Isn’t My Hint Working?
- Hints at a Glance
- Tips Review
- References
-
8: Query Tuning: Developer and Beginner DBA
- What Queries Do I Tune? Querying V$SQLAREA and V$SQL Views
- Oracle 11g Views for Locating Resource-Intensive Sessions and Queries
- When Should I Use an Index?
- What If I Forget the Index?
- What If I Create a Bad Index?
- Exercising Caution When Dropping an Index
- Indexing the Columns Used in the SELECT and WHERE
- Using the Fast Full Scan
- Making the Query “Magically” Faster
- Caching a Table in Memory
- Using the New 11g Result Cache
- Choosing Among Multiple Indexes (Use the Most Selective)
- The Index Merge
- Indexes That Can Get Suppressed
- Function-Based Indexes
- Virtual Columns
- The “Curious” OR
- Using the EXISTS Function and the Nested Subquery
- That Table Is Actually a View!
- SQL and Grand Unified Theory
- Tuning Changes in Oracle Database 11g
- Oracle 11g Automatic SQL Tuning
- Tuning SQL Statements Automatically Using SQL Tuning Advisor
- Using SQL Performance Analyzer (SPA)
- Tips Review
- References
-
9: Table Joins and Other Advanced Tuning (Advanced DBA and Developer)
- Real Application Testing
- SQL Performance Analyzer
- Join Methods
- Table Join Initialization Parameters
- A Two-Table Join: Equal-Sized Tables (Cost-Based)
- A Two-Table INDEXED Join: Equal-Sized Tables (Cost-Based)
- Forcing a Specific Join Method
- Eliminating Join Records (Candidate Rows) in Multitable Joins
- A Two-Table Join Between a Large and Small Table
- Three-Table Joins: Not as Much Fun
- Bitmap Join Indexes
- Third-Party Product Tuning
- Tuning Distributed Queries
- When You Have Everything Tuned
- Miscellaneous Tuning Snippets
- Tuning at the Block Level (Advanced)
- Tuning Using Simple Mathematical Techniques
- Join Tuning: Relational vs. Object-Relational Performance
- Tips Review
- References
-
10: Using PL/SQL to Enhance Performance (Developer and DBA)
- Leverage the PL/SQL Function Result Cache to Improve Performance (New in 11g)
- Reference Sequences Directly in PL/SQL Expressions (New in 11g)
- Use Named Parameters in SQL Function Calls (New in 11g)
- Simplify loops with the CONTINUE Statement (New in 11g)
- Leverage Compile-Time Warnings to Catch Programming Mistakes (Improved in 11g)
- Using Table Triggers (Improved in 11g)
- Increase Performance with Native Compilation (Improved in 11g)
- Maximize Performance with the Optimizing Compiler (Improved in 11g)
- Use DBMS_APPLICATION_INFO for Real-Time Monitoring
- Log Timing Information in a Database Table
- Reduce PL/SQL Program Unit Iterations and Iteration Time
- Use ROWID for Iterative Processing
- Standardize on Data Types, IF Statement Order, and PLS_INTEGER
- Reduce the Calls to SYSDATE
- Reduce the Use of the MOD Function
- Improve Shared Pool Use by Pinning PL/SQL Objects
- Identify PL/SQL Objects That Need to Be Pinned
- Use and Modify DBMS_SHARED_POOL.SIZES
- Get Detailed Object Information from DBA_OBJECT_SIZE
- Find Invalid Objects
- Find Disabled Triggers
- Use PL/SQL Associative Arrays for Fast Reference Table Lookups
- Find and Tune the SQL When Objects Are Used
- Consider the Time Component When Working with DATE Data Types
- Use PL/SQL to Tune PL/SQL
- Understand the Implications of PL/SQL Object Location
- Use Rollback Segments to Open Large Cursors
- Use Temporary Database Tables for Increased Performance
- Limit the Use of Dynamic SQL
- Use Pipelined Table Functions to Build Complex Result Sets
- Leave Those Debugging Commands Alone!
- The “Look and Feel”: Just for the Beginners
- Tips Review
- References
-
11: Exadata, Tuning RAC, and Using Parallel Features
- Exadata Terminology and the Basics
- Exadata Stats
- Exadata Storage Expansion Rack Briefly
- Exalogic Briefly
- Smart Scans
- Flash Cache
- Storage Indexes
- Hybrid Columnar Compression (11.2)
- I/O Resource Management (IORM)
- Use All Oracle Security Advantages with Exadata
- Best Practices
- Summary: Exadata = Paradigm Shift!
- Oracle Database Appliance
- SPARC SuperCluster
- Oracle Exalytics Business Intelligence Machine
- Other Oracle Hardware to Consider
- The Oracle Public Cloud and the Oracle Social Network
- Parallel Databases
- Real Application Clusters (RAC)
- RAC Performance Tuning Overview
- Basic Concepts of Parallel Operations
- Parallel DML Statements and Operations
- Managing Parallel Server Resources and Parallel Statement Queuing
- Parallelism and Partitions
- Inter- and Intra-operation Parallelization
- Creating Table and Index Examples Using Parallel Operations
- Parallel DML Statements and Examples
- Monitoring Parallel Operations via the V$ Views
- Using EXPLAIN PLAN and AUTOTRACE on Parallel Operations
- Tuning Parallel Execution and the Initialization Parameters
- Parallel Loading
- Performance Comparisons and Monitoring Parallel Operations
- Optimizing Parallel Operations in RAC
- Other Parallel Notes
- Oracle Documentation Is Online
- Tips Review
- References
-
12: The V$ Views (Developer and DBA)
- Creating and Granting Access to V$ Views
- Getting a Listing for the X$ Scripts That Make Up the V$ Views
- Using Helpful V$ Scripts
- Summary of Memory Allocated (V$SGA)
- Finding spfile.ora/init.ora Settings in V$PARAMETER
- Determining Hit Ratio for Data (V$SYSSTAT & V$SYSMETRIC)
- Determining Hit Ratio for the Data Dictionary (V$ROWCACHE)
- Determining Hit Ratio for the Shared SQL and PL/SQL (V$LIBRARYCACHE)
- Identifying PL/SQL Objects That Need to Be Kept (Pinned)
- Finding Problem Queries by Monitoring V$SESSION_LONGOPS
- Finding Problem Queries by Querying V$SQLAREA
- Finding Out What Users Are Doing and Which Resources They Are Using
- Using Indexes
- Identifying Locking Issues
- Finding Disk I/O Issues
- Finding Rollback Segment Contention
- Determining Whether Freelists Are Sufficient
- Checking for Privileges and Roles
- Wait Events V$ Views
- Some of the Major V$ View Categories
- Tips Review
- References
-
13: The X$ Tables (Advanced DBA)
- Introducing the X$ Tables
- Creating V$ Views and X$ Tables
- Obtaining a List of All the X$ Tables
- Obtaining a List of All the X$ Indexes
- Using Hints with X$ Tables and Indexes
- Monitoring Space Allocations in the Shared Pool
- Creating Queries to Monitor the Shared Pool
- Obtaining Information about Redo Log Files
- Setting Initialization Parameters
- Buffer Cache/Data Block Details
- Obtaining Database- and Instance-Specific Information
- Effective X$ Table Use and Strategy
- Related Oracle Internals Topics
- Reading the Trace File
- Some Common X$ Table Groups
- Some Common X$ Table and Non-V$ Fixed View Associations
- Common X$ Table Joins
- X$ Table Naming Conventions
- Tips Review
- References
-
14: Using Statspack and the AWR Report to Tune Waits, Latches, and Mutexes
- What’s New in 11gR2 (11.2) Statspack and the AWR Report
- Installing Statspack
- The Automatic Workload Repository (AWR) and the AWR Report
-
Interpreting the Statspack and AWR Report Output
- The Header Information
- The Load Profile
- Instance Efficiency
- Top Wait Events
- Oracle Bugs
- The Life of an Oracle Shadow Process
- RAC Wait Events and Interconnect Statistics
- Top SQL Statements
- Instance Activity Statistics
- Tablespace and File I/O Statistics
- Segment Statistics
- Additional Memory Statistics
- UNDO Statistics
- Latch and Mutex Statistics
- Tuning and Viewing at the Block Level (Advanced)
- Dictionary and Library Cache Statistics
- SGA Memory Statistics
- Nondefault Initialization Parameters
- Top 10 Things to Look for in AWR Report and Statspack Output
- Quick Notes on the New ADDM Report
- Scripts in 11gR2
- Tips Review
- References
-
15: Performing a Quick System Review (DBA)
- Total Performance Index (TPI)
- Education Performance Index (EPI)
- System Performance Index (SPI)
- Memory Performance Index (MPI)
- Disk Performance Index (DPI)
- Total Performance Index (TPI)
- Overall System Review Example
- System Information List
- Other Items to Consider in Your TPI and System Review
- Tips Review
- References
-
16: Monitor the System Using Unix Utilities (DBA)
- Unix/Linux Utilities
- Using the sar Command to Monitor CPU Usage
- Using the sar and vmstat Commands to Monitor Paging/Swapping
- Finding the Worst User on the System Using the top Command
- Using the uptime Command to Monitor CPU Load
- Using the mpstat Command to Identify CPU Bottlenecks
- Combining ps with Selected V$ Views
- Using the iostat Command to Identify Disk I/O Bottlenecks
- Using the ipcs Command to Determine Shared Memory
- Using the vmstat Command to Monitor System Load
- Monitoring Disk Free Space
- Monitoring Network Performance
- Other Sources to Improve Performance
- Tips Review
- References
-
A: Key Initialization Parameters (DBA)
- Obsoleted/Desupported Initialization Parameters
- Deprecated Initialization Parameters
- Top 25 Initialization Parameters
- Top 20 Initialization Parameters Not to Forget
- Top 13 Undocumented Initialization Parameters (As I See It)
- Bonus 11 Undocumented Initialization Parameters
- Listing of Documented Initialization Parameters (V$PARAMETER)
- Listing of Undocumented Initialization Parameters (X$KSPPI/X$KSPPCV)
- Oracle Applications Release 12 Recommendations (Note: 396009.1)
- Top 10 Reasons Not to Write a Book
- Tips Review
- References
- B: The V$ Views (DBA and Developer)
- C: The X$ Tables (DBA)
- Index
Product information
- Title: Oracle Database 11g Release 2 Performance Tuning Tips & Techniques
- Author(s):
- Release date: March 2012
- Publisher(s): McGraw Hill Computing
- ISBN: 9780071780278
You might also like
book
Oracle Database 12c Release 2 In-Memory: Tips and Techniques for Maximum Performance
Master Oracle Database 12c Release 2’s powerful In-Memory option This Oracle Press guide shows, step-by-step, how …
book
Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach
Performance problems are rarely "problems" per se. They are more often "crises" during which you're pressured …
book
Oracle Database 11gR2 Performance Tuning Cookbook
Shifting your Oracle Database into top gear takes a lot of know-how and fine-tuning ability. The …
book
Oracle Database 12c New Features
Maximize the New and Improved Features of Oracle Database 12 c Written by Master Principle Database …