I am checking SQL Query execution time in Testing database.
SQL Query has been taken 8 minutes to execute in First time. then it has been taken 5 seconds to execute in second time. I think, it has been taken output from buffer memory.
To find out actual time of execution time, i have cleared the buffer using below command and re-execute the query.
alter system flush shared_pool;
alter system flush buffer_cache;
I thought. if we cleared 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.
I have restarted the database and then check. but it is also taken 5 seconds even restart the database. But if we execute the same in next day, the first execution time is taken 8 minutes and then taken 5 seconds from second execution.
So buffer is not cleared even restart the database.
1) Why it has been taken long time first execution of every day (or) after some time intervel?
2) Why it has been taken minimum time/fast execution even cleared the buffer and also restarted the database?
3) Please suggest your ideas. Why the query execution is fast even clear the buffer. Then where it is stored? how we can clear to analyze the actual time of query performance?
Please suggest if any other way is there to flush the buffer memory.
Regarding production environment, we know, we should not flush buffer. there always taken same time(8 minutes) for that query multiple execution.
NOTE:
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.
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.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Points Added:
We are using the oracle database version 10.2.0.4.0 & Linux environment.
As i have analyzed the both AWR DB Report & SQL Report. It seems, User I/O wait time is high in the first time query execution.
Execution plan is same at all time whenever execute the query. but User I/O wait time is high in the first time query execution. After that, query execution fast even cleared the buffer memory and restarted the database.
Top 5 Timed Events in 2 different execution - User I/O wait time is high in the first execution. I have specified the both execution plan difference;
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
Execution I db file sequential read 209,500 493 2 91.2 User I/O
Execution II db file sequential read 196,684 1 0 11.1 User I/O
Plan Statistics
Stat Name Statement Total
Execution I User I/O Wait Time (ms) 491,506
Execution II User I/O Wait Time (ms) 876
Why User I/O Wait Time (ms) is taken time in the first time query execution?
I have cleared buffer and also restarted database and then re-execute the query again.but query execution is fast. It seems, Buffer memory is available even bounce the database.
How we can cleared / Flushed the buffer memory?