Oracle memory usage script

This chapter explains how to allocate memory to Oracle memory caches, and how to use those caches. Proper sizing and effective use of the Oracle memory caches greatly improves database performance. However, you can manually adjust the memory pools on your system and that process is provided in this chapter. Oracle stores information in memory caches and on disk. Memory access is much faster than disk access.

For this reason, it is more efficient for data requests for frequently accessed objects to be satisfied solely by memory, rather than also requiring disk access. Oracle strongly recommends the use of automatic memory management.

Before setting any memory pool sizes, review the following:. Automatic Shared Memory Management simplifies the configuration of the SGA and is the recommended memory configuration. In response to the workload on the system, the automatic SGA management distributes the memory appropriately for the following memory pools:. If these automatically tuned memory pools had been set to nonzero values, those values are used as a minimum levels by Automatic Shared Memory Management.

You would set minimum values if an application components needs a minimum amount of memory to function properly. See "Dynamically Changing Cache Sizes". The following pools are manually sized components and are not affected by Automatic Shared Memory Management:. Oracle Database Administrator's Guide for information on managing initialization parameters.

If the system is not using Automatic Shared Memory Management, you can choose to dynamically reconfigure the sizes of the shared pool, the large pool, the buffer cache, and the process-private memory. The following sections contain details on sizing of caches:. Memory for the shared pool, large pool, java pool, and buffer cache is allocated in units of granules.

The granule size is calculated and fixed when the instance starts up. The size does not change during the lifetime of the instance. The same granule size is used for all dynamic components in the SGA. An operation can be a grow or a shrink of a dynamic SGA component. This does not include any operations currently in progress.

Configuring Memory Manually

This view summarizes information based on all completed SGA resize operations since startup. Oracle Database Reference for detailed column information for these views. With memory configuration, it is important to size the cache appropriately for the application's needs.

Conversely, tuning the application's use of the caches can greatly reduce resource requirements. The cache should be optimally designed to use the operating system and database resources most efficiently. Making changes or additions to an existing application might require resizing Oracle memory structures to meet the needs of your modified application. If your application uses Java, you should investigate whether you need to modify the default configuration for the Java pool.

Paging occurs when an operating system transfers memory-resident pages to disk solely to allow new pages to be loaded into memory. Many operating systems page to accommodate large amounts of information that do not fit into real memory.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.

If you are using another Linux i. Therefore ipcs does not display any shared segment in the memory:. 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. Indeed, it considers that the variable size might grow up to bytes, leading to a PGA reduction to 0! I hope this posting has helped you getting useful information on Automatic Memory Management for Oracle AMM and interpreting it correctly.

How can we interpret what would be the correct one please? Save my name, email, and website in this browser for the next time I comment. This site uses Akismet to reduce spam. Learn how your comment data is processed. Infrastructure at your Service. Oracle Automatic Memory Management: real memory usage! One Comment. Vimal says:. November 29, at 1 h 16 min. Reply to Vimal. Leave a Reply Cancel Reply My comment is. This website uses cookies to improve your experience.

We'll assume you're ok with this, but you can opt-out if you wish. Accept Reject Read More. Necessary Always Enabled.Oracle technology is constantly changing, so don't miss my notes on updates to Oracle PGA behavior.

oracle memory usage script

Also see these important notes on over-riding the Oracle PGA defaults. On larger servers, RAM is still very expensive and depreciates regardless of use. The savvy Oracle professional knows how to accurately predict the high-water mark of RAM demands for their database, and fully allocates the RAM, reserving only enough to accommodate spikes in user connections. On all platforms, we need to ensure that the RAM processing demands of the Oracle database do not exceed the real RAM memory of the server.

Virtual memory is an internal "trick" that relies on the fact that not every executing task is always referencing it's RAM memory region. Since all RAM regions are not constantly in-use, vendors have developed a paging algorithm that move RAM memory pages to the swap disk when it appears that they will not be needed in the immediate future.

In order to provide for the sharing of RAM, a special area of disk called a swap disk is required, and the primary purpose of the swap disk is to hold page frames from in active programs on disk. The purpose of the swap disk is to offload the least-frequently-used LRU RAM page frames so that many applications can concurrently share the same memory.

Once RAM pages from inactive programs are written to disk a page-outthe operating system can make the freed RAM memory available for another active task. Later, when the inactive program resumes execution, the RAM pages are re-loaded from the swap disk into RAM a page-in. This reloading of RAM pages is called swapping, and swapping is very time-consuming and degrades the performance of the target program.

