Hello listers... Vital stats: SuSe Linux running ARS 7.6.04 p2, CMDB 7.6.04 p4, ITSM 7.6.04 p2. Oracle 11g RAC backend DB on Linux
My question is also at the end, but I'll add a tl;dr version here: Does anyone else know of places where the app is querying all of BMC Computer System besides the Asset Console? We have 781,000+ CI's in the BMC Computer System class. By default when you open the Asset Console a table field refreshes to show all of the CI's in your class set in your preferences. If you do not have one it uses BMC Computer System. The query it issues is really ridiculous. Here's an example (user ID removed for privacy reasons): SELECT C7,COUNT(*) FROM T1347 WHERE ((T1347.C400079600 = 'BMC_COMPUTERSYSTEM') AND ((T1347.C112 LIKE '%;''USER_ID_GOES_HERE'';%') OR (T1347.C112 LIKE '%;0;%') OR (T1347.C112 LIKE '%;-1098;%') OR (T1347.C112 LIKE '%;-20000;%') OR (T1347.C112 LIKE '%;-20032;%') OR (T1347.C112 LIKE '%;1000000266;%') OR (T1347.C112 LIKE '%;1000000264;%') OR (T1347.C112 LIKE '%;1000000007;%') OR (T1347.C112 LIKE '%;20403;%') OR (T1347.C112 LIKE '%;20316;%') OR (T1347.C112 LIKE '%;20315;%') OR (T1347.C112 LIKE '%;20313;%') OR (T1347.C112 LIKE '%;20302;%') OR (T1347.C112 LIKE '%;20302;%') OR (T1347.C112 LIKE '%;20055;%') OR (T1347.C112 LIKE '%;20032;%') OR (T1347.C112 LIKE '%;20012;%') OR (T1347.C112 LIKE '%;20007;%') OR (T1347.C112 LIKE '%;20003;%') OR (T1347.C112 LIKE '%;20000;%') OR (T1347.C112 LIKE '%;13010;%') OR (T1347.C112 LIKE '%;13007;%') OR (T1347.C112 LIKE '%;13006;%') OR (T1347.C112 LIKE '%;804;%') OR (T1347.C112 LIKE '%;803;%') OR (T1347.C112 LIKE '%;440;%'))) GROUP BY C7 ORDER BY C7 As a bonus, If you change your preferences anywhere in the suite of ITSM apps, it appears to change this back to BMC Computer System. The query above is exceptionally time-consuming at the database level out of the box (15 minutes in some cases). Our DBA's have done some tuning and gotten the query faster, and BMC support is having us add an index on BMC:Base_Element which appears to drop the query time substantially. We don't have this in production yet but we think we will As a stop-gap we changed everyone's preferences to use a different class in the app, but we are still seeing this query over and over again in the logs. It brought down two servers today so far. Does anyone else know of places where the app is querying all of BMC Computer System besides the Asset Console? Also, this is a known problem with BMC. See below: article KA314201 https://kb.bmc.com/infocenter/index?page=content&id=KA314201&actp=search&viewlocale=en_US&searchid=1396926399792 William Rentfrow wrentf...@stratacominc.com Office: 715-204-3061 Cell: 715-398-5056 _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"