HelloDBA [English]
搜索Internet 搜索 HelloDBABA
首页
技术文档
产品及下载
订阅
  • 至 Google
  • 至 Yahoo
  • 至 zhuxia
  • 至 xianguo
  • 至 QQ email
  • 至 YouDao
关于
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

select for update 与性能

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2011-01-13 08:26:47

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

11g之前的性能问题

    select for update是用于数据并发控制的语句。执行该语句后，会在表上加持RX锁（Row Exclusive,10gR1之前是Row Share锁），在被选中记录上加行锁。但是，正是因为其加锁的操作，for update子句会导致select查询的性能下降。

    我们先对一条没有for update的select查询做一个SQL Trace，

SQL代码
  1. HELLODBA.COM>alter session set sql_trace=true;  
  2.   
  3. Session altered.  
  4.   
  5. HELLODBA.COM>select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21);  
  6. ...  
  7.   
  8. 9 rows selected.  

    从trace文件中看到，它的CR是30次，并且发生在fetch阶段。

SQL代码
  1. PARSING IN CURSOR #3 len=105 dep=0 uid=35 oct=3 lid=35 tim=5568777256 hv=2764094589 ad='1f317fe8'  
  2. select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21)  
  3. END OF STMT  
  4. PARSE #3:c=0,e=1853,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=5568777250  
  5. EXEC #3:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5568796395  
  6. FETCH #3:c=0,e=71,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=5568799616  
  7. FETCH #3:c=0,e=277,p=0,cr=27,cu=0,mis=0,r=8,dep=0,og=4,tim=5568803585  
  8. STAT #3 id=1 cnt=9 pid=0 pos=1 obj=0 op='INLIST ITERATOR  (cr=30 pr=0 pw=0 time=309 us)'  
  9. STAT #3 id=2 cnt=9 pid=1 pos=1 obj=97819 op='TABLE ACCESS BY INDEX ROWID T_TEST1 (cr=30 pr=0 pw=0 time=271 us)'  
  10. STAT #3 id=3 cnt=9 pid=2 pos=1 obj=165726 op='INDEX RANGE SCAN T_TEST1_PK (cr=19 pr=0 pw=0 time=147 us)'  

    我们再对加上for update子句的查询做trace

SQL代码
  1. HELLODBA.COM>alter session set sql_trace=true;  
  2.   
  3. Session altered.  
  4.   
  5. HELLODBA.COM>select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21) for update;  
  6. ...  
  7.   
  8. 9 rows selected.  

    可以看到，查询计划的访问路径相同，但是CR将近翻倍，为59次。

SQL代码
  1. PARSING IN CURSOR #1 len=116 dep=0 uid=35 oct=3 lid=35 tim=5167534009 hv=1951974429 ad='1a63e8a4'  
  2. select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21) for update  
  3. END OF STMT  
  4. PARSE #1:c=0,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5167534004  
  5. EXEC #1:c=0,e=657,p=0,cr=29,cu=12,mis=0,r=0,dep=0,og=4,tim=5167553147  
  6. FETCH #1:c=0,e=60,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=5167556125  
  7. FETCH #1:c=0,e=488,p=0,cr=27,cu=0,mis=0,r=8,dep=0,og=4,tim=5167560187  
  8. STAT #1 id=1 cnt=9 pid=0 pos=1 obj=0 op='FOR UPDATE  (cr=59 pr=0 pw=0 time=1042 us)'  
  9. STAT #1 id=2 cnt=18 pid=1 pos=1 obj=0 op='INLIST ITERATOR  (cr=59 pr=0 pw=0 time=686 us)'  
  10. STAT #1 id=3 cnt=18 pid=2 pos=1 obj=97819 op='TABLE ACCESS BY INDEX ROWID T_TEST1 (cr=59 pr=0 pw=0 time=606 us)'  
  11. STAT #1 id=4 cnt=18 pid=3 pos=1 obj=165726 op='INDEX RANGE SCAN T_TEST1_PK (cr=37 pr=0 pw=0 time=320 us)'  

    再仔细看性能数据部分，可以发现多出的29次CR发生EXEC阶段。我们知道，每一个select实际上都操作了一个隐含游标。exec就相当于打开游标。也就是说，加上了for update以后，在打开游标时就对数据进行了一次扫描，而只读游标则不会进行该操作。

    实际上，我们也可以通过对显式游标做对比：

