To Find ideal sga size.

 

SQL> SELECT * FROM V$SGA_TARGET_ADVICE;

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
6144 1 674124 1 265406859
1536 .25 1784541 2.6472 517729160
3072 .5 943032 1.3989 326211570
12288 2 543748 .8066 226816702
7680 1.25 652282 .9676 262195436
9216 1.5 623834 .9254 253967823
10752 1.75 554534 .8226 226816702
4608 .75 730144 1.0831 277748278

8 rows selected.

V$SGA_TARGET_ADVICEV$SGA_TARGET_ADVICE provides 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