Oracle数据库性能SQL优化案例(2)

SQL_ID= d4hw7rpzdvmsd select /*+ OPT_PARAM('_optimizer_mjc_enabled', 'false') */ instance_id from ci_base_relationship where markasdeleted <> 1 and (source_instance_id in (select a.destination_instance_id from ci_base_relationship a, ci_class_relation_tree b where a.class_id = b.class_id and b.is_auto_discover = 1 and a.source_instance_id in (select /*+ OPT_PARAM('_optimizer_mjc_enabled', 'false') */ instance_id from ci_base_element where reconciliation_id in (select reconciliation_id from ci_base_element where instance_id in (select instance_id from rn_identification_batch where source_dataset_id = 7)) and dataset_id = 6) and a.destination_instance_id not in (select /*+ OPT_PARAM('_optimizer_mjc_enabled', 'false') */ instance_id from ci_base_element where reconciliation_id in (select reconciliation_id from ci_base_element where instance_id in (select instance_id from rn_identification_batch where source_dataset_id = 7)) and dataset_id = 6)) or destination_instance_id in (select a.destination_instance_id from ci_base_relationship a, ci_class_relation_tree b where a.class_id = b.class_id and b.is_auto_discover = 1 and a.source_instance_id in (select /*+ OPT_PARAM('_optimizer_mjc_enabled', 'false') */ instance_id from ci_base_element where reconciliation_id in (select reconciliation_id from ci_base_element where instance_id in (select instance_id from rn_identification_batch SQL语句 where source_dataset_id = 7)) and dataset_id = 6) and a.destination_instance_id not in (select /*+ OPT_PARAM('_optimizer_mjc_enabled', 'false' ) */ instance_id from ci_base_element where reconciliation_id in (select reconciliation_id from ci_base_element where instance_id in (select instance_id from rn_identification_batch where source_dataset_id = 7)) and dataset_id = 6))) 功能模块 开发分析 JDBC Thin Client 等价于 select /*+ OPT_PARAM('_optimizer_mjc_enabled', 'false') */ instance_id from ci_base_relationship where markasdeleted <> 1 and source_instance_id in (select a.destination_instance_id from ci_base_relationship a, ci_class_relation_tree b where a.class_id = b.class_id and b.is_auto_discover = 1 and a.source_instance_id in (select /*+ OPT_PARAM('_optimizer_mjc_enabled', 'false') */ instance_id from ci_base_element where reconciliation_id in (select reconciliation_id from ci_base_element where instance_id in (select instance_id from rn_identification_batch where source_dataset_id = 7)) and dataset_id = 6) and a.destination_instance_id not in (select /*+ OPT_PARAM('_optimizer_mjc_enabled', 'false') */ instance_id from ci_base_element 现场分析 where reconciliation_id in (select reconciliation_id from ci_base_element where instance_id in (select instance_id from rn_identification_batch where source_dataset_id = 7)) and dataset_id = 6)) union select /*+ OPT_PARAM('_optimizer_mjc_enabled', 'false') */ instance_id from ci_base_relationship where markasdeleted <> 1 and destination_instance_id in (select a.destination_instance_id from ci_base_relationship a, ci_class_relation_tree b where a.class_id = b.class_id and b.is_auto_discover = 1 and a.source_instance_id in (select /*+ OPT_PARAM('_optimizer_mjc_enabled', 'false') */ instance_id from ci_base_element where reconciliation_id in (select reconciliation_id from ci_base_element where instance_id in (select instance_id from rn_identification_batch where source_dataset_id = 7)) and dataset_id = 6) and a.destination_instance_id not in (select /*+ OPT_PARAM('_optimizer_mjc_enabled', 'false' ) */ instance_id from ci_base_element where reconciliation_id in (select reconciliation_id from ci_base_element where instance_id in (select instance_id from rn_identification_batch where source_dataset_id = 7)) and dataset_id = 6)) 思路：由于语句太复杂加上全局临时表的影响，OR语句导致执行计划出现笛卡尔乘积等错误的方式，通过分析发现UNION 的改写基本能保持执行计划正常，建议暂时修正为UNION 的写法。 处理步骤 遗留问题 2. SQL_ID=gyx7jpkgv9mzr（调和）

紧急程度 SQL描述 紧急 该语句一周执行近8000次，平均每次执行1000多秒左右，产生200多亿的逻辑读，代码逻辑非常奇怪，需要开发人员确认。 其中NE_CTRL_MSG记录44836条，ci_base_element记录20万条左右。 SQL_ID= gyx7jpkgv9mzr select /*+ OPT_PARAM('_optimizer_mjc_enabled', 'false') */ ds1.instance_id1, ds2.instance_id2, nvl(ds1.reconciliation_id, ds2.reconciliation_id) reconciliation_id from (select ci_base_element.instance_id instance_id1, ci_base_element.reconciliation_id, ci_base_element.class_id, CI_BASE_ELEMENT.Name ca1 from rn_identification_batch, ci_base_element where rn_identification_batch.instance_id = ci_base_element.instance_id and ci_base_element.class_id in (3, 1, 2, 4, 10…) and rn_identification_batch.source_dataset_id = :ds) ds1, (select ci_base_element.instance_id instance_id2, ci_base_element.reconciliation_id, ci_base_element.class_id, CI_BASE_ELEMENT.Name cb1 from rn_identification_batch, ci_base_element where rn_identification_batch.instance_id = ci_base_element.instance_id and ci_base_element.class_id in (3, 1, 2, 4, 10…) and rn_identification_batch.source_dataset_id = :ds) ds2 where ds1.ca1 = ds2.cb1 and ds1.class_id = ds2.class_id SQL语句

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库Oracle数据库性能SQL优化案例(2)在线全文阅读。