[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

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

--等待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.

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

热门文章

最新文章

相关电子书