ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用10046事件研究三种数据表访问方式(上)

利用10046事件研究三种数据表访问方式(上)

原创 Linux操作系统 作者:realkid4 时间:2012-05-28 20:12:55 0 删除 编辑

 

Oracle数据库而言,数据表的访问一共有三种方式,分别为:table access by rowidfull table scan(FTS)table access by index

 

三种访问方式是出现在Oracle SQL的执行计划中,作为优化器产物并且最后进行执行的动作操作。

 

那么,从微观角度看,三种访问方式的具体形态如何呢?本文使用10046这个基本工具,跟踪执行过程,归纳操作特点。首先,我们还是进行环境准备。

 

1、环境准备

 

我们使用Oracle 11gR2进行试验。

 

 

SQL> select * from v$version;

 

BANNER

------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE        11.2.0.1.0         Production

 

 

构建实验数据表T,同时在object_id列构建索引。为了保证实验效果,我们构建一张百条记录数据表即可。

 

 

SQL> create table t as select * from dba_objects where wner='SCOTT';

Table created

 

SQL> create index idx_t_id on t(object_id);

Index created

 

SQL> insert into t select * from dba_objects where wner='XDB';

844 rows inserted

 

SQL> commit;

Commit complete

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

 

--数据表和索引段的object_id编号,注意,后面会用到!!

SQL> select OBJECT_ID, data_object_id from dba_objects where wner='SYS' and object_name='IDX_T_ID';

 

 OBJECT_ID DATA_OBJECT_ID

---------- --------------

     75536          75536

 

SQL> select OBJECT_ID, data_object_id from dba_objects where wner='SYS' and object_name='T';

 

 OBJECT_ID DATA_OBJECT_ID

---------- --------------

     75535          75535

 

 

此时,从segment段空间角度,看数据表和其索引的形态如下:

 

 

--数据表T

SQL> select HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where wner='SYS' and segment_name='T';

 

HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS

----------- ------------ ---------- ---------- ----------

          1        90632     131072         16          2

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where wner='SYS' and segment_name='T';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          1      90632      65536          8

         1          1      90648      65536          8

 

--索引段

SQL> select HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where wner='SYS' and segment_name='IDX_T_ID';

 

HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS

----------- ------------ ---------- ---------- ----------

          1        90640      65536          8          1

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where wner='SYS' and segment_name='IDX_T_ID';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          1      90640      65536          8

 

 

上面结果中有一些需要关注,在下面实验中反复使用:

 

对数据表T而言,Oracle为了容纳数据,一共分配了2个分区,16个数据块。头块位置为(file=1, block=90632)。两个分区的头块分别为(file=1, block=90632)和(file=1, block=90648),每个分区连续8个数据块。

 

对索引段而言,头块为(file=1,block=90640)。分配了一个分区,8个数据块。

 

做好上面的信息准备之后,我们就开始实验。

 

2FTS全表扫描动作实验

 

FTS是最基本的oracle数据表访问动作之一。进行FTS的时候,Oracle会检索所有高水位线HWM以下的数据块内容。那么,具体是如何呢?

 

注意:本篇所有select动作前,都要保证shared poolbuffer cache清空。这样做是为了保证实验效果。

 

 

SQL> alter system flush buffer_cache;

系统已更改。

 

SQL> alter system flush shared_pool;

系统已更改。

 

 

使用10046事件对select进行跟踪,获取跟踪文件。

 

--11g中视图,可以方便获取到跟踪文件信息;

SQL>  select value from v$diag_info where name='Default Trace File';

 

VALUE

----------------------------------------------------------------------

/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_5969.trc

 

--跟踪会话

SQL> alter session set events '10046 trace name context forever, level 12';

会话已更改。

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

       858

 

SQL> alter session set events '10046 trace name context off';

会话已更改。

 

 

到指定目录中,确定跟踪文件。

 

--在服务器本地;

[root@oracle11g trace]# pwd

/u01/diag/rdbms/wilson/wilson/trace

 

[root@oracle11g trace]# ls -l | grep 5969

-rw-r-----  1 oracle oinstall     2705 Jan 15  2011 wilson_j004_5969.trc

-rw-r-----  1 oracle oinstall      195 Jan 15  2011 wilson_j004_5969.trm

-rw-r-----  1 oracle oinstall    16068 May 26 20:42 wilson_ora_5969.trc

-rw-r-----  1 oracle oinstall      117 May 26 20:42 wilson_ora_5969.trm

 

--使用tkprof命令处理raw trace file

[root@oracle11g trace]# tkprof wilson_ora_5969.trc res1.txt

 

TKPROF: Release 11.2.0.1.0 - Development on Sat May 26 20:35:25 2012

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

[root@oracle11g trace]# ls -l | grep res1

-rw-r--r--  1 root   root         5220 May 26 20:35 res1.txt

 

 

首先,我们检查res1.txt中处理过的数据文件。注意,由于我们事先清理过shared poolbuffer cache,在select过程中有很多数据字典相关的select操作,如对obj$,这些recursive call是内部生成的语句。我们只需要关注目标select的信息。

 

*************************************************************************

 

SQL ID: cyzznbykb509s

Plan Hash: 2966233522

select count(*)

from

 t

 

call    count       cpu    elapsed       disk      query    current        rows

------- -----  -------- ---------- ---------- ---------- ----------  ----------

Parse       1      0.02       0.02          4         27          0           0

Execute     1      0.00       0.00          0          0          0           0

Fetch       2      0.00       0.00         16         18          0           1

