14.4 Instance tuning

---

Though the approach to these discussions is divided into several areas, performance tuning is an overall strategy. Many times a change to a specific area can affect other areas of the database. For example, tuning the SQL queries and ensuring the query is using bind variables may require that you reduce the size of the shared pool, because there would be more shared queries, which would mean fewer queries to be cached and hence a smaller shared pool. On the other hand, adding an index could improve the query performance, but could reduce the performance of a DML operation, because Oracle has to perform an extra I/O to update this new index.

Under this section, let us look at tuning some of the instance-specific areas in a RAC instance. Once the instance-specific parameters have been tuned, these changes can be applied to the other instances.

14.4.1 Memory management

Each instance uses the memory of the node and operating system in which it resides. The normal practice is to allocate about 80% of the system's memory for use by Oracle. The memory at the O/S level is allocated by setting certain kernel parameters that configure the shared memory and semaphores. Oracle uses certain parameters defined in the initialization file to utilize the memory allocated by the O/S kernel. The following parameters and dynamic views directly or indirectly influence the memory management of an instance.

SHARED_POOL

The shared pool contains the library cache and dictionary cache areas. The library cache is where Oracle will store the shared SQL queries and the dictionary cache contains the data-dictionary-related information. Sizing the SHARED_POOL depends on the SQL queries of the application, how many SQL queries are present and how large each query would be. Normally, a large shared pool may be required when the SQL queries do not use bind variables and instead use hard-coded values or literals while accessing information from the database.

Oracle provides advice on the shared pool size through the V$SHARED_POOL_ADVICE dynamic view. The statistics or advice is only provided if the STATISTICS_LEVEL parameter has been enabled and has been set to TYPICAL.

SQL> show parameter shared  / NAME                         TYPE          VALUE --------------------------   -----------   ---------- hi_shared_memory_address     integer       0 max_shared_servers           integer       20 shared_memory_address        integer       0 shared_pool_reserved_size    big integer   107374182 shared_pool_size             big integer   2147483648 shared_server_sessions       integer       0 shared_servers               integer       0

V$SHARED_POOL_ADVICE

As mentioned above, the shared pool contains SQL statements; sizing of the shared pool is directly related to the quality of these SQL statements. The higher the size of the shared pool, the lower will be number of parses, because if sufficient memory is available then these SQL statements will not be aged out of the cache. The V$SHARED_POOL_ADVICE view provides information about library cache memory and provides additional help by predicting the effect of increasing or decreasing the amount of shared pool.

SELECT       SHARED_POOL_SIZE_FOR_ESTIMATE SPSE,       SHARED_POOL_SIZE_FACTOR SPSF, ESTD_LC_SIZE        ELS, ESTD_LC_MEMORY_OBJECTS ELMO,        ESTD_LC_TIME_SAVED ELTS,        ESTD_LC_TIME_SAVED_FACTOR ELTSF,        ESTD_LC_MEMORY_OBJECT_HITS ELMOH FROM V$SHARED_POOL_ADVICE / SPSE    SPSF  ELS   ELMO    ELTS  ELTSF     ELMOH ----  ------ ---- ------  ------  -----   ------- 1216   .5938  422  66163  109519      1   9175821 1424   .6953  422  66163  109519      1   9175821 1632   .7969  422  66163  109519      1   9175821 1840   .8984  422  66163  109519      1   9175821 2048       1  422  66163  109519      1   9175821 2256  1.1016  422  66163  109519      1   9175821 2464  1.2031  422  66163  109519      1   9175821 2672  1.3047  422  66163  109519      1   9175821 2880  1.4063  422  66163  109519      1   9175821 3088  1.5078  422  66163  109519      1   9175821 3296  1.6094  422  66163  109519      1   9175821 SPSE    SPSF  ELS   ELMO    ELTS  ELTSF     ELMOH ----  ------ ---- ------  ------  -----   ------- 3504  1.7109  422  66163  109519      1   9175821 3712  1.8125  422  66163  109519      1   9175821 3920  1.9141  422  66163  109519      1   9175821 4128  2.0156  422  66163  109519      1   9175821 15 rows selected.

The value of 1 in the SHARED_POOL_SIZE_FACTOR column indicates the current shared pool size. The rows above this value indicate that the number of parse counts would be significantly high if the value was less than the current SHARED_POOL size and the rows below this number indicate that the parse counts would reduce when this value is increased.

The data dictionary view also reports on the library cache information. Through the ESTD_LC_TIME_SAVED (ELTS) column it provides the amount of parse time that is saved because the library cache had already cached the information. Conversely, this is the amount of time (in seconds) that Oracle would have spent reloading the query into the library cache if the shared pool was not set to the current value.

