模拟DB File Sequential Read

最新推荐文章于 2019-07-29 14:28:47 发布

深圳gg

最新推荐文章于 2019-07-29 14:28:47 发布

阅读量1.2k

点赞数

分类专栏：等待事件

本文链接：https://blog.csdn.net/stevendbaguo/article/details/8982227

版权

等待事件专栏收录该内容

34 篇文章 1 订阅

订阅专栏

本文详细解析了在Oracle数据库环境中进行SQL查询时遇到的单块读等待问题，包括通过创建索引、调整参数设置、执行计划分析以及使用V$session_event视图来识别和优化查询性能瓶颈。通过实例展示了如何通过查询计划分析和Session Event跟踪，发现并解决了查询执行过程中遇到的性能问题。

摘要由CSDN通过智能技术生成

当发生单块读时，就会出现这种等待，大部分情况下是读索引，少部分情况下是读undo。构造索引范围扫描，用v$session_event中捕捉。
session1:
C:\Documents and Settings\guogang>sqlplus test/test
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 5月 23 20:10:12 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select distinct sid from v$mystat;
SID
----------
18
SQL> create table test as select * from dba_objects;
SQL> create index ind_t_id on test(object_id);
SQL> alter session set events '10046 trace name context forever,level 12';
SQL> set autotrace traceonly
SQL> alter system flush buffer_cache;
SQL> select /*+index(t ind_t_id)*/ object_id from test t where object_id > 1;
执行计划
----------------------------------------------------------
Plan hash value: 3343177607

-----------------------------------------------------------------------------
| Id | Operation        | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          | 63057 |   800K|   173   (0)| 00:00:03 |
|* 1 | INDEX RANGE SCAN| IND_T_ID | 63057 |   800K|   173   (0)| 00:00:03 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID">1)
统计信息
----------------------------------------------------------
         32 recursive calls
          0 db block gets
       5006 consistent gets
        517 physical reads
          0 redo size
    1043091 bytes sent via SQL*Net to client
      53072 bytes received via SQL*Net from client
       4789 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      71806 rows processed

session2:
刚连会话之时
SQL> select event,t.TOTAL_WAITS from v$session_event t where sid=18;
EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
Disk file operations I/O                                                   1
db file sequential read                                                   11
SQL*Net message to client                                                 14
SQL*Net message from client                                               13
events in waitclass Other                                                  1

清除缓存之前
SQL> select event,t.TOTAL_WAITS from v$session_event t where sid=18;
EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
Disk file operations I/O                                                   3
log file sync                                                              3
db file sequential read                                                  573
db file scattered read                                                    51
direct path write                                                          4
SQL*Net message to client                                                 24
SQL*Net message from client                                               23
events in waitclass Other                                                  8

执行查询之后
SQL> select event,t.TOTAL_WAITS from v$session_event t where sid=18;
EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
Disk file operations I/O                                                   4
log file sync                                                              3
db file sequential read                                                  579
db file scattered read                                                   116
direct path write                                                          4
SQL*Net message to client                                               4819
SQL*Net message from client                                             4818
events in waitclass Other                                                  9

session3:
select /*+index(t ind_t_id)*/ object_id
from
test t where object_id > 1

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.01          2          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     4789      0.14       0.23        160       4936          0       71806
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total     4791      0.14       0.24        162       4938          0       71806
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows     Row Source Operation
------- ---------------------------------------------------
71806 INDEX RANGE SCAN IND_T_ID (cr=4936 pr=160 pw=0 time=37954 us cost=173 size=819741 card=63057)(object id 73406)
Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait Total Waited
----------------------------------------   Waited ---------- ------------
db file sequential read                         2        0.00          0.01
SQL*Net message to client                    4789        0.00          0.00
SQL*Net message from client                  4789        0.00          0.18
db file scattered read                         20        0.02          0.04
********************************************************************************