read by other session等待事件

最新推荐文章于 2019-06-26 16:54:00 发布
最新推荐文章于 2019-06-26 16:54:00 发布
阅读量892 收藏
点赞数
分类专栏： Oracle 等待事件

今天处理客户数据库问题的时候，发现该数据库有大量的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事件

[plain] view plain copy
  1. select sid,  
  2.        serial#,  
  3.        username,  
  4.        status,  
  5.        machine,  
  6.        program,  
  7.        sql_hash_value,  
  8.        sql_id,  
  9.        to_char(logon_time,'yyyy-mm-dd hh24:mi:ss'),  
  10.        event,  
  11.        p1,  
  12.        p2  
  13.   from v$session  
  14.  where event in ('read by other session', 'db file scattered read');  
  15.   
  16.   
  17.   
  18.   
  19.    
  20.        SID    SERIAL# USERNAME    STATUS   MACHINE         PROGRAM                       SQL_HASH_VALUE SQL_ID        TO_CHAR(LOGON_TIME,'YYYY-MM-DD EVENT                                 P1         P2  
  21. ---------- ---------- ----------- -------- --------------- ----------------------------- -------------- ------------- ------------------------------ ----------------------------- ---------- ----------  
  22.        347        822 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:44:20            read by other session                  9     103885  
  23.        348       1558 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:34:45            read by other session                  9     171789  
  24.        349       1879 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:17:03            db file scattered read                 9     610910  
  25.        350       1241 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:46:19            read by other session                  9     103885  
  26.        351       1067 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:50            db file scattered read                 9     103885  
  27.        352       3296 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:35:19            read by other session                  9     171789  
  28.        353        391 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:21:49            db file scattered read                 9     441841  
  29.        354        616 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:23:18            db file scattered read                 9     391294  
  30.        355        681 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:25:13            read by other session                  9     279803  
  31.        356        712 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:33:17            read by other session                  9     171789  
  32.        357        510 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:45:17            read by other session                  9     103885  
  33.        358       1625 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:30:38            read by other session                  9     171789  
  34.        359        956 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:20:13            db file scattered read                 9     568327  
  35.        360        837 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:16:22            db file scattered read                 9     624695  
  36.        364      10488 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:17:30            db file scattered read                 9     590289  
  37.        370       3993 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:20:37            db file scattered read                 9     451617  
  38.        376       3258 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:49:47            db file scattered read                 9      69053  
  39.        377       4779 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:44:12            read by other session                  9     103885  
  40.        380      21129 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:15:39            db file scattered read                 9     668803  
  41.        381       2716 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:34:07            read by other session                  9     171789  
  42.        385       1617 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:27:59            read by other session                  9     171789  
  43.        389       1681 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:16:07            db file scattered read                 9     643364  
  44.        391       6657 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:26:49            db file scattered read                 9     234271  
  45.        392      43902 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:30:23            read by other session                  9     171789  
  46.        395       7003 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:15:15            db file scattered read                 9     719929  
  47.        398       5246 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:33:25            read by other session                  9     171789  
  48.        399       9635 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:15:39            db file scattered read                 9     672689  
  49.        410       5588 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:17:05            db file scattered read                 9     604003  
  50.        412        980 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:31:20            read by other session                  9     171789  
  51.        415       4591 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:20:13            db file scattered read                 9     556541  
  52.        416       6649 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:33:33            read by other session                  9     171789  
  53.        418      21354 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:27:21            db file scattered read                 9     183001  
  54.        419      43412 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:17:26            db file scattered read                 9     592652  
  55.        420       3669 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:19:26            db file scattered read                 9     581417  
  56.        423       2586 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:33:25            read by other session                  9     171789  
  57.        430      27397 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:31:50            read by other session                  9     171789  
  58.        432       7621 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:19:39            db file scattered read                 9     534361  
  59.        435       3845 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:29:16            read by other session                  9     171789  
  60.        439       3293 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:09            read by other session                  9     103885  
  61.        440      16800 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:43:40            read by other session                  9     103885  
  62.        446      18697 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:18:18            db file scattered read                 9     584413  
  63.        451       4734 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:45:40            read by other session                  9     103885  
  64.        452       2594 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:27:05            db file scattered read                 9     210845  
  65.        453       9508 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:16:22            db file scattered read                 9     615554  
  66.        458       6139 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:44:31            read by other session                  9     103885  
  67.        460      18012 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:44:08            read by other session                  9     103885  
  68.        461       3224 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:14:26            db file scattered read                 9     724229  
  69.        465       4371 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:34:18            read by other session                  9     171789  
  70.        466       4650 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:20:22            read by other session                  9     534361  
  71.        468       8735 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:25:22            db file scattered read                 9     268194  
  72.        469      14362 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:27:05            db file scattered read                 9     202463  
  73.        471       7194 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:07            read by other session                  9     103885  
  74.        474       2533 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:25:37            db file scattered read                 9     248817  
  75.        478       3757 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:46:43            read by other session                  9     103885  
  76.        485       4438 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:26:28            db file scattered read                 9     236349  
  77.        488       7859 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:26:19            read by other session                  9     239613  
  78.        489       8827 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:45:17            read by other session                  9     103885  
  79.        491       6996 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:24:16            db file scattered read                 9     293921  
  80.        492      21059 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:51:42            read by other session                  9      69053  
  81.        493       7681 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:30:42            read by other session                  9     171789  
  82.        497       4636 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:28:42            read by other session                  9     171789  
  83.        500       4062 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:48:06            read by other session                  9     103885  
  84.        501       9340 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:26:19            db file scattered read                 9     239613  
  85.        504       9746 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:33:57            read by other session                  9     171789  
  86.        506       4258 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:17:05            db file scattered read                 9     605955  
  87.        507       3436 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:17:59            db file scattered read                 9     584829  
  88.        509       3990 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:15:37            db file scattered read                 9     689377  
  89.        511       5592 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:48:47            read by other session                  9      69053  
  90.        515       2380 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:33:11            read by other session                  9     171789  
  91.        516       5049 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:23:42            db file scattered read                 9     375192  
  92.        517      11081 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:16:49            read by other session                  9     612067  
  93.        518      19019 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:19:16            db file scattered read                 9     582651  
  94.        519       2947 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:03            read by other session                  9     103885  
  95.        520      44555 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:19:39            db file scattered read                 9     573202  
  96.        523      32606 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:43:40            read by other session                  9     103885  
  97.        524      14352 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:25:40            read by other session                  9     248817  
  98.        525      49196 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:33:43            read by other session                  9     171789  
  99.        526       2815 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:22:03            db file scattered read                 9     432421  
  100.        527       7558 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:20:40            db file scattered read                 9     447233  
  101.        528      19934 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:18:42            db file scattered read                 9     583660  
  102.        532      12000 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:15:23            db file scattered read                 9     703321  
  103.        535      19291 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:15:40            db file scattered read                 9     651921  
  104.        537       5808 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:16:28            db file scattered read                 9     612067  
  105.        539       5824 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:08            read by other session                  9     103885  
  106.        542      22756 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:08            read by other session                  9     103885  
  107.        543      28090 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:21:19            db file scattered read                 9     446657  
  108.        546      25606 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:21:19            db file scattered read                 9     443777  
  109.        547      21390 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:31:28            read by other session                  9     171789  
  110.        548      31131 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:22:59            db file scattered read                 9     392686  
  111.        557      20900 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:22:05            db file scattered read                 9     420589  
  112.        558       7787 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:21:28            db file scattered read                 9     442321  
  113.        560      49814 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:45:14            read by other session                  9     103885  
  114.        561      33975 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:28:18            read by other session                  9     171789  
  115.        603        708 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:33:50            read by other session                  9     171789  
  116.        645      24200 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:22:05            db file scattered read                 9     419926  
  117.        663       8601 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:23:42            db file scattered read                 9     376348  
  118.        669      42540 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:44:08            read by other session                  9     103885  
  119.        670        355 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:31:20            db file scattered read                 9     171789  
  120.        671       5023 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:33:57            read by other session                  9     171789  
  121.        676        572 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:14:39            db file scattered read                 9     723209  
  122.        677       5142 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:16:26            db file scattered read                 9     615415  
  123.        687       8508 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:15:15            db file scattered read                 9     711833  
  124.        688        565 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:32:03            read by other session                  9     171993  
  125.        700      20809 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:25:13            db file scattered read                 9     279835  
  126.        702       3931 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:33:21            read by other session                  9     171993  
  127.        705       3521 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:16:00            db file scattered read                 9     649425  
  128.        772       1005 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:27:21            db file scattered read                 9     189930  
  129.        797       1081 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:47:35            read by other session                  9     104045  
  130.        806       4809 SYH123      ACTIVE   LENOVO-EWJS4146                                   2307979633 bj3rk3a4t1ybj 2013-02-01 11:18:42            read by other session                  9     584461  
  131.        819      32486 SYH123      ACTIVE   LENOVO-EWJS4146                                    300319264 187mwcn8yd0j0 2013-02-01 11:28:43            read by other session                  9     171993  
  132.        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
