SEARCH
Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.
BOOK ORACLE DATABASE PERFORMANCE TUNING GUIDE - E10821
SUMMARY
-
Items Found: 456
- Part I Performance Tuning
- 1 Performance Tuning Overview
- Introduction to Performance Tuning 1-1
- Performance Planning 1-1
- Instance Tuning 1-1
- SQL Tuning 1-4
- Introduction to Performance Tuning Features and Tools 1-4
- Automatic Performance Tuning Features 1-5
- Additional Oracle Database Tools 1-6
- Part II Performance Planning
- 2 Designing and Developing for Performance
- Oracle Methodology 2-1
- Understanding Investment Options 2-1
- Understanding Scalability 2-2
- What is Scalability? 2-2
- System Scalability 2-3
- Factors Preventing Scalability 2-4
- System Architecture 2-5
- Hardware and Software Components 2-5
- Configuring the Right System Architecture for Your Requirements 2-7
- Application Design Principles 2-9
- Simplicity In Application Design 2-10
- Data Modeling 2-10
- Table and Index Design 2-10
- Using Views 2-12
- SQL Execution Efficiency 2-13
- Implementing the Application 2-14
- Trends in Application Development 2-16
- Workload Testing, Modeling, and Implementation 2-16
- Sizing Data 2-17
- Estimating Workloads 2-17
- Application Modeling 2-18
- Testing, Debugging, and Validating a Design 2-18
- Deploying New Applications 2-19
- Rollout Strategies 2-19
- Performance Checklist 2-20
- 3 Performance Improvement Methods
- The Oracle Performance Improvement Method 3-1
- Steps in The Oracle Performance Improvement Method 3-2
- A Sample Decision Process for Performance Conceptual Modeling 3-3
- Top Ten Mistakes Found in Oracle Systems 3-4
- Emergency Performance Methods 3-6
- Steps in the Emergency Performance Method 3-6
- Part III Optimizing Instance Performance
- 4 Configuring a Database for Performance
- Performance Considerations for Initial Instance Configuration 4-1
- Initialization Parameters 4-1
- Configuring Undo Space 4-3
- Sizing Redo Log Files 4-3
- Creating Subsequent Tablespaces 4-4
- Creating and Maintaining Tables for Optimal Performance 4-5
- Table Compression 4-5
- Reclaiming Unused Space 4-6
- Indexing Data 4-7
- Performance Considerations for Shared Servers 4-7
- Identifying Contention Using the Dispatcher-Specific Views 4-8
- Identifying Contention for Shared Servers 4-9
- 5 Automatic Performance Statistics
- Overview of Data Gathering 5-1
- Database Statistics 5-2
- Operating System Statistics 5-4
- Interpreting Statistics 5-7
- Overview of the Automatic Workload Repository 5-8
- Snapshots 5-9
- Baselines 5-9
- Adaptive Thresholds 5-10
- Space Consumption 5-12
- Managing the Automatic Workload Repository 5-12
- Managing Snapshots 5-13
- Managing Baselines 5-14
- Managing Baseline Templates 5-17
- Transporting Automatic Workload Repository Data 5-19
- Using Automatic Workload Repository Views 5-21
- Generating Automatic Workload Repository Reports 5-22
- Generating Automatic Workload Repository Compare Periods Reports 5-28
- Generating Active Session History Reports 5-34
- Using Active Session History Reports 5-38
- 6 Automatic Performance Diagnostics
- Overview of the Automatic Database Diagnostic Monitor 6-1
- ADDM Analysis 6-2
- Using ADDM with Oracle Real Application Clusters 6-3
- ADDM Analysis Results 6-4
- Reviewing ADDM Analysis Results: Example 6-5
- Setting Up ADDM 6-5
- Diagnosing Database Performance Problems with ADDM 6-6
- Running ADDM in Database Mode 6-7
- Running ADDM in Instance Mode 6-7
- Running ADDM in Partial Mode 6-8
- Displaying an ADDM Report 6-8
- Views with ADDM Information 6-9
- 7 Configuring and Using Memory
- Understanding Memory Allocation Issues 7-1
- Oracle Memory Caches 7-2
- Automatic Memory Management 7-2
- Automatic Shared Memory Management 7-2
- Dynamically Changing Cache Sizes 7-3
- Application Considerations 7-5
- Operating System Memory Use 7-5
- Iteration During Configuration 7-6
- Configuring and Using the Buffer Cache 7-6
- Using the Buffer Cache Effectively 7-7
- Sizing the Buffer Cache 7-7
- Interpreting and Using the Buffer Cache Advisory Statistics 7-10
- Considering Multiple Buffer Pools 7-11
- Buffer Pool Data in V$DB_CACHE_ADVICE 7-13
- Buffer Pool Hit Ratios 7-13
- Determining Which Segments Have Many Buffers in the Pool 7-13
- KEEP Pool 7-15
- RECYCLE Pool 7-15
- Configuring and Using the Shared Pool and Large Pool 7-16
- Shared Pool Concepts 7-17
- Using the Shared Pool Effectively 7-19
- Sizing the Shared Pool 7-22
- Interpreting Shared Pool Statistics 7-27
- Using the Large Pool 7-28
- Using CURSOR_SPACE_FOR_TIME 7-31
- Caching Session Cursors 7-31
- Configuring the Reserved Pool 7-33
- Keeping Large Objects to Prevent Aging 7-35
- Sharing Cursors for Existing Applications 7-36
- Maintaining Connections 7-37
- Configuring and Using the Redo Log Buffer 7-38
- Sizing the Log Buffer 7-38
- Log Buffer Statistics 7-39
- PGA Memory Management 7-39
- Configuring Automatic PGA Memory 7-40
- Configuring OLAP_PAGE_POOL_SIZE 7-53
- Managing the Server and Client Result Caches 7-53
- Managing the Server Result Cache 7-54
- Managing the Client Result Cache 7-57
- Specifying Queries for Result Caching 7-59
- Requirements for the Result Cache 7-62
- Accessing Result Cache Information 7-63
- 8 I/O Configuration and Design
- About I/O 8-1
- I/O Configuration 8-2
- Lay Out the Files Using Operating System or Hardware Striping 8-2
- Manually Distributing I/O 8-5
- When to Separate Files 8-5
- Three Sample Configurations 8-7
- Oracle Managed Files 8-8
- Choosing Data Block Size 8-9
- I/O Calibration Inside the Database 8-10
- Prerequisites for I/O Calibration 8-10
- Running I/O Calibration 8-11
- I/O Calibration with the Oracle Orion Calibration Tool 8-12
- Introduction to the Oracle Orion Calibration Tool 8-12
- Getting Started with Orion 8-14
- Orion Input Files 8-15
- Orion Parameters 8-15
- Orion Output Files 8-19
- Orion Troubleshooting 8-23
- 9 Managing Operating System Resources
- Understanding Operating System Performance Issues 9-1
- Using Operating System Caches 9-2
- Memory Usage 9-3
- Using Operating System Resource Managers 9-4
- Resolving Operating System Issues 9-5
- Performance Hints on UNIX-Based Systems 9-5
- Performance Hints on Windows Systems 9-5
- Performance Hints on HP OpenVMS Systems 9-6
- Understanding CPU 9-6
- Resolving CPU Issues 9-7
- Finding and Tuning CPU Utilization 9-8
- Managing CPU Resources Using Oracle Database Resource Manager 9-10
- Managing CPU Resources Using Instance Caging 9-11
- 10 Instance Tuning Using Performance Views
- Instance Tuning Steps 10-1
- Define the Problem 10-2
- Examine the Host System 10-3
- Examine the Oracle Database Statistics 10-6
- Implement and Measure Change 10-10
- Interpreting Oracle Database Statistics 10-11
- Examine Load 10-11
- Using Wait Event Statistics to Drill Down to Bottlenecks 10-12
- Table of Wait Events and Potential Causes 10-13
- Additional Statistics 10-15
- Wait Events Statistics 10-17
- buffer busy waits 10-19
- db file scattered read 10-21
- db file sequential read 10-22
- direct path read and direct path read temp 10-24
- direct path write and direct path write temp 10-25
- enqueue (enq:) waits 10-25
- events in wait class other 10-28
- free buffer waits 10-28
- Idle Wait Events 10-30
- latch events 10-30
- log file parallel write 10-35
- library cache pin 10-35
- library cache lock 10-35
- log buffer space 10-35
- log file switch 10-35
- log file sync 10-36
- rdbms ipc reply 10-36
- SQL*Net Events 10-37
- Real-Time SQL Monitoring 10-38
- SQL Plan Monitoring 10-39
- Parallel Execution Monitoring 10-39
- Generating the SQL Monitor Report 10-39
- Enabling and Disabling SQL Monitoring 10-42
- Tuning Instance Recovery Performance: Fast-Start Fault Recovery 10-42
- About Instance Recovery 10-42
- Configuring the Duration of Cache Recovery: FAST_START_MTTR_TARGET 10-43
- Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor 10-46
- Part IV Optimizing SQL Statements
- 11 The Query Optimizer
- Overview of the Query Optimizer 11-1
- Optimizer Operations 11-1
- Components of the Query Optimizer 11-3
- Bind Variable Peeking 11-8
- Overview of Optimizer Access Paths 11-13
- Full Table Scans 11-13
- Rowid Scans 11-15
- Index Scans 11-15
- Cluster Access 11-21
- Hash Access 11-21
- Sample Table Scans 11-21
- How the Query Optimizer Chooses an Access Path 11-21
- Overview of Joins 11-22
- How the Query Optimizer Executes Join Statements 11-22
- How the Query Optimizer Chooses Execution Plans for Joins 11-23
- Nested Loop Joins 11-23
- Hash Joins 11-26
- Sort Merge Joins 11-27
- Cartesian Joins 11-28
- Outer Joins 11-28
- Reading and Understanding Execution Plans 11-32
- Overview of EXPLAIN PLAN 11-32
- Steps in the Execution Plan 11-33
- Controlling Optimizer Behavior 11-34
- Enabling Query Optimizer Features 11-35
- Choosing an Optimizer Goal 11-36
- 12 Using EXPLAIN PLAN
- Understanding EXPLAIN PLAN 12-1
- How Execution Plans Can Change 12-2
- Minimizing Throw-Away 12-2
- Looking Beyond Execution Plans 12-3
- EXPLAIN PLAN Restrictions 12-4
- The PLAN_TABLE Output Table 12-4
- Running EXPLAIN PLAN 12-4
- Identifying Statements for EXPLAIN PLAN 12-5
- Specifying Different Tables for EXPLAIN PLAN 12-5
- Displaying PLAN_TABLE Output 12-5
- Customizing PLAN_TABLE Output 12-6
- Reading EXPLAIN PLAN Output 12-6
- Viewing Parallel Execution with EXPLAIN PLAN 12-7
- Viewing Parallel Queries with EXPLAIN PLAN 12-9
- Viewing Bitmap Indexes with EXPLAIN PLAN 12-9
- Viewing Result Cache with EXPLAIN PLAN 12-10
- Viewing Partitioned Objects with EXPLAIN PLAN 12-11
- Examples of Displaying Range and Hash Partitioning with EXPLAIN PLAN 12-11
- Examples of Pruning Information with Composite Partitioned Objects 12-12
- Examples of Partial Partition-Wise Joins 12-14
- Examples of Full Partition-wise Joins 12-15
- Examples of INLIST ITERATOR and EXPLAIN PLAN 12-16
- Example of Domain Indexes and EXPLAIN PLAN 12-17
- PLAN_TABLE Columns 12-17
- 13 Managing Optimizer Statistics
- Overview of Optimizer Statistics 13-1
- Managing Automatic Optimizer Statistics Collection 13-2
- Enabling and Disabling Automatic Optimizer Statistics Collection 13-2
- Considerations When Gathering Statistics 13-3
- Gathering Statistics Manually 13-5
- Gathering Statistics with DBMS_STATS Procedures 13-5
- Setting Preferences for Manual Statistics Gathering 13-13
- When to Gather Statistics 13-14
- Comparing Statistics with DBMS_STATS Functions 13-15
- System Statistics 13-15
- Workload Statistics 13-16
- Noworkload Statistics 13-17
- Managing Statistics 13-18
- Pending Statistics 13-18
- Restoring Previous Versions of Statistics 13-19
- Exporting and Importing Statistics 13-20
- Restoring Statistics Versus Importing or Exporting Statistics 13-21
- Locking Statistics for a Table or Schema 13-21
- Setting Statistics 13-21
- Handling Missing Statistics 13-21
- Estimating Statistics with Dynamic Sampling 13-22
- Purpose of Dynamic Sampling 13-22
- Dynamic Sampling Concepts 13-22
- Setting Dynamic Sampling Levels 13-24
- Viewing Statistics 13-26
- Statistics on Tables, Indexes and Columns 13-26
- Viewing Histograms 13-26
- 14 Using Indexes and Clusters
- Understanding Index Performance 14-1
- Tuning the Logical Structure 14-1
- Index Tuning using the SQLAccess Advisor 14-2
- Choosing Columns and Expressions to Index 14-2
- Choosing Composite Indexes 14-3
- Writing Statements That Use Indexes 14-4
- Writing Statements That Avoid Using Indexes 14-4
- Re-creating Indexes 14-5
- Compacting Indexes 14-5
- Using Nonunique Indexes to Enforce Uniqueness 14-6
- Using Enabled Novalidated Constraints 14-6
- Using Function-based Indexes for Performance 14-7
- Using Partitioned Indexes for Performance 14-8
- Using Index-Organized Tables for Performance 14-8
- Using Bitmap Indexes for Performance 14-9
- Using Bitmap Join Indexes for Performance 14-9
- Using Domain Indexes for Performance 14-9
- Using Table Clusters for Performance 14-10
- Using Hash Clusters for Performance 14-11
- 15 Using SQL Plan Management
- Overview of SQL Plan Baselines 15-1
- Purpose of SQL Plan Baselines 15-1
- Architecture of SQL Plan Baselines 15-2
- Managing SQL Plan Baselines 15-3
- Capturing SQL Plan Baselines 15-3
- Selecting SQL Plan Baselines 15-5
- Evolving SQL Plan Baselines 15-6
- Using SQL Plan Baselines with SQL Tuning Advisor 15-7
- Using Fixed SQL Plan Baselines 15-8
- Displaying SQL Plan Baselines 15-8
- SQL Management Base 15-10
- Disk Space Usage 15-10
- Purging Policy 15-10
- SQL Management Base Configuration Parameters 15-11
- Importing and Exporting SQL Plan Baselines 15-11
- Migrating Stored Outlines to SQL Plan Baselines 15-12
- Overview of Stored Outline Migration 15-12
- Preparing for Stored Outline Migration 15-17
- Migrating Outlines to Utilize SQL Plan Management Features 15-18
- Migrating Outlines to Preserve Stored Outline Behavior 15-19
- Performing Follow-Up Tasks After Stored Outline Migration 15-20
- 16 SQL Tuning Overview
- Introduction to SQL Tuning 16-1
- Goals for Tuning 16-1
- Reduce the Workload 16-2
- Balance the Workload 16-2
- Parallelize the Workload 16-2
- Identifying High-Load SQL 16-2
- Identifying Resource-Intensive SQL 16-2
- Gathering Data on the SQL Identified 16-4
- Automatic SQL Tuning Features 16-5
- ADDM 16-5
- SQL Tuning Advisor 16-5
- SQL Tuning Sets 16-5
- SQL Access Advisor 16-5
- Developing Efficient SQL Statements 16-5
- Verifying Optimizer Statistics 16-6
- Reviewing the Execution Plan 16-6
- Restructuring the SQL Statements 16-7
- Controlling the Access Path and Join Order with Hints 16-9
- Restructuring the Indexes 16-12
- Modifying or Disabling Triggers and Constraints 16-12
- Restructuring the Data 16-12
- Maintaining Execution Plans Over Time 16-13
- Visiting Data as Few Times as Possible 16-13
- Building SQL Test Cases 16-14
- Creating a Test Case 16-15
- 17 Automatic SQL Tuning
- Overview of the Automatic Tuning Optimizer 17-1
- Statistics Analysis 17-2
- SQL Profiling 17-2
- Access Path Analysis 17-2
- SQL Structure Analysis 17-3
- Alternative Plan Analysis 17-3
- Managing the Automatic SQL Tuning Advisor 17-5
- How Automatic SQL Tuning Works 17-5
- Enabling and Disabling Automatic SQL Tuning 17-6
- Configuring Automatic SQL Tuning 17-7
- Viewing Automatic SQL Tuning Reports 17-8
- Tuning Reactively with SQL Tuning Advisor 17-9
- Input Sources 17-9
- Tuning Options 17-10
- Advisor Output 17-10
- Running SQL Tuning Advisor 17-10
- Managing SQL Tuning Sets 17-15
- Creating a SQL Tuning Set 17-16
- Loading a SQL Tuning Set 17-17
- Displaying the Contents of a SQL Tuning Set 17-17
- Modifying a SQL Tuning Set 17-18
- Transporting a SQL Tuning Set 17-18
- Dropping a SQL Tuning Set 17-19
- Additional Operations on SQL Tuning Sets 17-19
- Managing SQL Profiles 17-19
- Overview of SQL Profiles 17-20
- Accepting a SQL Profile 17-24
- Altering a SQL Profile 17-25
- Dropping a SQL Profile 17-25
- Transporting a SQL Profile 17-25
- SQL Tuning Views 17-26
- 18 SQL Access Advisor
- Overview of SQL Access Advisor 18-1
- Overview of Using SQL Access Advisor 18-3
- Using SQL Access Advisor 18-5
- Steps for Using SQL Access Advisor 18-5
- Privileges Needed to Use SQL Access Advisor 18-6
- Setting Up Tasks and Templates 18-6
- SQL Access Advisor Workloads 18-8
- Working with Recommendations 18-9
- Performing a Quick Tune 18-21
- Managing Tasks 18-22
- Using SQL Access Advisor Constants 18-23
- Examples of Using SQL Access Advisor 18-23
- Tuning Materialized Views for Fast Refresh and Query Rewrite 18-28
- DBMS_ADVISOR TUNE_MVIEW Procedure 18-28
- 19 Using Optimizer Hints
- Overview of Optimizer Hints 19-1
- Types of Hints 19-1
- Hints by Category 19-2
- Specifying Hints 19-8
- Specifying a Full Set of Hints 19-8
- Specifying a Query Block in a Hint 19-8
- Specifying Global Table Hints 19-10
- Specifying Complex Index Hints 19-11
- Using Hints with Views 19-12
- Hints and Complex Views 19-13
- Hints and Mergeable Views 19-13
- Hints and Nonmergeable Views 19-14
- 20 Using Plan Stability
- Using Plan Stability to Preserve Execution Plans 20-1
- Using Hints with Plan Stability 20-2
- Storing Outlines 20-3
- Enabling Plan Stability 20-3
- Using Supplied Packages to Manage Stored Outlines 20-3
- Creating Outlines 20-4
- Using Stored Outlines 20-5
- Viewing Outline Data 20-6
- Moving Outline Tables 20-6
- Using Plan Stability with Query Optimizer Upgrades 20-8
- Moving from RBO to the Query Optimizer 20-8
- Moving to a New Oracle Release under the Query Optimizer 20-9
- 21 Using Application Tracing Tools
- End to End Application Tracing 21-1
- Enabling and Disabling Statistic Gathering for End to End Tracing 21-2
- Viewing Gathered Statistics for End to End Application Tracing 21-3
- Enabling and Disabling for End-to-End Tracing 21-4
- Viewing Enabled Traces for End to End Tracing 21-6
- Using the trcsess Utility 21-6
- Syntax for trcsess 21-7
- Sample Output of trcsess 21-7
- Understanding SQL Trace and TKPROF 21-8
- Understanding the SQL Trace Facility 21-8
- Understanding TKPROF 21-9
- Using the SQL Trace Facility and TKPROF 21-9
- Step 1: Setting Initialization Parameters for Trace File Management 21-9
- Step 2: Enabling the SQL Trace Facility 21-11
- Step 3: Formatting Trace Files with TKPROF 21-12
- Step 4: Interpreting TKPROF Output 21-15
- Step 5: Storing SQL Trace Facility Statistics 21-20
- Avoiding Pitfalls in TKPROF Interpretation 21-22
- Avoiding the Argument Trap 21-22
- Avoiding the Read Consistency Trap 21-22
- Avoiding the Schema Trap 21-23
- Avoiding the Time Trap 21-24
- Sample TKPROF Output 21-24
- Sample TKPROF Header 21-24
- Sample TKPROF Body 21-24
- Sample TKPROF Summary 21-27
- Glossary
- Index
