[20150520]使用gdb查看等待事件.txt
-- 昨天开始重看vage
-- 使用gdb 看等待事件这部分内容跳过了,今天测试看看.如何操作.
-- 实际上设置断点在gdb下,11g等待事件的起始函数是kslwtbctx函数.还是通过演示来说明:
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> 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
AABJVUAAEAAAAd3AAA 50 MARKETING LONDON
--刷新数据缓存
SCOTT@test> alter system flush buffer_cache ;
System altered.
SCOTT@test> @spid
SID SERIAL# SPID C50
---------- ---------- ------ --------------------------------------------------
592 6201 22532 alter system kill session '592,6201' immediate;
2.开始测试:
--看看简单的select * from dept where rowid='AABJVUAAEAAAAdzAAA';会出现什么等待事件.注意这里仅仅可以看到主动登记的等待事件(按照vage的说法).
$ gdb `which oracle` 22532
gdb `which oracle` 22532
GNU gdb Red Hat Linux (6.3.0.0-1.96rh)
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB. Type "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu"...(no debugging symbols found)
Using host libthread_db library "/lib64/tls/libthread_db.so.1".
Attaching to program: /u01/app/oracle11g/product/11.2.0/db_2/bin/oracle, process 22532
Reading symbols from /u01/app/oracle11g/product/11.2.0/db_2/lib/libodm11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle11g/product/11.2.0/db_2/lib/libodm11.so
Reading symbols from /u01/app/oracle11g/product/11.2.0/db_2/lib/libcell11.so...done.
Loaded symbols for /u01/app/oracle11g/product/11.2.0/db_2/lib/libcell11.so
Reading symbols from /u01/app/oracle11g/product/11.2.0/db_2/lib/libskgxp11.so...done.
Loaded symbols for /u01/app/oracle11g/product/11.2.0/db_2/lib/libskgxp11.so
Reading symbols from /lib64/tls/librt.so.1...done.
Loaded symbols for /lib64/tls/librt.so.1
Reading symbols from /u01/app/oracle11g/product/11.2.0/db_2/lib/libnnz11.so...done.
Loaded symbols for /u01/app/oracle11g/product/11.2.0/db_2/lib/libnnz11.so
Reading symbols from /u01/app/oracle11g/product/11.2.0/db_2/lib/libclsra11.so...done.
Loaded symbols for /u01/app/oracle11g/product/11.2.0/db_2/lib/libclsra11.so
Reading symbols from /u01/app/oracle11g/product/11.2.0/db_2/lib/libdbcfg11.so...done.
Loaded symbols for /u01/app/oracle11g/product/11.2.0/db_2/lib/libdbcfg11.so
Reading symbols from /u01/app/oracle11g/product/11.2.0/db_2/lib/libhasgen11.so...done.
Loaded symbols for /u01/app/oracle11g/product/11.2.0/db_2/lib/libhasgen11.so
Reading symbols from /u01/app/oracle11g/product/11.2.0/db_2/lib/libskgxn2.so...done.
Loaded symbols for /u01/app/oracle11g/product/11.2.0/db_2/lib/libskgxn2.so
Reading symbols from /u01/app/oracle11g/product/11.2.0/db_2/lib/libocr11.so...done.
Loaded symbols for /u01/app/oracle11g/product/11.2.0/db_2/lib/libocr11.so
Reading symbols from /u01/app/oracle11g/product/11.2.0/db_2/lib/libocrb11.so...done.
Loaded symbols for /u01/app/oracle11g/product/11.2.0/db_2/lib/libocrb11.so
Reading symbols from /u01/app/oracle11g/product/11.2.0/db_2/lib/libocrutl11.so...done.
Loaded symbols for /u01/app/oracle11g/product/11.2.0/db_2/lib/libocrutl11.so
Reading symbols from /usr/lib64/libaio.so.1...done.
Loaded symbols for /usr/lib64/libaio.so.1
Reading symbols from /lib64/libdl.so.2...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/tls/libm.so.6...done.
Loaded symbols for /lib64/tls/libm.so.6
Reading symbols from /lib64/tls/libpthread.so.0...done.
[Thread debugging using libthread_db enabled]
[New Thread 182919881152 (LWP 22532)]
Loaded symbols for /lib64/tls/libpthread.so.0
Reading symbols from /lib64/libnsl.so.1...done.
Loaded symbols for /lib64/libnsl.so.1
Reading symbols from /lib64/tls/libc.so.6...done.
Loaded symbols for /lib64/tls/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /usr/lib64/libnuma.so...done.
Loaded symbols for /usr/lib64/libnuma.so
Reading symbols from /lib64/libnss_files.so.2...done.
Loaded symbols for /lib64/libnss_files.so.2
Reading symbols from /u01/app/oracle11g/product/11.2.0/db_2/lib/libnque11.so...done.
Loaded symbols for /u01/app/oracle11g/product/11.2.0/db_2/lib/libnque11.so
0x0000003b6530b222 in __read_nocancel () from /lib64/tls/libpthread.so.0
(gdb) b kslwtbctx
Breakpoint 1 at 0x8f7b192
--回到session 2:
SCOTT@test> select * from v$session_event where sid=592;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
592 Disk file operations I/O 2 0 0 .03 0 648 166678035 1740759767 8 User I/O
592 log file sync 1 0 0 .25 0 2489 1328744198 3386400367 5 Commit
592 db file sequential read 6 0 0 0 0 149 2652584166 1740759767 8 User I/O
592 SQL*Net message to client 14 0 0 0 0 27 2067390145 2000153315 7 Network
592 SQL*Net message from client 13 0 506 38.94 506 5061779 1421975091 2723168908 6 Idle
--回到session 1:
SCOTT@test> select * from dept where rowid='AABJVUAAEAAAAdzAAA';
---挂起!
(gdb) c
Continuing.
[Switching to Thread 182919881152 (LWP 22532)]
Breakpoint 1, 0x0000000008f7b192 in kslwtbctx ()
(gdb) info register
rax 0x62657100 1650815232
rbx 0x15c 348
rcx 0x0 0
rdx 0x1 1
rsi 0x7a59 31321
rdi 0x7fbfff9f10 548682047248
rbp 0x7fbfff9eb0 0x7fbfff9eb0
rsp 0x7fbfff9eb0 0x7fbfff9eb0
r8 0x2 2
r9 0x1e 30
r10 0x7 7
r11 0x14e 334
r12 0x0 0
r13 0xb9ed328 194958120
r14 0xb9ead20 194948384
r15 0x1 1
rip 0x8f7b192 0x8f7b192
eflags 0x246 582
cs 0x33 51
ss 0x2b 43
ds 0x0 0
es 0x0 0
fs 0x0 0
gs 0x0 0
--查询rdi 信息:rdi 0x7fbfff9f10 548682047248
(gdb) x /32 0x7fbfff9f10
0x7fbfff9f10: 0xbfff7a59 0x0000007f 0xbfffa020 0x0000007f
0x7fbfff9f20: 0xbfffa0b0 0x0000007f 0x0916b9c2 0x00000000
0x7fbfff9f30: 0x00000000 0x00000000 0x00000000 0x00000000
0x7fbfff9f40: 0x00000000 0x00000001 0x00000000 0x00000000
0x7fbfff9f50: 0xf47de243 0x00000002 0x00000000 0x00000000
0x7fbfff9f60: 0x00000000 0x00000000 0x00000001 0x00000000
0x7fbfff9f70: 0x09b764ac 0x00000000 0x0000015c 0x00000000
~~~~~~~~~~
0x7fbfff9f80: 0x7fffffff 0x00000000 0x62657100 0x00000000
-- 查看~~处,这个是等待事件编码.后面不在注明.
SCOTT@test> select event#,name from v$event_name where event#=to_number('15c','xxxx');
EVENT# NAME
---------- ----------------------------------------
348 SQL*Net message to client
--首先是SQL*Net message to client.
SCOTT@test> select * from v$session_event where sid=592;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
592 Disk file operations I/O 2 0 0 .03 0 648 166678035 1740759767 8 User I/O
592 log file sync 1 0 0 .25 0 2489 1328744198 3386400367 5 Commit
592 db file sequential read 6 0 0 0 0 149 2652584166 1740759767 8 User I/O
592 SQL*Net message to client 14 0 0 0 0 27 2067390145 2000153315 7 Network
592 SQL*Net message from client 14 0 35135 2509.68 34629 351354642 1421975091 2723168908 6 Idle
--继续按C.
SCOTT@test> select * from v$session_event where sid=592;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
592 Disk file operations I/O 2 0 0 .03 0 648 166678035 1740759767 8 User I/O
592 log file sync 1 0 0 .25 0 2489 1328744198 3386400367 5 Commit
592 db file sequential read 6 0 0 0 0 149 2652584166 1740759767 8 User I/O
592 SQL*Net message to client 15 0 0 0 0 35 2067390145 2000153315 7 Network
592 SQL*Net message from client 14 0 35135 2509.68 34629 351354642 1421975091 2723168908 6 Idle
--可以发现SQL*Net message to client 增 1.
(gdb) c
Continuing.
Breakpoint 1, 0x0000000008f7b192 in kslwtbctx ()
(gdb) info register
rax 0x4 4
rbx 0xbba00660 3147826784
rcx 0x2 2
rdx 0x0 0
rsi 0x7a59 31321
rdi 0x7fbfff8740 548682041152
rbp 0x7fbfff8730 0x7fbfff8730
rsp 0x7fbfff8730 0x7fbfff8730
r8 0xbb9bf998 3147561368
r9 0x0 0
r10 0xbfbc0628 3216770600
r11 0x0 0
r12 0x0 0
r13 0x0 0
r14 0x0 0
r15 0x0 0
rip 0x8f7b192 0x8f7b192
eflags 0x246 582
cs 0x33 51
ss 0x2b 43
ds 0x0 0
es 0x0 0
fs 0x0 0
gs 0x0 0
(gdb) x /32 0x7fbfff8740
0x7fbfff8740: 0x00007a59 0x00000000 0x00000004 0x00000000
0x7fbfff8750: 0xbfff8510 0x0000007f 0x08f8b5e1 0x00000000
0x7fbfff8760: 0x00000000 0x00000000 0x9745f268 0x0000002a
0x7fbfff8770: 0x00000040 0x00000000 0x974369f0 0x0000002a
0x7fbfff8780: 0x9745f5b8 0x0000002a 0x00000000 0x00000000
0x7fbfff8790: 0x9745f608 0x00000000 0x00000001 0x00000000
0x7fbfff87a0: 0x0a1ca470 0x00000000 0x0000000b 0x00000000
0x7fbfff87b0: 0x7fffffff 0x00000000 0x00000002 0x00000000
SCOTT@test> select event#,name from v$event_name where event#=to_number('0b','xxxx');
EVENT# NAME
---------- ----------------------------------------
11 Disk file operations I/O
--在gdb下继续c:
SCOTT@test> select * from v$session_event where sid=592;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
592 Disk file operations I/O 3 0 0 .03 0 956 166678035 1740759767 8 User I/O
592 log file sync 1 0 0 .25 0 2489 1328744198 3386400367 5 Commit
592 db file sequential read 6 0 0 0 0 149 2652584166 1740759767 8 User I/O
592 SQL*Net message to client 15 0 0 0 0 35 2067390145 2000153315 7 Network
592 SQL*Net message from client 14 0 35135 2509.68 34629 351354642 1421975091 2723168908 6 Idle
--可以发现Disk file operations I/O 增1.
(gdb) c
Continuing.
Breakpoint 1, 0x0000000008f7b192 in kslwtbctx ()
(gdb) info register
rax 0x0 0
rbx 0x2000 8192
rcx 0x0 0
rdx 0x0 0
rsi 0x0 0
rdi 0x7fbfff8968 548682041704
rbp 0x7fbfff86a0 0x7fbfff86a0
rsp 0x7fbfff86a0 0x7fbfff86a0
r8 0x0 0
r9 0xbdc43a40 3183753792
r10 0x7fbfff8968 548682041704
r11 0x246 582
r12 0xbba065d0 3147851216
r13 0x0 0
r14 0x0 0
r15 0xbcf25fe0 3170000864
rip 0x8f7b192 0x8f7b192
eflags 0x246 582
cs 0x33 51
ss 0x2b 43
ds 0x0 0
es 0x0 0
fs 0x0 0
gs 0x0 0
(gdb) x /32 0x7fbfff8968
0x7fbfff8968: 0x08f77a59 0x00000000 0x00000000 0x00000000
0x7fbfff8978: 0x9741abf8 0x0000002a 0x97447a28 0x0000002a
0x7fbfff8988: 0x0b9d4f80 0x00000000 0x0a46ce64 0x00000000
0x7fbfff8998: 0x00000000 0x00000000 0x00000000 0x00000000
0x7fbfff89a8: 0xba528ba0 0x00000000 0x0000001f 0x20000000
0x7fbfff89b8: 0x00000001 0x00000000 0x00000001 0x00000000
0x7fbfff89c8: 0x0a0dd930 0x00000000 0x00000092 0x00000000
0x7fbfff89d8: 0x7fffffff 0x00000000 0x00000004 0x00000000
SCOTT@test> select event#,name from v$event_name where event#=to_number('92','xxxx');
EVENT# NAME
---------- ----------------------------------------
146 db file sequential read
--在gdb下继续c:
SCOTT@test> select * from v$session_event where sid=592;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
592 Disk file operations I/O 3 0 0 .03 0 956 166678035 1740759767 8 User I/O
592 log file sync 1 0 0 .25 0 2489 1328744198 3386400367 5 Commit
592 db file sequential read 7 0 0 0 0 194 2652584166 1740759767 8 User I/O
592 SQL*Net message to client 15 0 0 0 0 35 2067390145 2000153315 7 Network
592 SQL*Net message from client 14 0 35135 2509.68 34629 351354642 1421975091 2723168908 6 Idle
-- db file sequential read 增 6->7.
(gdb) c
Continuing.
Breakpoint 1, 0x0000000008f7b192 in kslwtbctx ()
(gdb) info register
rax 0x62657100 1650815232
rbx 0x160 352
rcx 0x0 0
rdx 0x1 1
rsi 0x7a59 31321
rdi 0x7fbfffb5a0 548682053024
rbp 0x7fbfffb540 0x7fbfffb540
rsp 0x7fbfffb540 0x7fbfffb540
r8 0x2 2
r9 0x1 1
r10 0xbd76ec30 3178687536
r11 0x1 1
r12 0x0 0
r13 0xb9ed328 194958120
r14 0xb9ead20 194948384
r15 0x2 2
rip 0x8f7b192 0x8f7b192
eflags 0x246 582
cs 0x33 51
ss 0x2b 43
ds 0x0 0
es 0x0 0
fs 0x0 0
gs 0x0 0
(gdb) x /32 0x7fbfffb5a0
0x7fbfffb5a0: 0x00007a59 0x00000024 0xf485ed5f 0x0005167a
0x7fbfffb5b0: 0x0929efde 0x0005167b 0xf485ed5f 0x0005167a
0x7fbfffb5c0: 0xf485ed5f 0x0005167a 0x00000000 0x00000000
0x7fbfffb5d0: 0x64690000 0x732e732c 0x61697265 0x702c236c
0x7fbfffb5e0: 0x6970732e 0x61272c64 0x7265746c 0x73797320
0x7fbfffb5f0: 0x206d6574 0x00000000 0x00000001 0x6e6f6973
0x7fbfffb600: 0x09b76430 0x00000000 0x00000160 0x00000000
0x7fbfffb610: 0x7fffffff 0x69726573 0x62657100 0x00000000
SCOTT@test> select event#,name from v$event_name where event#=to_number('160','xxxx');
EVENT# NAME
---------- ----------------------------------------
352 SQL*Net message from client
--在gdb下继续c:
SCOTT@test> select * from v$session_event where sid=592;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
592 Disk file operations I/O 3 0 0 .03 0 956 166678035 1740759767 8 User I/O
592 log file sync 1 0 0 .25 0 2489 1328744198 3386400367 5 Commit
592 db file sequential read 7 0 0 0 0 194 2652584166 1740759767 8 User I/O
592 SQL*Net message to client 15 0 0 0 0 35 2067390145 2000153315 7 Network
592 SQL*Net message from client 15 0 35136 2342.37 34629 351355044 1421975091 2723168908 6 Idle
-- SQL*Net message from client 增 14->15.
(gdb) c
Continuing.
Breakpoint 1, 0x0000000008f7b192 in kslwtbctx ()
(gdb) info register
rax 0x62657100 1650815232
rbx 0x15c 348
rcx 0x0 0
rdx 0x1 1
rsi 0x7a59 31321
rdi 0x7fbfffb5a0 548682053024
rbp 0x7fbfffb540 0x7fbfffb540
rsp 0x7fbfffb540 0x7fbfffb540
r8 0x2 2
r9 0x12 18
r10 0xb9ed328 194958120
r11 0x202 514
r12 0x0 0
r13 0xb9ed328 194958120
r14 0xb9ead20 194948384
r15 0x1 1
rip 0x8f7b192 0x8f7b192
eflags 0x246 582
cs 0x33 51
ss 0x2b 43
ds 0x0 0
es 0x0 0
fs 0x0 0
gs 0x0 0
(gdb) x /32 0x7fbfffb5a0
0x7fbfffb5a0: 0x00007a59 0x00000028 0x2827d0de 0x0005167b
0x7fbfffb5b0: 0x2827d270 0x0005167b 0x2827d0de 0x0005167b
0x7fbfffb5c0: 0x2827d0de 0x0005167b 0x00000000 0x00000000
0x7fbfffb5d0: 0x64690000 0x732e732c 0x61697265 0x702c236c
0x7fbfffb5e0: 0x6970732e 0x61272c64 0x7265746c 0x73797320
0x7fbfffb5f0: 0x206d6574 0x00000000 0x00000001 0x6e6f6973
0x7fbfffb600: 0x09b764ac 0x00000000 0x0000015c 0x00000000
0x7fbfffb610: 0x7fffffff 0x69726573 0x62657100 0x00000000
SCOTT@test> select event#,name from v$event_name where event#=to_number('15c','xxxx');
EVENT# NAME
---------- ----------------------------------------
348 SQL*Net message to client
--在gdb下继续c:
SCOTT@test> select * from v$session_event where sid=592;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
592 Disk file operations I/O 3 0 0 .03 0 956 166678035 1740759767 8 User I/O
592 log file sync 1 0 0 .25 0 2489 1328744198 3386400367 5 Commit
592 db file sequential read 7 0 0 0 0 194 2652584166 1740759767 8 User I/O
592 SQL*Net message to client 16 0 0 0 0 42 2067390145 2000153315 7 Network
592 SQL*Net message from client 15 0 35136 2342.37 34629 351355044 1421975091 2723168908 6 Idle
-- SQL*Net message to client 增 15->16.
(gdb) c
Continuing.
Breakpoint 1, 0x0000000008f7b192 in kslwtbctx ()
(gdb) info register
rax 0x62657100 1650815232
rbx 0x160 352
rcx 0x0 0
rdx 0x1 1
rsi 0x7a59 31321
rdi 0x7fbfffb5a0 548682053024
rbp 0x7fbfffb540 0x7fbfffb540
rsp 0x7fbfffb540 0x7fbfffb540
r8 0x2 2
r9 0x1 1
r10 0xbd76ec30 3178687536
r11 0x1 1
r12 0x0 0
r13 0xb9ed328 194958120
r14 0xb9ead20 194948384
r15 0x2 2
rip 0x8f7b192 0x8f7b192
eflags 0x246 582
cs 0x33 51
ss 0x2b 43
ds 0x0 0
es 0x0 0
fs 0x0 0
gs 0x0 0
(gdb) x /32 0x7fbfffb5a0
0x7fbfffb5a0: 0x00007a59 0x00000029 0x302c8b73 0x0005167b
0x7fbfffb5b0: 0x302c8b7a 0x0005167b 0x302c8b73 0x0005167b
0x7fbfffb5c0: 0x302c8b73 0x0005167b 0x00000000 0x00000000
0x7fbfffb5d0: 0x64690000 0x732e732c 0x61697265 0x702c236c
0x7fbfffb5e0: 0x6970732e 0x61272c64 0x7265746c 0x73797320
0x7fbfffb5f0: 0x206d6574 0x00000000 0x00000001 0x6e6f6973
0x7fbfffb600: 0x09b76430 0x00000000 0x00000160 0x00000000
0x7fbfffb610: 0x7fffffff 0x69726573 0x62657100 0x00000000
SCOTT@test> select event#,name from v$event_name where event#=to_number('160','xxxx');
EVENT# NAME
---------- ----------------------------------------
352 SQL*Net message from client
--在gdb下继续c:
--这个时候会话1结果输出:
SCOTT@test> select * from dept where rowid='AABJVUAAEAAAAdzAAA';
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@test> select * from v$session_event where sid=592;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
592 Disk file operations I/O 3 0 0 .03 0 956 166678035 1740759767 8 User I/O
592 log file sync 1 0 0 .25 0 2489 1328744198 3386400367 5 Commit
592 db file sequential read 7 0 0 0 0 194 2652584166 1740759767 8 User I/O
592 SQL*Net message to client 16 0 0 0 0 42 2067390145 2000153315 7 Network
592 SQL*Net message from client 15 0 35136 2342.37 34629 351355044 1421975091 2723168908 6 Idle
--对比前面第1次的情况:
SCOTT@test> select * from v$session_event where sid=592;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
592 Disk file operations I/O 2 0 0 .03 0 648 166678035 1740759767 8 User I/O
592 log file sync 1 0 0 .25 0 2489 1328744198 3386400367 5 Commit
592 db file sequential read 6 0 0 0 0 149 2652584166 1740759767 8 User I/O
592 SQL*Net message to client 14 0 0 0 0 27 2067390145 2000153315 7 Network
592 SQL*Net message from client 13 0 506 38.94 506 5061779 1421975091 2723168908 6 Idle
--可以看出在一个块没有在内存的情况下,从磁盘读取经历如下等待事件:
SQL*Net message to client
Disk file operations I/O
db file sequential read
SQL*Net message from client
SQL*Net message to client
SQL*Net message from client