Exploration of dynamic SGA and PGA features of Oracle9i

"In the latest release 10.2 Oracle changed these default values. The memory allocated to a single SQL operator (_ smm_max_size) is limited:
For P_A_T <= 500 MB the parameter _ smm_max_size = 20% of P_A_T
For P_A_T between 500 MB and 1000 MB the parameter _ smm_max_size = 100 M
For P_A_T betweeen 1001 MB and 2560 MB (2.5 GB) the parameter _ smm_max_size = 10% of P_A_T
I have seen even cases when these values were even bigger after the instance was restarted with P_A_T set at 4 GB.
The maximum value for parallel operations changed from 30% to 50% PGA_AGGREGATE_TARGET/DOP. also the DOP changed. when DOP <= 5 then _ smm_max_size is used, otherwise _ smm_px_max_size/DOP limits the maximum memory usage...
Reprinted: http://www.1to2.us/ORACLE-a117737.htm
Settings of SGA in oracle
Keywords: sga
Summary of SGA settings
This summary is not applicable to special cases. For example, if the server only exists in OS + ORACLE, consider
This is also caused by the occurrence of too many repetitive issues recently.
First, do not trust STS, SG, OCP, EXPERT, and other statements about any suggestions and memory percentage.
The basic principle is that data buffer can be as large as possible, shared_pool_size should be moderate, and log_buffer is usually as large as several hundred kb to 1 MB.
Two problems must be clarified before setting
1: Aside from OS and other overhead, How much memory can be used for ORACLE
2: oracle is 64-bit or 32-bit. Generally, 32bit SGA has a limit of 1.7 GB. (Some OS processing or specific settings on WINDOWS support up to 2 GB or even 3.7 GB, I have no experience in this field)
Below is the oracle under my windows2000:
SQL> select * from v $ version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0-Production
PL/SQL Release 8.1.7.0.0-Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0-Production
NLSRTL Version 3.4.1.0.0-Production
SQL>
Windows has 32bit restrictions, such as AIX, hp unix, and other 64-bit OS and ORACLE versions. 32bit oracle can be installed on 64-bit OS, and 64-bit oracle cannot be installed on 32-bit OS.
Whether oracle is a 32-bit ORACLE or a 64-bit oracle database, if the application does not use bind var well, the shared_pool_size cannot be set to a large value, which should be controlled at 200M--300M, if oracle erp uses a lot of stored procedure functions, packages, or large systems, you can consider increasing the shared_pool_size. However, it may be dangerous to exceed MB, reaching 1 GB may cause serious CPU burden and even system paralysis. Therefore, if shared_pool_size exceeds MB and the hit rate is not high, you should find the reason from the application rather than simply increasing the memory. shared_pool_size is too large, which mainly increases the management burden and latch overhead.
Log_buffer: 128 K ---- 1 M is usually not a big problem.
Large_pool_size: If MTS is not set, it is usually used in RMAN and OPQ, but it should be about 10 M --- 50 M. If MTS is set, the UGA is placed in large_pool_size. In this case, the setting of large_pool_size must be increased based on the maximum number of sessions and parameters such as sort_ares_size, it can be considered session * (sort_area_size + 2 M ). We would like to remind you that MTS is not required. We do not advocate MTS, especially when the number of concurrent online users is less than 500.
Java_pool_size: If java is not used, 30 m is usually enough.
Data buffer. After the preceding settings are completed, all the memory that can be provided to oracle should be given to data buffer = (db_block_size * db_block_buffers)
In 9i, it can be db_cache_size.
There are two important parameters.
Sort_area_size and hash_area_size
These two parameters belong to PGA rather than SGA in non-MTS, and are allocated separately for each session. In addition to OS + SGA on our servers, we must consider these two parameters.
(***): OS Memory + SGA + session * (sort_area_size + hash_area_size + 2 M) <The total physical RAM is better
In this case, assume that oracle is 32 bit and the server RAM is greater than 2 GB. Pay attention to your PGA situation. We recommend that you
Shared_pool_size + data buffer + large_pool_size + java_pool_size <1.6G
Then, you can refer to the following settings based on the above (***) principles.
If 512 M RAM
Recommended shared_pool_size = 50 M, data buffer = 200 M
If 1G RAM
Shared_pool_size = 100 M, data buffer = 500 M
If 2G
Shared_pool_size = 150 M, data buffer = 1.2G
The physical memory size is no longer related to parameters.
Assume that 64-bit ORACLE
4 GB memory
Shared_pool_size = 200 M, data buffer = 2.5G
8 GB memory
Shared_pool_size = 300 M, data buffer = 5G
12 GB memory
Shared_pool_size = 300M-----800M, data buffer = 8G
The above is only a reference value. Different systems may differ greatly and need to be adjusted according to the actual situation. We recommend that you use lock_sga in init while setting parameters. different platforms may have different ways to lock the SGA in the physical memory instead of being placed in the SWAP, in this way, the efficiency is good, and the memory settings should be adjusted in detail, which does not play a significant role. However, according to the statspack information and v $ system_event, v $ sysstat, v $ sesstat, view information such as v $ latch to be fine-tuned
Pga settings
Hash_area_size is the region used for sorting in memory.
Hash_value does not affect PGA. The system automatically calculates the HASH operation to locate the memory address.
Increasing hash_area_size can reduce the physical disk sorting speed.
9I can manage PGA in two ways, affecting hash_area_size at the same time
Automatic Configuration of PGA
Manually configure PGA
If auto is set, the sort area and hash area are automatically allocated. The pga_aggregate_target parameter is used.
If it is set to manual, the sort area and hash area parameters are used. You can specify manual for some particularly resource-consuming parameters, and then set a reasonable sort area and hash area.
Set the 10104 event to determine whether hash_area_size needs to be increased. If the Number of rows left to be
The iterated over value is not 0, indicating that the temporary table is read. It cannot be completed once in the memory in two phases.
Before Oracle9i, the calculation and control of PGA were complicated. From Oracle9i, Oracle provided a new method for SQL Memory Management: with this new feature, Oracle can automatically adjust the s q l Memory zone without shutting down the database, this improvement greatly simplifies DBA work and improves the performance of Oracle databases.
To achieve automatic PGA management, Oracle introduces several new initialization parameters:
1. PGA_AGGREGATE_TARGET-this parameter is used to specify the maximum PGA memory available for all sessions. This parameter can be dynamically changed, with a value range of 10 M -- (4096G-1) bytes.
2. WORKAREA_SIZE_POLICY-this parameter is used to enable automatic PGA memory management. This parameter has two options: AUTO and MANUAL. When set to AUTO, the database uses the automatic PGA management function provided by Oracle9i, when it is set to MANUAL, it is still manually managed before Oracle9i.
By default, WORKAREA_SIZE_POLICY in Oracle9i is set to AUTO.
Note that in Oracle9i, The PGA_AGGREGATE_TARGET parameter is only valid for Dedicated connections of the Dedicated Server in Dedicated Server mode, but not for Shared Server connections; PGA_AGGREGATE_TARGET takes effect for both dedicated server connections and shared server connections starting from Oracle10g.
The PGA_AGGREGATE_TARGET parameter limits both global PGA allocation and private Workspace Memory Allocation:
1. For serial operations, the PGA memory that can be used by a single SQL operation is allocated according to the following principles:
MIN (5% PGA_AGGREGATE_TARGET, 100 MB)
2. for parallel operations
30% PGA_AGGREGATE_TARGET/DOP (DOP = Degree Of Parallelism concurrency)
To understand the automatic adjustment of PGA, you also need to distinguish between the tunable memory size and the untunable memory size ). The adjustable memory is used by the SQL workspace, and the remaining memory cannot be adjusted.
After automatic PGA adjustment is enabled, Oracle still needs to follow the following principles:
Untunable memory size + tunable memory size <= PGA_AGGREGATE_TARGET
The database system can only control the memory allocation of the adjustable part. If the adjustable part is too small, Oracle will never force this equation to be enabled.
In addition, the PGA_AGGREGATE_TARGET parameter affects the SQL Execution Plan in the CBO optimizer mode. Oracle evaluates the maximum or minimum memory used in Sort, HASH-JOIN, or Bitmap operations based on the PGA_AGGREGATE_TARGET parameter to select the optimal execution plan.
Oracle provides such a recommended solution for setting the PGA_AGGREGATE_TARGET parameter.
1. For OLTP Systems
PGA_AGGREGATE_TARGET = (<Total Physical Memory> * 80%) * 20%
2. For the DSS System
PGA_AGGREGATE_TARGET = (<Total Physical Memory> * 80%) * 50%
That is to say, for a pure database server, we usually need to reserve 20% of the physical memory for the operating system, and the remaining 80% can be allocated to Oracle. The memory used by Oracle is divided into two parts: SGA and PGA. Therefore, PGA can occupy 20% of the total memory consumed by Oracle (OLTP system) to 50% (DSS System ).
This is just a recommended setting. Further, we should adjust and optimize the usage of PGA based on the specific performance indicators of the database.
Oracle provides such a recommended solution for setting the PGA_AGGREGATE_TARGET parameter.
1. For OLTP Systems
PGA_AGGREGATE_TARGET = (<Total Physical Memory> * 80%) * 20%
2. For the DSS System
PGA_AGGREGATE_TARGET = (<Total Physical Memory> * 80%) * 50%
That is to say, for a pure database server, we usually need to reserve 20% of the physical memory for the operating system, and the remaining 80% can be allocated to Oracle. The memory used by Oracle is divided into two parts: SGA and PGA. Therefore, PGA can occupy 20% of the total memory consumed by Oracle (OLTP system) to 50% (DSS System ).
The internal features of the Oracle9i database are greatly enhanced. One of the most exciting ones is that you can dynamically set all the Oracle SGA control parameters. Different from 8i, initialization parameters are put in a text file and read when the database is started, however, Oracle9i can reset all Oracle parameters through the alter database and alter system commands.
Before 9i, if you want to change the processing mode of the Oracle database, the Oracle administrator must shut down the database, reset the parameters in the INIT. ORA file, and restart the database. For Oracle databases that use the OLTP mode during the day and switch to the Data Warehouse mode at night, this resetting is often done.
To stop and restart the Oracle database to modify the parameters, Oracle9i has been significantly enhanced in this regard, making it easier to achieve the goal of continuous Database Availability.
This ability to dynamically increase and reduce different regions in Oracle SGA provides some exciting new features for Oracle Database administrators. Database activities in each region of SGA can be monitored independently, and resources can be allocated and retrieved in the Oracle database according to the mode used.
Let's first look at the differences between the Oracle9i database and the Oracle8i Database. One of the most important enhancements of Oracle9i is that there is no need for an independent PGA space for all dedicated connections connected to the Oracle database. In Oracle8i, for dedicated Oracle connections, we need to allocate an independent region in the memory, called Program Global Area or PGA. The PGA space contains SORT_AREA_SIZE and an additional RAM control structure to maintain the status of the connection task. In Oracle9i, the PGA space has been replaced by a new memory space in Oracle SGA. It is set through the PGA_AGGREGATE_TARGET parameter.
Since all memory usage is allocated in Oracle SGA, the Oracle database administrator can increase the memory allocated to the Oracle server by 80% until the memory of the Orace server is fully allocated. Oracle recommends that the remaining 20% of the server memory be retained to the operating system.
When you connect to the Oracle9i database, the memory required for sorting will be allocated in the PGA_AGGREGATE_TARGET area of Oracle9i. This allows Oracle9i to run faster than Oracle8i because the memory is allocated only during the required period and can be immediately released to other connected Oracle tasks after completion.
Dynamically modify the SGA Region
Since the Oracle administrator can now add and remove all SGA regions, we can quickly check what the SGA region is like, in this way, we can know how Oracle DBAs monitor the use of these regions and allocate more effective memory for Oracle databases. The SGA region can be divided into the following parts.
Data buffers-Oracle9i has up to seven independent Data buffers to store Data blocks sent from the disk. These include the traditional KEEP pool, RECYCLE pool and DEFAULT pool, and the block size supported for each Oracle Database (2 K, 4 K, 8 K, 16 K, and 32 K) independent Data Buffer Pool
We can monitor the hit rate of these seven data buffer areas. If the hit rate of the buffer remains above 90%, we can reduce the memory allocated to the data buffer, and allocate them to other Oracle instances where extra memory is needed.
When the data buffer hit rate (DBHR) drops, we can separate the memory from a data buffer and allocate it to other data buffers.
Shared pool-the Shared pool of Oracle9i has an important role in analyzing and executing Oracle SQL statements. The low library cache hit rate indicates that the memory allocated to the library cache is insufficient. When the shared pool needs to perform a large amount of analysis and execution on SQL statements, the database administrator of Oracle9i can use alter system to add additional memory to the shared pool.
PGA region-the memory allocated to PGA_AGGREGATE_TARGET is used to maintain the connection-related information (such as the cursor status) of Oracle connections and sort the SQL result sets.
Log buffer -- whether the Oracle redo log buffer has a large amount of activity, we can see it at the log switch frequency. The Oracle administrator can monitor the activities in the redo log area and dynamically increase the memory when the Oracle database requires additional memory to serve the original buffer area.
Now let's take a closer look at how these memory areas work.
Change PGA Memory Allocation
When one of the following conditions is true, we need to dynamically modify the PGA_AGGREGATE_TARGET parameter.
. When the statistical value of "estimated PGA memory for one-pass" in V $ SYSSTAT exceeds PGA_AGGREGATE_TARGET, we need to increase the PGA_AGGREGATE_TARGET value.
. When the statistical value of "workarea executions-multipass" in V $ SYSSTAT exceeds 1%, the database will benefit from more memory.
. You may overestimate the PGA memory space. When the value of "workarea executions-optimal" in V $ SYSSTAT is always 100%, you can consider reducing the PGA_AGGREGATE_TARGET value.
We can use a simple script to check whether the shared pool requires more memory.
Measure the loss rate of Library Cache.
Set lines 80;
Set pages 999;
Column mydate heading 'yr. Mo Dy Hr. 'format a16
Column c1 heading "execs" format 9,999,999
Column c2 heading "Cache Misses | While Executing" format 9,999,999
Column c3 heading "Library Cache | Miss Ratio" format 999.99999
Break on mydate skip 2;
Select
To_char (snap_time, 'yyyy-mm-dd hh24') mydate,
Sum (new. pins-old.pins) c1,
Sum (new. reloads-old.reloads) c2,
Sum (new. reloads-old.reloads )/
Sum (new. pins-old.pins) library_cache_miss_ratio
From
Stats $ librarycache old,
Stats $ librarycache new,
Stats $ snapshot sn
Where
New. snap_id = sn. snap_id
And
Old. snap_id = new. snap_id-1
And
Old. namespace = new. namespace
Group
To_char (snap_time, 'yyyy-mm-dd hh24 ')

