Research on dynamic SGA characteristics of oracle9i

Oracle| News
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 (see Figure 1).


Figure A ************
(The difference between oracle8i and oracle9i in memory allocation)

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 the traditional keep pool,recycle pool and default pool, as well as independent data buffer pools (2K, 4 K, 8K, 16K, and 32K) that are supported for each Oracle database (see Figure 2).


Fig. 2 Independent oracle9i Data buffering

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 detach the memory from a buffer and reassign it to another data buffer (as shown in Figure 3).


Figure 3 reallocation of memory between oracle9i data buffers

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.