代码之家 › 专栏 › 技术社区 › Tommi

Oracle解释计划估计索引范围扫描的基数不正确

sql-execution-plan performance oracle10g indexing oracle

Tommi · 技术社区 · 15 年前

我有一个Oracle10.2.0.3数据库,还有一个这样的查询:

select count(a.id) 
from LARGE_PARTITIONED_TABLE a
join SMALL_NONPARTITIONED_TABLE b on a.key1 = b.key1 and a.key2 = b.key2
where b.id = 1000

表大分区表(A)有大约500万行,并由查询中不存在的列进行分区。表small_nonpartitioned_table(b)未分区,可容纳约10000行。

统计数据是最新的,在表A的key1和key2列中有高度平衡柱状图。

表A具有主键和列key1、key2、key3、key4和key5的全局非分区唯一索引。

说明计划 对于查询,显示以下结果:

---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                              |     1 |    31 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                              |     1 |    31 |            |          |
|   2 |   NESTED LOOPS     |                              |   406 | 12586 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN| INDEX_ON_TABLE_B            |     1 |    19 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| PRIMARY_KEY_INDEX_OF_TABLE_A |   406 |  4872 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   3 - access("b"."id"=1000)
   4 - access("a"."key1"="b"."key1" and
              "a"."key2"="b"."key2")

因此,步骤4估计的行(基数)是 四百零六 .

现在,A TKPROF 跟踪显示以下内容:

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=51 pr=9 pw=0 time=74674 us)
   7366   NESTED LOOPS  (cr=51 pr=9 pw=0 time=824941 us)
      1    INDEX RANGE SCAN INDEX_ON_TABLE_B (cr=2 pr=0 pw=0 time=36 us)(object id 111111)
   7366    INDEX RANGE SCAN PRIMARY_KEY_INDEX_OF_TABLE_A (cr=49 pr=9 pw=0 time=810173 us)(object id 222222)

所以现实中的基数是 七千三百六十六 ,不是406!

我的问题是: Oracle从何处获取估计基数 在这种情况下,为406,以及 我怎样才能提高它的准确度 ,以便估计更符合查询执行期间实际发生的情况?

更新: 这是我在查询中运行的10053跟踪的一个片段。

NL Join
  Outer table: Card: 1.00  Cost: 2.00  Resp: 2.00  Degree: 1  Bytes: 19
  Inner table: LARGE_PARTITIONED_TABLE  Alias: a
  ...
  Access Path: index (IndexOnly)
    Index: PRIMARY_KEY_INDEX_OF_TABLE_A
    resc_io: 2.00  resc_cpu: 27093
    ix_sel: 1.3263e-005  ix_sel_with_filters: 1.3263e-005
    NL Join (ordered): Cost: 4.00  Resp: 4.00  Degree: 1
      Cost_io: 4.00  Cost_cpu: 41536
      Resp_io: 4.00  Resp_cpu: 41536
  ****** trying bitmap/domain indexes ******
  Best NL cost: 4.00
          resc: 4.00 resc_io: 4.00 resc_cpu: 41536
          resp: 4.00 resp_io: 4.00 resp_cpu: 41536
Using concatenated index cardinality for table SMALL_NONPARTITIONED_TABLE
Revised join sel: 8.2891-e005 = 8.4475e-005 * (1/12064.00) * (1/8.4475e-005)
Join Card:  405.95 = outer (1.00) * inner (4897354.00) * sel (8.2891-e005)
Join Card - Rounded: 406 Computed: 405.95

这就是406值的来源。就像亚当回答的,加入基数是 join selectivity * filter cardinality (a) * filter cardinality (b) ,如上述跟踪引号的第二行到最后一行所示。

我不明白的是 Revised join sel 线。1/12064是用于从表B中查找行的索引的选择性(表中有12064行,并根据唯一ID进行选择)。但那又怎样呢?

基数似乎是由乘以 筛选基数表B (4897354)与 表A的选择性 (1/12064)。为什么?什么选择性是否开启 A表和多少有关应在中找到行表B, 当a-->b联接不基于 A.ID?
号码在哪 84.75-E-0.5 来自(整体上不在其他地方出现) 痕迹)?不会影响输出,但我还是想知道。

