About PGA_AGGREGATE_TARGET parameter

本文详细介绍了Oracle数据库中PGA_AGGREGATE_TARGET参数的作用及其设置方式。当该参数被设置为非零值时,Oracle会自动调整SQL操作的工作区大小,无需手动配置;而当设置为0时,工作区大小将依赖于手动设定的参数。文章还讨论了此参数与SGA_TARGET的区别。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

In order to make you understand About PGA_AGGREGATE_TARGET parameter let's have a look at parameter *_AREA_SIZE.

SQL> SHOW PARAMETER _AREA_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536
workarea_size_policy string AUTO

Here we see the parameter workarea_size_policy is set to AUTO because we have set non-zero value to pga_aggregate_target.

SQL> SHOW PARAMETER PGA_AGGREGATE_TARGET


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 525M

Now we try to set pga_aggregate_target to a zero value.

SQL> ALTER SYSTEM SET pga_aggregate_target=0;
ALTER SYSTEM SET pga_aggregate_target=0
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_target must be between 10M and 4096G-1

Whenever we try to set to as scope=spfile it is not set because 0 is not valid value.
So, I set it to zero in the pfile.

SQL> CREATE PFILE='/export/home/oracle/pfile.ora' FROM SPFILE;
File created.

SQL> !vi /export/home/oracle/pfile.ora
*.pga_aggregate_target=0


Now I have started database with this pfile.

SQL> STARTUP FORCE PFILE='/export/home/oracle/pfile.ora';

ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size 2021216 bytes
Variable Size 218106016 bytes
Database Buffers 1426063360 bytes
Redo Buffers 14753792 bytes
Database mounted.
Database opened.

Now have a look at the values. We will see that workarea_size_policy parameter is set to MANUAL.

SQL> SHOW PARAMETER _AREA_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536
workarea_size_policy string MANUAL
SQL> SHOW PARAMETER PGA_AGGREGATE_TARGET

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0

Now let me say about pga_aggregate_target.

• PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

• The default value for PGA_AGGREGATE_TARGET is non zero. Oracle sets it's value to 20% of the SGA or 10 MB, whichever is greater.

• 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. In that case we don't have to bother about settings of sort_area_size , hash_area_size etc.

• If you set PGA_AGGREGATE_TARGET to 0 then oracle 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.

• The memory allocated for PGA_AGGREGATE_TARGET has no relation with SGA_TARGET. The similarity is both is taken from total memory of the system.

• The minimum value of this parameter is 10 MB and maximum is 4096 GB - 1.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值