ORACLE 调优介绍（oracle数据库参数调优）

【一】调优介绍

一、明确谁来调优：

数据库管理员、应用架构师、应用设计师、应用开发人员、OS系统管理员、存储系统管理员。

二、DBA在调优中做什么：

1）应用调优（DBA和开发人员合作）

SQL statement performance Change management

2）实例调优（DBA负责）

MemoryDatabase structure

Instance configuration

3）操作系统（DBA与系统管理员合作）

I/O

Swap

Parameters

三、调优方法论：

OWI全称 Oracle Wait Interface，即基于等待事件的调优方法。等待事件到11g已发展到近1000个，从10g开始，性能调优的重点已经不再单纯是提高缓存击中率了。

OWI是一种用于定位process bottlenecks（即wait events）的方式：包括I/O、locks、latches、bk process activities、network latencies等等，它记录了所有这些事件的等待次数和总的等待时间。

在OWI之前，要定位问题必须将checklist上的所有项目都执行一遍，再根据经验判断问题所在，这往往浪费大量的时间而且容易产生错误。通过解除或者降低Wait Events，可以直接提高系统工作效率，这些数据都被记录在动态视图中或AWR报告里。

Oracle 推荐使用OWI方法，通过等待事件的分析，直接消除问题。

调整目标具有三个特征：

1）具体的（Specific）

2）可测的（Measurable）

3）可实现的（Achievable）

OWI方法论总结起来就是三点：

1）自顶向下，抓主要矛盾

2）选择可获得最大收益的事件入手

3）目标达到后见好就收

【二】基本调优工具

一、性能调优工具

1）Dynamic performance views--动态性能视图

2 Load Profile -- 系统负荷

Instance Efficiency Percentages -- 实例效率

Advise statistics -- 顾问统计信息

Time Model Statistics -- 时间模型统计

Top wait events -- 突出的等待事件等等

SQL order by -- 主要SQL资源占用

3）告警日志

Alert log文件和Trace files文件

4）Enterprise Manager Pages -- OEM

5）诊断包和调优包

二、DB Time model

1、什么是DB Time model

"The most important of the time model statistics is DB time. This statistics represents the total time spent in database calls and is a indicator of the total instance workload. It is calculated by aggregating the CPU and wait times of all sessions not waiting on idle wait events (non-idle user sessions). DB time is measured cumulatively from the time that the instance was started."

数据库消耗的总时间包括 DB time+background elapsed time

DB time反应的是所有user使用的数据库资源的总和，即：DB time=DB CPU+ DB Wait time(no-idle time)。

background elapsed time指数据库后台进程消耗的时间，比如PMON进程本身，或RMAN备份恢复。

idle time 比如处于连接状态的空闲session不包括在DB Wait time。

在一个正常的系统中一般来说DB time要远远大于background elapsed time。

2、调优时，很重要的是把DB Wait time（不包括idle wait）和DB CPU time对比，看看谁占的比例大，这决定了多少时间是花在有用的工作上，多少时间消耗在等待其他进程释放占用的资源。作为一般规则，调整DB Wait time比调整DB CPU time更为迫切，然而，较高的CPU time也可能表明SQL本身写的很差。而Wait time的急剧增加又可能反映了一个资源争用的迹象。

【注】资源争用通过增加更多的处理器，或集群节点，其作用往往是非常有限的，有时甚至可能适得其反。

在DB time的统计信息中，sql execute elapsed time 和parse time elapsed 以及DB CPU，这三项常常会占据90%以上的DB time，而其中sql execute elapsed time又应该会在95%以上，值得注意的是DB CPU和sql execute elapsed time是有交集的，因此你会看到在一份AWR报告中有出现DB CPU + sql execute elapsed time超过DB time的情况。

3、两个直接反应DB time统计信息的视图

v$sys_time_model
v$sess_time_model

v$sys_time_model中的STAT_NAME的db time时间单位是以微妙(microseconds)为单位，也就是百万分之一秒。视图中常用的时间单位有：

Secord 秒

Centisecond 厘秒--百分之一秒

Millisecond 毫秒--千之一秒

Microsecond 微秒--百万分之一秒

三、统计信息和等待事件

在Oracle数据库中，“统计信息”和“等待事件”是性能优化目标的重要原始数据。它们都是累计的信息。

