[20191119]测试dbms_system.wait_for_event.txt

[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. --//不可能是这个对象.