不同用户数访问量等级下oracle数据库参数调整

50用户以下：8GB 混用，oracle 占据1GB~3GB内存，open_cursors:300，processes:100
50～100用户：16GB 混用，oracle 占据3~4GB内存，open_cursors:300，processes:100
100～250用户：16GB 混用，oracle 占据4~6GB内存，open_cursors:500，processes:250
300～500用户：32GB 混用，oracle 占据6~8GB内存 open_cursors:1000 processes:500
500～1500用户：32GB 混用，oracle 占据8~16GB内存，open_cursors:2000，processes:800
2000用户以上：64GB 混用，oracle 占据50GB内存，open_cursors:3000，processes:1500



1.备份参数文件【所有用户数相同】
a.在E盘根目录下建立文件夹：Oracle_Bak
b.create pfile from spfile;
c.拷贝D:\oracle\product\11.2.0\dbhome_1\database下的INITorcl.ORA文件和SPFILEORCL.ORA文件到E:\Oracle_Bak
d.首先确认内存达到配置要求：
SQL> SHOW PARAMETER TARGET


2.根据CPU数调整db_writer_processes【所有用户数相同】
show parameter cpu;
alter system set db_writer_processes = cpu_count/8 scope=spfile;【动态的值cpu数除以8】

3.processes和open_cursors根据以上配置策略设置即可
alter system set processes = 100 scope=spfile;【50用户以下】【50-100用户】
alter system set open_cursors = 300 scope=spfile;【50用户以下】【50-100用户】

alter system set processes = 250 scope=spfile;【100-250用户】
alter system set open_cursors = 500 scope=spfile;【100-250用户】

alter system set processes = 500 scope=spfile;【300-500用户】
alter system set open_cursors = 1000 scope=spfile;【300-500用户】

alter system set processes = 800 scope=spfile;【500-1500用户】
alter system set open_cursors = 2000 scope=spfile;【500-1500用户】

alter system set processes = 1500 scope=spfile;【2000用户以上】
alter system set open_cursors = 3000 scope=spfile;【2000用户以上】

alter system set session_cached_cursors = 80 scope=spfile;【所有用户数相同】
alter system set sga_max_size = 0 scope=spfile;
alter system set sga_target = 0 scope=spfile;

alter system set memory_max_target = 3G scope=spfile;【50用户以下】
alter system set memory_target = 3G scope=spfile;【50用户以下】
alter system set memory_max_target = 4G scope=spfile;【50-100用户】
alter system set memory_target = 4G scope=spfile;【50-100用户】
alter system set memory_max_target = 6G scope=spfile;【100-250用户】
alter system set memory_target = 6G scope=spfile;【100-250用户】
alter system set memory_max_target = 8G scope=spfile;【300-500用户】
alter system set memory_target = 8G scope=spfile;【300-500用户】
alter system set memory_max_target = 16G scope=spfile;【500-1500用户】
alter system set memory_target = 16G scope=spfile;【500-1500用户】
alter system set memory_max_target = 50G scope=spfile;【2000用户以上】
alter system set memory_target = 50G scope=spfile;【2000用户以上】

alter database add logfile group 4 ('D:\ORADATA\ORCL\REDO04.LOG') size 100m;
alter database add logfile group 5 ('D:\ORADATA\ORCL\REDO05.LOG') size 100m;
alter database add logfile group 6 ('D:\ORADATA\ORCL\REDO06.LOG') size 100m;
alter database add logfile group 7 ('D:\ORADATA\ORCL\REDO07.LOG') size 100m;
alter database add logfile group 8 ('D:\ORADATA\ORCL\REDO08.LOG') size 100m;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;


4.重启数据库，需在cmd中使用"sqlplus / as sysdba"登陆后执行
shutdown immediate
startup

5.删除弃用日志文件组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

6.去D:\Oracle\oradata\orcl下手动删除文件REDO01.LOG，REDO02.LOG，REDO03.LOG

7.建立新的日志文件组及复用日志成员
alter database add logfile group 1 ('D:\ORADATA\ORCL\REDO01.LOG') size 200m;
alter database add logfile group 2 ('D:\ORADATA\ORCL\REDO02.LOG') size 200m;
alter database add logfile group 3 ('D:\ORADATA\ORCL\REDO03.LOG') size 200m;
alter database add logfile member 'E:\ORADATA\ORCL\REDO01a.LOG' to group 1;
alter database add logfile member 'E:\ORADATA\ORCL\REDO02a.LOG' to group 2;
alter database add logfile member 'E:\ORADATA\ORCL\REDO03a.LOG' to group 3;
alter database add logfile member 'E:\ORADATA\ORCL\REDO04a.LOG' to group 4;
alter database add logfile member 'E:\ORADATA\ORCL\REDO05a.LOG' to group 5;
alter database add logfile member 'E:\ORADATA\ORCL\REDO06a.LOG' to group 6;
alter database add logfile member 'E:\ORADATA\ORCL\REDO07a.LOG' to group 7;
alter database add logfile member 'E:\ORADATA\ORCL\REDO08a.LOG' to group 8;

8.使用命令查看各个参数是否调整正确
show parameter processes;
show parameter cursors;
show parameter memory;
show parameter sga;
SELECT group#, members, bytes / 1024 / 1024 || 'M' AS sizes, status FROM v$log;