[20150326]关于等待事件的注意问题.txt

[20150326]关于等待事件的注意问题.txt

--昨天重新看,书中提到等待事件的注意事项.oracle随着版本号加大,等待事件增加越来越多.
--书中提到一般分为两类:一类是主动触发事件.另一类是被动触发事件,还提到了一个小的注意事项,实际上自己以前也遇到过,还是自己
--做一个小例子来说明:

1.建立测试环境:
SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> alter system set cpu_count=2 ;
System altered.
--限制一些cpu的使用数量,不然很难模拟问题出现.

SCOTT@test> select rowid,dept.* from dept;
ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AABJVUAAEAAAAdzAAA         10 ACCOUNTING     NEW YORK
AABJVUAAEAAAAdzAAB         20 RESEARCH       DALLAS1
AABJVUAAEAAAAdzAAC         30 SALES          CHICAGO
AABJVUAAEAAAAdzAAD         40 OPERATIONS     BOSTON

$ cat h2.sql
declare
m_id number;
m_data varchar2(200);
begin
        for i in 1 .. 1e6 loop
                select dname into m_data from dept where rowid='&&1';
end loop;
end ;
/

$ cat wx.sql
select p1,p2,p3,p1raw,p2raw,p3raw,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait,sql_id from v$session where wait_class'Idle' and event not like 'SQL*Net%' order by event ;

2.开始测试:
--session 1:
SCOTT@test> @spid

SID    SERIAL# SPID   C50
---- ---------- ------ --------------------------------------------------
   5        581 20171  alter system kill session '5,581' immediate;

--session 2:
SCOTT@test> @spid

SID    SERIAL# SPID   C50
---- ---------- ------ --------------------------------------------------
   8        395 20181  alter system kill session '8,395' immediate;

--session 1:
SCOTT@test> @h2 1e6 AABJVUAAEAAAAdzAAA

--session 2:
SCOTT@test> @h2 2e6 AABJVUAAEAAAAdzAAA

--session 3:
SCOTT@test> @wx
       P1  P2            P3 P1RAW            P2RAW            P3RAW             SID  SERIAL#  SEQ# EVENT          STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
--------- --- ------------- ---------------- ---------------- ---------------- ---- -------- ----- -------------- ------------------- --------------- --------------- -------------
443009782   1   12884901888 000000001A67CAF6 0000000000000001 0000000300000000    8      395    31 cursor: pin S  WAITED SHORT TIME                 9               6 26jbrb8d6gkrq
443009782   1   38654705664 000000001A67CAF6 0000000000000001 0000000900000000    5      581    51 cursor: pin S  WAITED SHORT TIME                 6               0 26jbrb8d6gkrq

SCOTT@test> @wx
           P1            P2            P3 P1RAW            P2RAW            P3RAW                      SID       SERIAL#          SEQ# EVENT                        STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
------------- ------------- ------------- ---------------- ---------------- ---------------- ------------- ------------- ------------- ---------------------------- ------------------- --------------- --------------- -------------
    443009782             1   12884901888 000000001A67CAF6 0000000000000001 0000000300000000             5           581           138 cursor: pin S                WAITED SHORT TIME                29               8 26jbrb8d6gkrq
    443009782             0   12884901888 000000001A67CAF6 00               0000000300000000             8           395            75 cursor: pin S                WAITED SHORT TIME                 6               2 26jbrb8d6gkrq
            1             1             1 0000000000000001 0000000000000001 0000000000000001           583             1         53495 log file parallel write      WAITING                         420               0
          694    3418088938             0 00000000000002B6 00000000CBBBE5EA 00                         205           332             2 log file sync                WAITED KNOWN TIME              9586               0

SCOTT@test> @wx
           P1            P2            P3 P1RAW            P2RAW            P3RAW                      SID       SERIAL#          SEQ# EVENT                        STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
------------- ------------- ------------- ---------------- ---------------- ---------------- ------------- ------------- ------------- ---------------------------- ------------------- --------------- --------------- -------------
    443009782             0   12884901888 000000001A67CAF6 00               0000000300000000             8           395            79 cursor: pin S                WAITED SHORT TIME                 5               0 26gwzc3vucbbt
    443009782             1   12884901888 000000001A67CAF6 0000000000000001 0000000300000000             5           581           138 cursor: pin S                WAITED SHORT TIME                29              11 26jbrb8d6gkrq

