Tuesday, November 17, 2009

V$SGA_RESIZE_OPS.STATUS ERROR

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 900
SET LINESIZE 255
COL COMPONENT FORMAT A25
COL INITIAL_SIZE FORMAT A10
COL TARGET_SIZE FORMAT A10
COL FINAL_SIZE FORMAT A10
COL OPER_TYPE FORMAT A10
select START_TIME, component, oper_type, oper_mode,status, initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", END_TIME
from v$sga_resize_ops
order 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:24
16/11/2009:15:12:24 shared pool GROW IMMEDIATE COMPLETE 4416 4432 4432 16/11/2009:15:12:24
16/11/2009:19:22:34 DEFAULT buffer cache GROW DEFERRED ERROR 5760 5856 5792 16/11/2009:19:22:35
16/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,410
5120 .5 34072278 1.2959 1,863,613,370
7680 .75 28290588 1.076 1,204,264,183
10240 1 26292359 1 1,020,822,398
12800 1.25 25737600 .9789 973,149,992
15360 1.5 25416834 .9667 973,149,992
17920 1.75 25377404 .9652 847,180,508
20480 2 25377406 .9652 767,045,950
Idea???...Investigate & solve anything reduce SGA size... (perhaps increase SGA_TARGET)... or it's just Bug!

2 comments:

Coskan Gundogar said...

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

Surachart said...

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