Oracle: performance changing over time

Question

I am doing performance tests on a table and for that I insert several millions of rows with fake data and perform the query.

Initially the response time is severely degraded, but I retested several hours later, and the response time improved significantly.

Is Oracle busy with some activities just after my insertion but these activities are finished after some time? I need an explanation for this behavior.

Thanks!

Answer 1

When you first run a query, the SQL Engine has to do everything including, but not limited to:

• Parse the query;
• Generate a plan for the query;
• Perform IO to load the blocks of data from the datafile;
• Transform the data;
• Store the result set in the result cache;
• Return the results to the client.

When you run a query for a second time, the SQL engine can:

• Check whether the query has been run before and if the table statistics is unchanged and, if so, load the previous plan;
• Check if the underlying data is unchanged and if the result of the previous query is in the result cache and, if so, return the result directly from the result cache;
• If something has changed, can check if the blocks are still in its local cache and if they are not stale then it does not have to perform IO from the datafiles.

Therefore, on a second execution of a query there are lots of optimisations that can be made that short cut through some of the expensive operations that must be made on the first run of a query.

A simulated example of the performance optimisations that can be made using the results cache is in this db<>fiddle.