increasing sga_target?
778369Jul 6 2011 — edited Jul 6 2011i am thinking to increase sga_target to another 4 gb ,suggest me from below solutions
thanks for your support
SQL> COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size
string beginning "'Cache Siz..." missing terminating quote (').
SQL> COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
SQL> COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Facto
string beginning "'Estd Phys..." missing terminating quote (').
SQL> COLUMN estd_physical_reads FORMAT 999,999,999,999 heading 'Estd Phys|
string beginning "'Estd Phys..." missing terminating quote (').
SQL> SELECT size_for_estimate,
2 buffers_for_estimate,
3 estd_physical_read_factor,
4 estd_physical_reads
5 FROM v$db_cache_advice
6 WHERE name = 'DEFAULT'
7 AND block_size = (SELECT value
8 FROM v$parameter
9 WHERE name = 'db_block_size')
10 AND advice_status = 'ON';
SIZE_FOR_ESTIMATE Buffers ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- ------------ ------------------------- -------------------
384 46,056 1.2236 414836843
768 92,112 1.1783 399488119
1152 138,168 1.1458 388465308
1536 184,224 1.1222 380464946
1920 230,280 1.1033 374060001
2304 276,336 1.0849 367812471
2688 322,392 1.0649 361039812
3072 368,448 1.0439 353932583
3456 414,504 1.0238 347094936
3840 460,560 1.0061 341120689
3984 477,831 1 339036700
SIZE_FOR_ESTIMATE Buffers ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- ------------ ------------------------- -------------------
4224 506,616 .9913 336099103
4608 552,672 .9787 331804712
4992 598,728 .9681 328233079
5376 644,784 .9595 325301695
5760 690,840 .9517 322664053
6144 736,896 .9434 319831368
6528 782,952 .933 316325168
6912 829,008 .9197 311799958
7296 875,064 .901 305468556
7680 921,120 .8761 297030242
21 rows selected.
SQL> select * from v$sga_target_advice;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
5120 1 6688134 1 339037953
1280 .25 6944958 1.0384 414846839
2560 .5 6817215 1.0193 388469687
10240 2 6571092 .9825 297031151
6400 1.25 6654693 .995 328222642
7680 1.5 6623259 .9903 316322410
8960 1.75 6571092 .9825 297031151
3840 .75 6751671 1.0095 361041516