当包含注释时，PL/运行查询的速度更快

Question

我试图提高针对Oracle 10g数据库的SQL查询的性能。我有以下两个问题：

查询1

SELECT DISTINCT 
    WMS_EVENT_LOG.LOG_ID,
    WMS_EVENT_LOG.EVENT_ARG4,
    WMS_EVENT_LOG.EVENT_TYPE,
    WMS_EVENT_LOG.EVENT_ARG1,
    WMS_EVENT_LOG.EVENT_ARG3,
    WMS_EVENT_LOG.PROD_ITEM_ID,
    TRUNC(WMS_EVENT_LOG.LOG_DATE) AS LOG_DATE,
    WMS_EVENT_LOG.PALLET_ID,
    WMS_EVENT_LOG.LOG_USER,
    WMS_EVENT_LOG.POSTED_TO_KCAT,
    POSTEDWMSTRANS.POSTTRAN,
    DECODE(POSTEDWMSTRANS.POSTTRAN, 'Y', POSTEDWMSTRANS.CMNT, WMS_EVENT_LOG.EVENT_ARG3) AS CMNT --cmnt = comment
FROM 
    VMR_WMSEVENTLOG WMS_EVENT_LOG, 
    VM_ADJUST_REASON ADJUST_REASON,
    ( 
      SELECT 
          INVENTORY.BOM_TYPE AS POSTTRAN,
          INVENTORY.PROD_ITEM_ID,
          INVENTORY.CMNT,
          INVENTORY.WMSLINK
      FROM 
          VR_BOM INVENTORY
      WHERE 
          INVENTORY.BOM_TDATE >= TO_DATE(:report_start_date, 'YYYY/MM/DD') - 15 AND 
          INVENTORY.BOM_TDATE < TO_DATE(:report_end_date, 'YYYY/MM/DD') + 15 AND
          INVENTORY.WMSLINK IS NOT NULL AND 
          INVENTORY.BOM_TYPE <> 'HLD' 
    ) POSTEDWMSTRANS
WHERE 
    WMS_EVENT_LOG.LOG_DATE >= TO_DATE(:report_start_date, 'YYYY/MM/DD') AND 
    WMS_EVENT_LOG.LOG_DATE < TO_DATE(:report_end_date, 'YYYY/MM/DD') AND 
    WMS_EVENT_LOG.EVENT_TYPE = 31 AND
    ADJUST_REASON.ADJUST_REASON_CODE NOT IN ('SPL') AND
    ADJUST_REASON.ADJUST_REASON_CODE = WMS_EVENT_LOG.EVENT_ARG1 AND ( 
        WMS_EVENT_LOG.EVENT_ARG1 <> 'MOV' AND 
        WMS_EVENT_LOG.EVENT_ARG2 = 'ADJUST' 
    ) AND 
    WMS_EVENT_LOG.PROD_ITEM_ID = POSTEDWMSTRANS.PROD_ITEM_ID(+) AND
    WMS_EVENT_LOG.EVENT_ARG4 = POSTEDWMSTRANS.WMSLINK(+)  

查询2

--VARIABLE report_start_date VARCHAR
--VARIABLE report_end_date VARCHAR

SELECT DISTINCT 
    WMS_EVENT_LOG.LOG_ID,
    WMS_EVENT_LOG.EVENT_ARG4,
    WMS_EVENT_LOG.EVENT_TYPE,
    WMS_EVENT_LOG.EVENT_ARG1,
    WMS_EVENT_LOG.EVENT_ARG3,
    WMS_EVENT_LOG.PROD_ITEM_ID,
    TRUNC(WMS_EVENT_LOG.LOG_DATE) AS LOG_DATE,
    WMS_EVENT_LOG.PALLET_ID,
    WMS_EVENT_LOG.LOG_USER,
    WMS_EVENT_LOG.POSTED_TO_KCAT,
    POSTEDWMSTRANS.POSTTRAN,
    DECODE(POSTEDWMSTRANS.POSTTRAN, 'Y', POSTEDWMSTRANS.CMNT, WMS_EVENT_LOG.EVENT_ARG3) AS CMNT --cmnt = comment
FROM 
    VMR_WMSEVENTLOG WMS_EVENT_LOG, 
    VM_ADJUST_REASON ADJUST_REASON,
    ( 
      SELECT 
          INVENTORY.BOM_TYPE AS POSTTRAN,
          INVENTORY.PROD_ITEM_ID,
          INVENTORY.CMNT,
          INVENTORY.WMSLINK
      FROM 
          VR_BOM INVENTORY
      WHERE 
          INVENTORY.BOM_TDATE >= TO_DATE(:report_start_date, 'YYYY/MM/DD') - 15 AND 
          INVENTORY.BOM_TDATE < TO_DATE(:report_end_date, 'YYYY/MM/DD') + 15 AND
          INVENTORY.WMSLINK IS NOT NULL AND 
          INVENTORY.BOM_TYPE <> 'HLD' 
    ) POSTEDWMSTRANS
WHERE 
    WMS_EVENT_LOG.LOG_DATE >= TO_DATE(:report_start_date, 'YYYY/MM/DD') AND 
    WMS_EVENT_LOG.LOG_DATE < TO_DATE(:report_end_date, 'YYYY/MM/DD') AND 
    WMS_EVENT_LOG.EVENT_TYPE = 31 AND
    ADJUST_REASON.ADJUST_REASON_CODE NOT IN ('SPL') AND
    ADJUST_REASON.ADJUST_REASON_CODE = WMS_EVENT_LOG.EVENT_ARG1 AND ( 
        WMS_EVENT_LOG.EVENT_ARG1 <> 'MOV' AND 
        WMS_EVENT_LOG.EVENT_ARG2 = 'ADJUST' 
    ) AND 
    WMS_EVENT_LOG.PROD_ITEM_ID = POSTEDWMSTRANS.PROD_ITEM_ID(+) AND
    WMS_EVENT_LOG.EVENT_ARG4 = POSTEDWMSTRANS.WMSLINK(+)

正如您所看到的，这两个查询之间唯一的区别是顶部的注释。

由于某些原因，当我在PL/ second中多次运行query 2时，它在一秒钟内完成。当我在同一个环境中多次运行query 1时，需要15秒才能完成。

我已经检查了返回的行，这两个行都返回相同数据的行数。

为什么查询2会完成得更快？

我相信，这可能是PL/SQL开发人员中的一个设置，给了我错误的信息。

备注

所有这些最初都是为8i编写的，我正在为10g改进它。

Answer 1

第一个查询位于共享池中(对应于注释中引用的现有遗留过程)。第一个查询使用共享池中的已编译计划，因此具有更好的刷新时间，尽管两个查询都有相同的解释计划(为什么它们会有不同的解释计划)。