1 / 20

Chapter 4

Chapter 4. Database Processing. Chapter 4. Physical Database Layouts. Main Points. Database File Layout Verification of I/O Weighting Estimates File Location Database Space Usage Overview. Database File Layouts. Establish clear goals of the file distribution design

ghada
Download Presentation

Chapter 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4 Database Processing Chapter 4 Physical Database Layouts

  2. Main Points • Database File Layout • Verification of I/O Weighting Estimates • File Location • Database Space Usage Overview

  3. Database File Layouts • Establish clear goals of the file distribution design • Understand the nature of the database • Transaction-oriented vs Read-Intensive

  4. Database File Layout (6 steps) • Identify I/O contention among datafiles • Use statistics from analogous database if available • Assign I/O weights based on estimates relative to most active tablespace • See Table 4-1 for datafiles in optimal database • See Table 4-2 for estimated I/O weights for sample tablespaces

  5. Table 4-1

  6. Table 4-2

  7. Database File Layout 2. Identify I/O bottlenecks among all database files • Location of datafiles relative to each other • Online redo log files should not be stored on same disk as any other active datafile • Control file requires little I/O • LGWR-ARCH contention-don’t store Archive redo log files on same disk as redo log files • Oracle software—no statistics kept on I/O

  8. Background Processes 3. Types • Concurrent I/O—multiple accesses performed against same device at same instant • Interference—writes to a sequentially written file are interrupted by reads or writes to other files on the same disk • DBWR—reads and writes in random manner • Can be in contention with itself • ORACLE supports multiple DBWR processes for each instance • LGWR—writes sequentially to one file at a time • ARCH—reads and writes sequentially to one file at a time

  9. Security and Performance Goals 4. Recoverability—takes into account all processes that impact disk • Must mirror online redo log files (OS or redo log groups) • Prevails over performance tuning • Performance tuning goals—take into account the projected database file I/O distribution and the relative access speeds of the disk

  10. System Hardware and Mirroring Architecture 5. Number of disks required • Determined byDatabase size and database I/O weights • Models of disks required • Appropriate mirroring strategy

  11. Dedicated Database Disks • Identify Disks that can be dedicated to the database • To avoid concurrent I/O and interference with Non-ORACLE files

  12. Verifying I/O Weighting Estimates • Statistics table • Found in data dictionary • Compare to initial estimates and adjust • See page 100 for sample use of the view V$FILESTAT

  13. Summary: Basis for disk layout • Recovery • Mirroring of online redo log files • Database file I/O weight estimation • Contention among background processes • Contention between disks for DBWR • Defined performance goals • Known disk hardware options • Known disk mirroring architecture • Dedicated database disks

  14. Decisions • The author provides guidance physical disk layout decisions • “Dream” physical disk layout is best case scenario • Each successive iteration suggests the best compromise at that point • Similar to guidance for “Denormalizing” a database that has been fully normalized. • Will illustrate one iteration…see author for iterations 2-6!

  15. Disk Contents 1 Oracle Software 2 System Tablespace 3 RBS Tablespace 4 DATA Tablespace 5 INDEXES Tablespace 6 TEMP Tablespace 7 TOOLS Tablespace 8 Online Redo log 1 9 Online Redo log 2 10 Online Redo log 3 11 Control File 1 Dream Physical Layout—22 Disks • Disk Contents • 12 Control File 2 • 13 Control File 3 • 14 Application Software • 15 RBS_2 • 16 DATA_2 • 17 INDEXES_2 • 18 TEMP_USER • 19 TOOL_I • 20 USERS • 21 Archived redo log disk • 22 Export dump file disk

  16. Disk Contents 1 Oracle Software 2 System Tablespace 3 RBS Tablespace 4 DATA Tablespace 5 INDEXES Tablespace 6 TEMP Tablespace 7 TOOLS Tablespace 8 Online Redo log 1 Control File 1 9 Online Redo log 2 Control File 2 First Iteration—17 Disks • Disk Contents • 10 Online Redo log 3 Control File 3 • 11 Application Software • 12 RBS_2 • 13 DATA_2 • INDEXES_2 • 15 TEMP_USER • 16 Archived redo log disk • Export dump file disk • TOOL_I and USERSare omitted • Control Files have least interference with Online Redo log files • See author notes for rationale re TOOL_I and USER omission

  17. File Location • Database files • Separated from other software • Stored in directories created specifically for that database • Of different database should not be stored together

  18. Database Space Usage Overview • Storage Clause—default parameters • Initial extent size • Next extent size • Pct increase (careful use and monitoring) • Max extents • Min extents • Pct Free clause

  19. Sizing • Pertains to • Table segments • Index segments • Rollback segments • Temporary segments • Free space

  20. Scenarios • Remainder of Chapter 4 is how-to-do to achieve specific goals • Resizing Datafiles (7.2 and higher) • Automating Datafile Extensions • Moving Database Files • Moving Online Redo Log Files • Moving Control Files • Deallocating Space in 7.2 and 7.3 • Shrinking Datafiles • Shrinking Tables, Clusters and Indexes • Rebuilding Indexes

More Related