1 / 6

Preface 1 Performance Tuning Methodology: A Review Course Structure 1-2 Lesson Objective 1-3

Contents. Preface 1 Performance Tuning Methodology: A Review Course Structure 1-2 Lesson Objective 1-3 Concepts 1-4 Determining the Worst Bottleneck 1-5 Understanding Workload Characteristics 1-6 A Simple Methodology 1-7 State the Problem 1-9 Find the Problem Stack 1-15

anana
Download Presentation

Preface 1 Performance Tuning Methodology: A Review Course Structure 1-2 Lesson Objective 1-3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Contents • Preface • 1 Performance Tuning Methodology: A Review • Course Structure 1-2 • Lesson Objective 1-3 • Concepts 1-4 • Determining the Worst Bottleneck 1-5 • Understanding Workload Characteristics 1-6 • A Simple Methodology 1-7 • State the Problem 1-9 • Find the Problem Stack 1-15 • Drill Down and Find the Worst Bottleneck 1-19 • Fix It 1-21 • Test Against the Baseline 1-22 • Repeat Until You Hit Your Goal 1-23 • Detecting Performance Problems 1-24 • Database Server Stack 1-25 • Database Server Tuning 1-26 • Summary 1-27 • 2 Enqueue Structures • Objectives 2-2 • Introduction to Oracle Lock Types 2-3 • Enqueues 2-4 • Enqueue Identifiers 2-5 • Enqueue Modes 2-6 • Enqueue Compatibility 2-7 • Resources and Locks 2-8 • Resource Structure 2-10 • Client and Managed Enqueues 2-11 • State Objects 2-13 • Hashing and Latching 2-14 • Resource Free List 2-17 • Initialization Parameters 2-18 • Lock Acquisition 2-19 • Lock Conversion 2-20 • Lock Release 2-21 • Enqueue Operations 2-22 • Enqueue Operations Example 2-23 • Wait Event: Enqueue 2-25 • Tuning Enqueue Waits 2-27 • Deadlock Detection 2-28 • Diagnostics: State Object Dump 2-30 • Diagnostics: Enqueues Dump 2-34 • Diagnostics: V$LOCK 2-36 • Diagnostics: V$RESOURCE 2-37 iii

  2. Diagnostics: V$LOCKED_OBJECT 2-38 • Diagnostics: Scripts 2-39 • Summary 2-41 • References 2-42 • Understanding Lock Contention • Objectives 3-2 • Data Dictionary Locks 3-3 • Row Cache Locks 3-4 • Wait Event: Row Cache Lock 3-5 • Library Cache Locks 3-6 • Wait Event: Library Cache Lock 3-7 • Library Cache Pins 3-8 • Wait Event: Library Cache Pin 3-9 • DML Locks 3-10 • DML Table Locks: Implementation 3-11 • Disabling DML Table Locks 3-12 • DML Row Locks 3-14 • Row-Level Locks 3-15 • Row-Level Conflict 3-16 • Resolving Row-Level Conflict 3-18 • ITL Contention 3-20 • Transaction Locks 3-21 • Transaction Identifiers 3-22 • Transaction Table Dump 3-24 • Finding the Row That Is Locked 3-25 • Buffer Locks 3-26 • Wait Event: Buffer Busy Waits 3-27 • Buffer Lock Contention 3-29 • Data Block Contention 3-31 • Undo Segment Contention 3-33 • Diagnosing Undo Segment Header Contention 3-34 • Index Block Contention 3-36 • Free List Contention 3-37 • Resolving Free List Contention 3-38 • Wait Event: Write Complete Waits 3-39 • Sort Locks 3-40 • ORA-1575 3-41 • SMON Functions 3-42 • Coalescing Free Space 3-43 • Disabling Background Coalescing 3-44 • Temporary Segment Cleanup 3-45 • Disabling Temporary Segment Cleanup 3-46 • Summary 3-47 • References 3-48 iv

  3. Latch Internals • Objectives 4-2 • Purpose of Latches 4-3 • Latch Characteristics 4-4 • Latch Implementation 4-5 • Oracle Latch Implementation 4-6 • Latch Attributes 4-7 • Latch Levels 4-9 • Latch Declaration 4-10 • Internal Latch Structures 4-11 • Latch Interface Routines: Examples 4-12 • Waiting for a Latch 4-13 • Latch Acquisition in No-Wait Mode 4-15 • Latch Acquisition in Wait Mode 4-16 • Latch Release 4-18 • Latch Cleanup Function 4-19 • Wait Event: Latch Free Example 4-20 • Using V$ Views 4-30 • Latch Contention 4-32 • Tuning the Spin Count 4-33 • Highly Used Latches 4-34 • Diagnostics: Latch Views 4-36 • Diagnostics: Latches Dump 4-38 • Latch Contention Diagnosis: Example 4-40 • Latch Monitoring Summary 4-45 • Summary 4-46 • Reference 4-47 • Memory Management • Objectives 5-2 • Memory Areas 5-3 • System Global Area 5-4 • Dynamic SGA Memory Allocation 5-5 • Granule Size 5-7 • Granule Memory Manager 5-8 • SGA and Shared Memory 5-10 • Configuring Shared Memory 5-12 • SGA Shared Memory Allocation 5-14 • Fixed Area 5-16 • Database Block Buffers 5-17 • Redo Buffers 5-19 • Variable Area 5-20 • Heaps and Subheaps 5-21 • More on Subheaps 5-22 • The Shared Pool 5-23 v

  4. Allocation Classes 5-24 • Allocation Classes Viewed in X$KSMSP 5-26 • Chunk Allocation: Free Lists 5-27 • Chunk Allocation: LRU Lists 5-29 • Chunk Allocation: Hidden Free Memory 5-31 • The Reserved Pool 5-32 • Keeping Objects 5-33 • Flushing the Shared Pool 5-34 • The Large Pool 5-35 • The Java Pool 5-36 • Process Global Area 5-37 • User Global Area 5-38 • Call Global Area 5-39 • Heap Dumps 5-40 • Subheap Dumps 5-42 • Summary 5-43 • References 5-44 • 6 The Buffer Cache • Objectives 6-2 • The Kernel Buffer Cache 6-3 • Buffer Cache 6-4 • Kernel Layout 6-5 • Source Code 6-6 • Basic Terminology 6-7 • Hash Buckets and Chains 6-8 • Overview of Buffer Cache 6-9 • Buffer Header (kcbbh) 6-10 • Multiple Buffer Pools 6-11 • Buffer Pool (kcbwbpd) 6-12 • LRU 6-13 • Multiple LRU lists 6-14 • LRU Lists 6-15 • Working Sets (kcbwds) 6-17 • Working Sets 6-18 • Buffer Cache Users 6-19 • Buffer Get 6-20 • Buffer Gets 6-21 • Getting Current Buffers 6-22 • Buffer Management 6-23 • Touch Count 6-25 • Write Priority 6-27 • DBWR and Checkpoints 6-28 • Working Sets and DBWR 6-29 • DBWR Invocation 6-30 • DBWR: Make Clean Buffers 6-31 vi

  5. Checkpoints 6-33 • DBWR: LGWR Invocation 6-34 • Checkpoint and File Queues 6-35 • Checkpoint Queues 6-37 • Checkpoint Queue Management 6-38 • DBWR: Flush DBA Range 6-39 • DBWR: Flush Invalidated Range 6-40 • Buffer Cache Events 6-41 • Buffer Cache Statistics 6-43 • DBWR Statistics 6-45 • Buffer Cache Latches 6-46 • Buffer Cache Fixed Tables 6-48 • Tuning the Buffer Cache 6-49 • Buffer Busy Waits 6-51 • Free Buffer Waits 6-53 • Initialization Parameters 6-54 • Additional Tips 6-56 • Summary 6-57 • References 6-58 • 7 The Library Cache 7-1 • Objectives 7-2 • The Library Cache 7-3 • Library Cache Objects 7-4 • Shared Cursors 7-5 • Library Cache Architecture 7-7 • Hash Buckets 7-8 • Object Handles 7-9 • Heap 0 (Object) 7-10 • Object Types 7-11 • Object Names 7-13 • Object Flags 7-14 • Object Tables 7-16 • Object Data Blocks 7-18 • Library Cache Object 7-19 • Object Heaps 7-20 • Locks and Pins 7-22 • Lock and Pin Persistence 7-23 • Lock Modes 7-24 • Lock Compatibility 7-25 • Pin Modes 7-26 • Library Cache Latches 7-27 • Causes of Library Cache Contention 7-29 • Sharing Cursors 7-31 • Checking for Cursor Sharing 7-32 • Diagnostics: V$LIBRARYCACHE 7-34 vii

  6. Diagnostics: Library Cache Dump 7-36 • Library Cache Dump Interpretation 7-37 • Diagnostics: V$SGASTAT 7-42 • Diagnostics: V$SQLAREA 7-44 • Other Tuning Tips 7-45 • Library Cache Contention Diagnosis 7-47 • Latch Contention Diagnostics 7-48 • Summary 7-49 • References 7-50 • 8 Redo and Archiving 8-1 • Objectives 8-2 • Redo Architecture Concepts 8-3 • Logging Methods 8-4 • Oracle9i Architecture Diagram 8-6 • Redo Logging in the Oracle Server 8-7 • Page Fix Rule 8-8 • Write-Ahead Logging 8-9 • Log Force at Commit 8-10 • Logical Ordering of Redo 8-11 • Redo Log 8-12 • Redo Byte Address 8-13 • Redo Log Buffer 8-14 • Redo Generation 8-16 • Writes to Redo Log Files 8-19 • LGWR Algorithm 8-21 • Redo Wait Events 8-23 • Redo Statistics 8-25 • Redo Latches 8-27 • Optimizing the Redo Buffer 8-28 • Tuning Redo Latch Contention 8-30 • Redo-less Operations 8-32 • NOLOGGING Performance 8-33 • Redo with NOLOGGING 8-34 • Archiving 8-35 • Events that Post the Archiver 8-36 • ARCH Process Flow 8-37 • Archiver Operations 8-39 • Tuning ARCH 8-40 • Multiple Archive Log Processes 8-43 • Archive Problem Solving 8-44 • Summary 8-46 • References 8-47 • Appendix A • Appendix B viii

More Related