| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: gather_stats_job died due to AUTO SGA error
On 4/11/07, Don Seiler <don_at_seiler.us> wrote:
> AUTO SGA: Disabling background sga tuning due to err 604
>
> Also, is the sga auto tuning still disabled? Where can one check?
After reading Jonathan Lewis' article at http://jonathanlewis.wordpress.com/2007/04/16/sga-resizing/, I ran the query against v$sga_resize_ops and sure enough there hasn't been any operations since 11-APR-07 02:30:38, when this error happened.
v$sga_resize_ops also shows an INSANE amount of activity, each time shrinking the buffer cache (db_cache_size) and growing the shared pool (shared_pool_size).
What confuses me is that the INITIAL_SIZE and FINAL_SIZE never change during this flurry. Here is a small sample of the output, there is lots more just like it:
START_TIME OPER_TYPE COMPONENT PARAMETER OPER_MODE INITIAL_SIZE FINAL_SIZE
------------------ ---------- --------------------
-------------------- --------- ------------ ----------
11-apr-07 02:30:29 SHRINK DEFAULT buffer cache db_cache_size
IMMEDIATE 838860800 838860800
GROW shared pool shared_pool_size
IMMEDIATE 587202560 587202560
11-apr-07 02:30:30 SHRINK DEFAULT buffer cache db_cache_size
IMMEDIATE 838860800 838860800
GROW shared pool shared_pool_size
IMMEDIATE 587202560 587202560
11-apr-07 02:30:33 SHRINK DEFAULT buffer cache db_cache_size
IMMEDIATE 838860800 838860800
GROW shared pool shared_pool_size
IMMEDIATE 587202560 587202560
I'm considering the suggestion of setting decent minimums for the pool
sizes, but I don't see how it would address this particular issue.
Perhaps it is just that bug, and perhaps ASMM isn't as fantastic as I
thought.
-- Don Seiler oracle: http://seilerwerks.blogspot.com ultimate: http://www.mufc.us -- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 18 2007 - 12:05:26 CDT
 |
 |