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的知識體系這么龐大，細節問題茫茫多，運維路上，任重而道遠啊，繼續耕耘去也。

總結

以上是生活随笔為你收集整理的oracle字符集增加生僻字,Oracle SQL一个“生僻字”的优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。