[20191119]测试dbms_system.wait_for_event.txt

--//测试看看dbms_system.wait_for_event的延迟.
1.环境:

SYS@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> @ desc_proc sys dbms_system wait_for_event
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats

OWNER      PACKAGE_NAME         OBJECT_NAME                      SEQUENCE ARGUMENT_NAME        DATA_TYPE            IN_OUT    DEFAULTED
---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- ----------
SYS        DBMS_SYSTEM          WAIT_FOR_EVENT                          1 EVENT                VARCHAR2             IN        N
                                                                        2 EXTENDED_ID          BINARY_INTEGER       IN        N
                                                                        3 TIMEOUT              BINARY_INTEGER       IN        N

2.测试:
SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> execute dbms_system.wait_for_event('db file scattered read',1,1);
BEGIN dbms_system.wait_for_event('db file scattered read',1,1); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SYSTEM.WAIT_FOR_EVENT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

--//scott用户权限不够.换成sys用户测试看看.

alter system flush buffer_cache;
execute dbms_system.wait_for_event('db file scattered read',1,1);

SYS@book> select * from scott.dept;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
Elapsed: 00:00:00.00
--//恩并没有效果,测试有问题.还是理解错误.

3.看一下文档,应该这么测试:

--//实际上我的理解有误.
SYS@book> set timing on
SYS@book> execute dbms_system.wait_for_event('db file scattered read',1,1);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.00

SYS@book> execute dbms_system.wait_for_event('db file scattered read',1,2);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.00

SYS@book> execute dbms_system.wait_for_event('db file scattered read',2,2);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.00

SYS@book> execute dbms_system.wait_for_event('db file scattered read',3,2);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.01

--//也就是直接模拟db file scattered read,输入参数EXTENDED_ID表示什么不懂.

4.跟踪看看:
SYS@book> @ 10046on 12
old   1: alter session set events '10046 trace name context forever, level &1'
new   1: alter session set events '10046 trace name context forever, level 12'
Session altered.
Elapsed: 00:00:00.00

SYS@book> execute dbms_system.wait_for_event('db file scattered read',0,1);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.00

SYS@book> @ 10046off
Session altered.
Elapsed: 00:00:00.00

*** 2019-11-19 16:59:01.373
WAIT #139830919647368: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=5541 tim=1574153941373299

*** 2019-11-19 16:59:07.563
WAIT #139830919647368: nam='SQL*Net message from client' ela= 6190131 driver id=1650815232 #bytes=1 p3=0 obj#=5541 tim=1574153947563516
CLOSE #139830919647368:c=0,e=26,dep=0,type=1,tim=1574153947563638
=====================
PARSING IN CURSOR #139830919639928 len=70 dep=0 uid=0 oct=47 lid=0 tim=1574153947565709 hv=1754337657 ad='7c356e60' sqlid='fpqb1vxn924bt'
BEGIN dbms_system.wait_for_event('db file scattered read',0,1); END;
END OF STMT
PARSE #139830919639928:c=1999,e=2009,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1574153947565701
WAIT #139830919639928: nam='Disk file operations I/O' ela= 50 FileOperation=8 fileno=0 filetype=8 obj#=5541 tim=1574153947565866

*** 2019-11-19 16:59:08.566
WAIT #139830919639928: nam='db file scattered read' ela= 1000425 file#=0 block#=0 blocks=0 obj#=5541 tim=1574153948566430
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//奇怪file#=0 block#=0 blocks=0 obj#=5541

EXEC #139830919639928:c=0,e=1000606,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1574153948566524
WAIT #139830919639928: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=5541 tim=1574153948566599

*** 2019-11-19 16:59:12.524
WAIT #139830919639928: nam='SQL*Net message from client' ela= 3957845 driver id=1650815232 #bytes=1 p3=0 obj#=5541 tim=1574153952524478
CLOSE #139830919639928:c=0,e=24,dep=0,type=0,tim=1574153952524589
=====================
PARSING IN CURSOR #139830919639928 len=55 dep=0 uid=0 oct=42 lid=0 tim=1574153952524789 hv=2217940283 ad='0' sqlid='06nvwn223659v'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #139830919639928:c=0,e=138,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1574153952524788
WAIT #139830919639928: nam='Disk file operations I/O' ela= 47 FileOperation=8 fileno=0 filetype=8 obj#=5541 tim=1574153952524924
EXEC #139830919639928:c=0,e=382,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1574153952525351

SYS@book> select * from dba_objects where object_id=5541
  2  @ prxx
==============================
OWNER                         : SYS
OBJECT_NAME                   : DAM_CONFIG_PARAM$
SUBOBJECT_NAME                :
OBJECT_ID                     : 5541
DATA_OBJECT_ID                : 5541
OBJECT_TYPE                   : TABLE
CREATED                       : 2013-08-24 11:38:59
LAST_DDL_TIME                 : 2013-08-24 11:38:59
TIMESTAMP                     : 2013-08-24:11:38:59
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
NAMESPACE                     : 1
EDITION_NAME                  :
PL/SQL procedure successfully completed.

--//不可能是这个对象.