Jeff, It sounds like you may need to make some more indexes. Please have your DBAs provide a plan for the query execution. Look for sequential scans in particular. That may help you identify possible index creation options. We use PostgreSQL here, but I would suspect that many of the index creations needed for it to be performant would be the same with Oracle. There are a couple of posts about PostgreSQL tuning in the mailing list that you can refer too. Good luck.
Ken On Tue, May 15, 2007 at 09:37:11AM -0700, Jeff Stark wrote: > Hello, > > We are trying to identify very poor performance levels of the RT > Application 3.6.3 on REHL/Apache with Oracle. In doing so, we had our > DBAs take a look at the Oracle box and the identified some very > expensive queries that were taking nearly 10 seconds to return, such as: > > SELECT * FROM > ( SELECT limitquery.*, rownum limitrownum FROM > ( SELECT main.* FROM > ( SELECT DISTINCT main.id > FROM CustomFields main > JOIN ObjectCustomFields > ObjectCustomFields_1 ON ( ObjectCustomFields_1.CustomField = main.id ) > WHERE (main.Name = 'Customer') > AND (ObjectCustomFields_1.ObjectId = > '0') > AND (main.LookupType = > 'RT::Queue-RT::Ticket') ) distinctquery, > CustomFields main > WHERE (main.id = distinctquery.id) > ORDER BY main.SortOrder ASC, main.id ASC ) > limitquery WHERE rownum <= 1 ) > WHERE limitrownum >= 1 > > Their concern is mainly around the joins being inequality joins, as even > indexes won't help with these types of queries. Has anyone seen this > same behavior and/or has anyone done anything to modify the system to > generate better join clauses in the queries? > > Also I must note, we only have 450 tickets in the system at this > time...this was in just over a week with less than half of the users > active, so we expect the number to grow significantly as we onboard > additional users. > > Thanks, > > Jeff Stark > > > _______________________________________________ > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users > > Community help: http://wiki.bestpractical.com > Commercial support: [EMAIL PROTECTED] > > > Discover RT's hidden secrets with RT Essentials from O'Reilly Media. > Buy a copy at http://rtbook.bestpractical.com _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
