Oracle CBO 术语简写列表

Maclean Liu(刘相兵

Oracle CBO 术语简写列表  optimizer CBO terms

cardinality (CDN)
Legend
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
SLP - select list pruning
DP - distinct placement
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
  1: no partitioning required
  2: value partitioned
  4: right is random (round-robin)
  128: left is random (round-robin)
  8: broadcast right and partition left
  16: broadcast left and partition right
  32: partition left using partitioning of right
  64: partition right using partitioning of left
  256: run the join in serial
  0: invalid distribution method
sel - selectivity
ptn - partition

lunar

好帖，赞一个

linyiyong

ML，太给力了！谢谢。

Teddy.huang

收藏，难得不要钱的

xin_chy

赞一个，谢谢分享！

sangli00

这个需要复制粘贴，本地化。 哈哈

lgang403

好贴，顶老大

aya128

好贴  像刘大学习

dianchou

good、 好东西

ybb896

好贴,收藏下

Stone

谢谢ML的分享以及抛玉引砖 ~

我简单补充一下，这个部分的缩写其实可以从Oracle的10053 trace文件中方便查到，如果看10053 trace 多了，应该就比较熟悉啦 :)

1. [oracle@stonedb trace]$ sqlplus / as sysdba
   
2. 
   
3. SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 3 23:20:52 2013
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
6. 
   
7. 
   
8. Connected to:
   
9. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
   
10. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
11. 
    
12. SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
    
13. 
    
14. Session altered.
    
15. 
    
16. SQL> select count(*) from dba_objects;
    
17. 
    
18.   COUNT(*)
    
19. ----------
    
20.      74551
    
21. 
    
22. SQL> ALTER SESSION SET EVENTS '10053 trace name context off';
    
23. 
    
24. Session altered.
    
25. 
    
26. SQL>
    
27. SQL> !pwd
    
28. /u01/app/oracle/diag/rdbms/stone11g/STONE11G/trace
    
29. 
    
30. SQL> !ls -lrth | tail -2
    
31. -rw-rw---- 1 oracle oinstall  71K Mar  3 23:22 STONE11G_ora_7155.trm
    
32. -rw-rw---- 1 oracle oinstall 267K Mar  3 23:22 STONE11G_ora_7155.trc

复制代码

Legend位于10053 trace文件的大概位置：

1. [oracle@stonedb trace]$ grep -i legend -A 52 STONE11G_ora_7155.trc
   
2. Legend
   
3. The following abbreviations are used by optimizer trace.
   
4. CBQT - cost-based query transformation
   
5. JPPD - join predicate push-down
   
6. OJPPD - old-style (non-cost-based) JPPD
   
7. FPD - filter push-down
   
8. PM - predicate move-around
   
9. CVM - complex view merging
   
10. SPJ - select-project-join
    
11. SJC - set join conversion
    
12. SU - subquery unnesting
    
13. OBYE - order by elimination
    
14. OST - old style star transformation
    
15. ST - new (cbqt) star transformation
    
16. CNT - count(col) to count(*) transformation
    
17. JE - Join Elimination
    
18. JF - join factorization
    
19. SLP - select list pruning
    
20. DP - distinct placement
    
21. qb - query block
    
22. LB - leaf blocks
    
23. DK - distinct keys
    
24. LB/K - average number of leaf blocks per key
    
25. DB/K - average number of data blocks per key
    
26. CLUF - clustering factor
    
27. NDV - number of distinct values
    
28. Resp - response cost
    
29. Card - cardinality
    
30. Resc - resource cost
    
31. NL - nested loops (join)
    
32. SM - sort merge (join)
    
33. HA - hash (join)
    
34. CPUSPEED - CPU Speed
    
35. IOTFRSPEED - I/O transfer speed
    
36. IOSEEKTIM - I/O seek time
    
37. SREADTIM - average single block read time
    
38. MREADTIM - average multiblock read time
    
39. MBRC - average multiblock read count
    
40. MAXTHR - maximum I/O system throughput
    
41. SLAVETHR - average slave I/O throughput
    
42. dmeth - distribution method
    
43.   1: no partitioning required
    
44.   2: value partitioned
    
45.   4: right is random (round-robin)
    
46.   128: left is random (round-robin)
    
47.   8: broadcast right and partition left
    
48.   16: broadcast left and partition right
    
49.   32: partition left using partitioning of right
    
50.   64: partition right using partitioning of left
    
51.   256: run the join in serial
    
52.   0: invalid distribution method
    
53. sel - selectivity
    
54. ptn - partition

复制代码