我知道优化器可能选择了正确的路径。但仍然 基数计算错误 -这可能会对从那一点开始选择的执行路径产生重大影响(例如,在我使用IRL的情况下——这个例子是对它的简化)。

3 回复 | 直到 14 年前

Adam Musch 15 年前

生成10053跟踪文件将有助于准确显示优化器在估计基数和选择性方面所做的选择。乔纳森·刘易斯的特长 基于成本的Oracle基础知识 是了解优化器如何工作的一个很好的资源,我的打印跨越了8i到10.1。

从这项工作中:

Join Selectivity =   ((num_rows(t1) - num_nulls(t1.c1)) / num_rows(t1)) 
                   * ((num_rows(t2) - num_nulls(t2.c2)) / num_rows(t2))
                   / greater (num_distinct(t1.c1), num_distinct(t2.c2))

Join Cardinality =   Join Selectivity 
                   * filtered_cardinality (t1)
                   * filtered_cardinality (t2)

但是,因为我们有一个多列联接,所以联接选择性不在表级别,而是每列上联接选择性的乘积(交集)。假设游戏中没有空值:

Join Selectivity = Join Selectivity (key1) * Join Selectivity (key2)

Join Selectivity (key1) =   ((5,000,000 - 0) / 5,000,000)
                          * ((10,000 - 0)) / 10,000)
                          / max (116, ?)  -- distinct key1 values in B

                        = 1 / max(116, distinct_key1_values_in_B)

Join Selectivity (key2) =   ((5,000,000 - 0) / 5,000,000)
                          * ((10,000 - 0)) / 10,000)
                          / max (650, ?)  -- distinct key2 values in B

                        = 1 / max(650, distinct_key2_values in B)

Join Cardinality =  JS(key1) * JS(key2) 
                  * Filter_cardinality(a) * Filter_cardinality(b)

我们知道在上没有过滤器,所以这是表过滤器基数是行数。我们从B中选择键值,所以表的过滤基数是1。

因此,估计的联接基数的最佳情况是

Join Cardinality  = 1/116 * 1/650 * 5,000,000 * 1

                  =~ 67

向后工作可能更容易。根据我们所知,您的估计基数406将导致join selectivty为406/5000000,或约为1/12315。这恰好非常非常接近1/(116^2),这是优化器中的健全性检查,以防止优化器在多列联接上发现过于激进的基数。

对于TL;Dr Crowd:

找乔纳森·刘易斯 基于成本的Oracle基础知识 .
获取10053个您无法理解其行为的查询跟踪。

Jeffrey Kemp 15 年前

基数估计将基于a.key1和a.key2的选择性的乘积,而a.key1和a.key2的选择性(至少在10g中)将分别基于列统计中记录的这两列的不同值的数目。

对于5米行的表,406的基数估计与7366的基数估计没有显著差异。你必须问自己的问题是,这里的“不准确”估计是否会引起问题?

如果Oracle能够生成一个完全准确的估计,那么您可以通过获取一个解释计划来检查它将选择什么计划:

select /*+CARDINALITY(a 7366)*/ count(a.id) 
from LARGE_PARTITIONED_TABLE a
join SMALL_NONPARTITIONED_TABLE b on a.key1 = b.key1 and a.key2 = b.key2
where b.id = 1000;

如果这产生了相同的计划,那么Oracle正在计算的估计已经足够了。

Dave Costa 15 年前

您可能有兴趣阅读Wolfgang Breitling的这篇优秀论文,该论文对CBO计算有很多信息: http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of%20CBO%20-%20the%2010053%20Event.pdf .

正如这里所解释的,因为您有柱状图,所以这些列的过滤因子计算不使用不同值的数目(ndv),而是使用密度,密度是以某种方式从柱状图派生出来的。

A.key1和A.key2的用户选项卡列中的密度值是什么?

一般来说,这种情况下的问题是Oracle不收集成对列的统计信息,并假定它们的组合过滤因子将是它们各自因子的乘积。如果两列的值之间存在相关性,这将产生较低的估计值。

如果这会导致严重的性能问题,我想您可以基于这些列的函数创建一个基于函数的索引,并使用它进行查找。然后,甲骨文将收集该指数的统计数据,并可能做出更好的估计。