A-A+

正确分析解读执行计划

2014年09月04日 PreformanceTuning 暂无评论 阅读 1,239 次

该篇文档说明如何正确理解分析sql语句的执行计划。
首先,扩展下二叉树相关知识:
前序遍历(DLR)
前序遍历也叫做先根遍历,可记做根左右。
前序遍历首先访问根结点然后遍历左子树,最后遍历右子树。在遍历左、右子树时,仍然先访问根结点,然后遍历左子树,最后遍历右子树。
若二叉树为空则结束返回,否则:
(1)访问根结点
(2)前序遍历左子树
(3)前序遍历右子树
注意的是:遍历左右子树时仍然采用前序遍历方法。

中序遍历(LDR)
中序遍历也叫做中根遍历,可记做左根右。
中序遍历首先遍历左子树,然后访问根结点,最后遍历右子树。在遍历左、右子树时,仍然先遍历左子树,再访问根结点,最后遍历右子树。即:
若二叉树为空则结束返回,否则:
(1)中序遍历左子树
(2)访问根结点
(3)中序遍历右子树。
注意的是:遍历左右子树时仍然采用中序遍历方法。

后序遍历(LRD)
后序遍历也叫做后根遍历,可记做左右根。
后序遍历首先遍历左子树,然后遍历右子树,最后访问根结点。在遍历左、右子树时,仍然先遍历左子树,再遍历右子树,最后访问根结点。即:
若二叉树为空则结束返回,否则:
(1)后序遍历左子树。
(2)后序遍历右子树。
(3)访问根结点。
注意的是:遍历左右子树时仍然采用后序遍历方法。

如下执行计划列出正确的执行顺序

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |  7372 |   746K|       |   298  (81)| 00:00:04 |
|   1 |  MINUS                         |         |       |       |       |            |          |
|   2 |   SORT UNIQUE                  |         |  7372 |   424K|   504K|   168   (2)| 00:00:03 |
|   3 |    CONCATENATION               |         |       |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| A       |     1 |    59 |       |    29   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | IDX_A_1 |    34 |       |       |   118   (0)| 00:00:02 |
|*  6 |     TABLE ACCESS FULL          | A       |  7371 |   424K|       |    31   (4)| 00:00:01 |
|   7 |   SORT UNIQUE                  |         |  5584 |   321K|   384K|   130   (3)| 00:00:02 |
|   8 |    CONCATENATION               |         |       |       |       |            |          |
|   9 |     TABLE ACCESS BY INDEX ROWID| B       |     1 |    59 |       |    22   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN          | IDX_B_1 |    26 |       |       |    90   (0)| 00:00:02 |
|* 11 |     TABLE ACCESS FULL          | B       |  5583 |   321K|       |    23   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

A、10-5-11-9-6-4-8-3-7-2-1-0
B、10-9-11-5-4-6-8-3-2-7-1-0
C、5-4-6-10-9-11-3-8-2-7-1-0
D、5-4-3-6-2-10-9-8-11-7-1-0
E、_________________________

以下为分析过程
数据库版本

sys@ora11gr2> @reg

ACTION_TIME          ACTION               NAMESPACE            VERSION            ID COMMENTS                       BUNDLE_SER
-------------------- -------------------- -------------------- ---------- ---------- ------------------------------ ----------
25-DEC-2012 13:24:04 APPLY                SERVER               11.2.0.3            4 PSU 11.2.0.3.4                 PSU

1 row selected.

建测试数据

sys@ora11gr2> create table a as select * from dba_segments;

Table created.

sys@ora11gr2> create table b as select * from dba_extents;

Table created.

sys@ora11gr2> insert into a select * from a;

5601 rows created.

sys@ora11gr2> /

11202 rows created.

sys@ora11gr2> /

22404 rows created.

sys@ora11gr2> /

44808 rows created.

sys@ora11gr2> /

89616 rows created.

sys@ora11gr2> /

179232 rows created.

sys@ora11gr2> /

358464 rows created.

sys@ora11gr2> commit;

Commit complete.

sys@ora11gr2> insert into b select * from b;

8450 rows created.

sys@ora11gr2> /

16900 rows created.

sys@ora11gr2> /

33800 rows created.

sys@ora11gr2> /

67600 rows created.

sys@ora11gr2> /

135200 rows created.

sys@ora11gr2> /

270400 rows created.

sys@ora11gr2> commit;

Commit complete.

