[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2011-01-13 08:26:47
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代码
- 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.
From the trace file, we can find the CR is 30, which occured in Fetch phase.
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)'
Then, trace the "for update" query.
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.
And we found the CR is 59, which is almose twice of former, despite that they have the same access paths.
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)'
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代码
- 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.
Read the trace file, we can get the point that the read-only cursor did not access data when open cursor.
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)'
While the "for update" cursor did!
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
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代码
- 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
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代码
- 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
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代码
- 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 |
- --------------------------------------------------------------------------------------------
Execution plan in 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 |
- ---------------------------------------------------------------------------------------------
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代码
- 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
Logic IOs are cut down, just increase a tiny sort in memory.