一段sql 执行效率的疑惑。

mangguo6357

原帖由 remen 于 2008-4-19 10:13 发表
conn inv/inv
给出下面结果
select a.table_name,a.blocks,a.num_rows
from user_tables  a
where a.table_name=upper('mtl_categories_b')

select count(*) from  mtl_categories_b

select b.table_name,
       b.num_rows,
       b.index_name,
       b.avg_leaf_blocks_per_key,
       b.avg_data_blocks_per_key,
       b.clustering_factor
  from user_indexes b
where b.table_name = upper('mtl_categories_b')
   and b.index_name = 'MTL_CATEGORIES_B_U1'

------------------------------------------------------------------------------------
1
select a.table_name,a.blocks,a.num_rows
from user_tables  a
where a.table_name=upper('mtl_categories_b')

TABLE_NAME          BLOCKS             NUM_ROWS
MTL_CATEGORIES_B        1        1

-----------------------------------------------------------------

2   select count(*) from  mtl_categories_b

105970

-------------------------------------------------------------------
3
select b.table_name,
       b.num_rows,
       b.index_name,
       b.avg_leaf_blocks_per_key,
       b.avg_data_blocks_per_key,
       b.clustering_factor
  from user_indexes b
where b.table_name = upper('mtl_categories_b')
   and b.index_name = 'MTL_CATEGORIES_B_U1'

TABLE_NAME        NUM_ROWS        INDEX_NAME        AVG_LEAF_BLOCKS_PER_KEY        AVG_DATA_BLOCKS_PER_KEY        CLUSTERING_FACTOR
MTL_CATEGORIES_B        1        MTL_CATEGORIES_B_U1        1        1        1

棉花糖ONE

MTL_CATEGORIES_B 这个表的统计信息有问题

mangguo6357

原帖由 stronghearted 于 2008-4-18 18:34 发表
不要toad查看执行计划，这种工具可能会有bug.

set autotrace traceonly看看

1、如果还是FTS，索引是否创建的合适？统计信息是否正确？

2、这个两个参数是否需要调整
optimizer_index_caching   
optimizer_index_cost_adj

3、hint

用 set autotrace traceonly看了，还是TFS.

棉花糖ONE

搜集MTL_CATEGORIES_B表和索引的统计信息

remen

select a.table_name,a.blocks,a.num_rows
from user_tables  a
where a.table_name=upper('mtl_categories_b')

TABLE_NAME          BLOCKS             NUM_ROWS
MTL_CATEGORIES_B        1        1

-----------------------------------------------------------------

2   select count(*) from  mtl_categories_b

105970


看到这里你还不清楚吗？很明显这个表的统计信息有问题
重新分析这个表以及Index
然后再贴出
select a.table_name,a.blocks,a.num_rows
from user_tables  a
where a.table_name=upper('mtl_categories_b')的结果

mangguo6357

原帖由 wksw 于 2008-4-19 12:33 发表


1。 表和索引的结构相同，但如果两个环境的配置参数不同，表的数据量不同，或是表和索引的统计数据不同，那么优化器产生的执行计划也会不同。这是由于优化器要靠这些因素来共同决定何种执行计划是消耗最低的。
2。wip_requirement_operations_v 可能是一个view而不是table。其内容是什么？
3。为什么在你的语句里没看到mtl_categories_b表？

1.数据量情况：select count(*) from  mtl_item_categories mic  --PDN  2325430  ,TRN 2142420
                     select count(*) from      mtl_categories mca  --PDN  105970 ,TRN 105961
                     select count(*) from      wip_requirement_operations_v req  --PDN  1785,TRN 1225
                     select count(*) from      wip_discrete_jobs wdj  --PDN 2198,TRN 1879
   统计信息时间不同：
  select  index_name ,LAST_ANALYZED from DBA_INDEXES
   where  owner='INV' and index_name like 'MTL%CATEGORIES%B%';
   ---TRM
    MTL_CATEGORIES_B_N1        2007-11-21 16:41:55
      MTL_CATEGORIES_B_N_ABCG        2007-11-21 16:41:51
      MTL_CATEGORIES_B_U1        2007-11-21 16:41:55
      MTL__CATEGORIES_B_N2        2007-11-21 16:41:55
  ---PDN
     MTL_CATEGORIES_B_N1        2008-3-31 19:35:25
     MTL_CATEGORIES_B_N_ABCG        2008-3-31 19:35:25
     MTL_CATEGORIES_B_U1        2008-3-31 19:35:25
    MTL__CATEGORIES_B_N2        2008-3-31 19:35:25

    select owner,table_name,LAST_ANALYZED from dba_tables
     where owner='INV' and table_name='MTL_CATEGORIES_B';
   ---- TRN
        INV        MTL_CATEGORIES_B        2007-11-21 16:41:50
    ----PDN
        INV        MTL_CATEGORIES_B        2008-3-31 19:35:25

