sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle 单个进程PGA内存分配测试笔记

时间:2016-10-29 17:02   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库PGA自动内存管理的笔记,该笔记详细介绍单进程在PGA中运行情况与配置测试。
从9i开始PGA使用自动管理,只需要配置2个参数就搞定。

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 usesSOhttp://www.oracleplus.netRT_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系统


oracleplus.net>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

oracleplus.net>!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.

oracleplus.net>@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
oracleplus.net>@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的大小更改为之前值
oracleplus.net>alter system set "_smm_max_size"=3145728;

System altered.

oracleplus.net>@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大小调小
oracleplus.net>alter system set "_pga_max_size"=5G;

System altered.

oracleplus.net>alter system set pga_aggregate_target=1G;

System altered.

oracleplus.net>show parameter smm

NAME TYPE VALUE
———————————— ———– ——————————
_smm_max_size integer 3145728

oracleplus.net>@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 !)
本文固定链接: http://www.htz.pw/2014/07/07/%e5%8d%95%e4%b8%aa%e8%bf%9b%e7%a8%8bpga%e5%86%85%e5%ad%98%e5%88%86%e9%85%8d%e6%b5%8b%e8%af%95.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 单个进程PGA内存分配测试笔记

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1102.html

Oracle研究中心

关键词:

深入了解Oracle PGA内存配置

Oralce PGA使用自动管理的配置方法