Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 如何扫描索引根块，枝块，叶子块
发新帖
wshxgxiaoli

3

积分

0

好友

6

主题

[升级] 如何扫描索引根块，枝块，叶子块

1#
发表于 2013-3-6 10:55:42 | 查看: 4610| 回复: 6
您好！
       我这边在思考这么一个问题， 在索引范围扫描时， 比如:
  1. select * from employees where employee_id<150;
复制代码
我想知道，扫描索引时，先由索引段头找到索引根节点，然后再找到BRENCH块，然后再去找索引块，索引是有序排序的，是从1开始升序查找还是从150降序查找。
我的追踪过程如下：
  1. SQL> set arraysize 1000;
  2. SQL> alter system flush buffer_cache;

  4. System altered.

  6. SQL> alter system flush shared_pool;

  8. System altered.

  10. SQL> alter session set events '10046 trace name context forever,level 8';

  12. Session altered.

  14. SQL> select * from employees where employee_id<150;

  16. SQL> alter session set events '10046 trace name context off'
  17.   2  ;

  19. Session altered.
  20. 查看追踪文件：
  21. select * from employees where employee_id<150
  22. END OF STMT
  23. PARSE #6:c=45992,e=54198,p=21,cr=231,cu=0,mis=1,r=0,dep=0,og=1,plh=603312277,tim=1362534550029989
  24. EXEC #6:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=603312277,tim=1362534550030081
  25. WAIT #6: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=383 tim=1362534550030161
  26. WAIT #6: nam='Disk file operations I/O' ela= 111 FileOperation=2 fileno=5 filetype=2 obj#=73955 tim=1362534550030403
  27. WAIT #6: nam='db file sequential read' ela= 24 file#=5 block#=219 blocks=1 obj#=73955 tim=1362534550030475
  28. WAIT #6: nam='db file scattered read' ela= 118 file#=5 block#=200 blocks=8 obj#=73953 tim=1362534550030772
  29. FETCH #6:c=1000,e=634,p=9,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=603312277,tim=1362534550030835
  30. WAIT #6: nam='SQL*Net message from client' ela= 585 driver id=1650815232 #bytes=1 p3=0 obj#=73953 tim=1362534550031465
  31. WAIT #6: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=73953 tim=1362534550031525
  32. FETCH #6:c=0,e=122,p=0,cr=2,cu=0,mis=0,r=49,dep=0,og=1,plh=603312277,tim=1362534550031626
  33. STAT #6 id=1 cnt=50 pid=0 pos=1 obj=73953 op='TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=4 pr=9 pw=0 time=0 us cost=2 size=3450 card=50)'
  34. STAT #6 id=2 cnt=50 pid=1 pos=1 obj=73955 op='INDEX RANGE SCAN EMP_EMP_ID_PK (cr=2 pr=1 pw=0 time=392 us cost=1 size=0 card=50)'

  36. *** 2013-03-06 09:49:20.903
  37. WAIT #6: nam='SQL*Net message from client' ela= 10871651 driver id=1650815232 #bytes=1 p3=0 obj#=73953 tim=1362534560903363
  38. CLOSE #6:c=0,e=20,dep=0,type=0,tim=1362534560903474
复制代码
求指教！
2#
发表于 2013-3-6 12:36:40
如果创建索引时没有指定任何操作，比如desc等，默认是升序的。也就是从<150的最小值开始，一直找到<150这个条件为止。
如果索引指定了desc，进行反向操作、
如果指定hint的index_desc，也是反向操作。

回复 只看该作者 道具 举报

3#
发表于 2013-3-6 16:59:26
willing_ox 发表于 2013-3-6 12:36
如果创建索引时没有指定任何操作，比如desc等，默认是升序的。也就是从

谢谢您的回复， 不过我想得到的是如何去证明这些东西，通过追踪的方式！  

回复 只看该作者 道具 举报

4#
发表于 2013-3-6 17:19:01
在你的测试库上开启   _trace_pin_time=1
会产生很多的数据，小心。

回复 只看该作者 道具 举报