--session 1 执行次数少,在我的测试环境40秒已经完成,可是执行如下,依旧可以看到cursor: pin S等待事件.

SCOTT@test> @wx
           P1            P2            P3 P1RAW            P2RAW            P3RAW                      SID       SERIAL#          SEQ# EVENT                        STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
------------- ------------- ------------- ---------------- ---------------- ---------------- ------------- ------------- ------------- ---------------------------- ------------------- --------------- --------------- -------------
    443009782             0   12884901888 000000001A67CAF6 00               0000000300000000             8           395            79 cursor: pin S                WAITED SHORT TIME                 5               1 26gwzc3vucbbt

SCOTT@test> @wx
           P1            P2            P3 P1RAW            P2RAW            P3RAW                      SID       SERIAL#          SEQ# EVENT                        STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
------------- ------------- ------------- ---------------- ---------------- ---------------- ------------- ------------- ------------- ---------------------------- ------------------- --------------- --------------- -------------
    443009782             0   12884901888 000000001A67CAF6 00               0000000300000000             8           395            79 cursor: pin S                WAITED SHORT TIME                 5               3 26gwzc3vucbbt

SCOTT@test> @wx
           P1            P2            P3 P1RAW            P2RAW            P3RAW                      SID       SERIAL#          SEQ# EVENT                        STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
------------- ------------- ------------- ---------------- ---------------- ---------------- ------------- ------------- ------------- ---------------------------- ------------------- --------------- --------------- -------------
    443009782             0   12884901888 000000001A67CAF6 00               0000000300000000             8           395            79 cursor: pin S                WAITED SHORT TIME                 5               4 26jbrb8d6gkrq

SCOTT@test> @wx
           P1            P2            P3 P1RAW            P2RAW            P3RAW                      SID       SERIAL#          SEQ# EVENT                        STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
------------- ------------- ------------- ---------------- ---------------- ---------------- ------------- ------------- ------------- ---------------------------- ------------------- --------------- --------------- -------------
    443009782             0   12884901888 000000001A67CAF6 00               0000000300000000             8           395            79 cursor: pin S                WAITED SHORT TIME                 5               8 26gwzc3vucbbt

SCOTT@test> @wx
           P1            P2            P3 P1RAW            P2RAW            P3RAW                      SID       SERIAL#          SEQ# EVENT                        STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
------------- ------------- ------------- ---------------- ---------------- ---------------- ------------- ------------- ------------- ---------------------------- ------------------- --------------- --------------- -------------
    443009782             0   12884901888 000000001A67CAF6 00               0000000300000000             8           395            79 cursor: pin S                WAITED SHORT TIME                 5              10 26jbrb8d6gkrq

SCOTT@test> @wx
           P1            P2            P3 P1RAW            P2RAW            P3RAW                      SID       SERIAL#          SEQ# EVENT                        STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
------------- ------------- ------------- ---------------- ---------------- ---------------- ------------- ------------- ------------- ---------------------------- ------------------- --------------- --------------- -------------
    443009782             0   12884901888 000000001A67CAF6 00               0000000300000000             8           395            79 cursor: pin S                WAITED SHORT TIME                 5              11 26jbrb8d6gkrq

SCOTT@test> @wx
           P1            P2            P3 P1RAW            P2RAW            P3RAW                      SID       SERIAL#          SEQ# EVENT                        STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
------------- ------------- ------------- ---------------- ---------------- ---------------- ------------- ------------- ------------- ---------------------------- ------------------- --------------- --------------- -------------
    443009782             0   12884901888 000000001A67CAF6 00               0000000300000000             8           395            79 cursor: pin S                WAITED SHORT TIME                 5              12 26jbrb8d6gkrq

SCOTT@test> @wx
           P1            P2            P3 P1RAW            P2RAW            P3RAW                      SID       SERIAL#          SEQ# EVENT                        STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
