Study on dynamic SGA,PGA characteristics of oracle9i _oracle

"In the latest release 10.2 Oracle changed these default values. The memory allocated to a single SQL operator (_smm_max_size) are limited to:
For p_a_t <= 500MB the parameter _smm_max_size = 20% of p_a_t
For p_a_t between 500MB and 1000MB the parameter _smm_max_size = 100M
For p_a_t betweeen 1001MB and 2560MB (2.5GB) the parameter _smm_max_size = 10% of p_a_t
I have seen even cases when this values were even bigger after the instance is restarted with p_a_t set at 4GB.
The maximum value for parallel operations changed from 30% to 50%. Also the DOP changed. When DOP <=5 then _smm_max_size is used, otherwise _SMM_PX_MAX_SIZE/DOP limits the maximum memory ...
Reprint: http://www.1to2.us/ORACLE-a117737.htm
Settings for the SGA in Oracle
Key words: SGA
A summary of the SGA Setup
This summary is not for special cases, only for servers with OS + ORACLE as an example, if there are other applications please consider as appropriate
This is also due to the fact that there have been too many recurring problems lately.
First of all, do not superstitious sts,sg,ocp,expert, such as any suggestions, memory percentage of the argument
The basic principle is that data buffer can usually be as large as possible, shared_pool_size to moderate, log_buffer usually large to hundreds of K to 1M is almost
Before setting up, you need to be clear about 2 questions
1: How much memory can be used for Oracle, minus the OS and some other overhead
2:oracle is 64bit or bit,32bit usually has 1.7G limitations on the SGA (some OS processing or Windows specific settings can support to 2G above or even reach 3.7G, I do not have this experience)
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>
There are 32bit restrictions on windows, such as AIX, HP UNIX, and a clear version of 64BIT OS and Oracle, 32bit Oracle can be installed on 64bit OS, and bit Oracle cannot be installed on bit OS
Whether Oracle is a bit Oracle or bit, it is assumed that there is no good use of bind Var in the application, and can not be set shared_pool_size too large, usually should be controlled in 200m--300m, if the Oracle ERP class Using a lot of stored procedure functions, packages, or a large system, you can consider increasing shared_pool_size, but if more than 500M can be dangerous, reaching 1G can cause a heavy CPU burden, the system even paralyzed. So shared_pool_size if more than 300M also hit rate is not high, then should look for the reason from the application instead of blindly increase the memory, shared_pool_size mainly increased the management burden and latch overhead.
log_buffer:128k----1M is usually not a big problem.
Large_pool_size: If MTS is not set, it is usually used in RMAN, OPQ, but it should be about 10M---50M. If set MTS, then because UGA put to Large_pool_size's sake, this time according to the session maximum number and sort_ares_size and so on parameter setting, must increase large_pool_size the setting, may consider for the session * ( Sort_area_size + 2M). One caveat here is that MTS is not a must, and we do not advocate using MTS, especially if the number of online users is less than 500.
Java_pool_size: If you don't use Java, giving 30M is usually enough.
Data buffer, in the previous settings, where the memory can be provided to Oracle, should give data buffer = (Db_block_size * db_block_buffers)
It can be db_cache_size in 9i.
There are 2 other important parameters we need to be aware of.
Sort_area_size and Hash_area_size
These two parameters in the non-MTS is a PGA, does not belong to the SGA, is allocated for each session, in addition to our server OS + SGA, we must consider these two parts
(* * * * * * * * *: OS using memory + SGA + session* (sort_area_size + hash_area_size + 2M) < Total physical RAM for good
This boils down to the assumption that Oracle is a bit, server ram greater than 2G, pay attention to your PGA situation, it is recommended
Shared_pool_size + Data buffer +large_pool_size + java_pool_size < 1.6G
And then materialize, pay attention to meet the above (* * *) based on the principle can be referred to the following settings
If 512M RAM
Recommended shared_pool_size = 50M, data buffer = 200M
If 1G RAM
Shared_pool_size = 100M, data buffer = 500M
If the 2G
Shared_pool_size = 150M, data buffer = 1.2G
The physical memory size is no longer related to the parameters.
Assuming that the bit ORACLE
Memory 4G
Shared_pool_size = 200M, data buffer = 2.5G
Memory 8G
Shared_pool_size = 300M, data buffer = 5G
Memory 12G
Shared_pool_size = 300M-----800M, Data buffer = 8G
The above is only the reference value, the different systems may differ greatly, needs to adjust according to the concrete situation. It is recommended that when setting parameters, Init uses LOCK_SGA, different platforms may have different ways, so that the SGA locked in physical memory without being put into SWAP, so that the benefits of efficiency on the memory settings, to be carefully adjusted, the role of a small, However, you can consider fine-tuning according to Statspack information and V$system_event,v$sysstat,v$sesstat,v$latch view information.
PGA Setup
Hash_area_size the area used to sort in memory
Hash_value does not affect the PGA, he is the system automatically calculates the hash operation to locate the memory address.
By increasing the hash_area_size, you can reduce the sort of physical disks, so the speed of sorting is increased.
9I manages the PGA in two ways, while affecting the Hash_area_size
Automatic configuration of PGA
Manually configuring the PGA
If Auto is set, the sort area, the hash area automatically allocates the size, and the Pga_aggregate_target parameter is used.
If set to Manual, then the parameter sort area, the hash area parameter is used, for some special resource-consuming can specify manual, and then set a reasonable sort Area,hash area.
Set the 10104 event to determine if the hash_area_size is going to grow if the number of rows left to IS
The value of iterated over is 0, which means that the temporary table is read and two stages cannot be completed once in memory
PGA computing and control are complex before oracle9i, and Oracle offers a new approach to SQL memory management from oracle9i: Automating SQL Execution Memory management (automated SQL Execution Memory Management, with this new feature, Oracle can automatically adjust the S Q L memory area without shutting down the database, which greatly simplifies the DBA's work and improves the performance of the Oracle database.
To achieve automatic PGA Management, Oracle introduces several new initialization parameters:
1. pga_aggregate_target-This parameter is used to specify that all session totals can use the largest PGA memory. This parameter can be changed dynamically, taking a range of values from 10M--(4096g-1) bytes.
2. workarea_size_policy-This parameter is used to switch PGA memory Automatic management function, this parameter has two options: Auto and MANUAL, when set to auto, the database uses the automatic PGA management function provided by Oracle9i, when set to MANUAL, You still use the manual management method before oracle9i.
By default, the Workarea_size_policy in Oracle9i is set to Auto.
Note that in oracle9i, the Pga_aggregate_target parameter is valid only for exclusive connections in dedicated server mode (dedicated server), but for shared server (shared server) connections , starting with the oracle10g Pga_aggregate_target connection to a dedicated server and a shared server connection.
The Pga_aggregate_target parameter restricts both the global PGA allocation and the private workspace memory allocation:
1. For serial operations, the PGA memory that a single SQL operation can use is assigned according to the following principles:
MIN (5% PGA_AGGREGATE_TARGET,100MB)
2. For parallel operations
30% Pga_aggregate_target/dop (Dop=degree of Parallelism parallelism)
To understand the automatic tuning of the PGA, you also need to distinguish between adjustable memory (tunable MEMORY size) and an untunable MEMORY size. Adjustable memory is used by the SQL workspace and the remainder is not resizable.
After automatic PGA tuning is enabled, Oracle still needs to follow the following guidelines:
untunable MEMORY size + tunable MEMORY size <= pga_aggregate_target
The database system can control only the memory allocations of the adjustable portions, and if the adjustable portion is too small, Oracle will never force this equation to be enabled.
In addition, the Pga_aggregate_target parameter has an impact on the execution plan for SQL in the CBO optimizer mode. Oracle evaluates the execution plan by evaluating the maximum or minimum memory that can be used in sort,hash-join or bitmap operations based on the Pga_aggregate_target parameters, thus selecting the optimal execution plan.
For the setting of the Pga_aggregate_target parameter, Oracle provides such a recommended scenario
1. For OLTP systems
Pga_aggregate_target = (<total physical Memory > * 80%) * 20%
2. For DSS Systems
Pga_aggregate_target = (<total physical Memory > * 80%) * 50%
In other words, for a simple database server, we usually need to keep 20% of the physical memory for the operating system, and the remaining 80% can be allocated to Oracle for use. The memory used by Oracle is divided into two parts, the SGA and the PGA, so the PGA can occupy the 20% (OLTP system) to the 50% (DSS system), which consumes the total memory of Oracle.
This is only a recommended setting, and further we should adjust and optimize the use of PGA according to the specific performance metrics of the database.
For the setting of the Pga_aggregate_target parameter, Oracle provides such a recommended scenario
1. For OLTP systems
Pga_aggregate_target = (<total physical Memory > * 80%) * 20%
2. For DSS Systems
Pga_aggregate_target = (<total physical Memory > * 80%) * 50%
In other words, for a simple database server, we usually need to keep 20% of the physical memory for the operating system, and the remaining 80% can be allocated to Oracle for use. The memory used by Oracle is divided into two parts, the SGA and the PGA, so the PGA can occupy the 20% (OLTP system) to the 50% (DSS system), which consumes the total memory of Oracle.
The Oracle9i database has been greatly enhanced in its internal features, and one of the most exciting Oracle DBAs is the dynamic setting of all Oracle SGA control parameters. Unlike 8i, which originally put initialization parameters in a text file and read when the database was started, oracle9i can reset all Oracle parameters through the ALTER DATABASE and ALTER SYSTEM commands.
Before 9i, if you want to make some changes to the processing mode of the Oracle database, the Oracle administrator must close the database and reset the parameters in the Init.ora file, and then restart the database. This reset is often done for Oracle databases that operate during the day using OLTP mode and switch to the Data Warehouse mode at night.
Oracle9i has been significantly strengthened in this regard for the need to stop and restart the Oracle database to make it easier to achieve the goal of continuous database availability.
This ability to dynamically increase and shrink different areas in the Oracle SGA provides some exciting new features for Oracle database administrators. The SGA database activity in each zone can be monitored independently, and resources can be allocated and retrieved according to the pattern used in the Oracle database.
Let's first look at some of the differences between the following Oracle9i database and the Oracle8i database. One of the most important oracle9i is that there is no need to have a separate PGA space for all the dedicated connections to the Oracle database. In Oracle8i, for a dedicated Oracle connection, we need to allocate a separate region in memory, called Program Global area or PGA. The PGA space contains sort_area_size and additional RAM control structures to maintain the status of the connection task. In Oracle9i, the PGA space has been replaced by a new memory space in the Oracle SGA, which is set by the Pga_aggregate_target parameter
Because all memory usage is allocated in the Oracle SGA, Oracle database administrators can increase the amount of memory allocated to Oracle servers and allocate them to 80% of the total memory of the Orace server. Oracle recommends that the remaining 20% memory of the server be reserved for the operating system's tasks.
When a user connects to the Oracle9i database, the memory needed for the sort work is allocated in the Oracle9i pga_aggregate_target area. This allows oracle9i to run faster than oracle8i because the memory is allocated only during the required period, and can be released to other connected Oracle tasks immediately after completion.
Dynamically modifying the SGA area
Since Oracle administrators can now add and reduce all areas of the SGA, we can quickly see how the SGA area is, so that we can know how Oracle DBAs monitor the use of these areas and reallocate memory more efficiently for Oracle databases. The area of the SGA can be divided into the following sections.
Data buffering (buffers)--oracle9i has up to 7 separate data buffers to hold data blocks sent by the disk. These include traditional keep pool,recycle pool and default pool, as well as independent data buffer pools for each Oracle database supported block sizes (2K, 4 K, 8K, 16K, and 32K)
We can monitor the hit rates for these 7 buffer areas, and if the buffer hits are above 90%, we can reduce the amount of memory allocated to these data buffers and reassign them to places where additional memory is needed in other Oracle instances.
When the hit rate (DBHR) of the data buffer drops, we can isolate the memory from a data buffer and reassign it to another data buffer
Shared pool--oracle9i Shared pool has an important role to play in parsing and executing Oracle SQL statements. The low library cache hit ratio indicates that there is not enough memory allocated to the library cache, and when shared pool requires extensive analysis and execution of SQL statements, Oracle9i's database administrator can use alter system to treat shared Pool to add extra memory.
PGA Zone-the memory allocated to Pga_aggregate_target is used to allow Oracle connections to maintain connection-related information, such as the state of the cursor, and to sort the result set of SQL.
Log buffer--has a lot of activity for the Oracle redo log buffer, we can see it in the log switch frequency. Oracle administrators can monitor activity in the Redo log area and dynamically increase memory as the Oracle database requires additional memory for the original buffer zone service.
Now let's take a closer look at how the following memory areas work.
Change the PGA memory allocation
When one of the following conditions is true, we will need to dynamically modify the Pga_aggregate_target parameters.
。 When the statistics for the "estimated PGA memory for One-pass" in V$sysstat exceed 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 space of the PGA memory, and when the value of "Workarea Executions-optimal" in V$sysstat is always 100%, consider reducing the pga_aggregate_target value.
We can use a simple script to see if the shared pool requires more memory.
Measure the loss rate of the 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 BY
To_char (Snap_time, ' yyyy-mm-dd HH24 ')

Cache misses
Yr. Mo Dy Hr. Execs while executing library_cache_miss_ratio
---------------- ---------- --------------- ------------------------
2001-12-11 10 10,338 3.00029
2001-12-12 10 182,477 134.00073
2001-12-14 10 190,707 202.00106
2001-12-16 10 2,803 11.00392
From the example above, the shared pool is clearly missing memory between 9:00AM and 10:am each day. We can dynamically reset the Shared_pool parameter during this period to allocate additional memory by the db_cache_size.
Valve Value Summary for SGA
As you can see from the table below, there are several obvious thresholds to use when monitoring the use of the SGA's memory. We can write some scripts and integrate some of the intelligence into them, so that we can reset the SGA when the requirements for processing 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
Indication of exception conditions in table 1:SGA
To view the load of the SGA memory area
Oracle9i uses new internal views or adds new columns to existing views to help you see the internal memory allocations in Oracle9i. The following new v$ views can help monitor the memory usage of oracle9i connections.
V$process-Three new columns have been added to the oracle9i to monitor the use of PGA memory, with the new column named Pga_used_mem, Pga_alloc_mem, and Pga_max_mem.
V$sysstat-added a lot of new statistic lines, including area statistics for optimal, One-pass and Multi-Pass.
V$pgastat-This new view shows the PGA memory usage for all background processes and dedicated connections
v$sql_plan--This new view contains the execution plan information for all currently executing SQL. This is fascinating for performance tuning experts who need the most optimized SQL statements.
V$workarea-This new view provides detailed information on the cumulative memory statistics for oracle9i connections.
V$workarea_active-This new view provides internal memory usage information for all currently executing SQL statements.
Their purpose is to monitor memory usage in the SGA through these v$ views, and then reallocate the memory through the alter system command according to the Oracle instance processing requirements. Here's a look at some of these new oracle9i features and scripts that can help us see the detailed memory usage.
Of course we can't detail all the techniques here, let's look at a simple example that determines when to reset the Pga_aggregate_target parameter by using the V$sysstat view.
The following query can get the full number and percentage of work areas executed since the instance of the database was 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 the DBA when to dynamically adjust the pga_aggregate_target parameters. Under normal circumstances, if the execution of Multi-Pass is greater than 0, it is necessary to increase the Pga_aggregate_target value and reduce its value when the optimal executions is 100%.
We can also use the V$pgastat view to determine the memory usage of our Oracle instance. The V$pgastat view provides instance-level summary statistics for PGA usage and automatic memory management. The following script provides statistical information on the overall memory usage of all oracle9i connections.
Here is a simple script to detect the use of PGA Memory in Oracle9i.
Check_pga.sql
Column name format A30
Column value Format 999,999,999
Select
Name
Value
From
V$pgastat

The output of this query might look like the following:
NAME VALUE
------------------------------------------------------ ----------
Aggregate PGA Auto Target 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
Maximum PGA allocated 23,970,624
Total PGA used for auto Workareas 262,144
Maximum PGA used for auto Workareas 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
We can see the following statistics in the V$pgastat display above.
Aggregate PGA Auto Target-This column gives you all the memory you can use for a oracle9i connection. As we've already mentioned, this value is set by Pga_aggregate_target.
Global memory Bound-this statistic represents the maximum value of work area, and Oracle recommends that you increase the Pga_aggregate_target value when the statistic drops to 1M.
Total PGA Allocated-This statistic shows the high watermark used by all PGA memory in the database. When using the increase, you should see that the value is close to the Pga_aggregate_target value.
Total PGA used for auto Workareas-This statistic monitors the use of memory or all of the connections running in automatic memory mode. Keep in mind that not all internal processes use the automatic memory feature. For example, Java and Pl/sql will allocate memory, but this portion will not be counted in this value. So we can subtract this value by using the overall PGA value to get the memory used by the connection and Java and Pl/sql.
Estimated PGA memory for optimal/one-pass-This statistic estimates the memory required to perform all connection tasks in optimal mode. Keep in mind that if oracle9i encounters low memory, it invokes the multi-pass operation. This statistic is important for monitoring memory usage in oracle9i, and most Oracle DBAs will increase Pga_aggregate_target to this value.
Now that we've seen this concept, let's take a look at the ways to automatically reconfigure the SGA.
General statement
In a UNIX environment, it is easy to modify the memory configuration by timing tasks when processing requirements change. For example, many Oracle databases operate in OLTP mode during normal working hours, and in the evening run bulk reports with large memory requirements.
We know that db_cache_size should be set to a larger value in an OLTP database, and that additional memory needs to be allocated to pga_aggregate_target in bulk tasks that require large amounts of memory.
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 that there is an orphaned Oracle server with 8GB of memory. We also assume that 20% of the memory is reserved for UNIX use, while the remaining 6GB memory is used as Oracle and Oracle connections. These scripts are used in Hp/ux or Solaris and accept $oracle_sid as a parameter.
Dss_config. The Ksh script will run every night in 6:00 p.m to reset the bulk tasks that Oracle is running at night for large memory requirements.
Dss_config.ksh
#!/bin/ksh
# The 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=1500m;
ALTER SYSTEM set shared_pool_size=500m;
ALTER SYSTEM set pga_aggregate_target=400m;
Exit
!
Now that we know a common way to modify the Oracle configuration, it is easy to see that we can easily develop a technology to continuously monitor Oracle's processing requirements and modify it using alter system based on existing database requirements.
Conclusion
Although memory management in Oracle9i still requires a lot of manual operations, most Oracle administrators can use tools to continuously monitor memory usage in the Oracle SGA and can automatically reallocate memory based on the current usage in Oracle instance. This allows Oracle administrators to flexibly reset their systems based on changes in the system.