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

青旅半醒 2022-03-27 10:15 201阅读 0赞

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

第二篇： [《Oracle等待事件2之构造一个DB File Sequential Read等待事件和构造一个Direct Path 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扫描

[Oracle_1_db file scattered read]: http://www.cnblogs.com/Richardzhu/articles/2913950.html
[Oracle_2_DB File Sequential Read_Direct Path Read]: http://www.cnblogs.com/Richardzhu/articles/2933149.html