sys@ora11gr2> create index idx_a on a(SEGMENT_NAME);

Index created.

sys@ora11gr2> create index idx_b on b(SEGMENT_NAME);

Index created.

sys@ora11gr2> create index idx_a_1 on a(OWNER);

Index created.

sys@ora11gr2> create index idx_b_1 on b(OWNER);

Index created.

sys@ora11gr2> alter system flush BUFFER_CACHE;

System altered.

使用10046分析执行计划

sys@ora11gr2> @10046_on

Session altered.


TRACE_FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_2973.trc

1 row selected.

sys@ora11gr2> set autot traceonly
sys@ora11gr2> select owner,segment_name from a where segment_name='SYS' OR owner='SYSMAN'
  2  minus
  3  select owner,segment_name from b where segment_name='SYS' OR owner='SYSMAN';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1933636850

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |  7372 |   746K|       |   298  (81)| 00:00:04 |
|   1 |  MINUS                         |         |       |       |       |            |          |
|   2 |   SORT UNIQUE                  |         |  7372 |   424K|   504K|   168   (2)| 00:00:03 |
|   3 |    CONCATENATION               |         |       |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| A       |     1 |    59 |       |    29   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | IDX_A_1 |    34 |       |       |   118   (0)| 00:00:02 |
|*  6 |     TABLE ACCESS FULL          | A       |  7371 |   424K|       |    31   (4)| 00:00:01 |
|   7 |   SORT UNIQUE                  |         |  5584 |   321K|   384K|   130   (3)| 00:00:02 |
|   8 |    CONCATENATION               |         |       |       |       |            |          |
|   9 |     TABLE ACCESS BY INDEX ROWID| B       |     1 |    59 |       |    22   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN          | IDX_B_1 |    26 |       |       |    90   (0)| 00:00:02 |
|* 11 |     TABLE ACCESS FULL          | B       |  5583 |   321K|       |    23   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("OWNER"='SYSMAN')
   6 - filter("SEGMENT_NAME"='SYS' AND LNNVL("OWNER"='SYSMAN'))
  10 - access("OWNER"='SYSMAN')
  11 - filter("SEGMENT_NAME"='SYS' AND LNNVL("OWNER"='SYSMAN'))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      18367  consistent gets
      18361  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

sys@ora11gr2> @10046_off

Session altered.

sys@ora11gr2> set autot off

查看10046的trace文件

