前一阵子优化了个SQL，原代码如下：

 前一阵子，优化了一个SQL,原代码如下：
create or replace package body CUX_INV_DEAD_STOCK_DETAIL_PKG is
/* ================================================================================*   PROGRAM NAME:*                CUX_INV_DEAD_STOCK_DETAIL_PKG**   PROGRAM STRUCTURE:** ==============================================================================*/g_error varchar2(500);g_debug varchar2(500);procedure outlog(g_message in varchar2) isbeginfnd_file.PUT_LINE(fnd_file.LOG,g_message);end outlog; --输出日志procedure output(g_message in varchar2) isbeginfnd_file.PUT_LINE(fnd_file.OUTPUT,g_message);dbms_output.put_line(g_message);end output; --输出/* =================================================================================*   FUNCTION / PROCEDURE*   NAME : INV_DEAD_STOCK_DETAIL_MAIN*   DESCRIPTION: CUX:呆滞物料统计表** ==================================================================================*/procedure inv_dead_stock_detail_main(o_errcode             out varchar2,o_errmess             out varchar2,p_org_id              in number,    --业务实体p_item_category       in varchar2,  --物料类型p_inventory_item_f    in varchar2,  --物料从p_inventory_item_t    in varchar2,  --物料至p_dead_days           in number     --呆滞天数) isv_print_date         varchar2(30);  --打印日期v_ou                 varchar2(30);  --业务实体v_item_category      varchar2(100); --物料类型v_inventory_item_f   varchar2(100); --物料从v_inventory_item_t   varchar2(100); --物料至v_dead_days          varchar2(10);  --呆滞天数cursor c1 isselect b.organization_id,b.item_category,b.item_sub_category,b.segment1,b.description,b.primary_unit_of_measure,b.item_cost,b.onhand_quantity,b.onhand_amount,b.last_transaction_date,b.dead_stock_daysfrom (select a.organization_id,a.item_category,a.item_sub_category,a.segment1,a.description,a.primary_unit_of_measure,a.item_cost,a.onhand_quantity,a.onhand_amount,max(mmt.transaction_date) last_transaction_date,trunc(sysdate - max(mmt.transaction_date)) dead_stock_daysfrom mtl_material_transactions mmt,(select msib.organization_id,msib.inventory_item_id,mcb.segment1  item_category,     --物料大类mcb.segment2  item_sub_category,  --物料小类msib.segment1,msib.description,--onhand.transaction_uom_code,msib.primary_unit_of_measure,cic.item_cost,sum(onhand.transaction_quantity) onhand_quantity,(cic.item_cost * sum(onhand.transaction_quantity)) onhand_amountfrom mtl_system_items_b           msib,cst_item_costs               cic,mtl_categories_b             mcb,mtl_item_categories          mic,mtl_onhand_quantities_detail onhandwhere 1 = 1and msib.inventory_item_id = onhand.inventory_item_idand msib.organization_id = onhand.organization_idand onhand.inventory_item_id = cic.inventory_item_idand onhand.organization_id = cic.organization_idand mcb.category_id = mic.category_idand mic.inventory_item_id=msib.inventory_item_idand mic.organization_id=msib.organization_idand mcb.structure_id = 101and mic.category_set_id = 1and cic.cost_type_id = 3and onhand.organization_id = p_org_id--and msib.inventory_item_id=3073and mcb.segment2=nvl(p_item_category,mcb.segment2)and substr(onhand.subinventory_code,1,1)not in ('E','B')and msib.segment1 between nvl(p_inventory_item_f,msib.segment1) and nvl(p_inventory_item_t,msib.segment1)group by msib.organization_id,msib.inventory_item_id,mcb.segment1,mcb.segment2,msib.segment1,msib.description,msib.primary_unit_of_measure,cic.item_cost) awhere 1 = 1and a.organization_id = mmt.organization_idand a.inventory_item_id = mmt.inventory_item_idand mmt.transaction_type_id <>24group by a.organization_id,a.item_category,a.item_sub_category,a.segment1,a.description,a.primary_unit_of_measure,a.item_cost,a.onhand_quantity,a.onhand_amount) bwhere b.dead_stock_days > nvl(p_dead_days,0);begin--处理报表头信息g_debug        := '初始化公司LOGO和输入条件';--获取打印日期select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')into v_print_datefrom dual;--获取业务实体select trim(hou.name)into v_oufrom hr_operating_units houwhere hou.organization_id = p_org_id;--获取物料类型v_item_category:=trim(p_item_category);--获取物料从v_inventory_item_f:=trim(p_inventory_item_f);--获取物料至v_inventory_item_t:=trim(p_inventory_item_t);--获取物料呆滞天数v_dead_days:=to_char(p_dead_days);--开始输出报表头信息g_debug          :='输出打印日期和用户输入条件';output('

