ITPub博客

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

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

原创 Linux操作系统 作者:realkid4 时间:2012-05-29 20:46:02 0 删除 编辑

 

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

 

3、索引路径执行信息

 

我们借助在object_id列上添加的索引,构造一个索引路径执行计划。我们同样适用10046事件进行跟踪。

 

 

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

VALUE

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

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

 

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

会话已更改。

 

SQL> select * from t where object_id=75381;

 

OWNER

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

OBJECT_NAME

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

(结果集合省略……

 

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

会话已更改。

 

 

我们同样进行跟踪文件处理。

 

 

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

-rw-r-----  1 oracle oinstall    30869 May 26 20:56 wilson_ora_6063.trc

-rw-r-----  1 oracle oinstall      161 May 26 20:56 wilson_ora_6063.trm

 

[root@oracle11g trace]# tkprof wilson_ora_6063.trc res2.txt

 

TKPROF: Release 11.2.0.1.0 - Development on Sat May 26 20:57:50 2012

 

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

 

 

首先,我们检查一下处理过的跟踪文件res2.txt。其中,我们发现了如下片段。

 

 

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

SQL ID: 1dsg0anukjddx

Plan Hash: 514881935

select *

from t where object_id=75381

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.04       0.04          9         72          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          3          4          0           1

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

total        4      0.04       0.05         12         76          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

 

Rows     Row Source Operation

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

      1  TABLE ACCESS BY INDEX ROWID T (cr=4 pr=3 pw=0 time=0 us cost=2 size=90 card=1)

      1   INDEX RANGE SCAN IDX_T_ID (cr=3 pr=2 pw=0 time=0 us cost=1 size=0 card=1)(object id 75536)

 

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                         3        0.00          0.00

  SQL*Net message from client                     2        0.01          0.02

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

 

 

从执行计划上,我们看到了索引路径,并且存在三次的db file sequential real。下面是相应的trace raw file片段。

 

 

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

PARSING IN CURSOR #3 len=37 dep=0 uid=0 ct=3 lid=0 tim=1338036989520646 hv=891860413 ad='31c9bdc4' sqlid='1dsg0anukjddx'

select * from t where object_id=75381

END OF STMT

PARSE #3:c=48992,e=49128,p=9,cr=72,cu=0,mis=1,r=0,dep=0,og=1,plh=514881935,tim=1338036989520644

EXEC #3:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=514881935,tim=1338036989521534

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

WAIT #3: nam='db file sequential read' ela= 282 file#=1 block#=90641 blocks=1 obj#=75536 tim=1338036989521930

WAIT #3: nam='db file sequential read' ela= 458 file#=1 block#=90644 blocks=1 obj#=75536 tim=1338036989522485

WAIT #3: nam='db file sequential read' ela= 60 file#=1 block#=90633 blocks=1 obj#=75535 tim=1338036989522629

FETCH #3:c=1000,e=997,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=514881935,tim=1338036989522644

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

FETCH #3:c=0,e=29,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=514881935,tim=1338036989534638

STAT #3 id=1 cnt=1 pid=0 pos=1 bj=75535 p='TABLE ACCESS BY INDEX ROWID T (cr=4 pr=3 pw=0 time=0 us cost=2 size=90 card=1)'

STAT #3 id=2 cnt=1 pid=1 pos=1 bj=75536 p='INDEX RANGE SCAN IDX_T_ID (cr=3 pr=2 pw=0 time=0 us cost=1 size=0 card=1)'

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

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

CLOSE #3:c=0,e=42,dep=0,type=0,tim=1338036989543528

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

 

*** 2012-05-26 20:56:53.216

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

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

 

 

我们的确看到了三次的“db file sequential read”,索引路径中出现该事件的比例还是较多的。下面我们逐句进行分析:

 

首先一次的db file sequential read,对数据块(file=1,block=90641)进行检索。对象为75536

 

 

 

WAIT #3: nam='db file sequential read' ela= 282 file#=1 block#=90641 blocks=1 obj#=75536 tim=1338036989521930

 

 

 

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 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

 

 

从上面的数据字典信息,可以看到第一个read的动作是索引段的段头块的后一个块。对索引结构了解的朋友们可以知道,索引段头块后一个数据块就是索引的根节点所在数据块。这样,通过定位就可以一下找到索引的根节点,进行遍历。

 

接下来,Server Process同样会去访问索引段的90644数据块。我们猜测也可以知道,这个块就是目标叶子节点所在的数据块。这样就可以定位到结果所在数据行的rowid信息。

 

 

WAIT #3: nam='db file sequential read' ela= 458 file#=1 block#=90644 blocks=1 obj#=75536 tim=1338036989522485

 

 

最后就好理解了,第三次db file sequential read就直接获取数据行好了。

 

 

WAIT #3: nam='db file sequential read' ela= 60 file#=1 block#=90633 blocks=1 obj#=75535 tim=1338036989522629

 

 

经过上面的分析,我们可以知道Server Process在处理索引路径时的行为。

 

ü  首先从数据字典中,获取到索引的头块位置信息。但是,Server Process并不去读取头块,而是读取头块的后一个数据块,这就是索引树的根节点;

ü  定位根节点之后,就按照分支节点、叶子节点的顺序找到符合条件结果叶子节点。进而获取到rowid列表。

ü  最后根据rowid列表,可以直接定位到数据块位置;

 

注意,在索引路径中,都是db file sequential read动作。

 

最后我们分析一下号称最快定位数据的rowid方式。

 

4rowid访问方式

 

RowidOracle内部的物理地址。对堆表(heap table)而言,rowid是基本不会发生变化的。我们依然是采用10046事件方式,对rowid动作进行检验。

 

 

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

VALUE

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

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

 

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

会话已更改。

 

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

会话已更改。

 

SQL> select * from t where rowid='AAAScPAABAAAWIJAAM';

OWNER

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

OBJECT_NAME

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

(篇幅原因,省略部分……

 

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

会话已更改。

 

 

处理trace raw file

 

 

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

-rw-r-----  1 oracle oinstall    31312 May 26 21:10 wilson_ora_6196.trc

-rw-r-----  1 oracle oinstall      151 May 26 21:10 wilson_ora_6196.trm

[root@oracle11g trace]# tkprof wilson_ora_6196.trc res3.txt

 

TKPROF: Release 11.2.0.1.0 - Development on Sat May 26 21:11:05 2012

 

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

 

 

我们先检查处理过的res3.txt文件。

 

 

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

SQL ID: 1zwq3yahqajra

Plan Hash: 3207308387

select *

from

 t where rowid='AAAScPAABAAAWIJAAM'

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.03       0.04          6         74          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          1          1          0           1

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

total        4      0.04       0.04          7         75          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

 

Rows     Row Source Operation

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

      1  TABLE ACCESS BY USER ROWID T (cr=1 pr=1 pw=0 time=0 us cost=1 size=90 card=1)

 

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

  SQL*Net message from client                     2        0.00          0.00

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

 

 

执行计划是table access by rowid,说明执行路径的确是rowid。在其中,我们只看到了一次的db file sequential read。在trace raw file中,我们确定如此:

 

 

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

PARSING IN CURSOR #2 len=48 dep=0 uid=0 ct=3 lid=0 tim=1338037828092715 hv=2707769066 ad='3857b96c' sqlid='1zwq3yahqajra'

select * from t where rowid='AAAScPAABAAAWIJAAM'

END OF STMT

PARSE #2:c=39993,e=40057,p=6,cr=74,cu=0,mis=1,r=0,dep=0,og=1,plh=3207308387,tim=1338037828092711

EXEC #2:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3207308387,tim=1338037828092840

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

WAIT #2: nam='db file sequential read' ela= 493 file#=1 block#=90633 blocks=1 obj#=75535 tim=1338037828093519

FETCH #2:c=1000,e=652,p=1,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=3207308387,tim=1338037828093602

STAT #2 id=1 cnt=1 pid=0 pos=1 bj=75535 p='TABLE ACCESS BY USER ROWID T (cr=1 pr=1 pw=0 time=0 us cost=1 size=90 card=1)'

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

FETCH #2:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3207308387,tim=1338037828094668

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

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

CLOSE #2:c=0,e=23,dep=0,type=0,tim=1338037828102717

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

 

*** 2012-05-26 21:10:36.532

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

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

 

 

从参数上看,我们看到db file sequential read动作只去访问了(file=1, block=90633)一个数据块。解析我们条件语句中的rowid取值,可以发现对应。

 

 

SQL> select dbms_rowid.rowid_relative_fno('AAAScPAABAAAWIJAAM') fno, dbms_rowid.rowid_block_number('AAAScPAABAAAWIJAAM') blocknum from dual;

 

       FNO   BLOCKNUM

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

         1      90633

 

 

由此,如果直接是rowid方式,Oracle Server Process会直接根据rowid的取值将结果数据块获取到。

 

5、总结

 

Oracle数据表访问的几种方式,是我们最常用的执行计划动作。深刻了解细节,有助于我们进一步分析Oracle行为方式和内核原理,进而可以更好的进行优化调优工作。

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

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

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7544239