V$SGA_DYNAMIC_COMPONENTS

Oracle 11gR1
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. All sizes are expressed in bytes.

Column	Datatype	Description
COMPONENT	VARCHAR2(64)	Component name
CURRENT_SIZE	NUMBER	Current size of the component
MIN_SIZE	NUMBER	Minimum size of the component since instance startup
MAX_SIZE	NUMBER	Maximum size of the component since instance startup
USER_SPECIFIED_SIZE	NUMBER	Value of the user parameter for the component
OPER_COUNT	NUMBER	Number of operations since instance startup
LAST_OPER_TYPE	VARCHAR2(13)	Last completed operation for the component: · STATIC · INITIALIZING · DISABLED · GROW · SHRINK · SHRINK_CANCEL
LAST_OPER_MODE	VARCHAR2(9)	Mode of the last completed operation: · MANUAL · DEFERRED · IMMEDIATE
LAST_OPER_TIME	DATE	Start time of the last completed operation
GRANULE_SIZE	NUMBER	Granularity of the grow or the shrink operation

Note:

The fixed views V$SGA_DYNAMIC_COMPONENTS and V$SGAINFO display the current actual size of each SGA component.

select component, current_size, last_oper_type, min_size, user_specified_size from v$sga_dynamic_components;

COMPONENT	CURRENT_SIZE	LAST_OPER_TYPE	MIN_SIZE	USER_SPECIFIED_SIZE
shared pool	3372220416	SHRINK	3221225472	3221225472
large pool	167772160	STATIC	167772160	167772160
java pool	318767104	STATIC	318767104	318767104
streams pool	50331648	STATIC	50331648	50331648
DEFAULT buffer cache	13254000640	GROW	13086228480	10737418240
KEEP buffer cache	0	STATIC	0	0
RECYCLE buffer cache	0	STATIC	0	0
DEFAULT 2K buffer cache	0	STATIC	0	0
DEFAULT 4K buffer cache	0	STATIC	0	0
DEFAULT 8K buffer cache	0	STATIC	0	0
DEFAULT 16K buffer cache	0	STATIC	0	0
DEFAULT 32K buffer cache	0	STATIC	0	0
ASM Buffer Cache	0	STATIC	0	10737418240

select * from v$sgainfo;

NAME	BYTES	RESIZEABLE
Fixed SGA Size	2059120	No
Redo Buffers	14712832	No
Buffer Cache Size	13086228480	Yes
Shared Pool Size	3539992576	Yes
Large Pool Size	167772160	Yes
Java Pool Size	318767104	Yes
Streams Pool Size	50331648	Yes
Granule Size	16777216	No
Maximum SGA Size	17179869184	No
Startup overhead in Shared Pool	318767104	No
Free SGA Memory Available	0	NULL

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.

Oracle dynamic performance views

More Oracle DBA tips, please visit Oracle DBA Tips