2.wip_requirement_operations_v 是一个view,
CREATE OR REPLACE VIEW WIP_REQUIREMENT_OPERATIONS_V
(row_id, inventory_item_id, concatenated_segments, item_description, item_primary_uom_code, inventory_asset_flag, location_control_code, restrict_subinventories_code, restrict_locators_code, organization_id, wip_entity_id, operation_seq_num, repetitive_schedule_id, line_id, last_update_date, last_updated_by, creation_date, created_by, last_update_login, request_id, program_application_id, program_id, program_update_date, department_id, department_code, wip_supply_type, wip_supply_meaning, date_required, required_quantity, quantity_issued, quantity_open, quantity_per_assembly, comments, supply_subinventory, supply_locator_id, mrp_net_flag, mps_required_quantity, mps_date_required, routing_exists_flag, first_unit_start_date, first_unit_completion_date, last_unit_completion_date, attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15, quantity_allocated, auto_request_material)
AS
SELECT WRO.ROWID ROW_ID , WRO.INVENTORY_ITEM_ID , MSIK.CONCATENATED_SEGMENTS , MSIK.DESCRIPTION ITEM_DESCRIPTION , MSIK.PRIMARY_UOM_CODE ITEM_PRIMARY_UOM_CODE , MSIK.INVENTORY_ASSET_FLAG , MSIK.LOCATION_CONTROL_CODE , MSIK.RESTRICT_LOCATORS_CODE , MSIK.RESTRICT_SUBINVENTORIES_CODE , WRO.ORGANIZATION_ID , WRO.WIP_ENTITY_ID , WRO.OPERATION_SEQ_NUM , WRO.REPETITIVE_SCHEDULE_ID , WRS.LINE_ID , WRO.LAST_UPDATE_DATE , WRO.LAST_UPDATED_BY , WRO.CREATION_DATE , WRO.CREATED_BY , WRO.LAST_UPDATE_LOGIN , WRO.REQUEST_ID , WRO.PROGRAM_APPLICATION_ID , WRO.PROGRAM_ID , WRO.PROGRAM_UPDATE_DATE , WRO.DEPARTMENT_ID , BD.DEPARTMENT_CODE , WRO.WIP_SUPPLY_TYPE , ML1.MEANING WIP_SUPPLY_MEANING , WRO.DATE_REQUIRED , WRO.REQUIRED_QUANTITY , DECODE(WRO.QUANTITY_ISSUED, 0,NULL, WRO.QUANTITY_ISSUED) QUANTITY_ISSUED , DECODE((WRO.REQUIRED_QUANTITY-WRO.QUANTITY_ISSUED), 0,NULL, DECODE(SIGN(WRO.REQUIRED_QUANTITY), -1*SIGN(WRO.QUANTITY_ISSUED), (WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED), DECODE(SIGN(ABS(WRO.REQUIRED_QUANTITY) - ABS(WRO.QUANTITY_ISSUED)), -1, NULL, (WRO.REQUIRED_QUANTITY-WRO.QUANTITY_ISSUED)))) QUANTITY_OPEN , WRO.QUANTITY_PER_ASSEMBLY , WRO.COMMENTS , WRO.SUPPLY_SUBINVENTORY , WRO.SUPPLY_LOCATOR_ID , WRO.MRP_NET_FLAG , WRO.MPS_REQUIRED_QUANTITY , WRO.MPS_DATE_REQUIRED , DECODE(WOP.OPERATION_SEQ_NUM, NULL,0,1) ROUTING_EXISTS_FLAG , WOP.FIRST_UNIT_START_DATE , WOP.FIRST_UNIT_COMPLETION_DATE , WOP.LAST_UNIT_COMPLETION_DATE , WRO.ATTRIBUTE_CATEGORY , WRO.ATTRIBUTE1 , WRO.ATTRIBUTE2 , WRO.ATTRIBUTE3 , WRO.ATTRIBUTE4 , WRO.ATTRIBUTE5 , WRO.ATTRIBUTE6 , WRO.ATTRIBUTE7 , WRO.ATTRIBUTE8 , WRO.ATTRIBUTE9 , WRO.ATTRIBUTE10 , WRO.ATTRIBUTE11 , WRO.ATTRIBUTE12 , WRO.ATTRIBUTE13 , WRO.ATTRIBUTE14 , WRO.ATTRIBUTE15 , DECODE(WRO.QUANTITY_ALLOCATED, 0, to_number(NULL), WRO.QUANTITY_ALLOCATED) QUANTITY_ALLOCATED , WRO.AUTO_REQUEST_MATERIAL FROM MTL_SYSTEM_ITEMS_KFV MSIK , WIP_OPERATIONS WOP , WIP_REPETITIVE_SCHEDULES WRS , BOM_DEPARTMENTS BD , MFG_LOOKUPS ML1 , WIP_REQUIREMENT_OPERATIONS WRO WHERE MSIK.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID AND MSIK.ORGANIZATION_ID = WRO.ORGANIZATION_ID AND WRO.ORGANIZATION_ID = WOP.ORGANIZATION_ID(+) AND WRO.WIP_ENTITY_ID = WOP.WIP_ENTITY_ID(+) AND WRO.REPETITIVE_SCHEDULE_ID IS NULL AND NVL(WRO.REPETITIVE_SCHEDULE_ID,'-1') = NVL(WOP.REPETITIVE_SCHEDULE_ID,'-1') AND WRO.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM(+) AND WRS.REPETITIVE_SCHEDULE_ID(+) = WRO.REPETITIVE_SCHEDULE_ID AND WRS.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID AND BD.DEPARTMENT_ID(+) = WRO.DEPARTMENT_ID AND ML1.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE AND ML1.LOOKUP_TYPE = 'WIP_SUPPLY' AND MSIK.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID AND MSIK.ORGANIZATION_ID = WRO.ORGANIZATION_ID UNION ALL SELECT WRO.ROWID ROW_ID , WRO.INVENTORY_ITEM_ID , MSIK.CONCATENATED_SEGMENTS , MSIK.DESCRIPTION ITEM_DESCRIPTION , MSIK.PRIMARY_UOM_CODE ITEM_PRIMARY_UOM_CODE , MSIK.INVENTORY_ASSET_FLAG , MSIK.LOCATION_CONTROL_CODE , MSIK.RESTRICT_LOCATORS_CODE , MSIK.RESTRICT_SUBINVENTORIES_CODE , WRO.ORGANIZATION_ID , WRO.WIP_ENTITY_ID , WRO.OPERATION_SEQ_NUM , WRO.REPETITIVE_SCHEDULE_ID , WRS.LINE_ID , WRO.LAST_UPDATE_DATE , WRO.LAST_UPDATED_BY , WRO.CREATION_DATE , WRO.CREATED_BY , WRO.LAST_UPDATE_LOGIN , WRO.REQUEST_ID , WRO.PROGRAM_APPLICATION_ID , WRO.PROGRAM_ID , WRO.PROGRAM_UPDATE_DATE , WRO.DEPARTMENT_ID , BD.DEPARTMENT_CODE , WRO.WIP_SUPPLY_TYPE , ML1.MEANING WIP_SUPPLY_MEANING , WRO.DATE_REQUIRED , WRO.REQUIRED_QUANTITY , DECODE(WRO.QUANTITY_ISSUED, 0,NULL, WRO.QUANTITY_ISSUED) QUANTITY_ISSUED , DECODE((WRO.REQUIRED_QUANTITY-WRO.QUANTITY_ISSUED), 0,NULL, DECODE(SIGN(WRO.REQUIRED_QUANTITY), -1*SIGN(WRO.QUANTITY_ISSUED), (WRO.REQUIRED_QUANTITY-WRO.QUANTITY_ISSUED), DECODE(SIGN(ABS(WRO.REQUIRED_QUANTITY) - ABS(WRO.QUANTITY_ISSUED)),-1,NULL, (WRO.REQUIRED_QUANTITY-WRO.QUANTITY_ISSUED)))) QUANTITY_OPEN , WRO.QUANTITY_PER_ASSEMBLY , WRO.COMMENTS , WRO.SUPPLY_SUBINVENTORY , WRO.SUPPLY_LOCATOR_ID , WRO.MRP_NET_FLAG , WRO.MPS_REQUIRED_QUANTITY , WRO.MPS_DATE_REQUIRED , DECODE(WOP.OPERATION_SEQ_NUM, NULL,0,1) ROUTING_EXISTS_FLAG , WOP.FIRST_UNIT_START_DATE , WOP.FIRST_UNIT_COMPLETION_DATE , WOP.LAST_UNIT_COMPLETION_DATE , WRO.ATTRIBUTE_CATEGORY , WRO.ATTRIBUTE1 , WRO.ATTRIBUTE2 , WRO.ATTRIBUTE3 , WRO.ATTRIBUTE4 , WRO.ATTRIBUTE5 , WRO.ATTRIBUTE6 , WRO.ATTRIBUTE7 , WRO.ATTRIBUTE8 , WRO.ATTRIBUTE9 , WRO.ATTRIBUTE10 , WRO.ATTRIBUTE11 , WRO.ATTRIBUTE12 , WRO.ATTRIBUTE13 , WRO.ATTRIBUTE14 , WRO.ATTRIBUTE15 , DECODE(WRO.QUANTITY_ALLOCATED, 0, to_number(NULL), WRO.QUANTITY_ALLOCATED) QUANTITY_ALLOCATED , WRO.AUTO_REQUEST_MATERIAL FROM MTL_SYSTEM_ITEMS_KFV MSIK , WIP_OPERATIONS WOP , WIP_REPETITIVE_SCHEDULES WRS , BOM_DEPARTMENTS BD , MFG_LOOKUPS ML1 , WIP_REQUIREMENT_OPERATIONS WRO WHERE MSIK.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID AND MSIK.ORGANIZATION_ID = WRO.ORGANIZATION_ID AND WRO.ORGANIZATION_ID = WOP.ORGANIZATION_ID(+) AND WRO.WIP_ENTITY_ID = WOP.WIP_ENTITY_ID(+) AND WRO.REPETITIVE_SCHEDULE_ID IS NOT NULL AND WRO.REPETITIVE_SCHEDULE_ID = WOP.REPETITIVE_SCHEDULE_ID(+) AND WRO.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM(+) AND WRS.REPETITIVE_SCHEDULE_ID(+) = WRO.REPETITIVE_SCHEDULE_ID AND WRS.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID AND BD.DEPARTMENT_ID(+) = WRO.DEPARTMENT_ID AND ML1.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE AND ML1.LOOKUP_TYPE = 'WIP_SUPPLY' AND MSIK.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID AND MSIK.ORGANIZATION_ID = WRO.ORGANIZATION_ID

3.mtl_categories_b表 在view mtl_categories中有用到。

remen

别发这些没用的
1  analyze table inv.mtl_categories_b compute statistics
2  select a.table_name,a.blocks,a.num_rows
from user_tables  a
where a.table_name=upper('mtl_categories_b')

mangguo6357

原帖由 remen 于 2008-4-21 08:46 发表
别发这些没用的
1  analyze table inv.mtl_categories_b compute statistics
2  select a.table_name,a.blocks,a.num_rows
from user_tables  a
where a.table_name=upper('mtl_categories_b')

太感谢Remen,就是这个问题。

执行1以后select a.table_name,a.blocks,a.num_rows
from user_tables  a
where a.table_name=upper('mtl_categories_b') [/quote]



TABLE_NAME        BLOCKS        NUM_ROWS
MTL_CATEGORIES_B        1715        105970

wjz83

使用说明

remen

这次你的sql走了Index了吧？
oracle erp的table和view太麻烦，如果没有接触过的，确实调整起来比较麻烦