Integration

Hello happy10319

Usually, sga_max_size and sga_target will be the same value, but there may be times when you want to have the capability to adjust for peak loads. *By setting the Oracle sga_max_size parameter higher than sga_target, you allow dynamic adjustment of the sga_target parameter*.

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified non-zero value, then the following memory pools are automatically sized:

    Buffer cache (DB_CACHE_SIZE)

    Shared pool (SHARED_POOL_SIZE)

    Large pool (LARGE_POOL_SIZE)

    Java pool (JAVA_POOL_SIZE)

So, if these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by ASMM(Automatic Shared Memory Management). *You would set minimum values if an application component needs a minimum amount of memory to function properly*.

------> AIX Note for sga_target:

There are reported cases where sga_target cannot exceed two gigabytes. This is because of the need to edit the /etc/security/limits file and look for the Soft DATA segment, it should have the value of -1 (Unlimited).

HTH

Arsalan

happy10319 wrote:

Hi,

On 11.2.0.4 on AIX

Currently we have:

sga_target 0

And

On physical server:

free memory = 1378 MB

Do we have enough memory to set sga_target to 1Gb running:

SQL>ALTER SYSTEM SET sga_target=1G SCOPE=SPFILE;

Thank you.

Is there only 1 Oracle instance on this Server system?

how do you decide when RAM is a system bottleneck on any AIX system?

happy10319, is this a new Oracle instance or an existing, running instance.  If new if you only have 1.3G of memory left on the server the database instance is also going to need PGA so I would consider trying to use less than 1G.

- -

If existing, then what are the other memory parameter values set to?  For that matter even if new what do you plan to set these parameters to?  It is difficult and often unwise to make decisions based on limited information when more information is available.

- -

Database Administrator's Guide (11.2)

6 Managing Memory

https://docs.oracle.com/cd/E11882_01/server.112/e25494/memory.htm#ADMIN00207

- -

HTH -- Mark D Powell --

Thank you.

It is a running instance (not new). And other parameteres:

Regards.

What problem are you trying to solve?

How will you know when it has been solved?

Your settings of sga_target=0 and sga_max_size=1g mean that you have enabled automatic memory management, with memory_target=1g. You'll have to disable that before you can set your sga_target to 1g.

As for your "free memory" figure, you will have to explain how you calculated it. AIX will always stabilize with hardly any unused memory (as will any UNIX-type operating system) so it may be meaningless.

I think your question is unknown. Is there a performance issue in your database right now? Is there a performance issue in the buffer cache or library cache? What is the reason for changing the value of SGA_TARGET to non-zero? Or just want to use the features and capabilities of Automatic Shared Memory Management?

Regards

Happy10319, JohnWatson2's observation that you are using AMM means you do not want to change the sga_target parameter unless you plan to change your Oracle memory management methodology in use and unset memory_target and memory_max_target.  Oracle database parameters often impact other settings and should only be changed after reviewing the impacted subject area.  You should review the chapter on memory management in the DBA Administrators guide before proceeding.

- -

HTH -- Mark D Powell --

Thanks.

The reason:

in v$MEMORY_RESIZE_OPS

Regards.

happy10319 wrote:

Thanks.

The reason:

in v$MEMORY_RESIZE_OPS

Regards.

How does this demonstrate a need ("reason") for modifying your SGA target? It seems to me you have an observation, not a problem.

But the value of the status field is *error*. The status of ERROR means that the sizing operation was unable to complete.

This situation shows pool sizes were being adjusted too frequently for MMAN to keep up it gave up and auto tuning stopped.

I think you need to reduce the number of resizing operations.  It may not be necessary to increase or decrease the value of the parameters at all.

Regards

happy10319, I agree the posted v$memory_resize_ops indicates a potential problem, namely, mmon is abandoing memory resize operations because it is getting conflicting requests for memory.  One casue of this is that your total SGA is too small for the workload. If this is a real problem then I would expect your system suffers ORA-04031 errors fairly regularily.  If not then the second solution mentioned in the Oracle note below should probably be your first step, that is, set minimum sizes for the shared_pool, db_cache_size, steams_pool_size, ....

- -

