Hello
I see undersized sga in addm report. it's on and off. what size of sga i need to set for this to go away. Any help is apperciated.
thanks
Jim.
here is the deails
Linux6 vm
cpu cores : 6
MemTotal: 56658028 kB
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Total System Global Area 3.3554E+10 bytes
Fixed Size 4517232 bytes
Variable Size 5905580688 bytes
Database Buffers 2.7582E+10 bytes
Redo Buffers 62590976 bytes
sga_max_size 32000M
sga_target 30016M
SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor,
estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';SQL> SQL> SQL> 2 3 4 5 6
Estd Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
2,560 315,000 2.85 ############
5,120 630,000 2.42 ############
7,680 945,000 2.11 ############
10,240 1,260,000 1.86 ############
12,800 1,575,000 1.65 ############
15,360 1,890,000 1.48 905,386,873
17,920 2,205,000 1.33 810,958,270
20,480 2,520,000 1.20 733,667,131
23,040 2,835,000 1.09 667,013,908
25,600 3,150,000 1.01 613,595,734
Estd Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
25,792 3,173,625 1.00 610,041,053
28,160 3,465,000 .94 573,415,830
30,720 3,780,000 .89 545,584,824
33,280 4,095,000 .87 528,225,099
35,840 4,410,000 .85 516,668,461
38,400 4,725,000 .83 506,735,869
40,960 5,040,000 .81 496,454,452
43,520 5,355,000 .80 488,172,867
46,080 5,670,000 .78 478,466,273
48,640 5,985,000 .77 471,271,530
Estd Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
51,200 6,300,000 .76 465,457,631
21 rows selected.
1* select * from v$sga_target_advice order by sga_size
SQL> /
Estd Phys
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR Reads ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE CON_ID
---------- --------------- ------------ ------------------- ------------ ---------------------- --------------------- ----------
3752 .125 462620 .4612 610,084,320 28160 2240 0
7504 .25 2331958 2.3248 ############ 5120 1600 0
11256 .375 1982887 1.9768 ############ 7680 3008 0
15008 .5 1598306 1.5934 ############ 12800 1920 0
18760 .625 1458176 1.4537 963,262,133 15360 3328 0
22512 .75 1223255 1.2195 780,541,879 20480 2240 0
26264 .875 1130671 1.1272 709,650,081 23040 3584 0
30016 1 1003079 1 610,084,320 28160 2240 0
33768 1.125 963758 .9608 580,495,230 30720 3840 0
37520 1.25 924839 .922 549,685,972 35840 2816 0
Estd Phys
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR Reads ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE CON_ID
---------- --------------- ------------ ------------------- ------------ ---------------------- --------------------- ----------
41272 1.375 910395 .9076 539,131,514 38400 4160 0
45024 1.5 885518 .8828 519,425,790 43520 3072 0
48776 1.625 863149 .8605 501,428,303 48640 1920 0
52528 1.75 853821 .8512 495,205,443 51200 3328 0
56280 1.875 853420 .8508 495,205,443 51200 6400 0
60032 2 853420 .8508 495,205,443 51200 6400 0