If my memory serves me right, the ability to pin tables in memory was introduced in Oracle 8.0.6, so it's been a while back (more than 10 years ago). With each new database version, this feature has matured, the internals of this feature have changed and matured. But this feature is very solid, very mature and works.
You may either google your specific questions or ask a knowledgeable DBA, or somebody that knows all the internals of it. I don't worry about the internals, I only know that it works, by looking at the execution plans and how fast the data is provided. ________________________________ From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 08, 2011 3:22 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** How would pinning a table impact tables that may have a frequent update? For eg lets say in a case where your customer information that is created and updated frequently on a daily basis, is stored in the People form, and is accessed when creating and updating incident records for them? My understanding when you pin objects to memory, the read is not a frequent read. I do not know at what intervals the memory is updated or if it is updated as soon as there is a change on that object. Anybody with knowledge of that? If the read is as frequent as an update or an insert, what impact would that have on pinning it to the database? Joe From: patrick zandi<mailto:remedy...@gmail.com> Sent: Thursday, September 08, 2011 2:44 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Performance question CTM:People timing ** I have done this in the past:: pin "user" (T30 for me) table, this will drop IO to database.. have see this alot.. On Thu, Sep 8, 2011 at 2:36 PM, Guillaume Rheault <guilla...@dcshq.com<mailto:guilla...@dcshq.com>> wrote: ** Hi Joe, I got to disagree with you again... but I guess this is what makes this ARS list fun! Pinning a table into memory is not an overkill, it is quite simple to do, you can ask your Oracle DBA. Since the cost of physical memory is lower and lower every year, it is actually more cost-effective to add some more memory to your database server and pin look-up tables, than optimizing the searches to these look-up tables; optimizing the searches will involves one or more of the following: - Possible DBA time to analyze the performance of queries - Remedy Admin/Developer/Consultant time to figure where those sub-optimal searches are being issued from, and modify them - Possible customizations to the ITSM application (which is what everybody is trying to avoid) Pinning a table into memory involves: - Small amount of DBA time to alter the T table to pin it. - Small amount of sys admin to add memory in the database server (this cost is a one time cost) See, when you pin the table in memory, it does NOT matter if your queries are crappy or inefficient, since the table data is in memory; that's the beauty of it! While you are at it, you may as well pin the T table related to the User form. cheers, Guillaume ________________________________ From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] on behalf of Joe Martin D'Souza [jdso...@shyle.net<mailto:jdso...@shyle.net>] Sent: Thursday, September 08, 2011 12:33 PM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Performance question CTM:People timing ** Yes I agree you would want to avoid pinning a table to memory whose contents are changed continuously by way of modifications or additions.. This would result in frequent memory writes which would beat the purpose of why you choose to pin it to memory in the first place. While the CTM:People table is a good candidate as its contents change less frequently in most standard environments, unless it’s a B2C environment where you maintain your customer base in your CTM:People form, if the table size is as small as 140K, just optimizing searches on it is more than enough, and pinning it to memory is an overkill.. Optimizing searches on this table when records are about that much or even upto half a million, would return the search in less than a fraction of a second anyways.. Joe From: Guillaume Rheault<mailto:guilla...@dcshq.com> Sent: Thursday, September 08, 2011 12:15 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Performance question CTM:People timing ** Joe, well, I disagree with your rationale... actually because it is not a large table, you can pin in it memory. Generally speaking, you only pin into memory look-up tables that are used heavily, and the people form/table is a good candidate. You definitely do not want to pin a transactional table (like the incident form). Guillaume ________________________________ From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] on behalf of Joe Martin D'Souza [jdso...@shyle.net<mailto:jdso...@shyle.net>] Sent: Thursday, September 01, 2011 2:19 PM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Performance question CTM:People timing ** For only 140K records I don’t think you need to do anything out of the ordinary to boost up performance. If your statistics were not updated, it does make sense as Oracle didn’t know it had to use indexes and was perhaps attempting table scans assuming the table has no records if the statistics information it had for row count was 0 or thereabouts prior to updating it.. Personally I don’t really think you can consider CTM:People with around 140 K records to be a large object. Its big but not that big enough to be considered to pin to memory.. Joe From: John Sundberg<mailto:john.sundb...@kineticdata.com> Sent: Thursday, September 01, 2011 1:31 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Performance question CTM:People timing ** True... good suggestion. Fundamentally - I was looking for what is "normal" -- what we were seeing was what we thought was slow. But - just cause you think something is slow - does not mean that it is slow. Sometimes -- you have to look to your neighbors and compare. So - thanks to all that shared their timings and system info. -John On Sep 1, 2011, at 8:30 AM, Guillaume Rheault wrote: ** One more way to make things even faster in Oracle is to "pin" the underlying T table into memory. Ask the DBA over there to do that -Guilalume ________________________________ From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] on behalf of John Sundberg [john.sundb...@kineticdata.com<mailto:john.sundb...@kineticdata.com>] Sent: Thursday, August 25, 2011 7:25 AM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Performance question CTM:People timing ARSlist: "Where the Answers Are"_ _attend WWRUG11 www.wwrug.com<http://www.wwrug.com> ARSlist: "Where the Answers Are"_ -- Patrick Zandi _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