V$SGA_RESIZE_OPS.STATUS or V$MEMORY_RESIZE_OPS.STATUS is ERROR and MMAN / auto-tuning stops. (Doc ID 556928.1)

- -

Being that you said you have 1.3G of free memory at the OS leve if your Oracle is not suffering ORA-04030 errors increasing the memory_max_target and memory_max by 300M would probably be OK.  On the other hand if you are getting ORA-04030 errors you definitely do not want to do this.

- -

If you have a workload that swings say between heavy OLTP type DML and batch processing then AMM can be a poor choice especially on an older version like 11.2, but I would try setting a minimum size on the shared_pool and other caches as my first step before I changed the memory management methodology in use.

- -

HTH -- Mark D Powell --

What do you see in the memory advisor views, v$%advice ?

Your question:

On physical server:

free memory = 1378 MB

Do we have enough memory to set sga_target to 1Gb running:

Response:  If that is all the free memory on your server then you will encounter swapping issues eventually etc so the real problem is to make sure you have adequately sized the memory on your AIX server.  If the memory can be adjusted/partitioned on the box then you should be able to allocate additional memory as needed otherwise you should consider adding memory to the server and then attempt to tune the database memory assignments based on observed usage.

Reid Ricks-Oracle wrote:
Your question: 
On physical server:
free memory = 1378 MB
Do we have enough memory to set sga_target to 1Gb running:
Response: If that is all the free memory on your server then you will encounter swapping issues eventually etc so the real problem is to make sure you have adequately sized the memory on your AIX server. If the memory can be adjusted/partitioned on the box then you should be able to allocate additional memory as needed otherwise you should consider adding memory to the server and then attempt to tune the database memory assignments based on observed usage.

Free memory as reported by AIX is a totally USELESS indicator to decide about RAM status.

AIX will maintain about 5% free RAM regardless of Oracle load or total RAM in the system.

AIX uses any RAM not required by any application as OS file system cache; which it will readily relinquish when any application needs more RAM.

Without seeing any actual statistics on SWAPPING activity, you can not know if RAM is a system bottleneck or not!

Can hugepages come into play here? Or is that a non-issue on AIX?

The reason I ask is because the OP is using AMM and as I have noted before:

1) oracle strongly recommends the use of AMM

2) oracle strongly recommends the use of hugepages

and

3) 1 and 2, above, are mutually exclusive.

Thanks.

select * from v$sga_target_advice order by sga_size;

select PGA_TARGET_FOR_ESTIMATE,PGA_TARGET_FACTOR, ESTD_TIME frov$pga_target_advice order by PGA_TARGET_FOR_ESTIMATE;

Regards.

https://blog.pythian.com/enabling-large-pages-oracle-database-11g-running-ibm-aix/

All, posted SGA and PGA statistics indicate SGA is only 684M and no benefit for going past 1G while PGA only needs a around 430M.

- -

Happy10319, if short windows to bounce the instance are not difficult to get you might try a small increase in memory and monitor the results.  If your main reason to add memory is to try to get better performance from the instance you should also look at making sure the SQL is properly tuned.

- -

Ed, Reid, large pages are unlikely to be of benefit to a database of this size; however, if the server has more than 6G of memory and is running other databases large pages could be of benefit to the server which in turn may help this instance.

- -

HTH -- Mark D Powell --

Happy10319,

SGA: As Mark mention, Increasing the value of the SGA parameter will not have much effect. I just need to say that increasing the value of this parameter based on advisor data can reduce DB TIME very little.

PGA: Adding an extra 344 MB maybe will improve performance.

Cheers

Arsalan

Thanks to all.

We are on SGA and Memory automatique management:

Yes? Do you confirme?

Then the first try would be :

ALTER SYSTEM SET memory_target=1400M SCOPE=SPFILE;

ALTER SYSTEM SET memory_max_size=1400M SCOPE=SPFILE;

??

And verify if ERRORS in V$MEMORY_RESIZE_OPS will disappear.

Regards.

happy10319, do not forget to bounce the instance after updating the spfile settings to put the settings into effect.  Also depending on the nature of the workload it may not be possible to completely eliminate the occurrence of memory resize operation cancellations (ERRORS), though that would be nice, but what you are looking for is a reduction.

- -

HTH -- Mark D Powell --