Oracle- 优化SQL(二)
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秒内。