Oracle- 优化SQL(二)
用戶反映在DISCOVER中的报表捞不出数据资料。
把SQL代码提取到ORACLE SQL Developer 环境运行并分析。 等待 1：52：53后出来数据。
分析执行计划，只有一个WIP.WIP_ENTITIES 表走FULL ACCESS , 其他表都是使用索引，开销在5000以内。
这是不可思异的效率。
我提出以下问题：
1、运行3年的报表（一直在20秒内完成），为什么会突然变慢呢？
2、DBA优化操作系统的IO配置影响？
3、DBA调整数据库的INDEX文件？做了 ANALYZE TABLE、REBUILD INDEX 等等相关操作？
4、IO资源不足？
5、临时表空间不足？
6、CPU资源被其他程序吃完，导致 SELECT的资源不足？
把问题抛给DBA去分析，第2~6问题 ,DBA回复都是不存在的。
然而DBA还是为这个报表 的SQL脚本所涉及的表做了一次ANALYZE TABLE操作，依然没有改善效率问题。
如： ANALYZE TABLE INV.MTL_SYSTEM_ITEMS_B COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES;
第二天：
因为有3年的运行良好的效果，任谁都不会认为SQL脚本有优化的必要。
我回到SQL脚本上来，看到三级子查询中有个无效的右连接“ AND bom.bill_sequence_id=bc.bill_sequence_id(+)”
将它修改为“ AND bom.bill_sequence_id=bc.bill_sequence_id --(+)” ，同时增加 组织约束范围”BOM.ORGANIZATION_ID BETWEEN 426 AND 427“。
发现此子查询的表别名也与第一层表别名重复，故也修改了此处的表别名，如
inv.mtl_system_items_b msi1 的
MSI1改为“MSI4" ，既改善可阅读性，又提升效率。
select bom.assembly_item_id,'Y' result
from inv.mtl_system_items_b msi4 ,
--apps.BOM_BILL_OF_MATERIALS bom,
BOM.BOM_STRUCTURES_B BOM,
BOM.BOM_COMPONENTS_B BC
--apps.BOM_INVENTORY_COMPONENTS bic
where BOM.ORGANIZATION_ID BETWEEN 426 AND 427
AND bom.bill_sequence_id=bc.bill_sequence_id --(+)
and bc.component_item_id=msi4.inventory_item_id
and bom.organization_id =msi4.organization_id
and msi4.inventory_item_id in ('145281','180670','148464','145286','151222','151220','151218','151216','145288','201642','202228','180680','371742','200951',
'151214','180682','305124','370364','148420','148307')
and bom.ALTERNATE_BOM_DESIGNATOR is null
通过 这么一翻修改，效率回复了20秒内。