关于select for update的性能问题 [转帖]_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方技术论坛-国内领先的Weblogic、Tuxedo中间件技术论坛
联动北方-国内领先的云技术服务提供商
»  游客             当前位置：  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 4492 | 回复: 0   主题： 关于select for update的性能问题 [转帖]        下一篇 
Gavin
注册用户
等级：少校
经验：878
发帖：130
精华：1
注册：2011-7-21
状态：离线
发送短消息息给Gavin 加好友    发送短消息息给Gavin 发消息
发表于： IP：您无权察看 2011-8-8 9:47:22 | [全部帖] [楼主帖] 楼主

转自：http://www.jiagulun.com/thread-394-1-8.html

11g之前的性能问题
select for update是用于数据并发控制的语句。执行该语句后，会在表上加持RX锁（Row Exclusive,10gR1之前是Row Share锁），在被选中记录上加行锁。但是，正是因为其加锁的操作，for update子句会导致select查询的性能下降。
我们先对一条没有for update的select查询做一个SQL Trace，
SQL代码

HELLODBA.COM>alter session set sql_trace=true;
Session altered.
HELLODBA.COM>select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21);
9 rows selected.


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

PARSING IN CURSOR #3 len=105 dep=0 uid=35 oct=3 lid=35 tim=5568777256 hv=2764094589 ad='1f317fe8'
select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21)
END OF STMT
PARSE #3:c=0,e=1853,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=5568777250
EXEC #3:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5568796395
FETCH #3:c=0,e=71,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=5568799616
FETCH #3:c=0,e=277,p=0,cr=27,cu=0,mis=0,r=8,dep=0,og=4,tim=5568803585
STAT #3 id=1 cnt=9 pid=0 pos=1 obj=0 op='INLIST ITERATOR (cr=30 pr=0 pw=0 time=309 us)'
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)'
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代码

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


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

PARSING IN CURSOR #1 len=116 dep=0 uid=35 oct=3 lid=35 tim=5167534009 hv=1951974429 ad='1a63e8a4'
select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21) for update
END OF STMT
PARSE #1:c=0,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5167534004
EXEC #1:c=0,e=657,p=0,cr=29,cu=12,mis=0,r=0,dep=0,og=4,tim=5167553147
FETCH #1:c=0,e=60,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=5167556125
FETCH #1:c=0,e=488,p=0,cr=27,cu=0,mis=0,r=8,dep=0,og=4,tim=5167560187
STAT #1 id=1 cnt=9 pid=0 pos=1 obj=0 op='FOR UPDATE (cr=59 pr=0 pw=0 time=1042 us)'
STAT #1 id=2 cnt=18 pid=1 pos=1 obj=0 op='INLIST ITERATOR (cr=59 pr=0 pw=0 time=686 us)'
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)'
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代码

HELLODBA.COM>conn demo/demo
Connected.
Session altered.
HELLODBA.COM>alter session set sql_trace=true;
Session altered.
HELLODBA.COM>declare
2 cursor r_c is
3 select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21) for update;
4 begin
5 open r_c;
6 end;
7 /
PL/SQL procedure successfully completed.
HELLODBA.COM>conn demo/demo
Connected.
Session altered.
HELLODBA.COM>alter session set sql_trace=true;
Session altered.
HELLODBA.COM>declare
2 cursor r_c is
3 select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21);
4 begin
5 open r_c;
6 end;
7 /
PL/SQL procedure successfully completed.


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

PARSING IN CURSOR #1 len=157 dep=0 uid=35 oct=47 lid=35 tim=4813903056 hv=1931765894 ad='1a47fd68'
declare
cursor r_c is
select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21);
begin
open r_c;
end;
END OF STMT
PARSE #1:c=15625,e=2633,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=4813903051
=====================
PARSING IN CURSOR #2 len=105 dep=1 uid=35 oct=3 lid=35 tim=4813933171 hv=2433529423 ad='1a5d8c80'
SELECT /*+gather_plan_statistics*/* FROM T_TEST1 WHERE OBJECT_ID IN (12,123,41231,532,345,2,141,31323,21)
END OF STMT
PARSE #2:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4813933165
EXEC #2:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4813947559
EXEC #1:c=15625,e=18250,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=4813951275
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='INLIST ITERATOR (cr=0 pr=0 pw=0 time=15 us)'
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)'
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代码

