Sumário Itens Encontrados: 292Conventions Used in this Book xivSection One - DatafilesChapter 1 - Measuring Oracle Segment I/O . 1What is Really Going On? by John Weeg..1Theory .1Test It ..2What Happens When We Update?.4What Else? 5So What? 5Chapter 2 - Datafile Resizing Tips .6Setting Free Your Space by John Weeg ..6Alter Database7Double Checking the Work .9Chapter 3 - Reducing Disk I/O on Oracle Datafiles 12Oracle Expert Tuning Secrets to reduce disk I/O by DonBurleson . 12Oracle tuning and Disk I/O 12Tuning with RAM Data Buffers 13The KEEP Pool 13Locating Tables and Indexes for the KEEP Pool 14The RECYCLE Pool 16Using Multiple Block Sizes 16Disk I/O Tuning . 19STATSPACK Reports for Oracle Datafiles. 20Conclusion 22Chapter 4 - Measuring Data Segment Statistics 24iv Oracle Space Management HandbookDigging at the Segment Level : Performance Diagnosis ReachesA Deeper Level by Arup Nanda 24Background / Overview. 25Setting the Statistics Levels 26Segment Level Statistics Collection 28Column Explanation 29Examining Detailed Statistics . 30Improvements 30Case Study. 31Solution 34Conclusion 35Chapter 5 - Optimizing Oracle Physical Design 37Optimal Physical Database Design for Oracle8i by DaveEnsor 37Introduction. 37Physical Database Design 101 37What is Physical Database Design? 38Database Block Structure 39Block Size 40Unstructured Data 41Freelists 41Extents. 42AutoExtension 43Partitioning 44Index Compression 46Index Organized Tables (IOT's) 47Insert Times. 48Retrieval Times . 50Application Impact. 50Online Table Reorganization 51Temporary Tables 52Application Impact. 54Locally Managed Tablespaces. 55Transportable Tablespaces 56Conclusions . 58Chapter 6 - Verifying Segment Backup Scripts . 60Did the Backup Work? by John Weeg . 60Problem 60How Do We Know? . 61Parsing This String . 61Bring It In . 62Use It 64Use it Elsewhere 65Chapter 7 - Data Segment Update Internals 66How Much Does an Update Cost? by Jonathan Lewis 66A Brief History of Screen Generators 66What Does It Cost to Update a Column? . 67But There's More. 69Triggers 70Indexes 71Referential Integrity 72There's Always a Trade-off 73Conclusion 74Chapter 8 - Segment Transaction Slot Internals 75Interested Transaction List (ITL) Waits Demystified by ArupNanda 75What is ITL? 75What Is an ITL Wait . 76Simulation . 79How to Reduce ITL Waits 80How to Diagnose the ITL Wait 81What INITRANS Value is Optimal 83Automatic Block Management in Oracle9i 85Conclusion 86vi Oracle Space Management HandbookSection Two - TablespacesChapter 9 - Automated Space Cleanup in Oracle . 87Automated Space Cleanup in Oracle by Mike Hordila 87Stray Temporary Segments 87Manual Cleanup of Temporary Segments . 90Recommendations Regarding Temporary Segments 92Locking 93Problems with Rollback Segments 93Recommendations Regarding Rollback Segments 93Automated Space Cleanup 94Prerequisites 94Overview of the Package 94Setup . 96Chapter 10 - Using Oracle TEMP Files. 98Temporarily Yours: Tempfiles by John Weeg 98Don't Wait to Create . 98Don't Backup . 99Don't Recover 99Don't Copy for Standby 100Don't Add Overhead . 100Give It a Try . 101Chapter 11 - Monitoring TEMP Space Usage . 102Who Took All the TEMP? by John Weeg . 102Where Are My TEMP Tablespaces? 102Show Me the Objects. 103Who Are the Users? 103A Happy Ending 105Chapter 12 - Oracle9i Self-Management Features . 106Oracle9i Self-Management Features: The Early Winners byDave Ensor 106Introduction 106Test Environment 107Self-Management 108Goals 108Examples. 109Instance Parameter Management 110Self-Tuning Memory Management 112Memory Made Simple 112PGA Aggregate Target . 113Cache Advice 115Automatic Undo Management 117Background . 117Rollback Segments 118The Oracle9i Solution 119Database Resource Manager. 120Unused Index Identification . 121Oracle Managed Files. 123Conclusions 125Chapter 13 - Internals of Locally-Managed Tablespaces 126Locally Managed Tablespaces by Jonathan Lewis. 126Tablespaces Past and Present 126The Past 127The Present 128Where Are the Benefits? 131Conclusion . 138Chapter 14 - Multiple Block Sizes in Oracle9i . 139Using Multiple Block Sizes in Oracle9i by Don Burleson 139Indexes and Large Data Blocks 144Allocating Objects into Multiple Block Buffers . 144Tools for Viewing Data Buffer Usage 147Creating Separate Data Buffers 148Conclusion . 149Section Three - TablesChapter 15 - Automated Table Reorganization in Oracle8i150Automated Table/Index Reorganization In Oracle8i by MikeHordila . 150When Reorganizing, How Many Extents to Use?. 151Possible Reorganizing Strategies 151Assumptions and Experimental Figures 152Some Procedures Related to Table Reorganization . 152Important Issues Regarding Table/Index Moving/Rebuilding153The Behavior of the "Alter Table/Index Move/Rebuild"Commands. 155Limitations of the "ALTER TABLE MOVE" Command:. 155Manual Object Reorganization 156Step 1. 157Step 2. 157Automated Object Reorganization 159Prerequisites. 160Associated Tables . 160Overview of the Package. 161Setup 164Chapter 16 - Using External Table in Oracle9i 165External Tables in Oracle9i by Dave Moore. 165Example 166Limitations . 170Performance . 171Practical Applications. 173Database Administration. 174Chapter 17 - Instructors Guide to External Tables 176An Oracle Instructor's Guide to Oracle9i - External Tables byChristopher Foot 176External Tables 176Tablespace Changes 180Online Table Reorganizations . 185Index Monitoring 188Section Four - IndexesChapter 18 - Using Locally-Managed Indexes .191Locally Managed Indexes by John Weeg . 191Rebuild in the same Tablespace . 191No Fragment 1928.1 to the Rescue 193More Than One . 193What Goes Where 194Break Points . 194Script 195Conclusion . 195Chapter 19 - Sizing Oracle Index Segments â Part 1 197How Big Should This Index Be? by John Weeg. 197B-tree Theory 197Estimate Leafs. 198Estimate Branches 199Making the Index 200Chapter 20 - Sizing Oracle Index Segments â Part 2 201Is This Index the Right Size? by John Weeg. 201Validate Structure . 201Dba_Indexes 201Logical Steps for Resizing and Defragging. 203All Together Now. 206Section Five - PartitioningChapter 21 - Oracle Partitioning Design. 208Partitioning in Oracle 9i, Release 2 by Lisa Hernandez. 208Introduction 208Background 209Partitioning Defined 209When To Partition 210Different Methods of Partitioning 211x Oracle Space Management HandbookPartitioning Of Tables 212Range Partitioning 212Hash Partitioning. 213List Partitioning 214Composite Range-Hash Partitioning 214Composite Range-List Partitioning 215Conclusion . 216Chapter 22 - Oracle Partitioning Design â Part 2 217Partitioning in Oracle 9i, Release 2 -- Part 2 by Lisa Hernandez 217Introduction 217Background 217Globally Partitioned Indexes 218Locally Partitioned Indexes 221When to Use Which Partitioning Method 225Real Life Example 225Conclusion . 226Chapter 23 - Effective Segment Partitioning â Part 1 227Perils and Pitfalls in Partitioning â Part 1 by Arup Nanda 227Plan Table Revisited 227The New Tool DBMS_XPLAN 228Partition Pruning or Elimination 231Partition-wise Joins 235Character Value in Range Partitioning 240Chapter 24 - Effective Segment Partitioning â Part 2 243Perils and Pitfalls in Partitioning â Part 2 by Arup Nanda 243Multi-Column Partition Keys 243Subpartition Statistics. 248PARTNAME 248GRANULARITY 248Rule Based Optimizer 252Coalesce vs. Merge 252Other Questions 254What about Rebuild Partition and Global Indexes? 254While using partitioning, should you use bind variables?. 254How many partitions can be defined on a table? . 255Section Six - ReplicationChapter 25 - Multi-Master Replication 256A Four-phase Approach to Procedural Multi-masterReplication by Don Burleson 256Introduction 256Why Consider Oracle Multi-master Replication? 257Oracle Multi-master Replication 258Multi-master Conflicts and Resolutions 258Conflict Types. 259Conflict Resolution Mechanisms. 260Implementing Procedural Multi-master Replication 261Phase I: Pre-configuration Steps for Multi-master Replication 262Phase II: Set-up REPADMIN User and Database Links 263Phase III: Create the Master Database and Refresh Groups 264Phase IV: Monitoring Multi-master Replication 265Resources for Defining Multi-master Replication. 267Conclusion . 268References 268Chapter 26 - Replication Management 270Automated Replication Management by Mike Hordila 270Basic Replication 270Automated Replication Management . 272Prerequisites. 272Associated Tables . 273Overview of the Package. 273Setup 275Test Environment 277Chapter 27 - Replication Master Table 279Altering the Master Table in a Snapshot ReplicationEnvironment without Recreating the Snapshot by Arup Nanda 279Background 280The Usual Method 281The Alternative Approach 283Detailed Steps 283Conclusion . 286Index . 287