在 Oracle 11g 安装并建库后，需要进行一些调整，使数据库能够稳定、高效地运行。花了一段时间整理了以下的参数设置规范，仅供参考。
1、进程及会话数(默认150)
--查看进程及会话数
show parameter process;
show parameter sessions;
--修改进程及会话数
alter system set processes=1200 scope=spfile;
alter system set sessions=1325 scope=spfile;
参考：sessions = 1.1 * processes + 5
2、游标数
--查看游标数
show parameter open_cursors;
--查看当前打开的游标数目
select count(*) from v$open_cursor;
--修改最大游标数
alter system set open_cursors=1000 scope=both
3、字符集（建议建库时就设置好）
--查询数据库服务端的字符集：NLS_LANG = language_territory.charset
select * from nls_database_parameters;
--常见服务器参数设置
export NLS_LANG=AMERICAN_AMERICA.UTF8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
4、sga pga大小
oltp系统参考（要设置好，防止内存抖动）： sga=内存*80%*80% pga=内存*80%*20%
show parameter memory;
show parameter sga;
show parameter pga;
--如果要防止高并发情况下的内存抖动,考虑固定内存
alter system set memory_target=4096m scope=spfile;
alter system set memory_max_target=4096m scope=spfile;
alter system set sga_target=3027m scope=spfile;
alter system set sga_max_size=3027m scope=spfile;
alter system set pga_aggregate_target=3027m scope=spfile;
alter system set pga_aggregate_target=1024m scope=spfile;
5、控制文件
说明：控制文件默认2个，建议增加一个在不同位置
show parameter control;
alter system set control_files="/oracle/redolog/RFDB/controlfile/o1_mf_gjrb59v8_.ctl","/u01/App/oracle/product/11.2.0/db_1/dbs/control02.ctl" scope=spfile;
shutdown immediate;
!cp /oracle/redolog/RFDB/controlfile/o1_mf_gjrb59v8_.ctl /u01/app/oracle/product/11.2.0/db_1/dbs/control02.ctl
startup;
show parameter control;
6、redo日志
redo 默认50M, 改成100M或更大，具体根据每天产生redo的量来设置
--查看相关设置
select member from v$logfile;
select group#,members,bytes/1024/1024,status from v$log;
--增加日志组
alter database add logfile group 4 ('/home/u01/app/oracle/oradata/nwppdb/redo04.log') size 100M;
alter database add logfile group 5 ('/home/u01/app/oracle/oradata/nwppdb/redo05.log') size 100M;
alter database add logfile group 6 ('/home/u01/app/oracle/oradata/nwppdb/redo06.log') size 100M;
--redo每组默认3组每组一个成员，建议每组两个成员以上
alter database add logfile member '/home/u01/app/oracle/oradata/nwppdb/redo04_2.log' to group 4;
alter database add logfile member '/home/u01/app/oracle/oradata/nwppdb/redo05_2.log' to group 5;
alter database add logfile member '/home/u01/app/oracle/oradata/nwppdb/redo06_2.log' to group 6;
--切换
alter system switch logfile;
--删除之前小的日志组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3；
7、undo(20~30G,具体看实际业务而定)
--设置undo_retention 参数，默认是900s ，推荐设置为设置为10800，即3个小时
show parameter undo_retention;
alter system set undo_retention=10800 scope=spfile;
--查看undo表空间大小
select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';
--增大undo表空间大小
alter database datafile '/home/u01/app/oracle/oradata/ytzx/undotbs01.dbf' resize 10240M;
--给undo表空间增加数据文件
alter tablespace UNDOTBS1 add datafile '/home/u01/app/oracle/oradata/ytzx/undotbs2.dbf' size 10240M autoextend on;
--查看现在undo表空间大小
select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';
8、临时表空间(20~30G,具体看实际业务而定)
--查看临时表空间大小、是否自动扩展
select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files;
--增大临时表空间文件的大小，把20M 调整成10240 M
alter database tempfile '/u01/app/oracle/oradata/ytzx/temp01.dbf' resize 10240M autoextend on next 100M maxsize 10G;
--增加临时文件
alter tablespace temp add tempfile '/u01/app/oracle/oradata/ytzx/temp02.dbf' size 10240M AItpextemd pm mext 100M maxsize 10G;
9、归档及闪回配置
--1、配置归档
archive log list;
select name,log_mode,open_mode from v$database;
show parameter cluster; //RAC
alter system set cluster_database=false scope=spfile; //RAC
shutdown immediate;
startup mount;
alter database archivelog;
alter system set cluster_database=true scope=spfile; //RAC
shutdown immediate;
startup;
--2、配置闪回(11g不需要重启)
select flashback_on from V$database;
show parameter recover;
alter system set db_recovery_file_dest_size=1500m scope=both;
alter system set db_recovery_file_dest='+dgrecovery' scope=both;
alter system set cluster_database=false scope=spfile; //RAC
shutdown immediate;
startup mount;
alter database flashback on;
alter system set cluster_database=true scope=spfile; //RAC
shutdown immediate;
startup;
10、关于审计
--考虑关闭审计（oracle 11g 默认打开审计）
alter system set audit_trail=none sid='*' scope=spfile;
--说明：11g 默认打开数据库审计，为了避免审计带来的 SYSTEM 表空间的过多占用，可以关闭审计。
--最近一年审计记录
select * from aud$ where substr(sysdate-NTIMESTAMP#,2,9)<360;
--清理数据库审计
truncate table sys.aud$ reuse storage;
alter table sys.aud$ deallocate unused keep 25000m;
alter table sys.aud$ deallocate unused keep 20000m;
alter table sys.aud$ deallocate unused keep 15000m;
alter table sys.aud$ deallocate unused keep 10000m;
alter table sys.aud$ deallocate unused keep 5000m;
alter table sys.aud$ deallocate unused keep 2000m;
alter table sys.aud$ deallocate unused keep 500m;
alter table sys.aud$ deallocate unused keep 250m;
alter table sys.aud$ deallocate unused keep 10m;
11、关于密码重用
alter profile "DEFAULT" limit PASSword_GRACE_TIME UNLIMITED;
alter profile "DEFAULT" limit PASSWORD_LIFE_TIME UNLIMITED;
alter profile "DEFAULT" limit PASSWORD_LOCK_TIME UNLIMITED;
alter profile "DEFAULT" limit FAILED_LOGIN_ATTEMPTS UNLIMITED;
说明：11g 默认会将 DEFAULT 的 PROFILE 设置登录失败尝试次数（10 次）。这样在无意或恶意的连续使用错误密码连接时，导致数据库用户被锁住，影响业务。因此需要将登录失败尝试次数设为不限制。
12、关于维护任务
exec dbms_scheduler.disable( 'ORACLE_OCM.MGMT_CONFIG_JOB' );
exec dbms_scheduler.disable( 'ORACLE_OCM.MGMT_STATS_CONFIG_JOB' );
说明：关闭一些不需要的维护任务，这两个属于 ORACLE_OCM 的任务不关闭，可能会在 alert日志中报错。
13、其他
13.1、考虑是否要关闭自动统计信息收集（慎用，除非有其他手工收集统计信息的完整方案，否则不建议关闭）
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
说明：如果是需要采用手工收集统计信息策略，则关闭统计信息自动收集任务。
13.2、考虑是否要关闭自动收集直方图
exec DBMS_STATS.SET_GLOBAL_PREFS( 'method_opt','FOR ALL COLUMNS SIZE 1' );
或者
exec DBMS_STATS.SET_PARAM( 'method_opt','FOR ALL COLUMNS SIZE 1' );
说明：为减少统计信息收集时间，同时为避免直方图引起的 SQL 执行计划不稳定，可以在数据库全局级关闭自方图的收集，对于部分需要收集直方图的表列，可以使用
DBMS_STATS.SET_TABLE_PREFS 过程来设置。
13.3、关闭 auto space advisor
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/
说明：关闭数据库的空间 Advisor，避免消耗过多的 IO，还有避免出现这个任务引起的 library cache lock。
13.4、关闭 auto sql tuning
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
说明：关闭数据库的 SQL 自动调整 Advisor，避免消耗过多的资源。
13.5、调整时间窗口：
--查询窗口定义详情--10g
select window_name,repeat_interval,duration,enabled from dba_scheduler_windows;
--查询窗口定义详情--11g和12c（周一到周五每晚10点开始收集统计信息，duration是4h；周六周日早上6点开始收集统计信息，duration是20h）
SQL> select window_name,repeat_interval,duration,enabled from dba_scheduler_windows;
--根据具体业务情况调整
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW','repeat_interval','freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW','repeat_interval','freq=daily;byday=SUN;byhour=22;byminute=0;bysecond=0');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW', 'duration', '+000 08:00:00');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW', 'duration', '+000 08:00:00');
exec dbms_scheduler.disable('WEEKNIGHT_WINDOW', TRUE);
exec dbms_scheduler.disable('WEEKEND_WINDOW', TRUE);
说明：一些业务系统即使在周末，也同样处于正常的业务工作状态，比如面向公众的业务系统，在月底（虽然是周末）有批处理操作的系统，以及节假日调整的周末等，建议调整周六和周日窗口的起止时间和窗口时间长度，避免有时候周六或周日影响业务性能。
alter system set parallel_force_local=true sid='*' scope=spfile;
说明：这个参数是 11g 的新增参数，用于将并行的 slave 进程限制在发起并行 SQL 的会话所在的节点，即避免跨节点并行产生大量的节点间数据交换和引起性能问题。
这个参数用于取代 11g 之前 instance_groups 和 parallel_instance_group 参数设置。
alter system set "_gc_policy_time"=0 sid='*' scope=spfile;
alter system set "_gc_undo_affinity"=false scope=spfile;
说明：这两个参数用于关闭 RAC 的 DRM（dynamic remastering）特性，避免频繁的 DRM 使系统性能不稳定、严重的时候使数据库挂起。同时也关闭 Read-mostly Locking 新特性，这个特性目前会触发大量的 BUG，严重时使数据库实例宕掉。
针对 11g RAC，需要注意的是如果节点的 CPU 数量不一样，这可能导致推导出来的 lms 进程数量不一样，根据多个案例的实践来看，lms数量不一样在高负载时会产生严重的性能问题，在此种情况下，需要手工设置 gcs_server_processes 参数，使 RAC 数据库所有节点的 lms 进程数相同。
alter system set "_optimizer_adaptive_cursor_sharing"=false sid='*' scope=spfile;
alter system set "_optimizer_extended_cursor_sharing"=none sid='*' scope=spfile;
alter system set "_optimizer_extended_cursor_sharing_rel"=none sid='*' scope=spfile;
alter system set "_optimizer_use_feedback"=false sid ='*' scope=spfile;
说明：这几个参数都是用于关闭 11g 的 adaptive cursor sharing、cardinality feedback 特性，避免出现 SQL 性能不稳定、SQL 子游标过多的问题。
alter system set deferred_segment_creation=false sid='*' scope=spfile;
说明：这个参数用于关闭 11g 的段延迟创建特性，避免出现这个新特性引起的 BUG，比如数据导入导出 BUG、表空间删除后对应的表对象还在数据字典里面等。
alter system set event='28401 trace name context forever,level 1','10949 trace name context forever,level 1' sid='*' scope=spfile;
说明：这个参数主要设置 2 个事件：
1） 10949 事件用于关闭 11g 的自动 serial direct path read 特性，避免出现过多的直接路径读，消耗过多的 IO 资源。
2） 28401 事件用于关闭 11g 数据库中用户持续输入错误密码时的延迟用户验证特性，避免用户持续输入错误密码时产生大量的 row cache lock 或 library cache lock 等待，严重时使数据库完全不能登录。
alter system set resource_limit=true sid='*' scope=spfile;
alter system set resource_manager_plan='force:' sid='*' scope=spfile;
说明：这两个参数用于将资源管理计划强制设置为“空”，避免 Oracle 自动打开维护窗口（每晚 22:00 到早上 6:00，周末全天）的资源计划（resource manager plan），使系统在维护窗口期间资源不足或触发相应的 BUG。
alter system set "_undo_autotune"=false sid='*' scope=spfile;
说明：关闭 UNDO 表空间的自动调整功能，避免出现 UNDO 表空间利用率过高或者是 UNDO段争用的问题。
alter system set "_optimizer_null_aware_antijoin"=false sid ='*' scope=spfile;
说明：关闭优化器的 null aware antijoin 特性，避免这个新特性带来的 BUG。
alter system set "_px_use_large_pool"=true sid ='*' scope=spfile;
说明：11g 数据库中，并行会话默认使用的是 shared pool 用于并行执行时的消息缓冲区，并行过多时容易造成 shared pool 不足，使数据库报 ORA-4031 错误。将这个参数设置为 true，使并行会话改为使用 large pool。
alter system set "_partition_large_extents"=false sid='*' scope=spfile;
alter system set "_index_partition_large_extents"=false sid='*' scope=spfile;
说明：在 11g 里面，新建分区会给一个比较大的初始 extent 大小（8M），如果一次性建的分区很多，比如按天建的分区，则初始占用的空间会很大。
alter system set "_use_adaptive_log_file_sync"=false sid='*' scope=spfile;
说明：11.2.0.3 版本里面，这个参数默认为 true，LGWR 会自动选择两种方法来通知其他进程 commit 已经写入：post/wait、polling。前者 LGWR 负担较重，后者等待时间会过长，特别是高负载的 OLTP 系统中。在 10g 及之前的版本中是 post/wait 方式，将这个参数设置为 false恢复到以前版本方式。
alter system set "_memory_imm_mode_without_autosga"=false sid='*' scope=spfile;
说明：11.2.0.3 版本里面，即使是手工管理内存方式下，如果某个 POOL 内存吃紧，Oracle仍然可能会自动调整内存，用这个参数来关闭这种行为。
alter system set enable_ddl_logging=true sid='*' scope=spfile;
说明：在 11g 里面，打开这个参数可以将 ddl 语句记录在 alert 日志中。以便于某些故障的排查。建议在 OLTP 类系统中使用。
alter system set parallel_max_servers=64 sid='*' scope=spfile;
说明：这个参数默认值与 CPU 相关，OLTP 系统中将这个参数设置小一些，可以避免过多的并行对系统造成冲击。
alter system set sec_case_sensitive_logon=false sid='*' scope=spfile;
说明：从 11g 开始，用户密码区分大小写，而此前的版本则是不区分大小写，在升级时，如果这个参数保持默认值 TRUE，可能会使一些应用由于密码不正确而连接不上。
alter system set "_b_tree_bitmap_plans"=false sid='*' scope=spfile;
说明：对于 OLTP 系统，Oracle 可能会将两个索引上的 ACCESS PATH 得到的 rowid 进行 bitmap操作再回表，这种操作有时逻辑读很高，对于此类 SQL 使用复合索引才能从根本上解决问题。
附常用的数据统计Sql
1. 统计各个条件下的数据
select
BatchId,sum(CardSum) 总金额,
sum(case when Status=1 then CardSum else 0 end) as 已使用,
sum(case when Status=2 then CardSum else 0 end) as 已冻结
from GiftCard
group by BatchId
2. 统计每日,每月,每年的数据
select year(AddTime) 年,month(AddTime) 月,day(AddTime) 日,COUNT(1) 数量,sum(CardSum) 销售合计
from GiftCard
group by year(AddTime),month(AddTime),day(AddTime)
3. 某列去重统计
select COUNT(BatchId),COUNT(distinct BatchId),COUNT(distinct BatchName)
from GiftCard
4. 行转列
SELECT *
FROM (
SELECT
BatchName,
CardSum as TotAmount
FROM GiftCard
) as s
PIVOT
(
SUM(TotAmount)
FOR BatchName IN (zx测试商品, test新人优惠券,测试高考大放送)
)AS MyPivot
5. 得到表中最小的未使用的ID号
SELECT
(CASE WHEN EXISTS(SELECT * FROM GiftCard b WHERE b.Id = 1) THEN MIN(Id) + 1 ELSE 1 END) as Id
FROM GiftCard
WHERE NOT Id IN (SELECT a.Id - 1 FROM GiftCard a)
6. 查询某一列数据不重复的数量
select *
from GiftCard a
where not exists(select 1 from GiftCard where BatchName=a.BatchName and ID<a.ID)
7. 按年统计1月到12个月的销量
select year(AddTime) as '年',
SUM(case when MONTH(AddTime)=1 then CardSum else 0 end ) as '一月',
SUM(case when MONTH(AddTime)=2 then CardSum else 0 end ) as '二月',
SUM(case when MONTH(AddTime)=3 then CardSum else 0 end ) as '三月',
SUM(case when MONTH(AddTime)=4 then CardSum else 0 end ) as '四月',
SUM(case when MONTH(AddTime)=5 then CardSum else 0 end ) as '五月',
SUM(case when MONTH(AddTime)=6 then CardSum else 0 end ) as '六月',
SUM(case when MONTH(AddTime)=7 then CardSum else 0 end ) as '七月',
SUM(case when MONTH(AddTime)=8 then CardSum else 0 end ) as '八月',
SUM(case when MONTH(AddTime)=9 then CardSum else 0 end ) as '九月',
SUM(case when MONTH(AddTime)=10 then CardSum else 0 end ) as '十月',
SUM(case when MONTH(AddTime)=11 then CardSum else 0 end ) as '十一月',
SUM(case when MONTH(AddTime)=12 then CardSum else 0 end ) as '十二月'
from GiftCard
group by year(AddTime)
京公网安备 11010802035086号