Tools for Additional
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
Sorts, Freelists, Checkpoints, and
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
The DBA needs to monitor these items on a
regular basis and tune them as needed to get peak performance from the
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:
Group by or Order by statements
Use of the distinct operator
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.
FUNCTION: Generate a summary of Disk Sort Area Usage
NEW_VALUE bs NOPRINT
FROM v$parameter WHERE name='db block size';
title80 "Instance Disk Area Average Sizes"
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
Freelist contention is shown by contention for
data blocks in the buffer cache and/or the existence of freelist
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.
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.