While having the swap disk ensures concurrent RAM usage above the real amount of RAM, optimal performance requires that the swap disk is never used for active programs.

Join the world’s largest interactive community dedicated to Oracle technologies.

This is because reading RAM pages off of the swap disk is about 14, times slower than reading the memory pages from directly from RAM. As we know, disk access is measured in milliseconds, or thousandths of the second, while RAM access is access to in nanoseconds, or billionths of a second. For an Oracle server, the goal is to keep all of the RAM memory demands of the database and database connections beneath the amount of physical RAM memory.

This is because the Oracle database administrator can issue alter system command to change the RAM memory areas, and can grow and shrink the RAM memory areas on as needed basis. If you are licensed for the diagnostic and tuning packs, then you can run this query to see total and used RAM on your Oracle server:.

We can see the allocated size of the SGA in the Oracle alert log, and it is also displayed on the console when Oracle is started as shown here:. Database opened. In the example below we see that our total SGA size is megabytes:. Next, let's see how we can quickly find the amount of RAM on our server. On most Oracle servers you can issue a few commands to see the amount of RAM. Let's look at a few examples. We start with the lsdev command to show all devices that are attached to the UNIX server.

The lsdev command produces a large listing of all devices, but we can pipe the output from lsdev to the grep command to refine the display to only show the name of the device that has the RAM memory. Here we see that mem0 is the name of the memory device on this AIX server. Now we can issue the lsattr -El command passing mem0 as an argument to see the amount of memory on the server. Below we see that this server has 2 gigabytes of RAM memory attached to the mem0 device.

The "free" command can be used to quickly display the amount of RAM memory on the server. Here we see that this server has 1, megabytes of RAM. The MS-windows system display screen. For the purposes of this example, let's assume that we are on a dedicated MS-Windows Oracle server, and Oracle will be the only program running on the server. For systems that are not using Oracle's multithreaded server architecture, each connected session to the Windows server is going require an area of memory for the program global area, or PGA.

There is no easy way to determine the high-water mark of connected Oracle sessions. In our example, we have 1, megabytes of RAM memory on our MS-Windows server, and less 20 percent, we wind up with the total available allocation for Oracle of One gig.Disclaimer and Terms of Use. On certain occasions, I will be asked for an estimated resource requirements for a database that will be setup for a development environment or as a standby database. The following are my methods to calculate the memory and storage size based on the existing production database.

Get the estimated memory footprint of an existing database.

oracle memory usage script

NOTE: The resulting program name having no associated process name ex. Depending on the settings of the init parameter processes, it could be that the queries above only captures the active connections. The query below gives the max allocated memory by a user session. Get the maximum number of processes that an Oracle instance can handle. Get the total available connections by subtracting the connected sessions count from the processes parameter value.

The resulting value shall be multiplied by the resulting max allocated memory by a session done by the previous query. This would then give you the estimated amount of reserve memory needed to accommodate additional connections. Get the estimated storage sizing requirements of a database. As for windows, just right click and select the properties on each of the drive letters given by the results of the queries.

An alternative for a df command is to grep all the valid database related mount point in a single string to get a much more concise report. Information Technology. How To Check Oracle Database Memory and Storage Size On certain occasions, I will be asked for an estimated resource requirements for a database that will be setup for a development environment or as a standby database.If you prefer to exercise more direct control over the sizes of individual memory components, you can disable automatic memory management and configure the database for manual memory management.

With automatic shared memory managementyou set target and maximum sizes for the SGA. The database then tunes the total size of the SGA to your designated target, and dynamically tunes the sizes of many SGA components.

With manual shared memory managementyou set the sizes of several individual SGA components, thereby determining the overall SGA size. You then manually tune these individual SGA components on an ongoing basis.

The database then tunes the size of the instance PGA to your target, and dynamically tunes the sizes of individual PGAs. There is also manual PGA memory managementin which you set maximum work area size for each type of SQL operator such as sort or hash-join. This memory management method, although supported, is not recommended. When automatic shared memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of a workload without requiring any additional configuration.

The database automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory. Oracle Database remembers the sizes of the automatically tuned components across instance shutdowns if you are using a server parameter file SPFILE.

oracle memory usage script

As a result, the system does need to learn the characteristics of the workload again each time an instance is started. It can begin with information from the past instance and continue evaluating workload where it left off at the last shutdown.

