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:arslist@ARSLIST.ORG] On Behalf Of vikram_k > Sent: Monday, February 28, 2011 1:29 AM > To: arslist@ARSLIST.ORG > 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"