SQL代码
  1. HELLODBA.COM>conn demo/demo  
  2. Connected.  
  3.   
  4. Session altered.  
  5.   
  6. HELLODBA.COM>alter session set sql_trace=true;  
  7.   
  8. Session altered.  
  9.   
  10. HELLODBA.COM>declare  
  11.   2    cursor r_c is  
  12.   3      select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21) for update;  
  13.   4  begin  
  14.   5    open r_c;  
  15.   6  end;  
  16.   7  /  
  17.   
  18. PL/SQL procedure successfully completed.  
  19.   
  20. HELLODBA.COM>conn demo/demo  
  21. Connected.  
  22.   
  23. Session altered.  
  24.   
  25. HELLODBA.COM>alter session set sql_trace=true;  
  26.   
  27. Session altered.  
  28.   
  29. HELLODBA.COM>declare  
  30.   2    cursor r_c is  
  31.   3      select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21);  
  32.   4  begin  
  33.   5    open r_c;  
  34.   6  end;  
  35.   7  /  
  36.   
  37. PL/SQL procedure successfully completed.  

    得到trace文件后，可以看到只读游标在打开游标时没有访问数据：

SQL代码
  1. PARSING IN CURSOR #1 len=157 dep=0 uid=35 oct=47 lid=35 tim=4813903056 hv=1931765894 ad='1a47fd68'  
  2. declare  
  3.   cursor r_c is  
  4.     select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21);  
  5. begin  
  6.   open r_c;  
  7. end;  
  8. END OF STMT  
  9. PARSE #1:c=15625,e=2633,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=4813903051  
  10. =====================  
  11. PARSING IN CURSOR #2 len=105 dep=1 uid=35 oct=3 lid=35 tim=4813933171 hv=2433529423 ad='1a5d8c80'  
  12. SELECT /*+gather_plan_statistics*/* FROM T_TEST1 WHERE OBJECT_ID IN (12,123,41231,532,345,2,141,31323,21)  
  13. END OF STMT  
  14. PARSE #2:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4813933165  
  15. EXEC #2:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4813947559  
  16. EXEC #1:c=15625,e=18250,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=4813951275  
  17. STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='INLIST ITERATOR  (cr=0 pr=0 pw=0 time=15 us)'  
  18. STAT #2 id=2 cnt=0 pid=1 pos=1 obj=97819 op='TABLE ACCESS BY INDEX ROWID T_TEST1 (cr=0 pr=0 pw=0 time=7 us)'  
  19. STAT #2 id=3 cnt=0 pid=2 pos=1 obj=165726 op='INDEX RANGE SCAN T_TEST1_PK (cr=0 pr=0 pw=0 time=4 us)'  

    而加了for update的游标则相反：

