[转载]---Oracle等待事件1之分别用表和索引上数据的访问来产生db file scattered read等待事件...

weixin_30292843

于 2019-07-08 20:52:13 发布

阅读量61

点赞数

原文链接：http://www.cnblogs.com/Richardzhu/articles/2913950.html

版权

第一篇：《Oracle等待事件1之分别用表和索引上数据的访问来产生db file scattered read等待事件》

第二篇：《Oracle等待事件2之构造一个DB File Sequential Read等待事件和构造一个Direct Path Read等待事件》

第三篇：《暂无》

引言：其实等待事件我想大家都不会陌生，那么Oracle研究出来等待事件是为了什么呢？它会告诉我们在什么情况时数据库的性能是快还是慢，我们可以根据数据库在等待什么资源来进行性能优化(是否资源太忙，idle较多，latch争用严重，会话有阻塞了)，解决方案例如是增加SGA，还是PGA，还是系统I/0等，我们在日常巡检的时候也要经常看看数据库等待事件来给oracle诊脉，是否超过了合理阀值，如果超过这时候就会有问题了，需要DBA出手干预。而有些是合理等待事件，这类等待事件是正常允许的，我们可以忽略，关键就是如何来区分是合理还是异常。

一、我们先从一个最简单的等待事件来进入oracle wait event world

----我们要知道当前的会话id是是什么，这样我们就可以在会话等待视图中查看对于id的等待事件是什么了----
1 SQL> select distinct sid from v$mystat;
2 
3     SID
4 ----------
5      1

 1 SQL> col wait_class for a10;
 2 SQL> col state for a15;
 3 SQL> col event for a45;
 4 SQL> set linesize 150;
 5 SQL> select sid,event,wait_class,state,wait_time from v$session_wait;
 6 
 7     SID         EVENT                     WAIT_CLASS    STATE             WAIT_TIME
 8 ---------- ------------------------------ ---------- ---------------     ----------
 9      1       SQL*Net message to client     Network    WAITED SHORT TIME     -1
10

SQL*Net message to client ：这是一个非常常见的等待事件，在oracle 11g中它算做一个短等待，当客户端与服务器建立起会话后，服务器就会等待客户端发送命令，如果此时没有命令传输就会产生这个等待事件。我们举个例子比较好理解。

sqlplus jack/jack 我们通过sqlplus登录数据库，就会建立起会话，此时就会产生上述等待事件。

pl/sql Developer 软件访问数据库(这也是客户端与服务器建立会话的过程)，也会发生上述等待事件。

SQL*Net(原来叫net8)是Oracle自己的通信协议，只要安装了oracle客户端并通过客户端连接数据库就会走这个协议通信，当然就会产生基于这个协议的等待事件。(这个事件我们可以忽略)

二、分别用表和索引上数据的访问来产生db file scattered read等待事件，等待事件需要在v$session_wait和10046 trace文件中显示出来，贴出整个演示过程

db file scattered read等待事件：是由于多数据块读操作产生的，当我们检索数据时从磁盘上读数据到内存中，一次I/O读取多个数据块，而数据块在内存中是分散分布并不是连续的，当数据块读取到内存这个过程中会产生"db file scattered read"事件。

多数据块读场景：FTS(full table scan)全表扫描

