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 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