[plain] view plain copy
  1. SQL> select distinct sql_id  
  2.   2    from v$session  
  3.   3   where event in ('read by other session', 'db file scattered read');  
  4.    
  5. SQL_ID  
  6. -------------  
  7. bj3rk3a4t1ybj  
  8. 187mwcn8yd0j0  

发现所有的read other session和db file scattered read事件都集中在上面两个SQL语句上。


在查看read other session的参数情况：

[plain] view plain copy
  1. SQL> select name,parameter1,parameter2,wait_class from v$event_name where name='read by other session';  
  2.   
  3. NAME                           PARAMETER1 PARAMETER2 WAIT_CLASS  
  4. ------------------------------ ---------- ---------- --------------------  
  5. read by other session          file#      block#     User I/O  
  6.   
  7. SQL>   

可以看p1代表了file#，p2代表block#

查看这些read other session所访问的对象

[plain] view plain copy
  1. 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.   2  /  
  3.    
  4. OWNER                          SEGMENT_NAME                             SEGMENT_TYPE  
  5. ------------------------------ ---------------------------------------- ------------------  
  6. SCPEND                         TF_TASK_TASKINFOHISTB                    TABLE  
  7.    
  8. SQL>   
  9.   
  10. SQL>   
  11. SQL> select t.owner, t.segment_name, t.segment_type  
  12.   2    from dba_extents t  
  13.   3   where t.file_id = 9  
  14.   4     and 69053 between t.block_id and t.block_id + t.blocks;  
  15.     
  16.    
  17. OWNER                          SEGMENT_NAME                             SEGMENT_TYPE  
  18. ------------------------------ ---------------------------------------- ------------------  
  19. SCPEND                         TF_TASK_TASKINFOHISTB                    TABLE  
  20.    
  21. SQL>   
  22.   
  23. SQL>   
  24. SQL> select t.owner, t.segment_name, t.segment_type  
  25.   2    from dba_extents t  
  26.   3   where t.file_id = 9  
  27.   4     and 590289 between t.block_id and t.block_id + t.blocks;  
  28.     
  29.    
  30. OWNER                          SEGMENT_NAME                             SEGMENT_TYPE  
  31. ------------------------------ ---------------------------------------- ------------------  
  32. SCPEND                         TF_TASK_TASKINFOHISTB                    TABLE  

