update 语句更新慢如何优化

  最近一次升级研发的一个SQL导致数据库运行的非常的慢，后经awr报告定位到一个更新语句导致把系统的cpu将近吃紧60%左右，让研发改下sql，改了很久性能还是不行。后来我实在看不下去了，就帮忙看了些sql

原始sql 其实很简单就是一个更新语句，但是系统里的数据量很大，研发在写sql的时候没有考虑索引，他用to_char函数把该列的的索引给屏蔽掉了，我的测试只是一小部分数据。

SQL> explain plan for
  2   update t_tso tso
  3     set tso.is_special_sn = '1'
  4      where exists
  5         (select t.serial_no from t_special_no_line t where
  6          t.serial_no=tso.serial_no)
  7         and to_char(tso.created_date, 'yyyy-MM')=to_char(sysdate, 'yyyy-MM')

   8        and tso.is_award_flg = '0') ;
 
Explained
 
Executed in 0.047 seconds
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation             | Name                     | Rows  | Bytes | Cost
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |                          |  466 |   797K|  3192
|   1 |  UPDATE               | T_TSO               |       |       |
|   2 |   HASH JOIN RIGHT SEMI|                          |  466 |   797K|  3192
|   3 |    TABLE ACCESS FULL  | T_SPECIAL_NO_LINE |  4455 |   134K|    84
|   4 |    PARTITION RANGE ALL|                          |  554 |   908K|  3107
|   5 |     TABLE ACCESS FULL | T_TSO              |  554 |   908K|  3107
--------------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version
 
15 rows selected
 
Executed in 0.391 seconds

其实改写后的语句很简单就是一个merger into 和一个trunc函数就搞定了。

SQL> explain plan for
  2  merge into t_tso tso
  3  using t_special_no_line t
  4  on (t.serial_no = tso.serial_no and tso.created_date >= trunc(sysdate, 'mm') and tso.is_award_flg = '0')
  5  when matched then
  6    update set tso.is_special_sn = '1'
  7  ;
 
Explained
 
Executed in 0.047 seconds
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Rows
--------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                       |                          |     1
|   1 |  MERGE                                | T_TSO               |
|   2 |   VIEW                                |                          |
|   3 |    HASH JOIN                          |                          |     1
|   4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| T_TSO               |     1
|   5 |      INDEX RANGE SCAN                 | CREATED_DATE_201208      |     1
|   6 |     TABLE ACCESS FULL                 | T_SPECIAL_NO_LINE |  4455
--------------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version
 
16 rows selected
 
Executed in 0.125 seconds