#隐式转换

第一章适用环境

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')