oracle数据库优化参数调整

1.数据库buffer大小调整

sqlplus / as sysdba
create pfile from spfile;
##通过spfile创建pfile文件（此时会在E:\soft\Oracle11g\product\11.2.0\dbhome_1\database目录下生成pfile：initorcl.ora）
若修改参数以后数据库启动失败，则恢复之前配置，用之前备份的pfile文件启动数据库

startup pfile=‘E:\soft\Oracle11g\product\11.2.0\dbhome_1\database\initorcl.ora’;
关闭例程：

shutdown immediate;
启动数据库：
startup;
show parameter spfile;
查看buffer大小查看内存配置的情况:

show parameter target;
使用下面命令来调节大小（MEMORY_MAX_TARGET和MEMORY_TARGET大小为服务器可用内存的50%）：

ALTER SYSTEM SET MEMORY_MAX_TARGET = 4G SCOPE = SPFILE;
ALTER SYSTEM SET MEMORY_TARGET = 4G SCOPE = SPFILE;
ALTER SYSTEM SET SGA_TARGET =0 SCOPE = SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE;

2.根据CPU数调整db_writer_processes【所有用户数相同】

查看dbwr进程数：

show parameter cpu;
show parameter db_writer_processes
调整dbwr进程数：

alter system set db_writer_processes = cpu_count/8 scope=spfile;【动态的值cpu数除以8】

3.数据库sessions、processes使用检查

查看最大processes值：

show parameter processes;
设置processes值：

alter system set processes = 1500 scope=spfile;
当前最大session配置：

show parameter sessions;
当前session连接数：

select count(*) from v$session;
也可单独设置 sessions值（sessions 值随着 sessions自动调整：sessions= processes * 1.5 + 28）：

alter system set sessions = 2500 scope=spfile;

4.优化游标数

判断是否需要修改：

select 'session_cached_cursors' parameter,
       lpad(value, 5) value,
       decode(value, 0, '  n/a', to_char(100 * used / value, '990') || '%') usage
  from (select max(s.value) used
          from v$statname n, v$sesstat s
         where n.name = 'session cursor cache count'
           and s.statistic# = n.statistic#),
       (select value from v$parameter where name = 'session_cached_cursors')
union all
select 'open_cursors',
       lpad(value, 5),
       to_char(100 * used / value, '990') || '%'
  from (select max(sum(s.value)) used
          from v$statname n, v$sesstat s
         where n.name in
               ('opened cursors current', 'session cursor cache count')
           and s.statistic# = n.statistic#
         group by s.sid),
       (select value from v$parameter where name = 'open_cursors');

修改游标参数(session_cached_cursors使用率为100%时需要修改)：

alter system set open_cursors=500 scope=spfile sid='*';
alter system set session_cached_cursors=300 scope=spfile;

5.关闭 Oracle 11g的审计功能

审计功能，会针产生很多审计文件.aud，浪费磁盘空间和系统性能，默认是开启状态，需要关闭：

alter system set audit_trail=none scope=spfile;

6.Oracle 11g默认密码是大小写敏感的

设置为大小写不敏感：

alter system set sec_case_sensitive_logon=false scope=spfile;

7.Oracle 11g新特性，deferred_segment_creation延迟段创建，如果不修改会导致空表无法通过exp导出

alter system set deferred_segment_creation = false scope=spfile;

8.Oracle 11g默认密码过期日为180天

设置为永不过期：

alter profile default limit PASSWORD_LIFE_TIME unlimited;

9.Oracle 11g调整归档日志大小

一：su - oracle ##切换到oracle用户

二：sqlplus /as sysdba ##进入oracle数据库

三：show parameter recovery; ##查看归档日志信息

四：alter system set db_recovery_file_dest_size=20G scope=spfile; ##增加归档日志大小

五：shutdown immediate; ##关闭数据库六：startup; ##启动数据库

10.数据库dbf使用情况检查

查看：
查询表空间使用情况：

SELECT Upper(F.TABLESPACE_NAME)         "表空间名",
       D.TOT_GROOTTE_MB                 "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
       || '%'                           "使用比",
       F.TOTAL_BYTES                    "空闲空间(M)",
       F.MAX_BYTES                      "最大块(M)"
FROM   (SELECT TABLESPACE_NAME,
               Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
               Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
        FROM   SYS.DBA_FREE_SPACE
        GROUP  BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
        FROM   SYS.DBA_DATA_FILES DD
        GROUP  BY DD.TABLESPACE_NAME) D
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER  BY 1;

处理方法：
修改单个表空间文件上限大小：
alter database datafile ‘/home/oracle/oradata/easyweb/data6_01.dbf’ AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;
一个表空间文件最大为32g，超过32g需要增加，例如：
alter tablespace ZSK ADD datafile ‘/mc_data/oradata/zsk/zsk_01.dbf’ SIZE 30G;
如果增加数据库文件一次过大，可能需要时间会比较长

11.数据库监听日志大小检查

检查：检查监听日志大小，文件位置一般在$ORACLE_BASE\diag\tnslsnr<hostname>\listener\trace\listener.log，当文件到达4G就会使系统无法启动或者连接出现问题。
在linux上寻找日志文件路径，在oracle用户下输入lsnrctl,进入监听命令行后输入show log_directory，
worddavc6b354f64cb7a90497a20dc5972d881f
处理方法：将Oracle数据库服务与监听服务停止后，将原日志文件备份后，删除文件内内容，启动所有服务即可。

目录CONTENT