');output('' || v_print_date || '');output('' || v_ou || '');output('' || v_item_category || '');output('' || v_inventory_item_f || '');output('' || v_inventory_item_t || '');output('' || v_dead_days || '');--主体数据内循环开始g_debug         :='主体数据内循环开始';for c1r in c1 loopoutput('');output('' || c1r.item_category ||'.'|| c1r.item_sub_category || '');output('' || c1r.segment1 || '');output('' || cux_common_pkg.Xml_Format(c1r.description) || '');output('' || c1r.primary_unit_of_measure || '');output('' || c1r.Onhand_Quantity || '');output('' || to_char(c1r.last_transaction_date,'YYYY-MM-DD HH24:MI:SS') || '');output('' || c1r.Dead_Stock_Days || '');output('' || c1r.Item_Cost || '');output('' || c1r.onhand_amount || '');output('');end loop;output('');output('');exceptionwhen others theng_error := SQLERRM;INSERT INTO session_log --记录异常信息到异常表VALUES(SYSDATE, 'INVENTORY', 'CUX_INV_DEAD_STOCK_DETAIL_PKG', g_debug, g_error);COMMIT;outlog('出现错误');end inv_dead_stock_detail_main;
end CUX_INV_DEAD_STOCK_DETAIL_PKG;

这段代码运行的环境是oracle EBS R12中的一个查询呆滞物料的报表，运行的环境为IBM小机P系列（印象中应该P750）+v7000存储，这个报表跑出来的时间是最长达35分钟，在另一个低点的测试环境中用了将近七小时（具体配置就不提了），用10046 trace 出来的结果为（只取其中的主要的一段）：

