oracle和mysql数量级_oracle 11g亿级复杂SQL优化一例（数量级性能提升）

自从16年之后，因为工作原因，项目中就没有再使用oracle了，最近最近支持一个项目，又要开始负责这块事情了。最近在跑性能测试，配置全部调好之后，不少sql还存在性能低下的问题，主要涉及执行计划的不合理，以及相关pga隐含参数的优化。可能因为几年不接触的原因，略微有些生疏需要review了。这里以最近优化过的某个比较典型的例子为例(这里只讲思路、因为涉及到敏感信息，不给出最终结果，16C E5620@2.40GHz/45GB内存/fio 85/15 iops 8500/1500 配置，优化前100G临时空间都爆掉，到20分钟出结果，实际上允许完全调整的话，最多5分钟就可以运行出来)，这个例子比较经典，它是一个产品转换的例子，从业务逻辑上来看，它需要对两个100w(100管理人*1w支产品)记录的结果集进行黑名单(是指A产品->B产品是否允许转换，A->B不允许，不意味着B->A不允许)以外的笛卡尔积关联，，重点回顾下一些重要的operation以及如何通过hint强制，子查询独立运行时的operation和作为主查询的子查询运行时的operation不相同可能会造成性能差异巨大。首先，不要指望写一段sql，既可以在oracle下运行、也可以在mysql下运行。原始的sql语句以及执行计划如下：

INSERT INTOC3(

tenantid,c_code,l_serialno,c_txtfundcode,

c_chargetype,f_sharemin,f_sharemax,l_hold,c_txtothercode,

c_targetchargetype,c_custtype,c_flag,d_operatedate,d_cdate,c_cyno)SELECTa.tenantid,a.c_code,l_serialno,CASE a.agencytypes WHEN 'AB' THEN a.c_prdcode ELSE COALESCE(b.c_outprdcode,a.c_prdcode) ENDc_txtfundcode,

c_chargetype,f_sharemin,f_sharemax,l_hold,CASE a.agencytypes WHEN 'AB'THEN a.c_othercode ELSE COALESCE(c.c_outprdcode,a.c_othercode) ENDc_txtothercode,

c_targetchargetype,c_custtype,c_flag,d_operatedate,20100511d_cdate,a.c_cynoFROM (SELECT 0l_serialno,CASE t.c_mutextype WHEN 'A' THEN '0' WHEN 'B' THEN '1' WHEN 'C' THEN '2' ELSE t.c_mutextype ENDc_chargetype,CASE t.c_targetsharetype WHEN 'A' THEN '0' WHEN 'B' THEN '1' WHEN 'C' THEN '2' ELSE t.c_targetsharetype ENDc_targetchargetype,

ar.f_cminmutex f_sharemin,99999999999999.9 f_sharemax,0 l_hold,'2'c_custtype,'1' c_flag,'20000101'd_operatedate,

t.*

FROM (SELECTfo.tenantid, fo.c_code, fo.c_prdcode,fi.c_prdcode c_othercode,

fo.c_cyno,fo.c_mutextype,fi.c_mutextype c_targetsharetype,

fo.d_contractdate d_contractdate, fo.c_mutextypes c_mutextypes,

fo.agencytypes,

fi.d_contractdate d_othercontractdate, fi.c_mutextypes c_othersharetypesFROM (SELECT DISTINCTss.tenantid, ss.c_code, ss.c_prdcode,

ss.c_cyno,ss.c_mutextype c_mutextype,

ta.c_mutextypes agencytypes,

getsysvalue(ss.tenantid, ss.c_code, ss.c_cyno,ss.c_managercode, ss.c_prdcode,'ContractDate', '20991231') d_contractdate,

getsysvalue(ss.tenantid, ss.c_code, ss.c_cyno,ss.c_managercode, ss.c_prdcode,'ShareTypes', '') c_mutextypesFROM (SELECTfi.tenantid, fi.c_code, fi.c_prdcode,c.c_cyno,

s.c_mutextype, fi.c_managercodeFROMFUNDINFO fi,

(SELECT 'A' c_mutextype FROMDUALUNION ALL

SELECT 'B' c_mutextype FROMDUALUNION ALL

SELECT 'C' c_mutextype FROMDUAL) s,

QUALIFY c,

EXPBATCH dWHERE fi.c_code = 'F6'

AND fi.tenantid = '*'

AND fi.c_fundstatus NOT IN ('6', '9')AND c.c_code = 'F6'

AND c.tenantid = '*'

AND c.c_cyno =d.c_cynoAND c.c_code =d.c_codeAND c.tenantid =d.tenantidAND INSTR(fi.c_mutextypes, s.c_mutextype) > 0

AND (fi.c_fundtype<>'1'

OR

NOT EXISTS(SELECT 1 FROMTEXT_PARAMETERWHERE c_paramitem = 'ChangeLimit'

AND c_paramvalue = '1'

AND c_prdcode =fi.c_pr