------- -----  -------- ---------- ---------- ---------- ----------  ----------

total       4      0.02       0.02         20         45          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE (cr=18 pr=16 pw=0 time=0 us)

   858   TABLE ACCESS FULL T (cr=18 pr=16 pw=0 time=2815 us cost=6 size=0 card=858)

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  db file sequential read                         1        0.00          0.00

  db file scattered read                          2        0.00          0.00

  SQL*Net message from client                     2        0.00          0.00

********************************************************************************

 

 

注意上面代码片段中标红的部分,首先执行计划中反映该语句的执行计划是进行FTS。其次,在跟踪语句的wait events上,我们看到了可能与FTS实际动作有关的两个wait events,分别为“db file sequential read”和“db file scattered read”。在标红部分,我们只能看到分别发生了1次和2次。

 

至于说具体两个等待的动作参数取值,我们只能靠分析trace raw file的片段来获取得知。

 

 

=====================

PARSING IN CURSOR #1 len=22 dep=0 uid=0 ct=3 lid=0 tim=1338036170287069 hv=2763161912 ad='2fab988c' sqlid='cyzznbykb509s'

select count(*) from t

END OF STMT

PARSE #1:c=23997,e=23800,p=4,cr=27,cu=0,mis=1,r=0,dep=0,og=1,plh=2966233522,tim=1338036170287043

EXEC #1:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2966233522,tim=1338036170287120

WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1338036170287123

WAIT #1: nam='db file sequential read' ela= 341 file#=1 block#=90632 blocks=1 obj#=75535 tim=1338036170287464

WAIT #1: nam='db file scattered read' ela= 400 file#=1 block#=90633 blocks=7 obj#=75535 tim=1338036170288322

WAIT #1: nam='db file scattered read' ela= 63 file#=1 block#=90648 blocks=8 obj#=75535 tim=1338036170289789

FETCH #1:c=3999,e=3658,p=16,cr=18,cu=0,mis=0,r=1,dep=0,og=1,plh=2966233522,tim=1338036170290781

STAT #1 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=18 pr=16 pw=0 time=0 us)'

STAT #1 id=2 cnt=858 pid=1 pos=1 bj=75535 p='TABLE ACCESS FULL T (cr=18 pr=16 pw=0 time=2815 us cost=6 size=0 card=858)'

WAIT #1: nam='SQL*Net message from client' ela= 866 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036170291781

FETCH #1:c=0,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2966233522,tim=1338036170291841

WAIT #1: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036170291887

WAIT #1: nam='SQL*Net message from client' ela= 1373 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036170293290

CLOSE #1:c=0,e=20,dep=0,type=0,tim=1338036170293362

WAIT #0: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036170293750

 

*** 2012-05-26 20:42:55.857

WAIT #0: nam='SQL*Net message from client' ela= 5563757 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036175857537

=====================

 

 

上面的sql_id,表明了我们截取的片段正是我们的目标SQL。标红的三句wait event,也正是我们希望研究的FTS动作模式。下面我们逐句进行分析:

 

首先是一次的“db file sequential read”,这个事件对于熟悉OWI和经常查看AWR的朋友并不陌生。一些教条的说法是:该事件经常伴随在索引路径访问方式。但是,在当前我们的FTS中,同样看到了他的身影。这个事件的三个参数分别为文件编号、起始头编号和数据块数量。下面片段为:

 

 

WAIT #1: nam='db file sequential read' ela= 341 file#=1 block#=90632 blocks=1 obj#=75535 tim=1338036170287464

 

 

其中,ela表示elapse timeobj#为对象编号,tim为全局时间序号。对象75535就是数据表T。对应的读数据块为(file#=1, block#=90632,blocknum=1)。注意,这个数据块就是数据表的头块。数据头块中间包括该数据段分区的信息、高水位线位置。读头块的意义在于,server process据此可以获取所有的检索路径。

 

下面出现了两次的db file scatted read动作。具体我们观察细节。

 

 

WAIT #1: nam='db file scattered read' ela= 400 file#=1 block#=90633 blocks=7 obj#=75535 tim=1338036170288322

WAIT #1: nam='db file scattered read' ela= 63 file#=1 block#=90648 blocks=8 obj#=75535 tim=1338036170289789

 

 

两次的scatted read,分别从(file=1, block=90633, blocks=7)和(file=1,block=90648, blocks=8)入手,进行了两次读动作。两次读动作涉及的对象同样是数据表T。我们注意,第一个读动作是从extent=0头块的第二个数据块开始读的,按照一个分区8个数据块的分布,该动作相当于将第一个分区除了头块之外所有块读了一遍。第二个读动作对应的就是extent=1的八个数据块,注意虽然blocks=8,但是从ela=63的信息上,我们猜测并没有完全读完八个数据块,而是读到了HWM位置。

 

如果数据表包括成千上万个extents,那么db scatted read动作也应该会出现成千上万次。笔者窃以为这就是流言中“db scatted read”与FTS相对应的来源了。

 

从上面的分析,我们可以总结FTS(全表扫描)的动作特点:

 

ü  Oracle首先会去从数据字典中,获取到这个数据表对象的段头信息;

ü  无论数据表多大或者多小,Server Process会先去访问段头块,从其中获取到分区列表信息和HWM信息。此时Server Process对应的事件为“db sequential read”;

ü  之后,Server Process会按照一个分区的顺序,以“db scatted read”的动作访问HWM下所有的数据块。

 

下面我们去分析一下索引路径的执行操作情况。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-731324/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7660568