I searched for this topic in DB2 thread bu i could not find what i wanted...
Is there any cache memory for DB2? i ran a query in QMF which took around 10 seconds to respond for the first time i hit F2. But i got the resulst in 1 or 2 seconds when i ran it for second and further...
i dont understand why it takes such a long time for first time and less for further.... Please advice if any of my understanding is incorrect...
1) Bufferpools :
- data(rows&indexes) is stored on disks (=I/O = long elapsed)
- Data is read into bufferpools (memory)
- least used data gets flushed from bufferpool
2) Dynamic Statement Cache :
- Dynamic SQL (like QMF) has to be prepared before execution (= longer CPU and thus longer elapsed)
- those prepared statements are kept in the Dynamic Statement Cache (=memory)
- least used statement gets flushed
3) same things apply to Database description, Packages, ....
A lot of stuff is kept somewhere in memory
This is only DB2.
I'm not such a hardware guy but a lot of storage devices now have their own cache.