HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Select For Update and performance

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2011-01-13 08:26:47

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

Performance issue before 11g

    "select for update" is used for concurrent data access control. Once executed such SQL, oracle will hold a RX (Row Exclusive, RS in 9i instead) lock on the table, and hold Row Lock on those selected rows. However, such lock holding operation degrades the performance of the statement.

    To compare the performance, we traced a normal "select" without "for update".

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. rows selected.  

    From the trace file, we can find the CR is 30, which occured in Fetch phase.

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)'  

    Then, trace the "for update" query.

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. rows selected.  

    And we found the CR is 59, which is almose twice of former, despite that they have the same access paths.

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)'  

    Look the performance statistics data in detail, we can find the additional 29 CRs occured in EXEC phase. As we know, each Select statement actually operate an implict cursor. And Exec equal to Open Cursor. That is to say, once the cursor was opened, oracle accessed the data, which will not happen in a read-only cursor.

    We could compare the explict cursors.

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.  

    Read the trace file, we can get the point that the read-only cursor did not access data when open cursor.

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)'  

    While the "for update" cursor did!

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  

    Why it accessed the data before fetching data? Because oracle should detect if there is any locks conflict and hold locks to block other transactions access the data. After we opened a "for update" cursor, we could checked the lock status.

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  

Enhancement in 11rR1

    In 11gR1, oracle provides "skip locked" clause. Once add this clause, the transaction will not enqueue or raise an ORA-00054 exception when it detected locked rows, but skip them instead, lock and return those unlocked rows.

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  

Enhancement in 11gR2

    If you say that enhancement in 11gR1 did not affact performence directly, then you may be satisfied with the enhancement in 11gR2.

    Before introducing such enhancement, we should understand the execution plan changes.

    Execution plan before 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. 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. --------------------------------------------------------------------------------------------  

    Execution plan in 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. 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. ---------------------------------------------------------------------------------------------  

    The obviouse change is that there is a "buffer sort" operation between "for update" and acess paths. "buffer sort" is an operation to sort the data in private memory (sort area). However, there isn't any sort operation require in our query. We could understand that Oracle just adopt the mechanism to avoid read data from buffer cache when fetching data. Not only will it decrease the CR number, but also reduce latch requests.

    Wo can note the improvement on performance:

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. 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. 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  

    Logic IOs are cut down, just increase a tiny sort in memory.

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat