一篇关于调优的概念性文章

今天看了一篇关于调优的概念性文章(61998.1：Introduction to Tuning Oracle7 / Oracle8 / 8i / 9i)。

文章的目的是为了可以快速知晓执行过程中哪里花费了较多的时间，而不是关注不同统计数据的含义。更加关注实际。

1. 为什么需要调优tuning？

通常我们关注的是用户响应时间或批量作业的处理时间。也许是某天当中若干时间点出现性能问题，或者性能问题一直如此。也许仅仅一些用户函数执行较差，或者所有的函数执行地都慢。大多数系统都是用来服务用户的，如果能从系统的视角理解用户，就需要做出许多工作。

确信能够从用户视角理解所有问题-花时间向用户了解，确保清楚真实的问题。例如，如果两条大多数普通用户交易都会用到的语句出现了较差的执行计划，就说“系统任何时候都慢”，但是更细致的调查后发现，一些操作却可以获得正常的响应时间，这样的情况屡见不鲜，这就需要我们在工作的过程中，充分从客户的角度了解问题的细节，也要注重客观，这样才能做出一个有说服力的判断。

2. 根据时间来判断性能。

一旦开始根据响应时间或批处理时间思考性能问题，就可以有基础地判断任何建议的改变所带来的影响。调优的基础就是确定哪里花费的时间需要可以更快。一旦知道了哪里花费了时间，那么就可以判断影响的时间，以及潜在节省的时间。以下四个步骤是可以重复执行的：

1. 确定哪里花费了时间。

2. 挖掘出原因的细节。

3. 尝试各种可以节省时间的方法。

4. 应用这些改变，然后重复第一步。

3. Oracle能够告诉我们哪些关于时间的信息？

一个客户端应用进程通常与使用半双工协议的Oracle影子进程进行通话。每个影子进程可能是如下三种状态之一：

a. IDLE-等待处理。

b. 运行时代码-例如正在使用CPU或正在运行队列中。

c. 等待-i. 等待一些可利用资源。

ii. 等待请求的完成。

为了明确以上这些，下面是一些举例：

a. IDLE

用户影子进程正等待数据包，用户需要告诉他们接下来做什么，或者提供信息允许他们继续执行。

b. 运行时代码

进程处于等待使用CPU的运行队列，需要使用CPU资源。但Oracle不知道其正在使用CPU还是仅仅处于运行队列中。

c. i. 等待可利用资源。例如enqueue(锁)或闩。

ii. 等待事件的完成。例如一次IO读请求，或者等待LGWR完成redo日志的磁盘写。

4. 基础点。

init.ora参数TIMED_STATISTICS必须设置为TRUE，才能知道上面提到的各种状态所用时间。

Oracle记录的时间仅精确到10毫秒(1/100秒)。Oracle 9i中的一些时间已经可以支持微秒级。

5. 等待事件。

仅知道上面三种类别的耗时是不够的，所以Oracle还有一组”等待事件“对应于'a'和'c'，记录CPU利用率对应于'b'。用Oracle影子进程生命周期花费的些许时间就可以很好地证明以上说法。

状态 含义

IDLE 等待”SQL*Net message from client“。接收语句请求”parse/execute“的SQL*Net包。

ON CPU 解析SQL*Net包。

WAITING 等待”latch free“获得一个”library cache“闩。

ON CPU 在共享池中搜索SQL语句，找到匹配的，释放闩，建立与共享游标的链接，等等。开始执行。

WAITING 等待”db file sequential read“，因为我们需要一个当前不在buffer cache的数据块。例如：等待一次IO的完成。

ON CPU 读到数据块，开始执行。构建SQL*Net包，将包含第一行数据的信息返回给用户。

WAITING 等待”SQL*Net message to client“确认SQL*Net包已经送达。

IDLE 等待”SQL*Net message from client“进行下一次请求。

如果我们给以上各步骤分配时间，那么就能得到调优的效果。在独立语句级别，session级别或系统级别，Oracle都能提供一些有用的信息。

实例用时：

V$SESSION

V$SESSION_WAIT

语句级别：

SQL_TRACE or DBMS_SUPPORT.SET_TRACE output

session级别：

SQL_TRACE or DBMS_SUPPORT.SET_TRACE output

V$SESSION_EVENT

V$SESSTAT

系统级别：

V$SYSTEM_EVENT

V$SYSSTAT

6. 时间视角的观点。

如下SQL提供每个session当前的状态：

SELECT sid, status FROM V$SESSION;

可以显示每个session的状态是ACTIVE还是INACTIVE。INACTIVE的session通常正在等待来自客户端的请求。通常我们可以在V$SESSION_WAIT中看到WAIT_TIME参数值是0的”SQL*Net message from client“。

对于ACTIVE的session：

SELECT sid, wait_time, event FROM V$SESSION_WAIT;

可以显示实例中每个session状态的时间。

WAIT_TIME 含义

0 当前正在等待特定事件的session。(Oracle 9.0.1版本中，session当前未等待时，V$SESSION_WAIT显示 WAIT_TIME是0，但是由于Bug：2117360，可能CPU是有使用的。V$SESSION_WAIT.STATE会显示一个 除"WAITING"外的值。)

!=0 正使用CPU的session。列出的事件是session等待的最后一个事件。WAIT_TIME指出session等待最后这个事件的 时间。

-1 session需要等待一小段时间。

-2 不知道等待多久(TIMED_STATISTICS=false)。

>0 以10ms计算的实际等待时间。

总会有一些session一直使用CPU。如果执行上面的语句，SELECT的结果会显示”on CPU“(WAIT_TIME!=0)。

如果一个应用使用多个session或执行session转换，那么WAIT_TIME就可能是非0值，但session状态是INACTIVE。这样的sesson通常不是客户端当前的session，所以V$SESSION_WAIT显示session等待的最后事件，但该session当前是INACTIVE状态。

V$SESSION_EVENT和V$SYSTEM_EVENT视图显示了session级和系统级中每个等待事件花费的总时间。