The query below against the V$DB_OBJECT_CACHE view provides an indication on the number of database objects that have been cached. The output from the query provides the number of various objects by type that is currently in a KEEP state and how many are not. For example, there are four INDEX objects retained and 111 objects not retained. Similarly, none of the cursors has been kept. This view also provides information such as the number of times the object had to be loaded back because of space restrictions.

SELECT      TYPE, KEPT,      COUNT (*),      SUM (SHARABLE_MEM)  FROM V$DB_OBJECT_CACHE  GROUP BY  TYPE,     KEPT / TYPE       KEP   COUNT(*)  SUM(SHARABLE_MEM) ---------- ----  --------  ----------------- TYPE       NO         288            4768575 VIEW       NO         434            1103864 INDEX      NO         111             657241 INDEX      YES          4               5066 TABLE      NO         387            1467592 TABLE      YES         21              53696 CURSOR     NO        9120          123875555 CLUSTER    NO           4               8131 CLUSTER    YES          6              12741 LIBRARY    NO           8              10378 PACKAGE    NO          40            1213688 TYPE       KEP   COUNT(*)  SUM(SHARABLE_MEM) ---------- ----  --------   ----------------- PACKAGE    YES          2             483702 PUB_SUB    NO          11              16950 SYNONYM    NO         350            1631810 TRIGGER    NO          49             271407 FUNCTION   NO          62            1101355 OPERATOR   NO           2               6762 SEQUENCE   NO          21              54017 PROCEDURE  NO           1              20480

V$LIBRARY_CACHE_MEMORY

Continuing with the options of tuning the shared pool, the V$LIBRARY_CACHE_MEMORY dynamic view provides the amount of memory allocated to the library cache memory objects in different namespaces.

SELECT       LC_NAMESPACE NAMESPACE,       LC_INUSE_MEMORY_OBJECTS USED_BY_OBJ,       LC_INUSE_MEMORY_SIZE USED_SIZE,       LC_FREEABLE_MEMORY_OBJECTS AVL_FOR_OBJ,       LC_FREEABLE_MEMORY_SIZE FREE_SIZE FROM V$LIBRARY_CACHE_MEMORY / NAMESPACE        USED_BY_OBJ USED_SIZE AVL_FOR_OBJ FREE_SIZE ---------------- ----------- --------- ----------- --------- BODY                       6         0          76         1 CLUSTER                   13         0           8         0 INDEX                     14         0         220         0 JAVA DATA                  0         0           0         0 JAVA RESOURCE              0         0           0         0 JAVA SOURCE                0         0           0         0 OBJECT                     0         0           0         0 OTHER/SYSTEM               0         0          22         0 PIPE                       0         0           0         0 SQL AREA                  31         0       12803       105 TABLE/PROCEDURE          139         0        4197        16 NAMESPACE        USED_BY_OBJ USED_SIZE AVL_FOR_OBJ FREE_SIZE ---------------- ----------- --------- ----------- --------- TRIGGER                   46         0         152         0 12 rows selected.

V$LIBRARYCACHE

The query below will provide statistics that will help determine the performance of the library cache. Two of the key columns that should be monitored are RELOADS and INVALIDATIONS. RELOADS indicates the number of times the objects had to be reloaded into the library cache because they were not kept in memory. The reasons for this would be because of objects aging out of the library cache, objects not being used for a long time, or being flushed out because there was insufficient memory. Since RELOADS also contains the count for the load that occurred the very first time before the object was pinned, it should be observed if this value is growing. If the reloads were due to objects being flushed out, increasing the shared pool would be a potential option.

INVALIDATIONS occur when the objects become invalid because the underlying dependent objects may have been modified or may have changed the status to invalid. Invalidations of objects cause the objects to be reparsed after the underlying dependent objects have become valid again.

COL PINS FORMAT 99999999 COL RELOADS FORMAT 9999999 COL GETHITS FORMAT 9999999 COL PINHITS FORMAT 999999999 COL INVAL FORMAT 9999999 SELECT NAMESPACE,        GETS,        GETHITS,        PINS,        PINHITS,        RELOADS,        INVALIDATIONS INVAL FROM V$LIBRARYCACHE / NAMESPACE           GETS  GETHITS     PINS  PINHITS RELOADS INVAL ---------------  ------- -------- -------- -------- ------- ----- SQL AREA          399001   382726 20997285 20972663    1084  8154 TABLE/PROCEDURE  7381256  7373683 10108219 10087081    4937     0 BODY               26869    26823    26869    26815       8     0 TRIGGER          1021324  1021012  1021591  1020945      27     0 INDEX             104208   103909    54768    54408       8     0 CLUSTER             6235     6221     5365     5343       0     0 OBJECT                 0        0        0        0       0     0 PIPE                   0        0        0        0       0     0 JAVA SOURCE            0        0        0        0       0     0 JAVA RESOURCE          0        0        0        0       0     0 JAVA DATA              0        0        0        0       0     0 11 rows selected. 

DB_CACHE_SIZE

DB_CACHE_SIZE is a new parameter introduced in Version 9i that replaces the DB_BLOCK_BUFFERS parameter and defines the default buffer pool size. In situations where multiple block sizes are defined, normally the default cache size corresponds to the default database block size.

For every block size defined, a corresponding DB_CACHE_SIZE is to be created. This is to ensure that the data retrieved using a specific block size has a corresponding memory area and all rows are of the same block size.

Similar to the advice available for the shared pool, Oracle also provides advice for the DB_CACHE_SIZE. In order to enable the advice feature, the following two parameters should be enabled, the DB_CACHE_ADVICE parameter should be set to ON and the STATISTICS_LEVEL should be set to TYPICAL.

DB_CACHE_ADVICE

Turning this parameter ON enables statistics collection for the different cache sizes and Oracle populates the V$DB_CACHE_ADVICE view. This parameter is automatically enabled when the STATISTICS_LEVEL is set to TYPICAL or ALL.

V$DB_CACHE_ADVICE

Below is a query^[3] that provides information from this view. Similar to V$SHARED_POOL_ADVICE, this query also provides advice on the optimal sizing of the DB_CACHE_SIZE parameter. In the output below, where the column EST_PHYSICAL_READ_FACTOR has a value of 1, the DB_CACHE_SIZE has been set to 512 MB. At this value the number of the estimated number of physical reads is 2,266,628. Setting this value lower than the current value would increase the number of physical reads. Increasing the value of DB_CACHE_SIZE to say 720 MB would reduce the physical reads, though not significantly, as indicated below.

column size_for_estimate       format 999,999,999 heading                                 'Cache Size (m)' column buffers_for_estimate    format 999,999,999 heading 'Buffers' column estd_physical_          format 999.90 heading  read_factor                    'Estd Phys|Read Factor' column estd_physical_reads     format 999,999,999,999 heading Phys|Reads' SELECT           SIZE_FOR_ESTIMATE,           BUFFERS_FOR_ESTIMATE,           ESTD_PHYSICAL_READ_FACTOR,           ESTD_PHYSICAL_READS     FROM V$DB_CACHE_ADVICE WHERE NAME='DEFAULT' AND        BLOCK_SIZE =          ( SELECT VALUE          FROM V$PARAMETER          WHERE NAME ='DB_BLOCK_SIZE' ) AND ADVICE_STATUS ='ON' /    Cache              Estd Phys     Estd Phys Size (m)   Buffers  Read Factor         Reads --------  --------  -----------  ------------       48     5,739        89.43   202,695,043       96    11,478        84.00   190,395,258      144    17,217        80.78   183,093,708      192    22,956        77.80   176,354,748      240    28,695        75.91   172,069,835      288    34,434        51.73   117,244,628      336    40,173        10.25    23,238,498      384    45,912         4.77    10,812,383      432    51,651         1.81     4,100,150      480    57,390         1.21     2,732,271    Cache              Estd Phys     Estd Phys Size (m)   Buffers  Read Factor         Reads --------  --------  -----------  ------------     512     61,216         1.00     2,266,628      528     63,129          .90     2,040,032     576     68,868          .70     1,586,507     624     74,607          .62     1,409,380     672     80,346          .56     1,268,110     720     86,085          .53     1,195,732     768     91,824          .51     1,145,432     816     97,563          .50     1,135,638     864    103,302          .50     1,133,148     912    109,041          .50     1,132,152    Cache              Estd Phys     Estd Phys Size (m)   Buffers  Read Factor         Reads --------  --------  -----------  ------------      960    114,780          .50     1,132,152 21 rows selected. 

DB_KEEP_CACHE_SIZE

This parameter is used to specify the size of the keep buffer pool. Oracle does not support multiple cache sizes for the keep cache. DB_KEEP_CACHE_SIZE is based on the default block size. If a value of this parameter is not specified in the parameter, Oracle assigns a value of ZERO. This parameter replaces the BUFFER_POOL_KEEP parameter.

DB_RECYCLE_CACHE_SIZE

Similar to the DB_KEEP_CACHE_SIZE parameter, this parameter is used to specify the size of the recycle buffer pool. DB_RECYCLE_CACHE_SIZE is also based on the default block size. Multiple block sizes do not change the size of this parameter. This parameter replaces the BUFFER_POOL_RECYCLE parameter.

V$BUFFER_POOL

This view and the query below provide information about all buffer pools available for the instance. The output indicates that the instance is currently only configured with a single DB_CACHE_SIZE or buffer pool.