------------- ------------- ------------- ---------------- ---------------- ---------------- ------------- ------------- ------------- ---------------------------- ------------------- --------------- --------------- -------------
    443009782             0   12884901888 000000001A67CAF6 00               0000000300000000             8           395            79 cursor: pin S                WAITED SHORT TIME                 5              14 26jbrb8d6gkrq

SCOTT@test> @wx
           P1            P2            P3 P1RAW            P2RAW            P3RAW                      SID       SERIAL#          SEQ# EVENT                        STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
------------- ------------- ------------- ---------------- ---------------- ---------------- ------------- ------------- ------------- ---------------------------- ------------------- --------------- --------------- -------------
    443009782             0   12884901888 000000001A67CAF6 00               0000000300000000             8           395            79 cursor: pin S                WAITED SHORT TIME                 5              15

--等待session 2 完成后,再执行:
SCOTT@test> @wx
no rows selected

--可以发现要注意state列的值,如果waiting,表示会话正在等待,如果不是,说明当前会话已经不再等待某个事件.这个在以后工作中要注
--意.另外观察seq#是否变化也可以确定问题.

--另外注意我访问的是同一块,并没有出现latch: cache buffers chains等待事件,主要要执行会话太少,再增加几个可以再现.我打开4个
--才偶尔看到:
SCOTT@test> @wx
        P1            P2            P3 P1RAW            P2RAW            P3RAW             SID  SERIAL#  SEQ# EVENT                       STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
---------- ------------- ------------- ---------------- ---------------- ---------------- ---- -------- ----- --------------------------- ------------------- --------------- --------------- -------------
443009782             2   38654705664 000000001A67CAF6 0000000000000002 0000000900000000    8      395   181 cursor: pin S               WAITED SHORT TIME                 6               0 26jbrb8d6gkrq
443009782             2   38654705664 000000001A67CAF6 0000000000000002 0000000900000000  597      635   164 cursor: pin S               WAITED SHORT TIME                 5               0 26jbrb8d6gkrq
443009782 2564095475713   12884901888 000000001A67CAF6 0000025500000001 0000000300000000   12      107   254 cursor: pin S               WAITED SHORT TIME                 9               1 26jbrb8d6gkrq
3164418536           155             0 00000000BC9D31E8 000000000000009B 00                  5      581   303 latch: cache buffers chains WAITED SHORT TIME               584               1 26jbrb8d6gkrq

--如果换成如下呢?

--session 1:
SCOTT@test> @h2 2e6 AABJVUAAEAAAAdzAAB

--session 2:
SCOTT@test> @h2 2e6 AABJVUAAEAAAAdzAAA

--我的测试偶尔会出现
SCOTT@test> @wx
        P1  P2   P3 P1RAW            P2RAW            P3RAW  SID       SERIAL#          SEQ# EVENT                                    STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT SQL_ID
---------- --- ---- ---------------- ---------------- ------ --- ------------- ------------- ---------------------------------------- ------------------- --------------- --------------- -------------
3164418536 155    0 00000000BC9D31E8 000000000000009B 00       8           395           376 latch: cache buffers chains              WAITED SHORT TIME               343              51 26jbrb8d6gkrq
3164418536 155    0 00000000BC9D31E8 000000000000009B 00       5           581           439 latch: cache buffers chains              WAITED SHORT TIME               358              51 95sakskyugumy

SCOTT@test> select * from v$event_name where name='latch: cache buffers chains';
       EVENT#      EVENT_ID NAME                 PARAMETER1           PARAMETER2           PARAMETER3           WAIT_CLASS_ID   WAIT_CLASS# WAIT_CLASS
------------- ------------- -------------------- -------------------- -------------------- -------------------- ------------- ------------- --------------------
           87    2779959231 latch: cache buffers address              number               tries                   3875070507             4 Concurrency
                             chains

SCOTT@test> @lookup_rowid AABJVUAAEAAAAdzAAA
       OBJECT          FILE         BLOCK           ROW DBA                  TEXT
------------- ------------- ------------- ------------- -------------------- ----------------------------------------
       300372             4          1907             0 4,1907               alter system dump datafile 4 block 1907

