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