SQL代码
  1. PARSING IN CURSOR #1 len=168 dep=0 uid=35 oct=47 lid=35 tim=4806037526 hv=3705260954 ad='1a6eb274'  
  2. declare  
  3.   cursor r_c is  
  4.     select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21) for update;  
  5. begin  
  6.   open r_c;  
  7. end;  
  8. END OF STMT  
  9. PARSE #1:c=0,e=2700,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=4806037521  
  10. =====================  
  11. PARSING IN CURSOR #2 len=116 dep=1 uid=35 oct=3 lid=35 tim=4806071464 hv=2097265504 ad='1a5087cc'  
  12. SELECT /*+gather_plan_statistics*/* FROM T_TEST1 WHERE OBJECT_ID IN (12,123,41231,532,345,2,141,31323,21) FOR UPDATE  
  13. END OF STMT  
  14. PARSE #2:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4806071459  
  15. EXEC #2:c=0,e=638,p=0,cr=29,cu=12,mis=0,r=0,dep=1,og=4,tim=4806086909  
  16. EXEC #1:c=31250,e=19563,p=0,cr=29,cu=12,mis=0,r=1,dep=0,og=4,tim=4806090882  
  17. STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='FOR UPDATE  (cr=29 pr=0 pw=0 time=567 us)'  
  18. STAT #2 id=2 cnt=9 pid=1 pos=1 obj=0 op='INLIST ITERATOR  (cr=29 pr=0 pw=0 time=264 us)'  
  19. STAT #2 id=3 cnt=9 pid=2 pos=1 obj=97819 op='TABLE ACCESS BY INDEX ROWID T_TEST1 (cr=29 pr=0 pw=0 time=232 us)'  
  20. STAT #2 id=4 cnt=9 pid=3 pos=1 obj=165726 op='INDEX RANGE SCAN T_TEST1_PK (cr=18 pr=0 pw=0 time=119 us)'  
  21. XCTEND rlbk=0, rd_only=0  

    之所以在打开游标时就需要访问数据，是因为oracle需要在获取数据之前要检测是否存在冲突/是否需要等待，如果没有冲突，则对数据和表加锁。我们可以对上面的显示游标打开查看到锁的状态：

SQL代码
  1. HELLODBA.COM>select l.session_id, l.object_id, l.locked_mode, o.object_name from v$locked_object l, dba_objects o where l.object_id = o.object_id;  
  2.   
  3. SESSION_ID  OBJECT_ID LOCKED_MODE OBJECT_NAME  
  4. ---------- ---------- ----------- -----------  
  5.        313      97819           3     T_TEST1  
  6.   
  7. HELLODBA.COM>select * from v$lock where type in ('TM','TX');  
  8.   
  9. ADDR     KADDR           SID TYPE          ID1        ID2      LMODE    REQUEST      CTIME      BLOCK  
  10. -------- -------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------  
  11. 1DEC4878 1DEC4890        313 TM          97819          0          3          0        109          0  
  12. 1DFBDE8C 1DFBDFA8        313 TX         458791     119353          6          0        109          0  

11gR1中的改进

    在11gR1中，oracle增加了skip locked子句。此时，如果探测到了记录已经被锁，就不会进入等待队列或者抛出ORA-00054错误，而是跳过该条记录继续执行。

SQL代码
  1. session 1:  
  2. HELLODBA.COM>select owner, table_name from t_test2 where owner='OUTLN';  
  3.   
  4. OWNER                          TABLE_NAME  
  5. ------------------------------ ------------------------------  
  6. OUTLN                          OL$  
  7. OUTLN                          OL$HINTS  
  8. OUTLN                          OL$NODES  
  9.   
  10. HELLODBA.COM>select owner, table_name from t_test2 where owner='OUTLN' and rownum<=1 for update;  
  11.   
  12. OWNER                          TABLE_NAME  
  13. ------------------------------ ------------------------------  
  14. OUTLN                          OL$  
  15.   
  16. session 2:  
  17.   
  18. HELLODBA.COM>select owner, table_name from t_test2 where owner='OUTLN' for update skip locked;  
  19.   
  20. OWNER                          TABLE_NAME  
  21. ------------------------------ ------------------------------  
  22. OUTLN                          OL$HINTS  
  23. OUTLN                          OL$NODES  

11gR2中的改进

    如果说上述改进没有对性能起到直接影响的话，那么11gR2则真正的提高了for update的性能。看性能数据之前，我们先看下它的执行计划的改变：

    11gR2之前版本：