SELECT B.ORGANIZATION_ID, B.ITEM_CATEGORY, B.ITEM_SUB_CATEGORY, B.SEGMENT1, B.DESCRIPTION, B.PRIMARY_UNIT_OF_MEASURE, B.ITEM_COST, B.ONHAND_QUANTITY, B.ONHAND_AMOUNT, B.LAST_TRANSACTION_DATE, B.DEAD_STOCK_DAYS 
FROM(SELECT A.ORGANIZATION_ID, A.ITEM_CATEGORY, A.ITEM_SUB_CATEGORY, A.SEGMENT1, A.DESCRIPTION, A.PRIMARY_UNIT_OF_MEASURE, A.ITEM_COST, A.ONHAND_QUANTITY, A.ONHAND_AMOUNT, MAX(MMT.TRANSACTION_DATE) LAST_TRANSACTION_DATE, TRUNC(SYSDATE - MAX(MMT.TRANSACTION_DATE)) DEAD_STOCK_DAYS FROM MTL_MATERIAL_TRANSACTIONS MMT, (SELECT MSIB.ORGANIZATION_ID, MSIB.INVENTORY_ITEM_ID, MCB.SEGMENT1 ITEM_CATEGORY, MCB.SEGMENT2 ITEM_SUB_CATEGORY, MSIB.SEGMENT1, MSIB.DESCRIPTION, MSIB.PRIMARY_UNIT_OF_MEASURE, CIC.ITEM_COST, SUM(ONHAND.TRANSACTION_QUANTITY) ONHAND_QUANTITY, (CIC.ITEM_COST * SUM(ONHAND.TRANSACTION_QUANTITY)) ONHAND_AMOUNT FROM MTL_SYSTEM_ITEMS_B MSIB, CST_ITEM_COSTS CIC, MTL_CATEGORIES_B MCB, MTL_ITEM_CATEGORIES MIC, MTL_ONHAND_QUANTITIES_DETAIL ONHAND WHERE 1 = 1 AND MSIB.INVENTORY_ITEM_ID =ONHAND.INVENTORY_ITEM_ID AND MSIB.ORGANIZATION_ID = ONHAND.ORGANIZATION_ID AND ONHAND.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID AND ONHAND.ORGANIZATION_ID = CIC.ORGANIZATION_ID AND MCB.CATEGORY_ID = MIC.CATEGORY_ID AND MIC.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID AND MIC.ORGANIZATION_ID=MSIB.ORGANIZATION_ID AND MCB.STRUCTURE_ID = 101 AND MIC.CATEGORY_SET_ID = 1 AND CIC.COST_TYPE_ID = 3 AND ONHAND.ORGANIZATION_ID = :B4 AND MCB.SEGMENT2=NVL(:B3 ,MCB.SEGMENT2) AND SUBSTR(ONHAND.SUBINVENTORY_CODE,1,1)NOT IN ('E','B') AND MSIB.SEGMENT1 BETWEEN NVL(:B2 ,MSIB.SEGMENT1) AND NVL(:B1 ,MSIB.SEGMENT1) GROUP BY MSIB.ORGANIZATION_ID, MSIB.INVENTORY_ITEM_ID, MCB.SEGMENT1, MCB.SEGMENT2, MSIB.SEGMENT1, MSIB.DESCRIPTION, MSIB.PRIMARY_UNIT_OF_MEASURE, CIC.ITEM_COST) A WHERE 1 = 1 AND A.ORGANIZATION_ID = MMT.ORGANIZATION_ID AND A.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID AND MMT.TRANSACTION_TYPE_ID <>24 GROUP BY A.ORGANIZATION_ID, A.ITEM_CATEGORY, A.ITEM_SUB_CATEGORY, A.SEGMENT1, A.DESCRIPTION, A.PRIMARY_UNIT_OF_MEASURE, A.ITEM_COST, A.ONHAND_QUANTITY, A.ONHAND_AMOUNT) B WHERE B.DEAD_STOCK_DAYS > NVL(:B5 ,0)call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1    259.38    7987.81    1336813   28643548          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    259.38    7987.81    1336813   28643548          0           0Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)Rows     Row Source Operation
-------  ---------------------------------------------------0  FILTER  (cr=0 pr=0 pw=0 time=0 us)0   HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=408 size=138 card=1)
2382290    NESTED LOOPS  (cr=28643548 pr=1336813 pw=0 time=7771837034 us)
2384353     NESTED LOOPS  (cr=26956486 pr=38996 pw=0 time=133943370 us cost=407 size=4968 card=36)4323      VIEW  (cr=26927194 pr=19148 pw=0 time=51978 us cost=377 size=118 card=1)4323       HASH GROUP BY (cr=26927194 pr=19148 pw=0 time=32507 us)39226        CONCATENATION  (cr=26927194 pr=19148 pw=0 time=200800799 us)39226         FILTER  (cr=26927194 pr=19148 pw=0 time=200782622 us)39226          NESTED LOOPS  (cr=26927194 pr=19148 pw=0 time=200762465 us)39226           NESTED LOOPS  (cr=26874154 pr=13992 pw=0 time=169916397 us cost=261 size=152 card=1)39249            NESTED LOOPS  (cr=26795777 pr=9888 pw=0 time=150089655 us cost=259 size=134 card=1)
4592133             NESTED LOOPS  (cr=17625783 pr=6874 pw=0 time=110462727 us cost=247 size=1368 card=12)
4592133              MERGE JOIN CARTESIAN (cr=10739 pr=402 pw=0 time=6150125 us cost=223 size=612 card=12)117               TABLE ACCESS BY INDEX ROWID MTL_CATEGORIES_B (cr=11 pr=10 pw=0 time=17429 us cost=4 size=34 card=1)117                INDEX RANGE SCAN MTL__CATEGORIES_B_N2 (cr=1 pr=1 pw=0 time=560 us cost=1 size=0 card=29)(object id 118829)
4592133               BUFFER SORT (cr=10728 pr=392 pw=0 time=4082113 us cost=219 size=884 card=52)39249                TABLE ACCESS BY INDEX ROWID MTL_ONHAND_QUANTITIES_DETAIL (cr=10728 pr=392 pw=0 time=709319 us cost=219 size=884 card=52)39249                 INDEX RANGE SCAN MTL_ONHAND_QUANTITIES_N5 (cr=385 pr=385 pw=0 time=575783 us cost=204 size=0 card=52)(object id 120237)
4592133              TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cr=17615044 pr=6472 pw=0 time=0 us cost=2 size=63 card=1)
4592133               INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (cr=9171958 pr=1125 pw=0 time=0 us cost=1 size=0 card=1)(object id 120986)39249             INDEX UNIQUE SCAN MTL_ITEM_CATEGORIES_U1 (cr=9169994 pr=3014 pw=0 time=0 us cost=1 size=20 card=1)(object id 119585)39226            INDEX UNIQUE SCAN CST_ITEM_COSTS_U1 (cr=78377 pr=4104 pw=0 time=0 us cost=1 size=0 card=1)(object id 208817)39226           TABLE ACCESS BY INDEX ROWID CST_ITEM_COSTS (cr=53040 pr=5156 pw=0 time=0 us cost=2 size=18 card=1)0         FILTER  (cr=0 pr=0 pw=0 time=0 us)0          NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)0           NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=115 size=152 card=1)0            NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=113 size=134 card=1)0             NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=110 size=117 card=1)0              MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=0 us cost=46 size=6208 card=64)0               TABLE ACCESS BY INDEX ROWID MTL_CATEGORIES_B (cr=0 pr=0 pw=0 time=0 us cost=4 size=34 card=1)0                INDEX RANGE SCAN MTL__CATEGORIES_B_N2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=29)(object id 118829)0               BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=42 size=17262 card=274)0                TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cr=0 pr=0 pw=0 time=0 us cost=42 size=17262 card=274)0                 INDEX RANGE SCAN MTL_SYSTEM_ITEMS_B_U2 (cr=0 pr=0 pw=0 time=0 us cost=7 size=0 card=49)(object id 408422)0              INDEX UNIQUE SCAN MTL_ITEM_CATEGORIES_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 119585)0             TABLE ACCESS BY INDEX ROWID MTL_ONHAND_QUANTITIES_DETAIL (cr=0 pr=0 pw=0 time=0 us cost=3 size=17 card=1)0              INDEX RANGE SCAN MTL_ONHAND_QUANTITIES_N4 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 120239)0            INDEX UNIQUE SCAN CST_ITEM_COSTS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 208817)0           TABLE ACCESS BY INDEX ROWID CST_ITEM_COSTS (cr=0 pr=0 pw=0 time=0 us cost=2 size=18 card=1)
2384353      INDEX RANGE SCAN MTL_MATERIAL_TRANSACTIONS_N1 (cr=29292 pr=19848 pw=0 time=97162036 us cost=3 size=0 card=41)(object id 119944)
2382290     TABLE ACCESS BY INDEX ROWID MTL_MATERIAL_TRANSACTIONS (cr=1687062 pr=1297817 pw=0 time=0 us cost=31 size=720 card=36)Elapsed times include waiting on following events:Event waited on                             Times   Max. Wait  Total Waited----------------------------------------   Waited  ----------  ------------db file sequential read                   1336813        0.64       7789.24latch free                                      1        0.00          0.00

