In 10G version, the ASMM(Automatic Shared Memory Management) has been introduced to relieve DBAs from sizing some parts of the SGA by themselves.
ASMM can be configured by using the SGA_TARGET initialization parameter.
when set > 0, the ASMM is enabled
when set to 0, the ASMM is disabled
V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations.
ALTER SESSION SET nls_date_format = 'DD/MM/YYYY:HH24:MI:SS';SET PAGESIZE 900SET LINESIZE 255COL COMPONENT FORMAT A25COL INITIAL_SIZE FORMAT A10COL TARGET_SIZE FORMAT A10COL FINAL_SIZE FORMAT A10COL OPER_TYPE FORMAT A10select START_TIME, component, oper_type, oper_mode,status, initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", END_TIMEfrom v$sga_resize_opsorder by start_time, component;
START_TIME COMPONENT OPER_TYPE OPER_MODE STATUS INITIAL TARGET FINAL END_TIME------------------- ------------------------- ---------- --------- ---------- ---------- ---------- ---------- -------------------16/11/2009:15:12:24 DEFAULT buffer cache SHRINK IMMEDIATE COMPLETE 5776 5760 5760 16/11/2009:15:12:2416/11/2009:15:12:24 shared pool GROW IMMEDIATE COMPLETE 4416 4432 4432 16/11/2009:15:12:2416/11/2009:19:22:34 DEFAULT buffer cache GROW DEFERRED ERROR 5760 5856 5792 16/11/2009:19:22:3516/11/2009:19:22:34 shared pool SHRINK DEFERRED ERROR 4432 4336 4400 16/11/2009:19:22:35
My result found "ERROR" (sizing operation was unable to complete) status.
Use this error checked in metalink(556928.1) and they told to increase the SGA_TARGET.
So, I checked v$sga_target_advice ...
select * from v$sga_target_advice order by sga_size;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR Estd Phys Reads---------- --------------- ------------ ------------------- ----------------2560 .25 68538927 2.6068 5,648,312,4105120 .5 34072278 1.2959 1,863,613,3707680 .75 28290588 1.076 1,204,264,18310240 1 26292359 1 1,020,822,39812800 1.25 25737600 .9789 973,149,99215360 1.5 25416834 .9667 973,149,99217920 1.75 25377404 .9652 847,180,50820480 2 25377406 .9652 767,045,950
Idea???...Investigate & solve anything reduce SGA size... (perhaps increase SGA_TARGET)... or it's just Bug!
Posts
2 comments:
IMHO, It looks like shared pool did not give the needed memory back for buffer cache to grow.
If I were you I would check the load on the system during that period to understand if there was already a demand on shared pool so it couldn't shrink. I would also check v$db_cache_advice to see if buffer cache needs a minumum.
According to the sga_target_advice it looks like one time only issue depending on the load in the system
Thank you for your suggestion.
I'll investigate the problem before increase SGA_TARGET.
I think my system still reduces SGA from some pl/sql and some tables...
With db_cache_ advice -)
COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size in MB'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.99 heading 'Estd Phys Read Fctr'
COLUMN estd_physical_reads FORMAT 999,999,999,999 heading 'Estd Phys Reads'
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' order by estd_physical_read_factor
/
Cache Size in MB Buffers Estd Phys Read Fctr Estd Phys Reads
---------------- ------------ ------------------- ----------------
11,520 1,381,680 .47 163,270,577
10,944 1,312,596 .52 182,785,228
10,368 1,243,512 .57 200,822,683
9,792 1,174,428 .63 218,812,088
9,216 1,105,344 .68 236,823,474
8,640 1,036,260 .73 254,922,569
8,064 967,176 .78 273,345,135
7,488 898,092 .83 291,942,193
6,912 829,008 .89 310,966,629
6,336 759,924 .94 330,636,839
5,792 694,678 1.00 349,926,192
5,760 690,840 1.00 351,062,453
Post a Comment