oracle 11g 调整SGA

标签：sga oracle

原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://ithelp.blog.51cto.com/4501237/1130772            

   

1. 查看oracle 使用的参数文件

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfilemmdsdb.ora

2.参看系统内存

SQL> ho cat /proc/meminfo     
MemTotal:     16307484 kB
MemFree:        392528 kB
Buffers:        164264 kB
Cached:       14992764 kB
SwapCached:          0 kB
Active:        2756496 kB
Inactive:     12695660 kB
SwapTotal:    16771852 kB
SwapFree:     16771768 kB
Dirty:              28 kB
Writeback:           0 kB
AnonPages:      295140 kB
Mapped:         200196 kB
Slab:           378052 kB
SReclaimable:   361152 kB
SUnreclaim:      16900 kB
PageTables:      23564 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
WritebackTmp:        0 kB
CommitLimit:  24925592 kB
Committed_AS:  1575240 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    312888 kB
VmallocChunk: 34359424119 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
HugePages_Surp:      0
Hugepagesize:     2048 kB
DirectMap4k:     10240 kB
DirectMap2M:  16766976 kB
#oracle 可以使用物理内存的50%~70%

3.调整SGA

SQL> alter system set sga_max_size=5120m scope=spfile;

System altered.

SQL> alter system set sga_target=5120m scope=spfile;

System altered.

4.重启实例

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size                  2213136 bytes
Variable Size             603982576 bytes
Database Buffers         4697620480 bytes
Redo Buffers               40914944 bytes
Database mounted.
Database opened.
SQL>

5.验证是否成功

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 5G
sga_target                           big integer 5G
SQL>

 

6.spfile 与 pfile相互转换
pfile --> spfile
create pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initmmdsdb.ora' from  spfile
spfile -->pfile
create spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilemmdsdb.ora' from pfile
 

本文出自 “漫漫长路” 博客，请务必保留此出处http://ithelp.blog.51cto.com/4501237/1130772