Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

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:

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      3  consistent gets
      0  physical reads
      0  redo size
    538  bytes sent via SQL*Net to client
    543  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.