发现都是访问的同一个表。

再来看看该SQL的执行计划：


可以看到相关SQL确实在对该表做全表扫描的操作，耗时达到22分钟。


后续操作就是对该SQL进行优化


FIGHT_ANGEL
关注
专栏目录
read by other session
zhaoyangjian724的专栏
01-13 621
模拟场景: 同时开多个SESSION执行select * from test2; read by other session:   当会话必须等待其他会话将统一数据块读入缓冲存储器时,作为read by other session事件提交。
select/select 引起的read by other session
zhaoyangjian724的专栏
06-24 647
select/select 引起的read by other session: P1 文件号 P2 块号 Select/Select 引发的buffer lock争用,发生在讲相同块载入到内存的过程。我们可以测试了解Select/Select 引发的buffer lock争用怎样发生的,测试方案如下: 1) 创建拥有5W行的BFW_TEST表 2) 多个进程同时对BFW_TEST表执
Oracle Dba必须了解的Read By Other Session等待：
cotchte0421的博客
08-01 127
Oracle Dba必须了解的Read By Other Session等待：微博里写了篇关于Buffer Busy Waits等待，这里再介绍一种跟它很相像的一个等待Read By Other Session，这个等待在10G...
ORACLE等待事件：read by other session
weixin_30770783的博客
06-26 4541
read by other session简介 官方关于read by other session的介绍如下： When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more se...
大量read by other session导致oracle性能低
wish503的博客
07-29 7353
场景：测试库压力测试，用户反应oracle库很慢很慢 1检查等待事件： select event,count(1) from  v$session_wait group by event order by  2 desc; 发现 read by other session 排第一。 2找到read by other session的SQL，同时可以取一个AWR报告看看TOP SQL，都指向
Oracle数据库教程——oracle常见的等待事件说明[参照].pdf
10-11
在新版本中，针对第二个场景（读取由其他会话读取中的数据块）已独立为“read by other session”。Buffer Busy Waits通常与热点块（Hot Blocks）相关，即多个用户频繁访问相同的数据块。如果等待时间过长，它会在...
等待事件：read by other session
weixin_34343308的博客
12-31 69
业务说恒生自营估值系统有性能问题：分析awrtop1是readbyothersession关于Readbyothersession的说明如下：Wheninformationisrequestedfromthedatabase,Oraclewillfirstreadthedatafromdiskintothedatabase...
一次 read by other session 的处理过程
热门推荐
落落的专栏 专注SQL调优 性能调优
03-13 1万+
一个哥们给我打电话，他说系统中一直出现等待事件 read by other session ，并且该等待都是同一个sql引起的，比较紧急，请我帮忙远程看看。远程过去之后，用脚本把 等待事件给抓出来从图中看到 read by other session 是在运行同一个SQL , sql_id 是  1svyhsn0g56qd 于是查看执行计划该SQL走的是 ILMCU 这个列的索引，过滤条件有4
read by other session 等待事件。
Rhys learn oracle！
01-07 1384
今天是2014-01-06，从今天开始，打算春节之前每天学习一个等待事件，今天就记录一下read by other session这个等待事件笔记。 什么是read by other session？ This wait event occurs when we are trying to access a buffer in the buffer cache but we find that
read by other session 的等待事件(转)
cox11952的博客
07-06 177
原文地址：http://blogold.chinaunix.net/u3/107027/showart_2190272.html今天在做STATSPACK报告的时候，发现TOP 5中有一个以前没见过的等待事件read by ot...
gdb调试之--read by other session 等待事件的重现
haiross的专栏
01-21 2349
gdb调试之--read by other session 等待事件的重现                分类：            Oracle Debug2014-01-18 21:55290人阅读评论(0)收藏举报 转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/18461845      发生read by ot
一次 read by other session 的处理过程--数据走索引需要看索引字段的数据分布情况
hackshi008的博客
09-15 740
http://blog.csdn.net/robinson1988/article/details/44244957  文章最后连接了直方图的使用原因： 总结：一次 read by other session 的处理过程--数据走索引需要看索引字段的数据分布情况，若数据分布不均匀，oracle本不应该走索引， 但是如果oracle还是走了该索引，有可能是未收集统计信息， 没收集直方图。导致ora
read by other session原理和实战解决思路
conghe6716的博客
03-18 1472
3月17日七点左右，客户的一套11gRAC突然出现活动会话增加，等待事件过高，cpu使用率几乎接近0的情况。下面尽可能还原解决这个问题的过程： 查询等待事件： select inst_id, event#, even...
OBS-Studio-30.2.3-Windows.zip
最新发布
09-23
OBS-Studio-30.2.3-Windows.zip
oracle 常见的33个等待事件
09-09
Oracle数据库中的等待事件是指在数据库运行时，由于某些资源的限制，导致进程需要等待的事件。以下是常见的33个Oracle等待事件： 1. latch free - 等待获取latch资源的进程。 2. CPU time - 等待CPU处理时间。 3. log file sync - 等待日志文件同步完成。 4. buffer busy waits - 等待访问繁忙的数据缓冲区。 5. db file sequential read - 等待从磁盘读取数据文件的读取操作完成。 6. db file scattered read - 等待从磁盘读取散乱的数据块的读取操作完成。 7. log file parallel write - 等待并行写入日志文件的操作完成。 8. direct path read - 等待直接路径读取完成。 9. SQL*Net message from client - 等待来自客户端的SQL*Net消息。 10. log buffer space - 等待空闲的日志缓冲区空间。 11. control file parallel write - 等待并行写入控制文件的操作完成。 12. db file parallel write - 等待并行写入数据文件的操作完成。 13. enqueue - 等待获取enqueue资源的操作完成。 14. db file async I/O submit - 等待异步I/O提交的操作完成。 15. db file async I/O complete - 等待异步I/O完成的操作。 16. direct path write - 等待直接路径写入操作完成。 17. SQL*Net more data to client - 等待传输更多SQL*Net数据给客户端。 18. redo log space requests - 等待空闲的重做日志空间。 19. buffer deadlock - 等待缓冲区死锁解除。 20. db file checkpoint completion - 等待数据文件检查点完成。 21. db file parallel read - 等待并行读取数据文件的操作。 22. latch: cache buffers chains - 等待获取缓冲区链锁的进程。 23. read by other session - 等待其他会话读取数据。 24. control file sequential read - 等待从控制文件读取数据。 25. ASM background process - 等待ASM后台进程操作完成。 26. latch: In-Memory undo latch - 等待获取In-Memory undo latch锁的进程。 27. cell single block physical read - 等待从Cell服务器读取单个块的物理读取操作完成。 28. library cache: mutex X - 等待获取库缓存互斥锁的进程。 29. PX Deq: Table Q Normal - 等待并行执行查询操作。 30. direct path read temp - 等待从临时文件读取数据的直接路径读取操作完成。 31. PX Deq Execution Msg - 等待并行执行消息处理。 32. PX Deq Credit: send blkd - 等待并行执行接收处理。 33. PX Deq: reap credit - 等待并行执行回收资源的操作完成。 以上是常见的33个Oracle等待事件，了解这些事件对于排查和优化数据库性能非常重要。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值