ACKNOWLEOGMENTS INTROOUCTION PART I OCP Tuning Exam Guide 1 Database Tuning Overview How This Book Is Organized Topics Covered in the OCP OBA Tuning Exam The Oracle Performance Tuning Methodoiogy Diagnostics and Tuning Tools Sizing the Shared Pool Sizing the Buffer Cacbe Sizing Other SGA Structures Database Configuration and I/O lssues Optimizing Sort Operdtions Didgnosing Contention for Latches Tuning Rollback or UNDO Segments Monitoring and Detecting Lock Contention Tuning Oracle Sbared Server Application Tuning Using Oracle Blocks Efficiently SQL Statement Tuning OS Considerations Tuning Overview Why Do We Performance Tune? What Is Performance Tuning? Where Do I Tune7 How Do I Performance Tune? When Do I Tune? How Do I Know If My Performance Tuning Efforts Are Actually Working? To Advance Your Tuning Knowledge 2 Oracle Performance Tuning Methodology Oracle Performance Tuning Method Describe the Roles Associated with the Database Tuning Process Describe the Dependency Between Tuning in Different Deve1opment Phases Describe SLAs Describe the Tuning Goals Describe the Most Common Tuning Problems Describe Tuning Goals Associated with Different Types of Applications Performance and Safety Trade-Offs Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions 3 Diagnostic and Tuning Tools Diagnostic and Tuning Tools The Data Dictionary and Dynamic Performance Views Related to Tuning Data Dictionary Views How the ANALYZE Command Is Used with Data Dictionary Views Dynamic Performance Views Current State Views Accumulator Views Informational Views Statistics in the Dynamic Performance Views Instance/Database Performance Views Memory Disk Contention Session-Related What Is Statspack? Installing and Configuring Statspack Interactive or Batch Installations Statspack Maintenance Configuring Statspack Statistics Gathering Collect Statistics Using Statspack Take a Snapshot Automating Snapshots Producing a Performance Report Contents of the Statspack Performance Report Other Tools That Can Be Used for Tuning UTLBSTATUTLESTAT OEM Oracle Expert Custom Tools The A1ert Log and Trace Files Instance Alert Log Trace User SQL Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions 4 Sizing the Shared Pool Sizing the Shared Pool Shared Pool Concepts Measure and Tune the Library Cache Hit Ratio Size and Pin Objects in the Shared Pool Tune the Shared Pool Reserve Space Measure and Tune the Dictionary Cache Hit Ratio Describe UGA and Session Memory Considerations Setting the Large Pool Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions 5 Sizing the Buffer Cache Sizing the Buffer Cache Describe How the Buffer Cache Is Used by Different Dracle Processes Describe the Tuning lssues Related to the Buffer Cache Cetting Advice about the Buffer Cache Size Implement Dynamic SGA Allocation Monitor the Use of the Buffer Cache and the Different Poo1s Within the Buffer Cache Create and Size Multiple Buffer Pools Make Appropriate Use of Table Caching Diagnose LRU Latch Contention Diagnose Freelist Contention Deprecated Buffer Cache Parameters Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions 6 Sizing Other SGA Structures Sizing Other SGA Structures Monitor and Size the Redo Log Buffer Monitor and Size the Java Pool Limit the Amount of Java Session Memory Used by a Session Configuring I/O Slaves Configuring Multiple DBW Processes Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions 7 Database Configuration and I/O lssues Database Configuration and I/O Issues Describe Reasons for Distributing Different Oracle File Types Diagnose Inappropriate Use of Tablespaces Tune Full Table Scan Operations Describe How Checkpoints Work Monitor and Tune Checkpoints Monitor and Tune Redo Logs Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions 8 Optimize Sort Operations Optimize Sort Operations Describe How Sorts Are Performed in Oracle Set Old and New Sort Parameters Describe the Operations That Cause Sorting Differentiate Between Disk and Memory Sorts Create and Monitor Temporary Tablespaces Describe Ways to Reduce Total Sorts and Disk Sorts Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions 9 Diagnosing Contention for Latches Diagnosing Contention for Latches Describe the Purpose of Latches Describe the Different Types of Latch Requests Describe How to Diagnose Contention for Latches Identify the Resources to Be Tuned to Minimize Latch Contention Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions 10 Tuning Rollback or Undo Segments Tuning Rollback/Undo Segments Use the Dynamic Performance Views to Check Rollback Segment Performance Define the Number and Size of Manual Rollback Segments Appropriately Allocate Rollback Segments to Transactions Understand and Expiain the Concept of Automatic Undo Management Create and Maintain Automatically Managed Undo Tablespace. Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions 11 Monitoring and Detecting Lock Contention Monitoring and Detecting Lock Contention Define Levels of Locking Describe PossibIe Causes of Contention Use Oracle Utilities to Detect Lock Contention Resolve Contention in an Emergency Prevent Locking Problems Recognize Oracle Errors Arising from Deadlocks Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions 12 Tuning Oracle Shared Server Tuning Oracle Shared Server Identifying Issues Associated with Managing Users in a Shared Server Environment Diagnosing and Resolving Performance Issues with Shared Server Processes Configure the Shared Server Environment to Optimize Performance Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions l3 Application Tuning Application Tuning Describe the Role of the DBA in Tuning Applicatior Explain Different Storage Structures Explain and Describe Clustering Options Explain the Different Types of Indexes Explain IOTs Describe Materialized Views and the Use of Query Rewrites List Requirements for OLTP, DSS, and Hybrid Systems Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions l4 Using Oracle Blocks Efficiently Using Oracle Blocks Efficiently Describe the Correct Usage of Extents and Oracle Blocks Explain Space Usage and the High Watermark Determine the High Watermark Recover Space from Sparsely Populated Segments Describe the Use of Oracle Block Parameters Describe and Detect Chaining and Migration of Oracle Blocks Perform Index Reorganization Monitor Indexes to Determine Usage Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions 15 SQL Statement Tuning SQL Statement Tuning Describe How the Optimizer Is Used Explain the Concept of Plan Stabi1ity. Use of Stored Outlines Describe How Hints Are Used Collect Statistics on Indexes and Tables Describe the Use of Histograms Copy Statistics Between Databases OLTP and OSS Considerations Use SQL Trace and TKPROF Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions 16 OS Considerations and Oracle Resource Manager OS Considerations Describe Different System Architectures Understand Virtual Memory and Paging Describe the Primary Steps of OS Tuning Identify Similarities Between OS and DB Tuning Explain the Difference Between a Process and a Thread Database Resource Manager Configuring Resource Management Administer Resource Manager Chapter Summary Two-Minute Drill Chapter Questions Answers to Chapter Questions PART II Practice Exams 17 Practice Exam I and 2 Practice Exam 1 Questions Answers to Practice Exam 1 Questions Practice Exam Two Practice Exam 2 Questions Answers to Practice Exam 2 Questions Glossary Index
鄂公網(wǎng)安備 42010302001612號 