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 =============Mon Nov 18 16:15:00 CST 2019=================== 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的知识体系这么庞大，细节问题茫茫多，运维路上，任重而道远啊，继续耕耘去也。
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/130168.html
摘要：不会进行全表扫描函数是一种查询匹配字符串出现次数的函数执行语句执行结果经过相关资料的学习最终认为的效率与的效率是无法对比谁快谁慢，相关文章推荐阅读查询结果中文乱码原因主要是的编码字符集与数据库的字符集不一致导致的。 前言 身为一名前端工程师, 对于 SQL了解程度并不是很深刻, 盘点一些个人工作遇到的问题，给大家普及下知识, 以及记录自己如何解决这些问题的. 导航 SELECT 语句...
阅读 1249·2023-01-11 13:20
阅读 1557·2023-01-11 13:20
阅读 1011·2023-01-11 13:20
阅读 1680·2023-01-11 13:20
阅读 3971·2023-01-11 13:20
阅读 2518·2023-01-11 13:20
阅读 1310·2023-01-11 13:20
阅读 3485·2023-01-11 13:20
