oracle数据库调优总结(2)-转

以前的会话

V$session视图显示一个会话出现性能问题的原因，但是导致性能问题的会话已经消失。另一个重要视图v$session_event，为会话显示所有它的等待时间，但是和v$session一样，它只显示在DB中仍然active的会话的数据。v$sesstat为一个会话显示资源使用并能够提供哪一个会话消耗了某种资源(redo或内存)的容量。但是由于所有经历性能问题的会话都成为历史，查看这些视图并不能提供帮助。你需要确定发生在某个时间点指定历史会话的等待事件。

活动会话历史

在Oracle中称为活动会话历史中能找到性能问题的历史会话。每一秒钟，ASH调查DB找出活动的会话并dump和它们有关的相关信息-比如user id, state, 连接来自的machine，和它正在执行的SQL- 放入到SGA内一个指定区域，称为ASH buffer。因此即使一个会话在实例中不再存在，ASH buffer已经捕获了它的信息。除此之外，ASH每秒记录活动，它可以显示一个会话一秒接着一秒的快照。(注意当ASH buffer填满时，数据被写入到磁盘并且块重啊每10秒发生一次而不是每秒)。

可以查看v$active_session_history视图来查看ASH buffer的内容。下面是一些重要的列

SAMPLE_ID. The unique identifier of the Active Session History record.ASH记录的唯一标识
SAMPLE_TIME. When Active Session History captured this data on all active sessions.取样时间
USER_ID. The numerical user ID (not the username) of the database user who created this session.
SESSION_ID. The session ID (SID) of the session.
SESSION_STATE. The state the session was in when Active Session History took the sample. It shows WAITING if the session was waiting for something; otherwise, it shows ON CPU to indicate that the session was doing productive work.
EVENT. If the session was in a WAITING state (in the SESSION_STATE column), this column will show the wait event the session was waiting for.
TIME_WAITED. If the session was in a WAITING state, this column will show how long it had been waiting when Active Session History took the sample.
WAIT_TIME. If the session is doing productive work—not in a WAITING state—this column will show how long the session waited for the last wait event.
SQL_ID. The ID of the SQL statement the session was executing at the time the sample was taken.
SQL_CHILD_NUMBER. The child number of the cursor. If this is the only version of the cursor, the child number will be 0.

了解v$active_session_history的列，你就能找出历史会话正在等待什么。开始之前，先找出下面问题的答案：

使用哪一用户连接到DB？

性能问题发生的时间段？

 

现在假设用户告知性能问题发生在9月29日下午4:55到5:05。根据这个信息，你就能查询v$active_session_history视图找出这段时间内ARUP会话的活动。

col event format a30
col sample_time format a25
select session_id, sample_time, session_state, event, wait_time, time_waited, sql_id, sql_child_number CH#
from v$active_session_history
where user_id = 92
and sample_time between
    to_date('29-SEP-12 04.55.00 PM','dd-MON-yy hh:mi:ss PM')
       and
    to_date('29-SEP-12 05.05.00 PM','dd-MON-yy hh:mi:ss PM')
order by session_id, sample_time;

因为ASH收集所有活动会话的信息，你需要按照SID排序，之后按照收集时间。

让我们检查输出的第一行。它显示会话39正在等待 “enq: TX - row lock contention” 事件发生12年9月29日下午04.55.02，由于会话仍就处于waiting状态，wait_time列没有意义。因此显示为0.当会话最终获得锁时，它就能做自己它必须做的并且停止等待。这时，会话等待的总时间会更新到ASH中，就是第一次加粗的行，总等待时间是1,310,761,160微秒，或大约22分钟。这是ASH中如此重要的一个属性：在wait_time列看到0并不意味着会话完全不等待。它只是意味着会话正在等待一些时间大于1秒的事件，因为之前的wait_time和time_waited列的值都显示为0.你应该在ASH中为该会话检查等待事件的最后一次出现来确认等待总时间真实是多少。