一）统计信息的概念：

数据库的活动在内存中产生了大量的信息，把这些信息分门别类地统计出来，就是所谓的统计信息。

统计信息的值是自实例启动以来至当前的累计值，单一的统计值往往不能说明什么，而两个累计值的差才能反应那段时间内数据库的活动。

SYS@ prod>select count(distinct name) statistcs from v$sysstat;
STATISTCS
----------
604

二）等待事件的概念

先要弄清楚什么是事件？？？

Oracle根据数据库各类活动的特性定义了许多事件（Event），每个事件对应一个事件name，每个数据库版本的事件数量是不同的。本版本是11.2.0.1的，总共有多少个事件呢？

SYS@ prod>select count(distinct name) events from v$event_name;
EVENTS
----------
1118

现在回答什么是等待事件：

当一个进程无法顺利执行，那么只能通过排队等待某种资源，因为有堵塞才有等待。等待一定发生在共享资源上，一般分两种原因：

（1）资源不足

（2）资源争用

SYS@ prod>select count(distinct event) from v$system_event;
COUNT(DISTINCTEVENT)
--------------------
80

本系统没跑业务，这里有等待事件80个，它是自上次实例启动后到目前为止一共记录了80个等待event，它们都是累计值。

如果你这时跑一个大的并发访问的应用，出现资源不足或资源争用，那么还可能增加其他的等待事件，一些事件的统计值也会累计叠加。

资源不足：解决方案可以增加硬件，如CPU、MEMORY等；

资源争用：解决方案需要从应用层面和数据库结构层面想办法。

资源争用不能用资源不足的办法解决：

"When contention is evidenced by increased wait time,adding more CPUs to a node, or nodes to a cluster, would provide very limited benefit. "

统计信息和等待事件之间有一定的关系，但也不是包含关系，更不是一对一关系，它们侧重点不同，细分后命名方法也不同，从下面两个视图的对比就可说明问题。

select * from v$statname;
select * from v$event_name；

三）统计视图和等待视图

从三个方面（维度）反映重要的统计视图

1）基于系统级

v$sysstat

2）基于session级

v$sesstat 所有session分别列出统计信息，每一行是某个session对应的某种统计信息。

v$mystat 当前session统计信息。

3）基于service级

v$service_stats

此外还有一个常用的视图：

v$statname 此视图提供一个统计信息的完整列表，每行对应一种统计信息。

四）示例：查询日志累计统计信息

第一步：确定session1的sid号

[oracle@cuug ~]$ sqlplus / as sysdba
SYS@ prod>grant select on v_$mystat to scott;
SYS@ prod>conn scott/scott
SCOTT@ prod>select sid from v$mystat where rownum=1;
SID
----------
46

第二步：在plsql develop端查看该session下的有关redo的两项统计信息。观察 desc v$sesstat，该视图中没有name字段，可以和v$statname联查，以便确定相关信息。

SQL>
select ss.sid,sn.name,ss.value from v$sesstat ss,v$statname sn
where ss.STATISTIC#=sn.statistic#
and sn.name in('redo entries','redo size') and ss.sid=46;
SID NAME VALUE
---------------------------------------------
46 redo entries 630
46 redo size 80264

第三步：在该session下做一个dml操作，观察update 前后日志的变化：

SCOTT@ prod>update emp set sal=sal+1000 where empno=7788
SCOTT@ prod>commit;

第四步：重复第二步并查看结果：

SID NAME VALUE
---------------------------------------------
46 redo entries 631
46 redo size 80780

可以看到46号session的update的动作产生的日志统计信息：

产生redo entries=1 （631-630=1）

产生redo size=516 （80780-80264）

五）从三个方面反映重要的等待事件视图

1）基于系统级

v$system_event

2）基于session级

v$session_event ----所有session分别列出等待事件，每一行是某个session对应的某种等待事件；

v$session_wait ----所有session当前正在等待的事件。

3）基于service级

v$service_event

另外提供一个等待事件的完整列表，每行对应一种等待事件

v$event_name

理解事件的三个参数：

select name,parameter1,parameter2,parameter3 from v$event_name where name like '%buffer busy%'；

四、常见的几个等待事件

1、Buffer busy waits等待事件

