Re: Strange error when resizing MEMORY_TARGET
From: De DBA <dedba_at_tpg.com.au>
Date: Wed, 16 Jan 2013 10:00:46 +1000
Message-ID: <50F5EDAE.1010800_at_tpg.com.au>
G'day!
In addition to my previous response, here's data from this morning. As I see it, the total (relevant) memory usage is about 12GB. I resize the memory target to 16G and I receive the "Cannot Shrink" error. Am I missing something obvious here?
|GRANULE_SIZE
Date: Wed, 16 Jan 2013 10:00:46 +1000
Message-ID: <50F5EDAE.1010800_at_tpg.com.au>
G'day!
In addition to my previous response, here's data from this morning. As I see it, the total (relevant) memory usage is about 12GB. I resize the memory target to 16G and I receive the "Cannot Shrink" error. Am I missing something obvious here?
09:43:13 [16-JAN-2013_09:43:13][SYS_at_mydb]$ /
COMPONENT | CURRENT_SIZE| MIN_SIZE| MAX_SIZE|USER_SPECIFIED_SIZE|OPER_COUNT|LAST_OPER_TYP|LAST_OPER|LAST_OPER_TIME
|GRANULE_SIZE
=========================|======================|==========|======================|===================|==========|=============|=========|====================|============ shared pool | 1,342,177,280|1140850688| 1,409,286,144| 1073741824| 64|SHRINK |DEFERRED
|15-JAN-2013_16:43:55| 67108864
large pool | 134,217,728| 67108864| 134,217,728| 134217728| 1|GROW |MANUAL
|15-JAN-2013_16:27:22| 67108864
java pool | 268,435,456| 67108864| 268,435,456| 268435456| 3|GROW
|IMMEDIATE|12-JAN-2013_11:05:05| 67108864
streams pool | 134,217,728| 67108864| 134,217,728| 134217728| 1|GROW |MANUAL
|15-JAN-2013_16:27:29| 67108864
SGA Target | 4,898,947,072|4898947072| 4,898,947,072| 0| 0|STATIC |
| | 67108864
DEFAULT buffer cache | 2,885,681,152|2818572288| 3,355,443,200| 0| 69|GROW |DEFERRED
|15-JAN-2013_16:43:55| 67108864
KEEP buffer cache | 0| 0| 0| 0| 0|STATIC
| | | 67108864
RECYCLE buffer cache | 0| 0| 0| 0| 0|STATIC
| | | 67108864
DEFAULT 2K buffer cache | 0| 0| 0| 0| 0|STATIC
| | | 67108864
DEFAULT 4K buffer cache | 0| 0| 0| 0| 0|STATIC
| | | 67108864
DEFAULT 8K buffer cache | 0| 0| 0| 0| 0|STATIC
| | | 67108864
DEFAULT 16K buffer cache | 0| 0| 0| 0| 0|STATIC
| | | 67108864
DEFAULT 32K buffer cache | 0| 0| 0| 0| 0|STATIC
| | | 67108864
Shared IO Pool | 0| 0| 0| 0| 0|STATIC
| | | 67108864
PGA Target | 2,617,245,696|2617245696| 2,617,245,696| 0| 0|STATIC |
| | 67108864
ASM Buffer Cache | 0| 0| 0| 0| 0|STATIC
| | | 67108864
|----------------------|
|----------------------| | |
| | |
*sum | 12,280,922,112| | 12,817,793,024| | | |
| |*
16 rows selected.
Elapsed: 00:00:00.01
09:43:14 [16-JAN-2013_09:43:14][SYS_at_mydb]$ alter system set
memory_target = *16G*;
alter system set memory_target = *16G*
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00846: could not *shrink* MEMORY_TARGET to specified value
Elapsed: 00:02:00.37
09:45:38 [16-JAN-2013_09:45:38][SYS_at_mydb]$ sho parameter target
NAME |TYPE |VALUE ====================================|================================|============================== archive_lag_target |integer |0 db_flashback_retention_target |integer |10080 fast_start_io_target |integer |0 fast_start_mttr_target |integer |0 memory_max_target |big integer |20G memory_target |big integer |7G parallel_servers_target |integer |256 pga_aggregate_target |big integer |0 sga_target |big integer |0
Thanks,
Tony
On 16/01/13 9:05 AM, De DBA wrote:
> Ah, I should have added that I did check V$memory_resize_ops and > v$memory_dynamic_components and the total memory usage at the time that > I could see was only around 6GB, well under the target of 7G that was in > force. > > I understand that Oracle means target, not limit, which is why I did not > expect any immediate resizing when changing the target. Perhaps I > misunderstand the meaning of "target"? In my understanding it means that > Oracle should attempt to bring memory usage up/down if possible. What I > gather from the answers though, is that Oracle attempts to force memory > down into the new target and refuses to set that target if it > momentarily fails to do that. That would indicate that Oracle treats the > "target" as a hard limit, imho. > > I noticed a similar target=limit behaviour with memory_target and > memory_max_target. We did at one point require more than 7G, and > although memory_max_target was 20G and used memory (SGA + PGA) at 7G, > Oracle raised ORA-4031 instead of growing memory beyond 7G (which is why > we want to resize to 16G in the first place). > > Cheers, > Tony > > <snip>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 16 2013 - 01:00:46 CET