当你向用户解释在9月29日下午4:55到5:05时间段延迟的原因是锁不可用时，用户可能会询问那个时间点会话正在执行哪条SQL语句。从上面的输出可以看到会话39正在执行sql_id为fx60htyzmz6wv ，childnumber为0的SQL语句

该语句是

select SQL_TEXT 
from v$sql 
where sql_id = 'fx60htyzmz6wv';

SQL_TEXT
——————————————————————————
update test1 set status = 'D' where object_id = :b1

SQL语句包含一个update，它必须锁定行。由于行已经被其他会话锁定，会话39就无法成功加锁并因此必须等待。用户的下一个问题应该是会话和SQL语句正在等待哪一个表的哪一行，哪一个会话持有了该行的锁。

下面查询能很容易告诉你答案

select sample_time, session_state, blocking_session, current_obj#, current_file#, current_block#, current_row#
from v$active_session_history
where user_id = 92
and sample_time between
    to_date('29-SEP-12 04.55.00 PM','dd-MON-yy hh:mi:ss PM')
    and
    to_date('29-SEP-12 05.05.00 PM','dd-MON-yy hh:mi:ss PM')
and session_id = 39
and event = 'enq: TX - row lock contention'
order by sample_time;

blocking_session列显示持有锁的会话：会话43。输出也显示行被加锁表的对象ID和其他得到行信息的必要信息。通过上面的输出，使用下面的查询你就能得到锁定行的rowid

select
    owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,
    dbms_rowid.rowid_create (
        1,
        o.data_object_id,
        row_wait_file#,
        row_wait_block#,
        row_wait_row#
    ) row_id
from v$session s, dba_objects o
where sid = &sid
and o.data_object_id = s.row_wait_obj#

OBJ_NAME       ROW_ID
—————————————  —————
ARUP.TEST1:-   AAAdvSAAHAAABGPAAw

rowid AAAdvSAAHAAABGPAAw已经被会话43加锁并且正在被会话39请求更新。现在你指导了为何会话39很慢—它正在等待一个锁等待了22分钟—它正在执行SQL正在尝试对一个特定行加锁。

资源竞争

在找出会话39性能问题的根源之后，你现在调转注意力到会话44. 重新查看v$active_session_history的输出，就能看到44号会话正在waiting(session_state:waitting)和做生产工作（ON CPU）之间切换.

注意会话44第一次出现是在SAMPLE_TIME 29-SEP-12 04.55.34.419 PM。session_state列显示waiting,这意味着在这个时间点会话正在等待。event和time_waited列分别显示 “resmgr:cpu quantum” and “109984”。这意味着会话已经等待“resmgr:cpu quantum了109,984 microseconds, or about 0.11 seconds。

下一行一秒后的取样显示，session_state列为ON CPU，这意味着会话这个时间点正在做生产任务—不是等待。你需要知道为何会话间歇等待该等待事件并因此拖慢性能。

resmgr:cpu quantum事件是由于ORACLE DB的DB资源管理功能。DB资源管理就像一个资源主管：当所有会话的总CPU请求接近100%时，它限制单个会话的CPU消耗，并且它更多重要会话得到自己所需的CPU。由于输出显示会话正在等待，你就能断定会话44消耗的CPU在那个时间点很高，DB资源管理限制了它的CPU使用。如果用户认为该应用很重要，资源管理不应对其限制。这种情况下，你可能会调查会话处于哪一个具有比请求限制更多的消耗者组下。下一步你应该为该会话找出在该时间点哪一个消耗者组是活动的—不是现在。幸运的是v$active_session_history为会话记录了在该时间点哪一个消耗者组是活动的，并显示在CONSUMER_GROUP_ID列。

select sample_time, session_state, event, consumer_group_id
from v$active_session_history
where user_id = 92
and sample_time between
    to_date('29-SEP-12 04.55.02 PM','dd-MON-yy hh:mi:ss PM')
    and
    to_date('29-SEP-12 05.05.02 PM','dd-MON-yy hh:mi:ss PM')