IFFS (index fast full scan) 快速索引全扫描：把索引链切割成很多份，多块并行读取

  1 SQL> show parameter memory_target;
  2 
  3 NAME                     TYPE     VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 memory_target                 big integer 476M
  6 SQL> create table jack as select * from dba_objects;
  7 
  8 Table created.
  9 
 10 SQL> select count(*) from jack;
 11 
 12   COUNT(*)
 13 ----------
 14      71887
 15 
 ----FTS扫描----
 16 SQL> set autotrace trace exp;
 17 SQL> select count(*) from jack;
 18 
 19 Execution Plan
 20 ----------------------------------------------------------
 21 Plan hash value: 1205023501
 22 
 23 -------------------------------------------------------------------
 24 | Id  | Operation       | Name | Rows  | Cost (%CPU)| Time      |
 25 -------------------------------------------------------------------
 26 |   0 | SELECT STATEMENT   |      |    1 |   280   (1)| 00:00:04 |
 27 |   1 |  SORT AGGREGATE    |      |    1 |           |      |
 28 |   2 |   TABLE ACCESS FULL| JACK | 83009 |   280   (1)| 00:00:04 |
 29 -------------------------------------------------------------------
 30 
 31 Note
 32 -----
 33    - dynamic sampling used for this statement (level=2)
 ----由于这个动作非常的块，在v$session_wait视图中不容易捕捉到，其实我们还可以去10046事件中追踪一下----
 ----10046事件分析sql执行性能----
 ----解释：10046事件是发生在sql的执行过程中，它能够全面分析出sql执行过程和所消耗的资源，让你了解这条sql是否是最优的，10046事件有4个级别：
     LEVEL 1 等同于SQL_TRACE的功能
     LEVEL 4 在LEVEL 1基础上增加绑定变量信息
     LEVEL 8 在LEVEL 1基础上增加等待事件信息
     LEVEL 12 等同于LEVEL 4+LEVEL 8，即有绑定变量信息又有等待事件信息
 34 
 35 SQL> set autotrace off;
 ----先做一下表的分析，收集到更多的统计信息，方便10046正确评估----
 36 SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'JACK',cascade=>true);
 37 
 38 PL/SQL procedure successfully completed.
 39 
 ----清空data_buffer_cache，好显示多块读的效果----
 40 SQL> alter system flush buffer_cache;
 41 
 42 System altered.
 43 
 44 SQL> alter session set events '10046 trace name context forever,level 12';
 45 
 46 Session altered.
 47 
 48 SQL> select count(*) from jack;
 49 
 50   COUNT(*)
 51 ----------
 52      71887
 53 
 54 SQL> alter session set events '10046 trace name context off';
 55 
 56 Session altered.
 57 
 58 SQL> select name,value from v$diag_info where name='Default Trace File';
 59 
 60 NAME                VALUE

 61 -----------------   -----------------------------------------------------------------------------
 62 Default Trace File  /u01/app/oracle/diag/rdbms/yft2/YFT2/trace/YFT2_ora_3543.trc

 63  
 64  
 65  
 66 
 67 [oracle@node2 ~]$ view /u01/app/oracle/diag/rdbms/yft2/YFT2/trace/YFT2_ora_3543.trc
 68 
 69 =====================
 70 PARSING IN CURSOR #16 len=25 dep=0 uid=0 oct=3 lid=0 tim=1361072253016265 hv=2169573763 ad='369e5e30' sqlid='937fmby0p24c3'
 71 select count(*) from jack
 72 END OF STMT
 73 PARSE #16:c=21997,e=94784,p=294,cr=79,cu=0,mis=1,r=0,dep=0,og=1,plh=1205023501,tim=1361072253016263
 74 EXEC #16:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1205023501,tim=1361072253016350
 75 WAIT #16: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=73629 tim=1361072253016379
 76 WAIT #16: nam='db file scattered read' ela= 193 file#=1 block#=85441 blocks=7 obj#=73629 tim=1361072253016663
 77 WAIT #16: nam='db file scattered read' ela= 59 file#=1 block#=85449 blocks=7 obj#=73629 tim=1361072253017047
 78 WAIT #16: nam='db file scattered read' ela= 60 file#=1 block#=85456 blocks=8 obj#=73629 tim=1361072253017363
 79 WAIT #16: nam='db file scattered read' ela= 60 file#=1 block#=85464 blocks=8 obj#=73629 tim=1361072253017687
 80 WAIT #16: nam='db file scattered read' ela= 32 file#=1 block#=85472 blocks=8 obj#=73629 tim=1361072253018020
 81 WAIT #16: nam='db file scattered read' ela= 16 file#=1 block#=85480 blocks=4 obj#=73629 tim=1361072253018425
 82 WAIT #16: nam='db file scattered read' ela= 19 file#=1 block#=85485 blocks=3 obj#=73629 tim=1361072253018599
 83 WAIT #16: nam='db file scattered read' ela= 60 file#=1 block#=85488 blocks=8 obj#=73629 tim=1361072253018762
 84 WAIT #16: nam='db file scattered read' ela= 69 file#=1 block#=85496 blocks=8 obj#=73629 tim=1361072253019150
 85 WAIT #16: nam='db file scattered read' ela= 22 file#=1 block#=87040 blocks=4 obj#=73629 tim=1361072253019582
 86 WAIT #16: nam='db file scattered read' ela= 38 file#=1 block#=87045 blocks=3 obj#=73629 tim=1361072253019887
 87 WAIT #16: nam='db file scattered read' ela= 44 file#=1 block#=87048 blocks=8 obj#=73629 tim=1361072253020032
 88 WAIT #16: nam='db file scattered read' ela= 36 file#=1 block#=87056 blocks=8 obj#=73629 tim=1361072253020440
 89 WAIT #16: nam='db file scattered read' ela= 83 file#=1 block#=87064 blocks=3 obj#=73629 tim=1361072253020778
 90 WAIT #16: nam='db file scattered read' ela= 51 file#=1 block#=87068 blocks=4 obj#=73629 tim=1361072253020968
 91 WAIT #16: nam='db file scattered read' ela= 109 file#=1 block#=87072 blocks=8 obj#=73629 tim=1361072253021306
 92 WAIT #16: nam='db file scattered read' ela= 122 file#=1 block#=87080 blocks=8 obj#=73629 tim=1361072253021751
 93 WAIT #16: nam='db file scattered read' ela= 70 file#=1 block#=87088 blocks=6 obj#=73629 tim=1361072253022094
 ----参数解释：File#：访问数据文件号
              Block#：访问起始数据块号
              Blocks：读取了多少个数据块---- 
 ----例如：85456+8=85464->85472+8=85480一直读取到87088号，这些只是其中的一部分，可见这些数据块在文件中都是连续存放的，读取机制都是多块读，符合产生"db file scattered read"等待事件的条件----
 94 
 95 SQL> select file#,name from v$datafile where file#=1;
 96 
 97    FILE#                   NAME

 98 ----------  ------------------------------------------------------------------
 99     1           /u01/app/oracle/oradata/YFT2/system01.dbf

100 
 ----显示累计会话等待事件统计信息---- 
101 SQL> select sid,event,total_waits,time_waited from v$session_event where sid=1;

     
102  SID                   EVENT                             TOTAL_WAITS TIME_WAITED
103 ---------- --------------------------------------------- ----------- ----------- 
104   1               db file scattered read                     289          52      



105

小提示：当你长时间使用sqlplus登录数据库时在做上面的10046实验，你可能会发现在trace文件中只有SQL*Net message to client没有db file scattered read，解决的方法是退出来重新登陆这样就可以看到db file scattered read了。

IFFS扫描

转载于:https://www.cnblogs.com/Richardzhu/articles/2913950.html

weixin_30292843

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
[转载]---Oracle等待事件1之分别用表和索引上数据的访问来产生db file scattered read等待事件...

第一篇：《Oracle等待事件1之分别用表和索引上数据的访问来产生db file scattered read等待事件》第二篇：《Oracle等待事件2之构造一个DB File Sequential Read等待事件和构造一个Direct Path Read等待事件》第三篇：《暂无》引言：其实等待事件我想大家都不会陌生，那么Oracle研究出来等待事件是为了什么呢...
复制链接

扫一扫