linux:oracle10g调整SGA

oracle10g调整SGA

oracle10g调整10g很简单，oracle8i需要调整的参数就很多。
10g需要调整的两个参数为：
sga_max_size
sga_target

1:查看数据库用的spfile,还是pfile;

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/10.2.0/dbs/spfilezxhs.ora

VALUE存在，说明用的spfile;

2:查看SGA
SQL> show parameter sga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 956M
sga_target big integer 956M

3：查看服务器内存；
#top
Mem: 4149108k total, 4019852k used, 129256k free, 8364k buffers

用这个命令也是一样：
#cat /proc/meminfo
MemTotal: 4149108 kB
MemFree: 129704 kB
Buffers: 8532 kB
Cached: 3711552 kB
SwapCached: 16376 kB
Active: 725816 kB
Inactive: 3189956 kB
HighTotal: 3276480 kB
HighFree: 7232 kB
LowTotal: 872628 kB
LowFree: 122472 kB
SwapTotal: 8385920 kB
SwapFree: 7962196 kB
Dirty: 8 kB
Writeback: 0 kB
Mapped: 653636 kB
Slab: 63808 kB
CommitLimit: 10460472 kB
Committed_AS: 1730676 kB
PageTables: 20500 kB
VmallocTotal: 106488 kB
VmallocUsed: 6980 kB
VmallocChunk: 99340 kB
HugePages_Total: 0
HugePages_Free: 0
Hugepagesize: 2048 kB
物理内存是4G，所以sga_max_size至少可以调整到50%以上，最大可以调整到70%.这里我先调整到50%.调整SGA需要一步一步

来，要运行一段时间，看终端应用情况如何。在做调整。

4：调整SGA，采用scope=spfile;
SQL> alter system set sga_max_size=2000m scope=spfile;

System altered
SQL> alter system set sga_target=2000m scope=spfile;

System altered

5:重启服务器
[root@localhost /]# su - oracle
[oracle@localhost ~]$ export $ORACLE_SID=zxhs;
[oracle@localhost ~]$ echo $ORACLE_SID
zxhs
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Oct 30 21:47:37 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn /as sysdba;
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size 1220384 bytes
Variable Size 469762272 bytes
Database Buffers 1610612736 bytes
Redo Buffers 15556608 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 2000M
sga_target big integer 2000M

修改成功。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22934571/viewspace-1040632/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22934571/viewspace-1040632/