5#
发表于 2013-3-6 21:51:55
还是比较容易证明的，详见下面的日志：
  1. SQL> create table macleantest as select * from dba_objects;

  3. Table created.

  5. SQL> create unique index pk_test on macleantest(object_id);

  7. Index created.

  9. SQL>
  10. SQL> alter system flush buffer_cache;

  12. System altered.


  15. SQL> oradebug setmypid
  16. Statement processed.
  17. SQL> oradebug event 10046 trace name context forever,level 8;
  18. Statement processed.
  19. SQL> alter system flush buffer_cache;

  21. System altered.


  24. alter session set optimizer_dynamic_sampling=0;

  26. SQL>
  27. SQL> set autotrace on;  
  28. SQL>
  29. SQL>
  30. SQL> set autotrace traceonly;
  31. SQL>



  35. SQL> select /*+ rule */ 1 from macleantest where object_id between 2000 and 2500;

  37. 501 rows selected.


  40. Execution Plan
  41. ----------------------------------------------------------
  42. Plan hash value: 2895836267

  44. ------------------------------------
  45. | Id  | Operation        | Name    |
  46. ------------------------------------
  47. |   0 | SELECT STATEMENT |         |
  48. |*  1 |  INDEX RANGE SCAN| PK_TEST |
  49. ------------------------------------

  51. Predicate Information (identified by operation id):
  52. ---------------------------------------------------

  54.    1 - access("OBJECT_ID">=2000 AND "OBJECT_ID"<=2500)

  56. Note
  57. -----
  58.    - rule based optimizer used (consider using cbo)


  61. Statistics
  62. ----------------------------------------------------------
  63.           0  recursive calls
  64.           0  db block gets
  65.           4  consistent gets
  66.           3  physical reads
  67.           0  redo size
  68.        3076  bytes sent via SQL*Net to client
  69.         520  bytes received via SQL*Net from client
  70.           2  SQL*Net roundtrips to/from client
  71.           0  sorts (memory)
  72.           0  sorts (disk)
  73.         501  rows processed


  76.                
  77.                
  78. SQL> oradebug tracefile_name
  79. /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ora_21065.trc               
  80.                
  81.                
  82.                
  83. PARSING IN CURSOR #140451788454424 len=75 dep=0 uid=0 oct=3 lid=0 tim=1362577258748047 hv=1757295484 ad='99cb9058' sqlid='3j93kt9nbwcvw'
  84. select /*+ rule */ 1 from macleantest where object_id between 2000 and 2500
  85. END OF STMT
  86. PARSE #140451788454424:c=0,e=418,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,plh=2895836267,tim=1362577258748040
  87. EXEC #140451788454424:c=1000,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,plh=2895836267,tim=1362577258748172
  88. WAIT #140451788454424: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=536 tim=1362577258748226
  89. WAIT #140451788454424: nam='db file sequential read' ela= 368 file#=1 block#=37921 blocks=1 obj#=17398 tim=1362577258748947
  90. WAIT #140451788454424: nam='db file sequential read' ela= 286 file#=1 block#=37925 blocks=1 obj#=17398 tim=1362577258749418
  91. FETCH #140451788454424:c=999,e=1319,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=3,plh=2895836267,tim=1362577258749583
  92. WAIT #140451788454424: nam='SQL*Net message from client' ela= 572 driver id=1650815232 #bytes=1 p3=0 obj#=17398 tim=1362577258750229
  93. WAIT #140451788454424: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=17398 tim=1362577258750311
  94. WAIT #140451788454424: nam='db file sequential read' ela= 448 file#=1 block#=37926 blocks=1 obj#=17398 tim=1362577258751024
  95. FETCH #140451788454424:c=1000,e=1188,p=1,cr=2,cu=0,mis=0,r=500,dep=0,og=3,plh=2895836267,tim=1362577258751470
  96. STAT #140451788454424 id=1 cnt=501 pid=0 pos=1 obj=17398 op='INDEX RANGE SCAN PK_TEST (cr=4 pr=3 pw=0 time=3962 us)'
  97. WAIT #140451788454424: nam='SQL*Net message from client' ela= 966 driver id=1650815232 #bytes=1 p3=0 obj#=17398 tim=1362577258752619
  98. *** SESSION ID:(480.1755) 2013-03-06 08:40:58.755               



  102. WAIT #140451788454424: nam='db file sequential read' ela= 368 file#=1 block#=37921 blocks=1 obj#=17398 tim=1362577258748947
  103. WAIT #140451788454424: nam='db file sequential read' ela= 286 file#=1 block#=37925 blocks=1 obj#=17398 tim=1362577258749418
  104. WAIT #140451788454424: nam='db file sequential read' ela= 448 file#=1 block#=37926 blocks=1 obj#=17398 tim=1362577258751024


  107. file#=1 block#=37921


  110. seg/obj: 0x43f6  csc: 0x00.341a82  itc: 1  flg: -  typ: 2 - INDEX
  111.      fsl: 0  fnx: 0x0 ver: 0x01

  113. Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
  114. 0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00341a82
  115. Branch block dump
  116. =================
  117. header address 140229141850692=0x7f89a42fea44
  118. kdxcolev 1



  122. kdxcolev 1 ==》　index level (0 represents leaf blocks)



  126. file#=1 block#=37925　＝＝》叶子块




  131. Object id on Block? Y
  132. seg/obj: 0x43f6  csc: 0x00.341a82  itc: 2  flg: -  typ: 2 - INDEX
  133.      fsl: 0  fnx: 0x0 ver: 0x01

  135. Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
  136. 0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
  137. 0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00341a82
  138. Leaf block dump
  139. ===============
  140. header address 140155556162140=0x7f7882236a5c
  141. kdxcolev 0
  142. KDXCOLEV Flags = - - -
  143. kdxcolok 0
  144. kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
  145. kdxconco 1
  146. kdxcosdc 0
  147. kdxconro 513
  148. kdxcofbo 1062=0x426
  149. kdxcofeo 1881=0x759
  150. kdxcoavs 819
  151. kdxlespl 0
  152. kdxlende 0
  153. kdxlenxt 4232230=0x409426
  154. kdxleprv 4232228=0x409424
  155.                
  156.                
  157.                
  158. row#451[2613] flag: ------, lock: 0, len=11, data:(6):  00 40 93 3a 00 1b
  159. col 0; len 2; (2):  c2 15


  162. =====》　 c2 15 代表 2000


  165. SQL> select dump(2000,16) from dual;

  167. DUMP(2000,16)
  168. ------------------
  169. Typ=2 Len=2: c2,15  



  173. file#=1 block#=37926 ==》叶子快




  178. Object id on Block? Y
  179. seg/obj: 0x43f6  csc: 0x00.341a82  itc: 2  flg: -  typ: 2 - INDEX
  180.      fsl: 0  fnx: 0x0 ver: 0x01

  182. Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
  183. 0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
  184. 0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00341a82
  185. Leaf block dump
  186. ===============
  187. header address 140458789124700=0x7fbf1c3b3a5c
  188. kdxcolev 0
  189. KDXCOLEV Flags = - - -
  190. kdxcolok 0
  191. kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
  192. kdxconco 1
  193. kdxcosdc 0
  194. kdxconro 513
  195. kdxcofbo 1062=0x426
  196. kdxcofeo 1881=0x759
  197. kdxcoavs 819
  198. kdxlespl 0
  199. kdxlende 0



  203. SQL> select dump(2500,16) from dual;

  205. DUMP(2500,16)
  206. ------------------
  207. Typ=2 Len=2: c2,1a



  211. row#438[2769] flag: ------, lock: 0, len=11, data:(6):  00 40 93 40 00 38
  212. col 0; len 2; (2):  c2 1a         ==》 2500
  213.                                                                                              
复制代码

回复 只看该作者 道具 举报

6#
发表于 2013-3-7 11:35:46
Maclean Liu(刘相兵 发表于 2013-3-6 21:51
还是比较容易证明的，详见下面的日志：

谢谢刘大！   我也照着做了次分析。 效果相当好！  再次感谢！

回复 只看该作者 道具 举报

7#
发表于 2013-3-7 11:36:30
dla001 发表于 2013-3-6 17:19
在你的测试库上开启   _trace_pin_time=1
会产生很多的数据，小心。

谢谢您的回复， 已照刘大的文章做了次， 效果非常好！

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-16 10:52 , Processed in 0.049017 second(s), 20 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569