一次简单的SQL调整

最新推荐文章于 2024-10-30 17:13:21 发布

原创最新推荐文章于 2024-10-30 17:13:21 发布 · 1.2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#sql #oracle #statistics #access #table #disk

oracle 专栏收录该内容

21 篇文章

订阅专栏

本文介绍了一次Oracle数据库慢查询优化经历，发现字段类型不匹配导致未使用索引，通过调整查询方式显著提升性能，并讨论了绑定变量的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

突然在awr上看到了这么一条sql执行了十几秒：

select * from q_mo where MO_MSGID=5825623001725210715;

以为是没建索引，看了下索引在的。不管了，先跑个执行计划再说：

SQL> select * from sms.q_mo where MO_MSGID=5825623001725210715;

Elapsed: 00:00:18.60

Execution Plan
----------------------------------------------------------
Plan hash value: 2547691741

--------------------------------------------------------------------------------
---------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
Pstart| Pstop |

--------------------------------------------------------------------------------
---------------

| 0 | SELECT STATEMENT | | 1 | 98 | 14305 (1)| 00:02:52 |
| |

| 1 | PARTITION RANGE SINGLE| | 1 | 98 | 14305 (1)| 00:02:52 |
1 | 1 |

|* 2 | TABLE ACCESS FULL | Q_MO | 1 | 98 | 14305 (1)| 00:02:52 |
1 | 1 |

--------------------------------------------------------------------------------
---------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(TO_NUMBER("MO_MSGID")=5825623001725210715)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
52437 consistent gets
50784 physical reads
0 redo size
1395 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

看了下，一条简单的sql跑了18秒多，果然没走索引，是全表扫描。再往下看，MO_MSGID根本就是varchar2类型的。没想到oracle优化器是这么转的，我以为类型不同oracle会把后面的数字转成字符串，没想到的是它用to_number函数把MO_MSGID这一列都转成了数值型，这样自然就不会走原来的索引了。

修改以后如下：

SQL> select * from sms.q_mo where MO_MSGID='5825623001725210715';

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 1889654823

--------------------------------------------------------------------------------
---------------------------------------

| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time | Pstart| Pstop |

--------------------------------------------------------------------------------
---------------------------------------

| 0 | SELECT STATEMENT | | 1 | 98 |
4 (0)| 00:00:01 | | |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| Q_MO | 1 | 98 |
4 (0)| 00:00:01 | 1 | 1 |

|* 2 | INDEX RANGE SCAN | MO_SMOMSGID_PART | 1 | |
3 (0)| 00:00:01 | | |

--------------------------------------------------------------------------------
---------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("MO_MSGID"='5825623001725210715')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
2 physical reads
0 redo size
1399 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

运行时间一下从18.6秒减少到了0.03秒了，原本逻辑读52437减少到5，物理读50784减少到2。

这里还要说下绑定变量。我们都知道在高并发的OLTP系统中，使用绑定变量还是能够显著提高oracle数据库的运行效率的。但我们这里并发量不算高，和开发说了几次他们也没当回事，我也就没有再坚持。但是在awr的SQL Statistics里面top10的sql语句都是按照单位*执行次数来排列的。这样没有使用绑定变量的sql语句虽然效率很低，但是oracle是把它当做单独的sql来看待的，所以在top10里面不一定能够排的上，那么在后期的调优过程中就很可能被忽略掉。