SELECT       NAME,       BLOCK_SIZE,       BUFFERS,       SET_COUNT,       LO_SETID,       HI_SETID FROM V$BUFFER_POOL; NAME    BLOCK_SIZE  BUFFERS  SET_COUNT  LO_SETID HI_SETID ----    ---------- --------  --------- --------- -------- DEFAULT       8192   122432          2         5        6 

V$SGA_DYNAMIC_COMPONENTS

This view and the query below display information about the last 100 completed SGA resize operations. However, this does not include any work-in-progress operations.

SELECT     COMPONENT,     CURRENT_SIZE,     OPER_COUNT,     GRANULE_SIZE FROM V$SGA_DYNAMIC_COMPONENTS; COMPONENT     CURRENT_SIZE  OPER_COUNT  GRANULE_SIZE ------------  ------------  ----------  ------------ shared pool     2147483648           0      16777216 large pool       117440512           0      16777216 buffer cache    1073741824           0      16777216 

PGA_AGGREGATE_TARGET

This parameter replaces the SORT_AREA and SORT_AREA_RETAINED_ SIZE parameter available in the previous versions of Oracle. This parameter specifies the target aggregate PGA memory available to all server processes attached to the instance. This parameter enables the automatic setting of the SQL work area used for sort/merge operations during SQL query execution.

Similar to the DB_CACHE_SIZE and the SHARED_POOL parameters, Oracle also provides advice on the PGA_AGGREGATE_TARGET provided the WORKAREA_SIZE_POLICY parameter has been defined.

V$PGASTAT

This view provides the PGA memory usage statistics and is a cumulative value since instance startup. The value from this view provides a great detail of information in sizing the PGA_AGGREGATE_TARGET.

COL NAME FORMAT A45  SELECT * FROM V$PGASTAT; NAME                                 VALUE  UNIT --------------------------------- --------- ------- aggregate PGA target parameter    524288000 bytes aggregate PGA auto target         442920960 bytes global memory bound                26214400 bytes total PGA inuse                    32146432 bytes total PGA allocated                48112640 bytes maximum PGA allocated              49292288 bytes total freeable PGA memory           3997696 bytes PGA memory freed back to OS         1179648 bytes total PGA used for auto workareas         0 bytes NAME                                  VALUE  UNIT ---------------------------------     ------ ------ maximum PGA used for auto workareas   376832 bytes total PGA used for manual workareas        0 bytes maximum PGA used for manual workareas      0 bytes over allocation count                      0 bytes processed                            5899264 bytes extra bytes read/written                   0 bytes cache hit percentage                     100 percent 16 rows selected.

V$PGA_TARGET_ADVICE

This dynamic view provides statistics on the impact on the PGA when the PGA_AGGREGATE_TARGET parameter is changed.

SELECT       PGA_TARGET_FOR_ESTIMATE PTFE,       PGA_TARGET_FACTOR PTF,       ADVICE_STATUS ADS,       BYTES_PROCESSED BP,       ESTD_EXTRA_BYTES_RW EEBR,       ESTD_PGA_CACHE_HIT_PERCENTAGE EPCHP,       ESTD_OVERALLOC_COUNT EOC FROM V$PGA_TARGET_ADVICE / PTFE        PTF  ADS         BP     EEBR EPCHP   EOC ---------- ---- ----  --------- -------- ------ ---- 26214400   .125   ON   35991552 30892032    54    10 52428800    .25   ON   35991552  7723008    82     0 104857600    .5   ON   35991552        0   100     0 157286400   .75   ON   35991552        0   100     0 209715200     1   ON   35991552        0   100     0 251658240   1.2   ON   35991552        0   100     0 293601280   1.4   ON   35991552        0   100     0 335544320   1.6   ON   35991552        0   100     0 377487360   1.8   ON   35991552        0   100     0 419430400     2   ON   35991552        0   100     0 629145600     3   ON   35991552        0   100     0 838860800     4   ON   35991552        0   100     0 1258291200    6   ON   35991552        0   100     0 1677721600    8   ON   35991552        0   100     0 14 rows selected.

SGA_MAX_SIZE

Setting this parameter allows dynamic allocation of memory configuration parameters such as the SHARED_POOL, DB_CACHE_SIZE, etc. If this parameter is not set, Oracle calculates this value based on the combined values of the other memory configuration parameters and this becomes the upper bound value of the SGA_MAX_SIZE. In this case, changing the values of the other parameters is not possible.

OPTIMIZER_INDX_COST_ADJ

This parameter affects the cost calculation of an index. If the default value of this parameter is set to 100%, then it causes the optimizer to evaluate index-based access at regular cost. Setting the value of this parameter to 50% computes the cost of index-based access to half the normal access method. If this value is set very low, say around 5%, the optimizer will prefer index scans over full table scans thus improving overall performance of queries.

OPTIMIZER_INDEX_CACHING

