A-A+
正确分析解读执行计划
该篇文档说明如何正确理解分析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
