Creating an Oracle Database. This section discusses the initialization parameters that affect the amount of memory allocated to the System Global Area (SGA). Except for the SGA. The size of the SGA is dynamic, and can grow or shrink by dynamically altering these parameters. Components and Granules in the SGAThe SGA comprises a number of memory components, which are pools of memory used to satisfy a particular class of memory allocation requests. Examples of memory components include the shared pool (used to allocate memory for SQL and PL/SQL execution), the java pool (used for java objects and other java execution memory), and the buffer cache (used for caching disk blocks). Question: I wish to display my ORACLE. How do I find the value of ORACLE? Answer: There are several commands, some internal and some. All SGA components allocate and deallocate space in units of granules. Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component. The memory for dynamic components in the SGA is allocated in the unit of granules. Granule size is determined by total SGA size. Generally speaking, on most platforms, if the total SGA size is equal to or less than 1 GB, then granule size is 4 MB. For SGAs larger than 1 GB, granule size is 1. MB. Some platform dependencies may arise. For example, on 3.
Windows NT, the granule size is 8 MB for SGAs larger than 1 GB. Consult your operating system specific documentation for more details. You can query the V$SGAINFO view to see the granule size that is being used by an instance. The same granule size is used for all dynamic 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. For example, if the granule size is 4 MB and you specify DB. You can dynamically alter the initialization parameters affecting the size of the buffer caches, shared pool, large pool, Java pool, and streams pool but only to the extent that the sum of these sizes and the sizes of the other components of the SGA (fixed SGA, variable SGA, and redo log buffers) does not exceed the value specified by SGA. If you do specify SGA. 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. Note. The STATISTICS. Table 2- 3 lists the SGA components for which SGA. This is discussed in detail later in this section. Note. An easier way to enable automatic shared memory management is to use Oracle Enterprise Manager (EM). When you enable automatic shared memory management and set the Total SGA Size, EM automatically generates the ALTER SYSTEM statements to set SGA. For more information, see Oracle Database Performance Tuning Guide. Enabling Automatic Shared Memory Management. To enable automatic shared memory management: If you are migrating from a manual management scheme, execute the following query on the instance running in manual mode to get a value for SGA. For more information on the SCOPE clause, see . Do this by editing the text initialization parameter file, or by issuing ALTER SYSTEM statements similar to the one in step 2. To control the minimum size of one or more automatically sized SGA components, set those component sizes to the desired value. Do this by editing the text initialization parameter file, or by issuing ALTER SYSTEM statements similar to the one in step 2. For example, suppose you currently have the following configuration of parameters on a manual mode instance with SGA. Doing so can be useful if you know that an application cannot function properly without a minimum amount of memory in specific components. You specify the minimum amount of SGA space for a component by setting a value for its corresponding initialization parameter. Here is an example configuration: SGA. The remaining 1. 24. M (2. 56 minus 1. The actual distribution of values among the SGA components might look like this: Actual shared pool size = 6. MActual buffer cache size = 1. MActual Java pool size = 6. MActual large pool size = 4. MActual Streams pool size = 0. The parameter values determine the minimum amount of SGA space allocated. The fixed views V$SGA. You can also see the current actual values of the SGA components in the Oracle Enterprise Manager memory configuration page. Manually limiting the minimum size of one or more automatically sized components reduces the total amount of memory available for dynamic adjustment. This reduction in turn limits the ability of the system to adapt to workload changes. Therefore, this practice is not recommended except in exceptional cases. The default automatic management behavior maximizes both system performance and the use of available resources. Automatic Tuning and the Shared Pool. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this value by increasing in small increments over time. However, the internal tuning algorithm typically does not attempt to shrink the shared pool, because the presence of open cursors, pinned PL/SQL packages, and other SQL execution state in the shared pool make it impossible to find granules that can be freed. Therefore, the tuning algorithm only tries to increase the shared pool in conservative increments, starting from a conservative size and stabilizing the shared pool at a size that produces the optimal performance benefit. Dynamic Modification of SGA. If you reduce the value of SGA. You can reduce SGA. Oracle Database determines the minimum allowable value for SGA. 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. On such platforms, there is no real benefit in setting SGA. Therefore, setting SGA. Any manually configured components remain unaffected. For example, suppose you have an environment with the following configuration: SGA. The exact value depends on environmental factors such as the number of CPUs on the system. However, the value of DB. Consider the following combination of parameters: SGA. In addition, the primary buffer cache (determined by the DB. Thus the amount that SGA. Dynamically modifying SGA. After startup, you can dynamically tune SGA. Therefore the rules governing resize for all component parameters are the same as in earlier releases. However, when automatic shared memory management is enabled, the manually specified sizes of automatically sized components serve as a lower bound for the size of the components. You can modify this limit dynamically by changing the values of the corresponding parameters. If the specified lower limit for the size of a given SGA component is less than its current size, there is no immediate change in the size of that component. The new setting only limits the automatic tuning algorithm to that reduced minimum size in the future. For example, consider the following configuration: SGA. For example, if you increase the value of LARGE. This resizing occurs at the expense of one or more automatically tuned components. If you decrease the value of LARGE. The new setting only limits the reduction of the large pool size to 2. M in the future. Modifying Parameters for Manually Sized Components. Parameters for manually sized components can be dynamically altered as well. However, rather than setting a minimum size, the value of the parameter specifies the precise size of the corresponding component. When you increase the size of a manually sized component, extra memory is taken away from one or more automatically sized components. When you decrease the size of a manually sized component, the memory that is released is given to the automatically sized components. For example, consider this configuration: SGA. Likewise, reducing DB. This section provides guidelines on setting the parameters that control the size of each SGA components. Setting the Buffer Cache Initialization Parameters. The buffer cache initialization parameters determine the size of the buffer cache component of the SGA. You use them to specify the sizes of caches for the various block sizes used by the database. These initialization parameters are all dynamic. If you intend to use multiple block sizes in your database, you must have the DB. Oracle Database assigns an appropriate default value to the DB. Larger cache sizes generally reduce the number of disk reads and writes. However, a large cache may take up too much memory and induce memory paging or swapping. DB. Thus, to specify a value for DB. For example. DB. The size of the cache of standard block size buffers is 1. M. Additionally, 2. K and 8. K caches will be configured with sizes of 8. M and 4. M respectively. Note. You cannot use a DB. For example, if the value of DB. The size of the cache for the standard block size is always determined from the value of DB. Oracle Database selects an appropriate default value. In releases before Oracle Database 1. Release 1, the amount of shared pool memory that was allocated was equal to the value of the SHARED. The internal SGA overhead refers to memory that is allocated by Oracle during startup, based on the values of several other initialization parameters. This memory is used to maintain state for different server components in the SGA. For example, if the SHARED. In other words, if you are not using the automatic shared memory management feature, then the amount of shared pool memory that is allocated at startup is exactly equal to the value of SHARED. In manual SGA mode, this parameter must be set so that it includes the internal SGA overhead in addition to the desired value of shared pool size. In the previous example, if the SHARED. In order to maintain an effective value of 6. MB for shared pool memory after startup, you must set the SHARED. In Oracle Database 1. SGA overhead, also known as startup overhead in the shared pool, can be queried from the V$SGAINFO view. Also, in manual SGA mode, if the user- specified value of SHARED.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
August 2017
Categories |