Simple string comparison condition makes query run longer

user_3056886 0
Viewed 4208

Query #1 from the code below at the very first run takes 1-2 seconds to execute, second and further runs under 0.1 second. Meanwhile queries #2 and #3 always execute within 0.1 - 0.2 seconds. Is there any explanation for this behaviour? I would understand if it was vice versa because of false condition in #1.

I am not providing execute plan yet because that view is a bit complicated and not sure that plan itself would help. But I will if it's indeed required

1

SELECT
doc_id, line_num, load_id, ord_qty
FROM someview v
WHERE doc_id = 2987541 and 'A'='X' and v.iter_num = 
(select max(iter_num) from someview where doc_id=v.doc_id);

2

SELECT
doc_id, line_num, load_id, ord_qty
FROM someview v
WHERE doc_id = 2987541 and v.iter_num = 
(select max(iter_num) from someview where doc_id=v.doc_id);

3

SELECT
doc_id, line_num, load_id, ord_qty
FROM someview v
WHERE doc_id = 2987541 and 'X'='X' and v.iter_num = 
(select max(iter_num) from someview where doc_id=v.doc_id);
oracle12c oracle sql
1 Answers

The difference in runtimes for query #1 may due to hard parsing and/or a cold buffer cache.

After the first run, the parsed query cached is in the shared pool, and data blocks have been read into the buffer cache in shared memory (assuming that they don't immediately get aged out due to a busy system). So, the second run and beyond generally should be faster. You could test whether query #1's longer initial runtime is due to hard parsing by running the query, purging the associated cursor from the shared pool, running it again and seeing if the runtime remains at 1-2 seconds. Although the entire shared pool can be purged via an ALTER SYSTEM FLUSH SHARED POOL command, a less intrusive approach is to purge your specific cursor. The references below show how to do this.

DBMS_SHARED_POOL.PURGE

How to flush a single object from the shared pool

Alternatively, you could trace your session, and use tkprof to see how much time is spent in the parse, execute, and fetch stages for query #1, and compare the results against subsequent runs. See reference below for details.

SQL trace and TKPROF

As for query #2 and #3, if you're running them right after query #1, then they may be running faster due to query #1 already having warmed the cache. Unfortunately, there's not an easy way to selectively flush data blocks from the buffer cache like you can with parsed statements in the shared pool. While an ALTER SYSTEM FLUSH BUFFER CACHE statement can clear the cache, it's a really bad idea to do that on a production system as it can cause a huge spike in physical I/O while the cache is repopulated. Doing it on an isolated non-production system may be acceptable.

Back to parsing, query #3 may be able to use the same parsed query as #1 depending on how the cursor_sharing parameter is set, which would significantly save on parse time.

CURSOR_SHARING parameter

Finally, tkprof and the other tools referenced in this answer require elevated privileges in the database, so you may need help from your DBA.

user_116113770
Related