Setting this parameter higher than its default value of 0 helps adjust the behavior of cost-based optimizer to favor nested loop joins over in-list iterators.

Setting this parameter to a higher value, say between 90 and 95, makes nested loop joins and in-list iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loop joins over hash or sort-merge joins and to pick indexes using in-list iterators over other indexes or full table scans.

The following query lists all long-running operations on the database. The output from this query indicates a high number of hash joins and sort- merge operations on the database.

COL IID FORMAT 99 COL OPNAME FORMAT A11 COL SOFAR FORMAT 9999999 COL TOTAL FORMAT 9999999 COL UNITS FORMAT A8 COL TR FORMAT 999999 COL ES FORMAT 999999     SELECT       INST_ID IID,       OPNAME,       SOFAR,       TOTALWORK TOTAL,       UNITS,       START_TIME,       TIME_REMAINING TR,       ELAPSED_SECONDS ES FROM GV$SESSION_LONGOPS WHERE TIME_REMAINING> 0; / INST_ID OPNAME       SOFAR    TW    UNITS   START_TIME              TR    ES ------- -----------  -----  ----  -------   --------------------  ----  ----       2 Hash Join      915   945   Blocks   04-DEC-2002 14:58:07    43  1299       2 Hash Join      885   945   Blocks   04-DEC-2002 14:59:58    82  1203       2 Hash Join      930   945   Blocks   04-DEC-2002 15:00:17    19  1190       2 Hash Join      810   945   Blocks   04-DEC-2002 15:00:20   193  1159       2 Hash Join      915   945   Blocks   04-DEC-2002 15:00:57    38  1144       2 Hash Join      810   945   Blocks   04-DEC-2002 15:02:38   170  1018       1 Sort Output    157  2047   Blocks   04-DEC-2002 15:13:24  5008    41       1 Sort Output    436  2047   Blocks   04-DEC-2002 15:12:28  1696   459       1 Sort Output    162  2047   Blocks   04-DEC-2002 15:12:56  4945   425       1 Sort Output    157  2047   Blocks   04-DEC-2002 15:13:24  5044   419       1 Sort Output    148  2047   Blocks   04-DEC-2002 15:13:39  4978   388       1 Sort Output    148  2047   Blocks   04-DEC-2002 15:13:48  4991   389       2 Sort Output    317  2047   Blocks   04-DEC-2002 15:22:23  8879  1627       2 Sort Output    317  2047   Blocks   04-DEC-2002 15:22:23  8896  1630       2 Sort Output    317  2047   Blocks   04-DEC-2002 15:22:38  8983  1646       2 Sort Output    317  2047   Blocks   04-DEC-2002 15:22:41  8999  1649       2 Sort Output    317  2047   Blocks   04-DEC-2002 15:22:50  8983  1646 

Setting OPTIMIZER_INDX_COST_ADJ to 5 and OPTIMIZER_INDEX_CACHING to 95 changes the optimizer behavior, and the new execution plans generated by Oracle for the same query eliminates these high latencies during query operations.

SELECT       OPNAME,       SOFAR,       START_TIME,       TIME_REMAINING,       ELAPSED_SECONDS FROM V$SESSION_LONGOPS; / OPNAME             SOFAR START_TIME           TIME_REMAINING ELAPSED_SECONDS ---------------- ------- -------------------- -------------- --------------- Instance Recove  1638400 06-DEC-2002 17:03:43              0              31 Instance Recove        1 06-DEC-2002 17:03:43              0              39 Sort Output         2444 06-DEC-2002 17:08:34              0              12 Sort Output         2444 06-DEC-2002 17:08:34              0              12

The GV$SESSION_LONGOPS view provides visibility to the time taken to execute long-running operations under Oracle, providing the estimated total time and the time taken so far. The output from this query helps determine what kind of operations in the system take a long time to complete.

14.4.2 Log management

All the files and tablespaces, with the exception of redo log files, undo tablespaces, and archive log files, are shared across all instances. Redo log files, undo tablespaces, and archive log files store instance- specific information and hence there are multiple copies of these files, actually a set per instance. Having stated this, tuning of these files and their corresponding parameter falls under the category of tuning an instance.

In a RAC implementation that uses raw partitions to store data files, redo log files are also placed on these shared raw partitions. All tuning parameters and conditions also apply to tuning redo log files on a RAC instance.

Tuning this area of the instance has a corresponding effect on tuning the recovery management of the instance. This is because the redo log files, undo tablespaces, and archive log files play an important part in recovery management of both the instance and the database during instance failure or database failure.

Some of the parameters and dynamic views that help in tuning the log and recovery management of an instance will be discussed below.

FAST_START_MTTR_TARGET

