从9i开始PGA使用自动管理，只需要配置2个参数就搞定。
欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw
WORKAREA_SIZE_POLICY
|
Property |
Description |
|
Parameter type |
String |
|
Syntax |
WORKAREA_SIZE_POLICY = { AUTO | MANUAL } |
|
Default value |
AUTO |
|
Modifiable |
ALTER SESSION, ALTER SYSTEM |
WORKAREA_SIZE_POLICY specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned.
Values:
- AUTO
Work areas used by memory-intensive operators are sized automatically, based on the PGA memory used by the system, the target PGA memory set inPGA_AGGREGATE_TARGET, and the requirement of each individual operator. - MANUAL
The sizing of work areas is manual and based on the values of the *_AREA_SIZE parameter corresponding to the operation (for example, a sort usesSORT_AREA_SIZE). Specifying MANUAL may result in sub-optimal performance and poor PGA memory utilization.
这里看到配置工作区的管理的策略，当参数配置成auto时，代表工作区为自动管理模式
PGA_AGGREGATE_TARGET
|
Property |
Description |
|
Parameter type |
Big integer |
|
Syntax |
PGA_AGGREGATE_TARGET = integer [K | M | G] |
|
Default value |
10 MB or 20% of the size of the SGA, whichever is greater |
|
Modifiable |
ALTER SYSTEM |
|
Range of values |
Minimum: 10 MB Maximum: 4096 GB – 1 |
|
Basic |
Yes |
PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.
Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.
Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the*_AREA_SIZE parameters.
Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the work areas to private memory. When increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk.
When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET.
从绿色的部分，我们知道把将pga_aggreate_target配置成非0的时候，并且workarea_size_policy配置成auto时，代表sql working area是自动管理，权只代表sql wrok area是自动的管理。在PGA中，不仅仅存在sql wrok area区，还有一些其它的分区。
下面是测试单个进程分配PGA的情况
1，数据库版本与OS系统
|
www.htz.pw > select * from v$version where rownum<3;
BANNER —————————————————————- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi PL/SQL Release 10.2.0.4.0 – Production
www.htz.pw > !lsb_release -a LSB Version: :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch Distributor ID: RedHatEnterpriseAS Description: Red Hat Enterprise Linux AS release 4 (Nahant Update 8) Release: 4 Codename: NahantUpdate8 |
2，将pga参数调大
|
SQL> show parameter pga
NAME TYPE VALUE ———————————— ———– —————————— _pga_max_size big integer 5G pga_aggregate_target big integer 15G SQL> show parameter smm
NAME TYPE VALUE ———————————— ———– —————————— _smm_max_size integer 3145728 |
3，创建测试表
|
CREATE TABLE "SCOTT"."HTZ1" ( "OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
CREATE TABLE "SCOTT"."HTZ2" ( "OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
|
4，测试PGA分配
4.1 运行测试SQL，观察PGA分配情况
|
select /*+ use_hash(a b) swap_join_inputs(a) */ * from scott.htz1 a,scott.htz2 b where a.owner=b.owner;
SQL> @sess_pga.sql PGA_USED PGA_ALLOC PGA_FREEABLE PGA_MAX MEM(M) MEM(M) MEM(M) MEM(M) CATEGORY ALLOC USED ——– ——— ———— ——- ————— ——– ——- 2089 2141 0 2141 SQL 2088 2088 PL/SQL 0 0 Freeable 0 0 Other 52 |
这里观察到分配了2141M的内存，使用了2089，SQL区使用了2088M，基本上达到了单个进程分配的最大值。
4.2 _smm_max_size参数调为1G
|
SQL> alter system set "_smm_max_size"=1048576;
System altered. SQL> @sess_pga.sql Enter value for sid:
PGA_USED PGA_ALLOC PGA_FREEABLE PGA_MAX MEM(M) MEM(M) MEM(M) MEM(M) CATEGORY ALLOC USED ——– ——— ———— ——- ————— ——– ——- 833 972 97 2141 SQL 833 832 PL/SQL 0 0 Freeable 97 0 Other 42 |
进程分配了972M的内存，SQL区使用了833M的内存，接近_SMM_MAX_SIZE在大小，此步说明，进程PGA内存分配受到参数_smm_max_size的控制。
4.3 将pga_max_size更改为1G
|
SQL> alter system set "_pga_max_size"=1G;
System altered.
www.htz.pw > @sess_pga.sql Enter value for sid:
PGA_USED PGA_ALLOC PGA_FREEABLE PGA_MAX MEM(M) MEM(M) MEM(M) MEM(M) CATEGORY ALLOC USED ——– ——— ———— ——- ————— ——– ——- 122 133 0 2141 SQL 122 122 PL/SQL 0 0 Freeable 0 0 Other 10 |
这里看到分配了133M的内存大小
4.4 将pga_max_size更改为2G
|
www.htz.pw > @sess_pga.sql Enter value for sid:
PGA_USED PGA_ALLOC PGA_FREEABLE PGA_MAX MEM(M) MEM(M) MEM(M) MEM(M) CATEGORY ALLOC USED ——– ——— ———— ——- ————— ——– ——- 1057 1089 0 1089 SQL 1057 1057 PL/SQL 0 0 |
内存的分配变成了1089M，说明进程内存的分配跟_pga_max_size的大小有关系
4.5 将smm的大小更改为之前值
|
www.htz.pw > alter system set "_smm_max_size"=3145728;
System altered.
www.htz.pw > @sess_pga.sql Enter value for sid:
PGA_USED PGA_ALLOC PGA_FREEABLE PGA_MAX MEM(M) MEM(M) MEM(M) MEM(M) CATEGORY ALLOC USED ——– ——— ———— ——- ————— ——– ——- 1179 1214 0 1214 SQL 1179 1179 PL/SQL 0 0 |
此时内存的分配大小是1214，跟之前内存分配差不多，说明此时内存分配大小受pga_max_size的限制，而不是_smm_max_size的限制。
4.6 将smm.pga_max_size的大小更改为原来将，将PGA从15G更改为1G
|
下面把pga_max_size增加，把pga大小调小 www.htz.pw > alter system set "_pga_max_size"=5G;
System altered.
www.htz.pw > alter system set pga_aggregate_target=1G;
System altered.
www.htz.pw > show parameter smm
NAME TYPE VALUE ———————————— ———– —————————— _smm_max_size integer 3145728
www.htz.pw > @sess_pga.sql Enter value for sid:
PGA_USED PGA_ALLOC PGA_FREEABLE PGA_MAX MEM(M) MEM(M) MEM(M) MEM(M) CATEGORY ALLOC USED ——– ——— ———— ——- ————— ——– ——- 0 1 1 1 SQL 0 0 PL/SQL 0 0 Freeable 1 0 Other 0 913 971 0 971 SQL 912 912 PL/SQL 0 0 Freeable 0 0 Other 59 |
这里内存分配是971M，在pga_max_size与_smm_max_size都相同的情况下，pga由15G更改为5G，单个进程内存的分配由2G减少到1G的大小，说明PGA单个进程的分配跟pga_aggregate_target有关系。
下面是官方对上面3个参数的说明
|
1. PGA_AGGREGATE TARGET -> should be set to five times the desired work area size 上面4.6测试步骤，测试出来的结果是PGA分配1G的大小，PGA单个进程的内存分配达到了971M，跟官方说明的5倍不一致 2. _PGA_MAX_SIZE -> should be set in minimum of twice the desired work area size. The default value is 200Mb.
3. _SMM_MAX_SIZE
-> normally this parameter is not needed but maybe under certain circumstances(特定环境) -> if set it should be equal to the desired work area size (in kb !) |
单个进程PGA内存分配测试：等您坐沙发呢！