Dodzi Dzakuma
Dodzi Dzakuma

Reputation: 1426

PL/SQL Developer runs queries faster when contains comments

I'm trying to improve performance on an SQL query against an Oracle 10g database. I have the following two queries:

Query 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(+)  

Query 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(+)

As you can see, the only difference between the two queries are the comments on at the top.

For some reason when I run query 2 multiple times in PL/SQL developer, it completes in under a second. When I run query 1 multiple times in the same environment it takes 15 seconds to complete.

I have checked the rows returned and both return the same number of rows with the same data.

Why would query 2 finish faster?
I believe that it might be a setting in PL/SQL Developer that is giving me false information.

Note
All of this was originally written for 8i, I'm improving it for 10g.

Upvotes: 3

Views: 1354

Answers (1)

Patrick Bacon
Patrick Bacon

Reputation: 4660

The first query is in the shared pool (corresponds with existing legacy procedure which is cited in the comments). The first query uses the compiled plan from the shared pool, thus having better refresh times, though both queries have the same explain plan (why would they have a different explain plan).

Upvotes: 3

Related Questions