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