oracle数据库调优笔记

查看tablespace 路径

select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

 

2，查看log文件路径

selectgroup#,member from v$logfile;

 

 

切换redo日志

select * from v$logfile;

select * from v$log;

--新增logfile组

alterdatabaseaddlogfilegroup4 ('/opt/oracle/orabase/oradata//onlinelog/redo01.log','/opt/oracle/orabase/fast_recovery_area/xx/onlinelog/recover_redo01.log') size4000M;

alterdatabaseaddlogfilegroup5 ('/opt/oracle/orabase/oradata//onlinelog/redo02.log','/opt/oracle/orabase/fast_recovery_area/xxx/onlinelog/recover_redo02.log') size4000M;

alterdatabaseaddlogfilegroup6 ('/opt/oracle/orabase/oradata/onlinelog/redo03.log','/opt/oracle/orabase/fast_recovery_area/xxx/onlinelog/recover_redo03.log') size4000M;

--切换redo日志

alter system switch log file;

 

alter database drop logfile group1;

alter database droplog file group2;

alter database droplog file group 3;

ALTER SYSTEM CHECKPOINT;

alter database add logfile group 4

('/opt/oracle/orabase/oradata/onlinelog/redo01.log','/opt/oracle/orabase/fast_recovery_area/xxx/onlinelog/recover_redo01.log') size1400M;

alterdatabaseaddlogfilegroup5 ('/opt/oracle/orabase/oradata/onlinelog/redo02.log','/opt/oracle/orabase/fast_recovery_area/xxx/onlinelog/recover_redo02.log') size1400M;

alterdatabaseaddlogfilegroup6 ('/opt/oracle/orabase/oradata/onlinelog/redo03.log','/opt/oracle/orabase/fast_recovery_area/xxx/onlinelog/recover_redo03.log') size1400M;

oracle客户端实例安装

oracleinstance install

sudo apt-getinstall alien

alien -ioracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm

alien -ioracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm

生成AWR报告

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

手动生成视图:execdbms_workload_repository.create_snapshot;

更改redo日志路径

 

dump共享池信息

ALTERSESSION SET EVENTS 'immediate trace name heapdump level 2';

 

Oracle IO调优总结
通用优化手段

1，      将redo日志定向到裸设备。

2，      加大db_cache_size 。解决free buffer wait问题。

增大db_cache_size后，free buffer wait问题解决，log file switch (checkpoint incomplete) 等待，此事件的产生有两个原因：

A，     因为buffer中待写脏块多，

B，      Cpu占用时间长，

3，主机内存从8G扩大到 32G