< Day Day Up > |
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:
Invalid
Defined but not in use
Online
Offline
Needs recovery
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.
No comments:
Post a Comment