=====================
PARSING IN CURSOR #139675261676696 len=157 dep=0 uid=0 oct=3 lid=0 tim=1407831155594924 hv=2227977636 ad='91e32af0' sqlid='4s1knck2csgd4'
select owner,segment_name from a where segment_name='SYS' OR owner='SYSMAN'
minus
select owner,segment_name from b where segment_name='SYS' OR owner='SYSMAN'
END OF STMT
PARSE #139675261676696:c=0,e=111,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1933636850,tim=1407831155594923
EXEC #139675261676696:c=0,e=130,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1933636850,tim=1407831155595128
WAIT #139675261676696: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1407831155595165
WAIT #139675261676696: nam='db file sequential read' ela= 16 file#=1 block#=102201 blocks=1 obj#=71854 tim=1407831155595257
WAIT #139675261676696: nam='db file sequential read' ela= 10 file#=1 block#=105840 blocks=1 obj#=71854 tim=1407831155595371
WAIT #139675261676696: nam='db file sequential read' ela= 7 file#=1 block#=105697 blocks=1 obj#=71854 tim=1407831155595434
WAIT #139675261676696: nam='db file sequential read' ela= 10 file#=1 block#=80056 blocks=1 obj#=71850 tim=1407831155595506
WAIT #139675261676696: nam='direct path read' ela= 639 file number=1 first dba=80057 block cnt=71 obj#=71850 tim=1407831155596842
WAIT #139675261676696: nam='direct path read' ela= 531 file number=1 first dba=80384 block cnt=40 obj#=71850 tim=1407831155597494
WAIT #139675261676696: nam='direct path read' ela= 70 file number=1 first dba=80512 block cnt=16 obj#=71850 tim=1407831155598051
WAIT #139675261676696: nam='direct path read' ela= 631 file number=1 first dba=80640 block cnt=128 obj#=71850 tim=1407831155598815
WAIT #139675261676696: nam='direct path read' ela= 316 file number=1 first dba=80768 block cnt=128 obj#=71850 tim=1407831155599241
WAIT #139675261676696: nam='direct path read' ela= 161 file number=1 first dba=80896 block cnt=128 obj#=71850 tim=1407831155599712
WAIT #139675261676696: nam='direct path read' ela= 263 file number=1 first dba=81024 block cnt=128 obj#=71850 tim=1407831155600274
WAIT #139675261676696: nam='direct path read' ela= 157 file number=1 first dba=81152 block cnt=128 obj#=71850 tim=1407831155600753
WAIT #139675261676696: nam='direct path read' ela= 177 file number=1 first dba=81280 block cnt=128 obj#=71850 tim=1407831155601216
WAIT #139675261676696: nam='direct path read' ela= 166 file number=1 first dba=81408 block cnt=128 obj#=71850 tim=1407831155601734
WAIT #139675261676696: nam='direct path read' ela= 163 file number=1 first dba=81536 block cnt=128 obj#=71850 tim=1407831155602192
WAIT #139675261676696: nam='direct path read' ela= 171 file number=1 first dba=81664 block cnt=128 obj#=71850 tim=1407831155602695
WAIT #139675261676696: nam='direct path read' ela= 156 file number=1 first dba=81792 block cnt=128 obj#=71850 tim=1407831155603135
WAIT #139675261676696: nam='direct path read' ela= 248 file number=1 first dba=81920 block cnt=128 obj#=71850 tim=1407831155603824
WAIT #139675261676696: nam='direct path read' ela= 319 file number=1 first dba=82048 block cnt=128 obj#=71850 tim=1407831155604742
WAIT #139675261676696: nam='direct path read' ela= 243 file number=1 first dba=82176 block cnt=128 obj#=71850 tim=1407831155605354
WAIT #139675261676696: nam='direct path read' ela= 172 file number=1 first dba=82304 block cnt=128 obj#=71850 tim=1407831155605864
WAIT #139675261676696: nam='direct path read' ela= 251 file number=1 first dba=82432 block cnt=128 obj#=71850 tim=1407831155606412
WAIT #139675261676696: nam='direct path read' ela= 161 file number=1 first dba=82560 block cnt=128 obj#=71850 tim=1407831155606874
WAIT #139675261676696: nam='direct path read' ela= 163 file number=1 first dba=82688 block cnt=128 obj#=71850 tim=1407831155607320
WAIT #139675261676696: nam='direct path read' ela= 163 file number=1 first dba=82816 block cnt=128 obj#=71850 tim=1407831155607823
WAIT #139675261676696: nam='direct path read' ela= 188 file number=1 first dba=82944 block cnt=128 obj#=71850 tim=1407831155608303
WAIT #139675261676696: nam='direct path read' ela= 170 file number=1 first dba=83072 block cnt=128 obj#=71850 tim=1407831155609012
WAIT #139675261676696: nam='direct path read' ela= 212 file number=1 first dba=83200 block cnt=128 obj#=71850 tim=1407831155609519
WAIT #139675261676696: nam='direct path read' ela= 274 file number=1 first dba=83328 block cnt=128 obj#=71850 tim=1407831155610085
WAIT #139675261676696: nam='direct path read' ela= 289 file number=1 first dba=83456 block cnt=128 obj#=71850 tim=1407831155610744
WAIT #139675261676696: nam='direct path read' ela= 464 file number=1 first dba=83584 block cnt=128 obj#=71850 tim=1407831155611696
WAIT #139675261676696: nam='direct path read' ela= 257 file number=1 first dba=83712 block cnt=128 obj#=71850 tim=1407831155612264
WAIT #139675261676696: nam='direct path read' ela= 274 file number=1 first dba=83840 block cnt=128 obj#=71850 tim=1407831155612852
WAIT #139675261676696: nam='direct path read' ela= 319 file number=1 first dba=83968 block cnt=128 obj#=71850 tim=1407831155613468
WAIT #139675261676696: nam='direct path read' ela= 419 file number=1 first dba=84096 block cnt=128 obj#=71850 tim=1407831155614184
WAIT #139675261676696: nam='direct path read' ela= 247 file number=1 first dba=84224 block cnt=128 obj#=71850 tim=1407831155614755
WAIT #139675261676696: nam='direct path read' ela= 348 file number=1 first dba=84352 block cnt=128 obj#=71850 tim=1407831155615397
WAIT #139675261676696: nam='direct path read' ela= 310 file number=1 first dba=84480 block cnt=128 obj#=71850 tim=1407831155616005
WAIT #139675261676696: nam='direct path read' ela= 427 file number=1 first dba=84608 block cnt=128 obj#=71850 tim=1407831155616723
WAIT #139675261676696: nam='direct path read' ela= 242 file number=1 first dba=84736 block cnt=128 obj#=71850 tim=1407831155617268
WAIT #139675261676696: nam='direct path read' ela= 383 file number=1 first dba=84864 block cnt=128 obj#=71850 tim=1407831155617964
WAIT #139675261676696: nam='direct path read' ela= 368 file number=1 first dba=84992 block cnt=128 obj#=71850 tim=1407831155619042
WAIT #139675261676696: nam='direct path read' ela= 316 file number=1 first dba=85120 block cnt=128 obj#=71850 tim=1407831155619438
WAIT #139675261676696: nam='direct path read' ela= 300 file number=1 first dba=85248 block cnt=128 obj#=71850 tim=1407831155620036
WAIT #139675261676696: nam='direct path read' ela= 279 file number=1 first dba=85376 block cnt=128 obj#=71850 tim=1407831155620645
WAIT #139675261676696: nam='direct path read' ela= 290 file number=1 first dba=85504 block cnt=128 obj#=71850 tim=1407831155621230
WAIT #139675261676696: nam='direct path read' ela= 288 file number=1 first dba=85632 block cnt=128 obj#=71850 tim=1407831155621839
WAIT #139675261676696: nam='direct path read' ela= 316 file number=1 first dba=85760 block cnt=128 obj#=71850 tim=1407831155622445
WAIT #139675261676696: nam='direct path read' ela= 257 file number=1 first dba=85888 block cnt=128 obj#=71850 tim=1407831155623004
WAIT #139675261676696: nam='direct path read' ela= 403 file number=1 first dba=86016 block cnt=128 obj#=71850 tim=1407831155623696
WAIT #139675261676696: nam='direct path read' ela= 268 file number=1 first dba=86144 block cnt=128 obj#=71850 tim=1407831155624288
WAIT #139675261676696: nam='direct path read' ela= 280 file number=1 first dba=86272 block cnt=128 obj#=71850 tim=1407831155624892
WAIT #139675261676696: nam='direct path read' ela= 326 file number=1 first dba=86400 block cnt=128 obj#=71850 tim=1407831155625520
WAIT #139675261676696: nam='direct path read' ela= 230 file number=1 first dba=86528 block cnt=128 obj#=71850 tim=1407831155626042
WAIT #139675261676696: nam='direct path read' ela= 315 file number=1 first dba=86656 block cnt=128 obj#=71850 tim=1407831155626685
WAIT #139675261676696: nam='direct path read' ela= 376 file number=1 first dba=86784 block cnt=128 obj#=71850 tim=1407831155627369
WAIT #139675261676696: nam='direct path read' ela= 501 file number=1 first dba=86912 block cnt=128 obj#=71850 tim=1407831155628641
WAIT #139675261676696: nam='direct path read' ela= 313 file number=1 first dba=87040 block cnt=128 obj#=71850 tim=1407831155629285
WAIT #139675261676696: nam='direct path read' ela= 273 file number=1 first dba=87168 block cnt=128 obj#=71850 tim=1407831155629919
WAIT #139675261676696: nam='direct path read' ela= 446 file number=1 first dba=87296 block cnt=128 obj#=71850 tim=1407831155630668
WAIT #139675261676696: nam='direct path read' ela= 250 file number=1 first dba=87424 block cnt=128 obj#=71850 tim=1407831155631241
WAIT #139675261676696: nam='direct path read' ela= 337 file number=1 first dba=87552 block cnt=128 obj#=71850 tim=1407831155631886
WAIT #139675261676696: nam='direct path read' ela= 271 file number=1 first dba=87680 block cnt=128 obj#=71850 tim=1407831155632461
WAIT #139675261676696: nam='direct path read' ela= 171 file number=1 first dba=87808 block cnt=128 obj#=71850 tim=1407831155632927
WAIT #139675261676696: nam='direct path read' ela= 324 file number=1 first dba=87936 block cnt=128 obj#=71850 tim=1407831155633536
WAIT #139675261676696: nam='direct path read' ela= 390 file number=1 first dba=88064 block cnt=128 obj#=71850 tim=1407831155634229
WAIT #139675261676696: nam='direct path read' ela= 247 file number=1 first dba=88192 block cnt=128 obj#=71850 tim=1407831155634848
WAIT #139675261676696: nam='direct path read' ela= 464 file number=1 first dba=88320 block cnt=128 obj#=71850 tim=1407831155635616
WAIT #139675261676696: nam='direct path read' ela= 278 file number=1 first dba=88448 block cnt=128 obj#=71850 tim=1407831155636368
WAIT #139675261676696: nam='direct path read' ela= 298 file number=1 first dba=88576 block cnt=128 obj#=71850 tim=1407831155636959
WAIT #139675261676696: nam='direct path read' ela= 489 file number=1 first dba=88704 block cnt=128 obj#=71850 tim=1407831155637748
WAIT #139675261676696: nam='direct path read' ela= 286 file number=1 first dba=88832 block cnt=128 obj#=71850 tim=1407831155638367
WAIT #139675261676696: nam='direct path read' ela= 286 file number=1 first dba=88960 block cnt=128 obj#=71850 tim=1407831155638965
WAIT #139675261676696: nam='direct path read' ela= 368 file number=1 first dba=89088 block cnt=128 obj#=71850 tim=1407831155639636
WAIT #139675261676696: nam='direct path read' ela= 218 file number=1 first dba=89216 block cnt=128 obj#=71850 tim=1407831155640144
WAIT #139675261676696: nam='direct path read' ela= 437 file number=1 first dba=89344 block cnt=128 obj#=71850 tim=1407831155640873
WAIT #139675261676696: nam='direct path read' ela= 527 file number=1 first dba=89472 block cnt=128 obj#=71850 tim=1407831155641677
WAIT #139675261676696: nam='direct path read' ela= 224 file number=1 first dba=89600 block cnt=128 obj#=71850 tim=1407831155642168
WAIT #139675261676696: nam='direct path read' ela= 304 file number=1 first dba=89728 block cnt=128 obj#=71850 tim=1407831155642770
WAIT #139675261676696: nam='direct path read' ela= 213 file number=1 first dba=89856 block cnt=128 obj#=71850 tim=1407831155643238
WAIT #139675261676696: nam='direct path read' ela= 332 file number=1 first dba=89984 block cnt=128 obj#=71850 tim=1407831155643870
WAIT #139675261676696: nam='direct path read' ela= 325 file number=1 first dba=90112 block cnt=128 obj#=71850 tim=1407831155644454
WAIT #139675261676696: nam='direct path read' ela= 316 file number=1 first dba=90240 block cnt=128 obj#=71850 tim=1407831155645023
WAIT #139675261676696: nam='direct path read' ela= 483 file number=1 first dba=90368 block cnt=128 obj#=71850 tim=1407831155645759
WAIT #139675261676696: nam='direct path read' ela= 269 file number=1 first dba=90496 block cnt=128 obj#=71850 tim=1407831155646315
WAIT #139675261676696: nam='direct path read' ela= 386 file number=1 first dba=90624 block cnt=128 obj#=71850 tim=1407831155647023
WAIT #139675261676696: nam='direct path read' ela= 482 file number=1 first dba=90752 block cnt=128 obj#=71850 tim=1407831155647792
WAIT #139675261676696: nam='direct path read' ela= 270 file number=1 first dba=90880 block cnt=128 obj#=71850 tim=1407831155648445
WAIT #139675261676696: nam='direct path read' ela= 303 file number=1 first dba=91008 block cnt=128 obj#=71850 tim=1407831155649039
WAIT #139675261676696: nam='direct path read' ela= 304 file number=1 first dba=91136 block cnt=128 obj#=71850 tim=1407831155649634
WAIT #139675261676696: nam='direct path read' ela= 219 file number=1 first dba=91264 block cnt=128 obj#=71850 tim=1407831155650141
WAIT #139675261676696: nam='direct path read' ela= 260 file number=1 first dba=91392 block cnt=128 obj#=71850 tim=1407831155650693
WAIT #139675261676696: nam='direct path read' ela= 313 file number=1 first dba=91520 block cnt=128 obj#=71850 tim=1407831155651257
WAIT #139675261676696: nam='direct path read' ela= 350 file number=1 first dba=91648 block cnt=128 obj#=71850 tim=1407831155652029
WAIT #139675261676696: nam='direct path read' ela= 366 file number=1 first dba=91776 block cnt=128 obj#=71850 tim=1407831155652672
WAIT #139675261676696: nam='direct path read' ela= 255 file number=1 first dba=91904 block cnt=128 obj#=71850 tim=1407831155653188
WAIT #139675261676696: nam='direct path read' ela= 372 file number=1 first dba=92032 block cnt=128 obj#=71850 tim=1407831155653849
WAIT #139675261676696: nam='direct path read' ela= 319 file number=1 first dba=92160 block cnt=128 obj#=71850 tim=1407831155654435
WAIT #139675261676696: nam='direct path read' ela= 317 file number=1 first dba=92288 block cnt=128 obj#=71850 tim=1407831155655016
WAIT #139675261676696: nam='direct path read' ela= 318 file number=1 first dba=92416 block cnt=128 obj#=71850 tim=1407831155655589
WAIT #139675261676696: nam='direct path read' ela= 296 file number=1 first dba=92544 block cnt=128 obj#=71850 tim=1407831155656137
WAIT #139675261676696: nam='direct path read' ela= 241 file number=1 first dba=92672 block cnt=128 obj#=71850 tim=1407831155656687
WAIT #139675261676696: nam='direct path read' ela= 304 file number=1 first dba=92800 block cnt=128 obj#=71850 tim=1407831155657246
WAIT #139675261676696: nam='direct path read' ela= 326 file number=1 first dba=92928 block cnt=128 obj#=71850 tim=1407831155657888
WAIT #139675261676696: nam='direct path read' ela= 426 file number=1 first dba=93056 block cnt=128 obj#=71850 tim=1407831155658641
WAIT #139675261676696: nam='direct path read' ela= 361 file number=1 first dba=93184 block cnt=128 obj#=71850 tim=1407831155659324
WAIT #139675261676696: nam='direct path read' ela= 348 file number=1 first dba=93312 block cnt=128 obj#=71850 tim=1407831155660014
WAIT #139675261676696: nam='direct path read' ela= 317 file number=1 first dba=93440 block cnt=128 obj#=71850 tim=1407831155660640
WAIT #139675261676696: nam='direct path read' ela= 262 file number=1 first dba=93568 block cnt=128 obj#=71850 tim=1407831155661198
WAIT #139675261676696: nam='direct path read' ela= 200 file number=1 first dba=93696 block cnt=89 obj#=71850 tim=1407831155661697
WAIT #139675261676696: nam='db file sequential read' ela= 13 file#=1 block#=104633 blocks=1 obj#=71855 tim=1407831155662180
WAIT #139675261676696: nam='db file sequential read' ela= 10 file#=1 block#=107482 blocks=1 obj#=71855 tim=1407831155662229
WAIT #139675261676696: nam='db file sequential read' ela= 9 file#=1 block#=107189 blocks=1 obj#=71855 tim=1407831155662269
WAIT #139675261676696: nam='db file sequential read' ela= 9 file#=1 block#=80424 blocks=1 obj#=71851 tim=1407831155662323
WAIT #139675261676696: nam='direct path read' ela= 222 file number=1 first dba=80425 block cnt=87 obj#=71851 tim=1407831155662778
WAIT #139675261676696: nam='direct path read' ela= 126 file number=1 first dba=80528 block cnt=40 obj#=71851 tim=1407831155662952
WAIT #139675261676696: nam='direct path read' ela= 491 file number=1 first dba=93824 block cnt=128 obj#=71851 tim=1407831155663713
WAIT #139675261676696: nam='direct path read' ela= 288 file number=1 first dba=93952 block cnt=128 obj#=71851 tim=1407831155664200
WAIT #139675261676696: nam='direct path read' ela= 338 file number=1 first dba=94080 block cnt=128 obj#=71851 tim=1407831155664946
WAIT #139675261676696: nam='direct path read' ela= 324 file number=1 first dba=94208 block cnt=128 obj#=71851 tim=1407831155665630
WAIT #139675261676696: nam='direct path read' ela= 266 file number=1 first dba=94336 block cnt=128 obj#=71851 tim=1407831155666251
WAIT #139675261676696: nam='direct path read' ela= 256 file number=1 first dba=94464 block cnt=128 obj#=71851 tim=1407831155666926
WAIT #139675261676696: nam='direct path read' ela= 567 file number=1 first dba=94592 block cnt=128 obj#=71851 tim=1407831155667892
WAIT #139675261676696: nam='direct path read' ela= 344 file number=1 first dba=94720 block cnt=128 obj#=71851 tim=1407831155668808
WAIT #139675261676696: nam='direct path read' ela= 315 file number=1 first dba=94848 block cnt=128 obj#=71851 tim=1407831155669518
WAIT #139675261676696: nam='direct path read' ela= 339 file number=1 first dba=94976 block cnt=128 obj#=71851 tim=1407831155670212
WAIT #139675261676696: nam='direct path read' ela= 274 file number=1 first dba=95104 block cnt=128 obj#=71851 tim=1407831155670881
WAIT #139675261676696: nam='direct path read' ela= 447 file number=1 first dba=95232 block cnt=128 obj#=71851 tim=1407831155671722
WAIT #139675261676696: nam='direct path read' ela= 373 file number=1 first dba=95360 block cnt=128 obj#=71851 tim=1407831155672513
WAIT #139675261676696: nam='direct path read' ela= 217 file number=1 first dba=95488 block cnt=128 obj#=71851 tim=1407831155673093
WAIT #139675261676696: nam='direct path read' ela= 263 file number=1 first dba=95616 block cnt=128 obj#=71851 tim=1407831155673751
WAIT #139675261676696: nam='direct path read' ela= 273 file number=1 first dba=95744 block cnt=128 obj#=71851 tim=1407831155674378
WAIT #139675261676696: nam='direct path read' ela= 306 file number=1 first dba=95872 block cnt=128 obj#=71851 tim=1407831155675052
WAIT #139675261676696: nam='direct path read' ela= 266 file number=1 first dba=96000 block cnt=128 obj#=71851 tim=1407831155675704
WAIT #139675261676696: nam='direct path read' ela= 266 file number=1 first dba=96128 block cnt=128 obj#=71851 tim=1407831155676311
WAIT #139675261676696: nam='direct path read' ela= 165 file number=1 first dba=96256 block cnt=128 obj#=71851 tim=1407831155676894
WAIT #139675261676696: nam='direct path read' ela= 324 file number=1 first dba=96384 block cnt=128 obj#=71851 tim=1407831155677581
WAIT #139675261676696: nam='direct path read' ela= 312 file number=1 first dba=96512 block cnt=128 obj#=71851 tim=1407831155678288
WAIT #139675261676696: nam='direct path read' ela= 277 file number=1 first dba=96640 block cnt=128 obj#=71851 tim=1407831155679006
WAIT #139675261676696: nam='direct path read' ela= 316 file number=1 first dba=96768 block cnt=128 obj#=71851 tim=1407831155679673
WAIT #139675261676696: nam='direct path read' ela= 247 file number=1 first dba=96896 block cnt=128 obj#=71851 tim=1407831155680276
WAIT #139675261676696: nam='direct path read' ela= 340 file number=1 first dba=97024 block cnt=128 obj#=71851 tim=1407831155681067
WAIT #139675261676696: nam='direct path read' ela= 261 file number=1 first dba=97152 block cnt=128 obj#=71851 tim=1407831155681731
WAIT #139675261676696: nam='direct path read' ela= 287 file number=1 first dba=97280 block cnt=128 obj#=71851 tim=1407831155682386
WAIT #139675261676696: nam='direct path read' ela= 301 file number=1 first dba=97408 block cnt=128 obj#=71851 tim=1407831155683056
WAIT #139675261676696: nam='direct path read' ela= 269 file number=1 first dba=97536 block cnt=128 obj#=71851 tim=1407831155683741
WAIT #139675261676696: nam='direct path read' ela= 229 file number=1 first dba=97664 block cnt=128 obj#=71851 tim=1407831155684342
WAIT #139675261676696: nam='direct path read' ela= 283 file number=1 first dba=97792 block cnt=128 obj#=71851 tim=1407831155685007
WAIT #139675261676696: nam='direct path read' ela= 244 file number=1 first dba=97920 block cnt=128 obj#=71851 tim=1407831155685653
WAIT #139675261676696: nam='direct path read' ela= 275 file number=1 first dba=98048 block cnt=128 obj#=71851 tim=1407831155686302
WAIT #139675261676696: nam='direct path read' ela= 259 file number=1 first dba=98176 block cnt=128 obj#=71851 tim=1407831155687293
WAIT #139675261676696: nam='direct path read' ela= 341 file number=1 first dba=98304 block cnt=128 obj#=71851 tim=1407831155687679
WAIT #139675261676696: nam='direct path read' ela= 271 file number=1 first dba=98432 block cnt=128 obj#=71851 tim=1407831155688329
WAIT #139675261676696: nam='direct path read' ela= 304 file number=1 first dba=98560 block cnt=128 obj#=71851 tim=1407831155689137
WAIT #139675261676696: nam='direct path read' ela= 209 file number=1 first dba=98688 block cnt=90 obj#=71851 tim=1407831155689771
FETCH #139675261676696:c=94985,e=95168,p=18361,cr=18367,cu=0,mis=0,r=0,dep=0,og=1,plh=1933636850,tim=1407831155690354
STAT #139675261676696 id=1 cnt=0 pid=0 pos=1 obj=0 op='MINUS  (cr=18367 pr=18361 pw=0 time=95220 us)'
STAT #139675261676696 id=2 cnt=0 pid=1 pos=1 obj=0 op='SORT UNIQUE (cr=13279 pr=13276 pw=0 time=66974 us cost=168 size=434948 card=7372)'
STAT #139675261676696 id=3 cnt=0 pid=2 pos=1 obj=0 op='CONCATENATION  (cr=13279 pr=13276 pw=0 time=66960 us)'
STAT #139675261676696 id=4 cnt=0 pid=3 pos=1 obj=71850 op='TABLE ACCESS BY INDEX ROWID A (cr=3 pr=3 pw=0 time=309 us cost=29 size=59 card=1)'
STAT #139675261676696 id=5 cnt=0 pid=4 pos=1 obj=71854 op='INDEX RANGE SCAN IDX_A_1 (cr=3 pr=3 pw=0 time=281 us cost=118 size=0 card=34)'
STAT #139675261676696 id=6 cnt=0 pid=3 pos=2 obj=71850 op='TABLE ACCESS FULL A (cr=13276 pr=13273 pw=0 time=66639 us cost=31 size=434889 card=7371)'
STAT #139675261676696 id=7 cnt=0 pid=1 pos=2 obj=0 op='SORT UNIQUE (cr=5088 pr=5085 pw=0 time=28239 us cost=130 size=329456 card=5584)'
STAT #139675261676696 id=8 cnt=0 pid=7 pos=1 obj=0 op='CONCATENATION  (cr=5088 pr=5085 pw=0 time=28222 us)'
STAT #139675261676696 id=9 cnt=0 pid=8 pos=1 obj=71851 op='TABLE ACCESS BY INDEX ROWID B (cr=3 pr=3 pw=0 time=166 us cost=22 size=59 card=1)'
STAT #139675261676696 id=10 cnt=0 pid=9 pos=1 obj=71855 op='INDEX RANGE SCAN IDX_B_1 (cr=3 pr=3 pw=0 time=156 us cost=90 size=0 card=26)'
STAT #139675261676696 id=11 cnt=0 pid=8 pos=2 obj=71851 op='TABLE ACCESS FULL B (cr=5085 pr=5082 pw=0 time=28051 us cost=23 size=329397 card=5583)'
WAIT #139675261676696: nam='SQL*Net message from client' ela= 279 driver id=1650815232 #bytes=1 p3=0 obj#=71851 tim=1407831155690867
*** SESSION ID:(1.23) 2014-08-12 16:12:35.691

