oracle优化原则

oracle performance tuning guide
ADDM AWR ASH
AWR间隔一段时间自动生成需要的SQL
ADDM根据AWR来生成统计信息
ASH是用来短时间内分析数据]
在tune sql部分，在使用ADDM或者top sql确定了high-load sql后，可以使用sql tuning advisor和sql access advisor来tune
数据调优步骤：
1.perform pre-tuning preparations page2-5
1.1)get feedback from users;
1.2)sanity-check the operating systems of all systems involved with user performance
1.3)ensure that the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL to enable
   the automatic performance tuning features of Oracle Database,including the AWR and ADDM
2.tune the database proactively on a regular basis page2-5
2.1)review ADDM findings
2.2)implement ADDM recommendations
2.3)monitor performace problems with the database in real time
2.4)respond to performace-related alterts
2.5)validate that the changes made have produced the desired effect,and verify if the perception of performance to the users has improved
2.6)repeat these steps until your performance goals are met or become impossible to achieve due to other constraints
3.tuning the database reactively when performace problems are reported by the users; page2-6
3.1)run ADDM manually to diagnose current and historical database performance when performace problems are reported by the users
3.2)resolve transient performance problems(ASH)
3.3)resolve performance degradation over time(AWR)
3.4)validate that the changes made have produced the desired effect,and verify if the perception of performance to the users has improved
4.identify tuning,and optimizing high-load sql statements page2-7
4.1)identify high-load SQL statements
4.2)tune high-load SQL statements
4.3)optimize data access paths
4.4)repeat these steps until all high-load SQL statements are tuned for greatest efficiency
5.common performace problems:
5.1)cpu bottlenecks
5.2)undersized memory structures
5.3)IO capacity issues
5.4)suboptimal use of Oracle Database by the application
5.5)concurrency issues
5.6)Database configuration issues
5.7)short-lived performance problems
5.8)degradation of database performance over time
5.9)inefficient or high-load SQL statements
5.10)Data access paths to hot objects
  

 

查看初始化信息使用

SQL> show parameters

数据库性能指标可以从statspack报告获得，包括：

实例效率性能指标

缓存区命中率(buffer hit%)

共享区命中率(library hit%)

缓冲区未等待率(buffer nowait%)

内存排序率(in-memory sort%)

等待事件性能指标：

文件分散读取(db file scattered read)

文件顺序读取(db file sequential read)

日志文件同步(log file sync)