and session_id = 44
order by 1;
                           SESSION
SAMPLE_TIME                _STATE   EVENT               CONSUMER_GROUP_ID
—————————————————————————  ———————  
29-SEP-12 04.55.34.419 PM  WAITING  resmgr:cpu quantum              12166
29-SEP-12 04.55.35.419 PM  ON CPU                                   12166
29-SEP-12 04.55.36.419 PM  WAITING  resmgr:cpu quantum              12166
29-SEP-12 04.55.37.419 PM  ON CPU                                   12166
29-SEP-12 04.55.38.419 PM  WAITING  resmgr:cpu quantum              12166
29-SEP-12 04.55.39.419 PM  WAITING  resmgr:cpu quantum              12166
29-SEP-12 04.55.40.419 PM  ON CPU                                   12166
9-SEP-12 04.55.37.419 PM  ON CPU                                   12162
29-SEP-12 04.55.38.419 PM  ON CPU                                   12166
29-SEP-12 04.55.39.419 PM  ON CPU                                   12162
29-SEP-12 04.55.40.419 PM  ON CPU                                   12162

由于会话可能曾经处于不同消耗者组下，ASH中为该会话列出所有取样数据的消耗者组。在 29-SEP-12 at 04.55.37.419 PM时间点时会话44处于12166消耗者组下。找出该消耗组的名字

select name
from v$rsrc_consumer_group
where id in (12166,12162);
  ID  NAME
—————— ————————————
12166  OTHER_GROUPS
12162  APP_GROUP

该消耗者组为other_groups。在9-SEP-1204.55.37.419 PM会话的CONSUMER_GROUP_ID从12166变为12162。这可能是3种最可能原因之一：DBA手工激活了一个不同的资源计划，一个不同的计划被调度机制自动激活，或DBA修改了消耗者组other_groups内的会话到APP_GROUP中。不管什么原因，APP_GROUP消耗者组生效，会话比之前等待“resmgr:cpu quantum” event，能做更多生产任务。这个观察能得出很多结论，但是最明显的是APP_GROUP对CPU分配比other_groups限制更少。

下一个问题是为何会话44对CPU消耗如此之大以至于DB资源管理限制它。答案在会话44的SQL语句。查看sql_id fngb4y81xr57x

SQL> select SQL_TEXT from v$sql 
where sql_id = 'fngb4y81xr57x';
 
SQL_TEXT
———————————————————————————————————
SELECT MAX(TEST1.OWNER) FROM TEST1, 
TEST2, TEST2, TEST2, TEST2, TEST2, 
TEST2, TEST2, TEST2, TEST2, TEST2, 
TEST2, TEST2, TEST2, TEST2, TEST2, 
TEST2, TEST2, TEST2, TEST2, TEST2, 
TEST2, TEST2, TEST2, TEST2

ActiveSession History归档

由于ASH收集的信息保留在SGA内，根据DB活动的不同，会导致ASHbuffer内收集了太多数据，但是由于ASH buffer是内存结构，它的空间有限。另外，当实例关闭，实例内存消失，因此，Oracle归档ASH buffer的信息到DB表使其不丢失。在dba_hist_active_sess_history视图能看到归档表数据。如果你不能从v$active_session_history视图找到数据，检查dba_hist_active_sess_history视图。

select sample_time, session_state, blocking_session,
owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,
    dbms_ROWID.ROWID_create (
        1,
        o.data_object_id,
        current_file#,
        current_block#,
        current_row#
    ) row_id
from dba_hist_active_sess_history s, dba_objects o
where user_id = 92
and sample_time between
    to_date('29-SEP-12 04.55.02 PM','dd-MON-yy hh:mi:ss PM')
    and
    to_date('29-SEP-12 05.05.02 PM','dd-MON-yy hh:mi:ss PM')
and event = 'enq: TX - row lock contention'
and o.data_object_id = s.current_obj#
order by 1,2;