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) [[email protected]] on behalf of Joe Martin D'Souza [[email protected]] Sent: Thursday, September 08, 2011 12:33 PM To: [email protected] 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:[email protected]> Sent: Thursday, September 08, 2011 12:15 PM Newsgroups: public.remedy.arsystem.general To: [email protected]<mailto:[email protected]> 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) [[email protected]] on behalf of Joe Martin D'Souza [[email protected]] Sent: Thursday, September 01, 2011 2:19 PM To: [email protected] 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:[email protected]> Sent: Thursday, September 01, 2011 1:31 PM Newsgroups: public.remedy.arsystem.general To: [email protected]<mailto:[email protected]> 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) [[email protected]<mailto:[email protected]>] on behalf of John Sundberg [[email protected]<mailto:[email protected]>] Sent: Thursday, August 25, 2011 7:25 AM To: [email protected]<mailto:[email protected]> Subject: Re: Performance question CTM:People timing ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
