#隱式轉換?

第一章 適用環境

• oracle 11g+
• linux 6.9+

第二章 Top SQL概況

下面列出我們發現的特定模塊中Top SQL的相關情況：

SQL_ID	模塊	SQL類型	主要問題
fnc58puaqkd1n	無	select	索引創建不合理，導致全索引掃描，產生了大量邏輯讀

第三章 SQL優化方案

3.1 SQL_ID:fnc58puaqkd1n

3.1.1 SQL文本

SELECT MAX(TEST_ID) TEST_ID FROM TEST1 WHERE TEST_NAME = 28                                                

3.1.2 SQL執行計劃

PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fnc58puaqkd1n, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT MAX(TEST_ID) TEST_ID FROM TEST1 WHERE TEST_NAME = 28                                                                                                                                     Plan hash value: 2527920730                                                                                                                                                                             ---------------------------------------------------------------------------------------------------                                                                                                     
| Id  | Operation             | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                     
---------------------------------------------------------------------------------------------------                                                                                                     
|   0 | SELECT STATEMENT      |                           |       |       | 23200 (100)|          |                                                                                                     
|   1 |  SORT AGGREGATE       |                           |     1 |    13 |            |          |                                                                                                     
|*  2 |   INDEX FAST FULL SCAN| TEST1_TEST_ID_IDX         |     1 |    13 | 23200   (3)| 00:04:39 |                                                                                                     
---------------------------------------------------------------------------------------------------                                                                                                     Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     2 - filter(TO_NUMBER("TEST_NAME")=28)     

3.1.3 SQL資源消耗

            CPU(MS)  ELA(MS)     DISK          GET        ROWS      ROWS APPLI(MS) CONCUR(MS) CLUSTER(MS) USER_IO(MS)    PLSQL     JAVA                                                                 
EXEC       PRE EXEC PRE EXEC PRE EXEC     PRE EXEC    PRE EXEC PRE FETCH  PER EXEC   PER EXEC    PER EXEC    PER EXEC PER EXEC PER EXEC SQL_PROFILE                                                     
---------- -------- -------- -------- ------------ ----------- --------- --------- ---------- ----------- ----------- -------- -------- ---------------                                                 
2887          2,116    2,146        0       89,108           1         1         0          0           0           0        0        0                                                                 

該SQL執行了2887次，每次執行的平均邏輯讀為89,108，物理讀為0，每次返回1條數據，耗時2.146秒，其中CPU消耗2.116秒。

四、 問題分析及優化思路

通過分析SQL文本，發現該SQL為單表掃描類型，select涉及max函數。

通過分析執行計劃，SQL主要性能消耗在TEST1_TEST_ID_IDX 的全索引掃描，該索引的體積達到705MB，由于該SQL執行頻率高達2887次，等待主要在CPU上，這就是該SQL執行耗時只有2秒原因。

那為什么會導致該SQL走的是快速索引全掃描，而不是索引范圍掃描？觀察到執行計劃中有一個隱式轉換，是它的原因嗎？通過測試發現并不是。那問題在哪？

再看下TEST1_TEST_ID_IDX組成列的順序是TEST_ID、TEST_NAME，而SQL的過濾條件是TEST_NAME，針對TEST_ID算MAX值，到這里問題就清楚了，索引創建的順序有問題，前導列應該是TEST_NAME，正確的索引創建順序應該是TEST_NAME、TEST_ID，讓執行計劃走MAX/MIN類型。

另外還有一個問題，該SQL存在隱式轉換，觀察到TEST_NAME字段類型是varchar2,而SQL傳值類型是數值，如果不消除這個問題，有正確的索引仍然無法走最優的執行計劃。

結合以上分析，該SQL需要消除隱式轉換，再創建合適的索引即可優化。

五、 優化方案

5.1 創建組合索引

CREATE INDEX "TESTUSER"."TEST1_N1" ON "TESTUSER"."TEST1" (TEST_NAME,TEST_ID) tablespace TESTUSER_IDX  online;

5.2 查看索引效果

SQL> SELECT MAX(TEST_ID) TEST_ID FROM TESTUSER.TEST1 WHERE TEST_NAME = 28;Execution Plan
----------------------------------------------------------
Plan hash value: 3698544155------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |     1 |    13 | 13251   (3)| 00:02:40 |
|   1 |  SORT AGGREGATE       |                  |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| TEST1_N1         |  1075K|    13M| 13251   (3)| 00:02:40 |
------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter(TO_NUMBER("TEST_NAME")=28)

觀察仍然走的TEST1_N1索引INDEX FAST FULL SCAN。有兩種方案可以解決，一是創建函數索引，二是修改傳參類型，這里我們選擇改傳參類型。

5.3 優化后的效果

SQL> SELECT MAX(TEST_ID) TEST_ID FROM TESTUSER.TEST1 WHERE TEST_NAME = '28';Execution Plan
----------------------------------------------------------
Plan hash value: 1923666499-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                  |     1 |    13 |            |          |
|   2 |   FIRST ROW                  |                  |     1 |    13 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| TEST1_N1         |     1 |    13 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------3 - access("TEST_NAME"='28')

六、 優化效果對比

通過確認關鍵表數據量，制定執行計劃，可以減少每次查詢的邏輯讀和物理讀，提高SQL執行性能。

	邏輯讀	物理讀	執行時間(s)
優化前	89,108	0	2.3
優化后	3	0	0.01

---

🚀 更多數據庫干貨，歡迎關注【安呀智數據坊】

如果你覺得這篇文章對你有幫助，歡迎點贊 👍、收藏 ? 和留言 💬 交流，讓我知道你還想了解哪些數據庫知識！

📬 想系統學習更多數據庫實戰案例與技術指南？

📊 實戰項目分享

📚 技術原理講解

🧠 數據庫架構思維

🛠 工具推薦與實用技巧

立即關注，持續更新中 👇