突然在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里面不一定能够排的上,那么在后期的调优过程中就很可能被忽略掉。