Oracle memory structure of-PGA

Jun 30 posted in Oracle (RSS), 175 views.

I. Overview

SGA, PGA, UGA is Oracle's memory management.

SGA (System Global Area), that is, the overall situation of the district system, Oracle the most important area of memory.

PGA (Process Global Area), that is, the overall district program, a process-specific memory area.

UGA (User Global Area), and the user global areas, with a particular session is associated.

Dedicated server connection mode, UGA allocated in the PGA.

Shared server connection mode, UGA in the SGA in the distribution of Large Pool.

If the dedicated server connection mode, PGA contain UGA, is used to sort other regions, the combined hash and bitmap.

Simply put, PGA = UGA + sort + area + hash area bitmap merge area.

Second, PGA management mode.

PGA of two management models:

1) manually PGA memory management, user-specified sort and hash area of memory used by the district, each connected to use the same memory.

2) automatic PGA memory management, Oracle can be used to tell the total amount of PGA by Oraclce decision in accordance with the specific distribution of the system load.

l 9iR1 when PGA defaults to manual memory management, 9iR2 after the default for the automatic management of PGA memory.

l PGA memory can be expanded and recovery dynamic.

PGA memory management mode control by WORKAREA_SIZE_POLICY.

1) is set to MANUAL, manual memory management is enabled.

2) is set to AUTO, and 0:00 for PGA_AGGREGATE_TARGET not enable automatic memory management.

Third, manual memory management PGA

There are three most influential parameters on the PGA.

SORT_AREA_SIZE: sort of information the total memory used

SORT_AREA_RETAINED_SIZE: Sort in memory after the sort of information to preserve the total amount of memory.

HASH_AREA_SIZE: store hash list the amount of memory used.

The following description of these three parameters:

1) SORT_AREA_SIZE:

1) PGA_AGGREGATE_TARGET is a target. When connecting a few of the actual allocation of PGA memory than it is to small. A few more times to connect the actual allocation of PGA memory than it is to big, but Oracle will work hard to keep the total PGA keep PGA_AGGREGATE_TARGET value.

For example, PGA_AGGREGATE_TARGET set to 300MB. 5 users connected, each user may be allocated 10MB of PGA memory, a total of 50MB of PGA memory allocation. 300 user connections per user at the PGA may be allocation of 1.3MB of memory, a total of 390MB of PGA memory allocation. When a user connects a long time, Oracle will reduce the per-user usage of PGA memory.

2) a serial query (non-parallel query) may include a number of sort / hash operation, each sort / hash operation using up to 5% of the PGA memory.

3) a parallel query can be used up to 30% of the PGA memory, no matter how many parallel processes.

5, manual memory management and automatic PGA memory management PGA

Automatic PGA memory management PGA compared with the manual memory management there are many advantages

1, when a user is connected is low

a) manually PGA memory management can be used regardless of the number of memory are allocated in accordance with the default value. For example, the current free memory to 300MB, connections need to be sorted 10MB of memory, and we set the sort area size 5MB, lead free although there is sufficient memory is not allocated to the current connection, resulting in inefficient implementation.

b) automatic PGA memory management will be based on the current distribution of free memory. When free memory to 300MB, the current user needs 10MB memory sort, Oracle will be allocated 10MB of memory to the current user.

2, when a user is connected for a long time

a) manually PGA memory management will be fully in accordance with the default memory allocation. If the total amount of physical memory 1G, sort area as 5MB, when there are 300 users connected, Oracle will be the allocation of 1.5G of memory, which has more than our actual physical memory!

b) automatic PGA memory management will be conducted in accordance with the distribution of the current connection. If the total amount of physical memory 1G, PGA_AGGREGATE_TARGET to 300MB, when the number of users rose to 300 from 10, each user connects the memory will gradually meet the needs of 10MB reduced to 1.3MB, while the final total will be more than PGA_AGGREGATE_TARGET, PGA compared with manual memory management a lot better.

When using automatic PGA memory management? PGA when using manual memory management?

During the day when the normal operation of the system suitable for the use of automatic PGA memory management, so that Oracle automatically based on the current load management, distribution of PGA memory.

The night the number of users less time to maintain the current session can be set manually using PGA memory management, so that the maintenance of the current operation of the memory access to as much as possible to speed up the execution.

Such as: server usually runs on automatic PGA memory management mode, there is a task of the night, to a large table to update sort connected, you can operate in the interim to change the session in the PGA memory management manually, and then the distribution of large SORT_AREA_SIZE and HASH_AREA_SIZE ( 50% or even 80% of memory, it is necessary to ensure that no other users), this will greatly accelerate the speed of the system without affecting the peak period during the day and the impact on the system.

6, the operation command

System-level changes:

ALTER SYSTEM SET WORKAREA_SIZE_POLICY = (AUTO | MANAUL);

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 100000000;

ALTER SYSTEM SET SORT_AREA_SIZE = 65536 SCOPE = SPFILE;

ALTER SYSTEM SET HASH_AREA_SIZE = 65536 SCOPE = SPFILE;

Conversation-level changes

ALTER SESSION SET WORKAREA_SIZE_POLICY = (AUTO | MANAUL);

ALTER SESSION SET SORT_AREA_SIZE = 65536;

ALTER SESSION SET HASH_AREA_SIZE = 65536;

7, to apply their knowledge

1, sorting areas:

pga_aggregate_target for 100MB, a single query can be used to 5% when the order is the time required for 5MB

SQL> create table sorttable as select * from all_objects;

Table has been created.

SQL> insert into sorttable (select * from sorttable);

49735 line has been created.

SQL> insert into sorttable (select * from sorttable);

99470 line has been created.

SQL> set timing on;

SQL> set autotrace traceonly;

SQL> select * from sorttable order by object_id;

Have chosen to 198,940 lines.

Has spent time: 00: 00: 50.49

Session class to sort the district to amend the time needed for the 30mb

SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

Conversation has changed.

Has spent time: 00: 00: 00.02

SQL> ALTER SESSION SET SORT_AREA_SIZE = 30000000;

Conversation has changed.

Has spent time: 00: 00: 00.01

SQL> select * from sorttable order by object_id;

Have chosen to 198,940 lines.

Has spent time: 00: 00: 10.76

Time to see reduced from 50.49 seconds to 10.31 seconds, the speed upgrade is obvious.

2, hash District:

pga_aggregate_target for 100MB, a single query can be used to 5% is 5MB time when the table join

SQL> select / * + use_hash (tb1 tb2) * / * from sorttable tb1, sorttable tb2 where tb1.object_id = tb2.object_id;

Have chosen to 49,735 lines.

Has spent time: 00: 00: 40.50

Session class modify hash 30mb Time zone

SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

Conversation has changed.

Has spent time: 00: 00: 00.01

SQL> ALTER SESSION SET HASH_AREA_SIZE = 30000000;

Conversation has changed.

Has spent time: 00: 00: 00.01

SQL> select / * + use_hash (tb1 tb2) * / * from sorttable tb1, sorttable tb2 where tb1.object_id = tb2.object_id;

Have chosen to 49,735 lines.

Has spent time: 00: 00: 04.47

Time from 40.50 seconds to 4.47 seconds, the same effect is obvious.

Note: The above experiments are to ensure the implementation of full-table scan read the relevant table in the buffer zone to avoid data read into the cache did not cause the error.

Conclusion: 9iR2 version in the future, PGA no longer a DBA problem, Oracle will help us do a good job in the distribution of PGA. However, this does not mean that the DBA does not need a deeper understanding of the PGA, PGA master and will work in accordance with the proper use of a menace.