資訊專欄INFORMATION COLUMN
SQL優(yōu)化的內(nèi)容浩如煙海，今天給大家分享其中的冰山一角，帶大家了解一下關(guān)于索引和直方圖的不常見問題。
提到Oracle的SQL優(yōu)化，是不是腦海最先飄來三個(gè)字：建索引。誠然，建索引常見，建了不合理索引執(zhí)行計(jì)劃不走也常見，但是唯一索引不走就不常見了吧......曾經(jīng)就碰到過這樣一個(gè)案例，某省網(wǎng)管一條簡單的SQL，查詢條件唯一，查詢字段上有唯一索引，但是執(zhí)行計(jì)劃卻是走的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 |
當(dāng)然，事先我是不知道以上信息的，問題拿到手，常規(guī)思路分析一二。先看查詢字段離散度：
| SQL> select count(1),count(distinct FLOW_INSTANCE_ID) from TEST.TAB_TEST; COUNT(1) COUNT(DISTINCTFLOW_INSTANCE_ID) ---------- ------------------------------- 2422157 2422155 |
明顯字段值幾乎唯一，可選擇性可以說是極好。那就奇了怪了，這種數(shù)據(jù)分布的字段，基本可以排除數(shù)據(jù)傾斜導(dǎo)致的不走索引問題。再來看索引情況，獲取索引定義：
| Create index TEST. TAB_TEST_INDEX_FLOWINS on TEST.TAB_TEST(FLOW_INSTANCE_ID); |
很普通索引創(chuàng)建語句，也沒有什么花活，再來看下統(tǒng)計(jì)信息吧：
| 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 |
統(tǒng)計(jì)信息當(dāng)天已重新收集。嘗試使用hint強(qiáng)制走索引？然而hint被優(yōu)化器忽略，依然是TABLEACCESS FULL。到這里，感覺應(yīng)該不是常規(guī)的問題了，接著分析，是不是某些細(xì)節(jié)被忽略了？帶著疑問，接著查看詳細(xì)的統(tǒng)計(jì)信息：
| 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 |
發(fā)現(xiàn)FLOW_INSTANCE_ID的NUM_DISTINCT偏小，并且產(chǎn)生了高度平衡直方圖，按理說不應(yīng)該產(chǎn)生的，難道ORACLE認(rèn)為數(shù)據(jù)分布不均勻？再來看一下SQL：
| SQL> select * from Test.tab_test where flow_instance_id=flow6018601892605466511570_2017041101_15731144608692161; |
細(xì)看之下，一個(gè)突出的印象就是，這特么flow_instance_id字段值怎么這么長？我相信細(xì)心的小伙伴看到這，應(yīng)該已經(jīng)知道問題出在哪了，那就是12C之前Oracle直方圖有32字符的長度限制，也就是只存儲(chǔ)字段值的前32個(gè)字符（12C之后為64字符），這個(gè)SQL看上去就很符合啊......來看下取字段前32字符后，數(shù)據(jù)的離散度：
| 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 |
果不其然，就是這個(gè)問題了。
既然問題已經(jīng)定位，接下來就是解決了，辦法那是相當(dāng)簡單，不要直方圖就是了......
重新收集統(tǒng)計(jì)息，語法如下：
| 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執(zhí)行計(jì)劃：
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 |
可以看到，已經(jīng)走上索引，邏輯讀從3W多降到6，基本可以說效率是飛起！
大部分情況下，直方圖的32字符限制是夠了的，除了這一例，還真沒再碰到過，幸好關(guān)于直方圖的限制在腦海里有印象，不然又得多花好多時(shí)間去分析了。。。ORACLE的知識(shí)體系這么龐大，細(xì)節(jié)問題茫茫多，運(yùn)維路上，任重而道遠(yuǎn)啊，繼續(xù)耕耘去也。
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址：http://specialneedsforspecialkids.com/yun/130168.html
摘要：不會(huì)進(jìn)行全表掃描函數(shù)是一種查詢匹配字符串出現(xiàn)次數(shù)的函數(shù)執(zhí)行語句執(zhí)行結(jié)果經(jīng)過相關(guān)資料的學(xué)習(xí)最終認(rèn)為的效率與的效率是無法對(duì)比誰快誰慢，相關(guān)文章推薦閱讀查詢結(jié)果中文亂碼原因主要是的編碼字符集與數(shù)據(jù)庫的字符集不一致導(dǎo)致的。 前言 身為一名前端工程師, 對(duì)于 SQL了解程度并不是很深刻, 盤點(diǎn)一些個(gè)人工作遇到的問題，給大家普及下知識(shí), 以及記錄自己如何解決這些問題的. 導(dǎo)航 SELECT 語句...
閱讀 1346·2023-01-11 13:20
閱讀 1684·2023-01-11 13:20
閱讀 1132·2023-01-11 13:20
閱讀 1858·2023-01-11 13:20
閱讀 4100·2023-01-11 13:20
閱讀 2704·2023-01-11 13:20
閱讀 1385·2023-01-11 13:20
閱讀 3597·2023-01-11 13:20
