HomeOracle Page 5 - Tuning Your Database for Availability
Additional Advisors - Oracle
When dealing with databases, uptime is not the only issue; if the database is up but slow, the end user will still be unhappy. Fortunately, there are ways to tune an Oracle database to deal with this issue, which is the subject of this article. It is excerpted from chapter three of Oracle Database 10g High Availability with RAC, Flashback and Data Guard, written by Matthew Hart and Scott Jesse (McGraw-Hill/Osborne, 2004; ISBN: 0072254289).
The previous advisors merited sections on their own due to the impact that they potentially have and the new features that they bring with them to Oracle Database 10g. In addition to the advisors mentioned, Advisor Central provides links to other new advisors that can offer useful insights into the performance and layout of your database.
The MTTR Advisor allows making changes to fast_start_mttr_target, which essentially controls how long we want instance recovery to take, should we have a crash. Setting a target value here will cause Oracle to make internal changes to how frequently data is flushed to disk, or checkpointed, to ensure that if a crash recovery occurs, the time to roll forward will be close to the targeted value. This means the crash recovery will complete within x number of seconds, and the database will then be opened and made available. The view V$MTTR_TARGET_ ADVICE is used to provide an idea of what additional I/O might be incurred if the MTTR is reduced. This advisor also allows for the setting up of a flash recovery area, and enabling database archivelog mode. This is not tuning related, but of course is intrinsic to high availability, so these topics are covered more appropriately in other chapters.
The Segment Advisor essentially looks at space usage and advises on whether you can free up space. It also checks for fragmentation, and determines whether reorganizing individual segments and/or making changes to entire tablespaces can gain efficiencies. If it finds segments that can be reorganized, it will highlight them and provide a recommendation to do a shrink of the segment, and offer the ability to schedule a job to do so immediately or schedule the run for some future time. In Oracle Database 10g, a table or index segment can be compacted via the alter <object> <object_name> shrink … command. This command can be done while the table or index is online, without impacting user access to the segments and without the need to rebuild the indexes afterward. However, row movement must be enabled on the table in question first. Note in Figure 3-4 that the segment in question has generated advice, but the advice cannot be executed because row movement is not enabled. Thus, the selection is grayed out. In order to make any changes to this table, you will have to enable row movement (alter table PROD_ EMP enable Row Movement) and then rerun the Segment Advisor. Then you will be able to select the option under the Recommendations section to shrink the segment.
FIGURE 3-4.Segment Advisor
Undo Management Advisor
The Undo Management Advisor first takes you into a review of the undo management parameters and allows you to make or change settings for enabling automatic undo management, which will automatically tune the UNDO_RETENTION time for you based on longest running queries and available space in the UNDO tablespace. In addition, the Undo Management Advisor allows for making changes to the UNDO tablespace, or actually changing the undo to a different tablespace altogether. From here, you can actually launch into the Undo Advisor itself, and Oracle will give advice on what the undo retention should be (in seconds) to avoid Snapshot Too Old errors (ORA-1555). In addition, the Undo Advisor will estimate the size that the UNDO tablespace needs to be in order to retain that amount of undo. Since Oracle Database 10g provides you with the opportunity to guarantee the retention period, the Undo Advisor will take data from the past seven days (by default), analyze that data, and determine how much undo has been generated during that period. The Undo Advisor will then provide a graph, showing you how large the UNDO tablespace needs to be to achieve the desired UNDO_RETENTION time without running out of space in the UNDO_TABLESPACE.
Automatic Storage Management (ASM)
Automatic Storage Management, or ASM, is another new Oracle Database 10g feature that revolutionizes the way Oracle and the HA DBA manage database files. ASM combines volume management with the concept of Oracle managed files to allow the HA DBA to create a database comprised of datafiles that are not only self-managed, but also the I/O is automatically balanced among available disks. ASM combines the ability to simplify management of files with the ability to automatically self-tune, while at the same time providing a level of redundancy and availability that is absolutely imperative for the storage grid.
The implementation of ASM involves the creation of a normal Oracle instance with the parameter INSTANCE_TYPE=ASM set to a value of ASM on a node where a database or databases reside. This instance does not have an associated database, but rather is used to manage the disks that are accessed by your database(s). As such, an ASM instance is never opened—it is only mounted. Mounting an ASM instance involves mounting the disk groups associated with the ASM instance, so that the disk groups and files are then accessible from the other instances. We will discuss ASM in various sections throughout the remainder of the book, but we will take the time here to discuss the concepts behind ASM, how to implement ASM in your environment, and how to manage an ASM environment once you are up and running.
The underlying concept behind ASM is that it is a file system created specifically for Oracle datafiles, on top of RAW or block devices. This file system is kept and maintained by the Oracle kernel, so Oracle knows where file extents are and automatically manages the placement of these extents for maximum performance and availability of your database. You, as the HA DBA, will not know or care where Oracle is placing extents on disk. Oracle will do all of that management for you through ASM. No volume management software is needed, and no file system is needed.
ASM Disk Group
At its highest level, within ASM you will create ASM disk groups, comprised of one or more disks (usually RAW, but certified NFS storage will work as well). Oracle will take that disk group as the location for creating files, and will lay down files in 1MB extents across however many disks are available. The more disks that are used within a disk group, the more flexibility you will give Oracle to spread the I/O out among disks, resulting in better performance and improved redundancy. ASM disk groups can be used for all Oracle files, including the spfile, the controlfile, the online redo logs, and all datafiles. In addition, you can use an ASM disk group for your flashback recovery area (discussed in Chapter 8), as a location for all RMAN backups, flashback logs, and archived logs. Bear in mind, however, that ASM was created specifically for Oracle, so it cannot be used as a general purpose file system. As such, files in an ASM disk group are not visible at the OS, and files such as Oracle binaries and Oracle trace files must be kept on a regular file system (such as UFS or NTFS).
We mentioned that extents are written out in 1MBsizes, and this is true for all files except controlfiles and logfiles. Redo logs, controlfiles, and flashback logs use fine-grained striping, by default, which results in extents of 128K, rather than 1MB. This allows large I/Os to be split into smaller chunks and processed by more disks, resulting in better performance for these types of files.
Stripe and Mirror Everything (SAME)
ASM adheres to the same philosophy, which recommends to stripe and mirror everything. This is handled in ASM by allowing the setting of redundancy levels during the creation of a disk group. Normal redundancy implies that you have at least two disks, because every allocation unit (or extent) will be written twice, to two different disks within the disk group. High redundancy implies three-way mirroring, meaning every allocation unit (or extent) will be written to three separate disks within the disk group. This mirroring is not the traditional type of mirroring that you may be used to, however—this is done at the extent level. For example, let’s assume that we are mirroring with normal redundancy (two-way mirroring), and that we have five disks in a disk group. If we then create a 10MB file on that disk group, the first 1MB extent may be mirrored across disks 3 and 5, the next 1MB extent may be mirrored across disks 2 and 4, the next extent across disks 1 and 3, and so on. When all is said and done, every extent has been mirrored, but no two disks will contain identical data. If you choose external redundancy when creating a disk group, this is perfectly acceptable, but it implies that all mirroring is handled at the hardware level.
By the same token, ASM achieves striping by spreading the extents, aka allocation units, for a given file across all available disks in a disk group. So, your TEMP tablespace may be 4GB in size, but if you have a disk group with 10 disks in it, you will not care how the tablespace is laid out—Oracle with ASM will automatically spread the extents for this file across the disks, seeking to balance out the I/O and avoid hot spots on disk. If Oracle detects that a particular disk is getting too much I/O, it will attempt to read the mirrored copy of an extent from a different disk, if it is available. The same is true for all files, including redo logs.
Mirroring is actually performed to what are known as “partner” disks. Within an ASM disk group, any given disk can have a maximum of eight partners. This means that the extents written to a disk can be mirrored to any one of the eight partners defined for that disk. In our simple example, where we have only five disks, any disk can be the partner of another disk because we have not exceeded this limit. However, in a disk group with more than eight disks (say, hundreds or even thousands of disks), it is important to realize that each disk will be limited in the number of partners that can participate in the mirroring for that disk. This is done intentionally, as limiting the number of partners minimizes the possibility that a double disk failure could lead to data loss—this could only happen if the two disks that fail also happen to be partners. Utilizing high redundancy (triple-mirroring) reduces this likelihood even further. An ASM disk group will theoretically support up to 10,000 disks with a single ASM instance, spread across as many as 63 disk groups. ASM also supports up to 1 million files in a disk group. In Oracle Database 10g Release 1, only one ASM instance is allowed per node.