The SGA comprises a number of memory componentswhich are pools of memory used to satisfy a particular class of memory allocation requests. All SGA components allocate and deallocate space in units of granules. The memory for dynamic components in the SGA is allocated in the unit of granules.

Granule size is determined by total SGA size. Some platform dependencies may arise. Consult your operating system specific documentation for more details. The same granule size is used for all components in the SGA. If you specify a size for a component that is not a multiple of granule size, Oracle Database rounds the specified size up to the nearest multiple.

This parameter in effect replaces the parameters that control the memory allocated for a specific set of individual components, which are now automatically and dynamically resized tuned as needed.

Alternatively, you can set one or more of the automatically sized SGA components to a nonzero value, which is then used as the minimum setting for that component during SGA tuning.

This is discussed in detail later in this section. For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically.

The reason for this is that portions of the SGA are paged written to and read from disk by the operating system. See your operating system documentation for instructions for monitoring paging activity.

You can also view paging activity from the Performance property page of the Host page of Enterprise Manager. The procedure for enabling automatic shared memory management ASMM differs depending on whether you are changing to ASMM from manual shared memory management or from automatic memory management. For more complete automatic tuning, set the values of the automatically sized SGA components listed in Table to zero. To control the minimum size of one or more automatically sized SGA components, set those component sizes to the desired value.

See the next section for details. Set the values of the other automatically sized SGA components to zero.Register and Participate in Oracle's online communities. Learn from thousand of experts, get answers to your questions and share knowledge with peers. Error: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled.

Please turn JavaScript back on and reload this page. Welcome to Oracle Communities. Please enter a title. You can not post a blank message. Please type your message and try again.

This discussion is archived. I have two databases in one unix server. This content has been marked as final. Show 29 replies. Thanks But it seems not so much help. Oracle has several hundred data structures, and these are generally replacted with unique data into several data structure areas. Eg - the database buffer block has structures that include an 'image' of a block But I need other memory used by databases. Again - what do YOU mean by 'memory used by database'? The only memory really used by the database is the SGA.

And you already have that query. Other memory, such as the 'Progam Global Area' is not part of the database It just happens to be used for database-related work. You need to tell us WHY you want this information, so we can stop guessing what you really want. But we should not forget that PGA is always allocated on the server hosting the database. Pierre is right.

Daljit Singh. Go to original post. Re: DB Memory Size.There are several write ups out there on the web that attempt to address these questions. From what I could gather from them, there is only one truly good way to tell exactly how much memory is currently in use by an oracle instance or any other system, http, mysql, etcas well as the average memory usage for oracle dedicated connection processes.

This command displays the real current memory usage of a process broken down by memory segment types. By parsing the output of pmap, we can make several useful calculations. I have tested the below script on Lunix and Solaris only. It should be noted that pmap on Solaris is very slow. On Linux, pmap is pretty much instantaneous. My interest in pmap was really to determine the real current memory usage of each process and aggregate this information in a useful manner for capacity planning as well as trend and monitoring purposes.

Based on the description of pmap in the following post: Memory Areas, I have come to the conclusion that we need the following information to size the current memory usage of an oracle instance:.

These shared memory segments need to be accounted only once in our calculations. These shared memory segments are clearly identified by pmap. I have not been able to check on HP-UX nor AIX and I have not checked every versions of Linux and Solaris so you should verify this on your platform and adjust the below script as appropriate.

As most of you may know, the oracle binary also loads several shared library objects, including system libraries. Note that this can be displayed differently on different platforms and versions. Again, adjust the script as appropriate for your platform.

Beginning Performance Tuning with Arup Nanda (In English)

This is the variable portion of the binary. As the process runs, these variable sections will use more memory for each instance of the process running. These variable sections are therefore private memory and need to be summed up. In the below script, I have decided to break down the memory usage in four components so that it can also give us the average memory consumption by dedicated connections. Similarly, any shared database connections MTS, DRCP memory are mostly part of the SGA and are therefore not counted as distinct db processes but as part of the instance shared and private memory.

The output of the script can be in one of two formats. It can be used to capture this information in a similar fashion as sar or other OS type monitoring tools. Hopefully this will be useful to you as much as it has been for me. It allowed me to demonstrate to a client that they were over allocating their memory by having too many dedicated connections and an overly large SGA for the capacity of the server it was running on.

Tanel Poder has an interesting post about using pmap and an interesting script as well to summarize the memory used by segment types.

thoughts on “Oracle memory usage script

Leave a Reply

Your email address will not be published. Required fields are marked *