Caching Data to Buffers
Vikas Hivarkar on IDS 11.10 asked what update statistics does, whether a table can be pinned in buffers, and whether RESIDENT 1 suits OLTP. Mike Magie said update stats gives the optimizer accurate data. Art Kagel said table pinning was a failure in 7.30/9.21 and was removed, and variable page sizes isolate buffer pools. Forced residency is advisable unless memory is tight.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello, IDS 11.10.FC2W1 Yet again some simple but imp Qs 1)Could any one pls explain me IN SHORT what Update statistics actually does. i am reading/studying and running upate statiscs for some time now but even today it is not very clear to me :( 2)Also pls let me know if it is possible to cache(make it resident or pin it) a specific table in buffers. if yes then how do i achieve that. 3)I have alwasy worked with RESIDENT 0, but now i have been adviced to set the RESIDENT 1 (Setting the forced residency allows high-priority applications or segments to always remain in memory without being swapped to disk.) Who decides what all allows high-priority applications are to be kept in memory? For OLTP system is Foreced Residency a good option? Thanking you all in advance! Regards, vikas
Regarding Update Stats - short answer It synchs meta data with your real data, providing the SQL optimizer with the best possible query plan(s). MM
Sorry - long answer: http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/mil ler/0203miller.html
See below: On Thu, Jun 5, 2008 at 10:29 AM, VIKAS HIVARKAR <vikashivarkar3@gmail.com> wrote: > Hello, > > IDS 11.10.FC2W1 > > Yet again some simple but imp Qs > > 1)Could any one pls explain me IN SHORT what Update statistics actually > does. > i am reading/studying and running upate statiscs for some time now but even > today it is not very clear to me :( In SHORT? I provides the statistical data that the optimizer needs to make good decisions about the optimal query paths for your applications. > > > 2)Also pls let me know if it is possible to cache(make it resident or pin > it) > a specific table in buffers. if yes then how do i achieve that. No. There was such a feature in IDS 7.30 and 9.21, but it was a dismal failure. It caused more trouble that it fixed and it turns out that the engine, especially versions 10.00 and later, do a MUCH better job of deciding what should be kept in cache than most DBAs. That said, in 10.00 and later, by using the variable pagesizes to create independent buffer caches, you can isolate tables from indexes and any partition from others by placing then into dbspaces with different page sizes. > > > 3)I have alwasy worked with RESIDENT 0, > but now i have been adviced to set the RESIDENT 1 > (Setting the forced residency allows high-priority applications or segments > to > always remain in memory without being swapped to disk.) > Who decides what all allows high-priority applications are to be kept in > memory? The OS, but by forcing residency, you make IDS a priority for non-swappable memory. > > > For OLTP system is Foreced Residency a good option? Yes, unless memory is tight and other applications need a more equitable balance of swapping in order to perform. Certainly on a system with sufficient memory the answer is yes. > > > Thanking you all in advance! > Regards, > vikas > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.