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

 

发布了220 篇原创文章 · 获赞 31 · 访问量 30万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览