关于select for update的性能问题
本帖最后由 oraunix 于 2011-1-21 15:52 编辑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;
4begin
5 open r_c;
6end;
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);
4begin
5 open r_c;
6end;
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_IDOBJECT_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
----------------------------------------------------------
0recursive calls
0db block gets
18consistent gets
0physical reads
0redo size
709bytes sent via SQL*Net to client
384bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
9rows 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
----------------------------------------------------------
0recursive calls
9db block gets
17consistent gets
0physical reads
1988redo size
1043bytes sent via SQL*Net to client
384bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
1sorts (memory)
0sorts (disk)
9rows processed
可以看到逻辑IO大大减少，增加了sorts in memory。
页:
[1]