PARSING IN CURSOR #1 len=168 dep=0 uid=35 oct=47 lid=35 tim=4806037526 hv=3705260954 ad='1a6eb274'
declare
cursor r_c is
select /*+gather_plan_statistics*/* from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21) for update;
begin
open r_c;
end;
END OF STMT
PARSE #1:c=0,e=2700,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=4806037521
=====================
PARSING IN CURSOR #2 len=116 dep=1 uid=35 oct=3 lid=35 tim=4806071464 hv=2097265504 ad='1a5087cc'
SELECT /*+gather_plan_statistics*/* FROM T_TEST1 WHERE OBJECT_ID IN (12,123,41231,532,345,2,141,31323,21) FOR UPDATE
END OF STMT
PARSE #2:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=4806071459
EXEC #2:c=0,e=638,p=0,cr=29,cu=12,mis=0,r=0,dep=1,og=4,tim=4806086909
EXEC #1:c=31250,e=19563,p=0,cr=29,cu=12,mis=0,r=1,dep=0,og=4,tim=4806090882
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='FOR UPDATE (cr=29 pr=0 pw=0 time=567 us)'
STAT #2 id=2 cnt=9 pid=1 pos=1 obj=0 op='INLIST ITERATOR (cr=29 pr=0 pw=0 time=264 us)'
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)'
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)'
XCTEND rlbk=0, rd_only=0


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

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;
SESSION_ID OBJECT_ID LOCKED_MODE OBJECT_NAME
---------- ---------- ----------- -----------
313 97819 3 T_TEST1
HELLODBA.COM>select * from v$lock where type in ('TM','TX');
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
1DEC4878 1DEC4890 313 TM 97819 0 3 0 109 0
1DFBDE8C 1DFBDFA8 313 TX 458791 119353 6 0 109 0


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

session 1:
HELLODBA.COM>select owner, table_name from t_test2 where owner='OUTLN';
OWNER TABLE_NAME
------------------------------ ------------------------------
OUTLN OL$
OUTLN OL$HINTS
OUTLN OL$NODES
HELLODBA.COM>select owner, table_name from t_test2 where owner='OUTLN' and rownum<=1 for update;
OWNER TABLE_NAME
------------------------------ ------------------------------
OUTLN OL$
session 2:
HELLODBA.COM>select owner, table_name from t_test2 where owner='OUTLN' for update skip locked;
OWNER TABLE_NAME
------------------------------ ------------------------------
OUTLN OL$HINTS
OUTLN OL$NODES


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

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


SQL代码

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


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

HELLODBA.COM>select object_id, object_name from t_test1 where object_id in (12,123,41231,532,345,2,141,31323,21);
9 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
709 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)
9 rows processed
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;
9 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
9 db block gets
17 consistent gets
0 physical reads
1988 redo size
1043 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed


可以看到逻辑IO大大减少，增加了sorts in memory。


赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码： 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论

Weblogic中间件技术论坛

 |

Tuxedo中间件技术论坛

 |

数据库论坛

 |

Java论坛

 |

Linux/unix论坛

 |

网站地图


Powered by LandingBJ     @ 2008-2018     联动北方   
京ICP证号090597    京ICP备案09017624号    京公网安备110112000093号    
400-810-2327     Email：service@landingbj.com   

Powered by landingbj @ 2008-2015 联动北方
京ICP证号090597 京ICP备案09017624号 京公网安备110112000093号
86-27-87056338 Email：service@landingbj.com