thanks again
Pierre, October 21, 2009 - 3:54 pm UTC
You wrote:
SQL> commit;
Statement processed.
I assume your "commit" is a kind of SQL pun -:)
October 23, 2009 - 1:02 pm UTC
indeed, it was
hmmmmm...
Raj, October 22, 2009 - 3:47 am UTC
I guess that
SQL> commit;
Statement processed
happens in really old releases of Oracle because now it gives this message
SQL>commit;
Commit complete.
MEMORY_TARGET and MEMORY_MAX_TARGET
Danny, May 03, 2010 - 11:01 pm UTC
Hi Tom
I have upgraded the database to 11gR2.
In my DW (10gR2), sga_target=10G, pga_aggregate_target=25G.
After upgrade, should i use memory_target=35GB (sum of sga_target+pga_aggregate_target) ?
a) How should i calculate the right value for memory_target ?
b) What's the benefit of enabling "MEMORY_TARGET and MEMORY_MAX_TARGET in 11.2" instead of sga_target ?
c) Is there any downside of enabling memory_max_target and memory_target in any situations ?
d) Would you recommend using memory_target and memory_max_target instead of SGA_TARGET in 11gR2 ?
e) Are these params equally suitable for both OTLP Vs DW Vs Reporting apps ?
Thanks in advance.
MEMORY_TARGET and MEMORY_MAX_TARGET
Danny, May 07, 2010 - 10:57 am UTC
Hi Tom
when you get a chance, Could you please update on my above post.
Thanks!
Deallocating SGA
Sam, August 11, 2010 - 8:21 am UTC
So will oracle try to de-allocate SGA prior to letting the untunable PGA exceed the memory_target ?
If so then do we need to worry about a badly written process (like the one you mention above) resulting in an excessively small sga ?
August 18, 2010 - 11:53 pm UTC
it would depend. If you are manually setting the sga size - no, we don't do anything.
If you are using memory_target (let the database size SGA and PGA and resize them), then yes, maybe.
And yes, it could lead to an SGA smaller than you wanted - just like using automatic SGA memory management could lead to a smaller than desired buffer cache if you have an application that hard parses literal sql like mad (the shared pool will grow - shrinking the buffer cache).
MEMORY_MAX_TARGET and sub-optimal queries
Kev, August 10, 2011 - 3:49 am UTC
I have written a couple of times about my organisation not using bind variables and the problems we are subsequently having with excessive CPU time due to an overworked shared_pool (these queries are still not optimised). My question is this:
If our developers do not optimise these queries and I implement MEMORY_MAX_SIZE and MEMORY_TARGET, do I run the risk of the database resizing the shared_pool to such a size it will negatively impact performance?
I intend to use query_rewrite_enabled=true and query_rewrite_integrity=similar to attempt to alleviate the problem as much as possible but as long as the developers don't do anything about the source of the problem I am concerned we could have problems in our migration from 10gR1 to 11gR2.
Many thanks for your help in anticipation.
August 13, 2011 - 4:17 pm UTC
If our developers do not optimise these queries and I implement MEMORY_MAX_SIZE
and MEMORY_TARGET, do I run the risk of the database resizing the shared_pool
to such a size it will negatively impact performance?>
yes, almost certainly. If you have a bind variable issue - where the developers have not used them and the shared pool is stressed, I would not recommend automatic SGA memory management.
query rewrite settings do NOTHING for bind variable issues like this.
memory_target < memory_max_target
Michael, December 01, 2014 - 4:43 pm UTC
hi tom,
is there any reason for setting memory_target < memory_max_target?
why shouldn't the database use all allocated memory?
regards, michael
December 01, 2014 - 9:10 pm UTC
the max target is what the SGA could be allowed to grow up to if you want without having to shutdown the database. memory_target it what it will use.
It would typically be set with two different values on a machine with more than one instance. You might want to divvy up the memory between the instances, but have the flexibility to shrink one down and give that memory to the other.
eg: you have 6gb of ram. You set the memory max for each instance to 5gb. Initally each instance will get 2.5gb memory targets (5gb memory allocated to sga+pga for both instances, 1gb left over for other OS stuff).
Over time, you realize one of the instances would benefit greatly from another 1gb of ram and it would not hurt the second instance, so you modify one instance to have a memory target of 1.5gb and the other 3.5gb - which is OK because 3.5 is below the max of 5gb.
If you had set memory target = memory max = 2.5gb, you'd have to restart the instance to accomplish this. since you didn't - you can do this operation online when the system is not being heavily utilized without shutting down.
memory_max_target
Michael, December 16, 2014 - 10:11 am UTC
We are running Oracle 11.2.0.4 EE on AIX 7.1
Our memory parameters are:
memory_target = 5G
memory_max_target = 6G
pre_page_sga = true
It seems to me that Oracle pre-allocates 6G of memory at startup and never releases anything back to the os.
Is this due to pre_page_sga = true?
Cheers, Michael
December 17, 2014 - 6:47 pm UTC
we'd "allocate" 6gb - as in reserve 6gb - but not touch the 1gb above the 5gb we are using. As long as we don't touch it, the OS will never actually page it in. It won't be real memory
even if you don't pre-page - you'd still see 6gb in reserve - but as long as we don't touch that 1gb above the 5gb - the OS will never actually have a need to give it to us.
not sure I'd recommend pre-paging:
https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams187.htm#REFRN10174 <quote>
... PRE_PAGE_SGA can increase the process startup duration, because every process that starts must access every page in the SGA. The cost of this strategy is fixed; however, you might simply determine that 20,000 pages must be touched every time a process starts. This approach can be useful with some applications, but not with all applications. Overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off. </quote>it should be noted that in earlier versions - the entire possible SGA was 'touched'
http://kevinclosson.net/2009/05/08/oracle-database-11g-automatic-memory-management-part-iv-dont-use-pre_page_sga-ok/ In current releases (11.2.0.4 definitely) only the memory_target pages are touched.
however, in 2014 - i would not recommend pre-page-sga for anyone, it was useful in olden days to ensure the sga didn't swap out, but pretty much not needed now and only truly useful if you establish a connection pool AND NEVER LOG OUT any of those connections. Otherwise, the price of establishing a new connection goes way way way up.
memory_target
lh, December 18, 2014 - 7:11 am UTC
Hi
It might also be worth noticing that
- if You are using Linux Huge pages, then one cannot use memory_target.
- using *_max values is problematic in some operating systems, because that much memory is allocated and reserved for Oracle even if actual value of parameter (e.g. sga_target) is less. In practice memory is wasted. Please check how Your operation system works before setting different values for max and actual parameters.
lh
December 18, 2014 - 8:17 pm UTC
... In practice memory is wasted. ..
that is false. It is virtual memory. If you don't touch it, it never gets truly allocated.
lh, January 07, 2015 - 1:17 pm UTC
http://docs.oracle.com/database/121/ADMIN/memory.htm "
Dynamic Modification of SGA_TARGET
The SGA_TARGET parameter can be dynamically increased up to the value specified for the SGA_MAX_SIZE parameter, and it can also be reduced. If you reduce the value of SGA_TARGET, the system identifies one or more automatically tuned components for which to release memory. You can reduce SGA_TARGET until one or more automatically tuned components reach their minimum size. Oracle Database determines the minimum allowable value for SGA_TARGET taking into account several factors, including values set for the automatically sized components, manually sized components that use SGA_TARGET space, and number of CPUs.
The change in the amount of physical memory consumed when SGA_TARGET is modified depends on the operating system. On some UNIX platforms that do not support dynamic shared memory, the physical memory in use by the SGA is equal to the value of the SGA_MAX_SIZE parameter. On such platforms, there is no real benefit in setting SGA_TARGET to a value smaller than SGA_MAX_SIZE. Therefore, setting SGA_MAX_SIZE on those platforms is not recommended.
On other platforms, such as Solaris and Windows, the physical memory consumed by the SGA is equal to the value of SGA_TARGET.
"
Doesn't this apply also to MEMERY_TARGET ?
Automatic Memory Management
INDRANIL DAS, July 01, 2015 - 5:29 am UTC
Hi Tom,
We are having below settings :
-------------------------------
06:17:52 oracle@n007035 [~]uname -a
Linux n007035 2.6.32-504.3.3.el6.x86_64 #1 SMP Fri Dec 12 16:05:43 EST 2014 x86_64 x86_64 x86_64 GNU/Linux
---------------
06:18:15 oracle@n007035 [~]sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 1 06:18:54 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show sga
Total System Global Area 1.6034E+10 bytes
Fixed Size 2269072 bytes
Variable Size 1.1845E+10 bytes
Database Buffers 4127195136 bytes
Redo Buffers 60035072 bytes
SQL>
-----------------
SQL> show parameter memory_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 15G
memory_target big integer 15G
shared_memory_address integer 0
----------------
SQL> !free -g
total used free shared buffers cached
Mem: 31 31 0 5 0 29
-/+ buffers/cache: 1 30
Swap: 12 0 12
----------------
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 15G
sga_target big integer 0
----------------
SQL> show parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer 25
shared_memory_address integer 0
shared_pool_reserved_size big integer 40M
shared_pool_size big integer 0
shared_server_sessions integer
shared_servers integer 10
---------------
SQL> show parameter dispatchers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=tcp)(DISPATCHERS=5)
max_dispatchers integer
####################################################
Other values are like -
processes integer 2270
----------
SQL> show parameter open_cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 3000
##########################
Now, our system is now doing very well in term of performance for both DataWare House Reporting Purpose and Huge Data load as well.
But when some of the reporting queries use "/*+ PARALLEL 4 */ hint or even /*+ Parallel 2 */ it creates CPU bottlenecks some times. Can you please advise, is there any thing that we should modify in the parameter settings or whatever ?
Thanks in Advance,
Regards
INDRANIL
Automatic Memory Management
INDRANIL DAS, July 01, 2015 - 5:32 am UTC
Hi Tom,
Some more information that I missed in my previous post ::
SQL> show parameter cpu
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 6
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 6
SQL>
Thanks Again !!
Huge pages
lh, July 03, 2015 - 5:43 am UTC
memory_target < memory_max_target
Mike, October 22, 2015 - 2:49 pm UTC
Tom,
I am still not quite clear what happens when MEMORY_TARGET<MEMORY_MAX_TARGET... is is difference available to be used by OS (or only available to Oracle)?
According to ML note "SGA and PGA Management in 11g's Automatic Memory Management (AMM) (Doc ID 1392549.1)" it says:
Example:
If MEMORY_MAX_TARGET is set to 1400M, and MEMORY_TARGET is set to 1000M, only the 1000M is available to the instance. The remaining 400M is held in reserve, but locked by Oracle (MMAN). However, because MEMORY_MAX_TARGET is explicitly set, it now becomes possible to dynamically resize MEMORY_TARGET without a database restart.
As per example above, can OS allocate from remaining 400M?
Thank you
October 24, 2015 - 8:45 pm UTC
I would say the default is "no", ie, all of the memory defined by memory_max_target is held by Oracle.
*But* I would also stress that for things like memory management, this is very much a platform specific discussion. Different platforms have different faciltiies, especially in the area of shared memory.
I cant really see a reason why you would want to have memory_target differ from memory_max_target.