通過IP定位區域的SQL優化思路
昨天中午吃飯的時候，突然手機收到一條報警資訊，提示資料庫的負載突然提高了。對於一個高配，穩定，核心的系統來說，出現這麼一個報警會立刻引起關注。
連線到環境之後，發現在問題發生時間段快照中資源消耗較大的SQL情況如下：
SNAP_ID SQL_ID EXECUTIONS_DELTA ELAPSED_TI PER_TOTAL
---------- ------------- ---------------- ---------- ----------
2396 63f970ck8r3kc 26284 7316s 49%
2396 0k8yyk60k4y7x 63356 7342s 49%
2396 63f970ck8r3kc 0 7316s 49%
2396 174qq0v73j2at 37952 2s 0%
2396 87katd5jmmthy 1761 0s 0%
可以看到有兩個語句的執行頻率還是比較高，整體佔用了絕大多數的CPU資源。
對應的前兩個SQL語句如下：
SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
SELECT IP_ID,COUNTRY,PROVINCE,CAPITAL
FROM SWD_IP2COUNTY
WHERE STRIPTOINT(:B1 ) BETWEEN IP2NUM_LEFT_LINE AND IP2NUM_RIGHT_LINE
看起來語句很簡單，第一感覺是不是全表掃描導致的，也就潛意識中感覺是不是沒有相關的索引。
但是檢視錶結構資訊，發現在IP2NUM_LEFT_LINE和IP2NUM_RIGHT_LINE有兩個索引，索引最初的猜想不成立。
然後開始看這個語句的細節，發現這個語句還真是有點意思，之前還沒見過這種寫法。
首先是裡面看起來是是存在一個函式STRIPTOINT處理繫結變數，然後有一個過濾條件between xxx and xxx,兩端的可是欄位，不是具體的數值。
如果函式STRIPTOINT處理某個欄位的數值，那麼就是一種使用不規範的情況，肯定會走索引，但是這裡的情況又有些特殊。
看到這個邏輯，我是有些懵了，我們也不能隨便猜。看看錶結構。
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE
---------- ------------------------------ --------------- ----------- ----------
1 IP_ID NUMBER(10,0) 22 N
2 IP_LEFT_LINE VARCHAR2(15) 15 N
3 IP_RIGHT_LINE VARCHAR2(15) 15 N
4 IP2NUM_LEFT_LINE NUMBER(10,0) 22 N
5 IP2NUM_RIGHT_LINE NUMBER(10,0) 22 N
6 COUNTRY VARCHAR2(20) 20 Y
7 PROVINCE VARCHAR2(20) 20 Y
8 CAPITAL VARCHAR2(20) 20 Y
可以看出是和IP相關的，而且裡面存在一些地理區域的資訊。
函式STRIPTOINT是對傳入的IP做過濾，比如傳入一個IP 10.1.3.5 ，函式會把這個IP按照".”進行分隔，從第一部分開始到第四部分，會逐步拉開資料的梯度。
比如傳入一個IP,就會轉換為一個較大的數值，儘可能保證不會重複。
SQL> select STRIPTOINT('124.115.229.74') from dual;
STRIPTOINT('124.115.229.74')
----------------------------
208797012
所以這個SQL語句就類似下面的形式。
SELECT IP_ID,COUNTRY,PROVINCE,CAPITAL FROM tlbb.SWD_IP2COUNTY WHERE 208797012 BETWEEN IP2NUM_LEFT_LINE AND IP2NUM_RIGHT_LINE
這樣理解起來還真是有點費勁，我們繼續介紹一些相關的業務情況，這些也是我根據資料猜出來的，後面和開發的同學聊，和我想的是一樣的。
比如資料是這樣的形式：
IP_LEFT_LINE IP_RIGHT_LINE IP2NUM_LEFT_LINE IP2NUM_RIGHT_LINE COUNTRY PROVINCE CAPITAL
----- --------------- --------------- ---------------- ----------------- ---------- ---------- -------
5.34.184.0 5.34.191.255 86161408 86163455 中國 北京 北京
5.34.192.0 5.34.223.255 86163456 86171647 中國 河北 石家莊
比如IP 5.34.184.0~5.34.191.255 這個區間代表的是北京地區，5.34.192.0 ~ 5.34.223.255 這個區間代表的是河北石家莊。那麼傳入一個IP就開始對映得到一個數值，通過這個範圍區間來找到對應的地區。
這樣一個語句，明白了需求，好像還是有點道理。
來看看awr的SQL報告怎麼說吧。
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 2397 01-Sep-16 13:00:52 567 1.9
End Snap: 2398 01-Sep-16 13:30:54 566 1.9
Elapsed: 30.03 (mins)
DB Time: 138.02 (mins)
可以看到在短期內，資料庫的負載還是比較高。
而SQL的執行統計資訊如下:
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 8,067,698 276.4 97.4
CPU Time (ms) 8,059,783 276.1 97.8
Executions 29,191 N/A N/A
Buffer Gets 3.3692E+07 1,154.2 74.8
Disk Reads 0 0.0 0.0
Parse Calls 73 0.0 0.0
Rows 28,516 1.0 N/A
User I/O Wait Time (ms) 0 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 4 N/A N/A
Sharable Mem(KB) 46 N/A N/A
如果想得到更多的詳細的資訊，使用診斷事件也是不錯的選擇，這也是11g的一個功能。
比如對某一條SQL開啟sql_trace,可以使用如下的方式。
開啟：
alter system set events 'sql_trace [sql: 63f970ck8r3kc] level 12';
關閉：
alter system set events 'sql_trace [sql: 63f970ck8r3kc] off';
而如果有大量的會話頻繁呼叫，那還是不建議使用的，會生成大量的trace檔案，目前的情況就是如此，需要謹慎使用。
而我們怎麼去分析這個SQL呢，來做一個10053事件吧。
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
SELECT IP_ID,COUNTRY,PROVINCE,CAPITAL FROM test.SWD_IP2COUNTY WHERE 208797012 BETWEEN IP2NUM_LEFT_LINE AND IP2NUM_RIGHT_LINE;
ALTER SESSION SET EVENTS '10053 trace name context off';
這個時候trace的資訊可以看到，內部做了查詢轉換,會把原有的語句轉換為下面的形式：
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "SWD_IP2COUNTY"."IP_ID" "IP_ID","SWD_IP2COUNTY"."COUNTRY" "COUNTRY","SWD_IP2COUNTY"."PROVINCE" "PROVINCE","SWD_IP2C
OUNTY"."CAPITAL" "CAPITAL" FROM "TEST"."SWD_IP2COUNTY" "SWD_IP2COUNTY" WHERE "SWD_IP2COUNTY"."IP2NUM_LEFT_LINE"<=208797012
AND "SWD_IP2COUNTY"."IP2NUM_RIGHT_LINE">=208797012
對於執行路徑和表資訊的統計資訊如下：
Access path analysis for SWD_IP2COUNTY
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for SWD_IP2COUNTY[SWD_IP2COUNTY]
Column (#4):
NewDensity:0.000006, OldDensity:0.000006 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:163088
Column (#4): IP2NUM_LEFT_LINE(
AvgLen: 7 NDV: 163088 Nulls: 0 Density: 0.000006 Min: 16910592 Max: 4261412864
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Column (#5):
NewDensity:0.000006, OldDensity:0.000006 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:163246
Column (#5): IP2NUM_RIGHT_LINE(
AvgLen: 7 NDV: 163246 Nulls: 0 Density: 0.000006 Min: 16912383 Max: 4278190079
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Table: SWD_IP2COUNTY Alias: SWD_IP2COUNTY
Card: Original: 163246.000000 Rounded: 1114 Computed: 1114.40 Non Adjusted: 1114.40
Access Path: TableScan
Cost: 444.56 Resp: 444.56 Degree: 0
Cost_io: 443.00 Cost_cpu: 57439773
Resp_io: 443.00 Resp_cpu: 57439773
Access Path: index (RangeScan)
Index: IND_IP2NUM_LEFT_LINE
resc_io: 15.00 resc_cpu: 713197
ix_sel: 0.006874 ix_sel_with_filters: 0.006874
Cost: 15.02 Resp: 15.02 Degree: 1
Access Path: index (RangeScan)
Index: IND_IP2NUM_RIGHT_LINE
resc_io: 1902.00 resc_cpu: 101091934
ix_sel: 0.993126 ix_sel_with_filters: 0.993126
Cost: 1904.74 Resp: 1904.74 Degree: 1
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: IND_IP2NUM_LEFT_LINE
Cost: 15.02 Degree: 1 Resp: 15.02 Card: 1114.40 Bytes: 0
最大的發現是就是這個語句的邏輯結合自己的分析終於清晰了，但是怎麼優化還沒想好。為什麼效能如此差。
我用查詢轉換後的語句，直接在客戶端執行，檢視執行計劃。
SQL> SELECT "SWD_IP2COUNTY"."IP_ID" "IP_ID","SWD_IP2COUNTY"."COUNTRY" "COUNTRY","SWD_IP2COUNTY"."PROVINCE" "PROVINCE","SWD_IP2COUNTY"."CAPITAL" "CAPITAL" FROM "TEST"."SWD_IP2COUNTY" "SWD_IP2COUNTY" WHERE "SWD_IP2COUNTY"."IP2NUM_LEFT_LINE"<=3030728598
AND "SWD_IP2COUNTY"."IP2NUM_RIGHT_LINE">=3030728598 ;
發現這個時候CBO是分析得出需要走全表掃描的。
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1530 consistent gets
0 physical reads
0 redo size
748 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
這樣就徹底明白了，這裡走索引掃描的選擇度本身就很低，其實效果和走全表差距也不大，這樣一來也就容易理解上面的統計資訊中大量的buffer gets的原因了。
這個語句如何優化呢，問題還是擺在我們面前，根據和開發同學的溝通，對這個需求有了更多的一些瞭解，而且根據IP來確定範圍，是不會出現多個區域的情況，也就是說，這個結果集要不是1條，要麼是沒有對映到的。
所以語句可以簡單優化一下，只需要新增rownum=1即可。
SELECT "SWD_IP2COUNTY"."IP_ID" "IP_ID","SWD_IP2COUNTY"."COUNTRY" "COUNTRY","SWD_IP2COUNTY"."PROVINCE" "PROVINCE","SWD_IP2COUNTY"."CAPITAL" "CAPITAL" FROM "TEST"."SWD_IP2COUNTY" "SWD_IP2COUNTY" WHERE "SWD_IP2COUNTY"."IP2NUM_LEFT_LINE"<=3030728598
AND "SWD_IP2COUNTY"."IP2NUM_RIGHT_LINE">=3030728598 and rownum=1 ;
這個時候走了索引，而且一致性讀大大降低。
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
748 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
這個語句在實際環境中的改進效果如何呢，我抓取了對比的資料，發現效能提升了50%。
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 2,318,173 170.9 91.7
CPU Time (ms) 2,314,428 170.6 92.8
Executions 13,563 N/A N/A
Buffer Gets 9,615,771 709.0 49.7
Disk Reads 0 0.0 0.0
Parse Calls 101 0.0 0.0
Rows 13,151 1.0 N/A
User I/O Wait Time (ms) 0 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 2 N/A N/A
Sharable Mem(KB) 52 N/A N/A
執行計劃如下：
這種優化方式還是值得借鑑的。而且在這個基礎上可以考慮對索引進行優化，也就是建立符合索引，根據數值的分佈情況（存在》=，《=的數值區間），可以考慮對索引進行排序
比如：(IP2NUM_LEFT_LINE desc , IP2NUM_RIGHT_LINE asc)
連線到環境之後，發現在問題發生時間段快照中資源消耗較大的SQL情況如下：
SNAP_ID SQL_ID EXECUTIONS_DELTA ELAPSED_TI PER_TOTAL
---------- ------------- ---------------- ---------- ----------
2396 63f970ck8r3kc 26284 7316s 49%
2396 0k8yyk60k4y7x 63356 7342s 49%
2396 63f970ck8r3kc 0 7316s 49%
2396 174qq0v73j2at 37952 2s 0%
2396 87katd5jmmthy 1761 0s 0%
可以看到有兩個語句的執行頻率還是比較高，整體佔用了絕大多數的CPU資源。
對應的前兩個SQL語句如下：
SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
SELECT IP_ID,COUNTRY,PROVINCE,CAPITAL
FROM SWD_IP2COUNTY
WHERE STRIPTOINT(:B1 ) BETWEEN IP2NUM_LEFT_LINE AND IP2NUM_RIGHT_LINE
看起來語句很簡單，第一感覺是不是全表掃描導致的，也就潛意識中感覺是不是沒有相關的索引。
但是檢視錶結構資訊，發現在IP2NUM_LEFT_LINE和IP2NUM_RIGHT_LINE有兩個索引，索引最初的猜想不成立。
然後開始看這個語句的細節，發現這個語句還真是有點意思，之前還沒見過這種寫法。
首先是裡面看起來是是存在一個函式STRIPTOINT處理繫結變數，然後有一個過濾條件between xxx and xxx,兩端的可是欄位，不是具體的數值。
如果函式STRIPTOINT處理某個欄位的數值，那麼就是一種使用不規範的情況，肯定會走索引，但是這裡的情況又有些特殊。
看到這個邏輯，我是有些懵了，我們也不能隨便猜。看看錶結構。
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE
---------- ------------------------------ --------------- ----------- ----------
1 IP_ID NUMBER(10,0) 22 N
2 IP_LEFT_LINE VARCHAR2(15) 15 N
3 IP_RIGHT_LINE VARCHAR2(15) 15 N
4 IP2NUM_LEFT_LINE NUMBER(10,0) 22 N
5 IP2NUM_RIGHT_LINE NUMBER(10,0) 22 N
6 COUNTRY VARCHAR2(20) 20 Y
7 PROVINCE VARCHAR2(20) 20 Y
8 CAPITAL VARCHAR2(20) 20 Y
可以看出是和IP相關的，而且裡面存在一些地理區域的資訊。
函式STRIPTOINT是對傳入的IP做過濾，比如傳入一個IP 10.1.3.5 ，函式會把這個IP按照".”進行分隔，從第一部分開始到第四部分，會逐步拉開資料的梯度。
比如傳入一個IP,就會轉換為一個較大的數值，儘可能保證不會重複。
SQL> select STRIPTOINT('124.115.229.74') from dual;
STRIPTOINT('124.115.229.74')
----------------------------
208797012
所以這個SQL語句就類似下面的形式。
SELECT IP_ID,COUNTRY,PROVINCE,CAPITAL FROM tlbb.SWD_IP2COUNTY WHERE 208797012 BETWEEN IP2NUM_LEFT_LINE AND IP2NUM_RIGHT_LINE
這樣理解起來還真是有點費勁，我們繼續介紹一些相關的業務情況，這些也是我根據資料猜出來的，後面和開發的同學聊，和我想的是一樣的。
比如資料是這樣的形式：
IP_LEFT_LINE IP_RIGHT_LINE IP2NUM_LEFT_LINE IP2NUM_RIGHT_LINE COUNTRY PROVINCE CAPITAL
----- --------------- --------------- ---------------- ----------------- ---------- ---------- -------
5.34.184.0 5.34.191.255 86161408 86163455 中國 北京 北京
5.34.192.0 5.34.223.255 86163456 86171647 中國 河北 石家莊
比如IP 5.34.184.0~5.34.191.255 這個區間代表的是北京地區，5.34.192.0 ~ 5.34.223.255 這個區間代表的是河北石家莊。那麼傳入一個IP就開始對映得到一個數值，通過這個範圍區間來找到對應的地區。
這樣一個語句，明白了需求，好像還是有點道理。
來看看awr的SQL報告怎麼說吧。
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 2397 01-Sep-16 13:00:52 567 1.9
End Snap: 2398 01-Sep-16 13:30:54 566 1.9
Elapsed: 30.03 (mins)
DB Time: 138.02 (mins)
可以看到在短期內，資料庫的負載還是比較高。
而SQL的執行統計資訊如下:
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 8,067,698 276.4 97.4
CPU Time (ms) 8,059,783 276.1 97.8
Executions 29,191 N/A N/A
Buffer Gets 3.3692E+07 1,154.2 74.8
Disk Reads 0 0.0 0.0
Parse Calls 73 0.0 0.0
Rows 28,516 1.0 N/A
User I/O Wait Time (ms) 0 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 4 N/A N/A
Sharable Mem(KB) 46 N/A N/A
如果想得到更多的詳細的資訊，使用診斷事件也是不錯的選擇，這也是11g的一個功能。
比如對某一條SQL開啟sql_trace,可以使用如下的方式。
開啟：
alter system set events 'sql_trace [sql: 63f970ck8r3kc] level 12';
關閉：
alter system set events 'sql_trace [sql: 63f970ck8r3kc] off';
而如果有大量的會話頻繁呼叫，那還是不建議使用的，會生成大量的trace檔案，目前的情況就是如此，需要謹慎使用。
而我們怎麼去分析這個SQL呢，來做一個10053事件吧。
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
SELECT IP_ID,COUNTRY,PROVINCE,CAPITAL FROM test.SWD_IP2COUNTY WHERE 208797012 BETWEEN IP2NUM_LEFT_LINE AND IP2NUM_RIGHT_LINE;
ALTER SESSION SET EVENTS '10053 trace name context off';
這個時候trace的資訊可以看到，內部做了查詢轉換,會把原有的語句轉換為下面的形式：
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "SWD_IP2COUNTY"."IP_ID" "IP_ID","SWD_IP2COUNTY"."COUNTRY" "COUNTRY","SWD_IP2COUNTY"."PROVINCE" "PROVINCE","SWD_IP2C
OUNTY"."CAPITAL" "CAPITAL" FROM "TEST"."SWD_IP2COUNTY" "SWD_IP2COUNTY" WHERE "SWD_IP2COUNTY"."IP2NUM_LEFT_LINE"<=208797012
AND "SWD_IP2COUNTY"."IP2NUM_RIGHT_LINE">=208797012
對於執行路徑和表資訊的統計資訊如下：
Access path analysis for SWD_IP2COUNTY
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for SWD_IP2COUNTY[SWD_IP2COUNTY]
Column (#4):
NewDensity:0.000006, OldDensity:0.000006 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:163088
Column (#4): IP2NUM_LEFT_LINE(
AvgLen: 7 NDV: 163088 Nulls: 0 Density: 0.000006 Min: 16910592 Max: 4261412864
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Column (#5):
NewDensity:0.000006, OldDensity:0.000006 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:163246
Column (#5): IP2NUM_RIGHT_LINE(
AvgLen: 7 NDV: 163246 Nulls: 0 Density: 0.000006 Min: 16912383 Max: 4278190079
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Table: SWD_IP2COUNTY Alias: SWD_IP2COUNTY
Card: Original: 163246.000000 Rounded: 1114 Computed: 1114.40 Non Adjusted: 1114.40
Access Path: TableScan
Cost: 444.56 Resp: 444.56 Degree: 0
Cost_io: 443.00 Cost_cpu: 57439773
Resp_io: 443.00 Resp_cpu: 57439773
Access Path: index (RangeScan)
Index: IND_IP2NUM_LEFT_LINE
resc_io: 15.00 resc_cpu: 713197
ix_sel: 0.006874 ix_sel_with_filters: 0.006874
Cost: 15.02 Resp: 15.02 Degree: 1
Access Path: index (RangeScan)
Index: IND_IP2NUM_RIGHT_LINE
resc_io: 1902.00 resc_cpu: 101091934
ix_sel: 0.993126 ix_sel_with_filters: 0.993126
Cost: 1904.74 Resp: 1904.74 Degree: 1
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: IND_IP2NUM_LEFT_LINE
Cost: 15.02 Degree: 1 Resp: 15.02 Card: 1114.40 Bytes: 0
最大的發現是就是這個語句的邏輯結合自己的分析終於清晰了，但是怎麼優化還沒想好。為什麼效能如此差。
我用查詢轉換後的語句，直接在客戶端執行，檢視執行計劃。
SQL> SELECT "SWD_IP2COUNTY"."IP_ID" "IP_ID","SWD_IP2COUNTY"."COUNTRY" "COUNTRY","SWD_IP2COUNTY"."PROVINCE" "PROVINCE","SWD_IP2COUNTY"."CAPITAL" "CAPITAL" FROM "TEST"."SWD_IP2COUNTY" "SWD_IP2COUNTY" WHERE "SWD_IP2COUNTY"."IP2NUM_LEFT_LINE"<=3030728598
AND "SWD_IP2COUNTY"."IP2NUM_RIGHT_LINE">=3030728598 ;
發現這個時候CBO是分析得出需要走全表掃描的。
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1530 consistent gets
0 physical reads
0 redo size
748 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
這樣就徹底明白了，這裡走索引掃描的選擇度本身就很低，其實效果和走全表差距也不大，這樣一來也就容易理解上面的統計資訊中大量的buffer gets的原因了。
這個語句如何優化呢，問題還是擺在我們面前，根據和開發同學的溝通，對這個需求有了更多的一些瞭解，而且根據IP來確定範圍，是不會出現多個區域的情況，也就是說，這個結果集要不是1條，要麼是沒有對映到的。
所以語句可以簡單優化一下，只需要新增rownum=1即可。
SELECT "SWD_IP2COUNTY"."IP_ID" "IP_ID","SWD_IP2COUNTY"."COUNTRY" "COUNTRY","SWD_IP2COUNTY"."PROVINCE" "PROVINCE","SWD_IP2COUNTY"."CAPITAL" "CAPITAL" FROM "TEST"."SWD_IP2COUNTY" "SWD_IP2COUNTY" WHERE "SWD_IP2COUNTY"."IP2NUM_LEFT_LINE"<=3030728598
AND "SWD_IP2COUNTY"."IP2NUM_RIGHT_LINE">=3030728598 and rownum=1 ;
這個時候走了索引，而且一致性讀大大降低。
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
748 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
這個語句在實際環境中的改進效果如何呢，我抓取了對比的資料，發現效能提升了50%。
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 2,318,173 170.9 91.7
CPU Time (ms) 2,314,428 170.6 92.8
Executions 13,563 N/A N/A
Buffer Gets 9,615,771 709.0 49.7
Disk Reads 0 0.0 0.0
Parse Calls 101 0.0 0.0
Rows 13,151 1.0 N/A
User I/O Wait Time (ms) 0 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 2 N/A N/A
Sharable Mem(KB) 52 N/A N/A
執行計劃如下：
這種優化方式還是值得借鑑的。而且在這個基礎上可以考慮對索引進行優化，也就是建立符合索引，根據數值的分佈情況（存在》=，《=的數值區間），可以考慮對索引進行排序
比如：(IP2NUM_LEFT_LINE desc , IP2NUM_RIGHT_LINE asc)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2124424/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 通過qq進行ip定位
- 慢Sql優化思路SQL優化
- 通過新增條件優化SQL優化SQL
- 通過SQL PROFILE自動優化SQL語句SQL優化
- 通過hint push_subq優化sql優化SQL
- SQL優化例項-思路分析SQL優化
- 通過分析SQL語句的執行計劃優化SQL 二SQL優化
- 通過分析SQL語句的執行計劃優化SQL(總結)SQL優化
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- Sql語句本身的優化-定位慢查詢SQL優化
- Oracle優化案例-儲存過程的優化思路（二十三）Oracle優化儲存過程
- SQL優化思路&結果集重用優化、分割槽索引優化測試SQL優化索引
- SQL優化案例-定位系統中大量的rollback（十八）SQL優化
- 一條sql的優化過程SQL優化
- Oracle 通過rowid秒優SQLOracleSQL
- 通過pl/sql來格式化sqlSQL
- 通過shell指令碼定位效能sql和生成報告指令碼SQL
- 系統的優化思路優化
- 通過ADDM進行SQL調優SQL
- 用VC在區域網實現IP多播通訊
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- 區域性性原理——各類優化的基石優化
- echart map 區域定位 方案 方法
- 程式效能優化-區域性性原理優化
- 通過_optimizer_rownum_pred_based_fkr優化一條sql語句優化SQL
- 通過公網ip或域名下載自己的檔案的一些思路
- windows10中如何讓區域網內其他電腦通過IP訪問網站Windows網站
- 高效的SQL（Function-based Indexes 函式、運算優化思路）SQLFunctionIndex函式優化
- SQL Server 不同網段IP通過名稱訪問SQLServer
- 一條SQL語句的優化過程SQL優化
- 優化同事發過來的一個sql優化SQL
- 通過使用hint unnest調優sql語句SQL
- MySQL通過performance_schema定位未提交事務所執行的SQLMySqlORM
- sql 優化過程之union 替換 orSQL優化
- 通過分割槽進行優化(轉)優化
- Windows區域網通過NTP設定時間同步Windows
- SQL 調優一般思路SQL
- MindSpore模型精度調優實戰：常用的定位精度除錯調優思路模型除錯