今天处理客户数据库问题的时候，发现该数据库有大量的read by other session等待事件，借此记录下。
首先根据文档，这个事件的解释如下：
When informationis requested from the database, Oracle will first read the data from disk intothe database buffer cache. If two or more sessions request the sameinformation, the first session will read the data into the buffer cache whileother sessions wait. In previous versions this wait was classified under the"buffer busy waits" event. However, in Oracle 10.1 and higher thiswait time is now broken out into the "read by other session" waitevent. Excessive waits for this event are typically due to several processesrepeatedly reading the same blocks, e.g. many sessions scanning the same indexor performing full table scans on the same table. Tuning this issue is a matterof finding and eliminating this contention.
当请求一个数据时，如果buffer cache没有该数据时，Oracle会从磁盘将数据读入buffer cache。
如果有两个或者多个session 请求相同的信息，那么第一个session 会将这个信息读入buffer cache，其他的session 就会出现等待。
在10.1之前，该等待事件归类为bufferbusy waits，在Oracle 10.1之后，单独将该事件拿出并命令为read by other session。
一般来说出现这种等待事件是因为多个进程重复的读取相同的blocks，比如一些session 扫描相同的index或者在相同的block上执行full table scan。
解决这个等待事件最好是找到并优化相关的SQL语句。
read by other session 等待的出现也说明数据库存在热块问题，所以该等待事件通常会有db file sequential read或db file scattered read 同时出现。
来看看今天遇到的情况：
在查看数据库等待事件的时候，发现有大量的read other session和db file scattered read事件
select sid,
serial#,
username,
status,
machine,
program,
sql_hash_value,
sql_id,
to_char(logon_time,'yyyy-mm-dd hh24:mi:ss'),
event,
p1,
p2
from v$session
where event in ('read by other session', 'db file scattered read');
SID SERIAL# USERNAME STATUS MACHINE PROGRAM SQL_HASH_VALUE SQL_ID TO_CHAR(LOGON_TIME,'YYYY-MM-DD EVENT P1 P2
---------- ---------- ----------- -------- --------------- ----------------------------- -------------- ------------- ------------------------------ ----------------------------- ---------- ----------
347 822 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:44:20 read by other session 9 103885
348 1558 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:34:45 read by other session 9 171789
349 1879 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:17:03 db file scattered read 9 610910
350 1241 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:46:19 read by other session 9 103885
351 1067 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:50 db file scattered read 9 103885
352 3296 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:35:19 read by other session 9 171789
353 391 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:21:49 db file scattered read 9 441841
354 616 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:23:18 db file scattered read 9 391294
355 681 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:25:13 read by other session 9 279803
356 712 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:33:17 read by other session 9 171789
357 510 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:45:17 read by other session 9 103885
358 1625 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:30:38 read by other session 9 171789
359 956 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:20:13 db file scattered read 9 568327
360 837 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:16:22 db file scattered read 9 624695
364 10488 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:17:30 db file scattered read 9 590289
370 3993 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:20:37 db file scattered read 9 451617
376 3258 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:49:47 db file scattered read 9 69053
377 4779 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:44:12 read by other session 9 103885
380 21129 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:15:39 db file scattered read 9 668803
381 2716 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:34:07 read by other session 9 171789
385 1617 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:27:59 read by other session 9 171789
389 1681 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:16:07 db file scattered read 9 643364
391 6657 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:26:49 db file scattered read 9 234271
392 43902 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:30:23 read by other session 9 171789
395 7003 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:15:15 db file scattered read 9 719929
398 5246 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:33:25 read by other session 9 171789
399 9635 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:15:39 db file scattered read 9 672689
410 5588 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:17:05 db file scattered read 9 604003
412 980 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:31:20 read by other session 9 171789
415 4591 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:20:13 db file scattered read 9 556541
416 6649 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:33:33 read by other session 9 171789
418 21354 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:27:21 db file scattered read 9 183001
419 43412 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:17:26 db file scattered read 9 592652
420 3669 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:19:26 db file scattered read 9 581417
423 2586 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:33:25 read by other session 9 171789
430 27397 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:31:50 read by other session 9 171789
432 7621 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:19:39 db file scattered read 9 534361
435 3845 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:29:16 read by other session 9 171789
439 3293 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:09 read by other session 9 103885
440 16800 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:43:40 read by other session 9 103885
446 18697 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:18:18 db file scattered read 9 584413
451 4734 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:45:40 read by other session 9 103885
452 2594 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:27:05 db file scattered read 9 210845
453 9508 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:16:22 db file scattered read 9 615554
458 6139 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:44:31 read by other session 9 103885
460 18012 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:44:08 read by other session 9 103885
461 3224 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:14:26 db file scattered read 9 724229
465 4371 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:34:18 read by other session 9 171789
466 4650 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:20:22 read by other session 9 534361
468 8735 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:25:22 db file scattered read 9 268194
469 14362 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:27:05 db file scattered read 9 202463
471 7194 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:07 read by other session 9 103885
474 2533 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:25:37 db file scattered read 9 248817
478 3757 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:46:43 read by other session 9 103885
485 4438 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:26:28 db file scattered read 9 236349
488 7859 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:26:19 read by other session 9 239613
489 8827 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:45:17 read by other session 9 103885
491 6996 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:24:16 db file scattered read 9 293921
492 21059 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:51:42 read by other session 9 69053
493 7681 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:30:42 read by other session 9 171789
497 4636 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:28:42 read by other session 9 171789
500 4062 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:48:06 read by other session 9 103885
501 9340 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:26:19 db file scattered read 9 239613
504 9746 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:33:57 read by other session 9 171789
506 4258 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:17:05 db file scattered read 9 605955
507 3436 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:17:59 db file scattered read 9 584829
509 3990 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:15:37 db file scattered read 9 689377
511 5592 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:48:47 read by other session 9 69053
515 2380 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:33:11 read by other session 9 171789
516 5049 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:23:42 db file scattered read 9 375192
517 11081 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:16:49 read by other session 9 612067
518 19019 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:19:16 db file scattered read 9 582651
519 2947 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:03 read by other session 9 103885
520 44555 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:19:39 db file scattered read 9 573202
523 32606 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:43:40 read by other session 9 103885
524 14352 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:25:40 read by other session 9 248817
525 49196 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:33:43 read by other session 9 171789
526 2815 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:22:03 db file scattered read 9 432421
527 7558 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:20:40 db file scattered read 9 447233
528 19934 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:18:42 db file scattered read 9 583660
532 12000 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:15:23 db file scattered read 9 703321
535 19291 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:15:40 db file scattered read 9 651921
537 5808 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:16:28 db file scattered read 9 612067
539 5824 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:08 read by other session 9 103885
542 22756 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:08 read by other session 9 103885
543 28090 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:21:19 db file scattered read 9 446657
546 25606 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:21:19 db file scattered read 9 443777
547 21390 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:31:28 read by other session 9 171789
548 31131 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:22:59 db file scattered read 9 392686
557 20900 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:22:05 db file scattered read 9 420589
558 7787 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:21:28 db file scattered read 9 442321
560 49814 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:45:14 read by other session 9 103885
561 33975 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:28:18 read by other session 9 171789
603 708 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:33:50 read by other session 9 171789
645 24200 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:22:05 db file scattered read 9 419926
663 8601 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:23:42 db file scattered read 9 376348
669 42540 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:08 read by other session 9 103885
670 355 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:31:20 db file scattered read 9 171789
671 5023 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:33:57 read by other session 9 171789
676 572 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:14:39 db file scattered read 9 723209
677 5142 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:16:26 db file scattered read 9 615415
687 8508 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:15:15 db file scattered read 9 711833
688 565 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:32:03 read by other session 9 171993
700 20809 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:25:13 db file scattered read 9 279835
702 3931 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:33:21 read by other session 9 171993
705 3521 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:16:00 db file scattered read 9 649425
772 1005 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:27:21 db file scattered read 9 189930
797 1081 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:47:35 read by other session 9 104045
806 4809 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:18:42 read by other session 9 584461
819 32486 SYH123 ACTIVE LENOVO-EWJS4146 300319264 187mwcn8yd0j0 2013-02-01 11:28:43 read by other session 9 171993
839 26168 SYH123 ACTIVE LENOVO-EWJS4146 2307979633 bj3rk3a4t1ybj 2013-02-01 11:27:03 db file scattered read 9 232669进一步查看read other session和db file scattered read事件相关语句的SQL_ID
SQL> select distinct sql_id
2 from v$session
3 where event in ('read by other session', 'db file scattered read');
SQL_ID
-------------
bj3rk3a4t1ybj
187mwcn8yd0j0发现所有的read other session和db file scattered read事件都集中在上面两个SQL语句上。
在查看read other session的参数情况：
SQL> select name,parameter1,parameter2,wait_class from v$event_name where name='read by other session';
NAME PARAMETER1 PARAMETER2 WAIT_CLASS
------------------------------ ---------- ---------- --------------------
read by other session file# block# User I/O
SQL> 可以看p1代表了file#，p2代表block#
查看这些read other session所访问的对象
SQL> select t.owner,t.segment_name,t.segment_type from dba_extents t where t.file_id = 9 and 103885 between t.block_id and t.block_id+t.blocks
2 /
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ ---------------------------------------- ------------------
SCPEND TF_TASK_TASKINFOHISTB TABLE
SQL>
SQL>
SQL> select t.owner, t.segment_name, t.segment_type
2 from dba_extents t
3 where t.file_id = 9
4 and 69053 between t.block_id and t.block_id + t.blocks;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ ---------------------------------------- ------------------
SCPEND TF_TASK_TASKINFOHISTB TABLE
SQL>
SQL>
SQL> select t.owner, t.segment_name, t.segment_type
2 from dba_extents t
3 where t.file_id = 9
4 and 590289 between t.block_id and t.block_id + t.blocks;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ ---------------------------------------- ------------------
SCPEND TF_TASK_TASKINFOHISTB TABLE发现都是访问的同一个表。
再来看看该SQL的执行计划：
可以看到相关SQL确实在对该表做全表扫描的操作，耗时达到22分钟。
后续操作就是对该SQL进行优化
4274
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
