:"SYS_B_XX" is an Oracle bind variable. Oracle strips all the literals and replaced them with bind variables because CURSOR_SHARING is set to SIMILAR or FORCE. This is ok. The Remedy SQL logs should be used to get the SQL statement with the literal values.
This could be a case where a user is searching for "%" simply to return all records. To Doug's point, the T28.C536870993 LIKE "SYS_B_67" part of your query is simply an inefficient query. It may try to return 1m rows or it may be performing a table scan. Fix that part of the query and the rest should be fine. Axton On Mon, Feb 28, 2011 at 11:56 AM, Mueller, Doug <[email protected]>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" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
