Oracle Space Management Handbook

Cover......Page 1 Table of Contents......Page 6 Conventions Used in this Book......Page 16 About the Authors......Page 18 Foreword......Page 21 Theory......Page 23 Test It......Page 24 What Happens When We Update?......Page 26 So What?......Page 27 Setting Free Your Space by John Weeg......Page 28 Alter Database......Page 29 Double Checking the Work......Page 31 Oracle tuning and Disk I/O......Page 34 Team DDU......Page 35 Locating Tables and Indexes for the KEEP Pool......Page 36 Using Multiple Block Sizes......Page 38 Disk I/O Tuning......Page 41 STATSPACK Reports for Oracle Datafiles......Page 42 Conclusion......Page 44 Digging at the Segment Level : Performance Diagnosis Reaches A Deeper Level by Arup Nanda......Page 46 Background / Overview......Page 47 Setting the Statistics Levels......Page 48 Segment Level Statistics Collection......Page 50 Column Explanation......Page 51 Improvements......Page 52 Case Study......Page 53 Solution......Page 56 Conclusion......Page 57 Physical Database Design 101......Page 59 What is Physical Database Design?......Page 60 Database Block Structure......Page 61 Block Size......Page 62 Freelists......Page 63 Extents......Page 64 AutoExtension......Page 65 Partitioning......Page 66 Index Compression......Page 68 Index Organized Tables (IOT's)......Page 69 Insert Times......Page 70 Application Impact......Page 72 Online Table Reorganization......Page 73 Temporary Tables......Page 74 Application Impact......Page 76 Locally Managed Tablespaces......Page 77 Transportable Tablespaces......Page 78 Conclusions......Page 80 Problem......Page 82 Parsing This String......Page 83 Bring It In......Page 84 Use It......Page 86 Use it Elsewhere......Page 87 A Brief History of Screen Generators......Page 88 What Does It Cost to Update a Column?......Page 89 But There's More......Page 91 Triggers......Page 92 Indexes......Page 93 Referential Integrity......Page 94 There's Always a Trade-off......Page 95 Conclusion......Page 96 What is ITL?......Page 97 What Is an ITL Wait......Page 98 Simulation......Page 101 How to Reduce ITL Waits......Page 102 How to Diagnose the ITL Wait......Page 103 What INITRANS Value is Optimal......Page 105 Automatic Block Management in Oracle9i......Page 107 Conclusion......Page 108 Stray Temporary Segments......Page 109 Manual Cleanup of Temporary Segments......Page 112 Recommendations Regarding Temporary Segments......Page 114 Recommendations Regarding Rollback Segments......Page 115 Overview of the Package......Page 116 Setup......Page 118 Don't Wait to Create......Page 120 Don't Recover......Page 121 Don't Add Overhead......Page 122 Give It a Try......Page 123 Where Are My TEMP Tablespaces?......Page 124 Who Are the Users?......Page 125 A Happy Ending......Page 127 Introduction......Page 128 Test Environment......Page 129 Goals......Page 130 Examples......Page 131 Instance Parameter Management......Page 132 Memory Made Simple......Page 134 PGA Aggregate Target......Page 135 Cache Advice......Page 137 Background......Page 139 Rollback Segments......Page 140 The Oracle9i Solution......Page 141 Database Resource Manager......Page 142 Unused Index Identification......Page 143 Oracle Managed Files......Page 145 Conclusions......Page 147 Tablespaces Past and Present......Page 148 The Past......Page 149 The Present......Page 150 Where Are the Benefits?......Page 153 Conclusion......Page 160 Using Multiple Block Sizes in Oracle9i by Don Burleson......Page 161 Allocating Objects into Multiple Block Buffers......Page 166 Tools for Viewing Data Buffer Usage......Page 169 Creating Separate Data Buffers......Page 170 Conclusion......Page 171 Automated Table/Index Reorganization In Oracle8i by Mike Hordila......Page 172 Possible Reorganizing Strategies......Page 173 Some Procedures Related to Table Reorganization......Page 174 Important Issues Regarding Table/Index Moving/Rebuilding......Page 175 Limitations of the "ALTER TABLE MOVE" Command:......Page 177 Manual Object Reorganization......Page 178 Step 2......Page 179 Automated Object Reorganization......Page 181 Associated Tables......Page 182 Overview of the Package......Page 183 Setup......Page 186 External Tables in Oracle9i by Dave Moore......Page 187 Example......Page 188 Limitations......Page 192 Performance......Page 193 Practical Applications......Page 195 Database Administration......Page 196 External Tables......Page 198 Tablespace Changes......Page 202 Online Table Reorganizations......Page 207 Index Monitoring......Page 210 Rebuild in the same Tablespace......Page 213 No Fragment......Page 214 More Than One......Page 215 Break Points......Page 216 Conclusion......Page 217 B-tree Theory......Page 219 Estimate Leafs......Page 220 Estimate Branches......Page 221 Making the Index......Page 222 Dba_Indexes......Page 223 Logical Steps for Resizing and Defragging......Page 225 All Together Now......Page 228 Introduction......Page 230 Partitioning Defined......Page 231 When To Partition......Page 232 Different Methods of Partitioning......Page 233 Range Partitioning......Page 234 Hash Partitioning......Page 235 Composite Range-Hash Partitioning......Page 236 Composite Range-List Partitioning......Page 237 Conclusion......Page 238 Background......Page 239 Globally Partitioned Indexes......Page 240 Locally Partitioned Indexes......Page 243 Real Life Example......Page 247 Conclusion......Page 248 Plan Table Revisited......Page 249 The New Tool DBMS_XPLAN......Page 250 Partition Pruning or Elimination......Page 253 Partition-wise Joins......Page 257 Character Value in Range Partitioning......Page 262 Multi-Column Partition Keys......Page 265 GRANULARITY......Page 270 Coalesce vs Merge......Page 274 While using partitioning, should you use bind variables?......Page 276 How many partitions can be defined on a table?......Page 277 Introduction......Page 278 Why Consider Oracle Multi-master Replication?......Page 279 Multi-master Conflicts and Resolutions......Page 280 Conflict Types......Page 281 Conflict Resolution Mechanisms......Page 282 Implementing Procedural Multi-master Replication......Page 283 Phase I: Pre-configuration Steps for Multi-master Replication......Page 284 Phase II: Set-up REPADMIN User and Database Links......Page 285 Phase III: Create the Master Database and Refresh Groups......Page 286 Phase IV: Monitoring Multi-master Replication......Page 287 Resources for Defining Multi-master Replication......Page 289 References......Page 290 Basic Replication......Page 292 Prerequisites......Page 294 Overview of the Package......Page 295 Setup......Page 297 Test Environment......Page 299 Altering the Master Table in a Snapshot Replication Environment without Recreating the Snapshot by Arup Nanda......Page 301 Background......Page 302 The Usual Method......Page 303 Detailed Steps......Page 305 Conclusion......Page 308 Index......Page 309 Team DDU Oracle Space Management Handbook 1 Cover 1 Table of Contents 6 Conventions Used in this Book 16 About the Authors 18 Foreword 21 Section One - Datafiles 23 Chapter 1 - Measuring Oracle Segment I/O 23 What is Really Going On? by John Weeg 23 Theory 23 Test It 24 What Happens When We Update? 26 What Else? 27 So What? 27 Chapter 2 - Datafile Resizing Tips 28 Setting Free Your Space by John Weeg 28 Alter Database 29 Double Checking the Work 31 Chapter 3 - Reducing Disk I/O on Oracle Datafiles 34 Oracle Expert Tuning Secrets to reduce disk I/O by Don Burleson 34 Oracle tuning and Disk I/O 34 Tuning with RAM Data Buffers 35 The KEEP Pool 35 Locating Tables and Indexes for the KEEP Pool 36 The RECYCLE Pool 38 Using Multiple Block Sizes 38 Disk I/O Tuning 41 STATSPACK Reports for Oracle Datafiles 42 Conclusion 44 Chapter 4 - Measuring Data Segment Statistics 46 Digging at the Segment Level : Performance Diagnosis Reaches A Deeper Level by Arup Nanda 46 Background / Overview 47 Setting the Statistics Levels 48 Segment Level Statistics Collection 50 Column Explanation 51 Examining Detailed Statistics 52 Improvements 52 Case Study 53 Solution 56 Conclusion 57 Chapter 5 - Optimizing Oracle Physical Design 59 Optimal Physical Database Design for Oracle8i by Dave Ensor 59 Introduction 59 Physical Database Design 101 59 What is Physical Database Design? 60 Database Block Structure 61 Block Size 62 Unstructured Data 63 Freelists 63 Extents 64 AutoExtension 65 Partitioning 66 Index Compression 68 Index Organized Tables (IOT's) 69 Insert Times 70 Retrieval Times 72 Application Impact 72 Online Table Reorganization 73 Temporary Tables 74 Application Impact 76 Locally Managed Tablespaces 77 Transportable Tablespaces 78 Conclusions 80 Chapter 6 - Verifying Segment Backup Scripts 82 Did the Backup Work? by John Weeg 82 Problem 82 How Do We Know? 83 Parsing This String 83 Bring It In 84 Use It 86 Use it Elsewhere 87 Chapter 7 - Data Segment Update Internals 88 How Much Does an Update Cost? by Jonathan Lewis 88 A Brief History of Screen Generators 88 What Does It Cost to Update a Column? 89 But There's More 91 Triggers 92 Indexes 93 Referential Integrity 94 There's Always a Trade-off 95 Conclusion 96 Chapter 8 - Segment Transaction Slot Internals 97 Interested Transaction List (ITL) Waits Demystified by Arup Nanda 97 What is ITL? 97 What Is an ITL Wait 98 Simulation 101 How to Reduce ITL Waits 102 How to Diagnose the ITL Wait 103 What INITRANS Value is Optimal 105 Automatic Block Management in Oracle9i 107 Conclusion 108 Section Two - Tablespaces 109 Chapter 9 - Automated Space Cleanup in Oracle 109 Automated Space Cleanup in Oracle by Mike Hordila 109 Stray Temporary Segments 109 Manual Cleanup of Temporary Segments 112 Recommendations Regarding Temporary Segments 114 Locking 115 Problems with Rollback Segments 115 Recommendations Regarding Rollback Segments 115 Automated Space Cleanup 116 Prerequisites 116 Overview of the Package 116 Setup 118 Chapter 10 - Using Oracle TEMP Files 120 Temporarily Yours: Tempfiles by John Weeg 120 Don't Wait to Create 120 Don't Backup 121 Don't Recover 121 Don't Copy for Standby 122 Don't Add Overhead 122 Give It a Try 123 Chapter 11 - Monitoring TEMP Space Usage 124 Who Took All the TEMP? by John Weeg 124 Where Are My TEMP Tablespaces? 124 Show Me the Objects 125 Who Are the Users? 125 A Happy Ending 127 Chapter 12 - Oracle9i Self-Management Features 128 Oracle9i Self-Management Features: The Early Winners by Dave Ensor 128 Introduction 128 Test Environment 129 Self-Management 130 Goals 130 Examples 131 Instance Parameter Management 132 Self-Tuning Memory Management 134 Memory Made Simple 134 PGA Aggregate Target 135 Cache Advice 137 Automatic Undo Management 139 Background 139 Rollback Segments 140 The Oracle9i Solution 141 Database Resource Manager 142 Unused Index Identification 143 Oracle Managed Files 145 Conclusions 147 Chapter 13 - Internals of Locally-Managed Tablespaces 148 Locally Managed Tablespaces by Jonathan Lewis 148 Tablespaces Past and Present 148 The Past 149 The Present 150 Where Are the Benefits? 153 Conclusion 160 Chapter 14 - Multiple Block Sizes in Oracle9i 161 Using Multiple Block Sizes in Oracle9i by Don Burleson 161 Indexes and Large Data Blocks 166 Allocating Objects into Multiple Block Buffers 166 Tools for Viewing Data Buffer Usage 169 Creating Separate Data Buffers 170 Conclusion 171 Section Three - Tables 172 Chapter 15 - Automated Table Reorganization in Oracle8i 172 Automated Table/Index Reorganization In Oracle8i by Mike Hordila 172 When Reorganizing, How Many Extents to Use? 173 Possible Reorganizing Strategies 173 Assumptions and Experimental Figures 174 Some Procedures Related to Table Reorganization 174 Important Issues Regarding Table/Index Moving/Rebuilding 175 The Behavior of the "Alter Table/Index Move/Rebuild" Commands 177 Limitations of the "ALTER TABLE MOVE" Command: 177 Manual Object Reorganization 178 Step 1 179 Step 2 179 Automated Object Reorganization 181 Prerequisites 182 Associated Tables 182 Overview of the Package 183 Setup 186 Chapter 16 - Using External Table in Oracle9i 187 External Tables in Oracle9i by Dave Moore 187 Example 188 Limitations 192 Performance 193 Practical Applications 195 Database Administration 196 Chapter 17 - Instructors Guide to External Tables 198 An Oracle Instructor's Guide to Oracle9i - External Tables by Christopher Foot 198 External Tables 198 Tablespace Changes 202 Online Table Reorganizations 207 Index Monitoring 210 Section Four - Indexes 213 Chapter 18 - Using Locally-Managed Indexes 213 Locally Managed Indexes by John Weeg 213 Rebuild in the same Tablespace 213 No Fragment 214 8 1 to the Rescue 215 More Than One 215 What Goes Where 216 Break Points 216 Script 217 Conclusion 217 Chapter 19 - Sizing Oracle Index Segments – Part 1 219 How Big Should This Index Be? by John Weeg 219 B-tree Theory 219 Estimate Leafs 220 Estimate Branches 221 Making the Index 222 Chapter 20 - Sizing Oracle Index Segments – Part 2 223 Is This Index the Right Size? by John Weeg 223 Validate Structure 223 Dba_Indexes 223 Logical Steps for Resizing and Defragging 225 All Together Now 228 Section Five - Partitioning 230 Chapter 21 - Oracle Partitioning Design 230 Partitioning in Oracle 9i, Release 2 by Lisa Hernandez 230 Introduction 230 Background 231 Partitioning Defined 231 When To Partition 232 Different Methods of Partitioning 233 Partitioning Of Tables 234 Range Partitioning 234 Hash Partitioning 235 List Partitioning 236 Composite Range-Hash Partitioning 236 Composite Range-List Partitioning 237 Conclusion 238 Chapter 22 - Oracle Partitioning Design – Part 2 239 Partitioning in Oracle 9i, Release 2 -- Part 2 by Lisa Hernandez 239 Introduction 239 Background 239 Globally Partitioned Indexes 240 Locally Partitioned Indexes 243 When to Use Which Partitioning Method 247 Real Life Example 247 Conclusion 248 Chapter 23 - Effective Segment Partitioning – Part 1 249 Perils and Pitfalls in Partitioning - Part 1 by Arup Nanda 249 Plan Table Revisited 249 The New Tool DBMS_XPLAN 250 Partition Pruning or Elimination 253 Partition-wise Joins 257 Character Value in Range Partitioning 262 Chapter 24 - Effective Segment Partitioning – Part 2 265 Perils and Pitfalls in Partitioning - Part 2 by Arup Nanda 265 Multi-Column Partition Keys 265 Subpartition Statistics 270 PARTNAME 270 GRANULARITY 270 Rule Based Optimizer 274 Coalesce vs Merge 274 Other Questions 276 What about Rebuild Partition and Global Indexes? 276 While using partitioning, should you use bind variables? 276 How many partitions can be defined on a table? 277 Section Six - Replication 278 Chapter 25 - Multi-Master Replication 278 A Four-phase Approach to Procedural Multi-master Replication by Don Burleson 278 Introduction 278 Why Consider Oracle Multi-master Replication? 279 Oracle Multi-master Replication 280 Multi-master Conflicts and Resolutions 280 Conflict Types 281 Conflict Resolution Mechanisms 282 Implementing Procedural Multi-master Replication 283 Phase I: Pre-configuration Steps for Multi-master Replication 284 Phase II: Set-up REPADMIN User and Database Links 285 Phase III: Create the Master Database and Refresh Groups 286 Phase IV: Monitoring Multi-master Replication 287 Resources for Defining Multi-master Replication 289 Conclusion 290 References 290 Chapter 26 - Replication Management 292 Automated Replication Management by Mike Hordila 292 Basic Replication 292 Automated Replication Management 294 Prerequisites 294 Associated Tables 295 Overview of the Package 295 Setup 297 Test Environment 299 Chapter 27 - Replication Master Table 301 Altering the Master Table in a Snapshot Replication Environment without Recreating the Snapshot by Arup Nanda 301 Background 302 The Usual Method 303 The Alternative Approach 305 Detailed Steps 305 Conclusion 308 Index 309 Team DDU 35 Annotation Written by some of the world's top authors, this exciting eBook provides you with everything that you need to know to manage Oracle storage. Explained are how to monitor and reduce disk I/O, how to set up tablespaces for maximum efficiency, and how to define Oracle tables and indexes to make the most efficient use of disk storage. Not for the beginner, this is a guru-level advanced Oracle handbook with highly detailed explanations of Oracle storage internals. This eBook is laden with useful scripts that will allow you to quickly see inside your data files and tablespaces and tune them for optimal performance