Clear the cache memory in oracle database for performance tuning in test environment
I am checking SQL Query execution time in Testing database.
SQL Query has been taken 8 minutes to execute in First time. After that, it has been taken 5 seconds to execute in second time. I think, it has been taken output from buffer memory. So execution is fast.
To check actual time of execution time, i have cleared the buffer using below command and re-execute the query.
alter system checkpoint;
alter system flush shared_pool;
alter system flush buffer_cache;
My understanding, if we clear the buffer memory using above query, query execution is again will take 8 minutes, because it is like as first time execution. But it has been taken only 5 seconds.
Please suggest if any other way is there to flush the buffer memory.
Next day, if i execute the query, it has been taken 8 minutes for the first time execution. then taken 5 seconds even clean the buffer.
Please suggest your ideas. Why the query execution is fast even clear the buffer. Then where it is stored?
Analyzed AWR Report, it seems user I/O wait time in high during first execution. after that, it is minimum value.
Please explain about user I/O wait time in AWR Statistics.
Click Here to Expand Forum to Full Width