日期:2014-05-16  浏览次数:20445 次

oracle10g_参数调整(processes和sga)

1.oracle连接数调整
oracle连接数默认为150,通过一下设置把oracle的连接数调整到500

用sys,以sysdba权限登录数据库(推荐使用PL/SQL)
以下操作请在Command Window下执行
SQL> show parameter processes;

NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes????????????????????? integer???? 0
db_writer_processes????????????????? integer???? 1
gcs_server_processes???????????????? integer???? 0
job_queue_processes????????????????? integer???? 10
log_archive_max_processes??????????? integer???? 2
processes??????????????????????????? integer???? 150

SQL> alter system set processes=500 scope = spfile;

System altered

SQL> show parameter processes;

NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes????????????????????? integer???? 0
db_writer_processes????????????????? integer???? 1
gcs_server_processes???????????????? integer???? 0
job_queue_processes????????????????? integer???? 10
log_archive_max_processes??????????? integer???? 2
processes??????????????????????????? integer???? 150

SQL> create pfile from spfile;

Done

重新启动下oracle数据库(即oracle服务)

重启好后,用sys,以sysdba权限登录数据库
在Command Window下执行
SQL> show parameter processes;
?
NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes????????????????????? integer???? 0
db_writer_processes????????????????? integer???? 1
gcs_server_processes???????????????? integer???? 0
job_queue_processes????????????????? integer???? 10
log_archive_max_processes??????????? integer???? 2
processes??????????????????????????? integer???? 500

查看你这一行数据
processes??????????????????????????? integer???? 500
即表明数据库的连接数被成功修改为500



2.调整oracle数据库的sga(相关参数的设置与服务器的物理内存有关,下面参数适用内存为2G的服务器,如果内存为其他值的请与开发这边联系,确定具体的参数值)
用sys,以sysdba权限登录数据库(推荐使用PL/SQL)
以下操作请在Command Window下执行


--要动态修改一定要用spfile启动。如果现在是用pfile启动,可以这样切换成spfile启动:
--create spfile from pfile;
--shutdown immediate;
--startup;
--用show parameter spfile 查看如果values对应有值,表示是spfile启动的。否则就是pfile启动的。


SQL> show parameter sga;
?
NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
lock_sga???????????????????????????? boolean???? FALSE
pre_page_sga???????????????????????? boolean???? FALSE
sga_max_size???????????????????????? big integer 164M
sga_target?????????????????????????? big integer 0

SQL> show parameter statistics_level;
?
NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
statistics_level???????????????????? string????? TYPICAL

保证STATISTICS_LEVEL 参数设置为 TYPICAL (the default) 或者 ALL

如果不是TYPICAL 或者 ALL,那么请执行
SQL> alter system set statistics_level = TYPICAL;
?
System altered
?
SQL> commit;
?
Commit complete

SQL> alter system set sga_max_size = 800m scope=spfile;
?
System altered

SQL> commit;
?
Commit complete

至此请重新启动下oracle数据库(即oracle服务)

重启好后,用sys,以sysdba权限登录数据库
在Command Window下执行
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> alte