oracle字符集增加生僻字,Oracle SQL一个“生僻字”的优化

最新推荐文章于 2024-06-20 07:29:29 发布

weixin_39841136

最新推荐文章于 2024-06-20 07:29:29 发布

阅读量629

点赞数

文章标签： oracle字符集增加生僻字

本文通过一个实际案例，探讨了Oracle SQL优化中遇到的一个不常见问题：当查询条件是唯一索引字段时，执行计划却选择了全表扫描。经过分析，发现问题出在Oracle直方图的32字符限制上，由于字段值较长，导致统计信息不准确。解决方案是重新收集统计信息并禁用直方图，从而让SQL正确使用索引，显著提高执行效率。

摘要由CSDN通过智能技术生成

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

——————————————————————————-

——————————————————————————-

| 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.

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

———————————————————————————————————-

———————————————————————————————————-

| 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 |

———————————————————————————————————-