云水禅心

天行健君子以自强不息 地势坤君子以厚德载物

  • 博客访问: 32705
  • 博文数量: 202
  • 用 户 组: 普通用户
  • 注册时间: 2013-05-11 15:34
  • 认证徽章:
个人简介

一生做好一件事

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(202)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Oracle


  1. CBO(基于代价的优化器)是RBO(基于规则的优化器)的替代品,从9i开始oracle就建议用户使用CBO来进行SQL的优化。CBO大概的优化原理很简单,他通过对象上的统计信息来计算各个执行计划的代价,然后选择代价较小的执行计划来运行。所以对于CBO来说对象(比如表,索引)上的统计信息就显得十分的重要,不仅要有统计信息,还要保证统计信息是准确的,不准确的统计信息可能会带来灾难性的结果。那么oracle是怎么样利用这些统计信息呢。下面举个简单的例子帮助大家理解:
  2. SQL> create table sunwg (id number);
  3. Table created.
  4. SQL> insert into sunwg select rownum from all_tables where rownum<101;
  5. 100 rows created.
  6. SQL> commit;
  7. SQL> analyze table sunwg compute statistics for table;
  8. Table analyzed.
  9. sql> select num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len
  10. from user_tables where table_name = 'sunwg';
  11. num_rows blocks empty_blocks avg_space chain_cnt avg_row_len
  12. ---------- ---------- ------------ ---------- ---------- -----------
  13. 100 1 6 6978 0 6
  14. sql>select num_distinct,raw_to_number(low_value),raw_to_number(high_value),density,num_buckets
  15. from user_tab_columns where table_name='sunwg';
  16. no rows selected
  17. 我们创建了一张测试表sunwg,并且分析了表上的统计信息,但是我没有分析列id的统计信息。
  18. SQL> set autot traceonly exp
  19. SQL> select * from sunwg where id = 1;
  20. Execution Plan
  21. ----------------------------------------------------------
  22. Plan hash value: 459567752
  23. ---------------------------------------------------------------------------
  24. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  25. ---------------------------------------------------------------------------------------------------------------
  26. | 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
  27. |* 1 | TABLE ACCESS FULL| SUNWG | 1 | 6 | 2 (0)| 00:00:01 |
  28. ---------------------------------------------------------------------------------------------------------------
  29. 从这个SQL的执行计划可以看出来,Oracle认为这个SQL会查找出1条记录。事实上呢,实际表中ID = 1的记录也只有一条,那么oracle估计得很正确。Oracle真的这么聪明么?我们可以想象一下,如果仅仅告诉你一个表中有100条记录,然后问你在这个表中ID = 1的记录有几条,你能算得出来么?答案是肯定的,你不了解数据的分布情况,无法得知ID = 1的记录数量,所以oracle得到的这个ID = 1的结果是“蒙”的。那么我们接着看下面的例子。
  30. SQL> select * from sunwg where id >= 1;
  31. Execution Plan
  32. ----------------------------------------------------------
  33. Plan hash value: 459567752
  34. ---------------------------------------------------------------------------
  35. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  36. ---------------------------------------------------------------------------
  37. | 0 | SELECT STATEMENT | | 5 | 30 | 2 (0)| 00:00:01 |
  38. |* 1 | TABLE ACCESS FULL| SUNWG | 5 | 30 | 2 (0)| 00:00:01 |
  39. ---------------------------------------------------------------------------
  40. 这下oracle就彻底的露馅了,oracle估计ID > 1的结果是5行,但实际上应该是99条。其实这也不能怪oracle,因为你给他的信息实在是太少了,为了优化的正常进行他必须要估计一个大概基数值才可以进行代价的计算。
  41. Where 条件    Oracle估计记录数    表中实际记录数    Oracle估算公式(猜想)
  42. ID = 1    1    1    100 * 1%
  43. ID > 1    5    99    100* 5%
  44. ID >= 1    5    100    100* 5%
  45. ID = 110    1    0    1
  46. ID + 1 > 1    5    100    100* 5%
  47. ID + 1 >= 1    5    100    100* 5%
  48. ID + 1 > 1 AND ID + 1 > 1    5    100    100* 5%
  49. ID > 1 AND ID < 50    1    48    100 *( 5%* 5%)
  50. ID >80 OR ID <30    10    49    100*(5% + 5% - 5%* 5%)
  51. 从上面这个结果我们还看不出来到底oracle笨不笨,毕竟我们没有把完备的统计信息给他。下面我会统计表上列ID的信息,看看有了列ID上的统计信息后,oracle会有什么样子的表现。
  52. SQL> analyze table sunwg compute statistics for columns id size 100;
  53. Table analyzed.
  54. sql>select num_distinct,raw_to_number(low_value),raw_to_number(high_value),density,num_buckets
  55. from user_tab_columns where table_name = 'sunwg';
  56. num_distinct raw_to_number(low_value) raw_to_number(low_value) density
  57. ------------ ------------------------ ------------------------- ---
  58. 100 1 100 0.01
  59. 这个时候已经可以看到列ID上的统计信息了,在重复做上面的测试可以得到下面的结果:
  60. Where 条件    Oracle估计记录数    表中实际记录数    Oracle估算公式(猜想)
  61. ID = 1    1    1    100 *1%
  62. ID > 1    99    99    100*((100 – 1)/100)
  63. ID >= 1    100    100    100*((100 – 1)/100 + 0.01)
  64. ID = 110    1    0    1
  65. ID + 1 > 1    5    100    100 * 5%
  66. ID + 1 >= 1    5    100    100 * 5%
  67. ID + 1 > 1 AND ID + 1 > 1    5    100    100 * 5%
  68. ID > 1 AND ID < 50    48    48    100*((99/100)*(49/100))
  69. ID >80 OR ID <30    44    49    100*(20/100+29/100 - (20/100)*(29/100))
  70. 上面的结果虽然比没有分析列ID上的统计信息之前要准确了一些,但是和实际还是有一些差距的。
  71. 在CBO里面还有另外一个和基数对应的概念——选择率,他们之间的关系应该是:
  72. 基数 = 总记录数 * 选择率
  73. 如果要计算基数的时候只要先算出大概的选择率,然后在和记录数相乘就可以得到基数信息。
  74. 那么选择率应该怎么计算呢?(以第二张表格中的数据为例说明)
  75. 例一:条件ID = 1
  76. 选择率 = 1/100 = 0.01
  77. 基数 = 100 * 0.01 = 1
  78. 例二:条件ID >= 1
  79. 选择率 = (100 – 1)/100 + 0.01 = 1
  80. 基数 = 100 * 1 = 100
  81. 例三:条件ID > 1 AND ID < 50
  82. 选择率 = ((100 – 1)/100) * ((50 – 1)/100)= 0.48
  83. 基数 = 100 * 0.48 = 48
  84. 关于多个查询的条件的时候有三个公式的:
  85. P(A) AND P(B) = P(A)的选择率 * P(B)的选择率
  86. P(A) OR P(B) = P(A)的选择率 + P(B)的选择率 - P(A)的选择率 * P(B)的选择率
  87. NOT P(A) = 1 - P(A)的选择率
  88. 有个地方我们需要特别留意一下,就是在收集列ID上的信息的时候。analyze table sunwg compute statistics for columns id size 100;
  89. 在前面的例子里面我们使用的是SIZE 100,如果这个SIZE不同的话,那么分析出来的结果也是不一样的。实际上我们是在收集列上的直方图信息,这个SIZE就是直方图的BUCKET的数量,这个值对直方图信息的准确性有着很重要的影响。
  90. 有个地方我们需要特别留意一下,在没有直方图信息的时候 使用的是NUM_DISTINCT 也就是说前面的 1% 实际上是 1/NUM_DISTINCT = 1/100,而在有直方图的时候 使用的是 density。

阅读(624) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册