I noticed when running a SQL query it is faster the second time than the first. So my question is, in Oracle SQL Developer where can you see the statistics that explain this behaviour?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
What you are experiencing is called caching. The database doesn't have to go to disk the 2nd time because it can either get the data from its own buffer cache, or the operating system/disk array can also provide the data faster from its own cache.
In order to see whether Oracle fetched the data from disk, or used its cache you can enable autotrace in SQL Developer.
You'll get something like the following:
Physical reads are pulled from disk.
Another factor that makes the initial query slower is the fact that Oracle has to hard parse a query the first time it sees it - this involves finding the optimum (as far as the Oracle optimiser is concerned) execution plan for the query & is quite computationally (CPU) expensive, thus takes time.
Within the SGA, there is a component called shared pool which is made up of library cache and data dictionary cache. Compiled pl/sql & sql codes are kept in the library cache and hence subsequent execution of the same code is not compiled again.There is an expensive concept called library miss, which forces oracle to undergo a hard parse, a resource intensive process due to the fact that there was no compiled code available within the library cache and this is what happened the first time you executed the statement.