Written by some of the world's most widely-read authors, this exciting eBook provides you with everything that you need to know to manage Oracle storage. You will learn how to monitor and reduce disk I/O, how to set-up your 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.

* Shows how to effectively define Oracle data files for maximum performance. * Learn to measure Oracle segment I/O. * Understand how to reduce Oracle disk I/O. * See how to resize an Oracle data file. * Monitor the Oracle storage system with advanced SQL scripts. * Automate critical storage tasks including automating Oracle table reorganizations. * Understand the proper techniques for sizing Oracle index segments. * See how to use Oracle9i external tables. * Learn about table and index partitioning. * See how to read segment internal dumps. * Understand Oracle9i multiple blocksizes. * See how automatic segment space management works. * Learn about locally-managed tablespaces. * Contains ready-to-run expert data dictionary scripts.

About the Authors:

This eBook is authored by some of the world's leading experts.   Don Burleson   Don Burleson is one of the world’s top Oracle Database experts with more than 20 years of full-time DBA experience.  He specializes in creating database architectures for very large online databases and he has worked with some of the world’s most powerful and complex systems.   A former Adjunct Professor, Don Burleson has written 32 books, published more than 100 articles in National Magazines, and serves as Editor-in-Chief of Oracle Internals. Don is a popular lecturer and teacher and is a frequent speaker at Oracle Openworld and other international database conferences. Click here for Don Burleson's books Click here for Don Burleson articles       Jonathan Lewis   Jonathan Lewis, author of "Practical Oracle 8i - Building Efficient Databases". Jonathan has more than 17 years experience with the Oracle RDBMS, is a regular presenter at UKOUG SIGs, and is the designer and presenter of the seminar "Optimizing Oracle - performance by design".       Dave Ensor   Dave Ensor worked for Oracle for 7 years as a worldwide performance specialist and has been working with Oracle databases for 15 years. In total, he has over 35 years active programming and design experience. As an Oracle design and tuning specialist, Dave has built a global reputation for finding cost-effective solutions to Oracle performance problems and for his ability to explain performance issues to technical audiences with both clarity and humor.       Arup Nanda   Arup Nanda is the recipient of the coveted DBA of the Year 2003 award by Oracle Corporation.  This award is among the most highly coveted in the database industry, and each year only one of more than a quarter million Oracle professionals is honored by this distinction.  A decade of experience as a DBA has made Arup an expert in many Oracle areas including Oracle Design, Oracle Modeling, Oracle Performance Tuning and Oracle Backup & Recovery. Arup is a frequent speaker in many Oracle related conferences including IOUG Live and has written several Oracle related articles in technical journals in the US and Europe. He is on the editorial board for SELECT Journal, the publication of the International Oracle Users Group.         Dave Moore   Dave Moore is a Product Architect at BMC Software, a leading supplier of database software tools.  As the inventor of BMC's DBXray product, he has developed many products that assist Oracle DBAs and Developers.  He has over 13 years of experience in Information Technology - as a DBA and as a developer specializing in Oracle, Java and PL/SQL.   Dave has presented at Oracle OpenWorld and is a frequent contributor to Oracle Professional and DBAZine.  He holds a BS in Information Systems from Western Carolina University.  Dave lives with his wife and three children in Austin, Texas.         John Weeg   John Weeg has over 20 years of experience in information technology, starting as an application developer and progressing to his current level as an expert Oracle DBA. His focus for the past three years has been on performance, reliability, stability, and high availability of Oracle databases. Prior to this, he spent four years designing and creating data warehouses in Oracle. John has a Bachelor of Science degree in Computer Science from the University of Iowa, and did post-Graduate work at Michigan State University.       Chris Foot   Christopher T. Foot is an Oracle certified senior-level instructor, technical sales specialist and database architect. He has fifteen years' experience with database Technologies and is a regular speaker at the International Oracle Users Group and Oracle Open World conferences Contemporary Technologies Inc. is a leading provider of Oracle products and services.           Mike Hordila   Mike Hordila is a DBA OCP v.7, 8, 8i, 9i, and has his own Oracle consulting company, DBActions Inc., in Toronto, Ontario. He specializes in tuning, automation, security, and very large databases. Mike has articles in Oracle Magazine Online, Oracle Internals and DBAzine.com. He is also a technical editor with Hungry Minds (formerly IDG Books).

