1. 行級鎖

????Session1: select * from table for update;這樣的話是把表鎖了，如果Session2仍然用“select * from table for update ”查詢就會被堵塞。

??? 現在的需求是把鎖定的行屏蔽掉，讓另外的SESSION查的時候不會被堵塞，只是屏蔽了鎖定的行。例子如下：

session1:?

?>???select * from table;

? >?? field1? field2??

? >????? 1????asdfa?????

? >????? 2??? hgrew

? >????? 3??? poptoe

? 3 rows selected

??> select * from table where field1=1 for update;

session2:

? >? select * from table for update skip locked;

? >? field1? field2

? >?? 2??????hgrew

? >?? 3??????poptoe

? 2 rows selected

這樣就查詢到只有2條了。SQLSERVER好像也有類似的：select?? *?? from table?with(UPDLOCK,READPAST)?? where???id?? =??1

注:不過這樣的方法還是比較危險的，通常是用字段做標記，UPDATE字段表示占用或者空閑

?

2. SQL優化

原SQL，用時69.375S:

select count(*)? cnt from sfism4.r_wip_tracking_t
where? pallet_no='WC7182021T673856OQ'?
or pallet_no in (select Pallet_no from sfism4.r_wip_tracking_t
? where serial_number='WC7182021T673856OQ' and Pallet_No is not null and Pallet_no<>'N/A')

?

優化后SQL，用時0.047S：

select count(w.serial_number)? from sfism4.r_wip_tracking_t w
where? exists(select s.serial_number from sfism4.r_wip_tracking_t s
? where (s.serial_number='WC7182021T673856OQ' or? s.pallet_no='WC7182021T673856OQ') and s.Pallet_No is not null and s.Pallet_no<>'N/A' and w.pallet_no = s.pallet_no)

說明一下，表sfism4.r_wip_tracking_t中，serial_number有唯一索引，pallet_no也有索引，全表2495691條記錄

分析：

原SQL的執行計劃：

SELECT STATEMENT, GOAL = CHOOSE???Cost=7536?Cardinality=1?Bytes=4
?SORT AGGREGATE????Cardinality=1?Bytes=4
? FILTER?????
?? TABLE ACCESS FULL?Object owner=SFISM4?Object name=R_WIP_TRACKING_T?Cost=7536?Cardinality=106288?Bytes=425152
?? FILTER?????
??? TABLE ACCESS BY INDEX ROWID?Object owner=SFISM4?Object name=R_WIP_TRACKING_T?Cost=3?Cardinality=1?Bytes=19
???? INDEX UNIQUE SCAN?Object owner=SFISM4?Object name=WIP_SN?Cost=2?Cardinality=2124150?

?

優化后的執行計劃：

SELECT STATEMENT, GOAL = CHOOSE???Cost=74?Cardinality=1?Bytes=23
?SORT AGGREGATE????Cardinality=1?Bytes=23
? NESTED LOOPS???Cost=74?Cardinality=478?Bytes=10994
?? SORT UNIQUE?????
??? TABLE ACCESS BY INDEX ROWID?Object owner=SFISM4?Object name=R_WIP_TRACKING_T?Cost=28?Cardinality=22?Bytes=418
???? BITMAP CONVERSION TO ROWIDS?????
????? BITMAP OR?????
?????? BITMAP CONVERSION FROM ROWIDS?????
??????? INDEX RANGE SCAN?Object owner=SFISM4?Object name=WIP_SN?Cost=2??
?????? BITMAP CONVERSION FROM ROWIDS?????
??????? INDEX RANGE SCAN?Object owner=SFISM4?Object name=R107_PALLET_NO?Cost=3??
?? INDEX RANGE SCAN?Object owner=SFISM4?Object name=R107_PALLET_NO?Cost=2?Cardinality=22?Bytes=88
? 猜測是OR的原因導致沒有走索引

?3.左聯接丟失數據

select * from table1 left join table2?? on table1.name=table2.name?? and table2.id=1001
----------------------------------
select * from table1 left join table2?? on table1.name=table2.name where?? table2.id=1001

這里,第二個SQL會丟失部分數據

性能上 老婆大人推薦:可以寫成select * from table1 left join (select * from table2 where table2.xxx=1001) on table1.yyy=table2.zzz

這樣就可以走table2.xxx的索引了,而且table1的所有記錄都能顯示出來

4.查看鎖對象

? select * from v$locked_object;根據OBJECT_ID查看哪個OBJECT被鎖定，select * from dba_objects where object_id = 49549;

? 根據SESSION_ID 查看哪個SESSION鎖的，select * from V$session;

最后滅了SESSION,ALTER SYSTEM KILL SESSION '11,822';? (SID,SERIAL#)

ORACLE零碎筆記