Reputation: 1426
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
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