这个等待事件的产生说明了一个会话曾经等待一个Buffer（数据块）

有两种情形是：

(1)当一个会话试图修改一个Buffer，但这个Buffer正在被另一个会话修改时。热块是典型的是资源争用，分析热块产生原因，才可对症下药：以下为热块发生的部位：

①表块，②索引块，段头块（free list），undo块等

(2)当一个会话需要读取一个Buffer，而这个Buffer正在被另一个会话从磁盘读取到内存中时。

在11g的版本中，这种等待已经被独立出来，以read by other session命名等待事件。Buffer busy waits等待事件常见于数据库中存在热块的时候，当多个用户频繁地读取或者修改同样的数据块时，这个等待事件就会产生。

2、Free buffer waits等待事件

当一个会话将数据块从磁盘读到db buffer中时，它需要找到空闲的内存空间来存放这些数据块，当内存中没有空闲的空间时，就会产生这个等待；

会话在做一致性读时，需要构造数据块在某个时刻的前映像（image），此时需要申请内存来存放这些新构造的数据块，但内存中无法找到这样的可用内存块。

当数据库中出现比较严重的free buffer waits等待事件时，可能的原因是：

(1)database buffer cache太小，

(2)内存中的脏数据太多，DBWR无法及时将这些脏数据写到磁盘中以释放空间

3、Read waits的几个等待事件

①db file scattered read

这里指的是读取的数据块在内存中是以连续的方式存放的。全表扫描和index full scan 是其典型的代表。这是在一次性读取多个连续的BLOCK的时候，产生的等待事件。db file sequential read是数据库中最常见的等待事件，一个状态良好的系统，这个等待应该占比较高的比重。

②)db file sequential read

当Oracle 需要每次I/O只读取单个数据块这样的操作时，最常见的情况有索引的访问,以ROWID的方式访问表中的数据。

③db file paralle read

同步的multiblock read，需要多CPU支持。

④direct path read (11g新特性)

大表走全表扫描可能使用direct path read方式，即全表扫描全部采用物理读，绕过SGA，以减轻对buffer cache的压力

隐含参数：_serial_direct_read = trun|false可以开关此功能。

4、Enq: TX - row lock contention

Enqueue 是 lock 的另一种描述语。当我们在AWR 报告中发现长时间的enqueue 等待事件时，说明数据库中的一个事务的行锁阻塞了另一个事务。

可以关联AWR报告中的enqueue activity部分来确定是哪一种锁定出现了长时间等待。

5、log file switch：

通常是因为归档速度不够快。表示所有的提交(commit)的请求都需要等待"日志文件切换"的完成。Log file Switch 主要包含两个子事件:

①log file switch (archiving needed)

这个等待事件出现时通常是因为日志组循环写满以后，第一个日志归档尚未完成，出现该等待。

②log file switch (checkpoint incomplete)

当日志组都写完以后，LGWR 试图写第一个log file，如果这时数据库没有完成写出记录在第一个log file中的dirty 块时(例如第一个检查点未完成)，即没有Inactive日志组可重用，该等待事件通常表示你的DBWR 写出速度太慢或者IO 存在问题。

6、log file sync：

表现为commit很慢，原因还是LGWR无法迅速写出这些日志条目。如果这个等待事件影响到数据库性能，那么就需要修改应用程序的提交频率, 为减少这个等待事件，须一次提交更多记录，或将重做日志置于较快的磁盘上，以降低归档对LGWR的影响。

示例1：Enq: TX - row lock contention（行锁）等待事件的测试

第一步：session1

SYS@ prod>conn scott/scott
SCOTT@ prod>update emp1 set sal=sal+1000 where empno=7788;

第二步：session2

[oracle@cuug ~]$ sqlplus / as sysdba
SYS@ prod>select sid from v$mystat where rownum=1;
SID
----------
43

第三步：session1

SYS@ prod>select sid,event from v$session_wait where sid=43;
SID EVENT
--------------------------------------------------------
43 SQL*Net message from client

第四步：session2

SYS@ prod>delete scott.emp1 where empno=7788;

结果：锁住

第五步：session1

SYS@ prod>select sid,event from v$session_wait where sid=43;
SQL EVENT
----------------------------------------------------
43 enq: TX - row lock contention

the end ！！！

@jackman 共筑美好！