从上面的执行计划中可以看到，大量的nested loops中逻辑读与物理读很大，而在外层嵌套中表

MTL_MATERIAL_TRANSACTIONS的数据量大，消耗的时间7771，837034us 即将近七小时，改写方案如下：
1.先创建临时表，用来存放内层结果：
2.将传进来的参数作判断处理，
3.将最外层表与内层结果用hash 返回结果;结果如下：
CREATE GLOBAL TEMPORARY TABLE CUX.DEAD_MT
(ORGANIZATION_ID               NUMBER,  --MSIB_ORGANIZATION_IDINVENTORY_ITEM_ID             NUMBER,  --MSIB_INVENTORY_ITEM_IDitem_category                 VARCHAR2(40 BYTE),  -- MCB_SEGMENT1 item_category,     --物料大类item_sub_category             VARCHAR2(40 BYTE),  -- MCB_SEGMENT2 item_sub_category,  --物料小类SEGMENT1                      VARCHAR2(40 BYTE),  --MSIB_SEGMENT1DESCRIPTION                   VARCHAR2(240 BYTE), --MSIB_DESCRIPTIONPRIMARY_UNIT_OF_MEASURE  VARCHAR2(25 BYTE),  --MSIB_PRIMARY_UNIT_OF_MEASUREITEM_COST                     NUMBER,      --CIC_ITEM_COSTONHAND_QUANTITY               NUMBER,   --sum(onhand.transaction_quantity) onhand_quantity,ONHAND_AMOUNT                 NUMBER  --(cic.item_cost * sum(onhand.transaction_quantity))
)
ON COMMIT DELETE ROWS;create or replace package body CUX_INV_DEAD_STOCK_DETAIL_PKG is
/* ================================================================================*   PROGRAM NAME:*                CUX_INV_DEAD_STOCK_DETAIL_PKG** ==============================================================================*/g_error varchar2(500);g_debug varchar2(500);procedure outlog(g_message in varchar2) isbeginfnd_file.PUT_LINE(fnd_file.LOG,g_message);end outlog; --输出日志procedure output(g_message in varchar2) isbeginfnd_file.PUT_LINE(fnd_file.OUTPUT,g_message);dbms_output.put_line(g_message);end output; --输出/* =================================================================================*   FUNCTION / PROCEDURE*   NAME : INV_DEAD_STOCK_DETAIL_MAIN** ==================================================================================*/procedure inv_dead_stock_detail_main(o_errcode             out varchar2,o_errmess             out varchar2,p_org_id              in number,    --业务实体p_item_category       in varchar2,  --物料类型p_inventory_item_f    in varchar2,  --物料从p_inventory_item_t    in varchar2,  --物料至p_dead_days           in number     --呆滞天数) isv_print_date         varchar2(30);  --打印日期v_ou                 varchar2(30);  --业务实体v_item_category      varchar2(100); --物料类型v_inventory_item_f   varchar2(100); --物料从v_inventory_item_t   varchar2(100); --物料至v_dead_days          varchar2(10);  --呆滞天数cursor c1 is  select    b.organization_id,b.item_category,b.item_sub_category,b.segment1,b.description,b.primary_unit_of_measure,b.item_cost,b.onhand_quantity,b.onhand_amount,b.last_transaction_date,b.dead_stock_days
from(            
select              /*+use_hash(mmt ,a)*/a.ORGANIZATION_ID,a.item_category,-- a.INVENTORY_ITEM_ID,a.item_sub_category,a.segment1,a.description,a.primary_unit_of_measure,a.item_cost,a.onhand_quantity,a.onhand_amount,max(mmt.transaction_date) last_transaction_date,trunc(TO_DATE('2015-3-30 11:36:00','YYYY-MM-DD HH24:MI:SS') - max(mmt.transaction_date)) dead_stock_days--trunc(sysdate - max(mmt.transaction_date)) dead_stock_daysfrom mtl_material_transactions mmt, DEAD_MT awhere 1 = 1and a.organization_id = mmt.organization_idand a.inventory_item_id = mmt.inventory_item_idand mmt.transaction_type_id <>24and a.item_sub_category=nvl(p_item_category,a.item_sub_category)--and a.segment1 between nvl(null,a.segment1) and nvl(null,a.segment1)group by a.organization_id,a.item_category,a.item_sub_category,a.segment1,a.description,a.primary_unit_of_measure,a.item_cost,a.onhand_quantity,a.onhand_amount) b    
where     b.dead_stock_days > nvl(p_dead_days,0);                            beginif (p_inventory_item_f is null and p_inventory_item_t is null)theninsert /* +append*/into  DEAD_MT
select msib.organization_id,msib.inventory_item_id,mcb.segment1  item_category,     --物料大类mcb.segment2  item_sub_category,  --物料小类msib.segment1,msib.description,--onhand.transaction_uom_code,msib.primary_unit_of_measure,cic.item_cost,sum(onhand.transaction_quantity) onhand_quantity,(cic.item_cost * sum(onhand.transaction_quantity)) onhand_amountfrom mtl_system_items_b           msib,  cst_item_costs               cic,  mtl_categories_b             mcb,  mtl_item_categories          mic,  mtl_onhand_quantities_detail onhand   where 1=1    and msib.inventory_item_id = onhand.inventory_item_idand msib.organization_id = onhand.organization_idand onhand.inventory_item_id = cic.inventory_item_idand onhand.organization_id = cic.organization_idand mcb.category_id = mic.category_idand mic.inventory_item_id=msib.inventory_item_idand mic.organization_id=msib.organization_idand mcb.structure_id = 101and mic.category_set_id = 1and cic.cost_type_id = 3and onhand.organization_id = p_org_id--and msib.inventory_item_id=3073-- and mcb.segment2=nvl(null,mcb.segment2)  and mcb.segment2=nvl(p_item_category,mcb.segment2)and substr(onhand.subinventory_code,1,1)not in ('E','B')--  and msib.segment1 between nvl(null,msib.segment1) and nvl(null,msib.segment1)--   and msib.segment1 between nvl(p_inventory_item_f,msib.segment1) and nvl(p_inventory_item_t,msib.segment1)group by msib.organization_id,msib.inventory_item_id,mcb.segment1,mcb.segment2,msib.segment1,msib.description,msib.primary_unit_of_measure,cic.item_cost;
end if;                               if   ( p_inventory_item_f is not null and p_inventory_item_t is not null)theninsert into  DEAD_MT
select msib.organization_id,msib.inventory_item_id,mcb.segment1  item_category,     --物料大类mcb.segment2  item_sub_category,  --物料小类msib.segment1,msib.description,--onhand.transaction_uom_code,msib.primary_unit_of_measure,cic.item_cost,sum(onhand.transaction_quantity) onhand_quantity,(cic.item_cost * sum(onhand.transaction_quantity)) onhand_amountfrom mtl_system_items_b           msib,  cst_item_costs               cic,  mtl_categories_b             mcb,  mtl_item_categories          mic,  mtl_onhand_quantities_detail onhand   where 1=1    and msib.inventory_item_id = onhand.inventory_item_idand msib.organization_id = onhand.organization_idand onhand.inventory_item_id = cic.inventory_item_idand onhand.organization_id = cic.organization_idand mcb.category_id = mic.category_idand mic.inventory_item_id=msib.inventory_item_idand mic.organization_id=msib.organization_idand mcb.structure_id = 101and mic.category_set_id = 1and cic.cost_type_id = 3and onhand.organization_id = p_org_id--and msib.inventory_item_id=3073--and mcb.segment2=nvl(null,mcb.segment2) --and mcb.segment2=p_item_categoryand substr(onhand.subinventory_code,1,1)not in ('E','B')--  and msib.segment1 between nvl(null,msib.segment1) and nvl(null,msib.segment1)and msib.segment1 between p_inventory_item_f and p_inventory_item_tgroup by msib.organization_id,msib.inventory_item_id,mcb.segment1,mcb.segment2,msib.segment1,msib.description,msib.primary_unit_of_measure,cic.item_cost;end if;if ( p_inventory_item_f is not null and p_inventory_item_t is null) theninsert into  DEAD_MT
select msib.organization_id,msib.inventory_item_id,mcb.segment1  item_category,     --物料大类mcb.segment2  item_sub_category,  --物料小类msib.segment1,msib.description,--onhand.transaction_uom_code,msib.primary_unit_of_measure,cic.item_cost,sum(onhand.transaction_quantity) onhand_quantity,(cic.item_cost * sum(onhand.transaction_quantity)) onhand_amountfrom mtl_system_items_b           msib,  cst_item_costs               cic,  mtl_categories_b             mcb,  mtl_item_categories          mic,  mtl_onhand_quantities_detail onhand   where 1=1    and msib.inventory_item_id = onhand.inventory_item_idand msib.organization_id = onhand.organization_idand onhand.inventory_item_id = cic.inventory_item_idand onhand.organization_id = cic.organization_idand mcb.category_id = mic.category_idand mic.inventory_item_id=msib.inventory_item_idand mic.organization_id=msib.organization_idand mcb.structure_id = 101and mic.category_set_id = 1and cic.cost_type_id = 3and onhand.organization_id = p_org_id--and msib.inventory_item_id=3073--and mcb.segment2=nvl(null,mcb.segment2) and mcb.segment2=p_item_categoryand substr(onhand.subinventory_code,1,1)not in ('E','B')--  and msib.segment1 between nvl(null,msib.segment1) and nvl(null,msib.segment1)and msib.segment1 between p_inventory_item_f and msib.segment1group by msib.organization_id,msib.inventory_item_id,mcb.segment1,mcb.segment2,msib.segment1,msib.description,msib.primary_unit_of_measure,cic.item_cost;
end if;--处理报表头信息g_debug        := '初始化公司LOGO和输入条件';--获取打印日期select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')into v_print_datefrom dual;--获取业务实体select trim(hou.name)into v_oufrom hr_operating_units houwhere hou.organization_id = p_org_id;--获取物料类型v_item_category:=trim(p_item_category);--获取物料从v_inventory_item_f:=trim(p_inventory_item_f);--获取物料至v_inventory_item_t:=trim(p_inventory_item_t);--获取物料呆滞天数v_dead_days:=to_char(p_dead_days);--开始输出报表头信息g_debug          :='输出打印日期和用户输入条件';output('

