M.Sc. (CS)/ (SE) SY Oracle Database Administration
Unit 3
Physical databases layouts
3.1 Database File Layout
• By establishing the clear goals of the file distribution design, and by understanding the nature of the database, we can determine the proper distribution of database files across any number of devices. The design process requires us to understand following considerations.
1. Sources of I/O contention among datafiles.
2. I/O bottlenecks among all database files.
3. Concurrent I/O among background processes.
4. The security and performance goals for the database.
5. The available system hardware and mirroring architecture.
6. The other applications using the same resources.
M.Sc. (CS)/ (SE) SY Oracle Database Administration
3.1.1 I/O Contention among datafiles
• When designing the database layout, follow the OFA. Doing so should result in a database that contains some combination of the tablespaces as shown in the table.
• Each of these tablespaces requires a separate datafile. We can monitor database I/O among datafiles after the database has been crated; this capability is only useful during planning stages if a similar database is available for reference. If no such database is available, then the DBA must estimate the I/O load for each datafile.
2 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• Start the physical layout planning process by estimating the relative I/O activity among the datafiles. In general, the datafiles for the tablespaces containing application tables will be very active, along with the index tablespaces, SYSTEM tablespace, and, for transaction-intensive applications, the undo tablespace.
• In transaction processing (OLTP) applications with many users generating small transactions, the application tables & indexes may be separated or partitioned among many tablespaces to reduce the I/O against any single datafile. In OLTP databases the SYSTEM tablespace commonly has about half as much I/O as the data tablespaces.
• For data warehousing applications (featuring fewer, longer transactions & queries) the SYSTEM tablespace commonly accounts for about one-third of the I/O of the data tablespaces. The I/O against the undo tablespace or rollback segment depends on the volume of transactions in the database.
• The I/O against the index tablespaces can vary widely, and often exceeds the I/O against the data tablespaces. Queries that are resolved without table accesses generate I/O only against the index tablespaces and the SYSTEM tablespace (when the user permissions are checked). During transactions, heavily indexed tables commonly report more I/O against their indexes than against the base tables.
• The I/O against the MVIEWS tablespaces will be intense (strong) during the creation and refreshes of the materialized views, but still otherwise be limited to the queries the materialized views support/
• In production databases, 90% or more of the I/O in the database may be concentrated in the combination of the SYSTEM, DATA, INDEX and RBS/undo tablespaces. At a minimum we should have a separate disk for those tablespaces.
• If the application tables & indexes are partitioned, we’ll need additional disks to distribute the partitions.
3 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
3.1.2 I/O Bottlenecks among all Database files
• Once we have estimated the I/O activity of the datafiles, we can plan location of the datafiles relative to each other. In our design, we should also consider the other database file types as show below.
1. Online Redo Log files: Online redo log files store the records of each transaction in the database. Each database should have at least three online redo log files available on it. Oracle will write to one log file in a sequential fashion until it is filled, then it will start writing to the second redo log file. When the last redo log file is filled, the database will begin overwriting the contents of the first redo log file with new transactions.
⮚ DBAs need to make sure that the online redo log files are mirrored by some means. We can use redo log groups to enable the database dynamically maintain multiple sets of online redo log files. Redo log groups use the database to mirror the online redo log, thus minimizing recovery problems caused by a single disk failure.
⮚ We can also rely on the OS to mirror the redo log files. Oracle mirroring is preferable because two or more different commands are sent to original device; the OS will copy the bad data to the mirror.
⮚ In OLTP databases, we should place online redo log files apart from datafiles because of potential I/O conflict. Transaction entries are written to the online redo log files by LGWR (Log Writer) background process.
⮚ The data in transactions is concurrently written to several tablespace such as the RBS rollback segments tablespace and the DATA tablespace. The writes to the tablespaces are done via the DBWR (Database Writer) background process. Thus, even though the datafile I/O may be properly distributed, contention between the DBWR & LGWR background process may occur if a datafile is stored on the same disk as a redo log file.
4 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
2. Control files: Control files are mirrored by Oracle; we can specify the number & name of the control files via the CONTROL_FILES parameter in the initialization parameter file. If the control filenames are specified via this parameter during database creation, then they will be automatically created during the database creation process. The database will thereafter maintain the control files as identical copies of each other. Each database should have a minimum of three copies of its control files, located across three separate physical devices to migrate the impact of media failures. Although there is not a lot of data written starting with Oracle 8, the CKPT process writes checkpoint progress records to the control file every three seconds.
3. Archived Redo Log Files: When Oracle is run in ARCHIVELOG mode,, the database makes copy of each online redo log file after it has filled. These archived redo log files are usually written to a disk device. They may also be written directly to a tape device, but this tends to be operator intensive.
⮚ The ARCH background process performs the archiving function. Databases using the ARCHIVELOG option will encounter contention problems on their online redo log disk during heavy data transaction times, since LGWR will be trying to write to one redo log file while ARCH is trying to read another.
⮚ To avoid this contention, distribute the online redo log files across multiple disks. If we are running in ARCHIVELOG mode on a very transaction-oriented database, avoid LGWR-ARCH contention by splitting up our online redo log files across devices.
⮚ To further enhance the performance of the archiving process, create online redo log file groups with multiple members. The archiving process can archive faster there are multiple redo log files in a group to read from while archiving.
5 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
4. Oracle Software: The Oracle software files that are accessed during normal database operation vary according to the packages that are licensed for the host on which the server resides. The I/O against these files is not recorded within database but is visible via utilities such as sar on the UNIX platform.
⮚ To minimize contention between the database files & the database code (S/w), avoid placing database files one the same disk device as the code files. If datafiles must be placed on that disk device, then the least frequently used (LRU) datafiles should be placed there.
6 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
3.1.3 Concurrent I/O among Background Processes
• Files compete with each other if I/O from one file affects with I/O for the second file, so two randomly accessed files that are never accessed at the same time can be placed on the same device.
• LGWR will write to all members of the same redo log group at the same time & the ARCH process can write to multiple destinations at the same time. DBWR may also be attempting to write to multiple files at once. There is thus the potential for DBWR to cause contention with itself as it writes out modified blocks of multiple tables. To combat this problem, most operating systems support the creation of multiple DBWR processes for each instance.
• The number of DBWRs is set via the initialization parameter DBWR_PROCESSES; we can also start multiple I/O slaves for a single DBWR process via the DBWR_IO_SLAVES parameter. Oracle recommends setting DBWR_IO_SLAVES to a value between n & 2n, where n is the number of disks.
• In Oracle 8.1 & later release we can also start multiple LGWR I/O slaves & ARCH I/O slaves, Oracle uses DBWR_IO_SLAVES settings to determine how many LGWR & ARCH I/O slaves to start; setting DBWR_IO_SLAVES to a value greater than 0 sets the corresponding LGWR_IO_SLAVES & ARCH_IO_SLAVES settings each to 4.
7 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
3.1.4 Defining the Recoverability & Performance Goals for the System • Before designing the database’s disk layout, the recoverability and performance goals
for the layout must be clearly defined. The recoverability goals must take into account all processes that impact disks, including storage area for archived redo log files and the storage area for backup.
• As part of defining the recoverability requirements, we should establish service level agreement with the business users defining the allowable downtime & data loss in the event of system failures and major disasters.
• In terms of performance goals, we should define the goals for batch processes, administrative processes, query response time and data loading operations. In each case, define primary goal & stretch goal without incurring I/O waits on the disks.
3.1.5 Defining the System Hardware & Mirroring Architecture
• The system architecture includes specifying
⮚ The Number of disks required.
⮚ The models of disks required (for performance or size).
⮚ The appropriate mirroring strategy for disk arrays.
• The number of disk arrays required will be driven by the size of the database and the volume of database I/O activity. Whenever possible, those disks should be dedicated to Oracle files to avoid contention with non-Oracle files
• Disk mirroring provides fault tolerance with regard to media failure. Mirroring is performed either by maintaining a duplicate of each disk online (known as RAID-1 or volume shadowing) or by using a parity-check system among a group of disks (usually RAID-3 or RAID-5). The parity check systems implicitly (indirectly) perform file stripping across the disks in the mirroring group. A parity check is then written on another disk in the set so that if one disk is removed, its contents can be regenerated based on knowing the parity check and the contents of the rest of the mirrored set.
8 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
3.1.5.1 Identifying Disks that can be dedicated to the Database
• Whatever mirroring architecture is used, the disks chosen must be dedicated to the database. Otherwise, the non-database load on those disks will impact the database, & that impact is usually impossible to forecast correctly. User directory areas for Ex. May experience sudden increase in size – & wipe out the space that was intended for the archived redo log files, bringing the database to a halt.
9 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
3.2 Database Space usage Overview
• In order to understand how space should be allocated within the database, we first have to know how the space is used within database.
• When database is created, it is divided into multiple logical sections called tablespaces. The SYSTEM tablespace is the first tablespace created. Additional tablespaces are then created to hold different types of data.
• When tablespaces are created, datafiles are created to hold its data. These files immediately allocate the space specified during their creation. There is thus a one-to many relationship between datafiles and tablespaces.
• Each database will have multiple users, each of whom has a schema. Each user’s schema is a collection of logical database objects such as tables and indexes. These objects refer to the physical data structures that are stored in tablespaces. Objects from user’s schema may be stored in multiple tablespaces & single tablespace can contain objects from multiples schemas
• When a database objects (tables & indexes) is created, it is assigned to a tablespace via user defaults or specific instructions. A segment is created in that tablespace to hold the data associated with the object. The space that is allocated to the segment is never released until the segment is dropped, manually shrunk (compressed), or truncated.
• A segment is made up of sections called extents – contiguous sets of Oracle blocks. Once existing extents can no longer hold new data, the segment will obtain another extent to support additional inserts of data into the object. The extension process will continue until no more free space is available in the tablespace’s datafiles or until an internal maximum number of extents per segment is reached. When a datafile fills, it can extend based on the storage rules defined for it. The segments types available in Oracle include the following:
10 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
3.2.1 Implications of the storage Clause
• The amount of space used by a segment is determined by its storage parameters. These are specified when a segments is created, & may be altered later. If no specific storage parameters are given in the create table, create index, create cluster or create rollback segment command, the database will use the default storage parameters for the tablespace in which it is to be stored.
• The storage parameters specify the initial extent size, the next extent size, pctincrease (a factor by which each successive extent will geometrically grow), the maxextents (maximum number of extents), & minextents (minimum number of extents).
• After the segment has been created, the initial and minextents values cannot be altered. The default values for the storage parameters for each tablespace may be queried from the DBA_TABLESPACES view.
• When a segment is created, it will acquire at least one extent. The initial extent will store data until it no longer has any free space available. We can use the pctfree clause to reserve, within each block in each extent, a percentage of space that will remain available for updates of existing rows in the block. When additional data is added to the segment, the segment will extend by obtaining a second extent of the size specified by the next parameter.
• The pctincrease parameter is designed to minimize the number of extents in growing tables. A nonzero value for this parameter will cause the size of each successive extent to increase geometrically by the pctincrease factor specified.
1
M.Sc. (CS)/ (SE) SY Oracle Database Administration
3.2.2 Locally Managed Tablespaces
• We can use two different methods to track free and used space in a tablespace. • The first method, which has been available since the beginning of tablespaces, is to handle extent management via data dictionary (dictionary managed tablespace). • In a dictionary managed tablespace each time an extent is allocated or freed for reuse in a tablespace, the appropriate entry is updated in the data dictionary table. • The second method, available as of Oracle 8i, handles extent management within tablespaces themselves (locally managed tablespaces).
• In locally managed tablespaces, the tablespace manages its own space by maintaining a bitmap in each datafile of the free & used blocks or sets of blocks in the datafile. Each time an extent is allocated or freed for reuse, Oracle updates the bitmaps to show the new status. Within a locally managed tablespace, all extents can have the same size or the system can automatically determine the size of extents.
• To create a locally managed tablespace, specify the local option for the extent management clause in the create tablespace command.
12 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
3.2.3 Free space
• A free extent in a tablespace is a collection of contiguous free blocks in the tablespace. A tablespace may contain multiple data extents and free extents. When a segment is dropped, its extents are deallocated and marked as free. In dictionary managed tablespaces, these free extents are not always recombined with neighboring free extents; the barrier between these free extents may be maintained. The SMON background process periodically coalesces neighboring free extents, provided the default pctincrease for the tablespace is nonzero.
Segment 1 Extent 1
Segment 2 Extent 1
Segment 2 Extent 2
Segment 2 Extent 3
Segment 2 Extent 4
Segment 1 Extent 2
Free Space
Fig A. Initial Configuration
Segment 1 Extent 1
Free Space
Free Space
Free Space
Free Space
Segment 1 Extent 2
Free Space
Segment 1 Extent 1
Fig B. After Segment 2 is dropped (uncoalesced)
Free
Space
Fig C. After Segment 2 is dropped (coalesced)
Segment 1 Extent 2
Free Space
Fig 3.1. Free extent management in dictionary managed tablespaces
• The SMON background process only coalesces tablespace whose default pctincrease value is nonzero. A pctincrease of 1 will force the SMON to coalesce the adjacent free space in a tablespace but will coalesces eight areas of adjoining extents at a time. Each of the eight coalesces will join two or more extents together to create one large extent. For best space management, use locally managed tablespaces so that we never have to worry about coalescing. If someone still want to use dictionary managed
13 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
tablespaces, maintain a pctincrease of 0 and periodically coalesce any dropped extents manually.
• To force the tablespace to coalesce its free space, use the coalesce clause of the alter tablespace clause, as
• The preceding command will force the neighboring free extents in the DATA tablespace to be coalesced into larger free extents. The command will coalesce up to eight separate area, just like SMON
• Locally managed tablespaces do not require the same degree free space management. Since locally managed tablespaces can be configured to have consistent extent sizes for all segments, dropped extents are easily reused.
• When allocating new extent, Oracle will not merge contiguous free extents unless there is no alternative. In dictionary managed tables, this can result in the large free extents at the rear of the tablespace being used while the smaller free extents towards the front of the tablespace are relatively unused. The small free extents become “speed bumps” in the tablespace because they are not, by themselves, of adequate size to be of use. As this usage pattern progresses, the amount of space wasted in the tablespace increases.
• In an ideal database, all objects are created at their appropriate size and all free space is always stored together, a resource pool waiting to be used. If we can avoid dynamic space allocation during application usage, we remove both a performance impact and a source of potential application failure.
14 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
3.3 Moving Online Redo Log Files
• Online redo log files can be moved while the database is shut down, and renamed within the database via the alter database command. The procedure for moving online redo log files are very similar to those used to move datafiles via the alter database command.
• First, the database is shut down and the online redo log file is moved. The database is then mounted and the alter database command is used to tell the database the new location of the online redo log file The instance can then be opened, using the online redo log in its new location.
• In the following example, we have three redo log file groups with one members each. We want to move them to a different location to eliminate any contention between log file & Datafiles. The method we use here is similar to the method used to move datafiles with ALTER DATBASE method.
• Use OS Command to move the Redo Log Files from one location to another. • Start the instance in mount mode
15 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
16 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
3.4 Moving Control Files
• The location of control files is specified in the initialization parameter file for the instance. To move a control file, we must shut down the instance, move the control file with OS commands (cut/paste), edit the initialization parameter file, & then restart the instance.
• When we use a Server Parameter File (SPFILE), however, the procedure is a bit different. The initialization file parameter CONTROL_FILES is changed using ALTER SYSTEM ….SCOPE=SPFILE
• When either the instance is running or it’s shutdown and opened in NOMOUNT mode. Because the CONTROL_FILES parameter is not dynamic, the instance must be shut down and restarted in either case.
• Use OS command to move control, files to the newly specified location
17 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
3.5 How to deallocate space from segments
• As of Oracle 7.2 we can reclaim unused space from existing datafiles. As of Oracle 7.3, we can reclaim space from tables, indexes & clusters.
3.5.1 Shrinking Datafiles
• We can use the alter database command to reclaim unused space from datafiles. We cannot resize a datafile if the space we are trying to reclaim is currently allocated to a database object.
• For example, if the datafile is 100MB in size, and 70MB of the datafile is currently in use, we will need to leave at least 70MB in the datafile. We can use the resize clause of the alter database command to reclaim the space, as shown in the following example.
• As shown in the listing we specify the name of the file to be shrunk and its new size. If there are no database objects beyond the first 80MB of the specified datafile, the datafile will be shrunk to 80MB.
• If space is used within the datafile beyond the first 80MB, then an error will be returned. As shown in the following listing, the error will show the amount of space i.e. used within the datafile beyond the specified resize value.
18 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
3.5.2 Shrinking Tables, Clusters, & Indexes
• When Oracle writes data to a segment, it updates the high-water mark for the segment. Internally, the high-water advances as blocks are put on the free list, normally five blocks at a time. The high-water mark points to the block immediately following the last block on the free list. If we insert thousands of rows in the table, the high-water will be incremented; if we delete the records, the high-water mark will not decrease. Aside for dropping and re-creating the table, the high-water mark for a segment is only reset when we issue a truncate command or the segment is dropped and re-created.
• As of Oracle 7.3, unused space above the high-water mark in a segment can be reclaimed. If we have overestimated the storage requirements for an object, we may wish to reclaim space that was allocated unnecessarily.
• We can reclaim the space from the segment without dropping and re-creating it – with the limitation that we can only reclaim the space above high-water mark for the table. • Before we reclaim space from a table, we should therefore determine the high-water mark for the table. In the following listing, the UNUSED_SPACE procedure within the DBMS_SPACE package is used to determine the space usage of the table named COLLEGE owned by the user COCSIT
19 |
M.Sc. (CS)/ (SE) SY Oracle Database Administration
• The high-water mark of the table (in bytes) is the difference between the TOTAL_BYTES value & the UNSED_BYTES value returned by this procedure call. The UNUSED_BLOCKS value represents the number of blocks above the high-water mark; the TOTAL_BLOCKS value reflects the total number of blocks allocated to the table. To reclaim the space from the table, & if its UNUSED_BLOCKS value is nonzero, we can use the alter table command to reclaim the space above the high-water mark.
• Use Alter index & alter cluster for deallocation index & clusers.
20 |
No comments:
Post a Comment