Transcript Logical Database Layouts - University of South Alabama

Database
Chapter
Processing
4
Chapter 4
Physical Database Layouts
Chapter 4
Main Points
• Database File Layout
• Verification of I/O Weighting
Estimates
• File Location
• Database Space Usage Overview
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
Database File Layouts
• Establish clear goals of the file
distribution design
• Understand the nature of the
database
– Transaction-oriented vs Read-Intensive
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
Database File Layout (6 steps)
1. 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
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
Table 4-1
TABLESPACE
SYSTEM
DATA
DATA_2
INDEXES
INDEXES_2
RBS
RBS_2
TEMP
TEMP_USER
TOOLS
TOOLS_1
USERS
MEANING
Data Dictionary
Standard-operation tables
Static tables used during standard operation
Indexes for Standard-operation tables
Indexes for the static tables
Standard-operation rollback segments
Specialty rollback segments used for data loads
Standard operation temporary segments
Temporary segments created by a particular user
RDBMS tools table
Indexes for RDBMS tools table
User objects, in development database
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
Table 4-2
PERCENT
OF TOTAL
TABLESPACE WEIGHT
DATA
RBS
SYSTEM
INDEXES
TEMP
DATA_2
INDEXES_2
TOOLS
Copyright © 2001 Harold Pardue, University of South Alabama
100
40
35
33
5
4
2
1
220
46%
18%
16%
15%
2%
2%
1%
1%
100%
Chapter 4 - Physical Database Layouts
Chapter 4
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
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
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
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
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
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
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
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
Dedicated Database Disks
6. Identify Disks that can be
dedicated to the database
•
To avoid concurrent I/O and
interference with Non-ORACLE files
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
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
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
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
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
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!
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
Dream Physical Layout—22 Disks
• Disk
1
2
3
4
5
6
7
8
9
10
11
Contents
Oracle Software
System Tablespace
RBS Tablespace
DATA Tablespace
INDEXES Tablespace
TEMP Tablespace
TOOLS Tablespace
Online Redo log 1
Online Redo log 2
Online Redo log 3
Control File 1
Copyright © 2001 Harold Pardue, University of South Alabama
• Disk
12
13
14
15
16
17
18
19
20
21
22
Contents
Control File 2
Control File 3
Application Software
RBS_2
DATA_2
INDEXES_2
TEMP_USER
TOOL_I
USERS
Archived redo log disk
Export dump file disk
Chapter 4 - Physical Database Layouts
Chapter 4
First Iteration—17 Disks
•
Disk
1
2
3
4
5
6
7
8
9
1.
Contents
Oracle Software
System Tablespace
RBS Tablespace
DATA Tablespace
INDEXES Tablespace
TEMP Tablespace
TOOLS Tablespace
Online Redo log 1 Control File 1
Online Redo log 2 Control File 2
•
Disk
10
Contents
Online Redo log 3 Control File 3
11 Application Software
12 RBS_2
13 DATA_2
14 INDEXES_2
15 TEMP_USER
16 Archived redo log disk
17 Export dump file disk
TOOL_I and USERS are omitted
Control Files have least interference with Online Redo log files
2. See author notes for rationale re TOOL_I and USER omission
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
File Location
• Database files
– Separated from other software
– Stored in directories created
specifically for that database
– Of different database should not be
stored together
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
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
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
Sizing
• Pertains to
–
–
–
–
–
Table segments
Index segments
Rollback segments
Temporary segments
Free space
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts
Chapter 4
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
Copyright © 2001 Harold Pardue, University of South Alabama
Chapter 4 - Physical Database Layouts