V$SGA_TARGET_ADVICE

Oracle 11gR1
V$SGA_TARGET_ADVICE displays information about the SGA_TARGET initialization parameter.

Column	Datatype	Description
SGA_SIZE	NUMBER	Size of the SGA
SGA_SIZE_FACTOR	NUMBER	Ratio between the SGA_SIZE and the current size of the SGA
ESTD_DB_TIME	NUMBER	Estimated DB_TIME for this SGA_SIZE
ESTD_DB_TIME_FACTOR	NUMBER	Ratio between ESTD_DB_TIME and DB_TIME for the current size of the SGA
ESTD_PHYSICAL_READS	NUMBER	Estimated number of physical reads

Note:

The V$SGA_TARGET_ADVICE view provides information that helps you decide on a value for SGA_TARGET.

select * from v$sga_target_advice order by sga_size;

SGA_SIZE	SGA_SIZE_FACTOR	ESTD_DB_TIME	ESTD_DB_TIME_FACTOR	ESTD_PHYSICAL_READS
4096	0.25	12630175	1.6206	1338108425
8192	0.5	10309266	1.3228	951821737
12288	0.75	8869023	1.138	781692921
16384	1	7793517	1	616185497
20480	1.25	7274472	0.9334	516486684
24576	1.5	6836477	0.8772	432438982
28672	1.75	6576953	0.8439	382589575
32768	2	6576954	0.8439	382589575

If you are migrating from a manual management scheme, execute the following query on the instance running in manual mode to get a value for SGA_TARGET:

select (

(select sum(value) from v$sga) –

(select current_size from v$sga_dynamic_free_memory)

) "SGA_TARGET"

from dual;

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