oracle调优时一些常用分析经验

#限制连接数
create profile shanghai_ip2 limit SESSIONS_PER_USER 400;
alter user pbcebipdb_2900 profile shanghai_ip2;

查看连接数：
1、查看当前连接数
select count(*) from v$process;

2、允许的最大连接数
select value from v$parameter where name='processes';

3、修改最大连接数
alter system set processes = 2500 scope = spfile

4、修改SGA
1)最大值 alter system set sga_max_size=10240M scope=spfile;
alter system set sga_target=6G scope=spfile;

****************** log file sync 分析调优 **************
1、1) commit  
   2) rollback 
   3) DDL操作 (DDL操作实施前会进行一次commit)
   4) DDL操作导致的数据字典修改产生的commit
   5) 某些能递归修改数据字典的操作：比如查询 SEQ 的 NEXT 只，导致修改数据字典，SEQ的 cache 设置为 nocache 时，每次都修改字典

2、多分日志盘、优化应用、查看 CPU 资源(设置LGWR优先级)、调整 REDO LOG 块大小为 4k、AIX 开启内测预读 dscrctl -n -b -s l

3、log IO 分析获取时间，连续查两次，看时间段内变化
select event,wait_time_milli,wait_count from v$event_histogram where event = 'log file parallel write';
select event,wait_time_milli,wait_count from v$event_histogram where event = 'log file sync';

4、LOG BUFFER 一般不需要调整，除非 redo allocation latch 争用

5、commit_log(一般不用)，batch|immediate 两个模式，immediate 两条 redo record，对日志量减少影响不大
   commit_write(重要)，wait|nowait 两个模式，前台进程是否等待 log file sync 结束

alter system set commit_logging=batch scope=both

****************** REDO *********************
redo:
查看磁盘组
select name,state,type,free_mb,total_mb,usable_file_mb from v$asm_diskgroup;
查磁盘组
select name,path from v$asm_disk order by name;
查log file sync post-wait还是poll模式
select name,value from v$sysstat where name in('redo synch poll writes','redo synch polls');
查看trace文件
select * from v$diag_info
查看平均等待时间
select EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,AVERAGE_WAIT from v$system_event where event in('log file sync','log file parallel write');
查看logbuffer
select value from v$parameter where name = 'log_buffer';
查log组
select * from v$log;
select * from v$logfile;

ASM增加磁盘组：
1、检查已经有的系统磁盘
   df -k; lspv;
2、查ASM资源子目前情况
   asmcmd 
   lsdg
3、在EMC存储上划分lun
4、主机扫描新建磁盘
   cfgmgr
5、确认磁盘
   lspv
   powermt display dev=all|more
6、对新加磁盘赋予权限和属性
   cd /dev
   ls -l *hdiskpower*
   chown ora10g:dba /dev/hdiskpower
   chmod 660 /dev/hdiskpower
7、DG磁盘组增加disk
   1）查磁盘组
   select name,path from v$asm_disk order by name;
   2）增加
   alter diskgroup DGDATA add disk '/dev/hdiskpower' rebalance power 10;
8、监控asm磁盘组平衡速度
   select * from v$asm_operation;  
   直到出现以下信息表示磁盘组平衡完成   no rows selected

****************** 解析 *********************
--查看执行计划个数大于2的
select sql_id, count(1) as plan_num from v$sql group by sql_id having count(1) >=2 order by 2 desc;
--根据sqlid查sql
select * from v$sql where sql_id='3613943922'
--根据sqlid查sql
select * from v$sql where sql_text like '%update%T_EB_NPCP_PAYJOUR4%7708%'

****************** UNDO *********************
查释放情况
select t.status,sum(t.blocks)*8/1024||'M' from dba_undo_extents t group by t.status;
查自动释放设置：
select * from v$undostat;
undo设置
alter system set "_undo_autotune"=false
alter system set "_smu_debug_mode"=33554432
alter system set "undo_retention"=200

查占用情况
select s.USERNAME,u.name 
from v$transaction t,v$rollname u,v$rollstat r,v$session s
where s.taddr=t.ADDR and t.XIDUSN=r.USN and r.USN =u.usn order by s.username;

**************** 信息统计 ****************
exec dbms_stats.gather_schema_stats('数据库实例名',degree=>10);

 

**************** AWR ****************

1、手动创建一个快照：

exec dbms_workload_repository.create_snapshot;

2、AWR报告生成

本实例AWR报告	@?/rdbms/admin/awrrpt
RAC中选择实例号	@?/rdbms/admin/awrrpti
AWR 比对报告	@?/rdbms/admin/awrddrpt
RAC全局AWR报告	@?/rdbms/admin/awrgrpt

 

      报告关注：Hard parses、log、sql cpu time、cache命中率等内容，具体报告分析网上很多，不具体说明。