SQL优化的内容浩如烟海，今天给大家分享其中的冰山一角，带大家了解一下关于索引和直方图的不常见问题。
提到Oracle的SQL优化，是不是脑海最先飘来三个字：建索引。诚然，建索引常见，建了不合理索引执行计划不走也常见，但是唯一索引不走就不常见了吧......曾经就碰到过这样一个案例，某省网管一条简单的SQL，查询条件唯一，查询字段上有唯一索引，但是执行计划却是走的TABLEACCESS FULL。
SQL> set autotrace traceonly SQL> select * from Test.tab_test SQL> where flow_instance_id='flow6018601892605466511570_2017041101_15731144608692161'; Elapsed: 00:00:00.20 Execution Plan ---------------------------------------------------------- Plan hash value: 1626873291 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 365K| 27M| 6755 (2)| 00:01:22 | |* 1 | TABLE ACCESS FULL| TAB_TEST | 365K| 27M| 6755 (2)| 00:01:22 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("FLOW_INSTANCE_ID"='flow6018601892605466511570_2017041101_ 15731144608692161') Statistics ---------------------------------------------------------- 1 recursive calls |
当然，事先我是不知道以上信息的，问题拿到手，常规思路分析一二。先看查询字段离散度：
| SQL> select count(1),count(distinct FLOW_INSTANCE_ID) from TEST.TAB_TEST; COUNT(1) COUNT(DISTINCTFLOW_INSTANCE_ID) ---------- ------------------------------- 2422157 2422155 |
明显字段值几乎唯一，可选择性可以说是极好。那就奇了怪了，这种数据分布的字段，基本可以排除数据倾斜导致的不走索引问题。再来看索引情况，获取索引定义：
| Create index TEST. TAB_TEST_INDEX_FLOWINS on TEST.TAB_TEST(FLOW_INSTANCE_ID); |
很普通索引创建语句，也没有什么花活，再来看下统计信息吧：
| ora tstat TAB_TEST TEST \n=============Mon Nov 18 16:15:00 CST 2019===================\n Session altered. Session altered. OWNER PARTNAME NROWS BLOCKS AVGSPC CCNT ROWLEN SSIZE ANADATE ---------- ------------------------------ ---------- ---------- ------ ---- ------ -------- ------------------- TEST 2419330 30497 0 0 78 241933 2019-11-18 14:54:43 |
统计信息当天已重新收集。尝试使用hint强制走索引？然而hint被优化器忽略，依然是TABLEACCESS FULL。到这里，感觉应该不是常规的问题了，接着分析，是不是某些细节被忽略了？带着疑问，接着查看详细的统计信息：
| select owner,table_name,column_name,num_distinct,histogram,num_buckets from dba_tab_col_statistics where table_name='TAB_TEST'; OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS ------------------------------ --------------------- ---------------------- ------------ --------------- ----------- TEST TAB_TEST FLOW_INSTANCE_ID 6861 HEIGHT BALANCED 254 TEST TAB_TEST ORGNAME 1963 NONE 1 |
发现FLOW_INSTANCE_ID的NUM_DISTINCT偏小，并且产生了高度平衡直方图，按理说不应该产生的，难道ORACLE认为数据分布不均匀？再来看一下SQL：
| SQL> select * from Test.tab_test where flow_instance_id='flow6018601892605466511570_2017041101_15731144608692161'; |
细看之下，一个突出的印象就是，这特么flow_instance_id字段值怎么这么长？我相信细心的小伙伴看到这，应该已经知道问题出在哪了，那就是12C之前Oracle直方图有32字符的长度限制，也就是只存储字段值的前32个字符（12C之后为64字符），这个SQL看上去就很符合啊......来看下取字段前32字符后，数据的离散度：
| select count(1),count(distinct substr(FLOW_INSTANCE_ID,1,10)) from TEST.TAB_TEST; COUNT(1) COUNT(DISTINCTSUBSTR(FLOW_INSTANCE_ID,1,32)) ---------- -------------------------------------------- 2422196 80 |
果不其然，就是这个问题了。
既然问题已经定位，接下来就是解决了，办法那是相当简单，不要直方图就是了......
重新收集统计息，语法如下：
| exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST',tabname=>'TAB_TEST',estimate_percent=>100,method_opt=>'for all columns size 1',no_invalidate=>false,cascade=>true,degree => 10); |
再次查看SQL执行计划：
SQL> set autotrace traceonly SQL> select * from TEST.TAB_TEST where flow_instance_id='flow6018601892605466511570_2017041101_15731144608692161'; Execution Plan ---------------------------------------------------------- Plan hash value: 1259607901 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 78 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TAB_TEST | 1 | 78 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB_TEST_INDEX_FLOWINSTID | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("FLOW_INSTANCE_ID"='flow6018601892605466511570_2017041101_15731144608692161') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 678 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
可以看到，已经走上索引，逻辑读从3W多降到6，基本可以说效率是飞起！
大部分情况下，直方图的32字符限制是够了的，除了这一例，还真没再碰到过，幸好关于直方图的限制在脑海里有印象，不然又得多花好多时间去分析了。。。ORACLE的知识体系这么庞大，细节问题茫茫多，运维路上，任重而道远啊，继续耕耘去也。
