hi, We see this issue on every Monday after the deployment and it resolves by itself by Wednesday after the deployment is done on Saturday . I am checking the other interfaces that might have an impact as well.
Thanks, Vikram Misi Mladoniczky wrote: > > Hi, > > No you don't have the "%zzz%". > > You have a "zzz%", which is an entirely different thing, and will allow > usage of indexes. > > It is the leading match in the LIKE "%zzz%" that prevents the index from > being used. > > Admin changes can take a lot of resources from the server in the period > right after the change, to allow for the server to recache all > definitions. Typically less than 10 minutes will be needed. > > Admin changes will require the clients to recache as well, which may slow > things down slightly, but should not prevent the database from using > indexes. > > Best Regards - Misi, RRR AB, http://www.rrr.se > > Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10): > * RRR|License - Not enough Remedy licenses? Save money by optimizing. > * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. > Find these products, and many free tools and utilities, at http://rrr.se. > >> hi Doug, >> >> Thanks for the reply as always lot of knowledge shared. You are right I >> picked this query from TOAD application and hence the SYS_B_XX variable >> comes into the picture. I was able to catch the query from SQL logs and >> its >> pasted below : >> >> <SQL > <TID: 000033> <RPC ID: 0001008137> <Queue: List > >> <Client-RPC: >> 390620 > <USER: vikram > /* Mon Feb 28 2011 >> 08:46:16.9857 */ SELECT >> T28.C1,C600011000,C536870984,C536870913,C900106001,C901050057,C536871019,C900200400,C536870958,C536870950,C900170325,C536870935,C900100003,C536870929,C536870931,C536870914,C536871018,C536870938,C650000000,C536870952,C536871025,C536871038,C536900000,C536870912,C7,C900040206,C901092014,C900110076,C536870987,C901170021,C536870957 >> FROM T28 WHERE (((T28.C112 LIKE '%;44451;%') OR ((T28.C112 LIKE >> '%;44450;%') OR ((T28.C112 LIKE '%;9999999;%') OR ((T28.C112 LIKE >> '%;500014;%') OR ((T28.C112 LIKE '%;500013;%') OR ((T28.C112 LIKE >> '%;500000;%') OR ((T28.C112 LIKE '%;50098;%') OR ((T28.C112 LIKE >> '%;99986;%') OR ((T28.C112 LIKE '%;99986;%') OR ((T28.C112 LIKE >> '%;99989;%') >> OR ((T28.C112 LIKE '%;99930;%') OR ((T28.C112 LIKE '%;99985;%') OR >> ((T28.C112 LIKE '%;99931;%') OR ((T28.C112 LIKE '%;99925;%') OR >> ((T28.C112 >> LIKE '%;99906;%') OR ((T28.C112 LIKE '%;99928;%') OR ((T28.C112 LIKE >> '%;99957;%') OR ((T28.C112 LIKE '%;99932;%') OR ((T28.C112 LIKE >> '%;99929;%') >> OR ((T28.C112 LIKE '%;99921;%') OR ((T28.C112 LIKE '%;99926;%') OR >> ((T28.C112 LIKE '%;99902;%') OR ((T28.C112 LIKE '%;99950;%') OR >> ((T28.C112 >> LIKE '%;99924;%') OR ((T28.C112 LIKE '%;44407;%') OR ((T28.C112 LIKE >> '%;99960;%') OR ((T28.C112 LIKE '%;99922;%') OR ((T28.C112 LIKE >> '%;99990;%') >> OR ((T28.C112 LIKE '%;44405;%') OR ((T28.C112 LIKE '%;44404;%') OR >> ((T28.C112 LIKE '%;44418;%') OR ((T28.C112 LIKE '%;44418;%') OR >> ((T28.C112 >> LIKE '%;99923;%') OR ((T28.C112 LIKE '%;99908;%') OR ((T28.C112 LIKE >> '%;99904;%') OR ((T28.C112 LIKE '%;99927;%') OR ((T28.C112 LIKE >> '%;99939;%') >> OR ((T28.C112 LIKE '%;99971;%') OR ((T28.C112 LIKE '%;99991;%') OR >> ((T28.C112 LIKE '%;99901;%') OR ((T28.C112 LIKE '%;99905;%') OR >> ((T28.C112 >> LIKE '%;99941;%') OR ((T28.C112 LIKE '%;99958;%') OR ((T28.C112 LIKE >> '%;99907;%') OR ((T28.C112 LIKE '%;99953;%') OR ((T28.C112 LIKE >> '%;44406;%') >> OR ((T28.C112 LIKE '%;99987;%') OR ((T28.C112 LIKE '%;99903;%') OR >> ((T28.C112 LIKE '%;99988;%') OR ((T28.C112 LIKE '%;99951;%') OR >> ((T28.C112 >> LIKE '%;99972;%') OR ((T28.C112 LIKE '%;500012;%') OR ((T28.C112 LIKE >> '%;50045;%') OR ((T28.C112 LIKE '%;50048;%') OR ((T28.C112 LIKE >> '%;500011;%') OR ((T28.C112 LIKE '%;500010;%') OR ((T28.C112 LIKE >> '%;666;%') >> OR ((T28.C112 LIKE '%;500009;%') OR ((T28.C112 LIKE '%;500008;%') OR >> ((T28.C112 LIKE '%;500007;%') OR ((T28.C112 LIKE '%;50046;%') OR >> ((T28.C112 >> LIKE '%;500006;%') OR ((T28.C112 LIKE '%;500005;%') OR ((T28.C112 LIKE >> '%;500004;%') OR ((T28.C112 LIKE '%;500003;%') OR ((T28.C112 LIKE >> '%;500002;%') OR ((T28.C112 LIKE '%;500001;%') OR ((T28.C112 LIKE >> '%;0;%') >> OR ((T28.C112 LIKE '%;2005000;%') OR ((T28.C112 LIKE '%;655;%') OR >> ((T28.C112 LIKE '%;20017;%') OR ((T28.C112 LIKE '%;652;%') OR ((T28.C112 >> LIKE '%;671;%') OR (T28.C112 LIKE >> '%;0;%')))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) >> AND (T28.C536870913 LIKE '101200140%')) ORDER BY 1 ASC >> >> >> We have a lot of users in lot of groups and hence we have high number of >> LIKE clauses. >> As you can see we have "%zzz% as you mentioned in your post which will >> cause >> a complete table scan. Right now we are trying 2 things : >> >> a) Find the workflow that tends to fire this query and >> b) Try and find a way to minimize the groups every user belongs. Can >> computed groups be a good option for this and is it supported for ARS >> 5.0.1.? Sorry to ask you about this as I have limited understandning >> about >> computed groups. >> >> One interesting observation though, we see this issue only after the >> weekend >> we have some kind of code change on the production system and in a sense >> when we do some kind of admin operation on the application not >> necessarily >> the change is done on the schema on which these huge queries are fired >> but >> we still face this issue. Can there be any possible connection between >> this? >> >> Thanks, >> Vikram >> >> Mueller, Doug wrote: >>> >>> Vikram, >>> >>> Several observations about this command: >>> >>> First, this command seems to have been run through some kind of >>> translator >>> that >>> has taken every string between "" symbols and replaced it with an >>> incrementing >>> string of SYS_B_xx where xx is an incrementing number. >>> >>> Because of this, it is a bit hard to assist with the question you ask >>> because >>> there is critical information in the query that you have masked out and >>> that >>> information may be the key to the solution. The most critical missing >>> piece of >>> data is whatever SYS_B_67 was originally. As you will see from the >>> remaining >>> notes, the rest of the strings really aren't important. >>> >>> 1) You comment on disallowing unqualified queries and say that this >>> didn't >>> help >>> this query. Well, that option is not related as this is NOT an >>> unqualified >>> query. There is a qualification that is being specified by the >>> person >>> who >>> is issuing this query. >>> >>> '536870993' LIKE "SYS_B_67" >>> >>> Now, the issue here is what is SYS_B_67. If it is really a search >>> that >>> looks >>> something like the following >>> >>> %zzz% >>> >>> Then, you simply have a terribly inefficient query. You are >>> searching >>> in a >>> character field for where some set of characters (zzz in my example) >>> is >>> somewhere in the field. This requires a table scan to look at each >>> record >>> and the data in that field to see if the string matches. >>> >>> If it is really a search that looks more like >>> >>> zzz% >>> >>> Then, you can get an efficient search out of this. This is to find a >>> value >>> where the string STARTS with a set of characters (zzz in my example) >>> and has >>> other data after it. If like this, then indexes will be used in the >>> search >>> if there is an index on field 536870993. If there is no index on >>> that >>> field >>> one could be added and will make a difference. REMEMBER, the search >>> cannot >>> have a leading % for the index to be useful. If there is a leading % >>> like in >>> the first example, the index will not be used even if present. >>> >>> There is no other qualifier that the customer is supplying to further >>> limit >>> the search so the entire indexing scheme hinges on this one field. >>> >>> 2) All the comparisons of field 112 to a value are things the AR System >>> adds >>> onto the search because you have a row level security field (field >>> 112) >>> on >>> the form and you do not have global access to the entries, and the >>> system is >>> checking to see if you have access to rows it is retrieving. >>> >>> At first glance, the search is not efficient as it is a LIKE "%xxx%" >>> type >>> search. So, if that was the only search criteria, it is correct, >>> this >>> would >>> be an inefficient criteria. However, the design is for this to be an >>> ADDITIVE criteria, not the primary criteria. The primary criteria is >>> the >>> search the user provides, this is just an additional test. In the >>> role >>> of >>> additional test, efficiency is not significant as only one index can >>> be >>> used >>> for a search. The row would already have been retrieved to apply >>> this >>> criteria as additional qualification. >>> >>> So, the entire clause with Ored tests of field 112 can be discounted >>> from any >>> qualification you are trying to check performance of. >>> >>> Now, if that clause is the only clause in the WHERE, that means you >>> have >>> allowed unqualified queries and someone is issuing an unqualified >>> query >>> (which is a table scan). >>> >>> 3) Whoever the user is in this scenario is a member of 67 groups >>> (SYS_B_00 >>> through SYS_B_66). This is a lot of groups for a single user to be a >>> member >>> of. Not disallowed, and the system will work and it does support >>> that. >>> It >>> is just an observation that it is a large number. Typically, a user >>> is >>> a >>> member of a small number of groups and this clause is significantly >>> shorter. >>> >>> >>> >>> Hopefully, this gives you the information you need. What I really feel >>> is >>> the >>> challenge here is that the user is qualifying field 536870993 to look >>> for >>> a >>> string ANYWHERE in the field and that is causing the problem. >>> >>> Solutions: >>> >>> 1) If this really should be an = instead of a LIKE "%zzz%", you may >>> check >>> the >>> default QBE setting for the field and change it from Anywhere to >>> Leading or >>> Matching. The default QBE match criteria (a view property of the >>> field) is >>> Anywhere. So, a user may not be aware if they are using the query >>> screen and >>> putting in a value for that field what is happening. Changing the >>> definition will change the command issued. >>> >>> 2) If a user is doing this in a query bar, educate the user about the >>> cost >>> of >>> this type of operation. >>> >>> 3) If workflow is issuing this command, determine whether the workflow >>> could >>> use a leading or equal search. >>> >>> 4) Is there other criteria you can use on some other field(s) on the >>> form >>> to >>> help limit the scope of the rows that you need to search across? >>> >>> Of course, if what you need to do is a search for that string anywhere >>> in >>> the >>> field AND there are no other criteria you can use to search other fields >>> to >>> limit which rows are looked at, then you are stuck with the performance >>> of >>> the >>> operation you need to perform. >>> >>> >>> Good luck with your situation and with finding a solution for long >>> query. >>> >>> Doug Mueller >>> >>> -----Original Message----- >>> From: Action Request System discussion list(ARSList) >>> [mailto:[email protected]] On Behalf Of vikram_k >>> Sent: Monday, February 28, 2011 1:29 AM >>> To: [email protected] >>> Subject: Huge queries hogging up the database >>> >>> hi all, >>> >>> I had recently put some posts regarding AR Performance and I am thankful >>> that most of my problems are now resolved. But there is one issue which >>> is >>> a >>> bottle neck I am seeing a huge oracle query like >>> >>> SELECT >>> T28.C1,C600011000,C536870984,C536870913,C900106001,C901050057,C536871019,C900200400,C536870958,C536870950,C900170325,C536870935,C900100003,C536870929,C536870931,C536870914,C536871018,C536870938,C650000000,C536870952,C536871025,C536871038,C536900000,C536870912,C7,C900040206,C901092014,C900110076,C536870987,C901170021,C536870957 >>> FROM T28 >>> WHERE (((T28.C112 LIKE :"SYS_B_00") OR ((T28.C112 LIKE :"SYS_B_01") OR >>> ((T28.C112 LIKE :"SYS_B_02") OR ((T28.C112 LIKE :"SYS_B_03") OR >>> ((T28.C112 >>> LIKE :"SYS_B_04") OR ((T28.C112 LIKE :"SYS_B_05") OR ((T28.C112 LIKE >>> :"SYS_B_06") OR ((T28.C112 LIKE :"SYS_B_07") OR ((T28.C112 LIKE >>> :"SYS_B_08") >>> OR ((T28.C112 LIKE :"SYS_B_09") OR ((T28.C112 LIKE :"SYS_B_10") OR >>> ((T28.C112 LIKE :"SYS_B_11") OR ((T28.C112 LIKE :"SYS_B_12") OR >>> ((T28.C112 >>> LIKE :"SYS_B_13") OR ((T28.C112 LIKE :"SYS_B_14") OR ((T28.C112 LIKE >>> :"SYS_B_15") OR ((T28.C112 LIKE :"SYS_B_16") OR ((T28.C112 LIKE >>> :"SYS_B_17") >>> OR ((T28.C112 LIKE :"SYS_B_18") OR ((T28.C112 LIKE :"SYS_B_19") OR >>> ((T28.C112 LIKE :"SYS_B_20") OR ((T28.C112 LIKE :"SYS_B_21") OR >>> ((T28.C112 >>> LIKE :"SYS_B_22") OR ((T28.C112 LIKE :"SYS_B_23") OR ((T28.C112 LIKE >>> :"SYS_B_24") OR ((T28.C112 LIKE :"SYS_B_25") OR ((T28.C112 LIKE >>> :"SYS_B_26") >>> OR ((T28.C112 LIKE :"SYS_B_27") OR ((T28.C112 LIKE :"SYS_B_28") OR >>> ((T28.C112 LIKE :"SYS_B_29") OR ((T28.C112 LIKE :"SYS_B_30") OR >>> ((T28.C112 >>> LIKE :"SYS_B_31") OR ((T28.C112 LIKE :"SYS_B_32") OR ((T28.C112 LIKE >>> :"SYS_B_33") OR ((T28.C112 LIKE :"SYS_B_34") OR ((T28.C112 LIKE >>> :"SYS_B_35") >>> OR ((T28.C112 LIKE :"SYS_B_36") OR ((T28.C112 LIKE :"SYS_B_37") OR >>> ((T28.C112 LIKE :"SYS_B_38") OR ((T28.C112 LIKE :"SYS_B_39") OR >>> ((T28.C112 >>> LIKE :"SYS_B_40") OR ((T28.C112 LIKE :"SYS_B_41") OR ((T28.C112 LIKE >>> :"SYS_B_42") OR ((T28.C112 LIKE :"SYS_B_43") OR ((T28.C112 LIKE >>> :"SYS_B_44") >>> OR ((T28.C112 LIKE :"SYS_B_45") OR ((T28.C112 LIKE :"SYS_B_46") OR >>> ((T28.C112 LIKE :"SYS_B_47") OR ((T28.C112 LIKE :"SYS_B_48") OR >>> ((T28.C112 >>> LIKE :"SYS_B_49") OR ((T28.C112 LIKE :"SYS_B_50") OR ((T28.C112 LIKE >>> :"SYS_B_51") OR ((T28.C112 LIKE :"SYS_B_52") OR ((T28.C112 LIKE >>> :"SYS_B_53") >>> OR ((T28.C112 LIKE :"SYS_B_54") OR ((T28.C112 LIKE :"SYS_B_55") OR >>> ((T28.C112 LIKE :"SYS_B_56") OR ((T28.C112 LIKE :"SYS_B_57") OR >>> ((T28.C112 >>> LIKE :"SYS_B_58") OR ((T28.C112 LIKE :"SYS_B_59") OR ((T28.C112 LIKE >>> :"SYS_B_60") OR ((T28.C112 LIKE :"SYS_B_61") OR ((T28.C112 LIKE >>> :"SYS_B_62") >>> OR ((T28.C112 LIKE :"SYS_B_63") OR ((T28.C112 LIKE :"SYS_B_64") OR >>> ((T28.C112 LIKE :"SYS_B_65") OR (T28.C112 LIKE >>> :"SYS_B_66"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) >>> AND (T28.C536870993 LIKE :"SYS_B_67")) ORDER BY :"SYS_B_68" ASC >>> >>> I was told to disallow unqualified search which I did and yet I am >>> seeing >>> this kind of query. >>> >>> Can anyone help me understand what exactly is this query doing and what >>> can >>> be done to avoid it... >>> >>> Thanks, >>> Vikram >>> -- >>> View this message in context: >>> http://old.nabble.com/Huge-queries-hogging-up-the-database-tp31030153p31030153.html >>> Sent from the ARS (Action Request System) mailing list archive at >>> Nabble.com. >>> >>> _______________________________________________________________________________ >>> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >>> 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" >>> >>> >> >> -- >> View this message in context: >> http://old.nabble.com/Huge-queries-hogging-up-the-database-tp31030153p31046950.html >> Sent from the ARS (Action Request System) mailing list archive at >> Nabble.com. >> >> _______________________________________________________________________________ >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> 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" > > -- View this message in context: http://old.nabble.com/Huge-queries-hogging-up-the-database-tp31030153p31047544.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
