CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloperquery-optimization
Sam
Sam

Reputation: 446

First execution of query is slow in Oracle

The first execution time of the query in the data warehouse DB in Oracle 18c takes two minutes, but if cancel the query after a few seconds in the first execution and re-run the query, it only takes 3 seconds to execute the query, so I'm wondering what happens when I cancel the query for the first time that causing the second execution to be very fast.

The execution plan of the first and the second execution of query is exactly the same and all the statistics are up to date and there isn't any stale statistic, below is the execution plan.

enter image description here

And below are optimizer parameters

memoptimize_pool_size   0
inmemory_optimized_arithmetic   DISABLE
plsql_optimize_level        2
optimizer_features_enable   12.1.0.2
optimizer_mode              ALL_ROWS
optimizer_index_cost_adj    100
optimizer_index_caching     0
optimizer_dynamic_sampling  0
optimizer_ignore_hints      FALSE
optimizer_secure_view_merging       TRUE
optimizer_use_pending_statistics    FALSE
optimizer_capture_sql_plan_baselines    FALSE
optimizer_use_sql_plan_baselines    TRUE
optimizer_use_invisible_indexes     FALSE
optimizer_adaptive_reporting_only   FALSE
optimizer_adaptive_plans            TRUE
optimizer_inmemory_aware            TRUE
optimizer_adaptive_statistics       FALSE

Should I change the value of any parameters in the above list to fix the performance issue of first execution time? e.g. increase optimizer_dynamic_sampling

Upvotes: 0

Views: 759

Answers (2)

BobC
BobC

Reputation: 4416

It could be that data is cached in the buffer cache, as noted by in the previous answer. It could also be that the SQL is taking a long time to parse, and the second execution is using the shared cursor. One way to test this would be to see how long it takes to EXPLAIN the statement.

It's definitely not the Result Cache; you would see this in the execution plan.

Upvotes: 0

Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

Oracle Database uses cache memory to store query results, cancelling query execution has nothing to do with it.

Oracle Database uses the buffer cache to store data blocks read from the disk. On the other hand Result Cache is a new feature in Oracle 11g and it does exactly what its name implies, it caches the results of queries and puts them into a slice of the shared pool.

When the query is executed for the first time and if data is not cached Oracle DB fetches the blocks from Disk and then they are cached in the Buffer pool, next time you execute the same query then blocks are simply fetched from the buffer pool.

Upvotes: 2

Related Questions