This parameter is new in Oracle 9i and replaces the FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL parameters available in the previous versions of Oracle. This parameter enables specification of the number of seconds that the database should take to perform crash recovery of a single instance.

It allows the administrator to establish a distance between LGWR and the checkpoint position based on the estimated time to apply redo information (in seconds). After instance startup, Oracle 9i will use estimates of redo read and data block read/write to determine how best to reach the FAST_START_MTTR_TARGET and translate that into dynamic settings for FAST_START_IO_TARGET and LOG_CHECKPOINT_ INTERVAL. As the instance remains active/up, and actual read/write time statistics accumulate and the initial ''best guess'' estimates are replaced by real data. The dynamic settings for LOG_CHECKPOINT_ INTERVAL and FAST_START_IO_TARGET adapt to reflect changes in the average I/O times. This effectively makes instance recovery time self-tuning and responsive to changes in the operating environment.

The maximum value for FAST_START_MTTR_TARGET is 3600 (1 hour). When values are set that exceed this value, they are rounded down. There is no predefined minimum value; however, when values are set too low it may not be possible to achieve the required MTTR target, as this is limited by the low limit of the target number of dirty buffers, which is 1000. Added to this value is the time taken to mount the database.

If the value is set too low, then the effective MTTR target will be the best MTTR target the system can achieve. If the value is set high, the effective MTTR is estimated based on the whole buffer cache being dirty. The ESTIMATED_MTTR column in the V$INSTANCE_RECOVERY view can be used to view the effective MTTR. If the parameter setting, shown by the TARGET_MTTR column, is consistently different to the effective MTTR it should be adjusted, since this means it is set at an unrealistic value.

If FAST_START_MTTR_TARGET is set to a low value, then the database will be writing to disk very frequently and this could easily result in system slowdown. The parameter should therefore be adjusted such that the system is not doing excessive checkpoints but, at the same time, the recovery time is still within acceptable limits.

The output below is an extract from the alert log file and indicates that the FAST_START_MTTR_TARGET has been set to 100 and has enabled LOG_CHECKPOINT_INTERVAL:

Wed Dec 25 11:17:57 2002 ALTER SYSTEM SET fast_start_mttr_target= 100  SCOPE= MEMORY;  Wed Dec 25 11:18:26 2002 LOG_CHECKPOINT_INTERVAL was set when MTTR advisory was switched on. Wed Dec 25 11:20:37 2002

V$INSTANCE_RECOVERY

This view provides estimates of the amount of time it would take for a recovery to complete. The two columns ESTIMATED_MTTR and TARGET_MTTR provide an insight as to the actual amount of time it would take for crash recovery to complete. While the TARGET_MTTR is the value defined by the parameter FAST_START_MTTR_TARGET, the ESTIMATED_MTTR is the actual value based on the number of redo blocks being generated.

For example, an update operation to update one column of a 605,000 row table increased the ESTIMATED_MTTR value from 77 to 112, showing that it requires more MTTR to recover compared to the actual value specified by the FAST_START_MTTR_TARGET parameter. In order to maintain this recovery time, Oracle performs more frequent checkpoints that force writing of changes to the redo log files.

SELECT       RECOVERY_ESTIMATED_IOS RECEIOS,       ACTUAL_REDO_BLKS,       TARGET_REDO_BLKS,       TARGET_MTTR TMTTR,       ESTIMATED_MTTR EMTTR,       CKPT_BLOCK_WRITES CKPTBLKWRITS FROM V$INSTANCE_RECOVERY /    RECEIOS  ACTUAL_REDO_BLKS TARGET_REDO_BLKS   TMTTR   EMTTR CKPTBLKWRITS ---------- ----------------- ---------------- ------- ------- ------------       4836             77860           200000     100      77            0 SQL> /    RECEIOS  ACTUAL_REDO_BLKS TARGET_REDO_BLKS   TMTTR   EMTTR CKPTBLKWRITS ---------- ----------------- ---------------- ------- ------- ------------      11220            221258           200000     100      96        35193 SQL> /    RECEIOS  ACTUAL_REDO_BLKS TARGET_REDO_BLKS   TMTTR   EMTTR CKPTBLKWRITS ---------- ----------------- ---------------- ------- ------- ------------      12081            200023           200000     100     101        40762 SQL> /    RECEIOS  ACTUAL_REDO_BLKS TARGET_REDO_BLKS   TMTTR   EMTTR CKPTBLKWRITS ---------- ----------------- ---------------- ------- ------- ------------      12118            185539           200000     100     112        43465

While MTTR is important to maintain service level agreements, setting the FAST_START_MTTR_TARGET to a small value could hinder performance due to frequent checkpoints.

V$MTTR_TARGET_ADVICE

Oracle also provides another dynamic view for tuning the MTTR value. This view is populated only if the FAST_START_MTTR_TARGET parameter is enabled. This advisor contains estimates on the number of physical I/Os for the MTTR corresponding to each row.

