Query Execute Slow First Time But Fast on Second/Third Time

user_960567 0
Viewed 833

We are seeing a very weird condition in the Oracle query. The below query,

SELECT e.C1, MAX (e.Some) 
FROM MyTable e
WHERE e.Code = :Code
GROUP BY E.C1
ORDER BY MAX (e.Some)

Note the table contains around 5 million records and Code is a primary key.

On the first attempt, it returns the value in 60/70 second but after that, it returns the result in 500 milliseconds.

Is there any parameter sniffing in Oracle or can we have OPTION(RECOMPILE) in Oracle?

query-performance oracle sql
2 Answers

There are several reasons why an Oracle query may speed up on the second or third execution:

  1. Buffer Cache - Oracle will put frequently used table and index blocks in memory. The easiest way to check for this is to run the query in SQL*Plus multiple times, after enabling set autotrace on. If the value for "physical reads" disappears after the first run, then caching caused the difference. But caching can also happen at the storage or operating system level.
  2. Slow parsing/execution building - In some rare cases it may take Oracle a long time to build the initial execution plan. This can be especially true if dynamic sampling is used, and Oracle will read a portion of the table to make up for bad optimizer statistics. One way to look for this issue is to look for other system queries that run at the same time as your query. Another common cause of this is bad system or fixed object statistics, which means Oracle may have a hard time building queries to check for things like privileges.
  3. Cardinality feedback (11g)/statistics feedback (12c+) - By comparing expected cardinalities with actual cardinalities the optimizer is able to learn from its mistakes. Does the same SQL_ID have a different PLAN_HASH_VALUE in GV$SQL? If so, the execution plan is changing over time.
  4. Result cache - Oracle server and client can store the results of queries. This type of caching is actually much less common than people assume, because in practice the buffer cache is more useful - why store a single specific result in memory when you can store the data blocks that can serve multiple queries?
user_4091720

It might not be using index of the primary key. Try explain plan to check. Try rule base to force index usage:

select /*+ RULE */ from ...

Also, ask DBA to run table analysis to update statistics.

user_71510840
Related