');output('' || v_print_date || '');output('' || v_ou || '');output('' || v_item_category || '');output('' || v_inventory_item_f || '');output('' || v_inventory_item_t || '');output('' || v_dead_days || '');--主体数据内循环开始g_debug         :='主体数据内循环开始';for c1r in c1 loopoutput('');output('' || c1r.item_category ||'.'|| c1r.item_sub_category || '');output('' || c1r.segment1 || '');output('' || cux_common_pkg.Xml_Format(c1r.description) || '');output('' || c1r.primary_unit_of_measure || '');output('' || c1r.Onhand_Quantity || '');output('' || to_char(c1r.last_transaction_date,'YYYY-MM-DD HH24:MI:SS') || '');output('' || c1r.Dead_Stock_Days || '');output('' || c1r.Item_Cost || '');output('' || c1r.onhand_amount || '');output('');end loop;output('');output('');exceptionwhen others theng_error := SQLERRM;INSERT INTO session_log --记录异常信息到异常表VALUES(SYSDATE, 'INVENTORY', 'CUX_INV_DEAD_STOCK_DETAIL_PKG', g_debug, g_error);COMMIT;outlog('出现错误');end inv_dead_stock_detail_main;
end CUX_INV_DEAD_STOCK_DETAIL_PKG;这样变更后，在正式环境中运行的时为3分钟46秒，测试环境，从7小时变是20分钟。