記憶體自動管理與手動管理

sys@PROD>show parameter workarea_size_policy

NAME                                 TYPE        VALUE

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

workarea_size_policy                 string      AUTO

sys@PROD>

#該引數有兩個值：manual與auto，預設情況下，為auto值，即自動管理。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0  Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

--確定記憶體為手動管理：

PROD> show parameter target;

NAME                     TYPE    VALUE

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

archive_lag_target           integer     0

db_flashback_retention_target        integer     1440

fast_start_io_target             integer     0

fast_start_mttr_target           integer     0

memory_max_target            big integer 0

memory_target                big integer 0

parallel_servers_target          integer     512

pga_aggregate_target             big integer 160M

sga_target               big integer 640M

--修改為自動管理：

PROD> alter system set sga_target=0 scope=spfile;

System altered.

PROD> alter system set pga_aggregate_target=0 scope=spfile;

System altered.

PROD> alter system set memory_max_target=800M scope=spfile;

System altered.

PROD> alter system set memory_target=800M scope=spfile;

System altered.

--關閉重啟資料庫使其生效：

PROD> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

PROD> startup;

ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             507513936 bytes
Database Buffers          322961408 bytes
Redo Buffers                2371584 bytes
Database mounted.
Database opened.

--確定引數修改成功：

PROD> show parameter target;

NAME                     TYPE    VALUE

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

archive_lag_target           integer     0

db_flashback_retention_target        integer     1440

fast_start_io_target             integer     0

fast_start_mttr_target           integer     0

memory_max_target            big integer 800M

memory_target                big integer 800M

parallel_servers_target          integer     512

pga_aggregate_target             big integer 0

sga_target               big integer 0

注：修改的oracle記憶體不要大於實體記憶體，這點要檢查好單位是GB/MB/KB，否則會出現startup沒有任何反應。