内存SGA与PGA分别自动管理
——通过参数修改
查看目前内存分配情况
SQL> show parameter memory
NAME TYPE VALUE
------------------------------ ----------- ----------------------
hi_shared_memory_address integer 0
memory_max_target big integer 800M
memory_target big integer 800M
shared_memory_address integer 0
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ --------- ---------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 800M
sga_target big integer 0
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------ --------- ------------------------
pga_aggregate_target big integer 0
关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
备份pfile文件
[oracle@test dbs]$ ls
hc_PROD.dat init.ora initPROD.ora lkPROD spfilePROD.ora
[oracle@test dbs]$ cp initPROD.ora initPROD.ora.bak
[oracle@test dbs]$ ls
hc_PROD.dat init.ora initPROD.ora initPROD.ora.bak lkPROD spfilePROD.ora
修改pfile文件
[oracle@test dbs]$ vi initPROD.ora
db_name='PROD'
#memory_target=800M
sga_target=600M
pga_aggregate_target=200M
processes = 150
audit_file_dest='/u01/app/oracle/admin/PROD/adump'
#audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/FRA'
db_recovery_file_dest_size=1G
#diagnostic_dest=''
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = (/u01/app/oracle/oradata/PROD/control1, /u01/app/oracle/oradata/PROD/control2)
compatible ='11.2.0'
生成spfile文件
SQL> create spfile from pfile;
File created.
启动数据库
SQL> startup;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 234882088 bytes
Database Buffers 385875968 bytes
Redo Buffers 3313664 bytes
Database mounted.
Database opened.
再次查看内存分配情况
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 600M
sga_target big integer 600M
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M
SQL> show parameter memory;
NAME TYPE VALUE
------------------------------------ ---------- ------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL>
注: Lock_sga 和pre_page_sga,是两个平时用的不算太多的参数,但是这两个参数平时在优化的时候可能给你带来比较乐观的性能提升,通过修改lock_sga和pre_pga_sga参数可以保证SGA不被换出到swap,进而而已提高SGA的使用效率。
当lock_sga参数的值修改设置为true的时候,可以保证整个sga被锁定在物理内存中,这样可以防止sga被换出到swap中;当然理当需要把pre_page_sga参数也设置为true,只有这样才能保证在数据库启动之初将整个sga读取到物理内存,而不走交换内存,从而有效的提高数据库效率,当然会增加数据库的启动时间。