OPTIMIZER_VERSION参数调整为较低值，可能导致SQL性能问题
程序员文章站
2022-06-02 22:06:14
...
环境说明
数据库版本：DM7、DM8
现状描述
设置OPTIMIZER_VERSION 参数为一个较低的值（比默认值低，默认值一般为当前版本最新（最大）的值），导致SQL执行性能出现很大差异，严重影响SQL性能。
此参数默认没有在dm.ini中，在数据库中存在该参数。
测试过程如下
1.查询当前数据库 OPTIMIZER_VERSION 参数的默认值:
SQL> select * from v$version;
行号 BANNER
---------- --------------------------------------------------------------
1 DM Database Server x64 V7.6.1.58-Build(2020.05.08-121251)ENT
2 DB Version: 0x7000a
已用时间: 0.891(毫秒). 执行号:6.
SQL> select * from v$dm_ini where para_name='OPTIMIZER_VERSION';
行号 PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE
---------- ----------------- ---------- --------- --------- ------- ---------- ---------- ----------------- ---------
1 OPTIMIZER_VERSION 70097 70000 70097 N 70097 70097 optimizer version SESSION
已用时间: 4.686(毫秒). 执行号:7.
默认为最大值；
- 默认值的环境中，执行相关SQL
SQL> select a.* ,
case when b.memo is null
then a.frequencyname
else b.memo end as frequencymemo,
c.drugtypeid ,
d.cfattributes ,
( select dictname
from xx
where organid=d.organid
and typeid ='xx'
and dictid =d.cfattributes
and rownum =1
)
cfattributesname ,
nvl(c.skintestdrug, 0) as skintestdrug,
d.cardid ,
d.dbrname ,
d.dbrcardid ,
t.locationcode ,
d.isdjzy ,
( select /*aa*/ to_char(wm_concat(e.batchno))
from e
where e.organid =a.organid
and e.rcptno =a.rcptno
and a.yfdeptid =e.performedbydept
and a.seqid =e.forder
and a.projectcode=e.drugcode
) as batchno ,
c.highrisktype ,
e.dictname as essentialdrugsclass,
d1.skinresult ,
d1.skinnotes
from a
left join b
on a.organid =b.organid
and a.frequency=b.timesid
left join c
on a.organid =c.organid
and a.projectcode=c.drugcode
inner join d
on a.organid =d.organid
and a.serialno=d.serialno
left join d1
on d.organid =d1.organid
and d.serialno =d1.serialno
and d.ghbillid =d1.ghbillid
and a.projectcode =d1.drugcode
and a.cforderno =d1.orderno
and a.cfsuborderno=d1.suborderno
inner join t
on a.organid =t.organid
and a.yfdeptid =t.deptid
and a.projectcode=t.drugcode
left join e
on e.organid=a.organid
and e.typeid ='xx'
and e.dictid = c.essentialdrugsclass
where a.organid ='xx'
and a.yfdeptid= 'xx'
and a.rcptno ='xxx'
and ( 1= case when a.fystate<2 then 1 else case when 0 =0 then 0 else 1 end end)
order by
a.organid ,
a.yfdeptid,
a.rcptno ,
a.serialno,
a.orderno ,
a.suborderno ;
未选定行
已用时间: 26.035(毫秒). 执行号:3.
-
修改OPTIMIZER_VERSION 参数值为一个较低的值（本例中测试设置的值为70043）
修改该参数有2中方法：
第一种：该参数可以在线修改立即生效。修改方式：sp_set_para_value(1,‘OPTIMIZER_VERSION’,70043);
(如果参数没有在dm.ini里面，通过sp_set_para_value(1,’’,’’)这样修改，只会修改当前内存的值，重启会恢复默认的值。)
第二种：在参数文件中添加该参数并修改，然后重启；
SQL> select * from v$dm_ini where para_name='OPTIMIZER_VERSION';
行号 PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE
---------- ----------------- ---------- --------- --------- ------- ---------- ---------- ----------------- ---------
1 OPTIMIZER_VERSION 70043 70000 70097 N 70043 70043 optimizer version SESSION
SQL> select a.* ,
case when b.memo is null
then a.frequencyname
else b.memo end as frequencymemo,
c.drugtypeid ,
d.cfattributes ,
( select dictname
from xx
where organid=d.organid
and typeid ='xx'
and dictid =d.cfattributes
and rownum =1
)
cfattributesname ,
nvl(c.skintestdrug, 0) as skintestdrug,
d.cardid ,
d.dbrname ,
d.dbrcardid ,
t.locationcode ,
d.isdjzy ,
( select /*aa*/ to_char(wm_concat(e.batchno))
from e
where e.organid =a.organid
and e.rcptno =a.rcptno
and a.yfdeptid =e.performedbydept
and a.seqid =e.forder
and a.projectcode=e.drugcode
) as batchno ,
c.highrisktype ,
e.dictname as essentialdrugsclass,
d1.skinresult ,
d1.skinnotes
from a
left join b
on a.organid =b.organid
and a.frequency=b.timesid
left join c
on a.organid =c.organid
and a.projectcode=c.drugcode
inner join d
on a.organid =d.organid
and a.serialno=d.serialno
left join d1
on d.organid =d1.organid
and d.serialno =d1.serialno
and d.ghbillid =d1.ghbillid
and a.projectcode =d1.drugcode
and a.cforderno =d1.orderno
and a.cfsuborderno=d1.suborderno
inner join t
on a.organid =t.organid
and a.yfdeptid =t.deptid
and a.projectcode=t.drugcode
left join e
on e.organid=a.organid
and e.typeid ='xx'
and e.dictid = c.essentialdrugsclass
where a.organid ='xx'
and a.yfdeptid= 'xx'
and a.rcptno ='xxx'
and ( 1= case when a.fystate<2 then 1 else case when 0 =0 then 0 else 1 end end)
order by
a.organid ,
a.yfdeptid,
a.rcptno ,
a.serialno,
a.orderno ,
a.suborderno ;
未选定行
已用时间: 00:00:13.432. 执行号:7.
结论
通过上面两次测试可以发现，当使用默认的OPTIMIZER_VERSION 参数值时（也是当前版本最高的值），性能要比较低值高出非常多。
建议
首先，建议不要在生产环境中随意调整 OPTIMIZER_VERSION 参数，也建议不要在生产环境中随意更改其他参数，需在原厂工程师指导下或者咨询后再进行更改，更改后需要严格测试影响。
另外，可能会有某些BUG可以通过修改某些参数进行临时绕过，但建议尽快升级到修复后的高版本。