Like all other advisors, this advisor also provides estimates for various values of the MTTR_TARGET. For example, setting the FAST_START_MTTR_TARGET value to 150 would support a dirty buffer limit of 28,298 blocks, compared to 9875 blocks if the parameter were set to 100.

SELECT       MTTR_TARGET_FOR_ESTIMATE MTE,       DIRTY_LIMIT DL,       ESTD_CACHE_WRITES ECW,       ESTD_TOTAL_WRITES ETW,       ESTD_TOTAL_IOS ETI FROM V$MTTR_TARGET_ADVICE / SQL>/ MTE        DL    ECW    ETW     ETI ------ ------ ------ ------ -------  76      1000 103686 103686  126144 100      9875  91355  91355  113813 150     28298  91355  91355  113813 200     46721  91355  91355  113813

14.4.3 Undo management

Under Oracle 9i, with the new undo management architecture, every transaction that starts is allocated an undo segment of its own. If there are no undo segments available, a new undo segment is created automatically. However, if there is no space available for a new undo segment to be created, then Oracle assigns the transaction to a segment that is currently in use by another transaction.

When an instance is started, 10 undo segments are created; subsequently, for every five sessions, Oracle creates one undo segment. As mentioned earlier, when a new transaction is unable to identify an undo segment, it creates one. This way, on busy systems that started with 10 undo segments, this number of segments would grow very large, depending on the number of concurrent transactions and the duration of these transactions. A transaction retains an undo segment through the life of the transaction.

After undo segments are created, and as the database warms up and more and more users and transactions get started, a shutdown of the instance will reduce the number of undo segments back to the original 10. Subsequently, when the instance is started, only 10 segments are available and Oracle once again starts building the undo segments.

This is an expensive operation and shutting down instances frequently is discouraged. Since undo segments are automatically created, there is no significant maintenance involved. Undo segments could be monitored using the following dynamic performance views.

V$UNDOSTAT

This dynamic view helps in the determination of the amount of undo space required for the current workload. The values in the underlying fixed table (X$KTUSMST) are used by Oracle for automatic undo management. The view and the underlying X$ table retain the data spanning a 7-day cycle storing a maximum of 1008 rows. The view provides information such as the query length, transaction concurrency, and the amount of space consumed.

COL QUERYLEN FORMAT 9999999 COL STEALCNT FORMAT 9999999 SELECT       END_TIME,       UNDOBLKS,       TXNCOUNT,       MAXQUERYLEN QUERYLEN,       MAXCONCURRENCY TXNCONCURRENCY,       EXPSTEALCNT STEALCNT FROM V$UNDOSTAT; END_TIME             UNDOBLKS TXNCOUNT QUERYLEN TXNCONCURRENCY STEALCNT -------------------- -------- -------- -------- -------------- -------- 19-DEC-2002 05:40:35        0        0       0         0             0 19-DEC-2002 04:00:35        5   558434     236        10             0 19-DEC-2002 03:50:35       15   558380     227         5             0 19-DEC-2002 03:40:35       16   558309     226        11             0 19-DEC-2002 02:10:35      164   555686     231         2             0 19-DEC-2002 02:00:35       26   554496     227         2             0 19-DEC-2002 01:50:35      152   554381     225         1             0 19-DEC-2002 01:40:35        1   554196     224         1             0 19-DEC-2002 01:30:35        1   554185     225         1             0 19-DEC-2002 01:20:35       11   554172     228         1             0 19-DEC-2002 01:10:35        3   554085     224         1             0 19-DEC-2002 01:00:35       13   554048     228         1             0 19-DEC-2002 00:50:35       13   553953     228         1             0 19-DEC-2002 00:40:35        8   553870     225         1             0 END_TIME             UNDOBLKS TXNCOUNT QUERYLEN TXNCONCURRENCY STEALCNT -------------------- -------- -------- -------- -------------- -------- 19-DEC-2002 00:30:35       24   553817      225             1         0 19-DEC-2002 00:20:35        2   553697      228             1         0

DBA_UNDO_EXTENTS

The data in this view provides details on the number of extents for the undo segments across all the undo tablespaces. Please note that in the clustered database each instance is assigned an undo tablespace and this view reports on all undo tablespaces.

The view provides information on the currently active segments, blocks allocated to these active unexpired segments, and the tablespaces they belong to.