SCOTT@test> @bh 4 1907
HLADDR                 DBARFIL        DBABLK         CLASS CLASS_TYPE         STATE                TCH    CR_SCN_BAS    CR_SCN_WRP    CR_UBA_FIL    CR_UBA_BLK    CR_UBA_SEQ BA               OBJECT_NAME
---------------- ------------- ------------- ------------- ------------------ ---------- ------------- ------------- ------------- ------------- ------------- ------------- ---------------- --------------------
00000000BC9D31E8             4          1907             1 data block         xcur                 123             0             0             0             0             0 000000009ABB8000 DEPT

--HLADDR 对应上面的地址P1RAW=00000000BC9D31E8.

时间： 2024-10-08 17:47:24

[20150326]关于等待事件的注意问题.txt的相关文章

[20161208]等待事件enq: HW - contention

[20161208]等待事件enq HW - contention.txt --别人的系统遭遇enq: HW - contention,自己诊断遇到一点点小误区,实际上我看看我原来的帖子就知道问题在那里了 --链接:http://blog.itpub.net/267265/viewspace-1107524/ SELECT event_id       ,name       ,parameter1       ,parameter2       ,parameter3   FROM v$eve

[20140311]等待事件enq HW - contention

[20140311]等待事件enq HW - contention.txt 生产系统业务高峰时出现enq: HW - contention,一般这个主要是插入记录非常密集的情况下出现,自己对系统分析看看主要是那些对象 引起的问题. SQL> @ver BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.

[20161228]奇怪log file sync等待事件.txt

[20161228]奇怪log file sync等待事件.txt --这个来自链接:http://www.itpub.net/thread-2073857-1-1.html的讨论,很奇怪的问题: Top 10 Foreground Events by Total Wait Time Event                                              Waits    Total Wait Time (sec)    Wait Avg(ms)    % DB t

[20150520]使用gdb查看等待事件.txt

[20150520]使用gdb查看等待事件.txt -- 昨天开始重看vage-- 使用gdb 看等待事件这部分内容跳过了,今天测试看看.如何操作. -- 实际上设置断点在gdb下,11g等待事件的起始函数是kslwtbctx函数.还是通过演示来说明: 1.测试环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------

[20160205]大量子光标引起的等待事件.txt

[20160205]大量子光标引起的等待事件.txt --测试大量子光标引起的等待事件(11G): 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -----------------------------------------------------------------------

[20130228]等待事件library cache pin的快速定位与解决.txt

[20130228]等待事件library cache pin的快速定位与解决.txt 前几天管理的服务器出现library cache pin,当时解决有点乱了阵脚,正好下午空闲做一个例子来定位library cache pin事件以及解决方法,另外我也看许多blog,感觉定位太复杂,不合适快速解决问题: 1.环境以及问题再现: SQL> select * from v$version where rownumBANNER------------------------------------

[20130409]Data file init write等待事件.txt

[20130409]Data file init write等待事件.txt 清明前几天帮别人解决数据库问题,就是高峰的时候有点慢. 仔细看awr报表,除了发现几条sql语句没有建立索引外,发现等待事件里Data file init write.很明显主要数据文件next设置太小,当我看数据文件发现next竟然是8k,将它修改128M后问题消失. 我自己感到奇怪的是无论如何前面的安装人员都不应该把next设置为8k(难道是笔误应该是8M还有可能). 今天自己在11G上做一个测试,原来问题在于建立

[20130904]等待事件wait for a undo record模拟.txt

[20130904]等待事件wait for a undo record模拟.txt 模拟等待事件wait for a undo record. 1.测试环境: SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bi

Statspack之十四-"log file sync" 等待事件

原文出处: http://www.eygle.com/statspack/statspack14-LogFileSync.htm 当一个用户提交(commits)或者回滚(rollback),session的redo信息需要写出到redo logfile中.用户进程将通知LGWR执行写出操作,LGWR完成任务以后会通知用户进程.这个等待事件就是指用户进程等待LGWR的写完成通知. 对于回滚操作,该事件记录从用户发出rollback命令到回滚完成的时间. 如果该等待过多,可能说明LGWR的写出效率