[Laskey99] Section 3.5. Sizing and Configuring Database Objects

来源：百度文库编辑：神马文学网时间：2024/04/29 01:06:07

3.5.
Proper sizing andconfiguration of database objects are critical to achieving maximumdatabase performance. Proper object sizing is an ongoing task; asobjects are created and modified, you must continue to examine theircharacteristics and make changes when necessary. Some sizing-relatedproblems that negatively impact performance are:

Tablespace fragmentation
Thisproblem, which leaves many unusable small extents scattered about atablespace, can result when objects are created with inappropriateINITIAL or NEXT extent sizes.

Row chaining
This problem,which causes the data from a single row to reside in multiple Oracleblocks, typically occurs when an insufficient PCTFREE setting isspecified and updates subsequently occur to the table.

Multiple extents
Multipleextents, which may cause data for a particular object to be spreadacross one or more datafiles, result when objects are created withimproper INITIAL or NEXT extent sizes. This problem may becomecritical when the MAXEXTENTS parameter is permitted to assume thedefault value, since an attempt to allocate an extent beyond thatnumber will result in a failure.

Log waits
Log waits, which cause a process towait while log buffer records are written to a log file or while alog file switch is occurring, can add significant processing time.These are usually caused by a combination of an insufficient numberof log files and log files that are too small.

Failure to extend a rollback segment
Such failures, which can cause atransaction to roll back, are caused when not enough rollbacksegments are allocated, or when the rollback segments allocated arenot large enough.
The following sections contain specific guidelines and suggestionsthat may help prevent some of these performance problems.

3.5.1. Tables

Tables are the basic units of datastorage in an Oracle database, so their configuration and resultingperformance will have a large impact on overall database performance.Some guidelines for table configuration are as follows:

Try to estimate how big a table will be and allocate a sufficiently large INITIAL extent to hold the entire table. However, if you are using Parallel Query, allocate the total space across as many extents in different datafiles as the degree of parallelism for the table.
Consider using multiple tablespaces for tables, each for a different size or type of table. For example, you might have three tablespaces: LARGE_DATA, MEDIUM_DATA, and SMALL_DATA, each of which would be used to hold tables of a particular size. If you are using multiple tablespaces for tables, be sure to allocate each table in the appropriate tablespace.
Be sure to assign a DEFAULT TABLESPACE to each user. If one is not assigned, Oracle will use the SYSTEM tablespace by default.
If possible, always allocate INITIAL and NEXT extents in multiples of the same size units; for example, allocate in multiples of 512K. This way, extents will be of uniform size and it will be easier to allocate additional extents without fragmenting the tablespace. Where possible, consider making all extents in a tablespace the same size.
Set the PCTINCREASE parameter to 0, in order to prevent runaway extent allocation and to preserve uniform extent sizes.
Set MAXEXTENTS to UNLIMITED. This will prevent running out of extents, since multiple extents have little performance impact in and of themselves (although widely scattered extents can negatively affect performance). Do this to prevent errors, but do not use it as a substitute for proper INITIAL sizing.
Set PCTFREE to if no updates will be performed on the table. If updates will be performed, try to estimate the degree to which columns of a row will grow, and allocate a PCTFREE that will prevent block chaining without excessive unused space in the block.
Set INITRANS to a value greater than 1 (the default) if multiple transactions will access the table simultaneously.
Set MAXTRANS to the maximum number of simultaneous accesses expected on the table. A lower value will result in one or more transactions waiting for a prior transaction to complete.

3.5.2. Indexes

Perhaps no other single feature ofOracle can provide as much performance improvement as the proper useof indexes. While many performance gains will result from tuning SQLstatements (see Chapter 8), there are also severalconfiguration guidelines we suggest you follow:

Create a separate tablespace for indexes, and make certain that the datafiles for this index tablespace are not on the same disk device as any datafiles for tablespaces that contain indexed tables.
Try to estimate the size of an index and allocate a sufficient INITIAL extent to hold the entire index, unless you are using Parallel Query, in which case you should allocate the total space across as many datafiles as the degree of parallelism for the index.
If possible, always allocate INITIAL and NEXT extents in multiples of the same size units; for example, allocate in multiples of 512K. This way, extents will be of uniform size and it will be easier to allocate additional extents without fragmenting the tablespace.
Set PCTINCREASE to in order to prevent runaway extent allocation and to preserve uniform extent sizes.
Set MAXEXTENTS to UNLIMITED. This guideline will prevent your running out of extents, since multiple extents have little performance impact in and of themselves (although widely scattered extents can negatively affect performance). Do this to prevent errors, but do not use it as a substitute for proper INITIAL sizing.

3.5.3. Rollback Segments

Rollback segments are used byOracle to maintain data consistency and to allow transactions to becancelled or rolled back. The use of rollback segments is fairly I/Ointensive, and the following guidelines apply to their configuration:

Create a separate tablespace for rollback segments and, if possible, place the datafiles for this tablespace on a different disk device from other Oracle datafiles.
Never create rollback segments in the SYSTEM tablespace (except for the temporary rollback segment required during database creation; see Chapter 2).
Be sure that there is enough space allocated to your rollback tablespace to allow rollback segments to grow as large as necessary to accommodate large update transactions. Remember that batch transactions tend to be large.
Always use the same value for the INITIAL and NEXT extents for rollback segments (define them in the DEFAULT STORAGE clause of the CREATE TABLESPACE statement). This guideline will prevent space fragmentation by allocating rollback segment space in equal-size chunks.
Remember that each rollback segment must have at least two extents, so the initial size of a segment will actually be the sum of INITIAL + NEXT.
Define an OPTIMAL value so that rollback segments that are required to grow in size to accommodate a large transaction can be shrunk to a more reasonable size. Don't make this size too small, however, or time will be wasted allocating additional extents to your rollback segments.

3.5.4. Sort Areas

Oracle uses the INIT.ORA parameterSORT_AREA_SIZE to allocate memory foruse in sorting data. When a sort cannot be completed in memory,Oracle uses temporary segments in the database, which is considerablyslower. A careful balance is required for SORT_AREA_SIZE, since largesizes can dramatically increase performance by decreasing I/O, butwill also use up memory and can result in paging.

Remember that this parameter applies to each user process. Each user process performing a sort will have SORT_AREA_SIZE memory allocated. So, if SORT_AREA_SIZE is set to 1 megabyte, and 100 user processes are performing sorts, a total of 100 megabytes of memory may be allocated.

3.5.5. Temporary Tablespaces

Wheninsufficient sort memory is allocated to the user process to performa required sort, Oracle performs the sort on disk by creatingtemporary segments in the tablespace specified by theTEMPORARY TABLESPACE parameter for theuser. In addition, temporary segments are used to perform complexqueries like joins, UNIONs, and MINUSes, and for index creation.Guidelines for temporary areas include the following:

Create a separate tablespace (usually called TEMP) for temporary segments, and place the datafile(s) for this tablespace on a separate disk device, if possible.
Specify INITIAL and NEXT parameters in the DEFAULT STORAGE clause of the CREATE TABLESPACE command. Use the same value for both in order to eliminate space fragmentation, which is particularly likely in the TEMP tablespace, where objects are constantly being created and dropped.
Be certain to specify a TEMPORARY TABLESPACE for each user. If one is not specified, Oracle defaults to SYSTEM, which is almost guaranteed to have a negative impact on performance.

3.5.6. Redo Logs

Redo logs, also calledonline redo log files, are critical toOracle's ability to recover from a failure. Properconfiguration of redo logs is critical not only to overall databaseperformance, but also to your ability to recover the database (seeChapter 4). Guidelines include the following:

Use Oracle's built-in mirroring capability and put multiple sets of redo log files on different disk devices.
Allocate enough redo log files so that Oracle does not have to wait for a file to complete archiving before it is reused. Oracle requires at least two redo log files, but four or more may be necessary.
Allocate redo log files that are large enough to prevent too many log switches, but small enough to support good recovery if the current online log file is lost in a failure. With smaller files, you will probably be able to recover all transactions that have been archived, whereas a large log file size exposes the database to the potential for more lost transactions.
Set the INIT.ORA parameter LOG_CHECKPOINT_INTERVAL to a value larger than the size of your redo log files. This will prevent checkpoints until the log file is full (which forces a checkpoint). This parameter is expressed in database blocks.

Remember that a log switch causes dirty (i.e., updated) buffers to be written to disk from the SGA.

If you are running Oracle7, consider setting the INIT.ORA parameter CHECKPOINT_PROCESS to TRUE. Doing so creates a separate process that performs the checkpoint, rather than the LGWR (Log Writer) process. See Chapter 10, for more information.

3.5.7. Archive Log Destination

An often overlooked aspect of configurationis making certain there is enough space available in the archive logdestination. If the database is running in archivelog mode when anonline redo log file fills, Oracle's ARCH process copies the contents of thatfile to the directory specified in the INIT.ORAparameter ARCHIVE_LOG_DEST. If this destinationis too small, ARCH is unable to copy the log file, and once allonline log files are full, the entire database stops until thesituation is resolved. Experienced DBAs will immediately recognizethat this condition is most likely to occur in the middle of thenight, just as REM sleep has begun!

Create Bookmark (Key: b)Create Bookmark
Create Note or Tag (Key: t)Create Note or Tag
Download (Key: d)Download
Email This Page (Key: e)Email This Page
PrintPrint

Html View (Key: h)Html View
Zoom Out (Key: -)Zoom Out
Zoom In (Key: +)Zoom In
Toggle to Full Screen (Key: f)
Previous (Key: p)Previous
Next (Key: n)Next