踩坑CBO，解决那些坑爹的SQL优化问题_PART5

接PART4：

2.2 TABLE函数8168基数问题

  此问题来源于binding in list问题，使用TABLE函数构造传入的逗号分隔的值作为子查询条件，一般前端传入的值都较少，但是实际上走了HASH JOIN操作，无法使用T表索引，一旦执行频率高，必然对系统影响较大，为什么ORACLE不知道TABLE函数传入了很少的值呢？

进一步分析：

  
  从上面结果看出，TABLE函数的默认行数是8168行(TABLE函数创建的伪表是没有统计信息的)，这个值不小了，一般比实际应用中的行数要多的多，经常导致执行计划走hash join，而不是nested loop。怎么改变这种情况呢?当然可以通过hint提示来改变执行计划了,对where in list，常常使用的hint有：first_rows，index，cardinality，use_nl等。这里特别介绍下cardinality(table|alias,n)，这个hint很有用，它可以让CBO优化器认为表的行数是n，这样就可以改变执行计划了。现在改写上面的查询：

  
  加了cardinality(tab,5)自动走CBO优化器了，优化器把表的基数看成5，前面的where in list查询基数默认为8168的时候走的是hash join，现在有了cardinality，赶紧试试：


  现在走NESTED LOOPS操作，子节点可以走INDEX RANGE SCAN，逻辑读从184变成7，效率提升数十倍。当然，实际应用中，最好不要加hints,可以使用SQL PROFILER绑定。

2.3 选择性计算错误问题

  ORACLE内部计算选择性都是以数字格式计算，因此，遇到字符串类型，会将字符串转换成RAW类型，再将RAW类型转换成数字，并且ROUND到左起15位，这样对于转换后的数字很大，可能原来字符串相差比较大的，内部转换后的数字比较接近，这样就会引起选择性计算不准确问题。如下例：

执行计划如下：

  SQL执行计划走TEM_ID索引，需要运行1小时以上，计划中对应步骤cardinality很少（几十级别），实际很大（百万级别），判断统计信息出错。

  为什么走错索引？
  由于TEM_ID是CHAR字符串类型，长度20，CBO内部计算选择性会先将字符串转为RAW，然后RAW转为数字，左起ROUND 15位。因此，可能字符串值差别大的，转换成数字后值接近（因为超出15位补0），导致选择性计算错误。以TS_TEM_INFO_DEAD中的TEM_ID列为例：

NUM_ROWS：139495263

NUM_DISTINCT:139495263

LOW_VALUE:3838383630303830313239383031363230383620

HIGH_VALUE：4E554C4C35303839302020202020202020202020

NUM_NULLS:0

SQL SIM_NO谓词最大最小值内部一样

SQL> select get_internal_value('89860005126436720074') from dual;

GET_INTERNAL_VALUE('89860005126436720074')

----------------------------------------------------------------------------------------------------------

291929174972906000000000000000000000

SQL> select get_internal_value('8986005126436720050') from dual;

GET_INTERNAL_VALUE('8986005126436720050')

----------------------------------------------------------------------------------------------------------

291929174972906000000000000000000000

sel:1/139495263+1/139495263+(291929174972906000000000000000000000-291929174972906000000000000000000000)/(high_value-low_value)

=1/139495263+1/139495263

card:=139495263*(1/139495263+1/139495263)=2             
而实际根据条件查询出的行数  29737305。因此，索引走错了。

解决方法：

收集TEM_ID列直方图，由于内部算法有一定限制，导致值不同的字符串，内部计算值可能一致，所以收集直方图后，针对字符串值不同，但是转换成数字后相同的，ORACLE会将实际值存储到ENDPOINT_ACTUAL_VALUE中，用于校验，提高执行计划的准确性。走正确索引GPYTM_ID后，运行时间从1小时以上到5s内。
执行计划如下所示：

未完待续，见PART6：