What is the role of Already generated explain plan to execute the query

Question

My question is whether an already-generated explanation plan can improve query execution.

I have done the following:

I have executed the query for the first time and checked the SQL ID and the explain plan. It took 36 seconds.

After that, I am executing that query in the same session with different parameters so that data can't be fetched from the cache, and it is taking the same execution plan and getting executed in less than 3 seconds.

When I execute the same query in a different session, then again, it starts taking 30–40 seconds to execute using the same sqlid and plan_hash_value.

Please explain and suggest what we can do about this.

Answer 1

It's hard to give specific advice without much more details, but the below answer provides some information about caching and pre-existing plans that might explain some concepts that help you home in on the specific problem.

Caching may be more important than you think

Oracle's most important cache is the buffer cache, which stores the blocks used to generate the results. The actual results themselves are rarely cached. This means that there is a good chance of the same query with different parameters successfully using the same cache.

For example, if the two queries have a full table scan that is cached, the cache may work even if the second query returns completely different results. Even if there are only indexes used, the index blocks often contain extra rows that can help multiple queries. The cache is for the whole system, so it doesn't matter who the user is.

It's pretty difficult to tell exactly what the cache is doing. In addition to thinking about what rows are cached, you need to worry about when does the cache age out. You may need to run your queries repeatedly to keep the system "hot". Or you may want to alter system flush buffer_cache to test the system when it's "cold".

Advantage of pre-existing execution plans

Oracle has several mechanisms where it can re-write the execution plan depending on the initial results. For example, with statistics feedback (aka cardinality feedback), Oracle uses previous mistakes to change the execution plans. It's not unusual for queries to automatically get faster with more runs. Pay special attention to the "Note" section of the execution plan, for details on those weird mechanism that can improve execution plans. And make sure you view the adaptive results, because some plans can have multiple plan options:

select * from table(dbms_xplan.display(format => '+adaptive'));

Gather more information

If you want more specific advice, start with finding the actual numbers for the execution plans instead of just the estimates. Comparing the actual times and rows with the estimated times and rows is the best way to quickly find the exact problem and possible solutions. Since your query runs relatively quickly, you might want to use the /*+ gather_plan_statistics */ hint, as I demonstrate in this answer.