pga_aggregate_target从0改到非0时, workarea_size_policy会从MANUAL变为auto

结论：

pga_aggregate_target从非0改到非0时, workarea_size_policy不会从MANUAL变为auto.

pga_aggregate_target从0改到非0时, workarea_size_policy会从MANUAL变为auto.

1、pga_aggregate_target从0改到非0时, workarea_size_policy会从MANUAL变为auto.

参数文件:

*.memory_max_target=700M

*.memory_target=0

*.pga_aggregate_target=0

*.workarea_size_policy='MANUAL'

*.sga_max_size=500M

*.sga_target=500M

SQL> startup pfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORACLE 例程已经启动。

Total SystemGlobal Area  523108352 bytes

Fixed Size                  1375704 bytes

Variable Size             159384104 bytes

Database Buffers          356515840 bytes

Redo Buffers                5832704 bytes

数据库装载完毕。

数据库已经打开。

SQL> show parameters policy

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

parallel_degree_policy               string      MANUAL

workarea_size_policy                string      MANUAL

SQL> show parameters pga

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

pga_aggregate_target                 big integer 0

SQL> show parameters mem

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

hi_shared_memory_address             integer     0

memory_max_target                    big integer 700M

memory_target                        big integer 0

shared_memory_address                integer     0

SQL> alter system set pga_aggregate_target=100m;

系统已更改。

SQL> showparameters policy

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

parallel_degree_policy               string      MANUAL

workarea_size_policy                 string      AUTO

SQL>

2、

参数文件:

*.memory_max_target=700M

*.memory_target=0

*.pga_aggregate_target=50M

*.workarea_size_policy='MANUAL'

*.sga_max_size=500M

*.sga_target=500M

SQL> startup pfile=E:\app\hc\product\11.2.0\dbhome_1\database\INITmonkey.ORA

ORACLE 例程已经启动。

Total SystemGlobal Area  523108352 bytes

Fixed Size                  1375704 bytes

Variable Size             159384104 bytes

Database Buffers          356515840 bytes

Redo Buffers                5832704 bytes

数据库装载完毕。

数据库已经打开。

SQL> show parameters policy

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

parallel_degree_policy               string      MANUAL

workarea_size_policy                string      MANUAL

SQL> show parameters pga

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

pga_aggregate_target                 big integer 50M

SQL> show parameters mem

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

hi_shared_memory_address             integer     0

memory_max_target                    big integer 700M

memory_target                        big integer 0

shared_memory_address                integer     0

SQL> alter system set pga_aggregate_target=100m;

系统已更改。

SQL> showparameters policy

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

parallel_degree_policy               string      MANUAL

workarea_size_policy                 string      MANUAL

SQL>

说明：

pga_aggregate_target从非0改到非0时, workarea_size_policy不会从MANUAL变为auto.