针对这种SQL建议处理办法：
把需要拼接的字符串转成采用table类型，in转变成JOIN操作，这样sql版本大大减少，并可以共享。
具体办法：
如果需要绑定in list，首先，需要创建两个类型(type)：
针对数据类型的
CREATE OR REPLACE TYPE NUMTABLETYPE as table of number;
针对字符串类型的(每个list的单元大小不要超过200字节)
create or replace type vartabletype as table of varchar2(200);
然后创建两个相关的函数
数字列表函数
create or replace function str2numList( p_string in varchar2 ) return numTableType
as
v_str long default p_string || ‘,‘;
v_n number;
v_data numTableType := numTableType();
begin
loop
v_n := to_number(instr( v_str, ‘,‘ ));
exit when (nvl(v_n,0) = 0);
v_data.extend;
v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
v_str := substr( v_str, v_n+1 );
end loop;
return v_data;
end;
字符列表函数
create or replace function str2varList( p_string in varchar2 ) return VarTableType
as
v_str long default p_string || ‘,‘;
v_n varchar2(2000);
v_data VarTableType := VarTableType();
begin
loop
v_n :=instr( v_str, ‘,‘ );
exit when (nvl(v_n,0) = 0);
v_data.extend;
v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
v_str := substr( v_str, v_n+1 );
end loop;
return v_data;
end;
创建之后，我们就可以采用如下的方式来使用in list的绑定了。
SELECT /*+ ordered use_nl(a,u) */ id, user_id
from table(STR2NUMLIST(:bind0)) a,
bmw_users u
where u.user_id = a.column_value;
###hint是为了固定执行计划，当走in-list记录过滤性强时，公司Oracle采用动态采样，可以不用hint
或：
SELECT /*+ leading(a) */ id, user_id
from bmw_users u where user_id in
(select * from table(STR2NUMLIST(:bind0)) a);
###in-list 数据较少时也可以用这个
with id_generator as ( SELECT regexp_substr(:txt, '[^,]+', 1, LEVEL) token FROM dual CONNECT BY LEVEL <= length(:txt) - length(REPLACE(:txt, ',', '')) + 1 )
select u.id, u.username from users u, id_generator g where u.id = g.token;
Mybatis中使用批量方法：
// 开启批量更新的sqlsession ---> 替换begin ;end;的写法，预编译sql，性能提升；
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
GreSaDtlMapper mapper = sqlSession.getMapper(GreSaDtlMapper.class);
lists.forEach(greSaDtlListSub -> {
if(greSaDtlListSub.size()>0){
greSaDtlListSub.forEach(data -> {
mapper.updateBlankCostPriceOne(data);
});
//先放弃用原先的一次性提交的写法
// greSaDtlMapper.updateBlankCostPrice(greSaDtlListSub);
}
});
sqlSession.commit();
sqlSession.clearCache();
SELECT *
FROM
(SELECT tmp_page.*, rownum row_id
FROM
(SELECT v.headid,
v.checkno,
v.checktype,
v.capdeptid,
v.capdeptname,
v.memo,
v.checkmanid,
v.checkmanname,
v.checkdeptid,
v.checkdeptname,
to_char(v.checkdate, 'yyyy-mm-dd') checkdate,
to_char(v.checkenddate, 'yyyy-mm-dd') checkenddate,
v.capuserid,
v.capusername,
v.usestatus,
nvl(v.authpersonnames, '') authpersonnames
FROM cap_goods_check_head_v v
WHERE nvl(v.usestatus, 0) not in (4, 6)
AND nvl(v.publishstatus, 0) = 1
AND (v.checkmanid = :1
OR v.inputmanid= :2
OR EXISTS
(SELECT 1
FROM cap_goods_check_auth_person cp
WHERE cp.headid = v.headid
AND cp.employeeid = :3 )
OR EXISTS
(SELECT 1
FROM cap_check_manager_doc md,
cap_check_manager_dtl mdtl
WHERE md.deptid = mdtl.deptid
AND nvl(mdtl.usestatus, 1) = 1
AND (mdtl.deptid = checkdeptid
OR mdtl.deptid = capdeptid)
AND mdtl.userid = :4 ))
ORDER BY v.headid DESC) tmp_page
WHERE rownum <= :5 )
WHERE row_id > :6
这2个SQL类似，优化第二条SQL，第一条类似优化。
这个SQL组要问题在or部分，
这个or部分过滤性可以，需要提前执行，但oracle在使用filer执行总是最后过滤，从而影响性能。
优化办法是把这个SQL改成2个sql：
先去出headid：
select HEADID from CAP_GOODS_CHECK_HEAD v2
where nvl(v2.usestatus, 0) not in (4,6)
AND nvl(v2.publishstatus, 0) = 1
and (v2.checkmanid = :b1
OR v2.inputmanid= :b2
OR EXISTS
(SELECT 1
FROM cap_goods_check_auth_person cp
WHERE cp.headid = v2.headid
AND cp.employeeid = :b3 )
OR EXISTS
(SELECT 1
FROM cap_check_manager_doc md,
cap_check_manager_dtl mdtl
WHERE md.deptid = mdtl.deptid
AND nvl(mdtl.usestatus, 1) = 1
AND (mdtl.deptid = v2.checkdeptid OR mdtl.deptid = v2.capdeptid)
AND mdtl.userid = :b4 ))
取到headid后再和cap_goods_check_head_v2进行查找：
SELECT *
FROM
(SELECT tmp_page.*, rownum row_id
FROM
(SELECT v.headid,
v.checkno,
v.checktype,
v.capdeptid,
v.capdeptname,
v.memo,
v.checkmanid,
v.checkmanname,
v.checkdeptid,
v.checkdeptname,
to_char(v.checkdate, 'yyyy-mm-dd') checkdate,
to_char(v.checkenddate, 'yyyy-mm-dd') checkenddate,
v.capuserid,
v.capusername,
v.usestatus,
nvl(v.authpersonnames, '') authpersonnames
FROM cap_goods_check_head_v2 v
WHERE v.HEADID in (上一个SQL查到的内容)
ORDER BY v.headid DESC) tmp_page
WHERE rownum <= 10 )
WHERE row_id > 0;
采用with 子句把第一个SQL分离处理出来，SQL示例(建议按2个SQL方案修改）：
with tt as
(select /*+ materialize CARDINALITY(v2 1)*/ HEADID from CAP_GOODS_CHECK_HEAD v2
where nvl(v2.usestatus, 0) not in (4,6)
AND nvl(v2.publishstatus, 0) = 1
and (v2.checkmanid = :b1
OR v2.inputmanid= :b2
OR EXISTS
(SELECT 1
FROM cap_goods_check_auth_person cp
WHERE cp.headid = v2.headid
AND cp.employeeid = :b3 )
OR EXISTS
(SELECT 1
FROM cap_check_manager_doc md,
cap_check_manager_dtl mdtl
WHERE md.deptid = mdtl.deptid
AND nvl(mdtl.usestatus, 1) = 1
AND (mdtl.deptid = v2.checkdeptid OR mdtl.deptid = v2.capdeptid)
AND mdtl.userid = :b4 )))
SELECT *
FROM
(SELECT tmp_page.*, rownum row_id
FROM
(SELECT /*+ first_rows */ v.headid,
v.checkno,
v.checktype,
v.capdeptid,
v.capdeptname,
v.memo,
v.checkmanid,
v.checkmanname,
v.checkdeptid,
v.checkdeptname,
to_char(v.checkdate, 'yyyy-mm-dd') checkdate,
to_char(v.checkenddate, 'yyyy-mm-dd') checkenddate,
v.capuserid,
v.capusername,
v.usestatus,
nvl(v.authpersonnames, '') authpersonnames
FROM cap_goods_check_head_v v,tt
WHERE v.HEADID=tt.headid
ORDER BY v.headid DESC) tmp_page
WHERE rownum <= 10 )
WHERE row_id > 0;
parallel主要用于大型统计类型的SQL，不适用于OLTP环境。
目前POS数据库主要是一个OLTP业务环境。有些报表业务，带有OLAP特性，但主要还是OLTP为主，为关键的系统。使用parallel需谨慎
1 目前POS库
parallel_max_servers=3840
parallel_min_servers=384
这些配置对于OLTP业务来时是过大了。oracle在使用并行查询的时候，并行进程会有大量启动、回收操作，logon、logoff操作，如果SQL执行时间很短，这些开销并不能忽略。
另对一个OLTP系统，突然启动数百个并发线程，容易对系统产生波动，甚至因分配内存从而堵塞住。
2 不合理使用parallelhint，不合理设置表、索引parallel
如下视图，加了这么多hint，会干扰优化器，从而采用不合理的执行计划。parallel hint应该按需，按具体SQL来，要有针对性。
3 由于parallel进程，有大量library cache lock，cursor mutex X等各种等待事件发生，POS数据库running高的时候，经常有parallel特性的查询发生。
4 分区特性主要是针对大数据量，主要针对OLAP环境。在OLTP环境，使用分区表后，SQL中不能做分区裁剪时，需要访问所有分区，从而影响性能。特别是对一些执行频繁的SQL，没有使用分区表，可能逻辑读是20个，但使用分区表后，逻辑读可能会使1000.
另使用分布表后有执行计划不够优化的情况。
如index(a,b)
select max(b） from 他where a=:1这种SQL，对非分区表，会采用range scan min/max功能；而在分区表中只能对所有分区采用range scan功能。
总之对OLTP系统表分区需谨慎。需要检查上面的SQL，看是否针对分区特性有优化。
原则上OLTP系统不建议使用分区特性，local索引，除非开发已考虑到分区特性，SQL考虑分区裁剪优化，或确实表太大了。但表太大的，可以考虑归档删除。
