Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Tools for Additional Tuning Concerns Oracle Tips by Burleson

Once the DBA has tuned memory, tuned I/O, and tuned contention, there are still a couple of minor items that he or she needs to consider. Though these items will improve performance, if the other tuning areas are not taken care of first, any improvement to these areas would be masked--which is why they are addressed last. The final tuning areas concern sorts, freelists, checkpoints, and processes.

Sorts, Freelists, Checkpoints, and Processes

Improvement of sort speed provides obvious benefits. Freelists provide information on the free blocks inside database tables. If there aren't enough freelists, this can have an impact on performance. Checkpoints are writes from buffers to disk. Checkpoints, if excessive, can adversely effect performance as well: if there aren't enough checkpoints, recovery from disasters can be impeded.

The DBA needs to monitor these items on a regular basis and tune them as needed to get peak performance from the database.

Tuning Oracle Sorts

Sorts are done when Oracle performs operations that retrieve information and requires the information retrieved to be an ordered set--in other words, sorted. Sorts are done when the following operations are performed:

•    Index creation

•    Group by or Order by statements

•    Use of the distinct operator

•    Join operations

•    Union, Intersect and Minus set operators

Each of these operations requires a sort. Primarily, there is one indicator that your sorts are going to disk and therefore your sort area in memory is too small. This area is defined by the initialization parameters SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE in Oracle8, Oracle8i, and Oracle.

The primary indicator is the sorts (disk) statistic shown in Figure 13.19. If this parameter exceeds 10 percent of the sum of sorts(memory) and sorts(disk), increase the SORT_AREA_SIZE parameter. Large values for this parameter can induce paging and swapping, so be careful not to overallocate. In Oracle8i and Oracle, you can increase the SORT_AREA_SIZE for a specific session by using the ALTER SESSION SET SORT_AREA_SIZE = x, where x is the size in bytes for the new sort area allocation. This dynamic sort area allocation allows the developer or DBA to tune the sort needs on a per-session or per-transaction basis.

For standard sorts, you should set the SORT_AREA_SIZE to the average sort size for your database. The temporary tablespaces initial and next default storage parameters should be set to the value of SORT_AREA_SIZE. For use with parallel query sorts, a temporary tablespace should be spread (striped) across as many disks as the degree of parallelism.

The initialization parameter SORT_MULTIBLOCK_READ_COUNT does for sorts what DB_MULTIBLOCK_READ_COUNT does for full table scans: it forces Oracle to read at least that amount of data specified per merge read pass.

The views that are used to help in the sort tuning process are V$SORT_SEGMENT and V$SORT_USAGE. These views are not populated unless disk sorts occur. The V$SORT_SEGMENT view contains a single line for each sort operation that gives detailed information about segment size in  blocks. If you are getting excessive disk sorts, you should query this view to calculate the best possible sort area size. An example query to give average sort area size is shown in Source 13.19.

SOURCE 13.19 Example sorts report.

REM

REM FUNCTION: Generate a summary of Disk Sort Area Usage

REM

REM disksort.sql

REM

COLUMN value NEW_VALUE bs NOPRINT

SELECT value FROM v$parameter WHERE name='db block size';

START title80 "Instance Disk Area Average Sizes"

SPOOL rep_out\&&db\disk_sort

SELECT

     Tablespace_name,

     COUNT(*) areas,

     (SUM(total_blocks)/COUNT(*))*&&bs avg_sort_bytes

FROM v$sort_segment

GROUP BY tablespace_name;

SPOOL OFF

Reducing Freelist Contention

As stated above, a freelist is a list of data blocks that contain freelists. Every table has one or more freelists. This is determined by the storage clause parameter FREE_LISTS and FREE_LIST_GROUPS; FREE_LISTS has its default value set to 1. The maximum value of FREE_LISTS is blocksize-dependent and should be set to the number of simultaneous update processes that will be inserting to or updating the table. The setting of this parameter at the time the table is created determines the number of freelists for the table. The FREE_LIST_GROUPS parameter is used in only parallel server (not parallel query!) installations and should be set equal to the number of instances accessing the table. Both parameters apply to tables, only FREE_LISTS applies to indexes.

Under ORACLE7, each table specifies its own number of freelists by use of the  FREELISTS parameter of the CREATE TABLE command; this parameter will default to 1 if not specified explicitly.

Freelist contention is shown by contention for data blocks in the buffer cache and/or the existence of freelist waits.

Tuning Checkpoints

Checkpoints provide for rolling forward after a system crash. Data is applied from the time of the last checkpoint forward from the redo entries. Checkpoints also provide for reuse of redo logs. When a redo log is filled, the LGWR process automatically switches to the next available log. All data in the now-inactive log is written to disk by an automatic checkpoint. This frees the log for reuse or for archiving.

Checkpoints occur when a redo log is filled, when the INIT.ORA parameter  LOG_CHECKPOINT_INTERVAL ORACLE7 is reached (total bytes written to a redo log), or the elapsed time has reached the INIT.ORA parameter LOG_CHECKPOINT_TIMEOUT, expressed in seconds, or every three seconds, or when an ALTER SYSTEM command is issued with the CHECKPOINT option specified.

While frequent checkpoints will reduce recovery time, they will also degrade performance. Infrequent checkpoints will increase performance but also increase required recovery times. To reduce checkpoints to occur only on log switches, set LOG_CHECKPOINT_INTERVAL to larger than your redo log size, and set LOG_CHECKPOINT_TIMEOUT to 0.

If checkpoints still cause performance problems, set the INIT.ORA parameter CHECKPOINT_PROCESS to TRUE to start the CKPT process running. This will free the DBWR from checkpoint duty and improve performance. The INIT.ORA parameter PROCESSES may also have to be increased. Note that on Oracle8 and later, the checkpoint process is not optional and is started along with the other Oracle instance processes.

Another option new with Oracle8i was the concept of fast-start checkpointing. In order to configure fast-start checkpointing, you set the initialization parameter FAST_START_IO_TARGET. The FAST_START_IO_TARGET parameter sets the number of I/O operations that Oracle will attempt to limit itself to before writing a checkpoint. This feature is available only with Oracle Enterprise Edition.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.