SQL代码
  1. HELLODBA.COM>select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21) for update;  
  2.   
  3. 9 rows selected.  
  4.   
  5. Execution Plan  
  6. ----------------------------------------------------------  
  7. Plan hash value: 426862601  
  8.   
  9. --------------------------------------------------------------------------------------------  
  10. | Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
  11. --------------------------------------------------------------------------------------------  
  12. |   0 | SELECT STATEMENT              |            |     9 |   891 |     6   (0)| 00:00:06 |  
  13. |   1 |  FOR UPDATE                   |            |       |       |            |          |  
  14. |   2 |   INLIST ITERATOR             |            |       |       |            |          |  
  15. |   3 |    TABLE ACCESS BY INDEX ROWID| T_TEST1    |     9 |   891 |     6   (0)| 00:00:06 |  
  16. |*  4 |     INDEX RANGE SCAN          | T_TEST1_PK |     9 |       |     5   (0)| 00:00:06 |  
  17. --------------------------------------------------------------------------------------------  

    11gR2:

SQL代码
  1. HELLODBA.COM>select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21) for update;  
  2.   
  3. 9 rows selected.  
  4.   
  5. Execution Plan  
  6. ----------------------------------------------------------  
  7. Plan hash value: 344358954  
  8.   
  9. ---------------------------------------------------------------------------------------------  
  10. | Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
  11. ---------------------------------------------------------------------------------------------  
  12. |   0 | SELECT STATEMENT               |            |     9 |   882 |    10   (0)| 00:00:01 |  
  13. |   1 |  FOR UPDATE                    |            |       |       |            |          |  
  14. |   2 |   BUFFER SORT                  |            |       |       |            |          |  
  15. |   3 |    INLIST ITERATOR             |            |       |       |            |          |  
  16. |   4 |     TABLE ACCESS BY INDEX ROWID| T_TEST1    |     9 |   882 |    10   (0)| 00:00:01 |  
  17. |*  5 |      INDEX UNIQUE SCAN         | T_TEST1_PK |     9 |       |     9   (0)| 00:00:01 |  
  18. ---------------------------------------------------------------------------------------------  

    可以看到，for update与数据访问之间多出了一个buffer sort。而buffer sort是oracle在做排序操作时，将部分或全部数据放入私有内存（sort area）中进行排序的操作。但是，我们这里的语句并没有做任何排序操作。只所以引入这个操作，是为了借助oracle的排序机制，将已经访问和加锁的数据放入私有内存中，这样，在读取数据时就避免了再次访问buffer cache。不仅可以减少cr数量，而且可以减少latch请求。

    我们看下这一改进带来的性能变化：

SQL代码
  1. HELLODBA.COM>select object_id, object_name from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21);  
  2.   
  3. 9 rows selected.  
  4.   
  5.   
  6. Statistics  
  7. ----------------------------------------------------------  
  8.           0  recursive calls  
  9.           0  db block gets  
  10.          18  consistent gets  
  11.           0  physical reads  
  12.           0  redo size  
  13.         709  bytes sent via SQL*Net to client  
  14.         384  bytes received via SQL*Net from client  
  15.           2  SQL*Net roundtrips to/from client  
  16.           0  sorts (memory)  
  17.           0  sorts (disk)  
  18.           9  rows processed  
  19.   
  20. HELLODBA.COM>select object_id, object_name from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21) for update;  
  21.   
  22. 9 rows selected.  
  23.   
  24.   
  25. Statistics  
  26. ----------------------------------------------------------  
  27.           0  recursive calls  
  28.           9  db block gets  
  29.          17  consistent gets  
  30.           0  physical reads  
  31.        1988  redo size  
  32.        1043  bytes sent via SQL*Net to client  
  33.         384  bytes received via SQL*Net from client  
  34.           2  SQL*Net roundtrips to/from client  
  35.           1  sorts (memory)  
  36.           0  sorts (disk)  
  37.           9  rows processed  

    可以看到逻辑IO大大减少，增加了sorts in memory。
上一篇： 下一篇：
本类中的所有文章

Top

Copyright ©2005，HelloDBA.Com 保留一切权利

申明
by fuyuncat