Cache Misses
Yr. Mo Dy Hr. execs While Executing LIBRARY_CACHE_MISS_RATIO
-----------------------------------------------------------------
10 10,338 00029
10 182,477 134. 00073
10 190,707 202. 00106
10 2,803 11. 00392
As shown in the preceding example, the shared pool obviously lacks memory between AM and AM every day. During this period, we can dynamically reset the shared_pool parameter to allocate additional memory from db_cache_size.
SGA threshold Summary
As shown in table 1 below, several obvious thresholds can be used to monitor the memory usage of SGA. We can write some scripts and integrate some intelligence into them, so that we can reset the SGA when the requirements change.
RAM Area Too-small Condition Too-Large Condition
Shared pool Library cache misses No misses
Data buffer cache Hit ratio <90% Hit ratio> 95%
PGA aggregate high multi-pass executions 100% optimal executions
Table 1: exception condition indication in SGA
View the load in the SGA memory area
Oracle9i uses some new internal views or adds new columns to the existing views to help you view the internal memory allocation in Oracle9i. The following new V $ view can help monitor the memory usage of Oracle9i connections.
V $ PROCESS-three new columns are added to Oracle9i to monitor usage of PGA memory. The names of the new columns are pga_used_mem, pga_alloc_mem, and pga_max_mem.
V $ SYSSTAT-adds many new statistical lines, including area statistics for optimal, one-pass, and multi-pass.
V $ PGASTAT-the new view shows all background processes and dedicated connection PGA memory usage
V $ SQL _PLAN -- the new view contains information about the execution plan of all currently executed SQL statements. This is very attractive for experts who want to optimize the performance of SQL statements.
V $ WORKAREA-this new view provides detailed information about the cumulative memory statistics of Oracle9i connections.
V $ WORKAREA_ACTIVE-this new view provides internal memory usage information for all SQL statements currently being executed.
They aim to monitor memory usage in SGA through these V $ views, and then re-allocate the memory using the alter system command according to the processing requirements of the Oracle instance. Let's take a look at some of these new Oracle9i features and scripts, which can help us to view detailed memory usage.
Of course, we cannot detail all the technologies here. Let's take a look at a simple example. It uses the V $ SYSSTAT view to determine when to reset the PGA_AGGREGATE_TARGET parameter.
The following query shows the total number and percentage of work areas executed after the database instance is started.
Work_area. SQL
Select
Name profile,
Cnt,
Decode (total, 0, 0, round (cnt * 100/total) percentage
From
(
Select
Name,
Value cnt,
(Sum (value) over () total
From
V $ sysstat
Where
Name like 'workarea exec %'
);
The output of this query may be as follows:
PROFILE CNT PERCENTAGE
-------------------------------------------------------
Workarea executions-optimal 5395 95
Workarea executions-onepass 284 5
Workarea executions-multipass 0 0
The output of this query is used to tell DBA when to dynamically adjust the PGA_AGGREGATE_TARGET parameter. Generally, if the multi-pass execution is greater than 0, you need to increase the PGA_AGGREGATE_TARGET value and reduce the value when optimal executions is 100%.
We can also use the V $ PGASTAT view to determine the memory usage of our Oracle instances. The V $ PGASTAT view provides instance-level summary statistics for PGA usage and automatic memory management. The following script provides statistics on the overall memory usage of all Oracle9i connections.
The following is a simple script used to detect PGA memory usage in Oracle9i.
Check_pga. SQL
Column name format a30
Column value formats 999,999,999
Select
Name,
Value
From
V $ pgastat

The output of this query might look like the following:
NAME VALUE
----------------------------------------------------------------
Aggregate PGA autotarget 736,052,224
Global memory bound 21,200
Total expected memory 141,144
Total PGA inuse 22,234,736
Total PGA allocated 55,327,872
Max imum PGA allocated 23,970,624
Total PGA used for auto work as 262,144
Maximum PGA used for auto work as 7,333,032
Total PGA used for manual workareas 0
Maximum PGA used for manual workareas 0
Estimated PGA memory for optimal 141,395
Maximum PGA memory for optimal 500,123,520
Estimated PGA memory for one-pass 534,144
Maximum PGA memory for one-pass
In the above v $ pgastat display, we can see the following statistics.
Aggregate PGA auto target-this column provides all the memory that can be used for Oracle9i connections. We have mentioned that this value is set by PGA_AGGREGATE_TARGET.
Global memory bound-this statistic indicates the maximum value of the work area. Oracle recommends that you increase the PGA_AGGREGATE_TARGET value when the statistical value falls to 1 MB.
Total PGA allocated-This statistics shows the high water level used by all PGA memory in the database. When increasing the number, you should see that this value is close to the value of PGA_AGGREGATE_TARGET.
Total PGA used for auto workareas-this statistical monitor memory usage or all connections running in automatic memory mode. Remember that not all internal processes use the automatic memory feature. For example, Java and PL/SQL will allocate memory, but this part will not be counted into this value. Therefore, we can use the overall PGA value to subtract this value to get the memory used for connection and Java and PL/SQL.
Estimated PGA memory for optimal/one-pass-this statistic estimates the memory required to execute all the connection tasks in optimal mode. Remember that if Oracle9i encounters insufficient memory, it will call the multi-pass operation. This statistics is very important for monitoring the memory usage in Oracle9i. Most Oracle DBAs will increase PGA_AGGREGATE_TARGET to this value.
Now that we have understood this concept, let's take a look at how to automatically reconfigure SGA.
Overview
In a UNIX environment, it is very easy to modify the memory configuration by using a scheduled task when processing a change in demand. For example, many Oracle databases operate in OLTP mode during normal working hours and Run batch reports with high memory requirements at night.
We know that DB_CACHE_SIZE should be set to a large value in an OLTP database, and additional memory should be allocated to PGA_AGGREGATE_TARGET for Batch Tasks with large memory requirements.
The following UNIX scripts can be used to reset the SGA values of OLTP and DSS without stopping the instance. In this example, we assume there is an isolated Oracle Server with 8 GB memory. We also assume that 20% of the memory is reserved for UNIX, while the remaining 6 GB memory is used for Oracle and Oracle connections. These scripts are used in HP/UX or Solaris and $ ORACLE_SID is accepted as a parameter.
The DSS_CONFIG.KSH script will run at P.M every night to reset Oracle to run a large number of memory-intensive tasks at night.
Dss_config.ksh
#! /Bin/ksh
# First, we must set the environment ....
ORACLE_SID = $1
Export ORACLE_SID
ORACLE_HOME = 'cat/etc/oratab | grep ^ $ ORACLE_SID: | cut-f2-d ':''
# ORACLE_HOME = 'cat/var/opt/oracle/oratab | grep ^ $ ORACLE_SID: | cut-f2-d ':''
Export ORACLE_HOME
PATH = $ ORACLE_HOME/bin: $ PATH
Export PATH
$ ORACLE_HOME/bin/sqlplus-s/nologin <
Connect system/manager as sysdba;
Alter system set db_cache_size = 1500 m;
Alter system set shared_pool_size = 500 m;
Alter system set pga_aggregate_target = 400 m;
Exit
!
Now we know a common way to modify Oracle configurations. We can easily see that we can develop a technology to continuously monitor Oracle's processing requirements, use alter system to modify the existing database.
Conclusion
Although the memory management in Oracle9i still requires a lot of manual operations, most Oracle administrators can use tools to continuously monitor the memory usage in Oracle SGA, in addition, the memory can be automatically re-allocated based on the current usage of the Oracle instance. In this way, Oracle administrators can flexibly reset their systems based on system changes.