V$SGA_RESIZE_OPS

Monday, August 11, 2008

V$SGA_RESIZE_OPS

V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations. This does not include in-progress operations. All sizes are expressed in bytes.

Column	Datatype	Description
`COMPONENT`	`VARCHAR2(64)`	Component name
`OPER_TYPE`	`VARCHAR2(13)`	Operation type: · `STATIC` · `INITIALIZING` · `DISABLED` · `GROW` · `SHRINK` · `SHRINK_CANCEL`
`OPER_MODE`	`VARCHAR2(9)`	Operation mode: · `MANUAL` · `DEFERRED` · `IMMEDIATE`
`PARAMETER`	`VARCHAR2(80)`	Name of the parameter for the resize operation
`INITIAL_SIZE`	`NUMBER`	Parameter value at the start of the operation
`TARGET_SIZE`	`NUMBER`	Requested value of the parameter after the resize
`FINAL_SIZE`	`NUMBER`	Real value of the parameter after the resize
`STATUS`	`VARCHAR2(9)`	Completion status of the operation: · `INACTIVE` · `PENDING` · `COMPLETE` · `CANCELLED` · `ERROR`
`START_TIME`	`DATE`	Start time of the operation
`END_TIME`	`DATE`	End time of the operation

Note:

1. V$SGA_RESIZE_OPS.STATUS column can reveal some SGA issues. The following is an example (Metalink note 556928.1):

Symptom: V$SGA_RESIZE_OPS.STATUS displays "ERROR" for the last few rows. Then, there are no more records in this view, implying that auto-tuning has ceased. The status of ERROR means that the sizing operation was unable to complete.

Cause: Memory reallocation was occurring on a minute-by-minute basis. This is because the SGA was too small, and Oracle had to keep trying to make room for various pools. As a result, pool sizes were being adjusted too frequently for MMAN to keep up; it gave up and auto-tuning stopped.

Solution: The solution would be to increase the SGA_TARGET (and, if necessary, SGA_MAX_SIZE).

2. V$SGA_RESIZE_OPS.OPER_TYPE can be used to check SGA resize frequency.

select component, oper_type, final_size final,

to_char(start_time,'dd-mon hh24:mi:ss') started

from v$sga_resize_ops

order by component, started desc;

select component, oper_type, count(1)

from v$sga_resize_ops

group by component, oper_type

order by component;

3. The following queries may be helpful when monitoring manually and autotuned components:

   SELECT component, parameter, initial_size, final_size, status,
           to_char(end_time ,'mm/dd/yyyy hh24:mi:ss') changed
     FROM v$sga_resize_ops
ORDER BY component;

SELECT component, min(final_size) low,

           (min(final_size/1024/1024)) lowMB,
          max(final_size) high, (max(final_size/1024/1024)) highMB
     FROM v$sga_resize_ops
GROUP BY component
ORDER BY component;

4. The following views provide information about the SGA components and their dynamic resizing.

View	Description
V$SGA	Displays summary information about the system global area (SGA).
V$SGAINFO	Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.
V$SGASTAT	Displays detailed information about the SGA.
V$SGA_DYNAMIC_COMPONENTS	Displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup.
V$SGA_DYNAMIC_FREE_MEMORY	Displays information about the amount of SGA memory available for future dynamic SGA resize operations.
V$SGA_RESIZE_OPS	Displays information about the last 400 completed SGA resize operations.
V$SGA_CURRENT_RESIZE_OPS	Displays information about SGA resize operations that are currently in progress. A resize operation is an enlargement or reduction of a dynamic SGA component.
V$SGA_TARGET_ADVICE	Displays information that helps you tune SGA_TARGET.