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 Sent: Thursday, September 01, 2011 1:31 PM Newsgroups: public.remedy.arsystem.general To: 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] on behalf of John Sundberg [john.sundb...@kineticdata.com] Sent: Thursday, August 25, 2011 7:25 AM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Thanks all for the responses. We "figured out" our slowness. Turns out Oracle statistics had not been updated for 6+ months. Now with 140,000 -- it is near instantaneous on Oracle. -John On Mon, Aug 22, 2011 at 9:20 AM, Andrew C Goodall <ago...@jcpenney.com> wrote: Where are you counting from? - query on CTM_People involves multiple queries not just one, so are you just counting time from the "main" query to the next or the total time to process all queries for that operation? Ours 329ms (from main to last query in operation) - 357,000+ total records - SQL 2008 remote cluster. Regards, Andrew Goodall Software Engineer 2 | Development Services | jcpenney . www.jcp.com -----Original Message----- 2011/8/20 John Sundberg <john.sundb...@kineticdata.com>: > ** How long does it take your DB system to resolve a query for an "exact > match" on CTM:People where the query is > > 'Remedy Login ID' = "some user id" > > Also -- how many records are in your CTM:People -- and what DB are you > using? > Our sample system is 800ms - with 40,000 records... , Oracle 11g2 > > (Please get the timings from SQL log) > > -John > > > -- > John David Sundberg > 235 East 6th Street, Suite 400B > St. Paul, MN 55101 > (651) 556-0930-work > (651) 247-6766-cell > (651) 695-8577-fax > john.sundb...@kineticdata.com > _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" The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" -- John David Sundberg 235 East 6th Street, Suite 400B St. Paul, MN 55101 (651) 556-0930-work (651) 247-6766-cell (651) 695-8577-fax john.sundb...@kineticdata.com _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ -- John Sundberg Kinetic Data, Inc. "Building a Better Service Experience" Recipient of: WWRUG10 Best Customer Service/Support Award WWRUG09 Innovator of the Year Award john.sundb...@kineticdata.com 651.556.0930 I www.kineticdata.com _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"