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"