COL TABLESPACE_NAME FORMAT A20 COL SEGMENT_NAME FORMAT A20 SELECT       SEGMENT_NAME,       TABLESPACE_NAME,       BLOCKS,       STATUS FROM DBA_UNDO_EXTENTS / SEGMENT_NAME TABLESPACE_NAME BLOCKS     STATUS ------------ --------------- ------  ---------   _SYSSMU10$       UNDO_RAC1      8    EXPIRED   _SYSSMU10$       UNDO_RAC1    128  UNEXPIRED   _SYSSMU11$       UNDO_RAC2      7    EXPIRED   _SYSSMU11$       UNDO_RAC2      8    EXPIRED   _SYSSMU11$       UNDO_RAC2    128  UNEXPIRED   _SYSSMU12$       UNDO_RAC2      7    EXPIRED   _SYSSMU12$       UNDO_RAC2      8    EXPIRED   _SYSSMU13$       UNDO_RAC2      7    EXPIRED   _SYSSMU13$       UNDO_RAC2      8    EXPIRED   _SYSSMU14$       UNDO_RAC2      7    EXPIRED   _SYSSMU14$       UNDO_RAC2      8    EXPIRED SEGMENT_NAME TABLESPACE_NAME BLOCKS     STATUS ------------ --------------- ------  ---------   _SYSSMU20$       UNDO_RAC2      8    EXPIRED

V$ROLLNAME

This view acts as a cross-reference between the undo segment number and the actual undo segment.

SELECT USN,NAME FROM V$ROLLNAME; USN  NAME ---  -----------   0  SYSTEM  11  _SYSSMU11$  12  _SYSSMU12$  13  _SYSSMU13$  14  _SYSSMU14$   15  _SYSSMU15$  16  _SYSSMU16$  17  _SYSSMU17$  18  _SYSSMU18$  19  _SYSSMU19$  20  _SYSSMU20$

UNDO$

In its internal tables, Oracle maintains various status values for the undo segments, as it passes through various stages of its operation. The status column has the following values:

1. Invalid

2. Defined but not in use

3. Online

4. Offline

5. Needs recovery

6. Partially available

COL INST# FORMAT 9999 COL NAME FORMAT A15 SELECT      INST#,      US#,      NAME,      STATUS$,      XACTSQN,      UNDOSQN,      OPTIMAL FROM UNDO$ / ~INST#  US#       NAME   STATUS$ XACTSQN UNDOSQN OPTIMAL --------------------------------------------------------      0    0     SYSTEM         3       0       0      1    1  _SYSSMU1$         3   15046    3235      1    2  _SYSSMU2$         3   17047    3399      1    3  _SYSSMU3$         3   13821    2866      1    4  _SYSSMU4$         3   13653    3803      1    5  _SYSSMU5$         3   14259    2741      1    6  _SYSSMU6$         3   14222    2781      1    7  _SYSSMU7$         3   15171    3050      1    8  _SYSSMU8$         3   13846    2446 ~INST#  US#     NAME     STATUS$ XACTSQN UNDOSQN OPTIMAL -----------------------------------------------------      1   33  _SYSSMU33$        1    2665     590      1   34   SYSSMU34$        1    2725     600      1   35   SYSSMU35$        1    1879     524 ~INST#  US#     NAME     STATUS$ XACTSQN UNDOSQN OPTIMAL -----------------------------------------------------      2   36   SYSSMU36$        1    1867     474      2   77   SYSSMU77$        1     247      67      2   78   SYSSMU78$        1     253      72      2   79   SYSSMU79$        1     265      75      2   80   SYSSMU80$        1     273      72      2   81   SYSSMU81$        1     264      74      2   82   SYSSMU82$        1     240      68      2   83   SYSSMU83$        1     237      64      2   84   SYSSMU84$        1     215      59

Undo and rollback activity is also reported by the STATSPACK reports:

Rollback Segment Stats for DB: PRODDB instance: RAC1 Snaps:        Trans Table   Pct Undo Bytes RBS No        Gets Waits Written    Wraps Shrinks Extends ------ ----------- ---------------- ----- ------- -------      0        65.0  0.00      1,638     0       0       0      1     7,735.0  0.00  1,465,096     3       1       1      2     6,850.0  0.03  1,246,388     6       2       2      3     7,566.0  0.01  1,486,626    30       2       8      4     7,225.0  0.03  1,501,720    31       3       8      5     8,098.0  0.00  1,310,916     3       1       1      6     7,426.0  0.01  1,797,526     4       1       0      7     6,906.0  0.00  1,135,420    21       1       1      8     6,813.0  0.01  1,220,012     5       2       2      9     8,093.0  0.01  1,633,094     4       1       1     10     7,986.0  0.00  1,406,398     5       1       1 ------  ---------- ----- ---------- ------ ------ -------

In the output above, a high value for ''Pct Waits'' suggests more rollback segments may be required. RBS stats may not be accurate between begin and end snaps when using auto undo management, as RBS may be dynamically created and dropped as needed.

^[3]This query has been taken from the Oracle PerformanceTuning documentation Version 9i Release 2.

---