Table of Contents:

 

Section One - Datafiles Chapter 1 - Measuring Oracle Segment I/O What is Really Going On? by John Weeg Theory Test It What Happens When We Update? What Else? So What? Chapter 2 - Datafile Resizing Tips Setting Free Your Space by John Weeg Alter Database Double Checking the Work Chapter 3 - Reducing Disk I/O on Oracle Datafiles Oracle Expert Tuning Secrets to reduce disk I/O by Don Burleson Oracle tuning and Disk I/O Tuning with RAM Data Buffers The KEEP Pool Locating Tables and Indexes for the KEEP Pool The RECYCLE Pool Using Multiple Block Sizes Disk I/O Tuning STATSPACK Reports for Oracle Datafiles Conclusion Chapter 4 - Measuring Data Segment Statistics Digging at the Segment Level : Performance Diagnosis Reaches A Deeper Level by Arup Nanda Background / Overview Setting the Statistics Levels Segment Level Statistics Collection Column Explanation Examining Detailed Statistics Improvements Case Study Solution Conclusion Chapter 5 - Optimizing Oracle Physical Design Optimal Physical Database Design for Oracle8i by Dave Ensor Introduction Physical Database Design 101 What is Physical Database Design? Database Block Structure Block Size Unstructured Data Freelists Extents AutoExtension Partitioning Index Compression Index Organized Tables (IOT's) Insert Times Retrieval Times Application Impact Online Table Reorganization Temporary Tables Application Impact Locally Managed Tablespaces Transportable Tablespaces Conclusions Chapter 6 - Verifying Segment Backup Scripts Did the Backup Work? by John Weeg Problem How Do We Know? Parsing This String Bring It In Use It Use it Elsewhere Chapter 7 - Data Segment Update Internals How Much Does an Update Cost? by Jonathan Lewis A Brief History of Screen Generators What Does It Cost to Update a Column? But There's More Triggers Indexes Referential Integrity There's Always a Trade-off Conclusion Chapter 8 - Segment Transaction Slot Internals Interested Transaction List (ITL) Waits Demystified by Arup Nanda What is ITL? What Is an ITL Wait Simulation How to Reduce ITL Waits How to Diagnose the ITL Wait What INITRANS Value is Optimal Automatic Block Management in Oracle9i Conclusion Section Two - Tablespaces Chapter 9 - Automated Space Cleanup in Oracle Automated Space Cleanup in Oracle by Mike Hordila Stray Temporary Segments Manual Cleanup of Temporary Segments Recommendations Regarding Temporary Segments Locking Problems with Rollback Segments Recommendations Regarding Rollback Segments Automated Space Cleanup Prerequisites Overview of the Package Setup Chapter 10 - Using Oracle TEMP Files Temporarily Yours: Tempfiles by John Weeg Don't Wait to Create Don't Backup Don't Recover Don't Copy for Standby Don't Add Overhead Give It a Try Chapter 11 - Monitoring TEMP Space Usage Who Took All the TEMP? by John Weeg Where Are My TEMP Tablespaces? Show Me the Objects Who Are the Users? A Happy Ending Chapter 12 - Oracle9i Self-Management Features Oracle9i Self-Management Features: The Early Winners by Dave Ensor Introduction Test Environment Self-Management Goals Examples Instance Parameter Management Self-Tuning Memory Management Memory Made Simple PGA Aggregate Target Cache Advice Automatic Undo Management Background Rollback Segments The Oracle9i Solution Database Resource Manager Unused Index Identification Oracle Managed Files Conclusions Chapter 13 - Internals of Locally-Managed Tablespaces Locally Managed Tablespaces by Jonathan Lewis Tablespaces Past and Present The Past The Present Where Are the Benefits? Conclusion Chapter 14 - Multiple Block Sizes in Oracle9i Using Multiple Block Sizes in Oracle9i by Don Burleson Indexes and Large Data Blocks Allocating Objects into Multiple Block Buffers Tools for Viewing Data Buffer Usage Creating Separate Data Buffers Conclusion

Section Three - Tables Chapter 15 - Automated Table Reorganization in Oracle8i Automated Table/Index Reorganization In Oracle8i by Mike Hordila When Reorganizing, How Many Extents to Use? Possible Reorganizing Strategies Assumptions and Experimental Figures Some Procedures Related to Table Reorganization Important Issues Regarding Table/Index Moving/Rebuild The Behavior of the "Alter Table/Index Move/Rebuild" Commands Limitations of the "ALTER TABLE MOVE" Command Manual Object Reorganization Step 1 Step 2 Automated Object Reorganization Prerequisites Associated Tables Overview of the Package Setup Chapter 16 - Using External Table in Oracle9i External Tables in Oracle9i by Dave Moore Example Limitations Performance Practical Applications Database Administration Chapter 17 - Instructors Guide to External Tables An Oracle Instructor's Guide to Oracle9i - External Tables by Christopher Foot External Tables Tablespace Changes Online Table Reorganizations Index Monitoring Section Four - Indexes Chapter 18 - Using Locally-Managed Indexes Locally Managed Indexes by John Weeg Rebuild in the same Tablespace No Fragment 81 to the Rescue More Than One What Goes Where Break Points Script Conclusion Chapter 19 - Sizing Oracle Index Segments – Part 1 How Big Should This Index Be? by John Weeg B-tree Theory Estimate Leafs Estimate Branches Making the Index Chapter 20 - Sizing Oracle Index Segments – Part 2 Is This Index the Right Size? by John Weeg Validate Structure Dba_Indexes Logical Steps for Resizing and Defragging All Together Now Section Five - Partitioning Chapter 21 - Oracle Partitioning Design Partitioning in Oracle 9i, Release 2 by Lisa Hernandez Introduction Background Partitioning Defined When To Partition Different Methods of Partitioning Partitioning Of Tables Range Partitioning Hash Partitioning List Partitioning Composite Range-Hash Partitioning Composite Range-List Partitioning Conclusion Chapter 22 - Oracle Partitioning Design – Part 2 Partitioning in Oracle 9i, Release 2 -- Part 2 by Lisa Hernandez Introduction Background Globally Partitioned Indexes Locally Partitioned Indexes When to Use Which Partitioning Method Real Life Example Conclusion Chapter 23 - Effective Segment Partitioning – Part 1 Perils and Pitfalls in Partitioning — Part 1 by Arup Nanda Plan Table Revisited The New Tool DBMS_XPLAN Partition Pruning or Elimination Partition-wise Joins Character Value in Range Partitioning Chapter 24 - Effective Segment Partitioning – Part 2 Perils and Pitfalls in Partitioning — Part 2 by Arup Nanda Multi-Column Partition Keys Subpartition Statistics PARTNAME GRANULARITY Rule Based Optimizer Coalesce vs Merge Other Questions What about Rebuild Partition and Global Indexes? While using partitioning, should you use bind variables? How many partitions can be defined on a table? Section Six - Replication Chapter 25 - Multi-Master Replication A Four-phase Approach to Procedural Multi-master Replication by Don Burleson Introduction Why Consider Oracle Multi-master Replication? Oracle Multi-master Replication Multi-master Conflicts and Resolutions Conflict Types Conflict Resolution Mechanisms Implementing Procedural Multi-master Replication Phase I: Pre-configuration Steps for Multi-master Replication Phase II: Set-up REPADMIN User and Database Links Phase III: Create the Master Database and Refresh Groups Phase IV: Monitoring Multi-master Replication Resources for Defining Multi-master Replication Conclusion References Chapter 26 - Replication Management Automated Replication Management by Mike Hordila Basic Replication Automated Replication Management Prerequisites Associated Tables Overview of the Package Setup Test Environment Chapter 27 - Replication Master Table Altering the Master Table in a Snapshot Replication Environment without Recreating the Snapshot by Arup Nanda Background The Usual Method The Alternative Approach Detailed Steps