CLOSE #139675261676696:c=0,e=10,dep=0,type=0,tim=1407831155691886
=====================

块对象的访问顺序:
71854->71850->71855->71851

sys@ora11gr2> col OBJECT_NAME for a10
sys@ora11gr2> select OWNER,OBJECT_NAME,OBJECT_TYPE,DATA_OBJECT_ID from dba_objects where DATA_OBJECT_ID in (71854,71850,71855,71851);

OWNER                          OBJECT_NAM OBJECT_TYPE         DATA_OBJECT_ID
------------------------------ ---------- ------------------- --------------
SYS                            A          TABLE                        71850
SYS                            B          TABLE                        71851
SYS                            IDX_A_1    INDEX                        71854
SYS                            IDX_B_1    INDEX                        71855

4 rows selected.

翻译后块对象的访问顺序就是:

IDX_A_1	 ->	A	->	IDX_B_1 	->	B
5	 ->	4	->	10		->	9

结合执行计划,10046块访问顺序及二叉树,得出如下二叉树:

		0
		|
		1
             /     \
	    2       7
           /       /
          3       8
         / \     / \
        4   6   9   11
       /       /
      5       10

二叉树LRD遍历(后序遍历)路径为:5-4-6-3-2-10-9-11-8-7-1-0
所以正确的执行顺序也就是:5-4-6-3-2-10-9-11-8-7-1-0

标签:

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: