Re: Strange error when resizing MEMORY_TARGET
- From: De DBA <dedba@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 16 Jan 2013 10:00:46 +1000
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@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@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@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>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
