資訊專欄INFORMATION COLUMN
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的知識體系這么龐大，細節問題茫茫多，運維路上，任重而道遠啊，繼續耕耘去也。
文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。
轉載請注明本文地址：http://m.specialneedsforspecialkids.com/yun/130168.html
摘要：不會進行全表掃描函數是一種查詢匹配字符串出現次數的函數執行語句執行結果經過相關資料的學習最終認為的效率與的效率是無法對比誰快誰慢，相關文章推薦閱讀查詢結果中文亂碼原因主要是的編碼字符集與數據庫的字符集不一致導致的。 前言 身為一名前端工程師, 對于 SQL了解程度并不是很深刻, 盤點一些個人工作遇到的問題，給大家普及下知識, 以及記錄自己如何解決這些問題的. 導航 SELECT 語句...
閱讀 1353·2023-01-11 13:20
閱讀 1699·2023-01-11 13:20
閱讀 1211·2023-01-11 13:20
閱讀 1902·2023-01-11 13:20
閱讀 4161·2023-01-11 13:20
閱讀 2748·2023-01-11 13:20
閱讀 1397·2023-01-11 13:20
閱讀 3664·2023-01-11 13:20
