Blog - comments

Thanks a lot Franck. I agree to use FRA and RMAN deletion policy to manage standby site archived log...
RIck CHEN
I still say that you don't have to delete archivelogs because they are managed by oracle. That's the...
I don't know any documentation about those EC and ECJ. And I'm sorry I don't know the consequence of...
Hi Franck thanks for clarifying this. I was already wondering about the difference between EC and EC...
Reiner
Sometimes with a group of transactions generating many archived logs, shipped and applied on standby...
Rick Chen
Blog Yann Neuhaus Oracle Automatic Memory Management: real memory usage!

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

Oracle Automatic Memory Management: real memory usage!

When using Automatic Memory Management for Oracle, it is sometimes difficult to monitor the memory usage and in particular to find the right tools to get the right information about currently allocated structures.

The instance which will be analyzed has been configured with AMM (Automatic Memory Management) on Oracle Enterprise Linux 6.1.

The current memory_target is set to 1 GB:

 

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
memory_target                        big integer 1G

 

In order to use AMM, a tmps memory filesystem has been configured in the fstab with 3 GB.

If you are using another Linux (i. e. SLES), have a look at this post to configure your /dev/shm: http://www.dbi-services.com/index.php/blog/entry/configuration-of-tmpfs-on-sles-11-for-oracle-112-and-amm

 

oracle@srvora05:// [WSDBA1] cat /etc/fstab | grep shm
tmpfs                   /dev/shm                tmpfs   defaults,size=3G        0 0

 

While the instance is started, you will see that no "classical" shared memory has been allocated, at least not through the classical memory management mechanism (Oracle switched from SysV to POSIX-style shared memory). Therefore ipcs does not display any shared segment in the memory:

 

oracle@srvora05:/ORA-DBA-Essentials/030_Administration/CreateDatabase/ [WSDBA1] ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 294912     oracle     640        4096       0   
0x00000000 327681     oracle     640        4096       0   
0x073b35e4 360450     oracle     640        4096       0   

 

To get the REAL FACTS about your current memory structure, first of all, refer to V$MEMORY_DYNAMIC_COMPONENTS:

 

select component , CURRENT_SIZE , USER_SPECIFIED_SIZE
from V$MEMORY_DYNAMIC_COMPONENTS
where CURRENT_SIZE > 0
/

COMPONENT                      CURRENT_SIZE USER_SPECIFIED_SIZE
------------------------------ ------------ -------------------
shared pool                       209715200                   0
large pool                          4194304                   0
java pool                           4194304                   0
streams pool                        4194304                   0
SGA Target                        700448768                   0
DEFAULT buffer cache              465567744                   0
PGA Target                        373293056                   0

7 rows selected.

 

We currently have the following distribution:

  • 668 MB (700448768 bytes) allocated for the SGA (sga_target)
    • 200 MB for the shared pool
    • and 444 MB for the database buffer cache
    • the rest for the other SGA pools
  • The PGA target has been sized by Oracle to 356 MB (373293056 bytes)
  • The sum of these components corresponds to 1024 MB (1 GB), set through the memory_target

On the Operating System, the consumed space can be checked checked on the /dev/shm filesystem, it confirms the size of the SGA:

 

oracle@srvora05:// [WSDBA1] du -sh /dev/shm
668M    /dev/shm

 

The PGA is private to each server process connecting to the instance, therefore it is the sum of all background processes (check with top, or ps).

We can also check the currently allocated PGA through SQL (currently 160MB, less than half of the target):

 

select name , value from v$pgastat
where name = 'total PGA allocated'
/

NAME                           VALUE
------------------------- ----------
total PGA allocated        168211456

 

Be careful , "show sga" lies! The "Total System Global Area" considers the 668 MB of shared pool plus (!) the total amount of PGA. We have "variable size = current SGA + what could be stolen to the PGA". Indeed, it considers that the variable size might grow up to 595592376 bytes, leading to a PGA reduction to 0! The information "Total System Global Area" must be interpreted with care!

 

SQL> show sga

Total System Global Area 1068937216 bytes
Fixed Size                  2235208 bytes
Variable Size             595592376 bytes
Database Buffers          465567744 bytes
Redo Buffers                5541888 bytes

 

I hope this posting has helped you getting useful information on Automatic Memory Management for Oracle (AMM) and interpreting it correctly.

Rate this blog entry:
1

Yann Neuhaus is Chief Executive Officer (CEO) and Region Manager at dbi services. He has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies, cluster computing and high availability solutions (Oracle Real Application Clusters RAC, Oracle Data Guard, and Oracle Clusterware). His expertise also includes Oracle Grid Control as well as several open source solutions (MySQL, Unix/Linux, etc.). Yann Neuhaus is Oracle Certified Expert (OCE) Cluster 11g, Oracle RAC Certified Expert, MySQL 5.0 DBA certified, and Red Hat Certified Engineer. Prior to joining dbi services, Yann Neuhaus was Principal Consultant at Trivadis in Basel, where he was also responsible for the delivery and quality management of large infrastructure projects. He also worked as IT Database / Network Administrator for IFS France. Yann Neuhaus holds an Engineer's Degree in Computer Science from the University of Technology of Belfort-Montbéliard (F) as well as a Master in Business Engineering of the EM Strasbourg Business School (F). His branch-related experience covers Financial Services / Banking, Chemicals & Pharmaceuticals, Transport & Logistics, Retail, Food, etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Thursday, 27 November 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter