PL/SQL Developer runs queries faster when contains comments

Question

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.

turn off pl/sql dev, shutdown your db, start it again, start plsqldev, run query 2 ONCE and measure how long it takes to execute — Sebas, Jul 23 '14 at 17:48
@Sebas This is legacy code that I'm trying to bring up to speed, so your suggestion is greatly appreciated. — Dodzi Dzakuma, Jul 23 '14 at 17:49
check explain plan...are they different? perhaps query 1 is in the shared pool whereas query 2 is not? — Patrick Bacon, Jul 23 '14 at 17:51
Yeah there's something to do with the shared pool that's why I want him to restart the instance (it would also flush other pools). Comparing the explain plans would be a good idea even though I tend to think they will be similar — Sebas, Jul 23 '14 at 17:55
@Sebas, since query 1 is the existing legacy code, this would corroborate the shared pool explanation. — Patrick Bacon, Jul 23 '14 at 17:57
I am currently getting permission from the DBA to restart the database. This is a test environment database, but it is used by a few developers at the same time. @Sebas I ran an explain plan for both queries. The first time the explain plan came through, the second time it returned as `Explained`. I also got the message `'PLAN_TABLE' is old version` — Dodzi Dzakuma, Jul 23 '14 at 18:13
Guess what happens after you restart the database. If the problem goes away, you will never know how to actually resolve it until it happens again and you are then given the opportunity. In 20 years of Oracle, I've virtually never had to restart Oracle on stably patched systems (note I said virtually.. yes there have been a handful of cases). You should be able to accomplish whatever you need using administration commands. So what does restarting do? Flushes the SGA and all caches and forces everything to start cold. You lose your "real world" tuning environment until everything warms up. — codenheim, Jul 23 '14 at 18:15
yeah, I assumed it was dev and he could do whatever he wanted with it. Seems easier to run the flush commands now — Sebas, Jul 23 '14 at 18:17
I don't disagree that is may be SGA related, but he hasn't even viewed a query plan yet, so I think it is premature to restart Oracle. — codenheim, Jul 23 '14 at 18:18
The database has been rebooted and both queries run at about the same time. About 12 seconds which is too slow, but no more phantom comment speed. @Sebas — Dodzi Dzakuma, Jul 23 '14 at 18:42
Allright, then assume faster time is due to caching. Now it's time to optimize your query :) — Sebas, Jul 23 '14 at 18:43
You can obtain a plan_table ddl script that is appropriate for your oracle database by navigating to your $ORACLE_HOME/rdbms/admin directory if you want to pursue optimization. — Patrick Bacon, Jul 23 '14 at 19:09
Check the execution plans in memory (or from ASH if licenced) while they are running. My wild guess is they are different. — William Robertson, Dec 10 '15 at 18:21

score 3 · Accepted Answer · answered Jul 23 '14 at 19:30

3

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).

answered Jul 23 '14 at 19:30

Patrick Bacon

4,490
1
26
33

@Sebas,codenheim This was a very collaborative answer. – Patrick Bacon Jul 23 '14 at 19:40
Buffer cache is more likely than shared pool. The extra time to parse a single query is rarely noticeable, unless there's something weird going on like a large dynamic sampling. Buffer cache could easily explain a difference of more than a few seconds. Although something in this question still seems wrong. If the two queries were both run multiple times then neither buffer cache or shared pool should matter. – Jon Heller Jul 24 '14 at 05:24

PL/SQL Developer runs queries faster when contains comments

1 Answers1