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